MySQL is the favorite database utility for opensource software. Nagios and Nagios XI use MySQL to store data for retrieval by end-users. The retrieval process becomes evident when you install NDOutils and create organizational structure diagrams. However, why MySQL replication?
Replication means exactly what is stated in the dictionary. MySQL provides asynchronous replication between a master and one or more slaves. This article will explain a method I use to create slave instances of MySQL in support of Nagios. These instruction are designed to create a slave instance that we can fail-over to in the future if our master instance and or host fails.
Our scenario involves two physical hosts (in my case I have created two virtual hosts using Xen). One host will house the master (node1) and the other the slave (node2). If you already have an instance of MySQL running in support of Nagios, this procedure will enable you to create a slave without interrupting your current installation and established permissions.
NOTE: Read through these instructions to make sure you understand them, because you will want to perform this procedure as quickly as possible. Nagios is going to be trying to writing to the master server while the instance is stopped during our data migration process.
- Identify node1.
- On node1 log into MySQL: sudo mysql (provide password).
- Create a replication user: CREATE USER ‘repl’@’%.your domain.com’ IDENTIFIED BY ‘your password'; . Make sure you set up a separate account for the replication user, because the userid and password will be in plain text in your master.info and my.cnf files.
- Grant the repl user rights: GRANT REPLICATION ON ‘*.*’ TO ‘repl’@’%.your domain.com'; The reason why we use the ‘*.*’ is to allow replication of all databases and hosts on the master instance. We use ‘%.your domain.com’ so we can create a slave anywhere on our networks.
- Once that has been completed we need to proceed to node2 in order to modify the my.cnf file. If MySQL is running on the potential slave instance, stop MySQL.
- Set the server-id = 2 (or the next number in your series of slaves).
- Set the master-host = node1 hostname (I use the IP address of the master host).
- Set the master-user = repl.
- Set the master-password = your password (remember, I mentioned earlier that the userid and password for replication will be in plain text).
- Set the log-bin = mysql-bin (you need to set this flag in order to have the ability to fail-over to this MySQL instance).
- Now you want to start node2’s instance of MySQL. You will receive some warnings, but the instance should start and try to begin replication.
- Log into node2’s instance of MySQL: sudo mysql and run: “SHOW SLAVE STATUS;” You will see that the MySQL instance is now a slave.
- Now, run “STOP SLAVE;” This will stop the slave on node2 in preparation for our next steps. You can quit the MySQL session on node2 and return to a command prompt.
- On node1’s r instance of MySQL run: “FLUSH TABLES WITH READ LOCK;” This will prevent the tables from updating so we can obtain a snapshot.
- Log into another session on node1 so we can perform a backup of the databases. From the command prompt run: mysqldump –all-databases –master-data > mydbdump.db. The “–master-data” flag will capture all our required log markers.
- We will now restore the data from node1 to node2: sudo mysql < mydbdump.db. Remember, we froze node1, so we are restoring up-to-date data to node2.
- Once the restore has completed, log into the MySQL instance on node2 and run: “START SLAVE;”
- On node1 run: “UNLOCK TABLES;” This will begin the master’s data processing once more.
That’s all there is too it. I use a restore because I want my slave (node2) to spend as little time as possible updating or syncing to node1. This will also save on server load and network bandwidth. To test if replication is working properly perform this test:
- node1: Log into MySQL and run: “CREATE DATABASE testing;”, then “SHOW DATABASES;” and you should see the new database “testing”.
- node2: Log into MySQL and run: “SHOW DATABASES;” if replication is working you will now see the “testing” database.
I hope that helps with your MySQL instance on Nagios or Nagios XI. For more information on MySQL replication please visit: MySQL Replication .
Comments and or other solutions? You can always shoot me an email for questions at email@example.com.