Mysql SSL加密主从

#dd1
[mysqld]
server-id = 10
log-bin = mysql-bin
sync_binlog = 1

grant replication slave,replication client on *.* to ‘xq‘@‘192.168.55.140‘ identified by ‘123456‘;
show master status;

#dd2
[mysqld]
server-id = 20
log-bin = mysql-bin
relay-log=relay-bin
log-slave-updates = 1
read_only = 1

change master to master_host=‘192.168.55.139‘,master_user=‘xq‘,master_password=‘123456‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=423;
start slave;
show slave status \G;

#报错
Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table

use mysql;
create table `gtid_slave_pos` (
`domain_id` int(10) unsigned NOT NULL,
`sub_id` bigint(20) unsigned NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`seq_no` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`domain_id`,`sub_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=‘Replication slave GTID state‘;

systemctl restart mysqld

#dd1
create table dd(id int primary key auto_increment,name varchar(20));
create table xq(id int(10),name varchar(10),address varchar(20));
insert into xq values(‘1‘,‘xq01‘,‘this_is_master‘);

#dd1
cd /etc/pki/CA/
(umask 077;openssl genrsa -out private/cakey.pem 2048)
openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 36500
touch index.txt serial crlnumber
echo ‘01‘ > serial

mkdir -p /usr/local/mysql/ssl
cd /usr/local/mysql/ssl
(umask 077;openssl genrsa -out master.key 2048)
openssl req -new -key master.key -out master.csr -days 36500
openssl ca -in master.csr -out master.crt -days 36500

#dd2
mkdir -p /usr/local/mysql/ssl
cd /usr/local/mysql/ssl
(umask 077;openssl genrsa -out slave.key 2048)
openssl req -new -key slave.key -out slave.csr -days 36500
scp slave.csr dd1:/usr/local/mysql/ssl

#dd1
openssl ca -in slave.csr -out slave.crt -days 36500
#若报错，TXT_DB error number 2
vi /etc/pki/CA/index.txt.attr
unique_subject = yes 改为 no

scp slave.crt dd2:/usr/local/mysql/ssl/
cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/
scp /etc/pki/CA/cacert.pem dd2:/usr/local/mysql/ssl/

#dd1
chown -R mysql.mysql /usr/local/mysql/ssl/

#dd2
chown -R mysql.mysql /usr/local/mysql/ssl/

#dd1
vi /etc/my.cnf
ssl
ssl_ca = /usr/local/mysql/ssl/cacert.pem
ssl_key = /usr/local/mysql/ssl/master.key
ssl_cert = /usr/local/mysql/ssl/master.crt

systemctl restart mysqld

#dd2
vi /etc/my.cnf
ssl
ssl_ca = /usr/local/mysql/ssl/cacert.pem
ssl_key = /usr/local/mysql/ssl/slave.key
ssl_cert = /usr/local/mysql/ssl/slave.crt

systemctl restart mysqld

#dd1
mysql -uroot -p
show variables like ‘%ssl%‘;
grant replication slave,replication client on *.* to ‘xq‘@‘192.168.55.140‘ identified by ‘123456‘ require ssl;
flush privileges;
show master status;

#dd2
mysql -uroot -p
show variables like ‘%ssl%‘;

stop slave;

change master to master_host=‘192.168.55.139‘,master_user=‘xq‘,master_password=‘123456‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=800,master_ssl=1,master_ssl_ca=‘/usr/local/mysql/ssl/cacert.pem‘,master_ssl_cert=‘/usr/local/mysql/ssl/slave.crt‘,master_ssl_key=‘/usr/local/mysql/ssl/slave.key‘;
flush privileges;
start slave;
show slave status\G

#dd1
create table dd(id int primary key auto_increment,name varchar(20));
create table xq(id int(10),name varchar(10),address varchar(20));
insert into xq values(‘1‘,‘xq01‘,‘this_is_master‘);

Mysql SSL加密主从

原文：https://www.cnblogs.com/feizhuanye/p/10445549.html