CNDBA社区
Replication Manager是使用go语言开发的数据库高可用工具，支持GTID、切换主从和failover，也支持中间件proxysql/haproxy，且自带web监控。与MHA相比，最大的区别是支持中间件、自带web监控方便管理，另外也支持双主和多级slave
环境搭建
IP Port 角色
192.168.1.101 3306 主库
192.168.1.102 3306 从库1
192.168.1.103 3306 从库2
192.168.1.103 10001 管理节点
192.168.1.101 6033 proxysql
一 安装repmgr
1 安装git高版本
yum install perl-ExtUtils-Embed
wget https://www.kernel.org/pub/software/scm/git/git-2.9.5.tar.gz
tar -zxvf git-2.9.5.tar.gz
cd git-2.9.5 /usr/local/git
cd usr/local/git
./configure prefix=/usr/local/git
make && make install
export PATH=$PATH:/usr/local/git/bin >> /etc/profile
source /etc/profile
./git --version
2 安装go语言
tar zxvf go1.15.2.src.tar.gz -C /usr/local/
echo "export PATH=$PATH:/usr/local/go/bin" >> /etc/profile
source /etc/profile
go version
3 安装repmgr(节点192.168.1.103上安装)
rpm -ivh replication-manager-osc-2.0.2-1.x86_64.rpm
Preparing… ################################# [100%]
Updating / installing…
1:replication-manager-osc-159592514################################# [100%]
ll /usr/bin/replication*
-rwxr-xr-x 1 root root 15647968 Jul 28 16:33 /usr/bin/replication-manager-cli
-rwxr-xr-x 1 root root 17980832 Jul 28 16:33 /usr/bin/replication-manager-osc
4 创建集群管理帐号
grant all privileges on *.* to repmgr identified by 'mgr2019';
5 配置文件
mkdir /etc/replication-manager
mkdir /data/replication-manager
mkdir /data/share_replication-manager
mkdir -p /data/logs
vim /etc/replication-manager/config.toml
[db3306]
title = "db3306"
db-servers-hosts = "192.168.1.101:3306,192.168.1.102:3306,192.168.1.103:3306"
db-servers-prefered-master = "192.168.1.101:3306"
db-servers-credential = "repmgr:mgr2019"
db-servers-connect-timeout = 3
replication-credential = "repuser:repuser"
failover-mode = "manual"
proxysql=true
proxysql-server="192.168.1.101"
proxysql-port=6033
proxysql-admin-port=6032
proxysql-writer-hostgroup=10
proxysql-reader-hostgroup=20
proxysql-user="admin"
proxysql-password="admin"
proxysql-bootstrap=false
[Default]
http-server = true
http-bind-address = "0.0.0.0"
http-port = "10001"
monitoring-datadir = "/data/replication-manager"
monitoring-sharedir = "/data/share_replication-manager"
replication-multi-master = false
replication-multi-tier-slave = false
failover-readonly-state = true
log-level=3
log-file = "/data/logs/replication-manager.log"
5 启动repmgr
/etc/init.d/replication-manager start
或
replication-manager-osc --config /etc/replication-manager/config.toml --cluster=db3306 monitor
6 检查
netstat -ntlp
http://192.168.1.103:10001/
[参考]：
https://docs.signal18.io/
https://www.cnblogs.com/2woods/p/9575621.html
http://www.weijingbiji.com/1798/
https://blog.csdn.net/leonpenn/article/details/90168381
脚本
- Mysql端
create user 'monitor'@'%' identified by 'monitor'; grant all privileges on *.* to 'monitor'@'%' with grant option; create user 'testopr'@'%' identified by 'Test123!'; grant all privileges on webdb.* to 'testopr'@'%'; flush privileges;
- proxysql端
(1)创建组
use main;
delete from main.mysql_replication_hostgroups;
delete from mysql_galera_hostgroups;
insert into mysql_galera_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup, active) values(11,12,13,14,1);
load mysql servers to runtime;
save mysql servers to disk;
select * from main.mysql_replication_hostgroups;
select * from main.mysql_galera_hostgroups;
(2)创建服务器节点
delete from main.mysql_servers;
delete from main.runtime_mysql_servers;
load mysql servers to runtime;
save mysql servers to disk;
delete from main.mysql_servers;
delete * from main.runtime_mysql_servers;
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections) values (11,'192.168.1.101',3306,90,2000);
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections) values (12,'192.168.1.102',3306,90,2000);
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections) values (12,'192.168.1.103',3306,90,2000);
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections) values (13,'192.168.1.101',3306,90,2000);
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections) values (13,'192.168.1.102',3306,90,2000);
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections) values (13,'192.168.1.103',3306,90,2000);
load mysql servers to runtime;
save mysql servers to disk;
select * from main.mysql_servers order by hostgroup_id, hostname, weight;
select * from main.runtime_mysql_servers order by hostgroup_id, weight;
(3)ProxySQL监控 MySQL 后端节点
use monitor;
set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor';
load mysql variables to runtime;
save mysql variables to disk;
或
UPDATE global_variables SET variable_value='monitor'
WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor'
WHERE variable_name='mysql-monitor_password';
load mysql variables to runtime;
save mysql variables to disk;
select * from global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');
(4)为 ProxySQL 配置对外访问账号
use main;
delete from mysql_users;
insert into mysql_users(username,password,default_hostgroup) values ('testopr','Test123!',10);
load mysql users to runtime;
save mysql users to disk;
select * from mysql_users;
select * from runtime_mysql_users;
(5)配置读写分离规则
use main;
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (1,1,'^select.*for update$',11,1);
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (2,1,'^select',13,1);
load mysql query rules to runtime;
save mysql query rules to disk;
select * from mysql_query_rules;
(6)验证监控
select * from mysql_server_ping_log limit 10;
(7)测试读写分离
—测试读
mysql -h127.0.0.1 -utestopr -p'Test123!' -P6033 -e 'select @@hostname;'
—测试写
mysql -h127.0.0.1 -utestopr -p'Test123!' -P6033 -Dwebdb -e 'select * from emp for update;'
—查询历史记录
select hostgroup,schemaname,username,digest_text,count_star from stats_mysql_query_digest;
select * from stats_mysql_query_digest limit 20;
Q&A
1 proxysql卸载重装后，仍能看到以前的配置
A: 须完全卸载，删除/var/lib/proxysql下的数据文件
systemctl stop proxysql
yum remove proxysql
rm -rf /var/lib/proxysql
2 proxysql日志非常大，包含很多core文件
A: ProxySQL会报告主机组的变化，建议把它设置为0，除非你试图调试“某些东西”，否则你的日志将很快变得巨大
UPDATE global_variables SET Variable_Value=0 WHERE Variable_name='mysql-hostgroup_manager_verbose';
load mysql variables to runtime;
save mysql variables to disk;
—重置PXC节点（仅限PXC集群）
/*
set wsrep_on=0;
reset master;
set wsrep_on=1;
SET @@GLOBAL.GTID_PURGED='0817ea5a-037f-ee15-56fa-6093323883fc:1-5';
*/
版权声明：本文为博主原创文章，未经博主允许不得转载。
