MySQL Replication and Backup

Introduction

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

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.

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

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)

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

command can show what replication threads are running both on master and slave SELECT VERSION(); command provides MySQL DB version remotely

Requirements and hints:

Replication Howto

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

before 4.0.2 use FILE privilege instead

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';

at any system at using password 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

make sure option and is defined: 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

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

Useful commands:

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

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;

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:

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

SELECT INTO ... OUTFILE

BACKUP TABLE

SELECT INTO ... OUTFILE

BACKUP TABLE

If you want to make an SQL-level backup of a table, you can useor. For, the output file cannot previously exist. This is also true ofbecause 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

mysqldump

mysqldump description: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

description: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html general document on MySQL backup technics: http://dev.mysql.com/doc/refman/5.0/en/backup.html

The description ofprogram 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