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 [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> 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:

 

FormatDescriptionIntroduced
–add-drop-databaseAdd DROP DATABASE statement before each CREATE DATABASE statement
–add-drop-tableAdd DROP TABLE statement before each CREATE TABLE statement
–add-drop-triggerAdd DROP TRIGGER statement before each CREATE TRIGGER statement
–add-locksSurround each table dump with LOCK TABLES and UNLOCK TABLES statements
–all-databasesDump all tables in all databases
–allow-keywordsAllow creation of column names that are keywords
–apply-slave-statementsInclude STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output
–bind-addressUse specified network interface to connect to MySQL Server5.6.1
–commentsAdd comments to the dump file
–compactProduce more compact output
–compatibleProduce output that is more compatible with other database systems or with older MySQL servers
–complete-insertUse complete INSERT statements that include column names
–create-optionsInclude all MySQL-specific table options in CREATE TABLE statements
–databasesDump several databases
–debugWrite a debugging log
–debug-checkPrint debugging information when program exits
–debug-infoPrint debugging information, memory, and CPU statistics when program exits
–default-authAuthentication plugin to use
–default-character-setSpecify default character set
–defaults-extra-fileRead option file in addition to usual option files
–defaults-fileRead only named option file
–defaults-group-suffixOption group suffix value
–delayed-insertWrite INSERT DELAYED statements rather than INSERT statements
–delete-master-logsOn a master replication server, delete the binary logs after performing the dump operation
–disable-keysFor each table, surround the INSERT statements with statements to disable and enable keys
–dump-dateInclude dump date as “Dump completed on” comment if –comments is given
–dump-slaveInclude CHANGE MASTER statement that lists binary log coordinates of slave’s master
–eventsDump events from the dumped databases
–extended-insertUse multiple-row INSERT syntax that include several VALUES lists
–fields-enclosed-byThis option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
–fields-escaped-byThis option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
–fields-optionally-enclosed-byThis option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
–fields-terminated-byThis option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
–flush-logsFlush the MySQL server log files before starting the dump
–flush-privilegesEmit a FLUSH PRIVILEGES statement after dumping the mysql database
–helpDisplay help message and exit
–hex-blobDump binary columns using hexadecimal notation (for example, ‘abc’ becomes 0x616263)
–hostHost to connect to (IP address or hostname)
–ignore-tableDo not dump the given table
–include-master-host-portInclude MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with –dump-slave
–insert-ignoreWrite INSERT IGNORE statements rather than INSERT statements
–lines-terminated-byThis option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
–lock-all-tablesLock all tables across all databases
–lock-tablesLock all tables before dumping them
–log-errorAppend warnings and errors to the named file
–login-pathRead login path options from .mylogin.cnf5.6.6
–master-dataWrite the binary log file name and position to the output
–max_allowed_packetMaximum packet length to send to or receive from server
–net_buffer_lengthBuffer size for TCP/IP and socket communication
–no-autocommitEnclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements
–no-create-dbThis option suppresses the CREATE DATABASE statements
–no-create-infoDo not write CREATE TABLE statements that re-create each dumped table
–no-dataDo not dump table contents
–no-defaultsRead no option files
–no-set-namesSame as –skip-set-charset
–no-tablespacesDo not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output
–optShorthand for –add-drop-table –add-locks –create-options –disable-keys –extended-insert –lock-tables –quick –set-charset.
–order-by-primaryDump each table’s rows sorted by its primary key, or by its first unique index
–passwordPassword to use when connecting to server
–pipeOn Windows, connect to server using named pipe
–plugin-dirDirectory where plugins are installed
–portTCP/IP port number to use for connection
–print-defaultsPrint defaults
–protocolConnection protocol to use
–quickRetrieve rows for a table from the server a row at a time
–quote-namesQuote identifiers within backtick characters
–replaceWrite REPLACE statements rather than INSERT statements
–result-fileDirect output to a given file
–routinesDump stored routines (procedures and functions) from the dumped databases
–secure-authDo not send passwords to the server in old (pre-4.1.1) format5.6.17
–set-charsetAdd SET NAMES default_character_set to output
–set-gtid-purgedWhether to add SET @@GLOBAL.GTID_PURGED to output5.6.9
–shared-memory-base-nameThe name of shared memory to use for shared-memory connections
–single-transactionThis option issues a BEGIN SQL statement before dumping data from the server
–skip-add-drop-tableDo not add a DROP TABLE statement before each CREATE TABLE statement
–skip-add-locksDo not add locks
–skip-commentsDo not add comments to the dump file
–skip-compactDo not produce more compact output
–skip-disable-keysDo not disable keys
–skip-extended-insertTurn off extended-insert
–skip-optTurn off the options set by –opt
–skip-quickDo not retrieve rows for a table from the server a row at a time
–skip-quote-namesDo not quote identifiers
–skip-set-charsetSuppress the SET NAMES statement
–skip-triggersDo not dump triggers
–skip-tz-utcTurn off tz-utc
–socketFor connections to localhost, the Unix socket file to use
–sslEnable SSL for connection
–ssl-caPath of file that contains list of trusted SSL CAs
–ssl-capathPath of directory that contains trusted SSL CA certificates in PEM format
–ssl-certPath of file that contains X509 certificate in PEM format
–ssl-cipherList of permitted ciphers to use for SSL encryption
–ssl-crlPath of file that contains certificate revocation lists5.6.3
–ssl-crlpathPath of directory that contains certificate revocation list files5.6.3
–ssl-keyPath of file that contains X509 key in PEM format
–ssl-verify-server-certVerify server Common Name value in its certificate against host name used when connecting to server
–tabProduce tab-separated data files
–tablesOverride the –databases or -B option
–triggersDump triggers for each dumped table
–tz-utcAdd SET TIME_ZONE=’+00:00′ to the dump file
–userMySQL user name to use when connecting to server
–verboseVerbose mode
–versionDisplay version information and exit
–whereDump only rows selected by the given WHERE condition
–xmlProduce XML output

Reference: http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

 

Satya Prakash

VOIP Expert: More than 8 years of experience in Asterisk Development and Call Center operation Management. Unique Combination of Skill Set as IT, Analytics and operation management.

3 thoughts on “mysqldump – MySQL Database Backup and restore program

  • July 30, 2015 at 5:13 pm
    Permalink

    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

Leave a Reply