mysql ssl 主从同步_MySQL使用ssl主从复制与读写分离

最新推荐文章于 2023-05-01 23:52:59 发布

TechGoddess

最新推荐文章于 2023-05-01 23:52:59 发布

阅读量394

点赞数

文章标签： mysql ssl 主从同步

本文链接：https://blog.csdn.net/weixin_42348233/article/details/113464204

版权

环境要求：

实验目标：

1.实现mysql主从复制

2.实现mysql读写分离与负载均衡

一、案例前置知识点

1.mysql主从复制原理

1)mysql支持的复制类型

(1)基于语句的复制。在主服务器上执行的sql语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制类型

(2)基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍

(3)混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制

2)复制的工作过程

(1)在每个事务更新数据完成之前，master在二进制日志记录这些变化。写入二进制日志完成后，master通知存储引擎提交事务。

(2)slave将master的binary log复制到其中继日志。首先，slave开始一个工作线程--I/0线程，I/0线程在master上打开一个普通的连接，然后开始binlog dump

process。binlog

dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件，I/O线程将这些事件写入中继日志。

(3)sql slave thread(SQL从线程)处理该过程的最后一步，sql线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于os的缓存中，所以中继日志的开销很小。

2.mysql读写分离原理

目前较为常见的mysql读写分离分为两种

1)基于程序代码内部实现

在代码中根据select、insert进行路由分类，这类方法也是目前生产环境应用最广泛的。优点是性能较好，因为在程序代码中实现，不需要增加额外的设备作为硬件支持；缺点是需要开发人员来实现，运维人员无从下手。

2)基于中间代理层实现

代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有两个代表性程序。

(1)mysql-proxy。mysql-proxy为mysql开源项目，通过其自身带的lua脚本进行sql判断，虽然是mysql官方产品，但是mysql官方并不建议将mysql-proxy用到生产环境。

(2)Amoeba.由陈思儒开发，作者曾就职与阿里巴巴。该程勋有java语言进行开发，阿里巴巴将其用于生产环境。不不支持事务和存储过程

二、搭建mysql主从复制

1.建立时间同步环境，在主节点上搭建时间同步服务器

1)安装NTP(utp 123)

[root@centos2 ~]# yum -y install ntp

2)配置NTP

[root@centos2 ~]# vim /etc/ntp.conf

server 127.127.1.0//手工写

需要同步的上层服务器

fudge 127.127.1.0 stratum 8//手工写

同步上层服务器的stratum，大小不能超过或等于16

3)重启服务

[root@centos2 ~]# service ntpd restart

2.在从节点上进行时间同步

[root@centos3 ~]# yum -y

install

ntpdate

[root@centos3 ~]# /usr/sbin/ntpdate

192.168.1.2

3.关闭每台服务器防火墙

4.安装mysql过程略

5.启动mysql

#service mysqld

start

6.配置mysql主服务器

1)在/etc/my.cnf中修改或增加下面内容

server-id

11//修改

log-bin=master-bin//修改

定义bin-log的文件名 (默认保存在数据目录下)

log-slave-updates=true//增加

slave更新是否记入日志

2)重启mysql服务

[root@centos2 ~]# service mysqld restart

3)登录mysql程序，给从服务器授权复制权限

[root@centos2 ~]# mysql -u root -p

mysql> grant replication

slave on *.* to

'myslave'@'192.168.1.%' identified by

'123456';

mysql> flush privileges;

mysql> show master

status;

+-------------------+----------+--------------+------------------+

| File

Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| master-bin.000001|342|

+-------------------+----------+--------------+------------------+

file列显示日志名，position列显示偏移量

7.配置两台从服务器

1)在第一台从服务器/etc/my.cnf中修改或增加下面内容

server-id

22//修改

relay-log=relay-log-bin//增加

定义中继日志文件名称和路径(默认保存在数据目录下)

relay-log-index=slave-relay-bin.index//增加

