MySQL master-master replication + SSL Authentication

I. node information:
Master1: 192.168.80.143/24 + Ca
Master2: 192.168.80.144/24

The two nodes are the same as the master node and the slave of the other node.

Ii. Basic Configuration:

(1) MySQL is installed on both servers.

 
 

  
  
# pvcreate /dev/sda5   
  
  
# vgcreate myvg /dev/sda5  
  
  
# lvcreate -L 10G -n mydata myvg  
  
  
# mkdir -p /data/mydata  
  
  
# mke2fs -j /dev/myvg/mydata   
  
  
# mount /dev/myvg/mydata /data/mydata/  
  
  
 
  
  
# tar xf mysql-5.5.24-linux2.6-i686.tar.gz  -C /usr/local/  
  
  
# cd /usr/local/  
  
  
# ln -s mysql-5.5.24-linux2.6-i686/ mysql  
  
  
# cd mysql  
  
  
# useradd -r mysql  
  
  
# chown -R mysql.mysql .  
  
  
# scripts/mysql_install_db --datadir=/data/mydata/ --user=mysql 
  
  
# chown -R root .  
  
  
# cp support-files/my-large.cnf /etc/my.cnf  
  
  
# vim /etc/my.cnf   
  
  
thread_concurrency = 2 
  
  
datadir = /data/mydata  
  
  
 
  
  
# cp support-files/mysql.server /etc/rc.d/init.d/mysqld  
  
  
# chmod +x /etc/rc.d/init.d/mysqld  
  
  
# service mysqld start 
 
 

(2) configure the CA Service on master1

 
 

  
  
# Vim/etc/pki/tls/OpenSSL. CNF
  
  
Dir =/etc/pki/CA
  
  
 
  
  
# Cd/etc/pki/CA/
  
  
# Mkdir certs newcerts CRL
  
  
# Touch index.txt
  
  
# Echo 01> serial
  
  
 
  
  
# (Umask 077; OpenSSL genrsa-out private/cakey. pem1024)
  
  
# OpenSSL req-X509-New-key private/cakey. pem
  
  
 
  
  
# Mkdir/usr/local/MySQL/SSL
  
  
# Cd/usr/local/MySQL/SSL
  
  
 
  
  
Certificates are required for both master and slave servers. Therefore, four certificates are required.
  
  
# (Umask 077; OpenSSL genrsa 1024> master1.key)
  
  
# OpenSSL req-New-key master1.key-out master1.csr
  
  
# OpenSSL ca-In master1.csr-out master1.crt-days 365
  
  
 
  
  
# (Umask 077; OpenSSL genrsa 1024> master1slave. Key)
  
  
# OpenSSL req-New-key master1slave. Key-out master1slave. CSR
  
  
# OpenSSL ca-In master1slave. CSR-out master1slave. CRT-days 365
  
  
 
  
  
# (Umask 077; OpenSSL genrsa 1024> master2.key)
  
  
# OpenSSL req-New-key master2.key-out master2.csr
  
  
# OpenSSL ca-In master2.csr-out master2.crt-days 365
  
  
 
  
  
# (Umask 077; OpenSSL genrsa 1024> master2slave. Key)
  
  
# OpenSSL req-New-key master2slave. Key-out master2slave. CSR
  
  
# OpenSSL ca-In master2slave. CSR-out master2slave. CRT-days 365
  
  
 
  
  
# Cp/etc/pki/CA/cacert. pem.
  
  
 
  
  
# Chown-r mysql. MySQL/user/local/MySQL/SSL
  
  
 
  
  
# SCP-P/etc/pki/CA/cacert. pem master1slave. * master2. * 192.168.80.144:/usr/local/MySQL/SSL/
 
 

3. Two-node configuration:

Master1:

 
 

  
  
# Vim/etc/My. CNF
  
  
Skip-slave-Start = 1 // you need to manually enable the thread when the Restart service is disabled.
  
  
 
  
  
