Replicating MySQL - Database servers

MySQL has a replication feature to keep databases on different servers synchronized. It uses what is known as log replay, meaning that a transaction log is created on the master server which is then read by a slave server and applied to the database. As with the Web servers, we designate one database server as the master -- call it db1 to match the naming convention we used earlier -- and the other one, db2, is the slave.

To set up the master, first create a replication account -- a user ID defined in MySQL, not a system account, that is used by the slaves to authenticate to the master in order to read the logs. For simplicity, I'll create a MySQL user called "copy" with a password of "copypass." You will need a better password for a production system. This MySQL command creates the copy user and gives it the necessary privileges:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO copy@"10.1.0.0/255.255.0.0" IDENTIFIED BY 'copypass';

Next, edit the MySQL configuration file, /etc/my.cnf, and add these entries in the [mysqld] section:

# Replication Master Server (default)
# binary logging is required for replication
log-bin

# required unique id
server-id = 1

The log-bin entry enables the binary log file required for replication, and the server-id of 1 identifies this server as the master. After editing the file, restart MySQL. You should see the new binary log file in the MySQL directory with the default name of $HOSTNAME-bin.001. MySQL will create new log files as needed.

To set up the slave, edit its /etc/my.cnf file and add these entries in the [mysqld] section:

# required unique id
server-id = 2
#
# The replication master for this slave - required
# (replace with the actual IP of the master database server)
master-host = 10.1.1.21
#
# The username the slave will use for authentication when connecting
# to the master - required
master-user = copy

# The password the slave will authenticate with when connecting to
# the master - required
master-password = copypass

# How often to retry lost connections to the master
master-connect-retry = 15

# binary logging - not required for slaves, but recommended
log-bin

While it's not required, it is good planning to create the MySQL replication user (copy in our example) on each slave in case it needs to take over from the master in an emergency.

Restart MySQL on the slave and it will attempt to connect to the master and begin replicating transactions. When replication is started for the first time (even unsuccessfully), the slave will create a master.info file with all the replication settings in the default database directory, usually /var/lib/mysql.

To recap the database configuration steps,

1. Create a MySQL replication user on the master and, optionally, on the slave.
2. Grant privileges to the replication user.
3. Edit /etc/my.cnf on master and restart MySQL.
4. Edit /etc/my.cnf on the slave(s) and restart MySQL.

How to tell if replication is working

On the master, log in to the MySQL monitor and run show master status:

mysql> show master status \G;
*************************** 1. row ***************************
File: master-bin.006
Position: 73
Binlog_do_db:
Binlog_ignore_db:
1 row in set (0.00 sec)

On the slave, log in to the MySQL monitor and run show slave status:

mysql> show slave status \G;
*************************** 1. row ***************************
Master_Host: master.foo.com
Master_User: copy
Master_Port: 3306
Connect_retry: 15
Master_Log_File: intranet-bin.006
[snip]
Slave_IO_Running: Yes
Slave_MySQL_Running: Yes

The most important fields are Slave_IO_Running and Slave_MySQL_Running. They should both have values of Yes. Of course, the real test is the execute a write query to a database on the master and see if the results appear on the slave. When replication is working, slave updates usually appear within milliseconds.

Recovering from a database error

If the slave database server loses power or the network connection, it will no longer be able to stay synchronized with the master. If the outage is short, replication should pick up where it left off. However, if a serious error occurs on the slave, the safest way to get replication working again is to:

1. Stop MySQL on the master and slave.
2. Dump the master database.
3. Reload the database on the slave.
4. Start MySQL on the master.
5. Start MySQL on the slave.

Depending on the nature of the problem, a full reload on the slave may not be necessary, but this procedure should always work.

If the problem is with the master database server and it will be down for a while, you can reconfigure the slave as the master by updating its IP address and /etc/my.cnf file. All Web servers then must be changed to read from the new master. When the old master is repaired, it can be brought up as a slave server and the Web

Note : MySQL 5 introduced a special storage engine designed for distributed databases called NDB that provides another option. For more in-depth information on MySQL clustering, see the MySQL Web site or High Performance MySQL by Jeremy Zawodny and Derek Balling.

Clusters make it possible to scale a Web application to handle a tremendous number of requests. As traffic builds, network bandwidth also becomes an issue. Top-tier hosting providers can supply the redundancy and bandwidth required for scaling.


This article is taken from here