0

I am looking for a solution to take logical backup of MySQL database with mysqldump with single-transaction option and also avoiding any DDL statements during backup without locking any table. Currently mysqldump is getting failed if any alter table statement executed during backup.

salman
  • 11
  • 1
  • There is another post about this. You can find here: [Run MySQLDump without Locking Tables](https://stackoverflow.com/questions/104612/run-mysqldump-without-locking-tables) – F.Lazarescu Jan 29 '18 at 14:09
  • 2
    Thanks for the comment. As already mentioned i'm using mysqldump with --single-transaction option but this process is getting failed if any other user executes any DDL statement like Alter table 'table1'...., which results in failure of mysqldump throwing error as "Error 1412: Table definition has changed, please retry transaction when dumping table 'table1' at row 0". Note : All tables are InnoDB tables. – salman Jan 29 '18 at 14:17
  • Any solution to this when using mysqldump and --single-transaction failing if an ALTER TABLE is run at the same time as the dump is occurring? Seems like there should be a way for this to work... – Rob Mangiafico Feb 06 '20 at 21:46

1 Answers1

0

This helps you?

$ mysqldump --compress --quick --triggers --routines --lock-tables=false --single-transaction {YOUR_DATABASE_NAME}

F.Lazarescu
  • 1,385
  • 2
  • 16
  • 31
  • Nope! Same error occurred as i ran alter table command from other connection during execution of your suggested command. – salman Jan 30 '18 at 05:43