October 12th, 2012, 08:24 AM
Require help as to which files to tar for mysql backup
Rather than using mysqldump I would like to use tar to make a backup of the mysql files. I use mysql 5.5 and all tables are innidb storage engine based.
I know of the following files that need backing up:
Please note that log_bin is not in 'datadir' and logbin files are backed up seperately.
1. directory pointed at by 'innodb-data-home-dir'
2. directory pointed at by 'datadir'
3. currently in-use my.cnf file(s)
4. <database name> folders in 'datadir'
The real question is: Do I need to back up all files in the 'datadir' directory or can some be skipped?
There are loads of mysql-relay-bin* files.
I can go digging. but I am sure aomeone knows the answer offhand.
October 12th, 2012, 09:43 AM
Ok the following needs backing up:
<own data base directories>
The following 2 are for replication and I am not quite sure how to handle them. As far as I can see they are identical to log_bin files. I was sort of hoping that if you turn binary logging off in mysql, that these would keep logging so that if you did a restore of a mysqldump on the master, it would still replicate, as well as all the subsequent transactions, but it does not seem to be like that. When you turn lbinary logging off, these files also stop logging.
Correction: These files only exist on the slave in a replication screnario.
I am no longer sure whether replication provides any security for recovery as the slave might be behind the master, so switching over to it might lose transactions. It seems a backup restore + process logbin files on a single server might be better (ignoring trying to distribute reads to replicated servers).