0

I'd like to backup my database automatically but store each table in separate file. I've found a simple script here: http://www.jamescoyle.net/how-to/1167-export-mysql-database-into-separate-files-per-table.

However problem with this is that the data could be inconsistent if the database is changed when the script is running. I basically need to run the backup in single transaction or something like that.

Any tips?

EDIT: I could dump the entire database using one mysqldump, import it into dome dummy database to create a duplicate and then I wouldn't have to worry about transactions. Is that a good or bad idea?

enumag
  • 830
  • 11
  • 21
  • I'd say implement a replication server, switch that to read-only during the backup and dump from that one. – arkascha Apr 18 '14 at 11:15
  • @arkascha Sadly I don't have multiple servers at my disposal. Thanks for the tip though. – enumag Apr 18 '14 at 11:17
  • Why can't you do that on a single system? You just have to configure that "backup mysql server" to listen to other ports... – arkascha Apr 18 '14 at 11:28
  • Rather than import the dump file, I would suggest focusing on parsing and breaking up the dump file into multiple files rather than importing the whole thing back into a database. – rich remer Dec 29 '18 at 00:15

0 Answers0