MySQL dump and restore
From Shrubbery
Dumping and restoring databases with MySQL.
[edit] Dump and restore commands
To dump all databases to a file:
$ mysqldump -u dbuser -pdbpass --all-databases > all-databases.sql
To dump a single database to a file:
$ mysqldump -u dbuser -pdbpass somedb > somedb.sql
Dump InnoDB in a single transaction:
$ mysqldump -u dbuser -pdbpass --single-transaction somedb > somedb.sql
To restore:
$ mysql -u dbuser -pdbpass somedb < somedb.sql
See:
[edit] Restore problems
[edit] Inserts into tables with auto-increment columns fails with ERROR 1062 - Duplicate key
Inserts into tables with auto-increment columns can fail with ERROR 1062 if the exported data has '0' as the first id. For example, if there are two insert statements:
INSERT INTO foo VALUES (0,'first'); INSERT INTO foo VALUES (1,'second');
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:
mysql> SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO'; mysql> INSERT INTO foo VALUES (0,'first'); mysql> INSERT INTO foo VALUES (1,'second');
In a migration script you can temporarily set the 'NO_AUTO_VALUE_ON_ZERO':
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; ... inserts go here ... SET SQL_MODE=@OLD_SQL_MODE;
See:
