MySQL commands: Difference between revisions
(134 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
MySQL commands and troubleshooting of MySQL errors. | |||
=== Preparation === | |||
install MySQL database client apps contains {{kbd | key=mysql}} & {{kbd | key=mysqldump}} commands | |||
* {{Linux}}: | |||
* {{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: {{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> | |||
* Bulk export | (optional) install the [http://www.ivarch.com/programs/pv.shtml Pipe Viewer] (pv) package | ||
* {{Linux}}: | |||
*# [http://www.tecmint.com/enable-rpmforge-repository/ How to Enable RPMForge Repository in RHEL/CentOS 7.x/6.x/5.x/4.x] | |||
*# (1){{kbd | key =yum install pv}} for RHEL / CentOS / SL / Fedora Linux<ref>[http://www.cyberciti.biz/open-source/command-line-hacks/pv-command-examples/ Linux / Unix pv Command: Monitor Progress of Data Sent Via a Pipe]</ref>; (2) {{kbd | key =apt-get install pv}} for Debian / Ubuntu<ref>[http://www.ivarch.com/programs/pv.shtml ivarch.com: Pipe Viewer]</ref>; | |||
* {{Win}}: Install (1) [https://www.cygwin.com/ Cygwin], (2) search pv & install pv package | |||
* {{Mac}}: Install (1) {{kbd | key =brew install pv}} to get the latest version of pv package<ref>[http://ashleyangell.com/2013/11/pipe-viewer-pv-in-mac-osx/ Pipe Viewer (pv) in Mac OSX]</ref> and (2) MySQL database clients from [https://www.apachefriends.org/zh_tw/download.html XAMPP]. Path of mysql is: /Applications/xampp/xamppfiles/bin/mysql | |||
create the db user | |||
# create the db user | |||
# grant the minimum permission: {{kbd | key=SELECT}}, {{kbd | key=LOCK TABLES}} to the db user. SQL query: {{kbd | key=<nowiki>GRANT SELECT, LOCK TABLES ON `DATA\_BASE\_NAME`.* TO 'dbuser'@'localhost';</nowiki>}}<ref>[http://serverfault.com/questions/22712/minimum-permissions-for-a-user-to-perform-a-mysqldump security - Minimum permissions for a user to perform a mysqldump? - Server Fault]</ref> | |||
# the db user will be able to execute the mysqldump command | |||
=== Exporting data of database/table into MySql sql file === | |||
==== mysqldump Options ==== | |||
Default option without any options specified | |||
* {{kbd | key=<nowiki>drop table</nowiki>}} & insert new data | |||
Replace (overwrite) the current data | |||
* Run {{kbd | key=<nowiki>mysqldump</nowiki>}} with the option {{kbd | key=<nowiki>--replace --no-create-info</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_replace --replace]</ref><ref>[https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_no-create-info --no-create-info]</ref>: (1) {{kbd | key=<nowiki>--replace</nowiki>}}: "Write REPLACE statements rather than INSERT statements." (2) {{kbd | key=<nowiki>--no-create-info</nowiki>}}: "Do not write CREATE TABLE statements that create each dumped table." & "Do not add a DROP TABLE statement before each CREATE TABLE statement" | |||
Append the current data | |||
* Run {{kbd | key=<nowiki>mysqldump</nowiki>}} with the option {{kbd | key=<nowiki>--insert-ignore --no-create-info</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_insert-ignore --insert-ignore]</ref>: (1) {{kbd | key=<nowiki>mysqldump</nowiki>}} with the option {{kbd | key=<nowiki>--insert-ignore</nowiki>}}: "Write INSERT IGNORE statements rather than INSERT statements." | |||
Schema only | |||
* [https://www.electrictoolbox.com/mysqldump-schema-only/ Use mysqldump to get the schema only - Electric Toolbox] | |||
==== Exporting entire data ==== | |||
'''method1''': {{Gd}} export the compressed *.sql file (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysqldump<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><ref>[https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html MySQL :: MySQL 5.1 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program]</ref><ref>[http://stackoverflow.com/questions/5666784/how-can-i-slow-down-a-mysql-dump-as-to-not-affect-current-load-on-the-server backup - How can I slow down a MySQL dump as to not affect current load on the server? - Stack Overflow]</ref>: | |||
# (optional) install {{kbd | key =pv}} if the {{kbd | key =pv}} was not installed | |||
# (for InnoDB tables){{kbd | key = <nowiki>mysqldump -h localhost -u username -p --force --single-transaction --default-character-set=utf8 DATA_BASE_NAME | pv | gzip -c > database.sql.gz</nowiki>}} <ref>[http://journal.andrewloe.com/2009/05/12/pv-gzip-mysql/ pv + gzip + mysql — W. Andrew Loe III: Journal]</ref><ref>(if have permission to LOCK TABLES){{kbd | key = <nowiki>mysqldump -h localhost -u username -p DATA_BASE_NAME | pv | gzip -c > database.sql.gz</nowiki>}} (if have no permission to LOCK TABLES){{kbd | key = <nowiki>mysqldump -h localhost -u username -p --lock-tables=false DATA_BASE_NAME | pv | gzip -c > database.sql.gz</nowiki>}} via [http://stackoverflow.com/questions/104612/run-mysqldump-without-locking-tables mysql - Run MySQLDump without Locking Tables - Stack Overflow]</ref> and press {{kbd | key = <nowiki>Enter</nowiki> }} | |||
# ask to enter {{kbd | key = <nowiki>password</nowiki> }} and press {{kbd | key = <nowiki>Enter</nowiki> }} | |||
optional: Append the Today's date to file name | |||
* On {{Linux}}: (for InnoDB tables){{kbd | key = <nowiki>mysqldump -h localhost -u username -p --force --single-transaction --default-character-set=utf8 DATA_BASE_NAME | pv | gzip -c > database.sql.$(date +"%y%m%d").gz</nowiki>}} | |||
'''method2''': Bulk export individual tables into the multiple compressed *.sql files | |||
<pre> | <pre> | ||
mysqldump -h localhost -u username -pPASSWORD --force --single-transaction DB_NAME TABLE_1 | pv | gzip -c > table_1.sql.gz | mysqldump -h localhost -u username -pPASSWORD --force --single-transaction --default-character-set=utf8 DB_NAME TABLE_1 | pv | gzip -c > table_1.sql.gz | ||
mysqldump -h localhost -u username -pPASSWORD --force --single-transaction DB_NAME TABLE_2 | pv | gzip -c > table_2.sql.gz | mysqldump -h localhost -u username -pPASSWORD --force --single-transaction --default-character-set=utf8 DB_NAME TABLE_2 | pv | gzip -c > table_2.sql.gz | ||
</pre> | </pre> | ||
note: the last line of above part is '''return symbol'''. The last command will not executed automatically without the return symbol. | note: the last line of above part is '''return symbol'''. The last command will not be executed automatically without the return symbol. | ||
'''method3''': export the *.sql file. It will show a progress bar and estimated time to complete the mysqldump <ref>[http://dubbs.github.io/blog/2013/09/05/mysql-import-slash-export-progress-bar/ MySQL Import/Export Progress Bar - Kevin Warrington]</ref>: | |||
# (optional if the {{kbd | key =pv}} was not installed) {{kbd | key =yum install pv}} for RHEL / CentOS / SL / Fedora Linux | |||
# (for InnoDB tables){{kbd | key = <nowiki>mysqldump -h localhost -u username -p --force --single-transaction --default-character-set=utf8 DATA_BASE_NAME | pv > database.sql</nowiki>}} and press {{kbd | key = <nowiki>Enter</nowiki> }} | |||
# enter {{kbd | key = <nowiki>password</nowiki> }} and press {{kbd | key = <nowiki>Enter</nowiki> }} | |||
'''method4''': export the *.sql file | |||
# {{kbd | key = <nowiki>mysqldump -h localhost -u username -p --single-transaction --default-character-set=utf8 DATA-BASE-NAME > /path/to/database.sql </nowiki>}} | |||
'''method5''': Using phpmyadmin to export the database. {{exclaim}} But I found the number rows of table after imported was not the same with the number rows of original table once. | |||
==== Exporting selected data ==== | |||
[http://stackoverflow.com/questions/17397661/append-mysql-dump-to-a-table mysqldump - Append MYSQL dump to a table - Stack Overflow] {{access | date = 2016-06-29}} | |||
* | |||
==== Potential failure issue ==== | |||
* insufficient hard-disk space | |||
=== 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 | ||
* Import *.sql file: | 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 if the 7Zip was not installed) {{kbd | key =yum install p7zip}} for RHEL / CentOS / SL / Fedora Linux | |||
*# enter {{kbd | key = <nowiki>password</nowiki> }} and press {{kbd | key = <nowiki>Enter</nowiki> }} | |||
'''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: | |||
# 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. | |||
# {{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> }} | |||
# enter {{kbd | key = <nowiki>password</nowiki> }} and press {{kbd | key = <nowiki>Enter</nowiki> }} The console window will show ETA (Estimated Time of Arrival) | |||
'''method 2''': Import *.sql file: | |||
# (optional if the .sql file was compressed) {{kbd | key =unzip data.zip}} or {{kbd | key =gzip -d data.gz}} if the file was compressed by gzip | |||
# {{kbd | key = <nowiki>mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME < data.sql</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> }} | |||
'''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>: | |||
# 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> }} | |||
# enter {{kbd | key = <nowiki>password</nowiki> }} and press {{kbd | key = <nowiki>Enter</nowiki> }} The console window will show ETA (Estimated Time of Arrival) | |||
'''method 4''': Import ZIP file to MySql directly: | |||
# 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> }} | |||
# enter {{kbd | key = <nowiki>password</nowiki> }} and press {{kbd | key = <nowiki>Enter</nowiki> }} | |||
* | === Synchronizing databases === | ||
* | Synchronizing two MySQL databases: | ||
* | * [http://www.heidisql.com/ HeidiSQL - MySQL, MSSQL and PostgreSQL made easy] v.9.4.0.5125 for {{Win}}, {{Mac}} & {{Linux}} | ||
* | ** Steps: Choose the database or table --> Export database as SQL --> Output: Select the mysql server connection | ||
** Scheduler: Not available<ref>[http://www.heidisql.com/forum.php?t=22040 Schedule backups/exports]</ref> {{exclaim}}. Manually synchronizing databases between two different servers. | |||
* [https://dev.mysql.com/doc/refman/5.7/en/replication.html Replication] | |||
** Steps: | |||
** Scheduler: Auto {{Gd}}. | |||
* ''$'' [https://www.webyog.com/product/sqlyog SQLyog] v.12.4.0 for {{Win}}. | |||
** Steps: Menu --> Powertools --> Database Synchronization Wizard | |||
** 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 for Windows] | |||
** 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: | |||
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>: | |||
* ''$'' [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 === | ||
Line 51: | Line 125: | ||
# {{kbd | key=mysql -h HOST -u ACCOUNT -p }} | # {{kbd | key=mysql -h HOST -u ACCOUNT -p }} | ||
# And keyin the PASSWORD of your MySQL ACCOUNT. The first character will be changed to ''mysql>'' from ''$'' or ''#'' | # And keyin the PASSWORD of your MySQL ACCOUNT. The first character will be changed to ''mysql>'' from ''$'' or ''#'' | ||
# mysql> {{kbd | key=SHOW FULL PROCESSLIST\G}} to show the process list | # mysql> | ||
# mysql> {{kbd | key=kill 101;}} to kill the process with Id number: 101 | #* {{kbd | key=SHOW FULL PROCESSLIST\G}} to show the process list | ||
#* {{kbd | key=SHOW PROCESSLIST\G}} to show the brief process list | |||
# mysql> {{kbd | key=kill 101;}} to kill the process with Id number: {{kbd | key=101}} | |||
# mysql> {{kbd | key=exit;}} to leave the MySQL command. | # mysql> {{kbd | key=exit;}} to leave the MySQL command. | ||
=== Troubleshooting of MySQL errors === | |||
[[Troubleshooting of MySQL errors]] | |||
=== | === Further reading === | ||
* [https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html MySQL :: MySQL 8.0 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program] | |||
* [http://dev.mysql.com/doc/refman/5.7/en/error-handling.html MySQL :: MySQL 5.7 Reference Manual :: B Errors, Error Codes, and Common Problems] | |||
* [http://eosrei.net/articles/2013/03/forcing-mysqldump-always-be-nice-cpu-and-io Forcing a mysqldump to always be nice to CPU and I/O | eosrei.net]: using [http://www.computerhope.com/unix/unice.htm nice] or [http://www.tutorialspoint.com/unix_commands/ionice.htm ionice] commands {{exclaim}} | |||
** ionice command is not exists in cygwin | |||
* [http://www.dbastuff.net/2014/09/mysql-dump-and-partitions.html DBAStuff: Mysql Dump and partitions] {{access | date = 2016-01-01}} | |||
* [https://dev.mysql.com/doc/refman/5.5/en/backup-methods.html MySQL :: MySQL 5.5 Reference Manual :: 7.2 Database Backup Methods] {{access | date = 2016-06-29}} | |||
* [[SQL syntax debug]] | |||
* [https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server SQL Modes] | |||
* [https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html MySQL :: MySQL 8.0 Reference Manual :: 4.5.1.1 mysql Client Options] | |||
=== | === References === | ||
<references/> | <references/> | ||
{{Template:Troubleshooting}} | |||
[[Category:MySQL]] | [[Category:MySQL]] | ||
[[Category:Data Science]] |
Latest revision as of 10:34, 10 July 2023
MySQL commands and troubleshooting of MySQL errors.
Preparation[edit]
install MySQL database client apps contains mysql & mysqldump commands
- Linux :
- Win : Install (1) Cygwin, (2) search mysql & install MySQL database clients apps package
- Mac :
(optional) install the Pipe Viewer (pv) package
- Linux :
- How to Enable RPMForge Repository in RHEL/CentOS 7.x/6.x/5.x/4.x
- (1)yum install pv for RHEL / CentOS / SL / Fedora Linux[2]; (2) apt-get install pv for Debian / Ubuntu[3];
- Win : Install (1) Cygwin, (2) search pv & install pv package
- Mac : Install (1) brew install pv to get the latest version of pv package[4] and (2) MySQL database clients from XAMPP. Path of mysql is: /Applications/xampp/xamppfiles/bin/mysql
create the db user
- create the db user
- grant the minimum permission: SELECT, LOCK TABLES to the db user. SQL query: GRANT SELECT, LOCK TABLES ON `DATA\_BASE\_NAME`.* TO 'dbuser'@'localhost';[5]
- the db user will be able to execute the mysqldump command
Exporting data of database/table into MySql sql file[edit]
mysqldump Options[edit]
Default option without any options specified
- drop table & insert new data
Replace (overwrite) the current data
- Run mysqldump with the option --replace --no-create-info[6][7]: (1) --replace: "Write REPLACE statements rather than INSERT statements." (2) --no-create-info: "Do not write CREATE TABLE statements that create each dumped table." & "Do not add a DROP TABLE statement before each CREATE TABLE statement"
Append the current data
- Run mysqldump with the option --insert-ignore --no-create-info[8]: (1) mysqldump with the option --insert-ignore: "Write INSERT IGNORE statements rather than INSERT statements."
Schema only
Exporting entire data[edit]
method1: export the compressed *.sql file (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysqldump[9][10][11]:
- (optional) install pv if the pv was not installed
- (for InnoDB tables)mysqldump -h localhost -u username -p --force --single-transaction --default-character-set=utf8 DATA_BASE_NAME | pv | gzip -c > database.sql.gz [12][13] and press Enter
- ask to enter password and press Enter
optional: Append the Today's date to file name
- On Linux : (for InnoDB tables)mysqldump -h localhost -u username -p --force --single-transaction --default-character-set=utf8 DATA_BASE_NAME | pv | gzip -c > database.sql.$(date +"%y%m%d").gz
method2: Bulk export individual tables into the multiple compressed *.sql files
mysqldump -h localhost -u username -pPASSWORD --force --single-transaction --default-character-set=utf8 DB_NAME TABLE_1 | pv | gzip -c > table_1.sql.gz mysqldump -h localhost -u username -pPASSWORD --force --single-transaction --default-character-set=utf8 DB_NAME TABLE_2 | pv | gzip -c > table_2.sql.gz
note: the last line of above part is return symbol. The last command will not be executed automatically without the return symbol.
method3: export the *.sql file. It will show a progress bar and estimated time to complete the mysqldump [14]:
- (optional if the pv was not installed) yum install pv for RHEL / CentOS / SL / Fedora Linux
- (for InnoDB tables)mysqldump -h localhost -u username -p --force --single-transaction --default-character-set=utf8 DATA_BASE_NAME | pv > database.sql and press Enter
- enter password and press Enter
method4: export the *.sql file
- mysqldump -h localhost -u username -p --single-transaction --default-character-set=utf8 DATA-BASE-NAME > /path/to/database.sql
method5: Using phpmyadmin to export the database. But I found the number rows of table after imported was not the same with the number rows of original table once.
Exporting selected data[edit]
mysqldump - Append MYSQL dump to a table - Stack Overflow [Last visited: 2016-06-29]
Potential failure issue[edit]
- insufficient hard-disk space
Importing data from MySql sql file[edit]
Notice: existing database will be overwritten
Optional
- (optional) install pv if the pv was not installed for RHEL / CentOS / SL / Fedora Linux or brew install pv for Mac [15]
- (optional if the 7Zip was not installed) yum install p7zip for RHEL / CentOS / SL / Fedora Linux
method 1: 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:
- Install pv
- Check if the file is gzip format (test the compressed file integrity) ex: (1) gunzip -t database.sql.gz If not, it will return the message "gzip: database.sql.gz: not in gzip format." (2) Using WinMD5 Free for Win or md5sum command for Linux to obtain MD5 checksum of files.
- pv database.sql.gz | gunzip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME [16] and press Enter
- enter password and press Enter The console window will show ETA (Estimated Time of Arrival)
method 2: Import *.sql file:
- (optional if the .sql file was compressed) unzip data.zip or gzip -d data.gz if the file was compressed by gzip
- mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME < data.sql [17] and press Enter
- enter password and press Enter
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[18]:
- Install pv
- pv data.sql | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME [19] and press Enter
- enter password and press Enter The console window will show ETA (Estimated Time of Arrival)
method 4: Import ZIP file to MySql directly:
- Install the 7Zip
- 7za x -so data.zip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME [20][21] and press Enter
- enter password and press Enter
Synchronizing databases[edit]
Synchronizing two MySQL databases:
- HeidiSQL - MySQL, MSSQL and PostgreSQL made easy v.9.4.0.5125 for Win , Mac & Linux
- Steps: Choose the database or table --> Export database as SQL --> Output: Select the mysql server connection
- Scheduler: Not available[22] . Manually synchronizing databases between two different servers.
- Replication
- Steps:
- Scheduler: Auto .
- $ SQLyog v.12.4.0 for Win .
- Steps: Menu --> Powertools --> Database Synchronization Wizard
- Scheduler: Available . Save the job file & Schedule it using Windows scheduler.
- $ MySQL GUI Tool - MariaDB and MySQL Front End Client for Windows
- 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:
- Steps:
Transfer date from MS SQL server to MySQL or MariaDB server[23]:
- $ 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[edit]
Show the MySQL process list & kill the process (especial for SLOW query command)[24]
- mysql -h HOST -u ACCOUNT -p
- And keyin the PASSWORD of your MySQL ACCOUNT. The first character will be changed to mysql> from $ or #
- mysql>
- SHOW FULL PROCESSLIST\G to show the process list
- SHOW PROCESSLIST\G to show the brief process list
- mysql> kill 101; to kill the process with Id number: 101
- mysql> exit; to leave the MySQL command.
Troubleshooting of MySQL errors[edit]
Troubleshooting of MySQL errors
Further reading[edit]
- MySQL :: MySQL 8.0 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
- MySQL :: MySQL 5.7 Reference Manual :: B Errors, Error Codes, and Common Problems
- Forcing a mysqldump to always be nice to CPU and I/O | eosrei.net: using nice or ionice commands
- ionice command is not exists in cygwin
- DBAStuff: Mysql Dump and partitions [Last visited: 2016-01-01]
- MySQL :: MySQL 5.5 Reference Manual :: 7.2 Database Backup Methods [Last visited: 2016-06-29]
- SQL syntax debug
- MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server SQL Modes
- MySQL :: MySQL 8.0 Reference Manual :: 4.5.1.1 mysql Client Options
References[edit]
- ↑ How to access the MySQL CLI With MAMP - DEV Community
- ↑ Linux / Unix pv Command: Monitor Progress of Data Sent Via a Pipe
- ↑ ivarch.com: Pipe Viewer
- ↑ Pipe Viewer (pv) in Mac OSX
- ↑ security - Minimum permissions for a user to perform a mysqldump? - Server Fault
- ↑ --replace
- ↑ --no-create-info
- ↑ --insert-ignore
- ↑ mysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange
- ↑ MySQL :: MySQL 5.1 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
- ↑ backup - How can I slow down a MySQL dump as to not affect current load on the server? - Stack Overflow
- ↑ pv + gzip + mysql — W. Andrew Loe III: Journal
- ↑ (if have permission to LOCK TABLES)mysqldump -h localhost -u username -p DATA_BASE_NAME | pv | gzip -c > database.sql.gz (if have no permission to LOCK TABLES)mysqldump -h localhost -u username -p --lock-tables=false DATA_BASE_NAME | pv | gzip -c > database.sql.gz via mysql - Run MySQLDump without Locking Tables - Stack Overflow
- ↑ MySQL Import/Export Progress Bar - Kevin Warrington
- ↑ pv — Homebrew Formulae
- ↑ pv + gzip + mysql — W. Andrew Loe III: Journal
- ↑ Import MySQL Dumpfile, SQL Datafile Into My Database
- ↑ mysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange
- ↑ Import MySQL Dumpfile, SQL Datafile Into My Database
- ↑ database - Importing zipped files in Mysql using command line - Stack Overflow
- ↑ Install 7Zip on CentOS 5.5
- ↑ Schedule backups/exports
- ↑ MONyog & SQLyog - MariaDB Knowledge Base
- ↑ MySQL :: MySQL 5.1 Reference Manual :: 13.7.5.31 SHOW PROCESSLIST Syntax
Troubleshooting of ...
- PHP, cUrl, Python, selenium, HTTP status code errors
- Database: SQL syntax debug, MySQL errors, MySQLTuner errors or PostgreSQL errors
- HTML/Javascript: Troubleshooting of javascript, XPath
- Software: Mediawiki, Docker, FTP problems, online conference software
- Test connectivity for the web service, Web Ping, Network problem, Web user behavior, Web scrape troubleshooting
Template