Implementing Multimaster Replication
Today I tried to implement multimaster replication on two Red Hat Enterprise Linux 5 servers.
I studied the top three google result for 'multimaster replication mysql' today :
http://onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
http://capttofu.livejournal.com/1752.html
I found these three resources complementary. At first I have no idea about the replication process. Initially followed instructions from Dev.MySQL site to setup one-way replication, then read the livejournal blog for better one-page summary of the process. Reading the ONLAMP Article, I understood the syntax to stop replication and ordered MySQL to skip one SQL statement when resuming replication.
I'm using replication with existing data in the database, so the steps I use is a little different from the three web resource above. And because I'm using out-of-the box RHEL setup, there is an issue with the firewall blocking port 3306.
Unblock the firewall
as root, edit (use vi or nano or something) /etc/sysconfig/iptables
insert the line in red above.
Other steps is similar to what you might read in these three articles
I studied the top three google result for 'multimaster replication mysql' today :
http://onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
http://capttofu.livejournal.com/1752.html
I found these three resources complementary. At first I have no idea about the replication process. Initially followed instructions from Dev.MySQL site to setup one-way replication, then read the livejournal blog for better one-page summary of the process. Reading the ONLAMP Article, I understood the syntax to stop replication and ordered MySQL to skip one SQL statement when resuming replication.
I'm using replication with existing data in the database, so the steps I use is a little different from the three web resource above. And because I'm using out-of-the box RHEL setup, there is an issue with the firewall blocking port 3306.
Unblock the firewall
as root, edit (use vi or nano or something) /etc/sysconfig/iptables
> vi /etc/sysconfig/iptables
-A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
-A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited
COMMIT
insert the line in red above.
Other steps is similar to what you might read in these three articles
- Add server-id, log-bin, sync_binlog, innodb_flush_at_trx_commit to /etc/my.cnf at server A
- log-bin=mysql-bin
- server-id=1
- sync_binlog=1
- innodb_flush_log_at_trx_commit=1
- service mysqld restart (at A)
- create repl user at A, grant it replication capabilities : (I omit the domain so I wouldn't be debugging hostname problems, it is safe for my purpose because it is an internal server)
mysql>
CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
- run mysql (client) in one terminal session
: mysql> FLUSH TABLES WITH READ LOCK;
- in other terminal session, check latest binary log sequence number with
mysql> SHOW MASTER STATUS;
mysqldump --all-databases --lock-all-tables >dbdump.db
- back to first mysql session, unlock tables :
mysql> UNLOCK TABLES;
- prepare server B's /etc/my.cnf with full blown options :
- log-bin=mysql-bin
- server-id=2
- sync_binlog=1
- innodb_flush_log_at_trx_commit=1
- auto_increment_increment=2
- auto_increment_offset=2
- log-slave-updates
- master-host = ipofserverA
- master-user = repl
- master-password = passwordOfRepl
- log-slow-queries=/var/log/mysql-slow.log
- long_query_time=1
- log-queries-not-using-indexes=1
- log-bin=mysql-bin
- start server B's mysql with --skip-slave-start option :
/bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --user=mysql --skip-slave-start
- import database dump at B:
mysql <>
- change master on B to refer the A server :
mysql>
CHANGE MASTER TO
->MASTER_HOST='
master_host_name
',
->MASTER_USER='
replication_user_name
',
->MASTER_PASSWORD='
replication_password
',
->MASTER_LOG_FILE='
recorded_log_file_name
',
->MASTER_LOG_POS=
recorded_log_position
; mysql> START SLAVE;
- change A's my.cnf file to resemble B's. Substitute a different server-id and auto increment offset.
service mysqld restart
(on A:)
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
before starting A's slave again.
Comments