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:
Dump and restore with compression
Dump with bzip compression:
Restore with bzip compression:
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
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.
In a migration script you can temporarily set the 'NO_AUTO_VALUE_ON_ZERO':
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
/etc/my.cnf, for example), then restart MySQL.