MySQL主从复制 + MHA高可用 + ProxySQL读写分离 + sysbench测试

这些天沉迷于全职猎人了。。。

搭建这个架构，还是花费了挺长时间，中间遇到许多坑，但也都解决了。

架构图：

环境准备：

一、一主两从复制搭建

             搭建过程略

 【注意】：server-id必须不一致

1.1 创建mha管理账号

• mysqldb1

GRANTALL PRIVILEGES ON *.* TO 4'mha_rep'@'192.168.56.%' IDENTIFIED BY '123456';

1.2 从库上执行

set global read_only=1;         --设置为只读模式

set global relay_log_purge=0;  --禁用relay log自动清除

二、三台主机配置互信

           互信配置过程略

【注意】：配置完成后，采用 ssh [hostname] date 进行验证

三、MHA搭建及测试

3.1 下载MHA

网址：https://github.com/yoshinorim/mha4mysql-manager

文件：mha4mysql-manager-master.zip

网址：https://github.com/yoshinorim/mha4mysql-node

文件：mha4mysql-node-master.zip

3.2 安装MHA

每个节点都安装manager和node，但只有mysqldb2上启动manager。

1） 解压

unzip mha4mysql-manager-master.zip

unzip mha4mysql-node-master.zip

2） 安装依赖包

• node:

--yum search perl |grep install

yum install cpan

yum install perl-Module-Install.noarch

yum install perl-DBI

yum install perl-DBD-MySQL

• manager:

--yum search perl |grep install

yum install cpan

yum install perl-Module-Install.noarch

yum install perl-DBI

yum install perl-DBD-MySQL

yum install perl-Time-HiRes.x86_64

yum install perl-Config-Tiny.noarch

yum install perl-Log-Dispatch.noarch

yum install perl-Parallel-ForkManager.noarch

3） 编译安装（node）

cd opt/mha/mha4mysql-node-master

perl Makefile.PL

make && make install

4） 编译安装（manager）

cd opt/mha/mha4mysql-manager-master

perl Makefile.PL

make && make install

3.3 配置MHA

1） 创建MHA工作目录

mkdir -p etc/masterha

mkdir –p var/log/masterha/app1

2） 创建配置文件

cp opt/mha/mha4mysql-manager-master/samples/conf/app1.cnf etc/masterha/

cd etc/masterha/

修改配app1.cnf配置文件

编辑自动切换vip脚本

vi usr/local/bin/master_ip_failover

chmod +x usr/local/bin/master_ip_failover

配置VIP：

     在开启MHA之前，需先在Master上设置虚拟IP，然后master_ip_failover脚本在发生切换时才会自动切换虚拟IP.

ifconfig eth1:0192.168.56.100 up

/sbin/arping-c 3 -A 192.168.56.100 -I eth1 (默认对eth0网卡进行arping)

3） 检查配置文件

利用mha工具检测ssh

/usr/local/bin/masterha_check_ssh --conf=/etc/masterha/app1.cnf

报以下错：

错误解决：

wget http://xrl.us/cpanm --no-check-certificate -O sbin/cpanm && chmod +x  sbin/cpanm

cpanm Socket

再次执行：

4） 利用mha工具check检查repl环境

/usr/local/bin/masterha_check_ssh--conf=/etc/masterha/app1.cnf

报以下错:

在配置文件上port参数

如果还是报错：开启debug模式，进行调试。

在配置文件中全局选项中加上参数：log_level=debug

可以参考以下文章，对MHA代码进行修改

https://github.com/wubx/mha4mysql-node/commit/814e51e1250183dcde6f11510c4dd303e7272a4c

https://github.com/wubx/mha4mysql-manager/commit/d55ba2eccd04b45a7edbb70a087e8469d82164ee

5） 开启MHAManager，并查看状态

masterha_manager --conf=/etc/masterha/app1.cnf > var/log/masterha/app1/manager.log&

查看状态：

/usr/local/bin/masterha_check_status --conf=/etc/masterha/app1.cnf

3.4 测试MHA

1） 模拟MySQL故障，查看VIP漂移和MySQL自动切换情况

mysqldb1上执行：

mysqladmin -S tmp/mysql3307.sock -pwanbinshutdown

mysqldb2上观察manager日志：

tail -f /var/log/masterha/app1/manager.log

MHA发生切换后会在日志目录，也就是/var/log/masterha/app1产生app1.failover.complete文件

2） 切换之后MHA之后，mysqldb1重新加入集群

CHANGE MASTER TOmaster_host='192.168.56.59',

master_port=3307,

master_user='repl',

master_password='wanbin',

master_auto_position=1;

start slave     --启动slave服务

3） masterha_master_switch工具进行主从切换

masterha_master_switch --master_state=alive--conf=/etc/masterha/app1.cnf

mysqldb2上执行：

CHANGE MASTER TO MASTER_HOST='192.168.56.60',MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl',MASTER_PASSWORD='wanbin';

