MySQL commands: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
(6 intermediate revisions by the same user not shown)
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>
# (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:  
# (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 86:


'''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)


'''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> }}
Line 102: 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 ->
*** 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:


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>:  

Revision as of 15:55, 6 October 2021

MySQL commands and troubleshooting of MySQL errors.

Preparation

install MySQL database client apps contains mysql & mysqldump commands

  • Linux Os linux.png :
  • Win Os windows.png : Install (1) Cygwin, (2) search mysql & install MySQL database clients apps package
  • Mac icon_os_mac.png : Install XAMPP. Path of mysql executable file is: /Applications/xampp/xamppfiles/bin/mysql

(optional) install the Pipe Viewer (pv) package

create the db user

  1. create the db user
  2. grant the minimum permission: SELECT, LOCK TABLES to the db user. SQL query: GRANT SELECT, LOCK TABLES ON `DATA\_BASE\_NAME`.* TO 'dbuser'@'localhost';[4]
  3. 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

  • drop table & insert new data

Replace (overwrite) the current data

  • Run mysqldump with the option --replace --no-create-info[5][6]: (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[7]: (1) mysqldump with the option --insert-ignore: "Write INSERT IGNORE statements rather than INSERT statements."

Schema only

Exporting entire data

method1: 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[8][9][10]:

  1. (optional) install pv if the pv was not installed
  2. (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 [11][12] and press Enter
  3. ask to enter password and press Enter

optional: Append the Today's date to file name

  • On Linux Os linux.png : (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 [13]:

  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 --default-character-set=utf8 DATA_BASE_NAME | pv > database.sql and press Enter
  3. enter password and press Enter

method4: export the *.sql file

  1. 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. Icon_exclaim.gif 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

mysqldump - Append MYSQL dump to a table - Stack Overflow [Last visited: 2016-06-29]


Potential failure issue

  • insufficient hard-disk space

Importing data from MySql sql file

Icon_exclaim.gif Notice: existing database will be overwritten

Optional

  1. (optional) install pv if the pv was not installed for RHEL / CentOS / SL / Fedora Linux or brew install pv for Mac icon_os_mac.png [14]
  2. (optional if the 7Zip was not installed) yum install p7zip for RHEL / CentOS / SL / Fedora Linux

method 1: 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. Install pv
  2. 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 Os windows.png or md5sum command for Linux Os linux.png to obtain MD5 checksum of files.
  3. pv database.sql.gz | gunzip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME [15] and press Enter
  4. enter password and press Enter The console window will show ETA (Estimated Time of Arrival)

method 2: 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 [16] and press Enter
  3. 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[17]:

  1. Install pv
  2. pv data.sql | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME [18] and press Enter
  3. enter password and press Enter The console window will show ETA (Estimated Time of Arrival)

method 4: Import ZIP file to MySql directly:

  1. Install the 7Zip
  2. 7za x -so data.zip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME [19][20] and press Enter
  3. enter password and press Enter

Synchronizing databases

Synchronizing two MySQL databases:

  • HeidiSQL - MySQL, MSSQL and PostgreSQL made easy v.9.4.0.5125 for Win Os windows.png , Mac icon_os_mac.png & Linux Os linux.png
    • Steps: Choose the database or table --> Export database as SQL --> Output: Select the mysql server connection
    • Scheduler: Not available[21] Icon_exclaim.gif. Manually synchronizing databases between two different servers.
  • Replication
    • Steps:
    • Scheduler: Auto Good.gif.
  • $ SQLyog v.12.4.0 for Win Os windows.png .
    • Steps: Menu --> Powertools --> Database Synchronization Wizard
    • Scheduler: Available Good.gif. 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:


Transfer date from MS SQL server to MySQL or MariaDB server[22]:

  • $ SQLyog for Win Os windows.png . 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 (especial for SLOW query command)[23]

  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
    • SHOW PROCESSLIST\G to show the brief 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 errors

Troubleshooting of MySQL errors

Further reading

References

  1. Linux / Unix pv Command: Monitor Progress of Data Sent Via a Pipe
  2. ivarch.com: Pipe Viewer
  3. Pipe Viewer (pv) in Mac OSX
  4. security - Minimum permissions for a user to perform a mysqldump? - Server Fault
  5. --replace
  6. --no-create-info
  7. --insert-ignore
  8. mysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange
  9. MySQL :: MySQL 5.1 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
  10. backup - How can I slow down a MySQL dump as to not affect current load on the server? - Stack Overflow
  11. pv + gzip + mysql — W. Andrew Loe III: Journal
  12. (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
  13. MySQL Import/Export Progress Bar - Kevin Warrington
  14. pv — Homebrew Formulae
  15. pv + gzip + mysql — W. Andrew Loe III: Journal
  16. Import MySQL Dumpfile, SQL Datafile Into My Database
  17. mysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange
  18. Import MySQL Dumpfile, SQL Datafile Into My Database
  19. database - Importing zipped files in Mysql using command line - Stack Overflow
  20. Install 7Zip on CentOS 5.5
  21. Schedule backups/exports
  22. MONyog & SQLyog - MariaDB Knowledge Base
  23. MySQL :: MySQL 5.1 Reference Manual :: 13.7.5.31 SHOW PROCESSLIST Syntax


Troubleshooting of ...

Template