1

I don't want to convert the tables to InnoDB what is the best way to take safe backup of my live site?

I've gone thru many posts in this site. I think there isn't unless we down the site in case of MyIsam. For InnoDB we can use --single-transaction.

I've been using mysqldump till now. But for live forums it may be risky to restore. It seems mysqldump does lock the tables but one by one which can corrupt the database tables.

I'm wondering what could be the best way to do using single command!

AgA
  • 181
  • 1
  • 10

2 Answers2

1

One of the options is to set up replication and perform mysqldump on the slave server with --lock-tables. Alternative option is to stop the slave server for the time of the backup, copy/archive the data and then restart the mysql. This way you avoid mysqldump/restore procedure which is time-consuming for big data sets.

Aleh
  • 121
  • 2
  • Good idea. But I'm not earning so much from my sites right now so I'd not like to go this far.. – AgA Jul 04 '11 at 05:01
0

One suggestion would be to use the ZFS file system to store your databases, and then snapshot the filesystem. The approach is highly scalable, results in consistent snapshots, and does not lock the filesystem for any appreciable length of time (under a second?).

If your servers are on Amazon's cloud, you may consider the script ec2-consistent-snapshot. It works well with XFS filesystems, and essentially flushes the database, freezes the filesystem, takes a snapshot, and unfreezes the filesystem. While perhaps not entirely applicable to your setup, the procedure may still be relevant.

Both of the above work at the filesystem level as opposed to running through mySQL.

cyberx86
  • 20,805
  • 1
  • 62
  • 81