背景
mysql8安装
备机安装mysql8
- 主备状态查看
mysql router安装
keepalived安装
proxy安装
MHA安装
my.cnf配置
Granafa监控
主从同步处理
- 主从error处理
  - pt-table-sync安装
  - 手动处理
  - 自动处理
- 主挂了从升主处理
- 主挂了从升主之后，原主恢复变从
主从同步业务优化处理
- before
- after
canal 增量+全量处理

背景

centos7.6机器，256G内存，64C，3.5T SSD
之前线上已经有mysql运行中的机器了，所以会在原有基础上修改/etc/my.cnf配置文件
目前架构是一主一台，两个集群，数据是应用层双写，业务不太敏感数据轻微不一致
使用mysql router做集群内的读写分离，两台都安装router，并使用vip做router的高可用
没用使用MHA等方案监控自动主从切换，当主挂了时，人工介入目前，后续可能考虑
架构，单集群展示，VIP会漂移到Router A或者Router B，然后根据读写到mysqlA或者mysqlB

image.png

mysql8安装

安装repo，注意看有没报错哈，没报错就我截图那样

yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

image.png

安装mysql8，注意看有没报错哈，没报错就我截图那样

yum install -y --nogpgcheck mysql-community-server

image.png

看下机器之前有没mysql数据，有的话考虑rm -rf掉

ll /var/lib/mysql/

我是在本地编辑my.cnf然后上传上去的，所以我这边执行删除之前旧的my.cnf，再上传，如果你使用默认的就忽略就好

cd /etc
rm my.cnf
rz 上传本地编辑的文件
chmod 644 /etc/my.cnf

我的SSD盘是/cache1盘，my.inf配置了对应数据盘

mkdir -p /cache1/mysql_data
chmod 750 /cache1/mysql_data

开启mysql并自启动，没报错就是启动成功，这种启动方式能自动重启， kill -9可以测试

systemctl start mysqld
systemctl enable mysqld

改密码，先看机器上初始密码，我的是qMP-Vqed_6ec，我的密码是没有特殊符合不符合安全规则，我是先随便设置了个符合的然后SET GLOBAL validate_password.special_char_count = 0;再改回来

