MySQL commands: Difference between revisions

Jump to navigation Jump to search

MySQL commands (edit)

Revision as of 15:48, 3 August 2026

2,846 bytes added ,  Yesterday at 15:48
m
no edit summary
mNo edit summary
 
(3 intermediate revisions by the same user not shown)
Line 75: Line 75:
# (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
# (optional if the 7Zip was not installed) {{kbd | key =yum install p7zip}} for RHEL / CentOS / SL / Fedora Linux
 
'''method 1''': Import *.sql file:  
'''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
# (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> }}
# {{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> }}
# enter {{kbd | key =  <nowiki>password</nowiki> }} and press {{kbd | key =  <nowiki>Enter</nowiki> }}
'''method 2''': Import *.sql file with {{kbd | key =--binary-mode}} (useful when the dump contains complex JSON string, binary data or BLOB fields, to avoid "\r\n" being misinterpreted and causing corrupted imports):
# (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 --binary-mode --force DATA_BASE_NAME < data.sql</nowiki>}} <ref>[https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html#option_mysql_binary-mode mysql Command-Line Client Options — MySQL Reference Manual]</ref> and press {{kbd | key =  <nowiki>Enter</nowiki> }}
# enter {{kbd | key =  <nowiki>password</nowiki> }} and press {{kbd | key =  <nowiki>Enter</nowiki> }} {{kbd | key =--binary-mode}} disables all client-side conversions of {{kbd | key =\r\n}} and treats {{kbd | key =\0}} as a normal character, which is recommended whenever the dump was produced with {{kbd | key =mysqldump --hex-blob}} or otherwise contains binary/BLOB content


'''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>:  
Line 92: Line 90:
# 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''': Same as method 3, but combined with {{kbd | key =--binary-mode}} to safely handle binary/BLOB content while showing a progress bar:
# Install {{kbd | key =pv}}
# {{kbd | key =  <nowiki>pv data.sql | mysql -u username -p -h localhost --default_character_set utf8 --binary-mode --force DATA_BASE_NAME</nowiki>}} 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), and {{kbd | key =--binary-mode}} disables all client-side conversions of {{kbd | key =\r\n}} and treats {{kbd | key =\0}} as a normal character, which is recommended whenever the dump was produced with {{kbd | key =mysqldump --hex-blob}} or otherwise contains binary/BLOB content
 
'''method 5''': {{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 6''': Same as method 5, but combined with {{kbd | key =--binary-mode}} to safely handle binary/BLOB content while showing a progress bar:
# 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 -c | mysql -u username -p -h localhost --default_character_set utf8 --binary-mode --force DATA_BASE_NAME</nowiki>}} 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), and {{kbd | key =--binary-mode}} disables all client-side conversions of {{kbd | key =\r\n}} and treats {{kbd | key =\0}} as a normal character, which is recommended whenever the dump was produced with {{kbd | key =mysqldump --hex-blob}} or otherwise contains binary/BLOB content
 
'''method 7''': Import ZIP file to MySql directly:
# Install the 7Zip
# 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> }}
Line 119: Line 134:


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 ===
Line 150: Line 164:




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


[[Category:MySQL]]
[[Category: MySQL]]
[[Category:Data Science]]
[[Category: Data Science]]
[[Category: Revised with LLMs]]
Retrieved from "https://wiki.planetoid.info/index.php/Special:MobileDiff/25521...26425"

Navigation menu