MariaDB加密復制測試

環境：

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協議實現復制過程中數據的加密傳輸，此實驗使用三臺服務器實現半同步復制，并他復制之間啟用加密復制

使用如下腳本安裝三臺主從服務器

1. 使用如下腳本安裝三臺服務器

   [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

   主服務器的配置

2. 查看一下半同步插件的文件名稱

   [root@slave1 ~]#rpm -ql mariadb-server
   …
   /usr/lib64/mysql/plugin/semisync_master.so
   /usr/lib64/mysql/plugin/semisync_slave.so
   …

3. 在主服務器上建立復制帳號

   MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.205.%' identified by 'centos';

4. 在主服務器上確定復制的位置

   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)

5. 主服務器上安裝半同步插件

   MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so'; 

6. 查看半同步的變量

   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)

7. enable半同步復制

   MariaDB [(none)]> set global rpl_semi_sync_master_enabled=on

8. 查看半同步變量

   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)

9. 查看半同步狀態

   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)

   在兩臺從服務器的配置

10. 從服務器上運行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)

11. 安裝插件在從服務器上，沒開啟同步狀態為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)

12. 開啟半同步，此時再查看同步變量為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)

13. 此進查看狀態為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

14. 此時我們在主服務器上查看半同步的狀態

    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)

15. 測試將一個庫文件導入到主服務器上，在兩個從服務器上查看是否同步

    [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和證書

16. 為了簡化我們在主服務器上產生一個自簽名的根證書，首先產生一個私鑰

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

17. 利用私鑰產生自簽名的根證書

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

18. 為簡化我們先產生一個私鑰，并使用這個私鑰為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

19. 根據請求文件生成證書文件

    [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

20. 重復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

21. 最終生成如下的文件

    [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

22. 將文件復制到從節點上, 正常我們只需要根證書和自己的私鑰和證書三個文件即可

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

    配置證書在主節點

23. 查看加密的相關變量都是空

    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)

24. 修改配置文件

    [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

25. 此時查看變量值，但因為你連接時沒有起用加密，所以狀態的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
    …

26. 使用客戶端加密的方式連接，可以看到狀態為加密的

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

27. 我們再從節點上測試用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

28. 但我們也可以不用加密去連接，所以我們可以建立一個用強制使用加密方的連接數據庫

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

29. 用建立的帳號從另外一臺從服務器嘗試去登錄

    [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

    配置證書在從節點

30. 所以如果使用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

31. 在從節點上停掉當前使用的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)

32. 啟動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
            …

    測試

33. 刪除以前不用的復制帳號，建表或刪庫測試，

    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)

34. 在從節點上測試庫是否建立, 發現出錯，原因是從服務器在帳號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)