MySQL Replication and Backup

Introduction

To increase reliability of mission critical databases that are using MySQL DBMS there are two simple solutions that can be easily implemented to protect the databases against disasters:
  • Replication - using built in MySQL functionality allows to create a mirror copy (slave) of the master database that is always in sync with the master. It will protect you against hardware and software failures of the master server however it will help in situations when a buggy application destroys the data (slave will always contain the same information as the master with a very small delay, so if by accident DELETE FROM ... command is used on master database slave will repeat the same operation almost immediately).
  • Database backup - periodical copies of the whole database that although do not contain the most recent data but it can be useful when recovering from serious application failures were data is destroyed on both master and slave.

Replication Basics

MySQL one way replication is supported starting from MySQL 3.23.15. The master writes in a binary log files. When the slave server connects to the master and the master informs it of the last update position within the log files. Then the slave blocks and waits for the master to send the new updates.

Requirements and hints:

  • Binary logging must be enabled on the master server
  • Slave must be set up with the copy of the master databases created at the moment when binary logging was turned on the master
  • LOAD DATA FROM MASTER command can be used with MySQL 4.x - transfers about 1MB/sec (over 100Mbps network)
  • SHOW PROCESSLIST command can show what replication threads are running both on master and slave
  • SELECT VERSION(); command provides MySQL DB version remotely

Replication Howto

Setup an account on master that has 'REPLICATION SLAVE' privilege

  • before 4.0.2 use FILE privilege instead
  • no additional privilege is required
  • example for user repl at any system at mydomain.com using password slavepass
    mysql> GRANT REPLICATION SLAVE ON *.*
      -> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
         
  • to test if permissions are correct, log into master host with slave account:
    $ mysql -h master.mydomain.com -u repl -pslavepass
    maysql> SHOW SLAVE HOSTS;
         

Master: Make sure master binary logging is on

  • in /etc/my.cnf make sure log-bin option and server-id is defined:
    [mysqld]
    log-bin
    server-id = 1
         
  • if these are not present, add them and restart the server
  • server id must be unique for each server and must be a positive number (1...2^32-1)
  • if server id is not set, replication will not work

Get copy of mysql DB

On master:

  • for MyISAM tables, flush all tables and block write statements:
    mysql> FLUSH TABLES WITH READ LOCK;
         
  • leave the command line client running, otherwise the lock will be released
  • archive mysql db with tar:
    $ cd /var/lib/mysql
    $ tar -cvf /tmp/mysql-snapshot.tar *
         
  • copy the tar to slave server to /tmp dir

On slave:

  • go into mysql data dir and unpack db snapshot:
    $ cd /var/lib/mysql
    $ tar -xvf /tmp/mysql-snapshot.tar
         
  • you don't need any of the binary log files in the archive
  • if you encounter problems reading InnoDB tables, then try using mysql dump of master database instead of a binary archive

On Master:

  • while master is still locked read in the binary log position:
    mysql> SHOW MASTER STATUS;
         
  • record =File=/log ile name and =Position=/offset position
  • reenable write on master:
     mysql> UNLOCK TABLES;
         

Slave: Server configuration

  • specify startup options for slave in /etc/my.cnf
    [mysqld]
    server-id=2
    replicate-do-db=my_database
    read-only=1
         
    • server id must be unique for each slave
    • replicate-do-db statement is optional and can be used to replicate only selected database (otherwise all databases will be replicated)
  • (re)start slave server and eter following command with a client :
    mysql> CHANGE MASTER TO
              MASTER_HOST='master_name',
              MASTER_USER='replication_username',
              MASTER_PASSWORD='replication_password',
              MASTER_LOG_FILE='recorded_log_filename',
              MASTER_LOG_POS=recorded_log_position
         

Useful commands:

mysql> SLAVE START;   # start slave replication threads
mysql> SLAVE STOP;    # stop slave replication threads
mysql> SHOW SLAVE STATUS;   # show slave configuration
     

Additional notes

  • It is safe to shutdown master - it will not break the replication as the slave will retry to connect every 60 seconds by default
  • It is safe to shutdown slave - it will continue with replication and the last position after restart automatically
  • In very rare cases synchronisation between master and slave can be lost (because of corrupted log file) - in that cases please refer to mysql documentation for recovery instructions (http://dev.mysql.com/doc/refman/5.0/en/replication-slave-sql.html)
  • For more info refer to replication documentation: http://dev.mysql.com/doc/refman/5.0/en/replication.html

Database backup

There are several possibilities of creating database backups in MySQL covering both full backups and incremental backups.

Binary copy of the database

Because MySQL tables are stored as files, it is easy to do a backup by simply copying (archiving) the files. To get a consistent backup, do the following:
  • flush all tables and block write statements:
    mysql> FLUSH TABLES WITH READ LOCK;
         
  • leave the command line client running, otherwise the lock will be released
  • archive mysql db with tar:
    $ cd /var/lib/mysql
    $ tar -cvf /tmp/mysql-backup.tar
         
  • reenable write on master:
     mysql> UNLOCK TABLES;
         

When DB replication is in use this method can be used both on master and on slave server. When using the method on the master server the database has to be locked during the whole process of archiving the DB. However when using it on the slave server it is only slave which is locked. The master server can still accept new updates and slave will catch up soon after backup process is finished and tables are unlocked.

SQL-level backup

If you want to make an SQL-level backup of a table, you can use SELECT INTO ... OUTFILE or BACKUP TABLE. For SELECT INTO ... OUTFILE, the output file cannot previously exist. This is also true of BACKUP TABLE because allowing extant files to be overwritten would constitute a security risk.

This method allows you to backup individual tables, but it has to be done one by one for all databases.

Using mysqldump and incremental backups

The description of mysqldump program and instructions for incremental backups are outside the scope of this short document. For the details please refer to MySQL documentation:

-- Main.pnyczyk - 11 Jan 2006

Edit | Attach | Watch | Print version | History: r4 < r3 < r2 < r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r4 - 2006-08-10 - SophieLemaitre
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    LCG All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2019 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback