MySQL commands: Difference between revisions

=== Importing data from MySql sql file ===

{{exclaim}} Notice: existing database will be overwritten

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

# 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> }}

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

# {{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)