MySQL backup command mysqldump parameters and examples
1. Grammar options
-h, –host=name
CPU name
-P[ port_num], –port=port_num
The TCP / IP port number used to connect to the MySQL server
–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 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.
-x, –lock-all-tables
Lock 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.
–single-transaction
The 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.
-l, –lock-tables
Read lock on all tables. (Default is open, use –skip-lock-tablesto close the above options will shut down -loption)
-F, –flush-logs
Refresh 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.
–delete-master-logs
After the backup is complete delete the log on the main library. This option automatically opens “ –master-data`.
–opt
With -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.
-q, –quick
Do not buffer the query, directly exported to stdout. (Turned on by default, use –skip-quickto close) This option is used for dumping large tables.
–set-charset
Will be SET NAMES default_character_setadded to the output. This option is enabled by default. To disable the SET NAMESstatement, use –skip-set-charset.
–add-drop-tables
CREATE TABLEAdd a DROP TABLEstatement before each statement. Open by default.
–add-locks
Added before LOCK TABLESand after each table export UNLOCK TABLE. (In order to make it faster to insert into MySQL). Open by default.
–create-option
Include all MySQL table options in the CREATE TABLE statement. Open by default, use –skip-create-optionsto close.
-e, –extended-insert
Using the new multi-line INSERT syntax, turned on by default (gives tighter and faster insert statements)
-d, –no-data
Any row information not written to the table. This is useful if you only want to export the structure of a table.
–add-drop-database
Before the create database DROP DATABASE, the default off, so generally need to ensure that the database has been imported.
–default-character-set=
The default character set to use. If not specified, mysqldump uses utf8.
-B, –databases
Dump 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.
–tables
Overlay –databaseoptions. All arguments after the option are treated as table names.
-u[ name], –user=
MySQL user name to use when connecting to the server.
-p[password], –password[=password]
The 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.
Example
Export a database:
$ 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
Export a table:
$ mysqldump -u pak -p –opt –flush-logs pak t_user> pak-t_user.sql
Compress backup files:
$ mysqldump -hhostname -uusername -ppassword –databases dbname | gzip> backup-file.sql.gz
The corresponding reduction action is
gunzip <backup-file.sql.gz | mysql -uusername -ppassword dbname
Import the database:
mysql> use target_dbname
mysql> source /mysql/backup/path/backup-file.sql
or
$ mysql target_dbname <backup-file.sql
Import there is a mysqlimportcommand, yet to study.
Dump directly from one database to another:
mysqldump -u username -p –opt dbname | mysql –host remote_host -C dbname2
Recent Comments