Is it okay to use rsync on InnoDB database if the MySQL server is shutdown?

Is it okay to use rsync on InnoDB database if the MySQL server is shutdown ?

For example :

/etc/init.d/mysql stop
rsync -av /home/mysql/mydb/ root@newserver.com:/home/mysql

That really depends on what the directory structure underneath /home/mysql/mydb - if that's just the data files for one database, you'll need to extend your reach.

If you're going to use rsync (or take file-based backups in general), you'll need to make sure that your backup includes, at the very least:

• Your innodb logfiles (ib_logfile*)

• Your main innodb data file(s) (ibdata*)

• All additional innodb data files (*.ibd) (if using innodb_file_per_table = 1)

• All table definition files (*.frm) for your InnoDB tables

Obviously this only covers InnoDB - presumably you'll want to grab all of any other database files (MyISAM, Archive, CSV, etc.) as well.

MySQL's documentation on the topic also recommends issuing set global innodb_fast_shutdown = 0 before shutting down the server - this shouldn't be necessary, but it makes sense. That setting will force the server to flush your log files to your data files, and will speed up the recovery time significantly if you need to restore from backup.

(Strictly speaking, doing a slow shutdown means you should be able to get away without backing up your log files, but it's still a Good Idea, just in case it turns out that one day the server doesn't shut down cleanly for some reason)

I'm a big fan of Percona Xtrabackup for backups, as it generally makes sure that it copies all of the necessary files plus it can take the backups online, but there are definitely situations where rsync might be more suitable to your requirements.

Update: If you're using MySQL 5.6, you may want to check out InnoDB Transportable Tablespaces. This functionality is probably not going to be useful to you, and would require a bit of scripting if you have a lot of tables, but it can be really helpful if you use innodb_file_per_table and you're trying to avoid copying across all of the other InnoDB baggage.

StackExchange Database Administrators Q#41667, answer score: 7