定义中继日志索引文件名(记录很多的中继日志文件)默认情况，中继日志使用host_name-relay-bin.nnnnnn形式的文件名，其中host_name是从服务器主机名，nnnnnn是序列号。用连续序列号来创建连续中继日志文件，从000001开始。从服务器跟踪索引文件中目前正使用的中继日志。

默认中继日志索引文件名为host_name-relay-bin.index。默认情况，在从服务器的数据目录中创建这些文件。

2)重启mysql服务

[root@centos3 ~]# service mysqld restart

3)第二台从服务器只是server-id不同其他都和第一台一样

server-id

4)登录mysql，配置同步

#mysql -u root -p

mysql>change

mastertomaster_host='192.168.1.2',master_user='myslave',

master_password='123456',master_log_file='master-bin.000001',master_log_pos=342;

5)启动同步

mysql> start slave;

6)查看slave状态，确保以下两个至为YES

mysql> show slave status \G;

Slave_IO_Running:

Yes(I/O线程处于运行状态)

Slave_SQL_Running:

Yes(sql线程处于运行状态)

7)验证主从复制效果

①在主、从服务器上登录mysql

[root@centos2 ~]# mysql -u root -p

②在主服务器上新建数据库db_test

mysql> create database db_tese;

③在两台从服务器上查看数据库

mysql> show databases;

+--------------------+

| Database

+--------------------+

| information_schema |

| db_test|

| mysql

| performance_schema |

| test

+--------------------+

5 rows in set (0.02 sec)

三、搭建经过ssl的MySQL主从复制

这项实验采用的是mysql 5.7过程会有所不一致,my.cnf中binlog与relaylog已经设定成功

1.在主mysql服务器创建ssl/rsa文件

[root@192 ~]# cd /usr/local/mysql/bin/

[root@192bin]#mysql_ssl_rsa_setup-user=mysql--basedir=/usr/local/mysql--datadir=/usr/local/mysql/data

[root@192 bin]# cd ../data

[root@192 data]# ls

auto.cnfclient-key.pemib_logfile1

mysqlpublic_key.pemwst

ca-key.pemib_buffer_pool

ibtmp1

mysqld.err

server-cert.pem

ca.pemibdata1

master-bin.000001

performance_schema

server-key.pem

client-cert.pemib_logfile0

master-bin.indexprivate_key.pemsys

[root@192 data]# systemctl restart

mysqld#重启mysql服务

[root@192 data]# tail /usr/local/mysql/data/mysqld.err

2018-03-13T14:23:55.662963Z 0 [ERROR] SSL error: Unable to get

private key from 'server-key.pem'

#看到error项，mysql出现致命错误，发现不能获得私钥

[root@192 ~]# ll /usr/local/mysql/data/server-key.pem

#发现没有r权限读取

-rw-------. 1 root root 1679 Mar 13 22:19

/usr/local/mysql/data/server-key.pem

[root@192 ~]# chmod +r

/usr/local/mysql/data/server-key.pem

[root@192 ~]# ll /usr/local/mysql/data/server-key.pem

-rw-r--r--.

1 root root 1679 Mar 13 22:19

/usr/local/mysql/data/server-key.pem

[root@192 ~]# systemctl restart mysqld#再次重启服务，发现err日志中没有出现错误

2.数据库中配置

mysql> show variables like '%ssl%';

+---------------+-----------------+

| Variable_name | Value

+---------------+-----------------+

| have_openssl | YES

| have_ssl

YES|

| ssl_ca

ca.pem

| ssl_capath

| ssl_cert

server-cert.pem |

| ssl_cipher

| ssl_crl

| ssl_crlpath |

| ssl_key

server-key.pem |

+---------------+-----------------+

9 rows in set (0.00 sec)#发现mysql已经支持ssl安全连接

mysql启用ssl连接主要用于在internet的主从复制，局域网内可以明文复制

mysql> grant replication slave

on *.* to

'rep'@'192.168.1.%' identified by '123.com' require

ssl;#生成一个复制账号

mysql> show master status ;

