Mysql主从部署及基于Mysql-Proxy、mycat读写分离配置

Mysql主从部署及基于Mysql-Proxy、mycat读写分离配置

 服务环境:

Master:172.20.26.167

Slave:172.20.26.198

Mysql-Proxy、mycat:172.20.26.24

一、Mysql 主从

172.20.26.167、172.20.26.198 已配置主从同步(该文档后面内容也还有涉及主从配置的方法)

172.20.26.198 服务器上,修改my.cnf,添加log-bin=node2-bin

保存退出,重启mariadb服务

[root@localhost ~]# systemctl restart maria

登录mysql ,进行授权,并刷新

MariaDB [(none)]> grant replication slave on *.* to "tongbu"@"172.20.26.167" identified by "123456";

MariaDB [(none)]> flush privileges;

授权后查看172.20.26.198上master的 状态

MariaDB [(none)]> show master status;

在172.20.26.167服务器上连接172.20.26.198的主库,并查看连接状态,均为yes。

MariaDB [(none)]> change master to

    -> master_host="172.20.26.198",

    -> master_user="tongbu",

    -> master_password="123456",

    -> master_log_file="node2-bin.000001",

    -> master_log_pos=476;

MariaDB [(none)]> slave start;   #启动slave 服务

MariaDB [(none)]> show slave statusG

172.20.26.167、172.20.26.198的状态均为yes:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

查看172.20.26.167上数据库名称和数量

在72.20.26.198查看数据库名称和数量与172.20.26.167的一致。

在172.20.26.167上创建一个新的数据库wordpress

MariaDB [(none)]> create database wordpress charset utf8;

在172.20.26.198查看,新创建的wordpress数据库已同步过来

在172.20.26.198 服务器上对wordpress数据库进行创建t1表

MariaDB [wordpress]> create table t1 (

    -> id int auto_increment primary key,

    -> name varchar(20)

-> );

回到172.20.26.167服务器上查询,t1表已同步过来,表结构也是我们创建的表结构。

在172.20.26.198服务器上对t1进行插入数据

在172.20.26.167服务器上查询t1表,已将刚才在172.20.26.198服务器上对t1插入的数据同步过来

而172.20.26.167服务器上对t1表也进行插入数据

MariaDB [wordpress]> insert t1 values (null,"xiaohu");

回到172.20.26.198服务器上查询,刚才在172.20.26.167服务器对t1表插入的数据也同步过来了。

到此mysql互为主从同步完成,不过这个实现方案只适用于一个php对应一个mysql(主从),如果后端是php均衡的时候,可以使用步长来解决。

在172.20.26.167服务器上,设置步长为2,从1开始

auto-increment-increment=2

auto-increment-offset=1

保存退出

[root@v1 ~]# systemctl restart mariadb

在172.20.26.198服务器上,设置步长为2,从2开始

auto-increment-increment=2

auto-increment-offset=2

保存退出

重启mariadb服务

[root@localhost ~]# systemctl restart mariadb

然后检查主从数据库同步状态情况均为yes 即可

在172.20.26.198服务器对wordpress数据库里的t1表插入两条数据,查看id为4、6

这个时候在172.20.26.167服务器上同样对t1表进行插入数据,

insert t1 values (null,”xiaoqiang”);

插入的数据id变为7

使用步长的互为主从同步配置完成。

二、基于Mysql-Proxy实现读写分离

proxy可以选择和mysql部署在同一台服务器,也可以选择单独部署在另一台独立服务器。

Master:172.20.26.167

Slave:172.20.26.198

Proxy: 172.2026.24

确保172.20.26.167、172.20.26.198主从关系保持正常

在proxy172.20.26.24上,安装mariadb以及mariadb-server

yum install mariadb mariadb-server -y

进入/usr/src 目录,rz上传mysql-proxy软件包

tar xf mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz

mv mysql-proxy-0.8.4-linux-el6-x86-64bit /usr/local/mysql-proxy

Mysql-proxy 解压后已经有bin目录二进制文件,是免编译的,可直接使用。

接下来配置环境变量

[root@bogon src]# echo "export PATH=/usr/local/mysql-proxy/bin:$PATH" > /etc/profile.d/mysql-proxy.sh

[root@bogon src]# . /etc/profile.d/mysql-proxy.sh

创建mysql-proxy 系统用户

