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,搞定