#再次查看pos值

+-------------------+----------+--------------+------------------+-------------------+

| File

Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set

+-------------------+----------+--------------+------------------+-------------------+

| master-bin.000003|458

+-------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

[root@192 data]# scp ca.pem client-cert.pem client-key.pem

root@192.168.1.12:/usr/local/mysql/data/#把生成的证书传给从服务器

3.从服务器配置

[root@192 ~]# cd /usr/local/mysql/data/

[root@192 data]# chmod +r

client-key.pem

[root@192 data]# vim /etc/my.cnf

ssl-ca = /usr/local/mysql/data/ca.pem

ssl-cert = /usr/local/mysql/data/client-cert.pem

ssl-key = /usr/local/mysql/data/client-key.pem#添加以上内容

[root@192 data]# systemctl restart mysqld

mysql> show variables like '%ssl%';

+---------------+---------------------------------------+

| Variable_name | Value

+---------------+---------------------------------------+

| have_openssl | YES

| have_ssl

YES |

| ssl_ca

/usr/local/mysql/data/ca.pem

| ssl_capath

| ssl_cert

/usr/local/mysql/data/client-cert.pem |

| ssl_cipher

| ssl_crl

| ssl_crlpath |

| ssl_key

/usr/local/mysql/data/client-key.pem |

+---------------+---------------------------------------+

9 rows in set (0.00 sec)#进到数据库查看是否支持ssl安全连接

3.连接主从复制

在开启主从复制之前，可以使用ssl连接一下主mysql服务器

[root@192 data]# mysql --ssl-ca=ca.pem --ssl-cert=client-cert.pem

--ssl-key=client-key.pem -u req -p123.com -h

192.168.1.10

mysql: [Warning] Using a password on the command line interface can

be insecure.

Welcome to the MySQL monitor. Commands end with ;

or \g.

Your MySQL connection id is 5

Server version: 5.7.18-log Source distribution

reserved.

Oracle is a registered trademark of Oracle Corporation and/or

its

affiliates. Other names may be trademarks of their

respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input

statement.

mysql>#显示已经连接成功

退出主服务器！登回从mysql服务器

mysql> change master to

master_host='192.168.1.10',master_user='rep',master_password='123.com',

>master_log_file='master-bin.000003',master_log_pos=458,

>master_ssl=1,

>master_ssl_ca='/usr/local/mysql/data/ca.pem',

>master_ssl_cert='/usr/local/mysql/data/client-cert.pem',

>master_ssl_key='/usr/local/mysql/data/client-key.pem';

Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave ;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

4.测试

主服务器：mysql>

create database t412;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database

+--------------------+

| information_schema |

| mysql

| performance_schema |

| sys

| t412|

| wst

+--------------------+

6 rows in set (0.04 sec)

从服务器：

mysql> show databases ;

+--------------------+

| Database

+--------------------+

| information_schema |

| mysql

| performance_schema |

| sys

| t412|

| wst

+--------------------+

6 rows in set (0.00 sec)

四、构建mysql读写分离

1.在主机amoeba上安装java环境

[root@centos1 ~]# cp /mnt/jdk-6u14-linux-x64.bin

/usr/local/

[root@centos1 ~]# chmod +x

/usr/local/jdk-6u14-linux-x64.bin

[root@centos1 local]# cd /usr/local/

[root@centos1 local]# ./jdk-6u14-linux-x64.bin//根据提示按回车和yes即可

[root@centos1 local]# vim /etc/profile

export

JAVA_HOME=/usr/local/jdk1.6//指向jdk的安装路径

export CLAASSPATH=$JAVA_HOME/lib:$JAVA_HOME/jre/lib

export

PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin

export AMOEBA_HOME=/usr/local/amoeba/

export PATH=$PATH:$AMOEBA_HOME/bin

[root@centos1 local]# mv jdk1.6.0_14/

/usr/local/jdk1.6

[root@centos1 local]# source /etc/profile

[root@centos1 local]# java -version

