MySQL in a Nutshell, 2nd Edition

Name

mysqldump

Synopsis

mysqldump [options] --all-databases
mysqldump [options] --databases database [database ...]
mysqldump [options] database [table]

This utility exports MySQL data and table structures. Typically, you use it to make backups of databases or to copy databases from one server to another. You can run it on an active server. For consistency of data between tables, the tables should be locked (see the --lock-tables option) or the mysqld daemon should be shutdown.

There are three syntaxes for this utility. The first method shown makes a backup of all databases for the server. The second method backs up specific databases, named in a space-separated list, including all tables in each database. The third method backs up specific tables of a specific database.

Here is an example using the first method, backing up all databases on the server:

mysqldump --host=russell.dyerhouse.com --user=russell --password \
   --lock-tables --all-databases > /tmp/workrequests.sql

Because the backup is being run from a remote server (i.e., not the localhost), the --host option is given with a domain name address for the host. An IP address could be given instead. Making a backup remotely like this will work only if the host grants the necessary privileges to user russell with the host from which mysqldump is running. The example redirects the results with a greater-than sign to a text file.

To make a backup of a specific database, use the second syntax for this utility. Enter something like the following from the command line:

mysqldump -u russell -p --lock-tables workrequests > /tmp/workrequests.sql

In this example, the username is given with the -u option. The -p option tells the utility to prompt the user for a password. These shorter options are interchangeable with their longer, more verbose ones, but the verbose ones are becoming the norm and should be used. The --lock-tables option has the server lock the tables, make the backup, and then unlock them when it’s finished. Next, we specify the database to back up (workrequests). Finally, using the redirect (the greater-than sign), the output is saved to the filename given.

The --lock-tables option is generally not necessary because the --opt option is a default option and includes locking tables. In fact, if you’re making a backup and you do not have the LOCK TABLES privilege, you will receive an error when running mysqldump because of --opt. In such a situation, you’ll need to include the --skip-opt option to specifically disable --opt and thereby not attempt to lock the tables.

If you want to back up specific tables and not an entire database, you can use the third syntax shown at the start of this section for this utility. It’s not a very verbose syntax: you simply give the name of the database followed by one or more tables. You don’t identify them individually as a database versus tables; you just put them in the proper order without the --all-database option. Here’s an example of this syntax:

mysqldump -u russell -p workrequests work_req clients > 
   /tmp/workreq_clients_tables.sql

In this example, the database is workrequests and the tables to be backed up are work_req and clients. Their table structures and data will be copied into the text file workreq_clients_tables.sql.

The backup or dump file created by mysqldump will be in the text file format. It generally will contain a CREATE TABLE statement for each table in the database. If you want to eliminate the CREATE TABLE statements, add the --no-create-info. If they are not included in the dump file generated on your server, add the --create-options option and run mysqldump again. The dump files will also generally contain a separate INSERT statement for each row of data. To back up the data faster, you can add the --extended-insert option so that only one INSERT with multiple values will be generated for each table instead of separate INSERT statements for each row of data.

To restore the data from a dump file created by mysqldump, you can use the mysql client. To restore the file created by the preceding statement, you can enter the following from the command line:

mysql -u russell -p < /tmp/workrequests.sql

This example redirects the stdin by means of the less-than sign. This instructs the mysql client to take input from the file given. It will execute the series of SQL statements contained in the dump file. You won’t be placed into monitor mode; you will remain at the command line until it’s finished.

You can determine the contents of the dump file by the options you choose. Following is an alphabetical list of options, along with a brief explanation of each. For some options, there is a shorter, less verbose version (i.e., -u for --user). These shorter options are interchangeable with their longer, more verbose ones, but the verbose ones are becoming the norm and should be used.

mysqldump options

--add-drop-database

This option adds a DROP DATABASE statement followed by a CREATE DATABASE statement to the export file for each database, thus replacing the existing database and data if restored.

--add-drop-table

This option adds a DROP TABLE statement to the export file before each set of INSERT statements for each table.

--add-locks

This option adds a LOCK statement before each set of INSERT statements and an UNLOCK after each set.

--all, -a

This option includes all MySQL-specific statements in the export file. This option is deprecated as of version 4.1.2 of MySQL. It is replaced with the --create-options option.

