Description:
I use mysqldump to create nightly backups. I recently had to perform a restore due to a botched RAID install and found out that I couldn't restore my InnoDB tables from the dump file because the --delayed-insert I used in my backup script was getting put into the inserts on InnoDB tables which caused the inserts to fail. Could --delayed-insert please only work with MyISAM tables (and other tables that support it) and not InnoDB?
Here was my backup script - I have since removed --delayed-insert:
/usr/local/mysql/bin/mysqldump --add-locks \
--add-drop-table \
--all-databases \
--allow-keywords \
--complete-insert \
--compress \
--create-options \
--delayed-insert \
--delete-master-logs \
--disable-keys \
--extended-insert \
--flush-logs \
--force \
--lock-all-tables \
--master-data=2 \
--no-autocommit \
--quick \
--quote-names \
--socket=/usr/local/mysql/mysql.sock \
--password=xxxx \
--user=root \
| bzip2 -c > backup.sql.bz2
Thanks!
How to repeat:
Run above command and try to restore
Suggested fix:
Only have the INSERT DELAYED put in the dump file for MyISAM table types (and other supporting table types) and not InnoDB - or have InnoDB support delayed insert. :)
Description: I use mysqldump to create nightly backups. I recently had to perform a restore due to a botched RAID install and found out that I couldn't restore my InnoDB tables from the dump file because the --delayed-insert I used in my backup script was getting put into the inserts on InnoDB tables which caused the inserts to fail. Could --delayed-insert please only work with MyISAM tables (and other tables that support it) and not InnoDB? Here was my backup script - I have since removed --delayed-insert: /usr/local/mysql/bin/mysqldump --add-locks \ --add-drop-table \ --all-databases \ --allow-keywords \ --complete-insert \ --compress \ --create-options \ --delayed-insert \ --delete-master-logs \ --disable-keys \ --extended-insert \ --flush-logs \ --force \ --lock-all-tables \ --master-data=2 \ --no-autocommit \ --quick \ --quote-names \ --socket=/usr/local/mysql/mysql.sock \ --password=xxxx \ --user=root \ | bzip2 -c > backup.sql.bz2 Thanks! How to repeat: Run above command and try to restore Suggested fix: Only have the INSERT DELAYED put in the dump file for MyISAM table types (and other supporting table types) and not InnoDB - or have InnoDB support delayed insert. :)