15,013
edits
MySQL commands: Difference between revisions
Jump to navigation
Jump to search
→Exporting data into MySql sql file
| Line 19: | Line 19: | ||
=== Exporting data into MySql sql file === | === Exporting data into MySql sql file === | ||
'''method1''': {{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><ref>[http://stackoverflow.com/questions/5666784/how-can-i-slow-down-a-mysql-dump-as-to-not-affect-current-load-on-the-server backup - How can I slow down a MySQL dump as to not affect current load on the server? - Stack Overflow]</ref>: | |||
# (optional) install {{kbd | key =pv}} if the {{kbd | key =pv}} was not installed | |||
# (for InnoDB tables){{kbd | key = <nowiki>mysqldump -h localhost -u username -p --force --single-transaction --default-character-set=utf8 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> }} | |||
'''method2''': Bulk export multiple tables into the compressed *.sql files | |||
<pre> | <pre> | ||
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 --default-character-set=utf8 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 | mysqldump -h localhost -u username -pPASSWORD --force --single-transaction --default-character-set=utf8 DB_NAME TABLE_2 | pv | gzip -c > table_2.sql.gz | ||
</pre> | </pre> | ||
note: the last line of above part is '''return symbol'''. The last command will not executed automatically without the return symbol. | note: the last line of above part is '''return symbol'''. The last command will not executed automatically without the return symbol. | ||
'''method3''': export the *.sql file. It will show a progress bar and estimated time to complete the mysqldump <ref>[http://dubbs.github.io/blog/2013/09/05/mysql-import-slash-export-progress-bar/ MySQL Import/Export Progress Bar - Kevin Warrington]</ref>: | |||
# (optional if the {{kbd | key =pv}} was not installed) {{kbd | key =yum install pv}} for RHEL / CentOS / SL / Fedora Linux | |||
# (for InnoDB tables){{kbd | key = <nowiki>mysqldump -h localhost -u username -p --force --single-transaction --default-character-set=utf8 DATA_BASE_NAME | pv > database.sql</nowiki>}} and press {{kbd | key = <nowiki>Enter</nowiki> }} | |||
# enter {{kbd | key = <nowiki>password</nowiki> }} and press {{kbd | key = <nowiki>Enter</nowiki> }} | |||
'''method4''': export the *.sql file | |||
# {{kbd | key = <nowiki>mysqldump -h localhost -u username -p --single-transaction --default-character-set=utf8 DATA-BASE-NAME > /path/to/database.sql </nowiki>}} | |||
=== Importing data from MySql sql file === | === Importing data from MySql sql file === | ||