Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer

The short answer: yes, it's fine.

The not much longer answer: I'm assuming that you, like 99% or users, have everything under the "datadir" directory, and have no special setup for innodb tablespace files, innodb log files etc, location-wise.

In fact, rsyncing (or just plain old copying across NFS etc) is presumably the safest way to backup one's database. I do recall a Google engineer saying this is actually how they make their backups. The downside if, of course, the need to shut down your server.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.