1

Using mysqldump, how can I create a consistent backup of a MySQL (InnoDB) database and have the dump files be separated by table?

To create a consistent backup using mysqldump, I know there is --single-transaction

To create separate dump files, I know I can use my language of choice to loop through the tables and call separate mysqldump commands. But each table dump will be from a different point-in-time.

I was looking at the mysqldump documentation for someway to specify a point-in-time either by timestamp or by binary log coordinates, but I could not find it.

user156845
  • 11
  • 2
  • 1
    Tricky. I am tempted to think you would have to setup Mysql Replication. Pause replication on a slave, create your backups on the slave, then resume the replication. Or you simply have to compose a script that will parse out your single mysql dump command into multiple files. – Zoredache Jan 30 '13 at 20:14
  • A 24x7 replication slave would be too costly. And spinning up a slave as needed for backups would take too long (my database is 60+ GB, and it takes over 4 hours to restore from previous snapshot, plus the time for the slave to catch up). – user156845 Jan 30 '13 at 21:09
  • Giving all your requirements, I think you are out of luck. I could be wrong though. – Zoredache Jan 30 '13 at 21:13
  • What about --tab option ? `For each dumped table, mysqldump creates a tbl_name.sql file` https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_tab – Jerem Apr 21 '20 at 09:17

2 Answers2

0

Using your language of choice, you could lock all the tables and then loop through mysqldump for each table; or set the database to read only for the duration of the backup. You could also use innobackup (www.percona.com) which can keep everything consistent and then apply the logs that were written during the actual backup process.

chad
  • 1
0

I also loop through each table and instead of calling mysqldump i call "select into outfile".

Transactions are very much important for the application to have a consistent state for the db. so i think if you use the same the current method (loop through) or the "select into outfile" is the right method i suppose

user1616785
  • 101