MySQL Replication

From Superk

This page is unfinished. If you are logged in (Replication log in), you may edit this page. You can help make this site more informative!

This page describes very briefly the process of configuring a single master-slave replication environment using MySQL 4.0.x or later. This document should be edited to provide more details and remarks.

Assign IDs

It is important to assign unique IDs to both the master and the slave. These IDs need to be entirely numeric. For simplicity, the master has been given an ID of 001 while the slave receives an ID of 002.

Create Slave Server Account

In order for the slave host to be able to read the master's DB, it needs to have an account with proper privileges set. On the master create this slave account:

GRANT REPLICATION SLAVE ON *.*
TO <slave_user>@<slave_host>
IDENTIFIED BY <slave_password>;

The REPLICATION SLAVE privilege only allows the slave host to replicate the master's DB. This does not give the slave account the privilege to read or do anything else with the master's DB. If additional privileges are desired for this account (not recommended), the must be explicitly stated in the account creation.

GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.*
TO <slave_user>@<slave_host>
IDENTIFIED BY <slave_password>;

Duplicate the Master DB to the Slave

There are several methods for duplicating the master's DB to the slave. The easiest and most reliable is to stop the master server and simply copy all the database directories from the master host to the appropriate location on the slave host (be sure not to overwrite the mysql/ directory on the slave host since this directory contains the DB containing all the slave user privileges). Once the DBs are copied, leave the master host MySQL service stopped.

Modify the Master Configuration

Shut down the master host's MySQL service if it is running.

Create a new configuration file within the MySQL storage directory root (/var/lib/mysql on Debian) named 'my.cnf' and add the following to it:

[mysqld]
server-id=<master_server_id> # 001 in this example
log-bin=<binlog_name> # 'binlog' (no quotes) will work fine

Restart the master host's MySQL service.

RESET MASTER;

Modify the Slave Configuration

Shut down the slave host's MySQL service if it is running.

Create a new configuration file within the MySQL storage directory root (/var/lib/mysql on Debian) named 'my.cnf' and add the following to it:

[mysqld]
server-id=<slave_server_id> # 002 in this example
master-host=<master_host_hostname_or_ip>
master-user=<slave_user> # Username of the account created for replication on the Master host
master-password=<slave_password> # Password for the account created for replication on the Master host

Be sure to assign permissions to the 'my.cnf' file created in this section so that only the user/group running the MySQL daemon ('mysql'/'mysql' on Debian) has access to this file since passwords are being stored in the configuration. Also be sure to add the replication configuration to a server-specific configuration file (ie, not the /etc/my.cnf configuration file which is world-readable).

Restart the slave host's MySQL daemon.

Additional Notes

In addition to the basic configuration outlined above, there are some additional configuration variables that may be useful.

master-port

Within the slave host's private 'my.cnf' file the master-port variable can be defined if the master host is operating on a non-default port (3306 is the default port for MySQL).

master-port=1234

master-connect-retry

This variable defines the interval between retries of the connection to the master from the slave in seconds. This should be added into the slave's private 'my.cnf' configuration file.

master-connect-retry=120 # Will retry the connection after 2 minutes

master-retry-count

This variable is used in conjunction with the master-connect-retry variable to set the maximum number of times to retry a connection with the master from the slave before giving up. This should be added into the slave's private 'my.cnf' configuration file.

master-retry-count=500 # Will retry the connection 500 times before giving up

binlog-ignore

This variable will cause the master host to ignore specific DB's when updating replication binary logs. To block multiple DB's from being replicated, use this variable multiple times (once per line per DB). This should be added into the master's private 'my.cnf' configuration file.

binlog-ignore-db=mysql # Will prevent replication of the 'mysql' DB
binlog-ignore-db=test # Will prevent replication of the 'test' DB (in addition to 'mysql' as stated above)

Replication Administration Commands

The following are some commands that may prove useful in administrating a replication environment. All of these commands are issued through the MySQL command interface.

SLAVE STOP/START

Useful in stopping/starting the slave's replication process. This could be especially useful in making backups of the DB(s) without replication updating the DB(s) while they are being backed up.

SHOW SLAVE STATUS

Shows the status of the slave server in the replication process.

PURGE MASTER

Expires all the binary logs on the master host and starts fresh.

CHANGE MASTER

Run on a slave host, allows for changing various replication parameters such as which binary log to read from the master or which relay log file it writes to.