Linux Learning Note: mysqldump

Mysqldump is a client program for exporting data that is suitable for development,DBA testing, and so on. In its man help, it is mentioned thatmysqldump is doing a logical backup, and the resulting content is a bunch of SQL statements. It can also be exported as csv, text file (other split symbol),XML format.

Mysqldump requires at least a select,Show view,trigger,lock tables permission (provided that it is not used - Single-transaction option).

When you re -import The dump file, you need to have the corresponding permissions for all the SQL statements in the dump file (for example , create Database,create Table , etc.)

If the database engine you are using is MyISAM, you can use mysqlhotcopy.

for backups of large amounts of data, Mysqldump is not a fast and scalable backup solution. Because although it takes some time to export, the import is slower because it involves disk IO, rebuilding the index, and so on. You should consider using physical backups: If you are a hybrid engine database, you should use Mysqlbackup toprovide the best performance and minimal downtime, and enterprises should consider using it for backup; If you have only MyISAM engines, you should use Mysqlhotcopy.

Mysqldump uses memory to do the caching of the exported results, but for a large amount of data, it consumes more memory, can be paired with the-quick option to avoid caching, and if you must use the cache, you can use the-skip-opt option.

Mysqldump 's wording:

Mysqldump [ Options ] db_name [tbl_name ...] to export table data.

Mysqldump [ option ]–databasesdb_name .... This notation exports the entire database.

Mysqldump [ option ]–all-databases This notation exports all databases.

Mysqldump does not export the information_schema or performance_schema database by default , and if necessary, it needs to be explicitly indicated and used -- Skip-lock-tables option.

in the Before MySQL 5.5.25 ,mysqldump does not export general_log or slow_query_log; starting with MySQL 5.5.25, the mysqldump export contains statements that create these tables so that when the data is recovered, the tables can be created, but the contents of the table are not restored.

Mysqldump also does not export the MySQL cluster's ndbinfoinformation database

Mysqldump Some abbreviations for options

· --opt represents --add-drop-table,--add-locks,--create-options,--disable-keys,--Extended-insert, --lock-tables,--quick,--set-charset . and -opt is turned on by default.

· --compact represents --skip-add-drop-table,--skip-add-locks,--skip-comments,--Skip-disable-keys, --skip-set-charset

If you want to turn off some of the features within an option group, you can specify the command group –skip-xxx , for example

--opt–skip-quick represents the OPT option group, but turns off the quick feature.

--skip-opt–quick represents closing the entire opt option group, but turning on the quick function

More options Refer to man mysqldump

Linux Learning Note: mysqldump