[root@bogon src]# useradd -r mysql-proxy

启动Mysql-Proxy中间件:

mysql-proxy --daemon --log-level=error --user=mysql-proxy --keepalive --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="172.20.26.167:3306" --proxy-read-only-backend-addresses="172.20.26.198:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua"

查看端口,4040 是数据端口,4041是管理端口

通过管理端口查看当前的连接状态

查看主从库的地址以及端口信息,state 状态均为unknown, 是目前还没有人来连接。

在172.20.26.167服务器上对172.20.26.24进行授权

MariaDB [(none)]> grant all on *.* to "mysql-proxy"@"172.20.26.24" identified by "123456";

MariaDB [(none)]> flush privileges;

在172.20.26.24上连接172.20.26.167的mysql服务

mysql -h 172.20.26.167 -umysql-proxy -p123456

因为172.20.26.167与172.20.26.198为主从同步,所以连接172.20.26.198也同样可以连接

接下来可以连接4040数据端口创建数据库,再连接4041管理接口查看状态,rw 已为UP 状态

mysql -umysql-proxy -h172.20.26.24 -p123456 -P4040 -e "create database jingfeng charset utf8;"

mysql -uadmin -h172.20.26.24 -padmin -P4041

select * from backends;

再将172.20.26.198的只读状态激活一下,这里我们现在登录的是172.20.26.24,其实是通过4040端口登录172.20.26.167或者是172.20.26.198服务器。

mysql -uadmin -h172.20.26.24 -padmin -P4040

mysql -umysql-proxy -h172.20.26.24 -p123456 -P4040 -e "select * from jingfeng.t1;"

再进到4041来查询,多执行几次查询来激活从库的只读状态。

由于mysql-proxy 默认都是由主库进行读写,从库不执行读写,只有超过限制的连接数之后再由从库提供服务。

Vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

[root@localhost ~]# mysql -uadmin -h172.20.26.24 -padmin -P4041

接下来就是验证读写分离有没有实现

由于之前我们设置172.20.26.167、172.20.26.198是互为主从,所以可以在172.20.26.198上设置set sql_log_bin=0  暂时不记录到二进制文件里。

在172.20.26.198上登录mysql

[root@localhost ~]# mysql

MariaDB [(none)]> show variables like "%log_bin";

MariaDB [(none)]> set sql_log_bin=0;

因为我们刚才部署了互为主从,现在我们验证读写分离,所以我们必须在从库172.20.26.198服务器上加数据,让它不同步到主库,然后查询,如果查到是从库的,表示读写分离成功

MariaDB [(none)]> use jingfeng

MariaDB [jingfeng]> select * from t1;

MariaDB [jingfeng]> insert t1 values (2,"xiaofeng");  插入数据

172.20.26.167上有三个数据

查看主库172.20.26.167的数据情况,只有两个数据

在172.20.26.24服务器上查询,查询到三个数据,说明数据是有从库172.20.26.198返回的

在172.20.26.24上登录4040端口进行写数据

在172.20.26.24上通过4040端口登录查询

在主库172.20.26.167上查询,发现多了一条数据5 wangpeng ,数据写到了主库上了

刚才在172.20.26.24上写的数据,已写到了172.20.26.167上了,然后主库172.20.26.167同步给了从库172.20.26.198,所以在172.20.26.198上查询发现也有5 wangpeng 这条数据

可以查询主从库的连接数量信息

Mysql-Proxy读写分离实现完成。

三、基于Mycat实现读写分离

基于三台服务器实现mysql读写分离(mycat):一台做代理,另外两台著配置MySQL一主一从

环境:

Master:172.20.26.167

Slave:172.20.26.198

Mycat:172.20.26.24

172.20.26.167、172.20.26.198 已互为主从,172.20.26.24上部署了mycat-proxy服务。

先将172.20.26.24上的关闭mysql-proxy 服务,查看4040、4041端口是否已关闭

[root@bogon ~]# pkill mysql-proxy

[root@bogon ~]# netstat -nltp

进入源码安装目录,将mycat上传到/usr/src目录下

[root@bogon ~]# cd /usr/src

[root@bogon src]# rz

[root@bogon src]# tar xf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz

解压后mycat目录移动到/usr/local/

解压出来已带有bin目录,不需要再编译,由于mycat是基于java开发的,需要安装java环境,以及添加环境变量

