MySQL commands - Revision history

Planetoid at 03:32, 15 July 2025

2025-07-15T03:32:02Z

← Older revision		Revision as of 11:32, 15 July 2025
Line 149:		Line 149:


	{{Template:~~Troubleshooting~~}}		{{Template:Data factory flow}}

	[[Category:MySQL]]		[[Category:MySQL]]
	[[Category:Data Science]]		[[Category:Data Science]]

Planetoid: /* Synchronizing databases */

2025-03-20T06:24:47Z

Synchronizing databases

← Older revision		Revision as of 14:24, 20 March 2025
Line 119:		Line 119:

	Transfer date from MS SQL server to MySQL or MariaDB server<ref>[https://mariadb.com/kb/en/library/monyog-sqlyog/ MONyog & SQLyog - MariaDB Knowledge Base]</ref>:		Transfer date from MS SQL server to MySQL or MariaDB server<ref>[https://mariadb.com/kb/en/library/monyog-sqlyog/ MONyog & SQLyog - MariaDB Knowledge Base]</ref>:
	* ~~To export MySQL data compatible with Microsoft SQL Server: {{kbd \| key=<nowiki>mysqldump --compatible=mssql~~ [~~database_name]</nowiki>}}<ref>~~[~~https://stackoverflow.com/questions/60139129/how-to-convert-mysql-dump-to-sql-server-format How to convert~~ MySQL ~~dump~~ to ~~SQL Server format - Stack Overflow~~]~~</ref>~~		* See on [[Troubleshooting_of_MySQL_errors#Transfer_Data_from_MySQL_to_MSSQL \| Transfer Data from MySQL to MSSQL]]
	* ''$'' [https://www.webyog.com/product/sqlyog SQLyog] ~~for {{Win}}. Use a query to specify the data to transfer from MS SQL server into MySQL server.~~

	=== Show the MySQL process list & kill the process ===		=== Show the MySQL process list & kill the process ===

Planetoid: /* Synchronizing databases */

2025-03-20T03:29:59Z

Synchronizing databases

← Older revision		Revision as of 11:29, 20 March 2025
Line 119:		Line 119:

	Transfer date from MS SQL server to MySQL or MariaDB server<ref>[https://mariadb.com/kb/en/library/monyog-sqlyog/ MONyog & SQLyog - MariaDB Knowledge Base]</ref>:		Transfer date from MS SQL server to MySQL or MariaDB server<ref>[https://mariadb.com/kb/en/library/monyog-sqlyog/ MONyog & SQLyog - MariaDB Knowledge Base]</ref>:
			* To export MySQL data compatible with Microsoft SQL Server: {{kbd \| key=<nowiki>mysqldump --compatible=mssql [database_name]</nowiki>}}<ref>[https://stackoverflow.com/questions/60139129/how-to-convert-mysql-dump-to-sql-server-format How to convert MySQL dump to SQL Server format - Stack Overflow]</ref>
	* ''$'' [https://www.webyog.com/product/sqlyog SQLyog] for {{Win}}. Use a query to specify the data to transfer from MS SQL server into MySQL server.		* ''$'' [https://www.webyog.com/product/sqlyog SQLyog] for {{Win}}. Use a query to specify the data to transfer from MS SQL server into MySQL server.

Unknown user: /* Preparation */

2023-07-10T02:34:47Z

Preparation

← Older revision		Revision as of 10:34, 10 July 2023
Line 6:		Line 6:
	* {{Win}}: Install (1) [https://www.cygwin.com/ Cygwin], (2) search mysql & install MySQL database clients apps package		* {{Win}}: Install (1) [https://www.cygwin.com/ Cygwin], (2) search mysql & install MySQL database clients apps package
	* {{Mac}}:		* {{Mac}}:
	** Install [https://www.apachefriends.org/zh_tw/download.html XAMPP]. Path of {{kbd \| key=mysql}} executable file is: /Applications/xampp/xamppfiles/bin/mysql		** Install [https://www.apachefriends.org/zh_tw/download.html XAMPP]. Path of {{kbd \| key=mysql}} executable file is: {{kbd \| key=/Applications/xampp/xamppfiles/bin/mysql}}
	** MAMP: path of mysql command: {{kbd \| key=/Applications/MAMP/Library/bin/mysql}}<ref>[https://dev.to/arbaoui_mehdi/how-to-access-the-mysql-cli-with-mamp-25m How to access the MySQL CLI With MAMP - DEV Community]</ref>		** MAMP: path of mysql command: {{kbd \| key=/Applications/MAMP/Library/bin/mysql}}<ref>[https://dev.to/arbaoui_mehdi/how-to-access-the-mysql-cli-with-mamp-25m How to access the MySQL CLI With MAMP - DEV Community]</ref>

Unknown user: /* Preparation */

2023-07-10T02:21:46Z

Preparation

← Older revision		Revision as of 10:21, 10 July 2023
Line 5:		Line 5:
	* {{Linux}}:		* {{Linux}}:
	* {{Win}}: Install (1) [https://www.cygwin.com/ Cygwin], (2) search mysql & install MySQL database clients apps package		* {{Win}}: Install (1) [https://www.cygwin.com/ Cygwin], (2) search mysql & install MySQL database clients apps package
	* {{Mac}}: Install [https://www.apachefriends.org/zh_tw/download.html XAMPP]. Path of {{kbd \| key=mysql}} executable file is: /Applications/xampp/xamppfiles/bin/mysql		* {{Mac}}:
			** Install [https://www.apachefriends.org/zh_tw/download.html XAMPP]. Path of {{kbd \| key=mysql}} executable file is: /Applications/xampp/xamppfiles/bin/mysql
			** MAMP: path of mysql command: {{kbd \| key=/Applications/MAMP/Library/bin/mysql}}<ref>[https://dev.to/arbaoui_mehdi/how-to-access-the-mysql-cli-with-mamp-25m How to access the MySQL CLI With MAMP - DEV Community]</ref>

	(optional) install the [http://www.ivarch.com/programs/pv.shtml Pipe Viewer] (pv) package		(optional) install the [http://www.ivarch.com/programs/pv.shtml Pipe Viewer] (pv) package

Unknown user: /* Synchronizing databases */

2021-10-06T07:55:06Z

Synchronizing databases

← Older revision		Revision as of 15:55, 6 October 2021
Line 106:		Line 106:
	** Steps: Menu --> Powertools --> Database Synchronization Wizard		** Steps: Menu --> Powertools --> Database Synchronization Wizard
	** Scheduler: Available {{Gd}}. Save the job file & Schedule it using Windows scheduler.		** Scheduler: Available {{Gd}}. Save the job file & Schedule it using Windows scheduler.
	* ''$'' [https://www.devart.com/dbforge/mysql/studio/ MySQL GUI Tool - MariaDB and MySQL Front End Client]		* ''$'' [https://www.devart.com/dbforge/mysql/studio/ MySQL GUI Tool - MariaDB and MySQL Front End Client for Windows]
	** Steps:		** Steps:
	*** Menu: Comparison -> New Data Comparison -> Choose the connection: Next ->		*** Menu: Comparison -> New Data Comparison -> Choose the connection: Next ->
Line 114:		Line 114:
	*** Menu: SQL -> Execute		*** Menu: SQL -> Execute
	** Scheduler:		** Scheduler:
	* ''$'' [https://www.devart.com/dbforge/mysql/ MySQL Tools for Database Management and Administration]

	Transfer date from MS SQL server to MySQL or MariaDB server<ref>[https://mariadb.com/kb/en/library/monyog-sqlyog/ MONyog & SQLyog - MariaDB Knowledge Base]</ref>:		Transfer date from MS SQL server to MySQL or MariaDB server<ref>[https://mariadb.com/kb/en/library/monyog-sqlyog/ MONyog & SQLyog - MariaDB Knowledge Base]</ref>:

Unknown user: /* Synchronizing databases */

2021-06-15T03:58:33Z

Synchronizing databases

← Older revision		Revision as of 11:58, 15 June 2021
Line 114:		Line 114:
	*** Menu: SQL -> Execute		*** Menu: SQL -> Execute
	** Scheduler:		** Scheduler:
			* ''$'' [https://www.devart.com/dbforge/mysql/ MySQL Tools for Database Management and Administration]

	Transfer date from MS SQL server to MySQL or MariaDB server<ref>[https://mariadb.com/kb/en/library/monyog-sqlyog/ MONyog & SQLyog - MariaDB Knowledge Base]</ref>:		Transfer date from MS SQL server to MySQL or MariaDB server<ref>[https://mariadb.com/kb/en/library/monyog-sqlyog/ MONyog & SQLyog - MariaDB Knowledge Base]</ref>:

Unknown user: /* Importing data from MySql sql file */

2021-06-01T09:14:30Z

Importing data from MySql sql file

← Older revision		Revision as of 17:14, 1 June 2021
Line 72:		Line 72:
	Optional		Optional
	# (optional) install {{kbd \| key =pv}} if the {{kbd \| key =pv}} was not installed for RHEL / CentOS / SL / Fedora Linux or {{kbd \| key =brew install pv}} for {{Mac}}<ref>[https://formulae.brew.sh/formula/pv pv — Homebrew Formulae]</ref>		# (optional) install {{kbd \| key =pv}} if the {{kbd \| key =pv}} was not installed for RHEL / CentOS / SL / Fedora Linux or {{kbd \| key =brew install pv}} for {{Mac}}<ref>[https://formulae.brew.sh/formula/pv pv — Homebrew Formulae]</ref>
			# (optional if the 7Zip was not installed) {{kbd \| key =yum install p7zip}} for RHEL / CentOS / SL / Fedora Linux

	'''method 1''': {{Gd}} Import the compressed *.sql file with gzip (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysql command:		'''method 1''': {{Gd}} Import the compressed *.sql file with gzip (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysql command:
Line 90:		Line 91:

	'''method 4''': Import ZIP file to MySql directly:		'''method 4''': Import ZIP file to MySql directly:
	# ~~(optional if~~ the 7Zip ~~was not installed) {{kbd \| key =yum install p7zip}}~~		# Install the 7Zip
	# {{kbd \| key = <nowiki>7za x -so data.zip \| mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME</nowiki>}} <ref>[http://stackoverflow.com/questions/11267309/importing-zipped-files-in-mysql-using-command-line database - Importing zipped files in Mysql using command line - Stack Overflow]</ref><ref>[http://needs-be.blogspot.tw/2011/01/install-7zip-on-centos-55.html Install 7Zip on CentOS 5.5]</ref> and press {{kbd \| key = <nowiki>Enter</nowiki> }}		# {{kbd \| key = <nowiki>7za x -so data.zip \| mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME</nowiki>}} <ref>[http://stackoverflow.com/questions/11267309/importing-zipped-files-in-mysql-using-command-line database - Importing zipped files in Mysql using command line - Stack Overflow]</ref><ref>[http://needs-be.blogspot.tw/2011/01/install-7zip-on-centos-55.html Install 7Zip on CentOS 5.5]</ref> and press {{kbd \| key = <nowiki>Enter</nowiki> }}
	# enter {{kbd \| key = <nowiki>password</nowiki> }} and press {{kbd \| key = <nowiki>Enter</nowiki> }}		# enter {{kbd \| key = <nowiki>password</nowiki> }} and press {{kbd \| key = <nowiki>Enter</nowiki> }}

Unknown user: /* Importing data from MySql sql file */

2021-06-01T09:08:23Z

Importing data from MySql sql file

← Older revision		Revision as of 17:08, 1 June 2021
Line 69:		Line 69:
	=== Importing data from MySql sql file ===		=== Importing data from MySql sql file ===
	{{exclaim}} Notice: existing database will be overwritten		{{exclaim}} Notice: existing database will be overwritten

			Optional
			# (optional) install {{kbd \| key =pv}} if the {{kbd \| key =pv}} was not installed for RHEL / CentOS / SL / Fedora Linux or {{kbd \| key =brew install pv}} for {{Mac}}<ref>[https://formulae.brew.sh/formula/pv pv — Homebrew Formulae]</ref>

	'''method 1''': {{Gd}} Import the compressed *.sql file with gzip (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysql command:		'''method 1''': {{Gd}} Import the compressed *.sql file with gzip (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysql command:
	# ~~(optional) install~~ {{kbd \| key =pv}} ~~if the {{kbd \| key =pv}} was not installed for RHEL / CentOS / SL / Fedora Linux~~		# Install {{kbd \| key =pv}}
	# Check if the file is gzip format (test the compressed file integrity) ex: (1) {{kbd \| key = <nowiki>gunzip -t database.sql.gz</nowiki>}} If not, it will return the message "gzip: database.sql.gz: not in gzip format." (2) Using [http://www.winmd5.com/ WinMD5 Free] for {{Win}} or [http://linux.die.net/man/1/md5sum md5sum] command for {{Linux}} to obtain MD5 checksum of files.		# Check if the file is gzip format (test the compressed file integrity) ex: (1) {{kbd \| key = <nowiki>gunzip -t database.sql.gz</nowiki>}} If not, it will return the message "gzip: database.sql.gz: not in gzip format." (2) Using [http://www.winmd5.com/ WinMD5 Free] for {{Win}} or [http://linux.die.net/man/1/md5sum md5sum] command for {{Linux}} to obtain MD5 checksum of files.
	# {{kbd \| key = <nowiki> pv database.sql.gz \| gunzip \| mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME</nowiki>}} <ref>[http://journal.andrewloe.com/2009/05/12/pv-gzip-mysql/ pv + gzip + mysql — W. Andrew Loe III: Journal]</ref> and press {{kbd \| key = <nowiki>Enter</nowiki> }}		# {{kbd \| key = <nowiki> pv database.sql.gz \| gunzip \| mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME</nowiki>}} <ref>[http://journal.andrewloe.com/2009/05/12/pv-gzip-mysql/ pv + gzip + mysql — W. Andrew Loe III: Journal]</ref> and press {{kbd \| key = <nowiki>Enter</nowiki> }}
Line 82:		Line 85:

	'''method 3''': Import the *.sql file (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysql command<ref>[http://dba.stackexchange.com/questions/17367/how-can-i-monitor-the-progress-of-an-import-of-a-large-sql-file mysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange]</ref>:		'''method 3''': Import the *.sql file (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysql command<ref>[http://dba.stackexchange.com/questions/17367/how-can-i-monitor-the-progress-of-an-import-of-a-large-sql-file mysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange]</ref>:
	# ~~(optional if the~~ {{kbd \| key =pv}} ~~was not installed) {{kbd \| key =yum install pv}} for RHEL / CentOS / SL / Fedora Linux~~		# Install {{kbd \| key =pv}}
	# {{kbd \| key = <nowiki>pv data.sql \| mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME</nowiki>}} <ref>[http://www.cyberciti.biz/faq/import-mysql-dumpfile-sql-datafile-into-my-database/ Import MySQL Dumpfile, SQL Datafile Into My Database]</ref> and press {{kbd \| key = <nowiki>Enter</nowiki> }}		# {{kbd \| key = <nowiki>pv data.sql \| mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME</nowiki>}} <ref>[http://www.cyberciti.biz/faq/import-mysql-dumpfile-sql-datafile-into-my-database/ Import MySQL Dumpfile, SQL Datafile Into My Database]</ref> and press {{kbd \| key = <nowiki>Enter</nowiki> }}
	# enter {{kbd \| key = <nowiki>password</nowiki> }} and press {{kbd \| key = <nowiki>Enter</nowiki> }} The console window will show ETA (Estimated Time of Arrival)		# enter {{kbd \| key = <nowiki>password</nowiki> }} and press {{kbd \| key = <nowiki>Enter</nowiki> }} The console window will show ETA (Estimated Time of Arrival)

Unknown user: /* Synchronizing databases */

2021-05-18T02:11:03Z

Synchronizing databases

← Older revision		Revision as of 10:11, 18 May 2021
Line 103:		Line 103:
	** Scheduler: Available {{Gd}}. Save the job file & Schedule it using Windows scheduler.		** Scheduler: Available {{Gd}}. Save the job file & Schedule it using Windows scheduler.
	* ''$'' [https://www.devart.com/dbforge/mysql/studio/ MySQL GUI Tool - MariaDB and MySQL Front End Client]		* ''$'' [https://www.devart.com/dbforge/mysql/studio/ MySQL GUI Tool - MariaDB and MySQL Front End Client]
	** Steps: Menu: Comparison -> New Data Comparison -> Choose the connection: Next -> Mapping: Select objects that you want to compare ->		** Steps:
			*** Menu: Comparison -> New Data Comparison -> Choose the connection: Next ->
			*** Mapping: Select objects that you want to compare ->
			*** After hours or minutes ...
			*** Menu: Comparison -> Synchronize -> Open the synchronization script in the internal editor -> Synchronize
			*** Menu: SQL -> Execute
	** Scheduler:		** Scheduler: