Environmental Science:
192.168.205.37: as master server
192.168.205.47: as middle server
192.168.205.57: as slave server
Edition:
OS: centos 7 1810 with mini install
mariadb-5.5.60
Purpose:
Sometimes our database replication may need to be replicated across the network. If we don't want to be sniffed during the replication process, we can use ssl protocol to realize the encrypted transmission of data during the replication process. This experiment uses three servers to achieve semi-synchronous replication, and enables encrypted replication between replications.
Install three master-slave servers using the following script
-
Install three servers using the following script
[root@centos7 data]#cat /data/maridb_yum.sh #!/bin/bash # use last digit of IP as server-id ID=`ip a show dev eth0 | sed -r '3!d;s@(.*inet)(.*)(/.*)@\2@' | cut -d. -f4` # install mariadb-server and create data and logs directory rpm -q mariadb-server ||yum install -y mariadb-server [ -d /data/mysql ] || mkdir -p /data/mysql [ -d /data/logs ] || mkdir -p /data/logs chown mysql:mysql /data/{mysql,logs} # modify the my.cnf #Setting the location of the data file sed -i 's@datadir=/var/lib/mysql@datadir=/data/mysql@' /etc/my.cnf #Open the binary log and start the file name sed -i 's@log-bin=mysql-bin@log-bin=/data/logs/bin@' /etc/my.cnf #Setting the innodb table to separate files grep "innodb_file_per_table" /etc/my.cnf || sed -i '/\[mysqld\]/a innodb_file_per_table = on' /etc/my.cnf #Skip name resolution grep "skip_name_resolve" /etc/my.cnf || sed -i '/\[mysqld\]/a skip_name_resolve = on' /etc/my.cnf #Setting server-id to the last digit of eth0 IP can be changed according to your own needs grep "server-id" /etc/my.cnf || sed -i "/\[mysqld\]/a server-id=$ID" /etc/my.cnf #Start up service service mariadb restartConfiguration of primary server
- Check the file name of the semi-synchronous plug-in
[root@slave1 ~]#rpm -ql mariadb-server ... /usr/lib64/mysql/plugin/semisync_master.so /usr/lib64/mysql/plugin/semisync_slave.so ...
- Establishing a replication account on the primary server
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.205.%' identified by 'centos';
- Locate replication on the primary server
MariaDB [(none)]> show master logs; +------------+-----------+ | Log_name | File_size | +------------+-----------+ | bin.000001 | 30373 | | bin.000002 | 1038814 | | bin.000003 | 401 | +------------+-----------+ 3 rows in set (0.00 sec)
- Installing Semi-Synchronized Plug-ins on the Main Server
MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';
- Viewing Semi-Synchronized Variables
MariaDB [(none)]> show global variables like '%semi%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_semi_sync_master_enabled | OFF | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+ 4 rows in set (0.00 sec)
- enable semi-synchronous replication
MariaDB [(none)]> set global rpl_semi_sync_master_enabled=on
- Viewing Semi-Synchronized Variables
MariaDB [(none)]> show global variables like '%semi%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+ 4 rows in set (0.00 sec)
- View semi-synchronous status
MariaDB [(none)]> show global status like '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 363 | | Rpl_semi_sync_master_net_wait_time | 25473 | | Rpl_semi_sync_master_net_waits | 70 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 380 | | Rpl_semi_sync_master_tx_wait_time | 13305 | | Rpl_semi_sync_master_tx_waits | 35 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 35 | +--------------------------------------------+-------+ 14 rows in set (0.00 sec)
Configuration of two slave servers
- Run change master to from the server
MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.205.37', -> MASTER_USER='repluser', -> MASTER_PASSWORD='centos', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='bin.000003', -> MASTER_LOG_POS=401, -> MASTER_CONNECT_RETRY=10; Query OK, 0 rows affected (0.02 sec) - Installation plug-in on slave server, no synchronization status turned on to OFF
MariaDB [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show variables like '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 2 rows in set (0.00 sec)
- Turn on semi-synchronization, and then check that the synchronization variable is ON
MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=on; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show variables like '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 2 rows in set (0.00 sec)
-
This in view status is OFF, we need to open the slave thread
MariaDB [(none)]> show global status like '%semi%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | OFF | +----------------------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec) MariaDB [(none)]> start salve; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'salve' at line 1 MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show global status like '%semi%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | ON | +----------------------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.37 Master_User: repluser Master_Port: 3306 Connect_Retry: 10 Master_Log_File: bin.000003 Read_Master_Log_Pos: 401 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 523 Relay_Master_Log_File: bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes - At this point, we look at the semi-synchronous state on the primary server
MariaDB [(none)]> show global status like '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 2 | #There are already two clients that say it's OK. | Rpl_semi_sync_master_net_avg_wait_time | 363 | | Rpl_semi_sync_master_net_wait_time | 25473 | | Rpl_semi_sync_master_net_waits | 70 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 380 | | Rpl_semi_sync_master_tx_wait_time | 13305 | | Rpl_semi_sync_master_tx_waits | 35 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 35 | +--------------------------------------------+-------+ 14 rows in set (0.00 sec)
- The test imports a library file into the primary server and checks for synchronization between the two slave servers
[root@master ~]#mysql < hellodb_innodb.sql MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) //Two View Libraries from Servers MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
Preparing CA and Certificate
- To simplify the generation of a self-signed root certificate on the primary server, a private key is generated first.
[root@master ~]#mkdir /etc/my.cnf.d/ssl [root@master ~]#cd /etc/my.cnf.d/ssl [root@master ssl]#openssl genrsa 2048 > cakey.pem
- Generating Self-Signed Root Certificate with Private Key
[root@master ssl]#openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650
-
To simplify this, we first generate a private key and use this private key to generate a certificate request file for the master. Note that this is not a certificate, but a certificate request file.
[root@master ssl]#openssl req -newkey rsa:1024 -days 365 -nodes -keyout master.key > master.csr Generating a 1024 bit RSA private key .............++++++ ...++++++ writing new private key to 'master.key' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:contoso Organizational Unit Name (eg, section) []:devops Common Name (eg, your name or your server's hostname) []:master.contoso.com Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: [root@centos7 ssl]#ls cacert.pem cakey.pem master.csr master.key
- Generate certificate files based on request files
[root@master ssl]#openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt Signature ok subject=/C=CN/ST=beijing/L=beijing/O=contoso/OU=devops/CN=master.contoso.com Getting CA Private Key [root@master ssl]#ll total 20 -rw-r--r-- 1 root root 1334 Aug 11 21:55 cacert.pem -rw-r--r-- 1 root root 1675 Aug 11 21:52 cakey.pem -rw-r--r-- 1 root root 1034 Aug 11 23:01 master.crt -rw-r--r-- 1 root root 664 Aug 11 21:59 master.csr -rw-r--r-- 1 root root 916 Aug 11 21:59 master.key
-
Repeat 18 and 19 to generate two slave certificate files
[root@master ssl]#openssl req -newkey rsa:1024 -days 365 -nodes -keyout slave1.key > slave1.csr Generating a 1024 bit RSA private key .....++++++ ........++++++ writing new private key to 'slave1.key' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:contoso Organizational Unit Name (eg, section) []:devops Common Name (eg, your name or your server's hostname) []:slave1.contoso.com Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: [root@master ssl]#openssl req -newkey rsa:1024 -days 365 -nodes -keyout slave2.key > slave2.csr Generating a 1024 bit RSA private key .++++++ ........++++++ writing new private key to 'slave2.key' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:contoso Organizational Unit Name (eg, section) []:devops Common Name (eg, your name or your server's hostname) []:slave2.contoso.com Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: [root@master ssl]#openssl x509 -req -in slave1.csr -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave1.crt Signature ok subject=/C=CN/ST=beijing/L=beijing/O=contoso/OU=devops/CN=slave1.contoso.com Getting CA Private Key [root@master ssl]#openssl x509 -req -in slave2.csr -CA cacert.pem -CAkey cakey.pem -set_serial 03 > slave2.crt Signature ok subject=/C=CN/ST=beijing/L=beijing/O=contoso/OU=devops/CN=slave2.contoso.com Getting CA Private Key
- Finally, the following files are generated
[root@master ssl]#ll total 44 -rw-r--r-- 1 root root 1334 Aug 11 21:55 cacert.pem -rw-r--r-- 1 root root 1675 Aug 11 21:52 cakey.pem -rw-r--r-- 1 root root 1034 Aug 11 23:01 master.crt -rw-r--r-- 1 root root 664 Aug 11 21:59 master.csr -rw-r--r-- 1 root root 916 Aug 11 21:59 master.key -rw-r--r-- 1 root root 1034 Aug 11 23:05 slave1.crt -rw-r--r-- 1 root root 664 Aug 11 23:04 slave1.csr -rw-r--r-- 1 root root 916 Aug 11 23:04 slave1.key -rw-r--r-- 1 root root 1034 Aug 11 23:06 slave2.crt -rw-r--r-- 1 root root 664 Aug 11 23:05 slave2.csr -rw-r--r-- 1 root root 916 Aug 11 23:05 slave2.key
- Copy the file to the slave node. Normally, we only need three files: the root certificate and our private key and certificate.
[root@master ssl]#scp -r /etc/my.cnf.d/ssl/ 192.168.205.47:/etc/my.cnf.d/ [root@master ssl]#scp -r /etc/my.cnf.d/ssl/ 192.168.205.57:/etc/my.cnf.d/
Configuration Certificate in primary node
- Viewing Encrypted Related Variables is Empty
MariaDB [(none)]> show variables like '%ssl%'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | +---------------+----------+ 7 rows in set (0.00 sec)
- Modify configuration files
[root@master ssl]#vi /etc/my.cnf [mysqld] ssl-ca=/etc/my.cnf.d/ssl/cacert.pem ssl-cert=/etc/my.cnf.d/ssl/master.crt ssl-key=/etc/my.cnf.d/ssl/master.key [root@master ssl]#systemctl restart mariadb
-
Look at the value of the variable at this point, but because you did not use encryption when connecting, the ssl of the state is not in use
MariaDB [(none)]> show variables like '%ssl%'; +---------------+------------------------------+ | Variable_name | Value | +---------------+------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /etc/my.cnf.d/ssl/cacert.pem | | ssl_capath | | | ssl_cert | /etc/my.cnf.d/ssl/master.crt | | ssl_cipher | | | ssl_key | /etc/my.cnf.d/ssl/master.key | +---------------+------------------------------+ 7 rows in set (0.00 sec) MariaDB [(none)]> status -------------- mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 6 Current database: Current user: root@localhost SSL: Not in use ...
-
Connecting with client encryption, you can see that the state is encrypted.
[root@master ssl]#mysql --ssl-ca=cacert.pem --ssl-cert=master.crt --ssl-key=master.key MariaDB [(none)]> status -------------- mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 5 Current database: Current user: root@localhost SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384 ...
-
We then test ssl connection to the master node from the slave node
[root@slave1 ssl]#mysql --ssl-ca=cacert.pem --ssl-cert=slave1.crt --ssl-key=slave1.key -h192.168.205.37 -urepluser -pcentos MariaDB [(none)]> status -------------- mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 8 Current database: Current user: repluser@192.168.205.47 SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384
- But we can also connect without encryption, so we can build a connection database with a forced encryption party.
MariaDB [(none)]> grant replication slave on *.* to repluser2@'192.168.205.%' identified by 'centos' require ssl; Query OK, 0 rows affected (0.00 sec)
-
Attempt to log in from another slave server with the established account number
[root@slave1 ssl]#mysql -h192.168.205.37 -urepluser2 -pcentos ERROR 1045 (28000): Access denied for user 'repluser2'@'192.168.205.47' (using password: YES) [root@slave1 ssl]#mysql --ssl-ca=cacert.pem --ssl-cert=slave1.crt --ssl-key=slave1.key -h192.168.205.37 -urepluser2 -pcentos Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 14 Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> status -------------- mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 14 Current database: Current user: repluser2@192.168.205.47 SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384
Configuration Certificate in slave node
-
So if we use repluser2 to create replication with the primary server, we need to modify the configuration file.
[root@slave1 ssl]#vi /etc/my.cnf [mysqld] ssl-ca=/etc/my.cnf.d/ssl/cacert.pem ssl-cert=/etc/my.cnf.d/ssl/slave1.crt ssl-key=/etc/my.cnf.d/ssl/slave1.key [root@slave1 ssl]#systemctl restart mariadb [root@slave2 ssl]#vi /etc/my.cnf [mysqld] ssl-ca=/etc/my.cnf.d/ssl/cacert.pem ssl-cert=/etc/my.cnf.d/ssl/slave2.crt ssl-key=/etc/my.cnf.d/ssl/salve2.key [root@slave1 ssl]#systemctl restart mariadb
- Stop the repluser replication currently in use on the slave node and reuse repluser2 for replication (locate the primary server before replication)
MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> reset slave all; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.205.37', -> MASTER_USER='repluser2', -> MASTER_PASSWORD='centos', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='bin.000004', -> MASTER_LOG_POS=496, -> MASTER_SSL=1; Query OK, 0 rows affected (0.01 sec) -
Start slave to view status, one connection and replication is normal
MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.37 Master_User: repluser2 Master_Port: 3306 Connect_Retry: 10 Master_Log_File: bin.000004 Read_Master_Log_Pos: 415 Relay_Log_File: mariadb-relay-bin.000003 Relay_Log_Pos: 693 Relay_Master_Log_File: bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes ... Master_SSL_Allowed: Yes ...test
-
Delete previously unused duplicate accounts, build tables or delete library tests,
MariaDB [(none)]> drop user repluser@'192.168.205.%'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> select user,host,password from mysql.user; +-----------+---------------------+-------------------------------------------+ | user | host | password | +-----------+---------------------+-------------------------------------------+ | root | localhost | | | root | centos7.localdomain | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | centos7.localdomain | | | repluser2 | 192.168.205.% | *128977E278358FF80A246B5046F51043A2B1FCED | +-----------+---------------------+-------------------------------------------+ 7 rows in set (0.00 sec) MariaDB [(none)]> create database db1 -> ; Query OK, 1 row affected (0.01 sec) MariaDB [(none)]> MariaDB [(none)]> MariaDB [(none)]> MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | hellodb | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec) -
When we test whether the library is built on the slave node, we find that the error is due to the replication from the server after the account repluser is established. So when we delete it, we make an error because it is not from the server. The solution is to skip this error and test again. We find that db1 replication is successful and do the same test in slave 2.
MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.37 Master_User: repluser2 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000004 Read_Master_Log_Pos: 749 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 602 Relay_Master_Log_File: bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1396 Last_Error: Error 'Operation DROP USER failed for 'repluser'@'192.168.205.%'' on query. Default database: ''. Query: 'drop user repluser@'192.168.205.%'' .... #Note that this jump includes counts of correctness and error, and error replication may occur if correctness is skipped. MariaDB [(none)]> set global sql_slave_skip_counter = 1; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.37 Master_User: repluser2 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000004 Read_Master_Log_Pos: 749 Relay_Log_File: mariadb-relay-bin.000003 Relay_Log_Pos: 523 Relay_Master_Log_File: bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes .... MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | hellodb | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec)