15,063
edits
MySQL commands: Difference between revisions
Jump to navigation
Jump to search
→Exporting data of database/table into MySql sql file
m (→references) |
|||
| Line 20: | Line 20: | ||
=== Exporting data of database/table into MySql sql file === | === Exporting data of database/table into MySql sql file === | ||
==== mysqldump Options ==== | |||
Default option without any options specified | |||
* {{kbd | key=<nowiki>drop table</nowiki>}} & insert new data | |||
Replace / overwrite the current data | |||
* Run {{kbd | key=<nowiki>mysqldump</nowiki>}} with the option {{kbd | key=<nowiki>--replace</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_replace MySQL :: MySQL 8.0 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program]</ref> "Write REPLACE statements rather than INSERT statements." | |||
* Manually remove the {{kbd | key=<nowiki>drop table</nowiki>}} & {{kbd | key=<nowiki>create table</nowiki>}} syntax in the sql file. | |||
Append the current data | |||
* Run {{kbd | key=<nowiki>mysqldump</nowiki>}} with the option {{kbd | key=<nowiki>--insert-ignore</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_insert-ignore MySQL :: MySQL 8.0 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program]</ref> "Write INSERT IGNORE statements rather than INSERT statements." | |||
* Manually remove the {{kbd | key=<nowiki>drop table</nowiki>}} & {{kbd | key=<nowiki>create table</nowiki>}} syntax in the sql file. | |||
==== Exporting entire data ==== | ==== Exporting entire data ==== | ||
'''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>: | '''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>: | ||