Jump to Navigation

MySQL: Backups by copying files

This is a guide about creating MySQL backups by copying files.

Using a file copy backup has the following characteristics:

  • Offline backup;
  • Full backup;
  • All databases and tables are always backed up and restored;
  • For both MyISAM and InnoDB tables;
  • Can be run remotely by using a ssh connection.

Creating a backup

Before creating the full backup, shutdown the database as cleanly as possible.

You need to copy all database files except log files, PID files and socket files. You should also backup /etc/my.sql. Backing up the binlogs as part of the full backup is not needed, provided any slaves of this server have processed all data1, except the last binlog2. Backing up the relay logs is not needed, provided all relay logs were applied before shutdown.

After the backup you can start the database again.

Remote backups

You can create a backup without putting it on a database server disk by using ssh, for example:

# On the command-line of some other server
# Database server
DBSERVER=db1
# File to save data to
DBARCHIVE=database.tar.gz
# Optionally use sudo to be able to read the files
SUDO=sudo
ssh $DBSERVER $SUDO tar cfzp - -C / etc/mysql.cnf var/lib/mysql > $DBARCHIVE

Of course, you will have to setup the sudoers file to allow this, or connect with a user that can read the MySQL files.

Restoring a backup

Restoring to the same machine is easy:

  • Stop MySQL;
  • Copy all files back to their original locations;
  • If not all binlogs were included in the backup, edit the binlog index (often mysqld-bin.index) and remove those binlogs that are not available;
  • If this is a clone of a still existing machine, remove auto.cnf;
  • Start MySQL.

Restoring to a different machine

Restoring to a different machine should work properly, provided the MySQL version is the same3. Note that your newly restored machine will have the same UUID as the original machine, which will be a problem in a replication structure using GTIDs if the old machine is still in the air. You can generate a new UUID by deleting auto.cnf before starting MySQL.

Another thing to be aware of is that if you have not specified specific names for relay_log and log-bin in /etc/my.cnf you may have to do that on the new machine to match the old names.

  • 1. Backing up binlogs separately, as part of a backup strategy, is usually a very good idea, though
  • 2. This will keep the binlog position, as well as the GTIDs executed list
  • 3. A different patch level will probably not cause any trouble, but it is a risk
Tags:


Technical_article | by Dr. Radut