sudo grep 'temporary password' /cache1/mysql_data/*.err
mysql -uroot -pqMP-Vqed_6ec
ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxx';

配置备库读取权限

# 主库执行
CREATE USER 'repl_user'@'备库' IDENTIFIED BY '密码';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'备库';
FLUSH PRIVILEGES;

备机安装mysql8

步骤基本跟上面一致，就是my.cnf配置备注部分要改
也配置下读取备库(万一主从切换)权限

# 备库执行
CREATE USER 'repl_user'@'主库' IDENTIFIED BY '密码';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'主库';
FLUSH PRIVILEGES;

备库要执行开启同步

CHANGE MASTER TO
MASTER_HOST='主库ip',
MASTER_PORT=设置的端口,
MASTER_USER='repl_user',
MASTER_PASSWORD='密码',
MASTER_AUTO_POSITION=1;

START SLAVE;

SHOW SLAVE STATUS\G;

主备状态查看

查看主备状态具体

# 看下有没erro
1. SHOW SLAVE STATUS\G;
# 有error可能具体报错
SELECT * FROM performance_schema.replication_applier_status_by_worker\G;

mysqlrouter安装

安装命令，注意看有没报错，中途记得输入y

wget https://repo.mysql.com//mysql80-community-release-el7-7.noarch.rpm
sudo rpm -ivh mysql80-community-release-el7-7.noarch.rpm
sudo yum clean all

sudo yum install mysql-router --nogpgcheck

image.png

查看版本

mysqlrouter --version

image.png

修改配置文件

vim /etc/mysqlrouter/mysqlrouter.conf

在某尾加上

# 写路由，所有写流量只走主库
[routing:write]
bind_address = 0.0.0.0:7001
mode = read-write
destinations = 主库:xxx

# 读路由，优先走从库，从库挂了才能fallback到主库，当然读也可以不走主库，另外拓展出一台读机器，因为给的资源有限制，主库要拿来读了
[routing:read]
bind_address = 0.0.0.0:7002
mode = read-only
destinations = 从库:xxx，主库:xxx

启动

sudo systemctl start mysqlrouter
sudo systemctl enable mysqlrouter

查看效果

sudo netstat -lntp | grep mysqlrouter

image.png

keepalived安装

考虑到mysqlrouter单点故障，这边使用keepalived做高可用
安装，中间记得输入y，注意看有没报错

yum install keepalived

image.png

看下网卡，我的是输出eth2，如果主备keepalived机器网卡不同则virtual_ipaddress配置需不同

ip link show | grep 'state UP' | awk -F': ' '{print $2}'

修改配置文件

vim /etc/keepalived/keepalived.conf

配置文件数据，mysqlrouter有两台，主的设置如下

global_defs {
   router_id MYSQL_ROUTER_MASTER
}

vrrp_script check_mysqlrouter {
    script "killall -0 mysqlrouter"
    interval 2
    weight 2
}

vrrp_instance VI_1 {
    state MASTER
    interface eth2
    virtual_router_id 211
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 你的密码
    }
    track_script {
        check_mysqlrouter
    }
    virtual_ipaddress {
        你的vip地址(我这边是找公司申请的)
    }
}

配置文件数据，mysqlrouter有两台，备的设置如下，我的网卡是一致的，如果你的网卡不一致要特殊配置

global_defs {
   router_id MYSQL_ROUTER_SLAVE
}

vrrp_script check_mysqlrouter {
    script "killall -0 mysqlrouter"
    interval 2
    weight 2
}

vrrp_instance VI_1 {
    state SLAVE
    interface eth2
    virtual_router_id 211
    priority 99
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass xxx须跟主保持一致
    }
    track_script {
        check_mysqlrouter
    }
    virtual_ipaddress {
        你的vip需要跟主保持一致(我这边是找公司申请的vip)
    }
}

启动

# 启动服务（所有节点）
systemctl start keepalived

# 设置开机自启
systemctl enable keepalived

# 检查服务状态
systemctl status keepalived

image.png

验证vip功能

# 在主节点停止服务
systemctl stop keepalived

# 在备节点检查VIP（应自动接管），eth2 网要根据你自己的来
watch -n1 "ip addr show eth2 | grep 'inet '"

# 恢复主节点
systemctl start keepalived

# 观察VIP回切（根据preempt配置）
tail -f /var/log/keepalived.log

验证绑定, 注意eth2可能要改:
ip addr show eth2 | grep 'inet '

proxy安装

由于mysqlrouter已经符合要求，并且mysqlrouter更加轻量化，故不考虑使用proxy

MHA安装

目前没考虑自动主备切换，后续有需要再安装
注意MHA 不会自动回切或恢复旧主
MySQL InnoDB Cluster中MySQL Group Replication支持旧主重新假如自动修复，带需依赖旧主恢复时间和新主保留二进制数据的时间
大型mysql集群可以用

MySQL InnoDB Cluster
PXC多主，但是强一致性数据要求，跨集群有点蛋疼
分布式中间件（如 ProxySQL + 分片）
MHA + Keepalived

Granafa监控

做好监控查看各个指标

主从同步处理

主从error处理

处理之前需要主从机器上面的router都把备库踢掉

pt-table-sync安装


// centos7.6，建议主备两台机器都装上，防止备升主，主降备

sudo yum install [https://repo.percona.com/yum/percona-release-latest.noarch.rpm](https://repo.percona.com/yum/percona-release-latest.noarch.rpm) 

sudo percona-release enable tools release

sudo yum install percona-toolkit

pt-table-sync --version

手动处理

看下error的地方，大部分的错误是从库不小心写入数据


// 看下error

SHOW SLAVE STATUS\G;

// 看具体error

SELECT * FROM performance_schema.replication_applier_status_by_worker\G;

跳过某个异常, 可能有多个, 跳过后注意看异常的表，然后执行查看具体哪张表问题，再处理，有可能下面要循环多次以解决异常


STOP SLAVE;

// 这个gtid是你从 performance_schema.replication_applier_status_by_worker看到具体的错误来的
SET GTID_NEXT = '14c92d46-3541-11f0-a4f6-90e2ba00f37c:1222';
BEGIN;
COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE;
SHOW SLAVE STATUS\G;

自动处理

使用pt-table-sync自动查看，注意有时候数据量太多会卡住，可能某张表太大卡住，这时候就要手动接入处理掉卡住的大表


// --table不配置的话就是整个库查看异同

pt-table-sync h=主库host,P=port,u=xxx,p=xxxx \
h=从库host,P=port,u=xxx,p=xxxx \
--database xxx--table xx,xxx \
--print --no-check-slave --no-transaction > repair.sql

人工看下repair.sql数据确认下
自动修复


// 这里我配置了不可手动写入

STOP SLAVE;

SET GLOBAL super_read_only = OFF;

// 执行修复

mysql -hxxx -Pxxx -uxx -pxxxx 库名< repair.sql

SET GLOBAL super_read_only = ON;

// 确定要跳过的gtid, 看Last_SQL_Error

SHOW SLAVE STATUS\G;

SET GTID_NEXT='xxx:123';  
BEGIN;
COMMIT;
SET GTID_NEXT='AUTOMATIC';

START SLAVE;
SHOW SLAVE STATUS\G;

主挂了从升主处理


1\. 将从库提升为新主库
- vim /etc/my.cnf
将read_only=1 super_read_only=1反向设置都为0

2\. 确认主库故障
- SHOW SLAVE STATUS\G;
- 确保 Slave_IO_Running 和 Slave_SQL_Running 均为 Yes，且 Seconds_Behind_Master 为 0（无延迟）
3\. 停止从库复制进程
在从库执行以下命令，停止复制并解除与原主库的关联
STOP SLAVE;
RESET SLAVE ALL;  -- 清除复制信息
4\. 主备的/etc/mysqlrouter/mysqlrouter.conf，router写库改掉
systemctl restart mysqlrouter

主挂了从升主之后，原主恢复变从

当原主库恢复后，MHA 默认不会自动将其加入集群，需要人工干预或配置脚本自动处理。备份新主库数据（确保安全）。在原主库上重建数据
如果差别不大，比如binlog保存三天，只挂了一天，那就走上面提到的自动处理流程
如果差异比较大执行下面步骤，注意主库数据可以定时备份


1\. 停止并重置从库
STOP SLAVE;
RESET SLAVE ALL; 

2\. 备份主库
# 主库执行（GTID 模式专用）
mysqldump -h xxx -P xx-u xxx -pxxxx \
--single-transaction --set-gtid-purged=ON \
--databases xxx> xxx_full_backup.sql

2.1 传输, 公司传输需要通过跳板机
# 跳板机执行拉
scp -P xx [root@x](mailto:root@xxx)xx:/root/xxx_full_backup.sql /home/xxxx
# 跳板机执行推
scp -P xx  xx_full_backup.sql [root@x](mailto:root@xxx)xxx:/root
# 避免重复
rm xxxx_full_backup.sql 

3\. 清空从库 xxx库, 执行前主从都要将router read去掉从库
-- 从库执行
SET GLOBAL super_read_only = OFF;
DROP DATABASE IF EXISTS xxx;
SET GLOBAL super_read_only = ON;

4\. 导入到从库
# 移除 SET GTID_PURGED 语句
sed '/GTID_PURGED/d' XXX_full_backup.sql > clean_backup.sql

可以开两个窗口
SET GLOBAL super_read_only = OFF;
mysql -uxxx -pxxxx < clean_backup.sql
SET GLOBAL super_read_only = ON;
5\. 重新链接
-- 从库执行
grep 'GTID_PURGED' xxx_full_backup.sql
STOP SLAVE;
RESET MASTER;
SET GLOBAL GTID_PURGED = 'ea6a933c-5647-11f0-afa0-e4434bb74caa:1-1485';
CHANGE MASTER TO
  MASTER_HOST = 'xxx',
  MASTER_PORT = xxx,
  MASTER_USER = 'xxx',
  MASTER_PASSWORD = 'xxxxxxx',
  MASTER_AUTO_POSITION = 1;
START SLAVE;
SHOW SLAVE STATUS\G;

my.cnf配置

贴出 centos7.6机器，256G内存，64C，3.5T SSD对应的配置，主库，从库根据注释改改就好, 线上机器是join 临时表量很大超级多，所以按照下面配置了

[mysqld]
# 只同步 mydb 这个数据库
replicate-wild-do-table=xxx.%

# 不同步xxx.test
#replicate-ignore-table=xxx.test
# 兼容旧版本
default_authentication_plugin=mysql_native_password 
max_connections=3500
tmp_table_size=17179869184
max_heap_table_size=17179869184
table_open_cache=8192
innodb_buffer_pool_size=171798691840
innodb_io_capacity_max=20000
innodb_io_capacity=10000
binlog_cache_size=33554432
#expire_logs_days=1 已废弃
binlog_expire_logs_seconds=86400
long_query_time=30
innodb_flush_neighbors=0
lower_case_table_names=0
binlog_ignore_db=scheduler_task

performance_schema_instrument=wait/lock/metadata/sql/mdl=ON
default_authentication_plugin=mysql_native_password
#query_cache_size=0 已废弃
#query_cache_type=0 已废弃
relay_log_info_repository=TABLE
master_info_repository=TABLE
explicit_defaults_for_timestamp=1
key_buffer_size=4G
server_id=12502990
report_port=ss
report_host=你的ip
port=ss
datadir=/cache1/mysql_data
socket=/cache1/mysql_data/mysql.sock
log_error=/cache1/mysql_data/mysql_error.err
pid_file=/cache1/mysql_data/mysqld.pid
slow_query_log=1
slow_query_log_file=slow_query.log
#log_bin=mysql-bin
relay_log=mysql-relay-bin
relay_log_space_limit=10737418240
general_log=0
general_log_file=mysql-general.log
binlog_format=ROW
sync_binlog=0
log_slave_updates=1
# 主库两个都设置0
read_only=0
super_read_only=0

event_scheduler=0
transaction_isolation=REPEATABLE-READ
max_allowed_packet=64M
sort_buffer_size=32M
read_buffer_size=64M
read_rnd_buffer_size=32M
join_buffer_size=32M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
group_concat_max_len=64K
innodb_stats_on_metadata=0
innodb_file_per_table=1
thread_cache_size=1024
skip_external_locking
skip_name_resolve
innodb_log_file_size=256M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=16M
character_set_server=utf8
log_bin_trust_function_creators=1
open_files_limit=60000
secure_file_priv=''
slave_net_timeout=30
binlog_error_action=IGNORE_ERROR
#show_compatibility_56=on
#log_warnings=1
optimizer_switch='semijoin=OFF'
gtid_mode=ON
enforce_gtid_consistency=ON
mysqlx=0

[mysql]
no_auto_rehash
prompt="\u \d>"
[mysqldump]
quick

[client]
socket=/cache1/mysql_data/mysql.sock

主从同步业务优化处理

before

mysql5.7有三张表 A B C, 其中B的表变化非常频繁。之前主从正常，现在从库断了，一天，之前操作如下：

从主库dumpB表，然后记录binlog 水位
从库清理B 表然后重新执行dump的B表sql
主从同步设置从B表的binlog点位开始处理
pt-sync修复其他表的不同步数据
整体流程如上，有个问题假如C表也是变化很频繁，用pt-sync多修复几轮也修复不过来，那整个流程要怎么处理合适？

after

全量物理备份 + 精准binlog同步
pt-sync是逻辑行级修复工具，对高频写入的表（如C表）效率极低：修复期间新变更持续产生，形成“修复速度 < 变更速度”的死循环。
仅备份B表会导致其他表（A/C）与binlog起点不一致：从库从新binlog位点开始同步时，A/C表会重复应用历史binlog（因从库旧数据未清理），引发数据错乱
只备份需要主从的表
具体步骤

主库执行全量物理备份（推荐XtraBackup，设置备份主从同步需要的表），优势如下
1.1 备份期间不锁表（InnoDB热备），业务无感知。
1.2 自动生成xtrabackup_binlog_info文件，精确记录备份结束时的binlog位点（如mysql-bin.000003, 107）。
1.3 备份集包含所有表（A/B/C）的一致性状态
从库清理旧数据，恢复物理备份
配置主从同步，从备份记录的binlog位点开始
监控同步状态，确保追平，SHOW SLAVE STATUS；

为什么不推荐逻辑备份（mysqldump）？

锁表风险：即使--single-transaction，MyISAM表或DDL操作仍可能阻塞。
恢复速度慢：SQL逻辑导入比物理文件拷贝慢5-10倍，延长从库不可用时间。
大表瓶颈：100GB+的B/C表用mysqldump可能需数小时，期间主库binlog持续增长，追平更困难。

如果后续新增数据量小 + 更新频率低的 D 表，直接用 pt-sync 修复是最简单安全的方案。mysql 5.7以及以后都可以新增配置后reload

canal 增量+全量处理

整体架构设计，比如写入es，使用XtraBackup全量存下来，然后记录binlog点位

整体.png
Canal 客户端启动指令（精确控制位点），使用 Canal Admin 控制台（生产推荐）, 这样可以做到每个客户端都不同点位处理，开启增量同步
json

{
  "journalName": "mysql-bin.000003",  // SHOW MASTER STATUS 的 File
  "position": 12345,                  // SHOW MASTER STATUS 的 Position
  "timestamp": 1696161000000,         // 毫秒时间戳（可选）
  "gtid": ""                          // 如使用 GTID 模式
}

mysql高可用架构

mysql高可用架构

目录

背景

mysql8安装

备机安装mysql8

主备状态查看

mysqlrouter安装

image.png

keepalived安装

验证vip功能

proxy安装

MHA安装

Granafa监控

主从同步处理

主从error处理

pt-table-sync安装

手动处理

自动处理

主挂了从升主处理

主挂了从升主之后，原主恢复变从

my.cnf配置

主从同步业务优化处理

before

after

canal 增量+全量处理

相关阅读更多精彩内容

友情链接更多精彩内容