#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    5
    Rep Power
    0

    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?
    mysql-relay-bin*, relay-log.info
    ib_logfile0,ib_logfile1
    master.info
    mysql
    debian-5.5.flag
    mysql_upgrade_info
    performance_schema

    There are loads of mysql-relay-bin* files.

    I can go digging. but I am sure aomeone knows the answer offhand.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    5
    Rep Power
    0
    Ok the following needs backing up:
    debian-5.5.flag
    ib_logfile0
    ib_logfile1
    master.info
    mysql directory
    mysql_upgrade_info
    performance_schema directory
    relay-log.info
    <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.
    mysqld-relay-bin.<n>
    mysqld-relay-bin.index

    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).

IMN logo majestic logo threadwatch logo seochat tools logo