Add Slave to MySQL Master-Master Replication

After the previous MySQL Master-Master Replication, we recommended to add slaves to Masters, to reduce load on the masters.

In this article we will describe how add slave to one of our masters. You can continue to add slave(s) to another master as the this process.

Background

We will continue the previous post, and assume we have two masters - Server A and Server B, and we will add Server C as slave for Server A.

We are using AMI (Amazon EC2) distribution, but you can use your favor Linux distribution.

Server C

our servers are clean and fresh, so first we will install MySQL:

# sudo yum install mysql-server

Let's configure our new MySQL:

# sudo nano /etc/my.cnf

Under [mysqld] section add the next configuration snippet:

server-id = 11
log_bin = /var/log/mysql/mysql-bin.log

Let's start our new MySQL server:

# sudo service mysqld start

And set our MySQL admin password:

# sudo mysqladmin -u root password 'verystrongpass'

After our MySQL ready to be slave (do not take things out of context), let's configure it as well. Login to our MySQL:

mysql -u root -p

We first stop the MySQL slave operations:

> STOP SLAVE

We will now dump from Master Server (A) directly to our new MySQL slave:

# mysqldump -h SERVER-A-IP-OR-KNOWN-HOST-NAME -u root -p --all-databases > master.dump

Note: You will need root permissions from MySQL server A to Server C. If you don't have take dump from Server A (see next chapter).

After you have the dump, import it:

# mysql -u root -p < master.dump

Server A

On Server A we need to do few things.

First, we need the Master server send to its slave(s), operations that come from another Master. By default, MySQL doesn't do it, unless you declare it.

The property that will do this trick is log-slave-updates, which you can find wide explanation about this here. This variable is a must, when a slave is also to act as a master to other slaves in chained replication

Therefor, open the my.cnf:

# sudo nano /etc/my.cnf

Add it to the [mysqld] section:

[mysqld]
log-slave-updates=1

For apply this configuration, we will need to restart the MySQL service:

# sudo service mysqld restart

In addition, we need to query the Master to know its transaction last sync file and position. Let's login to MySQL:

# mysql -u root -p
> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 4955616 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Pay Attention! mysql-bin.000003, 4955616 - will be used on Server C slave configuration.

Finally, if you need to dump the data to Server A:

mysqldump -u root -p --all-databases > master.dump

Now you can move master.dump file to Server C, and import it.

(Back to) Server C

The last thing we need to do is sync the slave and start it again. Let's login to MySQL:

# mysql -u root -p

Sync MySQL slave to its Master (Server A):

> CHANGE MASTER TO MASTER_HOST = 'SERVER-A-IP-OR-KNOWN-HOST-NAME', MASTER_USER = 'replicator', MASTER_PASSWORD = 'reprep', MASTER_LOG_FILE = 'mysql-bin.000003', MASTER_LOG_POS = 4955616;
Query OK, 0 rows affected (0.11 sec)
>SLAVE START;

Now your slave is full configured and running.

Go into MySQL Master on Server A and insert some rows:

> use testrep;
> INSERT INTO reptable (name) VALUES ('Master-Slave to Master-Master');
> SELECT * FROM reptable;
+----+-------------------------------+
| id | name |
+----+-------------------------------+
| 2 | MySQL is very cool |
| 3 | MySQL Replication |
| 5 | Master-Slave to Master-Master |
+----+-------------------------------+
3 rows in set (0.00 sec)

Do the same on MySQL Master on Server B:

> use testrep;
> INSERT INTO reptable (name) VALUES ('Cool! Master-Master-Slave');
> SELECT * FROM reptable;
+----+-------------------------------+
| id | name |
+----+-------------------------------+
| 2 | MySQL is very cool |
| 3 | MySQL Replication |
| 5 | Master-Slave to Master-Master |
| 6 | Cool! Master-Master-Slave |
+----+-------------------------------+
4 rows in set (0.00 sec)

Go into your new slave and you supposed to see the new values:

> use testrep;
> SELECT * FROM reptable;
+----+-------------------------------+
| id | name |
+----+-------------------------------+
| 2 | MySQL is very cool |
| 3 | MySQL Replication |
| 5 | Master-Slave to Master-Master |
| 6 | Cool! Master-Master-Slave |
+----+-------------------------------+
4 rows in set (0.00 sec)

You can see that the slave receive both actions from both Masters, so you are done.

Blog