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:
I also installed MySQL client and server packages, along with the Python libraries for MySQL:
You need to edit the MySQL server config file. Open it with this command:
Look for the bind address and change it to 0.0.0.0:
Further down the file, several lines need to be uncommented. Look for these lines and uncomment them:
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:
Now enter the MySQL shell with this command:
Create a database:
Add some sample tables and data to the database:
Grant permission to replicate data to the slave server:
Lock the database tables, and use the 'SHOW MASTER STATEMENT' command to show some information about the state of the database:
In another terminal window, use this command to dump the contents of the database in a file called my_db_dump.sql:
Now use this command to copy the database dump to the slave server:
Back in the MySQL shell, unlock the database tables:
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:
Open the MySQL shell, and create a database:
At the bash prompt, type this command to import data from the dump file:
Next, you need to set up the configuration file for the MySQL slave server:
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:
And uncomment the following variables:
Note that the server-id on the slave needs to be set to 2. You also need to add an extra line:
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:
Go back into the MySQL shell, and type this command to tell the MySQL server to use the master server:
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:
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
In the mysql shell on the slave, type this command to make sure the new record can be accessed on the slave:
Share this page: