Nasser Heidari

2010-03-20

Mysql Master Master Repliction

Filed under: Miscellaneous — Nasser Heidari @ 11:39

I’m going to setup MySQL Multi-Master Replication, here are my systems information:

DB-1 : 172.20.1.6 —> (Master 1 / Slave 2)
DB-2 : 172.20.1.8 —> (Master 2 / Slave 1)
# uname -a
Linux DB-1.linax.wordpress.com 2.6.18-164.11.1.el5 #1 SMP Wed Jan 20 07:32:21 EST 2010 x86_64 x86_64 x86_64 GNU/Linux

also I’m using MYSQL community Edition, you can find rpm’s here :
# wget http://mirrors.bevc.net/mysql/Downloads/MySQL-5.1/MySQL-devel-community-5.1.41-0.rhel5.x86_64.rpm
# wget http://mirrors.bevc.net/mysql/Downloads/MySQL-5.1/MySQL-client-community-5.1.41-0.rhel5.x86_64.rpm
# wget http://mirrors.bevc.net/mysql/Downloads/MySQL-5.1/MySQL-server-community-5.1.41-0.rhel5.x86_64.rpm

OK, lets start:

I configured MySQL’s my.cnf file to use the tuning setup from the my-medium.cnf, which should give us good tuning parameters for our load at this point. Then I added the pieces necessary for replication in both DB-1 and DB-2:

Warning : I’ve changed default path of MYSQL data directory to /data/mysql, so you need to change it to your data directory. (default path is /var/lib/mysql/)

————————————————-
1. On Both Servers, you need to create a replication slave account in mysql:

mysql>GRANT REPLICATION SLAVE ON *.* TO 'replication'@'172.20.1.%' IDENTIFIED BY 'replication';
mysql>GRANT RELOAD ON *.* TO 'replication'@'172.20.1.%';
mysql>GRANT SUPER ON *.* TO 'replication'@'172.20.1.%';
mysql>FLUSH PRIVILEGES;

————————————————-

2. DB-1 my.cnf configuration :

server-id = 1
log-bin = /data/mysql/bin.log
log-bin-index = /data/mysql/log-bin.index
log-error = /data/mysql/error.log

relay-log = /data/mysql/relay.log
relay-log-info-file = /data/mysql/relay-log.info
relay-log-index = /data/mysql/relay-log.index
relay-log-space-limit = 6G

auto_increment_increment = 10
auto_increment_offset = 1
master-host = 172.20.1.8
master-user = replication
master-password = replication

replicate-do-db = MYDB
binlog-ignore-db = mysql
# log_bin_trust_function_creators = 1
expire_logs_days = 15
max_binlog_size = 10G

————————————————-
3. DB-2 my.cnf Configuration :

server-id = 2
log-bin = /data/mysql/bin.log
log-bin-index = /data/mysql/log-bin.index
log-error = /data/mysql/error.log

relay-log = /data/mysql/relay.log
relay-log-info-file = /data/mysql/relay-log.info
relay-log-index = /data/mysql/relay-log.index
relay-log-space-limit = 6G

auto_increment_increment = 10
auto_increment_offset = 2
master-host = 172.20.1.6
master-user = replication
master-password = replication

replicate-do-db = MYDB
binlog-ignore-db = mysql
# log_bin_trust_function_creators = 1
expire_logs_days = 15
max_binlog_size = 10G

————————————————-
4. Another thing that you should take care, when you setup Mysql Replication is Binary and Relay logs size.
as you can see in my configuration, I have set a 6GB space limit for relay-logs and 15 days expiry time for binary logs .
for purging binary-logs add a cron job like this :

0 1 * * * mysql -uroot -ppass -e "PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 15 DAY);"

this will purge your binary logs, older than 15 days .
————————————————-

I only wanted to replicate one database, so I listed it as the example shows with replicate-do-db. Since I intend on allowing for various nodes to be added, I set auto_increment_increment to 10, which will allow for 10 different servers, all of which could be read-write masters if I wanted them to be. In this case, my plan is only for 2, so this will give me a lot of room to grow. in other words :

When you have N master servers:

Set auto_increment_increment to N on each master.
Set each of the N masters to have a different auto_increment_offset, for example set to 1 in the first node, 2 in the second, and so on.

Using this two variables as described in the manual, you can ensure that all nodes in your replication array will use different sequences of auto-incrementing numbers. For example, using auto_increment_increment = 10 and auto_increment_offset=3, the numbers generated when inserting three records will be 3, 13, 23. Using 10, 7, you’ll get 7, 17, 27, and so on.

That’s it, now just start/restart mysql in both side.
you can check replication status with this mysql commands:

mysql> show master status;
mysql> show slave status\G

Advertisements

3 Comments »

  1. awsome..ur articles is cool.
    thx gan

    Comment by ipangsan — 2010-06-17 @ 13:18

  2. Hi Thanks for the article. I will be installing a mysql master-master today, and found your article in google. One quick note, I seem to remember that in newer versions of MySQL the auto_increment_offset is no longer needed.

    Best Regards

    Comment by Rui Ribeiro — 2010-07-15 @ 17:26

  3. I have already it working, and I do confirm that it is working with the auto_increment like a charm.

    Comment by Rui Fernando Ferreira Ribeiro — 2010-07-18 @ 03:06


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: