Database Replication Setup Manual: FMYiRC V1.00

From FMYiRCWiki

Jump to: navigation, search

Database Replication Setup Manual: Freemed-YiRC V1.00


Contents


Summary

This document will explain how to set up two Freemed-YiRC servers to replicate or mirror the Freemed-YiRC database in real-time. This is highly beneficial, it provides an agency with redunandcy. If either server fails (i.e., experiences a hardware failure, power outage, etc...), then the other server can be used almost immediately to resume work. This is also very helpful during equipment upgrades or replacements. If a server is to be replaced, users can be instructed to use the other server. The server can be replaced without having to rush it (which any IT person will love).

NOTES:

  • This document is highly technical!!!
  • This process was tested on servers running Mandriva Linux 2007.1 (MySQL 5.0.37)
  • This process does not copy Freemed-YiRC software changes. If you make software changes to the Freemed-YiRC source code, you must copy those changes manually to all servers!

Pre-Requisites

  • In order to begin this process, several conditions must be met:
    • Firstly, you must have two servers. While it's recommended the servers be roughly similar in speed and specs, it's not required. An agency may use a slower server (including a previous generation Freemed-YiRC server which may have been replaced with a newer, faster one) for redundancy. If a slower server were used, it will obviously be slightly frustrating if a user had to use it, however, that frustration should be countered with the fact that you may still work whereas if you were only using one server, the whole system would be down during that time!
    • The servers must be loaded with identical operating system versions and MySQL database software versions. This is important. While technically this may not be required by MySQL, it's been my experience that using identical versions will make for a far less frustrating experience! Therefore, if you are going to use an older server as the second Freemed-YiRC, say a server that may have been used for something else, or even for Freemed-YiRC, you must ensure that it is re-loaded with the same exact software that the primary server is loaded with!!!
    • Both servers must be on the same network. While it may be possible to set up replicating servers across the Internet, this is not recommended unless you have VERY FAST Internet connections at BOTH server locations!!!
    • Servers should not be stored in the same room. The purpose of having multiple servers is to protect the data. If both servers are in the same room and a fire were to break out, or even a circuit breaker were flipped, then neither server may be accessible. Placing the servers in different parts of a building is important. If the second server can be placed on a different floor of a building on the complete opposite end, this would provide for a great deal of fault tolerance. However, please ensure that the second server location is SECURE! Freemed-YiRC can contain HIPAA related information, so the location must be very restricted. And as always, a very good battery backup unit should be used too!!!
    • Network port 3306 must be open in the firewall on both machines. If you loaded Freemed-YiRC using the Helper CD, this was done for you.
    • In order to make this process more painless, the databases on both servers should be pre-synchronized BEFORE starting MySQL database replication. To do so, follow these steps. You must have root administrative privileges to perform all steps in this document!
      • On your primary server, create a database dump of the Freemed-YiRC database. Be sure to keep this file safe, as if there are any problems during this procedure, this file will be save your professional life! You may also wish to copy this file to another computer (other than the two involved in this process) for safe keeping!!! 
        • mysqldump freemed_yirc > /tmp/fmyirc_primary_db_dump.sql
      • Next, copy this file to the secondary server.
      • On the secondary server you must now delete any existing Freemed-YiRC database and import the database dump which was just created.
        • Start the MySQL command-line client: 
          • mysql
        • Delete any existing Freemed-YiRC database 
          • drop database freemed_yirc;
        • Now create a new blank Freemed-YiRC database: 
          • create database freemed_yirc;
        • Exit the MySQL command-line client: 
          • exit;
        • Finally, import the database dump from the command-line. Be sure you are in the same directory as the database dump file (fmyirc_primary_db_dump.sql). 
          • mysql freemed_yirc < fmyirc_primary_db_dump.sql
        • ALL SET! The Freemed-YiRC databases on both servers should now be the same. You are now ready to configure the servers for database replication.

Procedure

  • This procedure will require down-time. No users should be accessing either server during this process!
    • To ensure no one will be accessing these servers during this process, it is recommend you stop the software web server process. Typically Linux uses the Apache web server. Therefore you would issue the following command on a RedHat Linux based distribution (including Mandrake/Mandriva). You must issue this command on BOTH servers!!! 
      • /etc/rc.d/init.d/httpd stop
    • After issuing this command try to access both servers using a web browser. It should not work.

