Friday, August 31, 2012

MySQL Master-Master Replication

Recently I was assigned the task of building a backup control center for one of our customers.  One of the requirements was that they needed to continue to have reports.  Reports were VERY important to this customer in particular because it determined how they got paid.  Apparently, if a fire broke loose and burned their whole operations center down and nothing else worked but their reports - then all is well.  

The current version of MySQL that's running now is 5.1.30 and it does included replication.

I found out that replication is actually very simple to setup.  Of course, I advise you to learn more about replication thoroughly because if something bad happens - say a hard-drive crashes - then you may need to get your databases in sync.  For my configuration I needed 2 servers to run in a Master-Master configuration.  This is because if a failure did occur and data was sent to the secondary server then at some point when the primary comes back online you want to get back in sync with the latest changes since it went down.  

MySQL Replication is handled by each master server keeping track of all database changes in its binary log.  Each slave connects to the master and requests the changes since it's last read.  Those changes go into a relay log on the slave.  The slave then executes those changes from the relay log as if it were originally executed there, including table changes, updates, deletes, etc.  



Setting up replication is easier if you're configuring a new server.  But in my case I had existing data that must by copied on the secondary server.  Here's how I set them up:

Connect to the master database and open the MySQL command-line utility. Put in the root password to log in.

On the master database, with MySQL running, create create a user account to be used by the slave for replication.
mysql > grant replication slave, replication client on *.* to repl@'144.243.58.104' identified by "repl"
Lock the tables using the following command.
mysql > flush tables with read lock;
Obtain the binary log coordinates on the master database.
mysql > show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | netreports   | manual,mysql     |
+------------------+----------+--------------+------------------+
Create a dump of the reporting database.
shell > mysqldump netreports --routines --master-data > dump.db
The 'master-data' option automatically appends the CHANGE TO MASTER statement required on the slave to start the replication process.

Unlock the tables.
mysql > unlock tables;
Copy the dump file to the slave server.

Restart the master database service.

Update the configuration of the slave's my.ini file with the slave configuration defined below.

On the slave database, with MySQL running, create create a user account to be used by the master for replication.
mysql > grant replication slave, replication client on *.* to repl@'144.243.58.106' identified by "repl";
Import the dump file.
shell > mysql --user=root --password=<password> --database=netreports < dump.db
Start the slave threads.
mysql > start slave;
After you have performed this procedure, the slave should connect to the master and catch up on any updates that have occurred since the snapshot was taken.


Master Configuration
Slave Configuration
server-id=1
log-bin=bin.log
log-slave-updates
log-bin-index=log-bin.index
log-error=error.log

relay-log=relay.log
relay-log-info-file=relay-log.info
relay-log-index=relay-log.index

auto_increment_increment = 10
auto_increment_offset = 1
master-host=144.243.58.104
master-user=repl
master-password=repl
master-port=3306
master-connect-retry=60

replicate-do-db=netreports
server-id=2
log-bin=bin.log
log-slave-updates
log-bin-index=log-bin.index
log-error=error.log

relay-log=relay.log
relay-log-info-file=relay-log.info
relay-log-index=relay-log.index

auto_increment_increment = 10
auto_increment_offset = 2
master-host=144.243.58.106
master-user=repl
master-password=repl
master-port=3306
master-connect-retry=60

replicate-do-db=netreports

I was amazed at how fast I got this up and going.  Tell me how your experience went.  Happy replicating!


No comments:

Post a Comment