ProxySQL+Replication-manager搭建MySQL高可用集群(失败)

mtain 2023年06月14日 108次浏览

一、说明

MySQL高可用方案:

  1. 官方MGR(组复制)
  2. MHA(传统复制)
  3. replication-manager(GTID)

replication-manager

https://github.com/signal18/replication-manager
https://docs.signal18.io/

ProxySQL

https://proxysql.com/

二、集群规划

主机名ip角色
mysql-gateway192.168.123.217ProxySQL,replication-manager
mysql-server01192.168.123.101mysql-server
mysql-server02192.168.123.102mysql-server
mysql-server03192.168.123.103mysql-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. 创建账户

账户名密码角色
dbapassword管理
reppassword同步
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

1686813028716.png

命令行操作

切换主从

[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;