一、说明
MySQL高可用方案：
- 官方MGR(组复制)
- MHA(传统复制)
- replication-manager(GTID)
replication-manager
https://github.com/signal18/replication-manager
https://docs.signal18.io/
ProxySQL
二、集群规划
| 主机名 | ip | 角色 |
|---|---|---|
| mysql-gateway | 192.168.123.217 | ProxySQL,replication-manager |
| mysql-server01 | 192.168.123.101 | mysql-server |
| mysql-server02 | 192.168.123.102 | mysql-server |
| mysql-server03 | 192.168.123.103 | mysql-server |
三、部署步骤
安装MySQL
1. 安装
yum localinstall -y mysql-*.rpm
2. 初始化mysql
更改mysql数据库所属于用户及其所属于组
mysqld --initialize
chown mysql:mysql /var/lib/mysql -R
3. 启动mysql数据库
[root@test ~]# systemctl start mysqld.service
[root@test ~]# grep 'password' /var/log/mysqld.log
2023-06-15T06:33:41.365581Z 1 [Note] A temporary password is generated for root@localhost: s4ZwHxs-qTra
4. 修改数据库root密码
[root@hadoop log]# mysql -u root -p
mysql中执行
set password=password('EuuWwnZK3frG');
flush privileges;
5. 设置字符编码和表名不区分大小写
vi /etc/my.cnf
增加
[mysqld]
character-set-server=utf8
lower_case_table_names=1
systemctl restart mysqld.service
systemctl enable mysqld.service
6. 创建账户
| 账户名 | 密码 | 角色 |
|---|---|---|
| dba | password | 管理 |
| rep | password | 同步 |
grant all privileges on *.* to dba@'%' identified by 'password' with grant option;
grant all privileges on *.* to rep@'%' identified by 'password' with grant option;
注意:
复制账户只赋复制权限
GRANT REPLICATION SLAVE ON *.* to 'rep'@'%' identified by 'password';
7. 配置主从复制(增强半同步复制模式)
安装半同步复制插件
主：
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
set global rpl_semi_sync_master_enabled=on;
从：
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled=on;
注：主从节点会进行切换，所以主从最好全部安装插件
主节点和从节点配置开启binlog
server-id设置每台不一样
[mysqld]
server-id=10
log-bin=mysql-bin
log-bin-index=master-bin.index
binlog-format=ROW
gtid-mode=on
enforce-gtid-consistency=true
重启服务：systemctl restart mysqld
主节点配置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从节点配置
mysql> change master to master_host='192.168.123.101',master_user='rep',master_password='password',master_log_file='mysql-bin.000002',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
查看复制状态(Slave_IO_Running和Slave_SQL_Running应为Yes)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.123.101
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-server02-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 536
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: 40fb493c-0a9b-11ee-8865-000c290579ef
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
安装Replication-manager
1. 安装
vi /etc/yum.repos.d/signal18.repo
[signal18]
name=Signal18 repositories
baseurl=http://repo.signal18.io/centos/$releasever/$basearch/
gpgcheck=0
enabled=1
yum install replication-manager-osc
安装目录/etc/replication-manager
2. 配置
cp /etc/replication-manager/config.toml /etc/replication-manager/config.toml.bak
echo > /etc/replication-manager/config.toml
vi /etc/replication-manager/config.toml
[Default]
include = "/etc/replication-manager/cluster.d"
#########
## LOG ##
#########
log-file = "/var/log/replication-manager.log"
log-heartbeat = false
##########
## HTTP ##
##########
http-server = true
http-bind-address = "0.0.0.0"
http-port = "10001"
http-auth = false
http-session-lifetime = 3600
http-bootstrap-button = false
http-refresh-interval = 4000
#########
## API ##
#########
api-credentials = "admin:repman"
api-port = "10005"
cd /etc/replication-manager/cluster.d
cp cluster1.toml.sample mysql_cluster.toml
vi mysql_cluster.toml
[mysql_cluster]
title = "mysql_cluster"
db-servers-hosts = "192.168.123.101:3306,192.168.123.102:3306,192.168.123.103:3306"
db-servers-prefered-master = "192.168.123.101:3306"
db-servers-credential = "dba:PASSWORD"
db-servers-connect-timeout = 1
replication-credential = "rep:PASSWORD"
##############
## FAILOVER ##
##############
# 故障自动切换
failover-mode = "automatic"
# 30s内再次发生故障不切换，防止硬件问题或网络问题
failover-time-limit=30
# vip切换脚本
# failover-post-script = "/etc/replication-manager/vip_up.sh"
# 【默认】如果一个从站仍可以从主站获取事件，则取消故障转移
failover-falsepositive-heartbeat = true
# 【默认】故障转移和切换将从库设置为只读
failover-readonly-state = true
#【默认】调度程序相关
failover-event-scheduler = false
failover-event-status = false
# 【默认】当从库延迟超过30s时，选主时忽略此服务器
failover-max-slave-delay = 30
启动
systemctl restart replication-manager
systemctl status replication-manager
systemctl enable replication-manager
3. 使用
浏览器访问
http://【IP】:10001/
admin/repman
命令行操作
切换主从
[root@mysql cluster.d]# replication-manager-cli switchover --cluster=mysql_cluster
| Group: mysql_cluster | Mode: Manual
Id Host Port Status Failures Using GTID Current GTID Slave GTID Replication Health Delay RO
db15796154553354908838 192.168.123.101 3306 Slave 0 Yes 0-13402687052191002478-7,0-2820065391535071335-6 0-0-0,0-0-0 0 ON
db17461984861642470051 192.168.123.102 3306 Master 0 Yes 0-13402687052191002478-7,0-2820065391535071335-6 0 OFF
db12776641570098389483 192.168.123.103 3306 Slave 0 Yes 0-13402687052191002478-7,0-2820065391535071335-6,0-11210045953021579631-4 0-0-0,0-0-0,0-0-0 0 ON
INFO[0012] [mysql_cluster] INFO - Master switch on 192.168.123.101:3306 complete
INFO[0012] [mysql_cluster] INFO - Change master on slave 192.168.123.103:3306
INFO[0012] [mysql_cluster] INFO - Waiting for slave 192.168.123.103:3306 to sync
INFO[0012] [mysql_cluster] INFO - Switching other slaves to the new master
INFO[0012] [mysql_cluster] INFO - Set state called from /var/jenkins_home/workspace/go/src/github.com/signal18/replication-manager/cluster/cluster_fail.go#361
INFO[0012] [mysql_cluster] INFO - Server 192.168.123.102:3306 state transition from Master changed to: Slave
INFO[0012] [mysql_cluster] INFO - Doing MySQL GTID switch of the old master
INFO[0012] [mysql_cluster] INFO - Switching old leader to slave
INFO[0012] [mysql_cluster] INFO - Killing new connections on old master showing before update route
INFO[0012] [mysql_cluster] INFO - Inject fake transaction on new master 192.168.123.101:3306
INFO[0012] [mysql_cluster] INFO - Waiting 2s for unmanaged proxy to monitor route change
INFO[0012] [mysql_cluster] INFO - Failover proxies
INFO[0012] [mysql_cluster] INFO - Resetting slave on new master and set read/write mode on
INFO[0012] [mysql_cluster] INFO - Saved called from /var/jenkins_home/workspace/go/src/github.com/signal18/replication-manager/cluster/cluster_fail.go#234
INFO[0012] [mysql_cluster] INFO - Stopping slave threads on new master
INFO[0012] [mysql_cluster] INFO - MySQL GTID saving crash info for replication ExexecutedGtidSet 545dc080-0b47-11ee-98d3-000c29a848f9:1-7,
90eb0a6d-0b46-11ee-8764-000c290579ef:1-6
INFO[0012] [mysql_cluster] INFO - Save replication status and crash infos before opening traffic
INFO[0012] [mysql_cluster] INFO - Waiting sync IO_Pos:{mysql-bin.000004 %!!(MISSING)s(bool=true)}/334, Slave_Pos:{mysql-bin.000004 %!!(MISSING)s(bool=true)} 334
INFO[0012] [mysql_cluster] INFO - Reading all relay logs on 192.168.123.101:3306
INFO[0012] [mysql_cluster] INFO - Waiting for candidate master 192.168.123.101:3306 to apply relay log
INFO[0012] [mysql_cluster] INFO - Freezing writes rejecting writes via FTWRL on 192.168.123.102:3306
INFO[0012] [mysql_cluster] INFO - Freezing writes killing all other remaining threads on 192.168.123.102:3306
INFO[0012] [mysql_cluster] INFO - Freezing writes decreasing max_connections to 1 on 192.168.123.102:3306
INFO[0012] [mysql_cluster] INFO - Freezing writes saving max_connections on 192.168.123.102:3306
INFO[0012] [mysql_cluster] INFO - Freezing writes set read only on 192.168.123.102:3306
INFO[0012] [mysql_cluster] INFO - Freezing writes stopping all slaves on 192.168.123.102:3306
INFO[0012] [mysql_cluster] INFO - Set state called from /var/jenkins_home/workspace/go/src/github.com/signal18/replication-manager/cluster/srv_set.go#82
INFO[0012] [mysql_cluster] INFO - Server 192.168.123.101:3306 state transition from Slave changed to: Master
INFO[0012] [mysql_cluster] INFO - Slave 192.168.123.101:3306 has been elected as a new master
| Group: mysql_cluster | Mode: Manual
Id Host Port Status Failures Using GTID Current GTID Slave GTID Replication Health Delay RO
db15796154553354908838 192.168.123.101 3306 Master 0 Yes 0-13402687052191002478-7,0-2820065391535071335-6 0 ON
db17461984861642470051 192.168.123.102 3306 Slave 0 Yes 0-13402687052191002478-7,0-2820065391535071335-6 0 ON
db12776641570098389483 192.168.123.103 3306 Slave 0 Yes 0-13402687052191002478-7,0-2820065391535071335-6,0-11210045953021579631-4 0-0-0,0-0-0,0-0-0 0 ON
安装ProxySQL
1. 安装
Adding the repository:
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/centos/$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key
EOF
Installing ProxySQL:
yum install proxysql OR yum install proxysql-version
2. 配置ProxySQL
cat /etc/proxysql.cnf
暂时使用默认配置
3. proxysql admin设置读写分离
# 登录操作
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
show databases;
# 配置mysql后端节点信息
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'192.168.123.101',3306,1,1000,10,'');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(200,'192.168.123.101',3306,1,1000,10,'');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(200,'192.168.123.102',3306,1,1000,10,'');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(200,'192.168.123.103',3306,1,1000,10,'');
load mysql servers to runtime;
save mysql servers to disk;
select * from mysql_servers;
select * from runtime_mysql_servers;
# 配置对外账号密码
insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('dba','qQN0mqIMJq5M',1,1000,1);
load mysql users to runtime;
save mysql users to disk;
select * from mysql_users;
# 配置复制组关系
insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,check_type,comment) values (100,200,'read_only','读写分离高可用');
load mysql servers to runtime;
save mysql servers to disk;
select * from mysql_replication_hostgroups;
select * from runtime_mysql_servers;