Skip to navigation

Setting up Master-Slave MySQL Replication

There are times when one MySQL server isn't enough.  If you have a high traffic web site on a cluster of several web servers, you will need a cluster of database servers to handle all the SQL queries generated by the web servers.  One of the most commonly used methods of synchronizing servers in a MySQL cluster is replication.  One server is set up as a master, and the other servers in the cluster are slaves.  Any changes made to the master database will be replicated on the slave servers. 

I'm going to build a cluster of two MySQL servers, one master, and one slave.  I started with two fresh installations of Raspbian on each Banana Pi.  I have a monitor, mouse and keyboard attached to one of them, and I connected to the other Banana Pi using ssh.

Both servers have static IP addresses.  The master's IP address is 192.168.0.30, and the slave's IP address is 192.168.0.31.  

I started by making sure both servers were up to date by running this command on both servers:

sudo apt-get update && sudo apt-get upgrade -y

I also installed MySQL client and server packages, along with the Python libraries for MySQL:

sudo apt-get install mysql-server mysql-client python-mysqldb -y

Set up the Master Server

You need to edit the MySQL server config file.  Open it with this command:

sudo nano /etc/mysql/my.cnf 

Look for the bind address and change it to 0.0.0.0:

bind-address = 0.0.0.0

Further down the file, several lines need to be uncommented.  Look for these lines and uncomment them:

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = my_db

Type control-O and press return to save the changes you've made, and type control-x to exit from nano.  Restart the MySQL server:

sudo service mysql restart
[ ok ] Stopping MySQL database server: mysqld.
[ ok ] Starting MySQL database server: mysqld . ..
[info] Checking for tables which need an upgrade, are corrupt or were 
not closed cleanly..

Now enter the MySQL shell with this command:

mysql -u root -p

Create a database:

mysql> CREATE DATABASE my_db;
Query OK, 1 row affected (0.00 sec)

mysql> USE my_db

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Add some sample tables and data to the database:

mysql> CREATE TABLE users (username TEXT, password TEXT, email_addr TEXT, joined DATETIME);
mysql> COMMIT;

mysql> INSERT INTO users values('Steve', '123456', 'myemail@addr.com', CURRENT_DATE());
mysql> COMMIT;

Grant permission to replicate data to the slave server:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'192.168.0.31' IDENTIFIED BY 'mypassword';
mysql> FLUSH PRIVILEGES;

Lock the database tables, and use the 'SHOW MASTER STATEMENT' command to show some information about the state of the database:

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     4711 | my_db        |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

In another terminal window, use this command to dump the contents of the database in a file called my_db_dump.sql:

mysqldump -u root -p --opt my_db > my_db_dump.sql

Now use this command to copy the database dump to the slave server:

scp ./my_db_dump.sql bananapi@192.168.0.31:./

Back in the MySQL shell, unlock the database tables:

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

Set up the Slave Server

The next step is to set up the slave server.  I started by setting up a copy of the database on the slave server and importing the database dump from the master server.  

Log into the slave server via ssh:

ssh bananapi@192.168.0.31

Open the MySQL shell, and create a database:

mysql -u root -p

mysql> CREATE DATABASE my_db;
Query OK, 1 row affected (0.00 sec)

mysql> QUIT;
Bye

At the bash prompt, type this command to import data from the dump file:

mysql -u root -p my_db < my_db_dump.sql 

Next, you need to set up the configuration file for the MySQL slave server:

sudo nano /etc/mysql/my.cnf 

Most of the changes that you need to make are the same as the changes for the master.  You need to change the bind-address:

bind-address = 0.0.0.0

And uncomment the following variables:

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = my_db

Note that the server-id on the slave needs to be set to 2.  You also need to add an extra line:

relay-log = /var/log/mysql/mysql-relay-bin.log

As before, type control-O and press return to save the changes you've made, and type control-x to exit from nano.  Restart the MySQL server:

sudo service mysql restart
[ ok ] Stopping MySQL database server: mysqld.
[ ok ] Starting MySQL database server: mysqld . ..
[info] Checking for tables which need an upgrade, are corrupt or were 
not closed cleanly..

Go back into the MySQL shell, and type this command to tell the MySQL server to use the master server:

CHANGE MASTER TO MASTER_HOST='192.168.0.30', MASTER_USER='slave_user', MASTER_PASSWORD='mypassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=4711;

This command tells the slave what username and password it should use when it connects to the master server, and it uses the infomation from the SHOW MASTER STATUS command to specify the position of data in the log file on the master.

Start replicating the master database on the slave with this command:

START SLAVE;

Test Replication

Test that replication is working by creating a new user on the master, and then checking to see if you can search for that record on the slave.  

On the master in the mysql shell, type this command to add a record to the user table

INSERT INTO users values('Dave', '123456', 'myemail@addr.com', CURRENT_DATE());
COMMIT;

In the mysql shell on the slave, type this command to make sure the new record can be accessed on the slave:

SELECT * FROM my_db.users WHERE username='Dave';
+----------+----------+------------------+---------------------+
| username | password | email_addr       | joined              |
+----------+----------+------------------+---------------------+
| Dave     | 123123   | e-mail@addss.com | 2014-09-02 00:00:00 |
+----------+----------+------------------+---------------------+
1 row in set (0.00 sec)

 

Share this page:

comments powered by Disqus