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安装部署：
- yum install mariadb mariadb-server -y
- 修改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;
- slave start; 启动slave从库，show slave statusG 查看与slave的状态，均为yes，表示与主库连接成功：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
- 查看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 查询数据都是由主库返回的数据，两台从库无法返回数据，
