Segmenting mysql backups using tar

Question

In addition to using mysqldump, I've been backing up my mysql server by tarring up /var/lib/mysql for redundancy and convenience. This directory includes ibdata, ib_logfile0, and ib_logfile1 as well as some other files and a subdirectory for each of my databases. (I also back up my.cnf.) Now I need to segment the backup into two parts: (1) the "h" database containing only myisam compressed (readonly) tables and (2) all the other databases on the mysql server.

Assuming I am not changing server versions, is it safe and reliable to tar up /var/lib/mysql/h and to separately tar up everything else in the /var/lib/mysql directory? If "everything else" continues to change, can I still restore the "h" database from its older tar archive? If I restore everything else except the "h" subdirectory, will the other databases still be usable and the only effect be that the "h" database will be missing?

The reason I want to do this is that the "h" database is very large and only changes once a year. Everything else is comparatively small and changes all the time. If this approach works, I could make annual archives of the "h" database and weekly archives of everything else.

I am using MySQL Server 5.5.31 running on Debian 7 (Wheezy).

Are you stopping mysql before you backup /var/lib/mysql using tar? If not, what are you doing to make sure that you are getting a consistent backup (if DB file changes while tar is running, the backup may not restore)? — Walter, Aug 28 '13 at 00:54
Yes, I always stop the database before tarring up /var/mysql/db — user1389890, Aug 28 '13 at 01:12

score 1 · Answer 1 · answered Aug 28 '13 at 06:09

1

Yes, in theory the way you plan your backups should work.

But if the data is worth anything, you should have another server -- or your workstation, whatever -- where you try to actually restore your data and see what happens, and do that regularly. That way you can be sure you can actually restore your backups when it's needed.

answered Aug 28 '13 at 06:09

Janne Pikkarainen

31,454
4
56
78

I do plan to test the new backup strategy. However, the best I could come up with is to spot check some of the tables. As far as I am aware, there is no easy way to systematically test the entire database. I guess I could write software to do that, but it would take a very long time to run because the database is quite large. – user1389890 Aug 28 '13 at 12:57

score 0 · Answer 2 · answered Aug 28 '13 at 01:03

0

Assuming you a stopped writing/changing the files in the DB directory, one idea would be to backup the system db (mysql) twice. Once in the H backup and once in the everything else backup. That way, the H database can be restored to the way it was when it was backed up.

We used to create our MySQL offline, stop the DB, move the files to the production server, then start the production DB. It allowed us to update a multi-GB table without the hours it would take MySQL to load the data using SQL.

answered Aug 28 '13 at 01:03

Walter

1,047
7
14

I considered backing up all the system databases (mysql, information_schema, log, performance_schema) along with both the "h" backup and the everything else backup. The only problem with this is that, since the "h" archive will generally be a lot older than the everything else archive, if I restore from the "h" archive it will overwrite potentially newer system databases. – user1389890 Aug 28 '13 at 01:16
Follow-up question: If I drop the h database can I restore it simply by restoring the "h" subdirectory, or, alternatively, by first issuing a create database h and then restoring from the h archive? – user1389890 Aug 28 '13 at 01:44

Segmenting mysql backups using tar

2 Answers2