Skip to end of metadata
Go to start of metadata

Contents

Dumping and restoring databases with MySQL.

Dump and restore commands

To dump all databases to a file:

To dump a single database to a file:

Dump InnoDB in a single transaction:

To restore:

Dump and restore with compression

Dump with bzip compression:

Restore with bzip compression:

Reference

See:

Copying Databases

Databases can be copied from one host to another over a network using a single command line. Just pipe the output of mysqldump into the mysql command:

Restore problems

Inserts into tables with auto-increment columns fails with ERROR 1062 - Duplicate key

Inserts into tables with auto-increment columns can fail with <tt>ERROR 1062</tt> if the exported data has '0' as the first id. For example, if there are two insert statements:

The second insert will fail because the first insert causes an auto-increment. MySQL is configured to ignore auto-increment if the value is '1', but not with 'zero'. To work around this, you can set the NO_AUTO_VALUE_ON_ZERO SQL mode before the inserts.

For example:

In a migration script you can temporarily set the 'NO_AUTO_VALUE_ON_ZERO':

See:

ERROR 1153 (08S01) at line 363: Got a packet bigger than 'max_allowed_packet' bytes

This usually means the snapshot file contained BLOB or CLOB values larger than what the MySQL instance can handle.

The easiest way to fix this is to shut down MySQL, increase max_allowed_packet (in /etc/my.cnf, for example), then restart MySQL.

See Also

Labels