Knowledgebase

Configuring Master Replication Server (217.sophio.com)

1. Install OpenSSL and visual c++ 2008 in the server. Download it from http://gnuwin32.sourceforge.net/packages/openssl.htm
2. Create CA Certificates. You can create it by using the command prompt. Just be sure you are under the directory of the OpenSSL and certifcates folder is available. This will create the ca-key.pem and ca-cert.pem files.
   
   • openssl genrsa 2048 > "D:/MySQL/certificates/ca-key.pem"
   • openssl req -new -x509 -nodes -days 3600 -key "D:/MySQL/certificates/ca-key.pem" > "D:/MySQL/certificates/ca-cert.pem"
3. Create Server Certificates. This will create server-req.pem and server-cert.pem files.
   • openssl req -newkey rsa:2048 -days 3600 -nodes -keyout "D:/MySQL/certificates/server-key.pem" > "D:/MySQL/certificates/server-req.pem"
   • openssl x509 -req -in "D:/MySQL/certificates/server-req.pem" -days 3600 -CA "D:/MySQL/certificates/ca-cert.pem" -CAkey "D:/MySQL/certificates/ca-key.pem" -set_serial 01 > "D:/MySQL/certificates/server-cert.pem"
     
4. Create client certificates. This will create client-req.pem and client-cert.pem files.
   • openssl req -newkey rsa:2048 -days 3600 -nodes -keyout "D:/MySQL/certificates/client-key.pem" > "D:/MySQL/certificates/client-req.pem"
   • openssl x509 -req -in "D:/MySQL/certificates/client-req.pem" -days 3600 -CA "D:/MySQL/certificates/ca-cert.pem" -CAkey "D:/MySQL/certificates/ca-key.pem" -set_serial 01 > "D:/MySQL/certificates/client-cert.pem"
     
5. Create slave user in MySQL shell.
   
   • GRANT REPLICATION SLAVE ON *.* TO 'sophio_slave'@'%' IDENTIFIED BY '******' REQUIRE SSL;
   • FLUSH PRIVILEGES;
   • Quit MySQL
     
6. Stop MySQL in Services.
7. Add the following lines in my.ini file under [mysqld] section.
   • old_passwords=1
   • ssl
   • ssl-ca=D:/MySQL/certificates/ca-cert.pem
   • ssl-cert=D:/MySQL/certificates/server-cert.pem
   • ssl-key=D:/MySQL/certificates/server-key.pem
   • server-id               = 1
   • log_bin                 = D:/MySQL/binlog/mysql-bin.log
   • expire_logs_days        = 14
   • max_binlog_size         = 100M
   • binlog_do_db            = whi_catalog
8. Start MySQL in Services.
9. On MySQL shell, run the following commands.
   • USE whi_catalog;
     FLUSH TABLES WITH READ LOCK;
     SHOW MASTER STATUS;
10. The "show master status" command will show something like this. Be sure to write down the results, we'll need it later.
    • +------------------+----------+--------------+------------------+
      | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
      +------------------+----------+--------------+------------------+
      | mysql-bin.000002 |      106 | whi_catalog  |                  |
      +------------------+----------+--------------+------------------+
11. Backup whi_catalog database.
    • mysqldump -u root -p****** --opt whi_catalog > whi_catalog.sql
12. We can now unlock the whi_catalog database and leave the MySQL shell.
    • UNLOCK TABLES;
      quit;

Configuring the Slave Server (ex. 169.sophio.com)

1. Be sure slave server can telnet port 3306 to Master Server.
2. Copy the whi_catalog backup file in the server.
   
3. Stop MySQL in Services.
4. Add the following lines in my.ini under [mysqld] section.

   • old_passwords=1

   • ssl

   • server-id=2

   • master-connect-retry=60

   • replicate-do-db=whi_catalog

5. Start MySQL in Services.
6. On MySQL shell, create whi_catalog database then restore the backup.
   • create database whi_catalog;
   • se whi_catalog;
   • source D:\backup\whi_catalog.sql;
   • quit;
     
7. Still in MySQL shell, run this command.
   • CHANGE MASTER TO MASTER_HOST='217.sophio.com',
   • MASTER_USER='sophio_slave', MASTER_PASSWORD='*****',
   • MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106,
   • MASTER_SSL=1, MASTER_SSL_CA = 'D:/MySQL/certificates/ca-cert.pem',
   • MASTER_SSL_CERT = 'D:/MySQL/certificates/client-cert.pem',
   • MASTER_SSL_KEY = 'D:/MySQL/certificates/client-key.pem';
     
8. Finally start slave server.
   • start slave;
     
9. Check the slave status.
   • show slave status \G
     
10. It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output; otherwise, something went wrong--you should check your setup again and take a look at error log to find out about any errors. As you're using an SSL connection now, you should also find values in the fields Master_SSL_Allowed, Master_SSL_CA_File, Master_SSL_Cert, and Master_SSL_Key.