Environment:
192.168.205.37: as master server
192.168.205.47: as middle server
192.168.205.57: as slave server
Version:
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 you don’t want others to sniff during the replication process, We can use the 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 other replications.
Use the following script to install three master-slave servers< /h4>
-
Use the following script to install three servers
[[email protected] 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;[emailprotected](.*inet)(.*)( /.*)@\[email protected]' | 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< br />chown mysql:mysql /data/{mysql,logs}
# modify the my.cnf
#setting Data file location
sed -i'[email protected]=/var/lib/[email protected]=/data/[email protected]' /etc/my.cnf
#Open binary log and file
sed -i'[email protected][email protected]=/data/logs/[email protected]' /etc/my.cnf
#Set the innodb table separated file
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
#Set server-id to the last digit of eth0’s IP The number 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 the service
service mariadb restart
Configuration of the main server
- Check the file name of the semi-synchronous plug-in
[ [email protected] ~]#rpm -ql mariadb-server
…
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so
…
- Create a replication account on the primary server
MariaDB [(none)]> grant replication slave on *.* to [emailprotected]'192.168.205. %'identified by'centos';
- Determine the location of the replication on the master 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)
- Install the semi-synchronous plugin on the main server
MariaDB [(none)]> install plugin rpl_semi_sync_master soname ' semisync_master.so';
- View 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
- View 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-synchronization 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| Railfemi_sync_on |
| br />| 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 | | 35 |
+------------------------------------------ --+-------+
14 rows in set (0.00 sec)
Configuration on two slave servers
- Slave server Run change master to
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)
- Install the plugin on the slave server, and the synchronization status is 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 the synchronization variable to 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%';
+-------- -------------------------+-------+
| Var iable_name | Value |
+---------------------------------+------- +
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+-------------------------- -------+-------+
2 rows in set (0.00 sec)
-
The status of this entry 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 affecte d (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: Y es
- At this time, we check the status of semi-synchronization on the main server
MariaDB [(none)]> show global status like'%semi%';
+- -------------------------------------------+------ -+
| Variable_name | Value |
+---------------------------------- ----------+-------+
| Rpl_semi_sync_master_clients | 2 | #There are already two clients that are normal
| 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_master_semi_sync | 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)
< li>Test importing a library file to the master server and check whether it is synchronized on the two slave servers
[[emailprotected] ~]#mysql
+--------------------+
| Database |
+---------- ----------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
Two slave servers to view the library
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--- -----------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
Prepare CA and Certificate
- In order to simplify the generation of a self-signed root certificate on the main server, first generate a private key
[[emailprotected] ~]#mkdir /etc/my.cnf .d/ssl
[[email protected] ~]#cd /etc/my.cnf.d/ssl
[[emailprotected] ssl]#openssl genrsa 2048> cakey.pem
- Using the private key to generate a self-signed root certificate
[[emailprotected] ssl]#openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650
-
To simplify, 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.
[[ email protected] 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 []:
[[emailprotected] ssl]#ls
cacert.pem cakey.pem master.csr master.key
- Generate a certificate file according to the request file
[[emailprotected] 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
[[emailprotected] 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 more slave node certificate files
[[emailprotected] 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 []:
< br />Please enter the following'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[[email protected] 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 t here 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 []:
[[emailprotected] 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
[[ email protected] 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
- The following file is finally generated
[[emailprotected] 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 the three files of the root certificate and our own private key and certificate.< pre>[[email protected] ssl]#scp -r /etc/my.cnf.d/ssl/ 192.168.205.47:/etc/my.cnf.d/
[[email pro tected] ssl]#scp -r /etc/my.cnf.d/ssl/ 192.168.205.57:/etc/my.cnf.d/
Configure the certificate on the master node
- View the encrypted variables are all 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 the configuration file
[[emailprotected] 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
[[email protected] ssl]#systemctl restart mariadb
-
Check the variable value at this time, but because you Encryption is not enabled when connecting, so the status of ssl 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: [emailprotected]
SSL: Not in use
…
-
Use client-side encryption to connect, you can See that the status is encrypted
[[emailprotected] 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: [emailprotected]
SSL: Cipher in use is DHE-RSA-AES256 -GCM-SHA384
…
-
Let’s test the connection to the master node with ssl from the node
[[emailprotected] ssl]#mysql --ssl-ca=cacert.pem --ssl-cert=slave1.crt --ssl-key=slave1.key -h192.168.205.37 -urepluser -pcentos
MariaDB [(none)]> status< br />--------------
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 8
Current database:
Current user: [email protected]
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384
- But We can also connect without encryption, so we can create a connection database that uses a mandatory encryption party
MariaDB [(none)]> grant replication slave on *.* to [emailprotected]'192.168.205.%' identified by'centos' require ssl;
Query OK, 0 rows affected (0.00 sec)
-
Use the created account to try to log in from another slave server
[[email protected] ssl]#mysql -h192.168.205.37 -urepluser2 -pcentos
ERROR 1045 (28000): Access denied for user'repluser2'@'192.168.205.47' (using password: YES)
[[email protected] 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:
Curre nt user: [email protected]
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384
Configure the certificate on the slave node
- < p>So if we use repluser2 to establish replication with the main server, we need to modify the configuration file
[[emailprotected] 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
[[emailprotected] ssl]#systemctl restart mariadb
[[emailprotected] 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
[[emailprotected] ssl]#systemctl restart mariadb
- Stop the currently used repluser replication on the slave node, and re-use repluser2 for replication (determine the location of the master server before copying)
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 view status, a connection and replication are 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 Copy accounts that were not used before, create tables or delete databases to test,
MariaDB [(none)]> drop user [email protected]'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)< /pre>
-
I tested whether the library was established on the slave node, and found an error. The reason was that the slave server copied it after the account repluser was established. So when we deleted it, it was not on the slave server. Error, the solution is to skip this error, test again, find that db1 replication is successful, do the same test on slave2.
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: < br /> Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1396
Last_Error: Error'Operation DROP USE R failed for'repluser'@'192.168.205.%'' on query. Default database:''. Query:'drop user [email protected]'192.168.205.%''
….
#Note that this jump includes correct and incorrect counts. If the correct one is skipped, copy errors may occur.
MariaDB [(none)]> set global sql_slave_skip_counter = 1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> stop slave;< br />Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
< br />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)
< /ol>
Use the following script to install three servers
[[email protected] 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;[emailprotected](.*inet)(.*)( /.*)@\[email protected]' | 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< br />chown mysql:mysql /data/{mysql,logs}
# modify the my.cnf
#setting Data file location
sed -i'[email protected]=/var/lib/[email protected]=/data/[email protected]' /etc/my.cnf
#Open binary log and file
sed -i'[email protected][email protected]=/data/logs/[email protected]' /etc/my.cnf
#Set the innodb table separated file
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
#Set server-id to the last digit of eth0’s IP The number 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 the service
service mariadb restart
Configuration of the main server
[ [email protected] ~]#rpm -ql mariadb-server
…
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so
…
MariaDB [(none)]> grant replication slave on *.* to [emailprotected]'192.168.205. %'identified by'centos';
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)
MariaDB [(none)]> install plugin rpl_semi_sync_master soname ' semisync_master.so';
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)
MariaDB [ (none)]> set global rpl_semi_sync_master_enabled=on
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)
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| Railfemi_sync_on |
| br />| 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 | | 35 |
+------------------------------------------ --+-------+
14 rows in set (0.00 sec)
Configuration on two slave servers
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)
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)
MariaDB [(none)]> set global rpl_semi_sync_slave_enabled =on;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show variables like'%semi%';
+-------- -------------------------+-------+
| Var iable_name | Value |
+---------------------------------+------- +
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+-------------------------- -------+-------+
2 rows in set (0.00 sec)
The status of this entry 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 affecte d (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: Y es
MariaDB [(none)]> show global status like'%semi%';
+- -------------------------------------------+------ -+
| Variable_name | Value |
+---------------------------------- ----------+-------+
| Rpl_semi_sync_master_clients | 2 | #There are already two clients that are normal
| 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_master_semi_sync | 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)
< li>Test importing a library file to the master server and check whether it is synchronized on the two slave servers
[[emailprotected] ~]#mysqlMariaDB [(none)]> show databases;
+--------------------+
| Database |
+---------- ----------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
Two slave servers to view the library
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--- -----------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
Prepare CA and Certificate
[[emailprotected] ~]#mkdir /etc/my.cnf .d/ssl
[[email protected] ~]#cd /etc/my.cnf.d/ssl
[[emailprotected] ssl]#openssl genrsa 2048> cakey.pem
[[emailprotected] ssl]#openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650
To simplify, 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.
[[ email protected] 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 []:
[[emailprotected] ssl]#ls
cacert.pem cakey.pem master.csr master.key
[[emailprotected] 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
[[emailprotected] 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 more slave node certificate files
[[emailprotected] 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 []:
< br />Please enter the following'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[[email protected] 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 t here 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 []:
[[emailprotected] 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
[[ email protected] 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
[[emailprotected] 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
[[email pro tected] ssl]#scp -r /etc/my.cnf.d/ssl/ 192.168.205.57:/etc/my.cnf.d/
Configure the certificate on the master node
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)
[[emailprotected] 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
[[email protected] ssl]#systemctl restart mariadb
Check the variable value at this time, but because you Encryption is not enabled when connecting, so the status of ssl 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: [emailprotected]
SSL: Not in use
…
Use client-side encryption to connect, you can See that the status is encrypted
[[emailprotected] 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: [emailprotected]
SSL: Cipher in use is DHE-RSA-AES256 -GCM-SHA384
…
Let’s test the connection to the master node with ssl from the node
[[emailprotected] ssl]#mysql --ssl-ca=cacert.pem --ssl-cert=slave1.crt --ssl-key=slave1.key -h192.168.205.37 -urepluser -pcentos
MariaDB [(none)]> status< br />--------------
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 8
Current database:
Current user: [email protected]
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384
MariaDB [(none)]> grant replication slave on *.* to [emailprotected]'192.168.205.%' identified by'centos' require ssl;
Query OK, 0 rows affected (0.00 sec)
Use the created account to try to log in from another slave server
[[email protected] ssl]#mysql -h192.168.205.37 -urepluser2 -pcentos
ERROR 1045 (28000): Access denied for user'repluser2'@'192.168.205.47' (using password: YES)
[[email protected] 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:
Curre nt user: [email protected]
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384
Configure the certificate on the slave node
[[emailprotected] 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
[[emailprotected] ssl]#systemctl restart mariadb
[[emailprotected] 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
[[emailprotected] ssl]#systemctl restart mariadb
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 view status, a connection and replication are 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 Copy accounts that were not used before, create tables or delete databases to test,
MariaDB [(none)]> drop user [email protected]'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)< /pre>
I tested whether the library was established on the slave node, and found an error. The reason was that the slave server copied it after the account repluser was established. So when we deleted it, it was not on the slave server. Error, the solution is to skip this error, test again, find that db1 replication is successful, do the same test on slave2.
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: < br /> Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1396
Last_Error: Error'Operation DROP USE R failed for'repluser'@'192.168.205.%'' on query. Default database:''. Query:'drop user [email protected]'192.168.205.%''
….
#Note that this jump includes correct and incorrect counts. If the correct one is skipped, copy errors may occur.
MariaDB [(none)]> set global sql_slave_skip_counter = 1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> stop slave;< br />Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
< br />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)
< /ol>