java version "1.7.0_45"

OpenJDK Runtime Environment (rhel-2.4.3.3.el6-x86_64

u45-b15)

OpenJDK 64-Bit Server VM (build 24.45-b08, mixed mode)

说明：CLASSPATH指定java类搜索路径，要使用已经编写好java的类，前提当然是能够找到它们，JVM就是通过CLASSPATH来寻找类的。

java环境已经成功

2.安装并配置amoeba软件

[root@centos1 local]# mkdir /usr/local/amoeba

[root@centos1 local]# tar zxf

/mnt/amoeba-mysql-binary-2.2.0.tar.gz

-C

/usr/local/amoeba

[root@centos1 local]# chmod -R 755 /usr/local/amoeba/

[root@centos1 local]# /usr/local/amoeba/bin/amoeba

amoeba start|stop看到此提示代表amoeba安装成功

3.配置amoeba读写分离，两个slave读负载均衡

①master、slave1和slave2中开放权限给amoeba访问

mysql> grant all on *.* to 'test'@'192.168.1.%' identified by

'123.com';

②编辑amoeba.xml配置文件

[root@centos1 local]# vim

/usr/local/amoeba/conf/amoeba.xml

28 amoeba

30123456

113 master

115master(注意去掉注释)

116 slaves

③编辑dbServer.xml配置文件

[root@centos1 local]# vim

/usr/local/amoeba/conf/dbServers.xml

26test

29123.com(注意去掉注释行)

45 master"

parent="abstractServer">

48 192.168.1.2

52 slave1"

parent="abstractServer">

55192.168.1.3

58slave2"

parent="abstractServer">

61192.168.1.4

65slaves"

virtual="true">

68 1

71slave1,slave2

④配置无误后，可以启动amoeba软件，默认端口为tcp8066

[root@centos1 local]# /usr/local/amoeba/bin/amoeba start

4.测试

①在client主机上

l#yum

-y install

mysql

l然后通过代理访问mysql

l[root@centos1

~]# mysql -u amoeba -p123456 -h 192.168.1.1 -P8066(-P为大写)

②在master上创建一个表，同步到各个从服务器上，然后关掉各从服务器的slave功能，再插入区别语句

mysql> use db_test;

mysql> create table zang(id int(10),name varchar(10),address

varchar(20));

③分别在两台从服务器上

mysql>stop slave;

④然后在主服务器上插入数据

mysql> insert inot zang

values('1','zhang','this_is_master');

⑤从服务器同步了表，手动插入其他内容

slave1：

mysql> use db_test;

mysql> insert into zang

values('2','zhang','this_is_slave1');

slave2：

mysql> use db_test;

mysql> insert into zang

values('2','zhang','this_is_slave2');

⑥测试读操作

在client主机上第一次查询的结果如下：

mysql> use db_test;

mysql> select * from zang;

+------+-------+----------------+

| id | name |

address

+------+-------+----------------+

| 3 | zhang |

this_is_slave1|

+------+-------+----------------+

1 row in set (0.03 sec)

第二次查询结果如下：

mysql> select * from zang;

+------+-------+----------------+

| id | name |

address

+------+-------+----------------+

| 2 | zhang |

this_is_slave2|

+------+-------+----------------+

1 row in set (0.02 sec)

第三次查询的结果如下：

mysql> select * from zang;

+------+-------+----------------+

| id | name |

address

+------+-------+----------------+

| 3 | zhang |

this_is_slave1|

+------+-------+----------------+

1 row in set (0.03 sec)

测试写操作

mysql> insert into zang

values('4','zhang','write_test');

但是在客户机上查询不到

最终只能在主服务器上才能看到这条语句内容，说明写操作在master服务器上

mysql> select * from zang;

+------+-------+----------------+

| id | name |

address

+------+-------+----------------+

| 1 | zhang |

this_is_master |

| 4 | zhang |

write_test

+------+-------+----------------+

2 rows in set (0.03 sec)

mysql>

TechGoddess

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