Setting up MySQL Replication
Today I was asked to set up a MySQL Replication server to allow for failover on one of the services we operate.
A friend of mine also asked me to document the procedure as its been a while since he’d done it and couldn’t remember. So here goes….
The Setup
I’ll be doing all of this on FreeBSD 7.1 servers and I’ll be installing things from ports and then setting up the replication part.
Installing MySQL is pretty much a breeze, I do tend to do this first though
# cat <<EOF > /etc/make.conf
.if ${.CURDIR:M*/databases/mysql5*}
BUILD_OPTIMIZED=YES
.endif
EOF
to ensure that MySQL is built to run as quickly as possible. Then you have the simple commands to install the server, assuming you use ports-mgmt/portupgrade
# portinstall databases/mysql50-server
once that has been done on each server then we can begin setting up the replication service.
Instruct the Master
First we need to tell the master that it is a master, we also need to make sure that it’ll listen for slaves. So we need to add some things to the configuration file. You can use /etc/my.cnf which is pretty global but I’ve opted for /var/db/mysql/my.cnf
[mysqld]
bind-address = 192.168.0.10
server-id = 1
now, it will listen and it knows its the master.
Next we need to instruct it where to keep the binary log file for replaying to the slaves so in the same file
log-bin = mysql-bin
expire-logs-days = 20
max_binlog_size = 104857600
so the binary log file will be written to /var/db/mysql/mysql-bin, entries older than 20 days should be dropped and it shouldn’t grow bigger than 100 megabytes.
Finally we tell it exactly which databases we want to replicate, can you ignore this if you don’t want to be specific. Again in the same file
binlog-do-db = testdb
binlog-ignore-db = mysql,test
so only our testdb table will be binary logged, additionally mysql and test databases will be ignored.
Now you can restart your MySQL master server, to check that things are working you can connect to the server and run
mysql> SHOW MASTER STATUS;
+------------------+----------+---------------+-----------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+---------------+-----------------------+
| mysql-bin.000001 | 98 | testdb,testdb | mysql,test,mysql,test |
+------------------+----------+---------------+-----------------------+
1 row in set (0.01 sec)
and you should get something like above. You can also check to make sure the server is listening for connections with
# sockstat | grep mysql | grep tcp4
mysql mysqld 90119 13 tcp4 192.168.0.10:3306 *:*
Creating a replication user
So we need to setup an account on the master server for the slave to use to authenticate itself. To do this we create a REPLICATION SLAVE using the following MySQL statement
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'slavehost' IDENTIFIED BY 'slavepass';
we’ll set these values on the slave as well so that it can authenticate to the master.
Instructing the Slave
Again I’ll be keeping my configuration in /var/db/mysql/my.cnf, so for the slave we need to tell it that it isn’t top of the food chain and also who is. To do this we add
[mysqld]
bind-address = 192.168.0.11
server-id = 2
to tell the Slave to listen for connections, this is so that external services which perhaps only need read access to the database can use the slave and save the master for writes.
Next we must inform the Slave of its masters address and its authentication credentials for use when addressing the Master
master-host = 192.168.0.10
master-user = slave
master-password = slavepass
master-connect-retry = 60
Finally as we only want one database to be replicated, in my case at least, add the following line to the configuration file
replicate-do-db = testdb
so that only the testdb database will be replicated.
Copying the database to the Slave
Firstly we need to know where in the binlog we are at the moment, so on the master server run the following command
mysql> SHOW MASTER STATUS;
+------------------+----------+---------------+-----------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+---------------+-----------------------+
| mysql-bin.000001 | 327128 | testdb,testdb | mysql,test,mysql,test |
+------------------+----------+---------------+-----------------------+
remember the File and Position values as we’ll need these to resynchronised the databases once the slave has a copy of the data.
Using mysqldump create a copy of the master database to put onto the slave
# mysqldump --opt --databases testdb > /root/master-databases.sql
Now copy the /root/master-databases.sql file to the slave and then import it, assuming you copied it to /root/master-databases.sql on the slave as well
# mysql < /root/master-databases.sql
Now we need to synchronise the two copies. On the slave load up the mysql client
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST = '192.168.0.10',
-> MASTER_USER = 'slave_user', MASTER_PASSWORD = 'slave_pass',
-> MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 327128;
mysql> START SLAVE;
and the changes will be synchronised between the two servers.
A quick test
Now its a good idea to make some test insertions into the master database and then check to see if they exist on the slave as well. I’ll leave this part up to you…
