Previous: Unified Zimbra/Exchange GAL Next: Building a Custom RPM

---

Setting up Mysql Replication

This is a primary/secondary (master/slave) setup. Note that it has limitations (possible to have an action commit on primary that doesn’t get to secondary in the event of a hard crash on the primary).

I’m using primary/secondary vs. master/slave to model the recommended setup of two servers. It is possible to create a lot more complicated setups.

Reference documentation:

1. General Documentation: http://dev.mysql.com/doc/refman/5.0/en/replication.html
2. Features and Issues: http://dev.mysql.com/doc/refman/5.0/en/replication-features.html

How to setup

1. Install mysql on two servers called primary and secondary.

2. Create SSL certificates for the two servers and make sure that the CA certificate is available on both hosts.

3. Configure the primary server with

   ssl
   ssl-cert = /usr/local/etc/openssl/certs/[primary host].crt
   ssl-key = /usr/local/etc/openssl/certs/[primary host].key
   ssl-capath = /usr/local/etc/openssl/certs
   log-bin = mysql-bin
   server-id = 1
   

4. Start primary server and configure with desired databases, permissions, etc...

5. Configure a replication user in mysql cli:

   grant replication slave on *.* to 'repl'@'[secondary host]' \
   identified by '[password]' \
   require subject '[output of subject via openssl x509 -in [cert file] -subject]';
   flush privileges;

6. In another window lock master database

   FLUSH TABLES WITH READ LOCK;
   

7. Make sure database is quiet (or lock tables and run):

   $ mysqldump -u root -p --all-databases --master-data > ~/primary-dbdump.db
   

8. Copy primary-dbdump.db to secondary

9. Configure secondary mysql server:

   log-bin = mysql-bin
   server-id = 2
   

10. Start secondary mysql server (may need to run mysql_install_db to start with empty db)

11. Import data from primary:

    $ mysql -u root -p < primary-dbdump.db
    

12. Configure replication via mysql cli on secondary:

    CHANGE MASTER TO
    MASTER_HOST='[primary server]',
    MASTER_USER='repl',
    MASTER_PASSWORD='[password]',
    MASTER_SSL=1,
    MASTER_SSL_CAPATH = '/usr/local/etc/openssl/certs', \
    MASTER_SSL_CERT = '/usr/local/etc/openssl/certs/[secondary host].crt', \
    MASTER_SSL_KEY = '/usr/local/etc/openssl/certs/[secondary host].key', \
    MASTER_LOG_FILE='[from primary-dbdump.db]', \
    MASTER_LOG_POS=[from primary-dbdump.db];

13. Enable replication on secondary

    start slave;
    

14. Check status

    show slave status\G;