Setting Up The Primary Server As A Master

  • On the Primary Server:
    • Open the file /etc/my.cnf in a file editor (vi, kwrite, etc...)
    • Search for: log-bin (may also be log-bin=mysql-bin). This will be located somewhere in the [mysqld] section of the file.
      • If you already have a line for this, make sure it is NOT commented, i.e. the line does not start with a pound sign (#). If it does, remove it the pound sign.
      • If you do not have a line for this, add it in the [mysqld] section of the file.
    • Add the following lines below the log-bin line: 
binlog-do-db=freemed_yirc
binlog-ignore-db=mysql
binlog-ignore-db=test
slave-net-timeout = 30
    • Search for: server-id. This also will be located in the [mysqld] section of the file.
      • If you already have a line for this, ensure it is set to 1 on the primary server.
      • If you do not have a line for this, add it under the lines just added. 
      • server-id=1
    • Save the file and exit the file editor.
    • Next, start the MySQL command-line client: 
      • mysql
    • Issue the following command, which gives MySQL replication capability. Replace the text SERVER2IP with the IP address of the secondary server. 
      • grant replication slave on *.* to 'replication'@SERVER2IP identified by 'slave';
    • Exit the MySQL command-line client: 
      • exit;
    • Restart the MySQL server software 
      • /etc/rc.d/init.d/mysqld restart

Setting Up The Secondary Server As A Slave

  • On the Secondary Server
    • Open the file /etc/my.cnf in a file editor (vi, kwrite, etc...)
    • Search for: server-id. This also will be located in the [mysqld] section of the file.
      • If you already have a line for this, ensure it is set to 2 on the secondary server.
      • If you do not have a line for this, add it under the lines just added. 
      • server-id=2
    • Next, add the following lines below the above server-id line. Replace the text SERVER1IP with the IP address of the primary server. 
master-host = SERVER1IP
master-user = replication
master-password = slave
master-port = 3306
master-connect-retry = 30
    • Save the file and exit the file editor.
    • Restart the MySQL server software 
      • /etc/rc.d/init.d/mysqld restart
    • Next, start the MySQL command-line client: 
      • mysql
    • Issue the following commands: 
      • start slave;show slave status\G;
    • One of the first lines should read: 
      • Slave_IO_State: Waiting for master to send event
    • If this DOES NOT say Waiting for master to send event, i.e., if it says Connecting to master, THERE IS AN ERROR AND IT NEEDS TO BE CORRECTED BEFORE CONTINUING! (You may find the log files in /var/log/mysqld to be helpful here)
    • Exit the MySQL command-line client: 
      • exit;

Setting Up The Secondary Server As A Master

  • On the Secondary Server
    • Open the file /etc/my.cnf in a file editor (vi, kwrite, etc...)
    • Search for: log-bin (may also be log-bin=mysql-bin). This will be located somewhere in the [mysqld] section of the file.
      • If you already have a line for this, make sure it is NOT commented, i.e. the line does not start with a pound sign (#). If it does, remove it the pound sign.
      • If you do not have a line for this, add it in the [mysqld] section of the file.
    • Add the following lines below the log-bin line: 
binlog-do-db=freemed_yirc
binlog-ignore-db=mysql
binlog-ignore-db=test
slave-net-timeout = 30
    • Save the file and exit the file editor.
    • Next, start the MySQL command-line client: 
      • mysql
    • Issue the following command, which gives MySQL replication capability. Replace the text SERVER1IP with the IP address of the primary server. 
      • grant replication slave on *.* to 'replication'@SERVER1IP identified by 'slave2';
    • Exit the MySQL command-line client: 
      • exit;
    • Do NOT restart the MySQL server software at this point!

Setting Up The Primary Server As A Slave

  • On the Primary Server
    • Open the file /etc/my.cnf in a file editor (vi, kwrite, etc...)
    • Search for: server-id=1
    • Under the above line, add the following lines. Replace the text SERVER2IP with the IP address of the secondary server. 
master-host = SERVER2IP
master-user = replication
master-password = slave2
master-port = 3306
master-connect-retry = 30

Final Setup

  • Stop the MySQL softare service on BOTH servers! 
    • /etc/rc.d/init.d/mysqld stop
  • Delete the master.info file in your MySQL software database directory on BOTH servers
    • We do this because if there was any type of error in the above steps, even if it has been corrected, this file needs to be updated. So, to be safe, we'll just delete it. IT WILL GET RECREATED AUTOMATICALLY WHEN WE DO THE NEXT STEP!
    • You must find the location of your MySQL database. If you are using the Freemed-YiRC Helper CD, it will be located in /home/database. If you are using a stock install of Mandriva Linux, it may be in /var/lib/mysql. 
    • rm /home/database/master.info
  • Start the MySQL softare service on BOTH servers! 
    • /etc/rc.d/init.d/mysqld start
  • At this point, take a short break. At least two minutes. The reason being is that, unless you started the service AT THE EXACT SAME TIME, one of the servers will initially fail to connect to it's master (because it wasn't running at that moment). However, it will retry every 60 seconds. So, within two minutes, both servers should be talking.
  • Verify that all is well on BOTH servers:
    • Start the MySQL command-line client: 
      • mysql
    • Check the slave status (i.e., ensure that the server is receiving data from the other) 
      • start slave;show slave status\G
      • One of the first lines should read: 
        • Slave_IO_State: Waiting for master to send event
    • If all looks well, we can then begin a test!

TESTING!

  • Let's do some tests to ensure that database replication is now working.
  • On BOTH Servers:
    • Start the MySQL command-line client and go into the Freemed-YiRC database. 
      • mysql freemed_yirc
    • Run this query to ensure that the same number of records exist in a given table: 
      • select count(id) from fy_sysaudit;
  • On the Primary Server: 
    • insert into fy_sysaudit () VALUES ();
  • On BOTH servers:
    • Run this query to ensure that the same number of records exist in a given table. It should have been incremented by one, ON BOTH SERVERS. 
      • select count(id) from fy_sysaudit;
    • Exit the MySQL command-line client: 
      • exit;
  • Real test time! Let's turn the web server software back on and do some testing.
  • On BOTH Servers:
    • Start the web server software 
      • /etc/rc.d/init.d/httpd start
    • Go back into the MySQL command-line client, into the Freemed-YiRC database. 
      • mysql freemed_yirc
    • Next, log into Freemed-YiRC like you normally would using a web-browser (on the primary server)
    • Run this query, on BOTH servers, to ensure that you are the last person to have logged in: 
      • select * from fy_sysaudit WHERE event_date IS NOT NULL ORDER BY event_date, event_time DESC LIMIT 1;
    • Then, log out of Freemed-YiRC like you normally would. Issue the same query. The results should be the same on both servers.
    • Another test would be to log into Freemed-YiRC on the Secondary Server using a web-browser and issue the above query again.
  • Assuming these tests worked, you should now be all set!!!

IF YOU HAVE A PROBLEM

This process can be very tricky. You must follow the above steps EXACTLY as described above. The order of events is very important. Remember:

  • We made a backup file of your primary server database! Regardless of what happens, this is your insurance policy.
  • The log files in /var/log/mysqld are you friends. They can be QUITE cryptic, but they will tell you what the problem is.


MAINTENANCE

It is imperative that you test replication from time to time. At least once a week. It is possible for these servers to de-synchronize themselves due to any number of factors... network issues, large data transfer, etc... Please check the log files in /var/log/mysqld regularly as well as test to ensure the same data is on both servers (the query of fy_sysaudit is a good one to conduct to check this).

A future version of Freemed-YiRC may include an automated monitor for this

HOW TO RE-SYNC A BROKEN CONNECTION

If your servers become de-synchronized, don't fret, here is what you do:

  • Shut down the MySQL and web server software services on BOTH Servers: 
/etc/rc.d/init.d/mysqld stop
/etc/rc.d/init.d/httpd stop<pre>
* '''On the Primary Server''', create a ''database dump''
** <pre>mysqldump freemed_yirc > /tmp/fmyirc_primary_db_dump.sql
  • Copy this file to the Secondary Server.
  • On the Secondary Server, issue this command from the directory where the database dump file is located: 
    • mysql freemed_yirc < fmyirc_primary_db_dump.sql
  • Restart the MySQL software service 'on BOTH Servers: 
    • /etc/rc.d/init.d/mysqld start
  • Wait two minutes for the databases to connect to each other.
  • Now follow the testing procedures in the above Procedure section under the TESTING section to ensure all is well.
  • When finished, ensure that you've started the web server software service! 
/etc/rc.d/init.d/httpd start


References

The bulk of this document was taken from this page and adapted for use with Freemed-YiRC: MySQL Master Master Replication - HowtoForge.

Retrieved from "http://freemed-yirc.familyandyouth.org/freemed-yirc/wiki/index.php/Database_Replication_Setup_Manual:_FMYiRC_V1.00"
Personal tools