SSL // specify SSL and Ca Information
  
  
SSL-CA =/usr/local/MySQL/SSL/cacert. pem
  
  
SSL-Cert =/usr/local/MySQL/SSL/master1.crt
  
  
SSL-Key =/usr/local/MySQL/SSL/master1.key
  
  
 
  
  
Log-bin = mysql-bin
  
  
Relay-log = mysql-relay // enable relay log
  
  
Auto-increment = 2 // Add 2 to each ID
  
  
Auto-increment-offset = 1 // you can specify the auto-increment ID.
  
  
 
  
  
Server-id = 1
 
 

Master2:

 
 

  
  
# vim /etc/my.cnf  
  
  
skip-slave-start=1 
  
  
 
  
  
ssl  
  
  
ssl-ca=/usr/local/mysql/ssl/cacert.pem  
  
  
ssl-cert=/usr/local/mysql/ssl/master2.crt  
  
  
ssl-key=/usr/local/mysql/ssl/master2.key  
  
  
 
  
  
log-bin=mysql-bin  
  
  
relay-log=mysql-relay  
  
  
auto-increment-increment = 2 
  
  
auto-increment-offset = 2 
  
  
 
  
  
server-id       = 2 
  
  
 
 
 

Restart service to take effect

# Service mysqld restart

Configure and copy user information together, and specify to use SSL:

 
 

  
  
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO repluser@'192.168.80.%' IDENTIFIED BY 'RedHat' REQUIRE SSL;  
  
  
 
  
  
mysql> flush privileges; 
 
 

View log location information separately:
Master1:

 
 

  
  
mysql>show master status;  
  
  
+------------------+----------+--------------+------------------+  
  
  
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
  
  
+------------------+----------+--------------+------------------+  
  
  
| mysql-bin.000011 |      107 |              |                  |  
  
  
+------------------+----------+--------------+------------------+  
  
  
1 row in set (0.00 sec 
 
 

Master2:

 
 

  
  
mysql>show master status;  
  
  
+------------------+----------+--------------+------------------+  
  
  
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
  
  
+------------------+----------+--------------+------------------+  
  
  
| mysql-bin.000017 |      107 |              |                  |  
  
  
+------------------+----------+--------------+------------------+  
  
  
1 row in set (0.00 sec 
 
 

Configure the slave information of master1 on master2:

 
 

  
  
Mysql> change master to master_host = '192. 168.80.143 ', // specify the master server
  
  
-> Master_user = 'repluser', // specify the user
  
  
-> Master_password = 'redhat', // Password
  
  
-> Master_log_file = 'mysql-bin.000017', // specify the log
  
  
-> Master_log_pos = 107, // specify the log bit
  
  
-> Master_ssl = 1,
  
  
-> Master_ssl_ca = '/usr/local/MySQL/SSL/cacert. pem ',
  
  
-> Master_ssl_cert = '/usr/local/MySQL/SSL/master1slave. CRT ',
  
  
-> Master_ssl_key = '/usr/local/MySQL/SSL/master1slave. key ';
 
 

Configure the slave information of master2 on master1:

 
 

  
  
mysql> CHANGE MASTER TO MASTER_HOST = '192.168.80.144' ,    
  
  
    -> MASTER_USER = 'repluser' ,     
  
  
    -> MASTER_PASSWORD = 'redhat' ,      
  
  
    -> MASTER_LOG_FILE = 'mysql-bin.000011' ,    
  
  
    -> MASTER_LOG_POS = 107 ,      
  
  
    -> MASTER_SSL = 1 ,  
  
  
    -> MASTER_SSL_CA = '/usr/local/mysql/ssl/cacert.pem' ,  
  
  
    -> MASTER_SSL_CERT = '/usr/local/mysql/ssl/master2slave.crt' ,  
  
  
    -> MASTER_SSL_KEY = '/usr/local/mysql/ssl/master2slave.key';