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:

 

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

 

Related Posts

  • 45
    Dump and restore from .sql Dump mysqldump db_name table_name > table_name.sql Restore mysql -u <user_name> -p db_name mysql> source <full_path>/table_name.sql or in one line mysql -u username -p db_name < /path/to/table_name.sql Dump and restore from a compressed (.sql.gz) format Dump mysqldump db_name table_name | gzip > table_name.sql.gz Restore gunzip <…
    Tags: mysql, restore, mysqldump, dump, database, tables, format, table, backup
  • 42
      In the backup script the views are first created as tables which are then dropped at the end of the script as each view is being created, so it seems that an error occurs while creating the views at the end of the script. However when a view is…
    Tags: database, tables, backup, add, option, mysql, table
  • 39
    MySql export schema without data Login to DB server Run the below command to take backup of schema of database without data. # mysqldump -u root –p  -d olddb > backup.sql OR # mysqldump -u root -p --no-data dbname > schema.sql    
    Tags: data, mysqldump, database, mysql, server, backup
  • 38
    To learn more about what is binary log and how to setup Click here MySQL binary log : mysqlbinlog utility mysqldump &#8211; MySQL Database Backup and restore program Purge Binary log MySQL Master-Master-Slave-Slave Replication Database Recover from MySQL binary log: Binary logs store all the queries (in STATEMENT format) or…
    Tags: database, mysql, file, restore, data
  • 36
    To enable the query log, put this in /etc/my.cnf in the [mysqld] section log = /path/to/query.log #works for mysql < 5.1.29 Also, to enable it from MySQL console SET general_log = 1; Remember that this logfile can grow very big on a busy server. update: With mysql 5.1.29+ , the log option is deprecated. To specify…
    Tags: mysql, set, table, database, server, file

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: