MySQL commands: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
Line 2: Line 2:
* {{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>:  
* {{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>:  
*# (optional if the {{kbd | key =pv}} was not installed)  
*# (optional if the {{kbd | key =pv}} was not installed)  
*#* {{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>;  
*#* {{Linux}}: (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>;  
*#* {{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) pv package and (3) MySQL database clients apps package
*#* Install (1) [https://www.cygwin.com/ Cygwin], (2) pv package and (3) MySQL database clients apps package for {{Win}}
*#* {{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  
*#* 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] for {{Mac}}. Path of mysql is: /Applications/xampp/xamppfiles/bin/mysql  
*# (for InnoDB tables){{kbd | key =  <nowiki>mysqldump -h localhost -u username -p --force --single-transaction 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> }}
*# (for InnoDB tables){{kbd | key =  <nowiki>mysqldump -h localhost -u username -p --force --single-transaction 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> }}
*# ask to enter {{kbd | key =  <nowiki>password</nowiki> }} and press {{kbd | key =  <nowiki>Enter</nowiki> }}

Revision as of 09:30, 15 December 2015

Exporting data into MySql sql file

  • Good.gif export the compressed *.sql file (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysqldump[1][2]:
    1. (optional if the pv was not installed)
      • Linux Os linux.png : (1)yum install pv for RHEL / CentOS / SL / Fedora Linux[3]; (2) apt-get install pv for Debian / Ubuntu[4];
      • Win Os windows.png : Install (1) Cygwin, (2) pv package and (3) MySQL database clients apps package
      • Mac icon_os_mac.png : Install (1) brew install pv to get the latest version of pv package[5] and (2) MySQL database clients from XAMPP. Path of mysql is: /Applications/xampp/xamppfiles/bin/mysql
    2. (for InnoDB tables)mysqldump -h localhost -u username -p --force --single-transaction DATA_BASE_NAME | pv | gzip -c > database.sql.gz [6][7] and press Enter
    3. ask to enter password and press Enter


  • Bulk export multiple tables into the compressed *.sql files
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 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 executed automatically without the return symbol.

  • export the *.sql file. It will show a progress bar and estimated time to complete the mysqldump [8]:
    1. (optional if the pv was not installed) yum install pv for RHEL / CentOS / SL / Fedora Linux
    2. (for InnoDB tables)mysqldump -h localhost -u username -p --force --single-transaction DATA_BASE_NAME | pv > database.sql and press Enter
    3. enter password and press Enter
  • export the *.sql file
    1. mysqldump -h localhost -u username -p DATA-BASE-NAME > /path/to/database.sql

Importing data from MySql sql file

Icon_exclaim.gif Notice: existing database will be overwritten

  • Good.gif 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:
    1. (optional if the pv was not installed) yum install pv for RHEL / CentOS / SL / Fedora Linux
    2. pv database.sql.gz | gunzip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME [9] and press Enter
    3. enter password and press Enter The console window will show ETA (Estimated Time of Arrival)
  • Import *.sql file:
    1. (optional if the .sql file was compressed) unzip data.zip or gzip -d data.gz if the file was compressed by gzip
    2. mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME < data.sql [10] and press Enter
    3. enter password and press Enter
  • Import the *.sql file (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysql command[11]:
    1. (optional if the pv was not installed) yum install pv for RHEL / CentOS / SL / Fedora Linux
    2. pv data.sql | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME [12] and press Enter
    3. enter password and press Enter The console window will show ETA (Estimated Time of Arrival)
  • Import ZIP file to MySql directly:
    1. (optional if the 7Zip was not installed) yum install p7zip
    2. 7za x -so data.zip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME [13][14] and press Enter
    3. enter password and press Enter

Show the MySQL process list & kill the process

Show the MySQL process list & kill the process (especial for SLOW query command)[15]

  1. mysql -h HOST -u ACCOUNT -p
  2. And keyin the PASSWORD of your MySQL ACCOUNT. The first character will be changed to mysql> from $ or #
  3. mysql> SHOW FULL PROCESSLIST\G to show the process list
  4. mysql> kill 101; to kill the process with Id number: 101
  5. mysql> exit; to leave the MySQL command.


Troubleshooting of MySQL command

command not found: mysqldump

  1. locate the mysqldump command
    • sudo find / -iname mysqldump for Mac icon_os_mac.png or Linux Os linux.png
    • And find /Applications/XAMPP/xamppfiles/bin/mysqldump from XAMPP for Mac icon_os_mac.png
  2. input the complete path of mysqldump command
    • old command which caused error mysqldump -h 127.0.0.1 -u root -p --force --single-transaction DATABASE_NAME | pv | gzip -c > DATABASE_NAME.sql.gz
    • new command /Applications/XAMPP/xamppfiles/bin/mysqldump -h 127.0.0.1 -u root -p --force --single-transaction DATABASE_NAME | pv | gzip -c > DATABASE_NAME.sql.gz

references

  1. ↑ mysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange
  2. ↑ MySQL :: MySQL 5.1 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
  3. ↑ Linux / Unix pv Command: Monitor Progress of Data Sent Via a Pipe
  4. ↑ ivarch.com: Pipe Viewer
  5. ↑ Pipe Viewer (pv) in Mac OSX
  6. ↑ pv + gzip + mysql — W. Andrew Loe III: Journal
  7. ↑ (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
  8. ↑ MySQL Import/Export Progress Bar - Kevin Warrington
  9. ↑ pv + gzip + mysql — W. Andrew Loe III: Journal
  10. ↑ Import MySQL Dumpfile, SQL Datafile Into My Database
  11. ↑ mysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange
  12. ↑ Import MySQL Dumpfile, SQL Datafile Into My Database
  13. ↑ database - Importing zipped files in Mysql using command line - Stack Overflow
  14. ↑ Install 7Zip on CentOS 5.5
  15. ↑ MySQL :: MySQL 5.1 Reference Manual :: 13.7.5.31 SHOW PROCESSLIST Syntax

further reading

Retrieved from "https://wiki.planetoid.info/index.php?title=MySQL_commands&oldid=15924"