MariaDB配置SSL方式主从服务器数据同步

简单记录下

先生成SSL证书

# 生成CA。common name可以设置为MariaDB CA
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 730 -key ca-key.pem -out ca-cert.pem
# 生成服务器证书。common name可以设置为MariaDB Server
openssl req -newkey rsa:2048 -days 730 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
# 生成客户端证书。common name可以设置为MariaDB Client
openssl req -newkey rsa:2048 -days 730 -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
# 验证下证书及证书链是否都正确
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

配置master主服务器

打开主服务器，编辑my.cnf

[client]部分增加

# ssl encrypt for replication
ssl-ca=/etc/mariadb/ca-cert.pem
ssl-cert=/etc/mariadb/client-cert.pem
ssl-key=/etc/mariadb/client-key.pem

[mysqld]部分增加或者编辑

log-bin=mysql-bin
log-basename=mariadb-master-1
binlog_format=mixed
server-id       = 1

# ssl encrypt for replication
ssl-ca=/etc/mariadb/ca-cert.pem
ssl-cert=/etc/mariadb/server-cert.pem
ssl-key=/etc/mariadb/server-key.pem

上传刚才创建的证书到目录/etc/mariadb，然后重启MariaDB

sudo systemctl restart mariadb

master主服务器上添加用户

需要专门创建一个用户，用于同步数据

MariaDB [(none)]> CREATE USER 'replication_user'@'%' IDENTIFIED BY '密码';
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
MariaDB [(none)]> FLUSH PRIVILEGES;

master主服务器dump数据

我们需要对当前的master打一个快照，并且记录下位置。slave启动后，会从这个位置继续获取数据更新

MariaDB [(none)]> SHOW MASTER STATUS;
+------------------------------+----------+--------------+------------------+
| File                         | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------------+----------+--------------+------------------+
| mariadb-master-1-bin.000002 |      340 |              |                  |
+------------------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

记录下上面的mariadb-master-1-bin.000002和340，后面要用

dump数据

mysqldump -uroot -p --opt --all-databases > db-all.sql

然后把db-all.sql拷贝到slave从服务器上

最好dump完后，再SHOW MASTER STATUS确定下进度是否有改变

配置slave从服务器

一样，先把所有的ssl证书拷贝到/etc/mariadb

然后编辑/etc/my.cnf，增加或者修改

[client]
...
# ssl encrypt for replication
ssl-ca=/etc/mariadb/ca-cert.pem
ssl-cert=/etc/mariadb/client-cert.pem
ssl-key=/etc/mariadb/client-key.pem

[mysqld]
server-id   = 3 # 这里要改一个数字，比master大就行

# 下面的证书还是server，不是client，要分清楚
# ssl encrypt for replication
ssl-ca=/etc/mariadb/ca-cert.pem
ssl-cert=/etc/mariadb/server-cert.pem
ssl-key=/etc/mariadb/server-key.pem

重启MariaDB

sudo systemctl restart mariadb

导入老数据，保证和主服务器一致

mysql -uroot -p < db-all.sql

slave从服务器上配置master主服务器

MariaDB [(none)]> CHANGE MASTER TO
  MASTER_HOST='主服务器IP/域名',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='主服务器密码',
  MASTER_LOG_FILE='前面记录下来的文件名，比如mariadb-master-1-bin.000002',
  MASTER_LOG_POS=前面记录下来的位置，比如340,
  MASTER_SSL=1,
  MASTER_SSL_CERT = '/etc/mariadb/client-cert.pem',
  MASTER_SSL_KEY = '/etc/mariadb/client-key.pem',
  MASTER_SSL_CA = '/etc/mariadb/ca-cert.pem',
  MASTER_SSL_VERIFY_SERVER_CERT=0;

接着，启动slave

MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: web-1.jpe.azure.servbay.dev
                   Master_User: replication_user
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-master-1-bin.000002
           Read_Master_Log_Pos: 340
                Relay_Log_File: mariadb-relay-bin.000003
                 Relay_Log_Pos: 1058
         Relay_Master_Log_File: mariadb-master-1-bin.000002
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
          Replicate_Rewrite_DB:
               Replicate_Do_DB:
           Replicate_Ignore_DB:
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 0
                    Last_Error:
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 831
               Relay_Log_Space: 1369
               Until_Condition: None
                Until_Log_File:
                 Until_Log_Pos: 0
            Master_SSL_Allowed: Yes
            Master_SSL_CA_File: /etc/mariadb/ca-cert.pem
            Master_SSL_CA_Path:
               Master_SSL_Cert: /etc/mariadb/client-cert.pem
             Master_SSL_Cipher:
                Master_SSL_Key: /etc/mariadb/client-key.pem
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error:
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: optimistic
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              Slave_DDL_Groups: 1
Slave_Non_Transactional_Groups: 2
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

OK，搞定