Web Tech Tips

This MySQL replication example is specific to Red Hat Enterprise Server 5, so you may need to modify these directions on other flavors of Linux/UNIX.

For basic MySQL replication, there will be a master server and a slave server. In this example, we will use IP addresses for each of these servers. Let’s say:

Master server: 192.16.1.1

Slave server: 192.168.1.2

Step 1: set up user accounts

It’s best to create a new mysql user that will be solely responsible for the replication process. We’ll call this user “repl.”

First login to the master server, and then get to the mysql command line:

# mysql -u username -p

Enter your password when prompted.

At the mysql prompt:

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘repl’@’192.168.1.2′ IDENTIFIED BY ‘password’;

Be sure to replace ‘password’ with whatever you want. Note that we are granting these privileges to the ‘repl’ user on the SLAVE server (see the IP). We’ll repeat this on the slave server being sure to change the ip address:

Now login to the slave server, and then get to the mysql command line:

# mysql -u username -p

Enter your password when prompted.

At the mysql prompt:

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘repl’@’192.168.1.1′ IDENTIFIED BY ‘password’;

Be sure to replace ‘password’ with whatever you want. Note that we are granting these privileges to the ‘repl’ user on the MASTER server (see the IP).

Step 2: edit the mysql /etc/my.cnf file on the master server

On the MASTER server, edit the /etc/my.cnf file in the editor of you choice. Below is the an example my.cnf file. Your file may look different. Add only the bolded lines to your file. The other lines of your file should not have to be changed. These two lines will give your server and id and tell in to start binary logging.

[mysqld]
server_id=1
log-bin=/var/lib/mysql/logs/superfly-bin.log

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

–END FILE–

From the command line, restart mysql:

# service mysqld restart

Step 3: getting a snapshot of the current state of the database(s)

The next step involves temporarily preventing any data changes to any MySQL databases on your system, so be sure to warn any users. Start up the mysql command line client:

# mysql -u username -p

Enter your password when prompted, and then enter the following commands:

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS; (BE SURE TO NOTE THE ‘File’ and ‘Position’ VALUES–let’s say they have these values; ‘master-bin.00001′ and ’98′ respectively)

Back at the system command line:

# mysqldump –all-databases –master-data > dbdump.db

This will give you a snapshot of the current state of you databases. In a coming step, you will load this data in the slave server.

Back at the mysql prompt:

mysql> UNLOCK TABLES;

Now your users can make changes to your database(s). Copy (scp) the “dbdump.db” file to your SLAVE server.

Step 4: edit the mysql /etc/my.cnf file on the slave server

Now onto the SLAVE server, edit the /etc/my.cnf file in the editor of you choice. Below is the an example my.cnf file. Your file may look different. Add only the bolded lines to your file. The other lines of your file should not have to be changed.

[mysqld]
server_id=2
log-bin=/var/lib/mysql/logs/dolemite-bin.log
log-bin-index=/var/lib/mysql/logs/dolemite-bin.index
log-error=/var/lib/mysql/logs/dolemite-error.log
relay-log=/var/lib/mysql/logs/dolemite-relay.log
relay-log-info=/var/lib/mysql/logs/dolemite-relay.info
relay-log-index=/var/lib/mysql/logs/dolemite-relay.index
log-slave-updates
log-warnings

# Slave details
report-host=192.168.1.2

# Master details
master-host=192.168.1.1
master-user=repl
master-password=password (set this to whatever you set on the master server)
master-port=3306

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

– END FILE –

Step 5: Starting with a clean database, import your dump file:

# mysql -u username -p < dbdump.db

Enter your password when prompted.

Step 6: initialize slave with values from SHOW MASTER STATUS from master:

mysql> change master to MASTER_LOG_FILE=’master-bin.00001′,MASTER_LOG_POS=98;
mysql> start slave;

The databases should now be replicating…

ERRORS?

If you get the following error:

mysql> start slave;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

Then do this:

mysql> reset slave;
Query OK, 0 rows affected (0.06 sec)

mysql> reset master;
Query OK, 0 rows affected (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

That should fix your error.

NOTE:

If you want to check the status of your slave server, from the mysql prompt on the slave server:

mysql> SHOW SLAVE STATUS;

mysql Add new tag, database, mysql, replication, synchronization