MySQL commands: Difference between revisions
Jump to navigation
Jump to search
→Preparation
| (12 intermediate revisions by the same user not shown) | |||
| 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: {{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> | |||
(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 | ||
| Line 69: | Line 71: | ||
=== 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: | ||
# | # 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 88: | ||
'''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>: | ||
# | # 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: | ||
# | # 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 108: | ||
** 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 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 server: | 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. | * ''$'' [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. | ||
| Line 113: | Line 128: | ||
#* {{kbd | key=SHOW FULL PROCESSLIST\G}} to show the process list | #* {{kbd | key=SHOW FULL PROCESSLIST\G}} to show the process list | ||
#* {{kbd | key=SHOW PROCESSLIST\G}} to show the brief 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: 101 | # 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. | ||
| Line 128: | Line 143: | ||
* [[SQL syntax debug]] | * [[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/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 === | ||