MariaDB加密复制测试

创新互联主要从事网站制作、成都做网站、网页设计、企业做网站、公司建网站等业务。立足成都服务源城,10多年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:18980820575

环境：

192.168.205.37: as master server
192.168.205.47: as middle server
192.168.205.57: as slave server

版本：

OS: centos 7 1810 with mini install
mariadb-5.5.60

目地：

有时我们的数据库复制可能要跨网络复制，如果不想在复制过程中让别人嗅探，我们可以使用ssl协议实现复制过程中数据的加密传输，此实验使用三台服务器实现半同步复制，并他复制之间启用加密复制

使用如下脚本安装三台主从服务器

使用如下脚本安装三台服务器

[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
 #设置数据文件位置
sed -i 's@datadir=/var/lib/mysql@datadir=/data/mysql@' /etc/my.cnf 
#开启二进制日志并文件的起始名称
sed -i 's@log-bin=mysql-bin@log-bin=/data/logs/bin@' /etc/my.cnf  
#设置innodb表分离文件
grep "innodb_file_per_table" /etc/my.cnf || sed -i '/\[mysqld\]/a innodb_file_per_table = on' /etc/my.cnf 
#跳过名称解析
grep "skip_name_resolve" /etc/my.cnf || sed -i '/\[mysqld\]/a skip_name_resolve = on' /etc/my.cnf
#将server-id设为eth0的IP的最后一位数，可跟据自己的需求更改
grep "server-id" /etc/my.cnf || sed -i "/\[mysqld\]/a server-id=$ID" /etc/my.cnf
#启动服务
service mariadb restart

主服务器的配置

查看一下半同步插件的文件名称

[root@slave1 ~]#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 repluser@'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)

enable半同步复制

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                | 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)

在两台从服务器的配置

从服务器上运行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)

安装插件在从服务器上，没开启同步状态为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)

开启半同步，此时再查看同步变量为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)

此进查看状态为OFF,我们需要开启slave线程

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

此时我们在主服务器上查看半同步的状态

MariaDB [(none)]> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     |  #已经有两个客户端说明正常
| 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)

测试将一个库文件导入到主服务器上，在两个从服务器上查看是否同步

[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)
两台从服务器上查看库
MariaDB [(none)]>   show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

准备CA和证书

为了简化我们在主服务器上产生一个自签名的根证书，首先产生一个私钥

[root@master ~]#mkdir /etc/my.cnf.d/ssl
[root@master ~]#cd /etc/my.cnf.d/ssl
[root@master ssl]#openssl genrsa 2048 > cakey.pem

利用私钥产生自签名的根证书

[root@master ssl]#openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650

为简化我们先产生一个私钥，并使用这个私钥为master生成证书请求文件，注意这时不是证书，是证书请求文件

[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

根据请求文件生成证书文件

[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

重复18和19再生成两个从节点证书文件

[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

最终生成如下的文件

[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

将文件复制到从节点上, 正常我们只需要根证书和自己的私钥和证书三个文件即可

[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/

配置证书在主节点

查看加密的相关变量都是空

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)

修改配置文件

[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

此时查看变量值，但因为你连接时没有起用加密，所以状态的ssl为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
…

使用客户端加密的方式连接，可以看到状态为加密的

[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
…

我们再从节点上测试用ssl连接主节点

[root@slave1 ssl]#mysql --ssl-ca=cacert.pem --ssl-cert=slave1.crt --ssl-key=slave1.key -h292.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

但我们也可以不用加密去连接，所以我们可以建立一个用强制使用加密方的连接数据库

MariaDB [(none)]> grant replication slave on *.* to  repluser2@'192.168.205.%' identified by 'centos' require ssl;
Query OK, 0 rows affected (0.00 sec)

用建立的帐号从另外一台从服务器尝试去登录

[root@slave1 ssl]#mysql  -h292.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 -h292.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

配置证书在从节点

所以如果使用repluser2去和主服务器建立复制，我们需要修改配置文件

[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

在从节点上停掉当前使用的repluser复制，重新使用repluser2进行复制(复制前要确定主服务器的位置)

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)

启动slave查看状态，一连接和复制正常

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
        …

测试

删除以前不用的复制帐号，建表或删库测试，

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)

在从节点上测试库是否建立, 发现出错，原因是从服务器在帐号repluser建立后复制的，所以当我们删除时因为从服务器上没有，所以出错误了，解决办法是跳过这次错误，再次测试，发现db1复制成功，在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: 
       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.%''
    ….

#注意此跳包括正确和错误的计数，如果正确的被跳过可能出现错误复制。
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)

网站名称：MariaDB加密复制测试
新闻来源：http://chengdu.cdxwcx.cn/article/iegdhi.html

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

首页

网站建设

网站制作案例

解决方案

网站设计报价

网站制作动态

关于我们

联系我们

成都网站建设设计将想法与焦点和您一起共享

MariaDB加密复制测试

环境：

版本：

目地：

使用如下脚本安装三台主从服务器

主服务器的配置

在两台从服务器的配置

准备CA和证书

配置证书在主节点

配置证书在从节点

测试

其他资讯

linux常用命令all Linux常用命令及示范

html5背景缩放 html怎么背景图片怎么成比例放大

怎么找到微信企业群如何查找微信企业群

android点击转圈安卓手机一直转圈圈打不开

标签的css样式 css中标签的作用

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

成都网站建设设计 将想法与焦点和您一起共享

MariaDB加密复制测试

环境：

版本：

目地：

使用如下脚本安装三台主从服务器

主服务器的配置

在两台从服务器的配置

准备CA和证书

配置证书在主节点

配置证书在从节点

测试

其他资讯

linux常用命令all Linux常用命令及示范

html5背景缩放 html怎么背景图片怎么成比例放大

怎么找到微信企业群 如何查找微信企业群

android点击转圈 安卓手机一直转圈圈打不开

标签的css样式 css中标签的作用

成都网站建设设计将想法与焦点和您一起共享

怎么找到微信企业群如何查找微信企业群

android点击转圈安卓手机一直转圈圈打不开