mysqldump Tips

This section surveys techniques that enable you to use mysqldump to solve specific problems:

• How to make a copy a database

• How to copy a database from one server to another

• How to dump stored programs (stored procedures and functions, triggers, and events)

• How to dump definitions and data separately

7.4.5.1 Making a Copy of a Database

shell> mysqldump db1 > dump.sqlshell> mysqladmin create db2shell> mysql db2 < dump.sql

Do not use --databases on the mysqldump command line because that causes USE db1 to be included in the dump file, which overrides the effect of naming db2 on the mysql command line.

7.4.5.2 Copy a Database from one Server to Another

On Server 1:

shell> mysqldump --databases db1 > dump.sql

Copy the dump file from Server 1 to Server 2.

On Server 2:

shell> mysql < dump.sql

Use of --databases with the mysqldump command line causes the dump file to include CREATE DATABASE andUSE statements that create the database if it does exist and make it the default database for the reloaded data.

Alternatively, you can omit --databases from the mysqldump command. Then you will need to create the database on Server 2 (if necessary) and specify it as the default database when you reload the dump file.

On Server 1:

shell> mysqldump db1 > dump.sql

On Server 2:

shell> mysqladmin create db1shell> mysql db1 < dump.sql

You can specify a different database name in this case, so omitting --databases from the mysqldump command enables you to dump data from one database and load it into another.

7.4.5.3 Dumping Stored Programs

Several options control how mysqldump handles stored programs (stored procedures and functions, triggers, and events):

• --events: Dump Event Scheduler events

• --routines: Dump stored procedures and functions

• --triggers: Dump triggers for tables

The --triggers option is enabled by default so that when tables are dumped, they are accompanied by any triggers they have. The other options are disabled by default and must be specified explicitly to dump the corresponding objects. To disable any of these options explicitly, use its skip form: --skip-events, --skip-routines, or --skip-triggers.

7.4.5.4 Dumping Table Definitions and Content Separately

The --no-data option tells mysqldump not to dump table data, resulting in the dump file containing only statements to create the tables. Conversely, the --no-create-info option tells mysqldump to suppress CREATEstatements from the output, so that the dump file contains only table data.

For example, to dump table definitions and data separately for the test database, use these commands:

shell> mysqldump --no-data test > dump-defs.sqlshell> mysqldump --no-create-info test > dump-data.sql

For a definition-only dump, add the --routines and --events options to also include stored routine and event definitions:

shell> mysqldump --no-data --routines --events test > dump-defs.sql

7.4.5.5 Using mysqldump to Test for Upgrade Incompatibilities

When contemplating a MySQL upgrade, it is prudent to install the newer version separately from your current production version. Then you can dump the database and database object definitions from the production server and load them into the new server to verify that they are handled properly. (This is also useful for testing downgrades.)

On the production server:

shell> mysqldump --all-databases --no-data --routines --events > dump-defs.sql

On the upgraded server:

shell> mysql < dump-defs.sql

Because the dump file does not contain table data, it can be processed quickly. This enables you to spot potential incompatibilities without waiting for lengthy data-loading operations. Look for warnings or errors while the dump file is being processed.

After you have verified that the definitions are handled properly, dump the data and try to load it into the upgraded server.

On the production server:

shell> mysqldump --all-databases --no-create-info > dump-data.sql

On the upgraded server:

shell> mysql < dump-data.sql

Now check the table contents and run some test queries