MySQL backup command mysqldump parameters and examples<\/p>\n
1. Grammar options
\n-h, –host=name
\nCPU name<\/p>\n
-P[ port_num], –port=port_num
\nThe TCP \/ IP port number used to connect to the MySQL server<\/p>\n
–master-data
\nThis option adds the location and filename of the binlog to the output. If it is equal to 1, it will be printed as a CHANGE MASTERcommand; if equal to 2, the comment prefix will be added. And this option will automatically open –lock-all-tables, unless at the same time set up –single-transaction(in this case, the global read lock will only add a small amount of time to start the dump, do not forget to read –single-transactionthe part). In all cases, the actions in all logs occur at the exact moment of export. This option will automatically shut down –lock-tables.<\/p>\n
-x, –lock-all-tables
\nLock all the tables in all the libraries. This is achieved by holding a global read lock throughout the dump. Will automatically shut down –single-transactionand –lock-tables.<\/p>\n
–single-transaction
\nThe exported data is a consistent snapshot by encapsulating the export operation within a single transaction. Works only if the table uses a storage engine that supports MVCC (currently only InnoDB); other engines can not guarantee that the export is consistent. When the export –single-transactionoption is enabled , to make sure that the export file is valid (the correct table data and binary log location), make sure that no other connections execute the following statement: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLEThis will invalidate the consistent snapshot. This option will automatically shut down when turned on –lock-tables.<\/p>\n
-l, –lock-tables
\nRead lock on all tables. (Default is open, use –skip-lock-tablesto close the above options will shut down -loption)<\/p>\n
-F, –flush-logs
\nRefresh the server’s log file before starting export. Note that if you export many databases at a time (use -databases=or –all-databasesoption), log refreshes when each library is exported. The exception is when using –lock-all-tablesor –master-data: The log will only be refreshed once, at that time all tables will be locked. So if you want your exports and log refreshes to happen at exactly the same time, you need to use –lock-all-tablesor –master-datacooperate –flush-logs.<\/p>\n
–delete-master-logs
\nAfter the backup is complete delete the log on the main library. This option automatically opens “ –master-data`.<\/p>\n
–opt
\nWith -add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, –disable-keys. ( –skip-optWhich is turned on by default and off means that these options keep their defaults) should give you the quickest possible export for reading into a MySQL server, –compactalmost banning the options above.<\/p>\n
-q, –quick
\nDo not buffer the query, directly exported to stdout. (Turned on by default, use –skip-quickto close) This option is used for dumping large tables.<\/p>\n
–set-charset
\nWill be SET NAMES default_character_setadded to the output. This option is enabled by default. To disable the SET NAMESstatement, use –skip-set-charset.<\/p>\n
–add-drop-tables
\nCREATE TABLEAdd a DROP TABLEstatement before each statement. Open by default.<\/p>\n
–add-locks
\nAdded before LOCK TABLESand after each table export UNLOCK TABLE. (In order to make it faster to insert into MySQL). Open by default.<\/p>\n
–create-option
\nInclude all MySQL table options in the CREATE TABLE statement. Open by default, use –skip-create-optionsto close.<\/p>\n
-e, –extended-insert
\nUsing the new multi-line INSERT syntax, turned on by default (gives tighter and faster insert statements)<\/p>\n
-d, –no-data
\nAny row information not written to the table. This is useful if you only want to export the structure of a table.<\/p>\n
–add-drop-database
\nBefore the create database DROP DATABASE, the default off, so generally need to ensure that the database has been imported.<\/p>\n
–default-character-set=
\nThe default character set to use. If not specified, mysqldump uses utf8.<\/p>\n
-B, –databases
\nDump several databases. Usually, mysqldump treats the first name parameter in the command line as the database name, followed by the name as the table name. With this option, it treats all name parameters as database names. CREATE DATABASE IF NOT EXISTS db_nameAnd the USE db_namestatement is included in the output in front of each new database.<\/p>\n
–tables
\nOverlay –databaseoptions. All arguments after the option are treated as table names.<\/p>\n
-u[ name], –user=
\nMySQL user name to use when connecting to the server.<\/p>\n
-p[password], –password[=password]
\nThe password to use when connecting to the server. If you use short form (-p), you can not have a space between the option and the password. If on the command line, the password value behind –passwordor -poption is ignored , you will be prompted for one.<\/p>\n
Example
\nExport a database:<\/p>\n
$ mysqldump -h localhost -uroot -ppassword \\ – master-data = 2 –single-transaction –add-drop-table –create-options –quick \\ – extended-insert –default-character-set = utf8 \\ – databases discuz> backup-file.sql
\nExport a table:<\/p>\n
$ mysqldump -u pak -p –opt –flush-logs pak t_user> pak-t_user.sql
\nCompress backup files:<\/p>\n
$ mysqldump -hhostname -uusername -ppassword –databases dbname | gzip> backup-file.sql.gz
\nThe corresponding reduction action is
\ngunzip <backup-file.sql.gz | mysql -uusername -ppassword dbname
\nImport the database:<\/p>\n
mysql> use target_dbname
\nmysql> source \/mysql\/backup\/path\/backup-file.sql
\nor
\n$ mysql target_dbname <backup-file.sql
\nImport there is a mysqlimportcommand, yet to study.<\/p>\n
Dump directly from one database to another:<\/p>\n
mysqldump -u username -p –opt dbname | mysql –host remote_host -C dbname2<\/p>\n","protected":false},"excerpt":{"rendered":"
MySQL backup command mysqldump parameters and examples<\/p>\n
1. Grammar options -h, –host=name CPU name<\/p>\n
-P[ port_num], –port=port_num The TCP \/ IP port number used to connect to the MySQL server<\/p>\n
–master-data This option adds the location and filename of the binlog to the output. If it is equal to 1, it will be printed as […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7173"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7173"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7173\/revisions"}],"predecessor-version":[{"id":7174,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7173\/revisions\/7174"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7173"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7173"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7173"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}