Establishing a MySQL master-slave replication relationship
Posted by creining | Filed under Linux/BSD
I recently set up a second IDS-backend MySQL (3.23.58) database and Sguil server. I wanted to insure against the primary database server reaching some unfortunate fate and not having anywhere to INSERT events that Snort is generating. The plan was to set up replication between my primary (master) database server and the secondary (slave) database server and in the event that the master becomes unavailable to have Barnyard point at the slave. In order to accomplish this I did the following (keep in mind I am by no means a MySQL expert, so there may be easier ways to do this):
Preparation:
1) Pick an ID for each server. My master will be 1 and my slave will be 2.
2) Add an account on my master for the slave:
mysql> GRANT FILE ON *.* TO 'repl'@'slavehostname' IDENTIFIED BY 'slavepasswd';
3) My slave is an untouched install at this point. The operating system in my case sets a blank password for the ‘root’ MySQL account so I correct that by setting a password:
mysql prompt> UPDATE user SET Password=PASSWORD('dbpasswd') WHERE user='root';
mysql prompt> FLUSH PRIVILEGES;
4) A repl user is created in MySQL on the slave:
mysql prompt> GRANT ALL PRIVILEGES ON sguildb.* TO repl@localhost IDENTIFIED BY 'replpasswd' WITH GRANT OPTION; mysql prompt> FLUSH PRIVILEGES;
5) On the master I add these lines to the MySQL configuration file, my.cnf:
server-id=1 log-bin
6) On the slave I add these lines to the my.cnf file:
server-id=2 master-host=masterhostname master-user=repl master-password=slavepassword
7) Next I shut down the master in order to get a backup. This can be accomplished other ways, such as keeping the server up and doing a FLUSH TABLES WITH READ LOCK; and UNLOCK TABLES; on all the tables, in any event it is very important that the master and slave start with the same data:
mysqladmin -p -u root shutdown
Next I compress and transfer the sguildb directory on the master to the slave:
tar czvf sguildb.tgz sguildb/ ; scp sguildb.tgz ...
9) Now I shut down the slave using the aforementioned command performed on the master.
10) Then I uncompress the sguildb.tgz file in the correct data directory on the slave.
11) Lastly, on the master I start the MySQL service back up and do the same on the slave MySQL server.
Disaster Recovery:
1) The master database server melts
2) At this point I have two options to start using the slave. I can retrieve all the Barynard configuration files from the sensors (or I can use the last ones that I fetched centrally and saved) and programatically substitute the master’s name with that of the slaves. There should be two instances of the master’s name in the Barnyard configuration file, one to define the database server and the other to define the sguild server, in my deployment they are one in the same. Then I can push the modified Barnyard configuration file out to all the sensors and it will be restarted automagically. I have that infrastructure designed and built already for sensor management (others may not). Or, since I am using the name of the master server in the Barnyard configuration file it would only require a change in DNS to point to that name to the slave’s IP, which would be efficient as well.
3) Barnyard should now be doing INSERTS directly to the slave.
Recovery back to the master should be fairly straightforward. It is much a reverse of the process it took to set up the slave.
That should be about it. This document at mysql.com was helpful in setting up the replication. Also, I’ve learned how robust that other database software, PostgreSQL, really is. For instance, MySQL databases do not support Multi-Version Concurrency Control which forgoes LOCKS for tasks like a backup. Oracle uses MVCC too, but we all know Oracle isn’t free. If anyone has any suggestions on my process please get in touch at creining at packetfu.org.
Comments are closed.