[root@bogon src]# yum install java –y

配置mycat环境变量:

echo "export PATH=/usr/local/mycat/bin:$PATH" > /etc/profile.d/mycat.sh

. /etc/profile.d/mycat.sh  或者 source /etc/profile.d/mycat.sh

授权mycat

在主库172.20.26.167上登录mysql,执行授权信息,从库会自动同步:

MariaDB [(none)]> grant all on *.* to "mycat-proxy"@"172.20.26.24" identified by "123456";

MariaDB [(none)]> flush privileges;

编辑修改mycat中的server.xml文件里的相关参数

server.xml:定义用户以及系统相关变量,如端口等

[root@bogon src]# vim /usr/local/mycat/conf/server.xml

8066 是数据端口,9066是管理端口(mysql-proxy的4040、4041类似)

指定管理用户名、密码、逻辑库等信息

用户名:mycat,密码:123456 逻辑库:lutixiadb

保存退出

再编辑修改mycat中的schema.xml文件里的相关参数

schema.xml:定义逻辑库、表、数据节点等

balance指的负载均衡类型,目前的取值有4种:

1. balance=”0”, 不开启读写分离机制,所有读操作都发送到当前可用的writeHost上。

2. balance=”1”,全部的readHost与stand bywriteHost参与select语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与 M2互为主备),正常情况下,M2,S1,S2都参与select语句的负载均衡。

3. balance=”2”,所有读操作都随机的在writeHost、readhost上分发。

4. balance=”3”,所有读请求随机的分发到wiriterHost对应的readhost执行,writerHost不负担读压力

注意:balance=3只在1.4及其以后版本有,1.3没有。

[root@bogon src]# vim /usr/local/mycat/conf/schema.xml

指定逻辑库、数据节点、真实库

balance 为0表示不开启读写均衡,如果需要开启均均衡将balance 设置为3,所有读都走从库(如果只是一主一从,host名称可以不用改),改为M2表示第二组的主库 master,当第一组的主库172.20.26.167宕机时,自动将从库172.20.26.198切换为主库

保存退出,启动mycat服务(如果启动报错,可以检查一下环境变量)

netstat –nltp  查看mycat的端口8066、9066 (如果8066、9066没有启动,可以检查一下server.xml、schema.xml 的参数设置)

8066、9066 端口一直在,表示mycat 服务没有问题,如果一会有一会没有,表示mycat服务有问题。

连接mycat 的8066端口服务,查看逻辑库

[root@localhost ~]# mysql -h127.0.0.1 -umycat -p123456 -P8066

查询数据库及表,是4条数据,而172.20.26.198从库上也是这4条数据,但是而主库172.20.26.167上是3条数据,表明查询操作是由从库提供服务。

现在在172.20.26.24上的mycat连接后进行写数据操作,然后在查询主库和从库的数据

MySQL [lutixiadb]> insert t1 values (6,"xiaotong");

在主库172.20.26.167上查询,发现写还是直接写到主库里

主库172.20.26.167再把数据同步给从库172.20.26.198

如果想主库也参与查询,可以将schema 里的balance 改为2,表示所有的读操作是随机给到主库或者从库

[root@localhost ~]# vim /usr/local/mycat/conf/schema.xml

保存退出

重启mycat服务, 重启连接mycat

 [root@localhost ~]# mycat restart

[root@localhost ~]# mysql -h127.0.0.1 -umycat -p123456 -P8066

再插入一条数据,再查询,发现查询操作在主从库之间轮询操作

MySQL [lutixiadb]> insert t1 values (7,"xiaoning");

如果主库172.20.26.167意外宕机

mycat重新可以连接8066端口,进行查询,一切正常

[root@localhost ~]# mysql -h127.0.0.1 -umycat -p123456 -P8066

写操作也没问题, 说明从库已替代了主库,由mycat实现自动切换。

数据库管理权限DML

[root@localhost ~]# vim /usr/local/mycat/conf/server.xml

dml="0110" 中,0代表禁止insert,1代表允许更新,1代表允许查询,0代表禁止删除

重启mycat服务

[root@localhost ~]# mycat restart

mycat重新可以连接8066端口,进行查询,

因禁止了插入权限,所以失败,(0110—禁止insert)

可以更新数据

