Ranthologia: Rants on the Big Web 2.0

Note: This post is the first of a 2-part entry. It’s a bit too lengthy to post the entire tutorial here

A little intro on MySQL replication, from the official MySQL manual:

“MySQL 3.23.15 and up features support for one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves.”

This will not be another tutorial on implementing MySQL replication, you could find those all across the internet. Furthermore, they cover this process in more detail than I would in this article. As the title would suggest, I’ll compress the whole shebang in just a few easy steps.

First off, 2 stages are involved in replication: In the MASTER, and in the SLAVE. To get you up and running in setting it all up, here are some things to keep in mind:

1. Both database servers should be of the same build and version. Otherwise, make sure the SLAVE has the latest version than the MASTER.

2. It is important that BOTH the MASTER and the SLAVE be in the same state at the time that you will be setting up replication. When you say SAME STATE, that means you’ll have to dump a copy of your master database and dump it onto your slave, making it an exact copy of the master. Your setup is likely to fail if you don’t stick to this rule.

3. Do your updates, alterations and removals from the MASTER. The Slave is just a backup so just keep it intact. Preferrably, no operations should be performed on the slave server.

And now, on with the show!

Step 1: Configure the MASTER

1. In your /etc/mysql/my.cnf file, in the [mysqld] section, enable networking on the master by commenting out the following lines (if available)

#skip-networking
#bind-address = 127.0.0.1

2. Insert the following lines:

# enable binary logging; in Red Hat, no need to provide arguments
log-bin = /var/log/mysql/mysql-bin.log
# database to replicate
binlog-do-db = nameofyourdb
# server id; 1 indicates master
server-id = 1

3. Restart the MYSQL server.

4. Create a user with replication privileges. This user will be used by the slave to access the master

GRANT REPLICATION SLAVE ON *.* TO ’slave_name’@'hostname’ IDENTIFIED BY ‘password_here’
FLUSH PRIVILEGES;

5. Get master status by issuing the following command

USE yoursampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Take note of the output of the last command, esp. FILE, POSITION, BINLOG_DO_DB.Log out of MySQL.

6. Create a dump of the master database and transfer this to the slave server. Don’t import the dump into the slave yet!

7. Log in to MySQL again and unlock the tables by issuing the following commands:

UNLOCK TABLES;
quit;

That takes care of the master. Now on to the slave!