Prerequisites
This post assumes the reader knows how to use the MySQL command line and has access to the servers they are trying to set up replication between. It also assumes that network access on the master has been configured correctly (including a user with replication permissions on the master).
Foreword
While this post contains nothing about MySQL replication that’s exclusive to setting it up on a Raspberry Pi. I’m just using this category of the blog to archive what I’ve managed to make my Raspberry Pi do.
Setting up replication on the master server
Configuring the master server to support replication is an easy task. All that is needed is either to enable binlog for the entire server. Make sure that a server ID for the master is set as well (server-id=1 or similar in the config).
log-bin=mysql-bin
It’s also possible to enable replication for specific databases only:
binlog_do_db=wordpress
Keep in mind that using binlog_do_db can lead to inconsistencies because it looks at what database is in use and not what database a query acted upon. Thus, enabling binlog for the entire server is the recommended approach.
Getting data from the master
Getting data from the master is easy, simply flush all tables and lock the database, after that get the status of the master.
FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
Note the output of this command somewhere and then proceed with dumping the data. Which should display something along the lines of:
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000544 | 1473773 | | | +------------------+----------+--------------+------------------+
Dumping the database can be done using mysqldump. With something like this:
mysqldump --add-drop-database --add-drop-table --hex-blob --databases| gzip -c --best >database-$(date -I).sql.gz
When the database has been backed up properly the table locks can be released and you can start transferring the backup to the slave server.
If the database is too large to import (as it was in my case) moving the data files over directly is another option (situated in /var/lib/mysql for Debian servers). Check the position of the log using the command above, shut down the server and move the files over. Then use “CHANGE MASTER TO” from the section below (after setting up a server ID of course).
Unlocking the tables can be done using:
UNLOCK TABLES;
Transfer the database dump to the slave server using your tool of choice (e.g. scp).
Configuring the slave server
After moving the database dump over to the slave we can get started with configuring the slave server.
Importing the data into mysql on the slave server is easy, it’s simply a matter of using gzip and piping it into mysql (make sure there’s not too much data for it to handle):
gzip -c -d database-2014-03-05.sql.gz | mysql -u root -p
If you transferred the data over in the previous step using scp or similar then extract it into /var/lib/mysql (unless skipped archiving and simply moved it there directly, which is fine).
After this, shut down the MySQL server and add a server ID to the slave. If importing data files directly then shut down the mysql server before importing.
# And set up a server ID (any 32-bit integer should do, as long as it doesn't match the master) server-id=70078
If you want to replicate only some databases that can be done using “replicate-do-db” although it has the same problem as binlog_do_db has (i.e. might introduce inconsistencies).
If you wish to have the host name of the slave appear when checking the status of the slave servers on master then add this as well:
report-host=hostname-here
Now, start the MySQL server again and run the following command in the mysql command prompt (note: it’s using the information from the SHOW MASTER STATUS above):
CHANGE MASTER TO MASTER_HOST='master.address.here', MASTER_USER='replicationuser', MASTER_PASSWORD='secritpassword', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000544', MASTER_LOG_POS=1473773, MASTER_CONNECT_RETRY=10;
And then run:
START SLAVE;
Everything should be up and running, you can check the status of the slave server by running the following on the slave:
SHOW SLAVE STATUS;
Errors such as connection failures and similar are listed in the output there. Finally, checking the slave status on the master can be done using these:
SHOW SLAVE HOSTS; SHOW PROCESSLIST;
And there you have it, MySQL replication set up on a RPi. :3