MySQL [lutixiadb]> update t1 set name="jfedu" where id=5;

server.xml配置文件里的Firewall 设置,默认没有开启,所以别的机器可以连接

[root@localhost ~]# vim /usr/local/mycat/conf/server.xml

如果去掉注释,只能本机连接

保存退出,重启mycat服务,再从172.20.26.167主库机器登录,将无法登录。

至此,基于mycat实现mysql读写分离的配置就完成了。

四、配置一主多从,实现读均衡

Master:172.20.26.167

Slave1:172.20.26.198

Slave2:172.20.26.86

Mycat:172.20.26.24

Master172.20.26.167安装部署:

1、yum install mariadb mariadb-server -y

2、修改my.cnf文件,添加log-bin=master167、server-id=167,保存退出,重启mariadb

3、验证二进制功能开启是否成功,show master status;

4、授权指定哪些机器可以连接这台主库服务器,grant replication slave on *.* to "tongbu"@"172.20.26.%" identified by "123456";

Slave172.20.26.198安装部署:

  1. yum install mariadb mariadb-server -y
  2. 修改my.cnf文件,添加server-id=198,保存退出,重启mariadb

登录mysql,连接主库,change master to

    -> master_host="172.20.26.167",

    -> master_user="tongbu",

    -> master_password="123456",

    -> master_log_file="mysql167-bin.000003",

-> master_log_pos=245;

  1. slave start;   启动slave从库,show slave statusG 查看与slave的状态,均为yes,表示与主库连接成功:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

  1.  查看master、slave主从库的数据库数量及名称是否一致

Slave172.20.26.86安装部署:

因从库172.20.26.86服务器是在前面主从的基础上增加的,安装完mysql后需要导入从主库导出的数据,来保持主从库数据一致。

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

在my.cnf中添加server-id=86

保存退出。

在172.20.26.167主库上将数据导出到all.sql 文件,再将all.sql文件拷贝到172.20.26.86的root目录下,并对172.20.26.86进行授权。

MariaDB [(none)]> grant replication slave on *.* to "tongbu"@"172.20.26.%" identified by "123456";

在172.20.26.86上登录mysql ,将all.sql 导入

MariaDB [wordpress]> source /root/all.sql

MariaDB [wordpress]> show databases;      #主库的数据都已导入

查看主库172.20.26.167上节点文件及pos点

在新增加的从库172.20.26.86上连接主库

MariaDB [(none)]> change master to

    -> master_host="172.20.26.167",

    -> master_user="tongbu",

    -> master_password="123456",

    -> master_log_file="mysql-bin.000007",

    -> master_log_pos=245;

MariaDB [(none)]> slave start;   #启动slave 服务

MariaDB [(none)]> show slave statusG     #查看slave的连接状态,均为yes

Mycat安装部署

1、yum install java -y    #安装mycat

2、配置mycat环境变量:

echo "export PATH=/usr/local/mycat/bin:$PATH" > /etc/profile.d/mycat.sh

. /etc/profile.d/mycat.sh

3、在主库执行授权mycat,从库会自动同步:

grant all on *.* to "mycat-proxy"@"172.20.26.24" identified by "123456";

4、vim /usr/local/mycat/conf/server.xml:定义用户以及系统相关变量,8066 是数据端口,9066是管理端口,用户名:mycat,密码:123456 逻辑库:lutixiadb

在slave 172.20.26.86上检查是否有mycat-proxy用户

MariaDB [jingfeng]> select user,host from mysql.user;

因为172.20.26.86上数据库数据是从主库172.20.26.167导出来导入的,所以是有mycat-proxy用户的。

在mycat上添加一台从库

[root@localhost ~]# vim /usr/local/mycat/conf/schema.xml

保存退出,重启mycat服务

[root@localhost ~]# mycat restart      (/usr/local/mycat/bin/mycat restart)

netstat -nltp   #查看8066、9066  端口

在slave 172.20.26.198 上插入数据

MariaDB [jingfeng]> insert t1 values (198,"wangxiao");

在slave 172.20.26.86 上插入数据

MariaDB [jingfeng]> insert t1 values (86,"wangqiang");

在mycat上进行查询

[root@localhost ~]# mysql -h127.0.0.1 -umycat -p123456 -P8066

这里有点问题,mycat 查询数据都是由主库返回的数据,两台从库无法返回数据,