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
> 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
  1. 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

  2. service mysqld restart (at A)
  3. 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'@'%';

  4. run mysql (client) in one terminal session : mysql> FLUSH TABLES WITH READ LOCK;
  5. in other terminal session, check latest binary log sequence number with mysql> SHOW MASTER STATUS;
  6. mysqldump --all-databases --lock-all-tables >dbdump.db
  7. back to first mysql session, unlock tables :
    mysql> UNLOCK TABLES;
  8. 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

  9. 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
  10. import database dump at B:
    mysql <>
  11. 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;
  12. mysql> START SLAVE;
  13. change A's my.cnf file to resemble B's. Substitute a different server-id and auto increment offset.
  14. service mysqld restart
    (on A:)
After these steps, the system choked on first replicated statement from server B. The solution is to stop slave on A and do
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; before starting A's slave again.

Comments

Popular posts from this blog

Long running process in Linux using PHP

Reverse Engineering Reptile Kernel module to Extract Authentication code

SAP System Copy Lessons Learned