--all-databases, -A

This option exports all databases.

--all-tablespaces, -Y

This option is used with MySQL Cluster so that the utility will include the necessary SQL statements related to the NDB storage engine. This option is available as of version 5.1.6 of MySQL.

--allow-keywords

This option makes keywords allowable for column names by including the table name and a dot before such column names in the export file.

--character-sets-dir=path

This option specifies the directory containing character sets.

--comments[=0|1], -i

If this option is set to a value of 1 (the default), any comments from a table’s schema will be included in the export file. If it is set to 0, they won’t be included. To disable this option since it’s the default, use the --skip-comments option.

--compact

This option omits comments from the dump file to make the file more compact. It also calls the --skip-add-drop-table, --skip-add-locks, --skip-disable-keys, and --skip-set-charset options. Don’t confuse this option with --compress. Before version 5.1.2 of MySQL, this option did not work with databases that contained views.

--compatible=type

This option makes the export file’s contents compatible with other database systems. The choices currently are: ansi, mysql323, msyql40, postgresql, oracle, mssql, db2, maxdb (or sapdb for older versions), no_key_options, no_table_options, and no_field_options. More than one type may be given in a comma-separated list. This option is used with version 4.1.0 of MySQL or higher.

--complete-insert, -c

This option generates complete INSERT statements in the export file.

--compress, -C

This option compresses data passed between the utility and the server, if compression is supported.

--create-options

This option includes all MySQL-specific statements (e.g., CREATE TABLE) in the export file. It’s synonymous with the --all option.

--databases, -B

This option names more than one database to export. Table names may not be given with this option unless using the --tables option.

--debug[=options], -#[options]

This option logs debugging information. The set of options used by default is 'd:t:o,logname'. See Table 16-1 at the end of the list of options for this utility for an explanation of these flags and others that may be used. Here is an example of how you might use this option:

mysqldump -u russell -p --debug='d:f:i:o,/tmp/mysql_debug.log' 
   workrequests > /tmp/workrequests.sql

--debug-check

This option writes debugging information to the log when the utility ends. It’s available as of version 5.1.21 of MySQL.

--debug-info

This option writes debugging information and CPU and memory usage information to the log after the utility ends.

--default-character-set=set

This option specifies the default character set for the utility to use. Execute SHOW CHARACTER SET from MySQL on the server to get a list of possibilities. By default, recent versions of the utility use UTF-8. Previous versions used Latin 1.

--delayed-insert

This option adds the DELAYED keyword to INSERT statements in the export file. In older versions of mysqldump, this option was --delayed.

--delete-master-logs

This option instructs the utility to lock all tables on all servers and then to delete the binary logs of a master replication server after completing the export. Using this option also invokes the --master-data option.

--disable-keys, -K

For MyISAM tables, this option adds an ALTER TABLE...DISABLE KEYS statement to the export file before each set of INSERT statements, and an ALTER TABLE...ENABLE KEYS statement after each set to optimize later restoration.

--events, -E

This option includes events from the databases. It is available as of version 5.1.8 of MySQL.

--extended-insert, -e

This option bundles INSERT statements together for each table in the export file to make the export faster. Otherwise, a separate INSERT statement for each row of each table will be placed in the dump file.

--fields-enclosed-by=characters

Use this option with the --tab option to specify the characters that start and end fields in the data text file.

--fields-escaped-by=character

Use this option with the --tab option to specify the character that escapes special characters in the data text file. A backslash is the default.

--fields-optionally-enclosed-by=characters

Use this option with the --tab option to specify the characters that can be used when necessary to start and end fields in the data text file.

--fields-terminated-by=character

Use this option with the --tab option to specify the characters that end fields in the data text file.

--first-slave

This option locks all tables on all servers. It has been deprecated and replaced with --lock-all-tables.

--flush-logs, -F

This option flushes all logs. It requires the user to have RELOAD privilege on the server.

--flush-privileges

This option flushes all privileges. It was added as of version 5.1.12.

--force, -f

This option instructs the utility to continue processing data despite errors. This is useful in completing dumps for irrelevant errors such as ones related to views that no longer exist.

--help, -?

This option displays basic help information.

--hex-blob

This option uses hexadecimal equivalents for BINARY, BIT, BLOB, and VARBINARY columns.

--host=host, -h host

This option specifies the name or IP address of the server for connection. The localhost is the default. The user and host combination and related privileges will need to be set on the server.

--ignore-table=database.table

This option instructs the utility not to export the given table of the given database. For more than one table, enter this option multiple times with one database and table combination in each.

--insert-ignore

This option adds the IGNORE keyword to INSERT statements in the dump file.

--lines-terminated-by=character

Use this option with the --tab option to specify the character that ends records in the data text file.

--lock-tables, -l

This option instructs the utility to get a READ LOCK on all tables of each database before exporting data, but not on all databases at the same time. It locks a database when it’s dumping and releases the lock before locking and dumping the next database. This option is typically used with MyISAM tables. For transactional storage engines, use --single-transaction instead.

--lock-all-tables, -x

This option locks all tables on all servers. It replaces --first-slave, which has been deprecated.

--log-error=logfile

This option writes errors and warning messages to the file named. The file path may be included. This option is available as of version 5.1.18 of MySQL.

--master-data=value

This option is used with replication. It writes the name of the current binary log file and server’s position in the log file to the dump file. It requires the RELOAD privilege. It will typically disable --lock-tables and --lock-all-tables.

--no-autocommit

This option adds SET AUTOCOMMIT=0: before each INSERT statement, and a COMMIT; statement after each INSERT statement.

--no-create-db, -n

This option instructs the utility not to add CREATE DATABASE statements to the export file when the --all-databases option or the --databases option is used.

--no-create-info, -t

This option instructs the utility not to add CREATE TABLE statements to the export file.

--no-data, -d

This option exports only database and table schema, not data.

--opt

This option is a combination of several commonly used options: --add-drop-table, --add-locks, --create-options (or --all before version 4.1.2), --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. As of version 4.1 of MySQL, the --opt option is enabled by default. Use --skip-opt to disable it for users with limited access.

--order-by-primary

This option sorts rows of tables by their primary key or first index. It slows down the backup process, though.

--password[=password], -p[password]

This option provides the password to pass to the server. A space is not permitted after -p if the password is given. If the password is not given when using the -p option, the user will be prompted for one.

--port=port, -P port

This option specifies the port number to use for connecting to the server. A space is expected before the port number when using the -P form of the option.

--protocol=protocol

This option is used to specify the type of protocol to use for connecting to the server. The choices are TCP, SOCKET, PIPE, and MEMORY.

--quick, -q

This option instructs the utility not to buffer data into a complete results set before exporting. Instead, it exports data one row at a time directly to the export file.

--quote-names, -Q

This option places the names of databases, tables, and columns within backticks (`). This is the default option. If the server is running in ANSI_QUOTES SQL mode, double quotes will be used instead. This option is enabled by default. Use --skip-quote-names to disable it.

--replace

This option puts REPLACE statements into the dump file instead of INSERT statements. It was added as of version 5.1.3 of MySQL.

--result-file=filename, -r filename, > filename

This option provides the path and the name of the file to which data should be exported. Use the --result-file option on Windows systems to prevent newline characters (\n) from being converted to carriage return and newline characters (\r\n).

--routines, -R

This option dumps stored procedures and functions. It was added as of version 5.1.2 of MySQL. It requires the SELECT privilege in the proc table of the mysql database. The statements written to the dump file related to these routines do not include timestamps, so the current time will be used when restoring instead.

--set-charset

This option adds the SET NAMES statement to the dump file. It’s enabled by default. Use --skip-set-charset to disable it.

--single-transaction

This option executes a BEGIN statement before exporting to help achieve data consistency with the backup. It’s effective only on transactional storage engines. It should not be used with MySQL Cluster.

--skip-comments

This option instructs the utility not to export any comments from a table’s schema to the export file. It disables the --comments option.

--skip-opt

This option disables the --opt option.

--skip-quote-names

This option disables the --quote-names option.

--ssl

This option specifies that secure SSL connections should be used. It requires the server to have SSL enabled. If this option is enabled on the utility by default, use --skip-ssl to disable it.

--ssl-ca=pem_file

This option specifies the name of the file (i.e., the pem file) containing a list of trusted SSL CAs.

--ssl-capath=path

This option specifies the path to the trusted certificates file (i.e., the pem file).

--ssl-cert=filename

This option specifies the name of the SSL certificate file to use for SSL connections.

--ssl-cipher=ciphers

This option gives a list of ciphers that may be used for SSL encryption.

--ssl-key=filename

This option specifies the SSL key file to use for secure connections.

--ssl-verify-server-cert

This option verifies the client’s certificate against the server’s certificate for the client at startup. It is available as of version 5.1.11 of MySQL.

--socket=filename, -S filename

This option provides the name of the server’s socket file on a Unix-type system or the named pipe on Windows systems.

--tab=path, -T path

This option creates two separate export files: one for the table schema (e.g., table.sql) and another for the data (e.g., table.txt). The data text file will contain data in a tab-separated format. This option requires FILE privilege, and the MySQL server must have write permission for the directory it is to write the exported file.

--tables

This option specifies tables to dump. All names after the --tables option are treated as table names and not as database names.

--triggers

This option includes triggers in dump files. It is the default. Use --skip-triggers to disable it.

--tz-utc

This option adds SET TIME_ZONE='+00:00'; to the dump file so that the dump files may be restored on a server in a different time zone and not cause inconsistencies with TIMESTAMP columns. This option is available as of version 5.1.2 of MySQL and is enabled by default. Use --skip-tz-utc to disable it.

--user=user, -u user

This option specifies the username for connecting to the server. A space is expected after the -u option. If the -u version of this option is used and the username is not given, the current system user is assumed.

--verbose, -v

This option displays more information.

--version, -V

This option displays the version of the utility and exits.

--where='condition', -w 'condition'

This option sets a WHERE condition for selecting rows from tables to be exported. For instance, suppose that we want to back up the clients table with only the clients who are located in New Orleans. We could run the utility like so:

mysqldump -u russell -p /
   --where="client_city='New Orleans'" workrequests clients > /tmp/
      workreq_clients_neworleans.sql

--xml, -X

This option exports databases in XML format.

mysqldump --debug options

Table 16-1 lists the debugging, tracing, and profiling flags used with the --debug option for several MySQL-related utilities. The format is generally --debug='flag:flag:flag'. When a particular option needs more details, follow the flag with a comma and the details or extra settings in a comma-separated list: --debug='flag:flag,setting,setting:flag'. An alternative to the --debug='flag:flag:flag' syntax is --#flag:flag:flag. This syntax lacks the equals sign or quotes; the space afterward marks the end of the flags and settings.

Table 16-1. Debugging options

Flag	Description
`d`	Logs the DBUG macros. To log only certain macros, give the `d` flag followed by the specific macro keywords.
`D`	Used to specify a delay after each line in the debugging log. After the flag and a comma, give the number of tenths of a second to delay (e.g., `D,10` for a 1 second delay).
`f`	Limits debugging, tracing, and profiling to particular functions. The `f` flag with no functions listed results in all functions being filtered out of the log.
`F`	Names the source filename for debugging and tracing output.
`i`	Specifies the process identifier (PID) or thread identifier for each line of debugging and tracing output that is logged.
`g`	Enables profiling. A file named dbugmon.out may be used to provide details for profiling. A list of functions to profile may be given after this flag. If none are specified, all functions will be included.
`L`	Includes the source file’s line number in each line of the debugging and tracing log.
`n`	Logs the nesting depth of each function for debugging and tracing.
`N`	Includes a line number in each line of the log.
`o`	Redirects debugging information to a given file, rather than `stderr`. The filename is given after the flag, separated from it by a comma (e.g., `o,/tmp/mysql_debug.log`).
`O`	This is the same as the `o` flag, but the log file is flushed between each write, and possibly opened and closed each time.
`p`	Limits debugging to given processes. Each process has to be specified with the `DBUG_PROCESS` macro.
`P`	Writes the current process name for each line to the debugging and tracing logs.
`r`	Resets the previous state’s function nesting level.
`S`	Used with `safemalloc` to locate memory leaks. Will run until nonzero is returned.
`t`	Enables call and exit trace logging. A numeric maximum trace level may be given after the flag, separated from it by a comma.

Table of Contents for MySQL in a Nutshell, 2nd Edition

Name

Synopsis

mysqldump options

mysqldump --debug options

Table of Contents for
MySQL in a Nutshell, 2nd Edition