Category: Projects

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

Disclaimer: Most of the text here will serve as a reminder to me of how I did things in case something explodes later.

Prerequisites: This blog post assumes the reader knows how to set up a zone using bind as well as the general syntax of bind’s configuration files.

Background

Recently, I got a Raspberry Pi to play around with and I decided to set up a secondary DNS server. While I have no real need for a secondary DNS server I figured it would be a good exercise to set it up. It also allows me to bring down the server for an upgrade without losing name resolution on the network so there’s really no reason not to do it.

To start off I installed Bind9 using

pacman -S bind

Configuring bind

Setting up the Pi as a secondary DNS server was easy.  It was just a matter of adding A-, AAAA-, and NS-records for it and then changing the configuration files of the master and slave to allow transfers between the two.

Which ended up being added as follows (to the chalamius.se zone):

chalamius.se. 3600 IN NS luna.chalamius.se.
luna.chalamius.se. 3600 IN A 192.168.x.x
luna.chalamius.se. 3600 IN AAAA

And a number of NS-records for the other zones (DHCP hosts and reverse DNS).

Next, configuring zone transfers was as simple as adding the host to the ACL for transfers I have set up (but it works just as well with addresses).

To add a host to allow-transfer one simply adds the following (between the omitted parts, […] signifies omitted parts of the config) to the zone-declaration in the master’s config file:

zone "chalamius.se." IN {
    type master;
    [...]
    allow-transfer {
        [...]
        192.168.x.x;
    }
    [...]
}

Configuring the Pi as a slave is simply a matter of adding a slave zone-declaration to the config file (one for each zone that it’s supposed to handle), which is done as follows:

zone "chalamius.se." IN {
    type slave;
    file "/var/lib/named/chalamius.se.hosts";
    masters {
        192.168.x.y;
    };
    allow-transfer { none; };
};

Verification

Verifying that it works is simple, issue a name resolution request and see if the response is positive. To verify that transfers are working one simply adds a host to one of the zones that are configured for transfers, update the serial and reload the zones on the master.

Verifying that transfers work without doing that (assuming bind doesn’t do it automatically upon reloading the configuration file, which it should) can be triggered using (replace with your zone of choice):

dig axfr chalamius.se

Verifying that name resolution works can be done by using:

dig +norecurse @192.168.x.x newhost.chalamius.se

Next up

Assuming I manage to find the energy to write something on this  blog sometime soon I’ll put up a post on how to configure MySQL replication using a Raspberry Pi.