mysqldump – MySQL Database Backup and restore program
What is mysqldump
- The mysqldump client is a utility that performs logical backups, producing a set of SQL statements that can be run to reproduce the original schema objects, table data, or both.
- It dumps one or more MySQL database for backup or transfer to another SQL server.
- The mysqldump command can also generate output in CSV, other delimited text, or XML format.
mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.
Syntax
There are in general three ways to use mysqldump—in order to dump a set of one or more tables, a set of one or more complete databases, or an entire MySQL server—as shown here:
shell>mysqldump [shell>options]db_name[tbl_name...]mysqldump [shell>options] --databasesdb_name...mysqldump [options] --all-databases
To dump entire databases, do not name any tables following db_name, or use the --databases or --all-databases option.
mysqldump Options:
| Format | Description | Introduced |
|---|---|---|
| –add-drop-database | Add DROP DATABASE statement before each CREATE DATABASE statement | |
| –add-drop-table | Add DROP TABLE statement before each CREATE TABLE statement | |
| –add-drop-trigger | Add DROP TRIGGER statement before each CREATE TRIGGER statement | |
| –add-locks | Surround each table dump with LOCK TABLES and UNLOCK TABLES statements | |
| –all-databases | Dump all tables in all databases | |
| –allow-keywords | Allow creation of column names that are keywords | |
| –apply-slave-statements | Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output | |
| –bind-address | Use specified network interface to connect to MySQL Server | 5.6.1 |
| –comments | Add comments to the dump file | |
| –compact | Produce more compact output | |
| –compatible | Produce output that is more compatible with other database systems or with older MySQL servers | |
| –complete-insert | Use complete INSERT statements that include column names | |
| –create-options | Include all MySQL-specific table options in CREATE TABLE statements | |
| –databases | Dump several databases | |
| –debug | Write a debugging log | |
| –debug-check | Print debugging information when program exits | |
| –debug-info | Print debugging information, memory, and CPU statistics when program exits | |
| –default-auth | Authentication plugin to use | |
| –default-character-set | Specify default character set | |
| –defaults-extra-file | Read option file in addition to usual option files | |
| –defaults-file | Read only named option file | |
| –defaults-group-suffix | Option group suffix value | |
| –delayed-insert | Write INSERT DELAYED statements rather than INSERT statements | |
| –delete-master-logs | On a master replication server, delete the binary logs after performing the dump operation | |
| –disable-keys | For each table, surround the INSERT statements with statements to disable and enable keys | |
| –dump-date | Include dump date as “Dump completed on” comment if –comments is given | |
| –dump-slave | Include CHANGE MASTER statement that lists binary log coordinates of slave’s master | |
| –events | Dump events from the dumped databases | |
| –extended-insert | Use multiple-row INSERT syntax that include several VALUES lists | |
| –fields-enclosed-by | This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| –fields-escaped-by | This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| –fields-optionally-enclosed-by | This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| –fields-terminated-by | This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| –flush-logs | Flush the MySQL server log files before starting the dump | |
| –flush-privileges | Emit a FLUSH PRIVILEGES statement after dumping the mysql database | |
| –help | Display help message and exit | |
| –hex-blob | Dump binary columns using hexadecimal notation (for example, ‘abc’ becomes 0x616263) | |
| –host | Host to connect to (IP address or hostname) | |
| –ignore-table | Do not dump the given table | |
| –include-master-host-port | Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with –dump-slave | |
| –insert-ignore | Write INSERT IGNORE statements rather than INSERT statements | |
| –lines-terminated-by | This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| –lock-all-tables | Lock all tables across all databases | |
| –lock-tables | Lock all tables before dumping them | |
| –log-error | Append warnings and errors to the named file | |
| –login-path | Read login path options from .mylogin.cnf | 5.6.6 |
| –master-data | Write the binary log file name and position to the output | |
| –max_allowed_packet | Maximum packet length to send to or receive from server | |
| –net_buffer_length | Buffer size for TCP/IP and socket communication | |
| –no-autocommit | Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements | |
| –no-create-db | This option suppresses the CREATE DATABASE statements | |
| –no-create-info | Do not write CREATE TABLE statements that re-create each dumped table | |
| –no-data | Do not dump table contents | |
| –no-defaults | Read no option files | |
| –no-set-names | Same as –skip-set-charset | |
| –no-tablespaces | Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output | |
| –opt | Shorthand for –add-drop-table –add-locks –create-options –disable-keys –extended-insert –lock-tables –quick –set-charset. | |
| –order-by-primary | Dump each table’s rows sorted by its primary key, or by its first unique index | |
| –password | Password to use when connecting to server | |
| –pipe | On Windows, connect to server using named pipe | |
| –plugin-dir | Directory where plugins are installed | |
| –port | TCP/IP port number to use for connection | |
| –print-defaults | Print defaults | |
| –protocol | Connection protocol to use | |
| –quick | Retrieve rows for a table from the server a row at a time | |
| –quote-names | Quote identifiers within backtick characters | |
| –replace | Write REPLACE statements rather than INSERT statements | |
| –result-file | Direct output to a given file | |
| –routines | Dump stored routines (procedures and functions) from the dumped databases | |
| –secure-auth | Do not send passwords to the server in old (pre-4.1.1) format | 5.6.17 |
| –set-charset | Add SET NAMES default_character_set to output | |
| –set-gtid-purged | Whether to add SET @@GLOBAL.GTID_PURGED to output | 5.6.9 |
| –shared-memory-base-name | The name of shared memory to use for shared-memory connections | |
| –single-transaction | This option issues a BEGIN SQL statement before dumping data from the server | |
| –skip-add-drop-table | Do not add a DROP TABLE statement before each CREATE TABLE statement | |
| –skip-add-locks | Do not add locks | |
| –skip-comments | Do not add comments to the dump file | |
| –skip-compact | Do not produce more compact output | |
| –skip-disable-keys | Do not disable keys | |
| –skip-extended-insert | Turn off extended-insert | |
| –skip-opt | Turn off the options set by –opt | |
| –skip-quick | Do not retrieve rows for a table from the server a row at a time | |
| –skip-quote-names | Do not quote identifiers | |
| –skip-set-charset | Suppress the SET NAMES statement | |
| –skip-triggers | Do not dump triggers | |
| –skip-tz-utc | Turn off tz-utc | |
| –socket | For connections to localhost, the Unix socket file to use | |
| –ssl | Enable SSL for connection | |
| –ssl-ca | Path of file that contains list of trusted SSL CAs | |
| –ssl-capath | Path of directory that contains trusted SSL CA certificates in PEM format | |
| –ssl-cert | Path of file that contains X509 certificate in PEM format | |
| –ssl-cipher | List of permitted ciphers to use for SSL encryption | |
| –ssl-crl | Path of file that contains certificate revocation lists | 5.6.3 |
| –ssl-crlpath | Path of directory that contains certificate revocation list files | 5.6.3 |
| –ssl-key | Path of file that contains X509 key in PEM format | |
| –ssl-verify-server-cert | Verify server Common Name value in its certificate against host name used when connecting to server | |
| –tab | Produce tab-separated data files | |
| –tables | Override the –databases or -B option | |
| –triggers | Dump triggers for each dumped table | |
| –tz-utc | Add SET TIME_ZONE=’+00:00′ to the dump file | |
| –user | MySQL user name to use when connecting to server | |
| –verbose | Verbose mode | |
| –version | Display version information and exit | |
| –where | Dump only rows selected by the given WHERE condition | |
| –xml | Produce XML output |
Reference: http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html
Get Database back from mysqldump :
If it’s an entire DB, then:
$ mysqldump -u [uname] -p[pass] db_name > db_backup.sql
If it’s all DBs, then:
$ mysqldump -u [uname] -p[pass] –all-databases > all_db_backup.sql
If it’s specific tables within a DB, then:
$ mysqldump -u [uname] -p[pass] db_name table1 table2 > table_backup.sql
You can even go as far as auto-compressing the output using gzip (if your DB is very big):
$ mysqldump -u [uname] -p[pass] db_name | gzip > db_backup.sql.gz
If you want to do this remotely and you have the access to the server in question, then the following would work (presuming the MySQL server is on port 3306):
$ mysqldump -P 3306 -h [ip_address] -u [uname] -p[pass] db_name > db_backup.sql
It should drop the .sql file in the folder you run the command-line from.
Pingback: Restore MySQL database from binary log
Pingback: MySQL Export query to SQL file : mysqldump - Eduguru