Denie's Tech Blog

To setup Master-Slave Replication the first thing you need to do is create a user on the Master server that allows replication.

# mysql -u root -p
mysql> grant replication slave on *.* TO repl@”%” identified by ‘[repl password]‘;
mysql> quit

Be sure to replace [repl password] with the actual password you want to use. Also, you must ensure that your firewall has port 3306:tcp open, the default port for the mysql server service.

Next, exit your ini file (typically /etc/my.cnf on Linux servers) to start binary logging of the Master server. You may or may not want to use the last line to ignore changes to the mysql database since that is the database used for mysql configuration and permissions.

Under the [mysqld] heading add the following lines:

log-bin=mysql-bin
server-id=1
binlog-ignore-db=”mysql”

Restart your mysql server service.

Before we start copying changes, we want to make sure the data on each server is the same, so dump the data from the Master server and add it to the Slave server. This can easily be performed using mysqldump as follows:

# mysqldump -u root -p[password] [database]>/home/[user]/[database].sql

Be sure again to replace [password] with the actual password, [database] with the actual name of each database, one at a time.

Now that you have a snapshot of the data, get the binary position of the log file.

# mysql -u root -p[plain-text password]
mysql> SHOW MASTER STATUS;

The output should look something like this:

+——————+————————–+——————+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+————————–+——————+
| mysql-bin.000112 |        79 |              | mysql            |
+——————+————————–+——————+
1 row in set (0.00 sec)

Write down the filename and log position for use on the Slave server.

Next, copy the databases to your Slave server.

# scp /home/[user]/*.sql [Slave IP]:/home/[user]/

At this point we are done with the Master server. Now for the Slave.

Edit the Slave server’s mysql configuation file (typically /etc/my.cnf on Linux servers) to identify its server number, master host and user.

Under the [mysqld] heading add the following lines:

server-id=2
master-host = [IP of Master Server]
master-user = repl
master-password = [repl password]
master-port = 3306

Again, ensure you replace [repl password] with the actual repl user password, and [IP of Master Server] with the IP address of the Master server.

Insert the data from the Master server into the Slave server databases for each of the databases.

# mysql -p[password] [database] < /home/[user]/[database].sql

Be sure to replace [password] with the root users password, and [database] with each database’s name, one at a time.

Restart the mysql server service.

Now log in to Mysql and configure the Slave replication.

# mysql -u root -p
mysql> CHANGE MASTER TO MASTER_LOG_FILE=’[Filename written down]‘,
MASTER_LOG_POS=[Position written down];
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

Slave_IO_State status information should identify “Waiting for master to send event”. If it stops at “Connecting to Master” check your log file.

By default it is located in /var/log/mysqld.log but may be different on your system. Check your my.cnf file for the exact location of your log file.

Bookmark This Site

Posted on June 8th, 2009 by Denie
Filed under: MySQL