300字范文 > mysql基于SSL实现主从复制

mysql基于SSL实现主从复制

时间：2021-05-31 23:30:42

相关推荐

mysql基于SSL实现主从复制

mysql数据库基于SSL实现主从复制

实验环境：

node1:192.168.4.61

node2:192.168.4.62

CA：192.168.4.63

node1和node2时间同步

[root@node1~]#ntpdate 172.18.0.1

[root@node2~]#ntpdate 172.18.0.1

[root@node1~]#vim /etc/chrony.conf#node1和node2操作一样

[root@node1~]#systemctl start chronyd.service#启动服务

node1和node2基于key连接

[root@node1~]#ssh-keygen

[root@node1~]#ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.4.62#将公钥复制到node2上

node2操作和node1操作相同。

修改hosts文件

node1和node2配置相同

[root@node1~]#vim /etc/hosts

3192.168.4.61node14192.168.4.62node2

确保关闭iptables和selinux

node1和node2安装mariadb数据库

[root@node1~]#yum install -y mariadb-server

[root@node2~]#yum install -y mariadb-server

配置node1为主服务器

[root@node1~]#vim /etc/f.d/f

[root@node1~]#systemctl start mariadb#启动mariadb服务

MariaDB[(none)]>GRANTREPLICATIONCLIENT,REPLICATIONSLAVEON*.*TO'joah'@'192.168.4.62'IDENTIFIEDBY'123456';#对用户授权

查看是否开启二进制日志

MariaDB[(none)]>SHOWVARIABLESLIKE'%log%bin%';

在node1上二进制日志状态信息

MariaDB[(none)]>SHOWMASTERSTATUS;

配置node2从服务器

[root@node2~]#vim /etc/f.d/f

[root@node2~]#systemctl start mariadb#启动mariadb服务

MariaDB[(none)]>CHANGEMASTERTOMASTER_HOST='192.168.4.61',MASTER_USER='joah',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=417;#设置连接主服务器

启动从服务器

MariaDB[(none)]>STARTSLAVE;

启动IO线程和SQL线程

MariaDB[(none)]>STARTSLAVEIO_THREAD,SQL_THREAD;

查看从服务器状态

MariaDB[(none)]>STARTSLAVEIO_THREAD,SQL_THREAD;

如果出现图中红框中的信息说明已经启动成功。

测试是否已经实现主从复制

实现SSL功能

搭建CA服务器

[root@CA/etc/pki/CA]#touch index.txt

[root@CA/etc/pki/CA]#echo 01 > serial

生成key文件

[root@CA/etc/pki/CA]#(umask 077;openssl genrsa -out private/cakey.pem 2048)

生成自签证书

[root@CA/etc/pki/CA]#openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650

node1生成证书

[root@node1~]#mkdir /etc/mysql/ssl -pv

[root@node1~]#chown mysql.mysql /etc/mysql/ssl/ -R

[root@node1/etc/mysql/ssl]#(umask 077;openssl genrsa -out master.key 2048)[root@node1/etc/mysql/ssl]#openssl req -new -key master.key -out master.csr

[root@node1/etc/mysql/ssl]#scp master.csr 192.168.4.63:/etc/pki/CA/newcerts/

[root@CA/etc/pki/CA/newcerts]#openssl ca -in master.csr -out master.crt -days 365

[root@CA/etc/pki/CA/newcerts]#scp master.crt ../cacert.pem 192.168.4.61:/etc/mysql/ssl

[root@node1~]#vim /etc/f.d/f

[root@node1~]#systemctl restart mariadb

查看是否开启SSL功能

node2生成证书

[root@node2~]#mkdir /etc/mysql/ssl -pv

[root@node2~]#chown mysql.mysql /etc/mysql/ssl/ -R

[root@node2/etc/mysql/ssl]#(umask 077;openssl genrsa -out slave.key 2048)

[root@node2/etc/mysql/ssl]#openssl req -new -key slave.key -out slave.csr

[root@node2/etc/mysql/ssl]#scp slave.csr 192.168.4.63:/etc/pki/CA/newcerts

[root@CA/etc/pki/CA/newcerts]#openssl ca -in slave.csr -out slave.crt -days 365

[root@CA/etc/pki/CA/newcerts]#scp slave.crt ../cacert.pem 192.168.4.62:/etc/mysql/ssl

[root@node2/etc/mysql/ssl]#vim /etc/f.d/f

重启服务

[root@node2/etc/mysql/ssl]#systemctl restart mariadb

基于SSL连接

node1授权

MariaDB[(none)]>GRANTREPLICATIONCLIENT,REPLICATIONSLAVEON*.*TO'joah'@'192.168.4.62'IDENTIFIEDBY'123456'REQUIRESSL;

测试

[root@node2~]#mysql -ujoah -p123456 -h192.168.4.61 --ssl

node2连接主服务器以ssl复制

MariaDB[(none)]>CHANGEMASTERTOMASTER_HOST='192.168.4.61',MASTER_USER='joah',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-log.000008',MASTER_LOG_POS=429,MASTER_SSL=1,MASTER_SSL_CA='/etc/mysql/ssl/cacert.pem',MASTER_SSL_CERT='/etc/mysql/ssl/slave.crt',MASTER_SSL_KEY='/etc/mysql/ssl/slave.key';MariaDB[(none)]>STARTSLAVE;#启动从服务器MariaDB[(none)]>SHOWSLAVESTATUS\G;