start slave

vip配置：

mysqldb2上：

ifconfig eth1:0 down

set globalread_only=1;         --设置为只读模式

set globalrelay_log_purge=0;  --禁用relay log自动清除

mysqldb1上：

ifconfig eth1:0 192.168.56.100 up

/sbin/arping -c 3 -A 192.168.56.100 -I eth1

3.5 利用sysbench工具进行测试

安装：

curl -shttps://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh |sudo bash

sudo yum -y install sysbench

sysbench usr/share/sysbench/oltp_read_write.lua\

--mysql-socket=/tmp/mysql3307.sock \

--mysql-user=root \

--mysql-password=wanbin \

--mysql-db=sbtest \

--db-driver=mysql \

--tables=10 \

--table-size=50000 \

--report-interval=10 \

--threads=128 \

--time=120 \

prepare/run/cleanup

prepare:准备数据，

run：执行压测，

cleanup:清除数据

测试的时候模拟主库宕机，MHA进行切换，观察manager.log日志。

四、ProxySQL

4.1 ProxySQL安装

点击https://github.com/sysown/proxysql/releases网站，下载相应版本

rpm -ivhproxysql-1.3.8-1-centos67.x86_64.rpm

4.2 ProxySQL配置

启动ProxySQL

service proxysql start

遇到报错：

解决方法：

把这两行注释掉

登入proxysql控制台：

mysql -uadmin -padmin -h127.0.0.1 -P6032

1） 配置后端MySQL。

登陆ProxySQL，把MySQL主从信息添加进去。

insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)values(100,'192.168.56.100',3307,1,512,10,'test proxysql');

--主库用vip

insert intomysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)values(101,'192.168.56.59',3307,1,512,10,'test proxysql');

insert intomysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)values(101,'192.168.56.59',3307,1,512,10,'test proxysql');

2） 配置后端MySQL用户

mysqldb1上执行

GRANT USAGE ON *.* TO 'proxy'@'192.168.56.57' IDENTIFIED BY'proxy';      --监控用户

GRANT ALL PRIVILEGES ON sbtest.* TO 'wb'@'192.168.56.57'IDENTIFIED BY 'wanbin';      --程序用户

mysqldb4(proxysql)上执行

insert intomysql_users(username,password,active,default_hostgroup,transaction_persistent)values('wb','wanbin',1,100,1);

3） 设置监控账号

mysqldb4(proxysql)上执行

UPDATE global_variables SET variable_value='proxy' WHEREvariable_name='mysql-monitor_username';

UPDATE global_variables SET variable_value='proxy' WHEREvariable_name='mysql-monitor_password';

4） 加载配置

因为修改了servers,users,variables，所以要执行：

load mysql servers to runtime;

load mysql users to runtime;

load mysql variables to runtime;

save mysql servers to disk;

save mysql users to disk;

save mysql variables to disk;

save mysql users to mem; --设置明文密码时，可以使用此命令进行转换hash值密码。

5） 连接数据库，通过proxysql客户端接口访问（6033）

mysql -uwb -pwanbin -h192.168.56.57 -P6033

进行测试，创建表，及插入数据测试。。。

–登陆proxysql控制台

mysql -uadmin -padmin -h127.0.0.1 -P6032

--查看各类命令的执行情况

select Command,Total_Time_us,Total_cnt fromstats_mysql_commands_counters where Total_cnt >0;

-- 查看各类SQL的执行情况

select * from stats_mysql_query_digest;

注意hostgroup，都是100，说明还没实现读写分离。。

6） 定义SQL路由规则

INSERT INTOmysql_query_rules(active,match_pattern,destination_hostgroup,apply)VALUES(1,'^SELECT.*FOR UPDATE$',100,1);

INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply)VALUES(1,'^SELECT',101,1);

load mysql query rules to runtime;

save mysql query rules to disk;

定义完成后进行测试：

4.3 sysbench进行测试

mysqldb4上

测试前先清空stats_mysql_query_digest_reset表

SELECT 1 FROMstats_mysql_query_digest_reset LIMIT 1;

sysbench /usr/share/sysbench/oltp_read_write.lua\

--mysql-host=192.168.56.57 \

--mysql-port=6033 \

--mysql-user=wb \

--mysql-password=wanbin \

--mysql-db=sbtest \

--db-driver=mysql \

--tables=10 \

--table-size=50000 \

--report-interval=10 \

--threads=128 \

--time=120 \

prepare/run/cleanup

prepare：生成数据

run:运行测试

cleanup：清除数据

selecthostgroup,schemaname,username,substr(digest_text,120,-120),count_star fromstats_mysql_query_digest;

参考：

http://www.cnblogs.com/zhoujinyi/p/6829983.html

http://www.cnblogs.com/zhoujinyi/p/3808673.html

http://www.ywnds.com/?p=10111