Monday, August 16, 2010

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 :

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

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/ --user=mysql --skip-slave-start
  10. import database dump at B:
    mysql <>
  11. change master on B to refer the A server :
    -> 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.

No comments: