MySQL主从复制与集群
主要是掌握主从复制的原理和实现过程，搭建MySQL主从集群
MySQL单节点工作的弊端：存在单点故障，导致整个系统崩溃的问题，无法查询等
扩展到MySQL集群的好处：需要将同一份数据放在两个DB server上，主从复制保证数据的一致性，基于binlog
可以在读写分离/主从复制架构中加入proxy代理，对外只有一个IP/域名，内部的机制
1.消除单点故障
2.提高读写性能，提高性能瓶颈
3.使用读写分离，写(更新机)和读(查询机)分开部署，提高查询库的用途--->开源软件可以用mycat来代替proxy
MySQL主从复制原理：异步复制
主从复制的方式：异步复制，主从节点之间可能会有数据的丢失，因为采用异步复制不保证同步的一定的实时性
异步复制在复制同步大量的数据时：比如执行存储过程，可能是百万千万条记录一起插入到库中，会导致同步时延非常大，可能到1min往上了
[hellodb]>show slave status \G;
Seconds_Behind_Master: 211.开启binlog
master主节点开启二进制功能binlog，开启两个binlog开关，最好是主从节点都开启
新数据更新写操作，先写事务日志redo_log，在写binlog
show variables like "%log_bin%";
show variables like "%sql_log_bin%";
set global sql_log_bin=1;
vim /etc/my.cnf
log_bin=/data/mysql/binlog
systemctl restart mysqld
flush logs; --更新日志文件，更新Binlog2.开启dump同步线程
服务线程---IO线程---sql thread
同步机制：异步复制，下发写操作，更新，返回客户端(无论是本地socket也好，还是远程工具也好)，最好是用异步复制，效率比较高；数据库一般都是并发读写操作的
实时同步：同步数据到从节点成功后，才返回写操作OK
实时同步binlog到从节点，同步binlog的pos，保证位置的一致性
binlog同步完成后写入到中继日志relay_log，通过sql thread写入到从库中，完成数据更新，其实relay_log就是主节点binlog同步后的存放位置，是主节点的数据更新
--创建一个用户用于同步，允许访问所有库
--指定从哪个pos开始查看并导出binlog日志
mysqlbinlog --start-position=524 /data/mysql/binlog/rocky-bin.000003 > /backup/binlog.sql各种复制架构
1.首选主--->从，比较均衡
2.1主2从，增加冗余性，1主从2从，增加冗余性，但是复制存在延迟
3.两个主，互为主备节点，存在双写数据冲突的问题，可以配置读写分离代理实现，master1+2作为读写节点，一个坏了另一个马上升级为主节点，提供服务
在proxy上指定一下读写操作指向节点的IP就行
搭建主从架构的问题：最好是数据库版本一致，从节点最好是使用高版本，因为不同版本之间存在binlog日志格式不一致的问题，高版本可以兼容低版本的
slave：高版本，5.7-8.0，最好是重装，然后导入完整备份就行
master：低版本，5.7-8.0
搭建MySQL主从同步环境
环境：主master 10.0.0.132，基本步骤，注意：主从复制关系在机器服务重启后仍然保持同步状态；从节点关机后，在开机后马上可以同步过来，如果数据量大存在一定的延迟
从slave：10.0.0.128
version：mysql 8.0.30
1.主节点
1） 二进制日志
2） server_id=
3) 创建用户，replication slave 权限
4） 全备份 --master-data=1，--master-data=2，binlog的pos加上注释
2.从节点
1) server_id=
2) source /data/full.sql 导入备份
3) CHANGE MASTER TO 修改主节点信息
MASTER_HOST='10.0.0.8',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=30489887,
MASTER_CONNECT_RETRY=5;
4) start slave;
stop slave;
reset slave all; ##重置从节点
master status：执行DML语句后的pos开始复制
start slave;
3.主主架构1.开启binlog
如果是8.0及以下得版本，则需要手动开启binlog
##8.0默认开启binlog
show variables like "%log_bin%";
show variables like "%sql_log_bin%";
set global sql_log_bin=1;
vim /etc/my.cnf
log_bin=/data/mysql/binlog/matser-bin --->指定Binlog日志文件的前缀
systemctl restart mysqld
flush logs; --更新日志文件，更新Binlog2.配置主从节点基本信息
##主节点，修改hostname
hostnamectl set-hostname master
##修改mysqld配置文件中的server-id，可以配置为1，每个节点的server-id一定要不一样，不然无法区分
可以配置为IP地址最后一位
vim /etc/my.cnf
[mysqld]
server-id=132
##查看主节点的二进制文件信息，不用指定任何库，直接复制所有新增写入的数据，从这个binlog的pos开始复制，--start-position=157
[(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| rocky-bin.000019 | 157 | | | |
+------------------+----------+--------------+------------------+-------------------+3.主节点创建同步账号，授权
revoke回收权限
##查看catyer这个用户的权限
show grants for 'catyer'@'10.0.0.%';
+-----------------------------------------------------------+
| Grants for catyer@10.0.0.% |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `catyer`@`10.0.0.%` |
| GRANT ALL PRIVILEGES ON `zabbix`.* TO `catyer`@`10.0.0.%` |
+-----------------------------------------------------------+
##回收删除权限
revoke delete on hellodb.* from catyer@'10.0.0.%';
flush privileges;
##回收所有权限
revoke all on hellodb.* from catyer@'10.0.0.%';
flush privileges;各版本之间的差异，一定要执行grant，不然会出现无法生成master节点的dump线程
##早期版本5.7左右，授权+创建账号
grant replication slave on *.* to sync@'%‘ identified by '123';
##MySQL 8.0版本
create user xxx@'%' identified by '123';
show grants for sync@'%';
##回收权限，刷新权限，赋予复制权限
revoke all on *.* from sync@'%';
grant replication slave on *.* to sync@'%';
flush privileges;
[(none)]>show grants for sync@'%';
+----------------------------------------------+
| Grants for sync@% |
+----------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `sync`@`%` |
+----------------------------------------------+
1 row in set (0.01 sec)
这一条是failed，证明sync用户没有replication的权限
4.主节点做一份全备，导入到备节点，确保初始数据一致
可以考虑将所有库都备份导出一次，形成不同的.sql文件，然后使用存储过程批量导入.sql数据，写成脚本或者是MySQL的存储过程--->可以查一下，直接或者写成事务，批量化执行，最好是脚本
因为从库可能是新库，初始数据不一致，如果直接同步的话那就只有新数据，没有老数据了
单库备份
#!/bin/bash
TIME=`date +%Y-%m-%d_%H-%M-%S`
DIR=/data/backup
DB="hellodb"
PASS=123
HOST=10.0.0.128
##-B表示选择
[ -d $DIR ] && echo "dir exist" || mkdir -p $DIR
mysqldump -uroot -p$PASS --single-transaction --source-data=2 -B $DB -F | gzip > ${DIR}/${DB}.${TIME}.sql.gz
#mysqldump -usync -p$PASS -h$HOST --single-transaction --source-data=2 -A | gzip > /data/backup/backup.sql.gz
scp ${DIR}/${DB}.${TIME}.sql.gz $HOST:/data/backup多库备份，除去系统库+expect ssh交互
#!/bin/bash
TIME=`date +%F_%T`
DIR=/data/backup
DB="hellodb"
PASS=123
##批量化复制主机
HOST=(10.0.0.128
10.0.0.129)
[ -d $DIR ] && echo "dir exist" || mkdir -p $DIR
mysqldump -uroot -p$PASS --single-transaction --source-data=2 -A -F > ${DIR}/${DB}_${TIME}_all.sql
#mysqldump -usync -p$PASS -h$HOST --single-transaction --source-data=2 -A | gzip > /data/backup/backup.sql.gz
for IP in ${HOST[*]};do
#免交互复制ssh-keygen到目的主机
expect <<EOF
spawn ssh-copy-id root@$IP
expect {
"yes/no" {send "yes\n";exp_continue}
"password" {send "123\n"}
}
expect eof
EOF
scp ${DIR}/${DB}_${TIME}_all.sql $IP:/data/backup
echo "$IP copy success"
done完全备份-A，直接source就行，不用选库
##-A完全备份，-F刷新日志
##!/bin/bash
TIME=`date +%Y-%m-%d_%H-%M-%S`
DIR=/data/backup
DB="hellodb"
PASS=123
HOST=10.0.0.128
[ -d $DIR ] && echo "dir exist" || mkdir -p $DIR
mysqldump -uroot -p$PASS --single-transaction --source-data=2 -A -F > ${DIR}/${DB}_${TIME}_all.sql
#mysqldump -usync -p$PASS -h$HOST --single-transaction --source-data=2 -A | gzip > /data/backup/backup.sql.gz
scp ${DIR}/${DB}_${TIME}_all.sql $HOST:/data/backup
[zabbix]>source /data/backup/hellodb_2022-09-11_09-15-11_all.sql主节点看一下dump线程，在运行的线程
show processlist;
[(none)]>show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 7920 | Waiting on empty queue | NULL |
| 52 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+单表备份：直接备份一个库内的一个表
也可以备份多张表
##假如不小心清空从库表的数据
truncate table stu;
[hellodb]>select * from stu;
Empty set (0.00 sec)
##导出单表，还原
mysqldump -uroot -p123 hellodb stu > /data/stu.sql
scp /data/stu.sql 10.0.0.128:/data/
source /data/stu.sql
[hellodb]>select * from stu where stuid=25;
+-------+-------------+-----+--------+
| StuID | Name | Age | Gender |
+-------+-------------+-----+--------+
| 25 | Sun Dasheng | 100 | M |
+-------+-------------+-----+--------+
##备份多张表
mysqldump -uroot -p123 hellodb stu teachers job > /data/stu.sql5.从节点配置文件以及导入备份，基于线程来复制
可以先导入文件，再show master status记录下binlog的pos和名字，再执行change master
vim /etc/my.cnf
read-only ##只读，防止普通用户修改数据，不能防止root
CHANGE MASTER TO
MASTER_HOST='10.0.0.132',
MASTER_USER='sync',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=30489887,
MASTER_CONNECT_RETRY=5;
##取消掉注释，复制过去，从节点加上binlog选项
CHANGE MASTER TO
MASTER_HOST='10.0.0.132',
MASTER_USER='sync',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='rocky-bin.000783', MASTER_LOG_POS=197;
关闭二进制，再导入，查看从节点的信息，发生生成了中继日志，即master的binlog写过来的日志存放文件relay_log，SLAVE_IO还是not running，开启复制线程
set sql_log_bin=0;
source /data/backup/hellodb_2022-09-11_10-48-26_all.sql
show slave status\G;
[zabbix]>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.132
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: rocky-bin.000021
Read_Master_Log_Pos: 157
Relay_Log_File: master-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: rocky-bin.000021
Slave_IO_Running: No
Slave_SQL_Running: No
start slave;
show processlist;
IO线程+SQL线程，IO线程是从主节点接收同步信息，通过SQL thread写入到从库的数据中，Query thread，显示waiting for updates
主节点：binlog dump线程
6.测试主从环境
主写新，看从有无，可以执行存储过程
测试执行存储过程，从节点直接SQL thread断掉了
[hellodb]>select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | catyer | 16 | M |
| 6 | catyer2 | 17 | M |
+-----+---------------+-----+--------+
##主节点写入新数据，删除数据，没了
[hellodb]>delete from teachers where tid=6;
Query OK, 1 row affected (0.00 sec)
[hellodb]>select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | catyer | 16 | M |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
##测试执行存储过程
insert into teachers(name,age,gender) values('a',22,'M');
select * from teachers;出现Slave_IO_Running: NO的解决办法：可能是用户没权限
##复制账号没权限，从节点查看
[(none)]>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.132
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: rocky-bin.000022
Read_Master_Log_Pos: 60979553
Relay_Log_File: master-relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: rocky-bin.000022
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
##授权
grant replication slave on *.* to sync@'%';
flush privileges;
[(none)]>show grants for sync@'%';
+----------------------------------------------+
| Grants for sync@% |
+----------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `sync`@`%` |
+----------------------------------------------+
1 row in set (0.01 sec)出现Slave_SQL_Running：No的解决办法
重新执行一下slave的change master语句，记录下master的binlog pos，修改执行
可以查看日志，错误日志：mysql-error.log，可以自定义一下错误日志的位置
##从停止slave
stop slave;
##主查看binlog日志的pos
show master status;
[hellodb]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| rocky-bin.000022 | 60979553 | | | |
+------------------+----------+--------------+------------------+-------------------+
##从手动修改Pos,执行
CHANGE MASTER TO
MASTER_HOST='10.0.0.132',
MASTER_USER='sync',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='rocky-bin.000784', MASTER_LOG_POS=197;
##启动slave
start slave
##可能是表结构不一致，需要修改表结构字段
2022-09-11T03:39:17.143632Z 28 [Warning] [MY-010584] [Repl] Slave SQL for channel '': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: MY-001756
desc testlog;
show create table testlog;执行一个存储过程后SQL_running=NO的解决，通过show master status的报错查看
主要是看errorlog来拍错，让我们看一下error log或者是performance_schema.replication_applier_status_by_worker，这个表来查看有什么原因导致的
在同步前，新的数据要是master节点存在，slave节点不存在的，就算是空表也不行
##先看错误现象，SQL_RUNNING为NO，让看看错误日志
[hellodb]>show slave status \G;
Seconds_Behind_Master: 21
Slave_SQL_Running: No
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log rocky-bin.000023, end_log_pos 444.
See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
##查看错误日志，好像是表的什么问题，再看下一个表
tail -f mysql.log
2022-09-11T03:34:59.334704Z 23 [ERROR] [MY-013146] [Repl] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log rocky-bin.000022, end_log_pos 60979827; Column 1 of table 'hellodb.testlog' cannot be converted from type 'char(30(bytes))' to type 'char(40(bytes) utf8mb4)', Error_code: MY-013146
##查出原因了，这个testlog表已经存在了，不能往里面写数据，在同步前，新的数据
[hellodb]>select * from performance_schema.replication_applier_status_by_worker\G;
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at master log rocky-bin.000023, end_log_pos 444;
Error 'Table 'testlog' already exists' on query. Default database: 'hellodb'. Query: 'create table testlog (id int auto_increment primary key,name char(30),salary int default 20) character set utf8mb4'
##删除掉表，停止slave，启动slave，修改MASTER信息
drop table testlog;
stop slave
##从手动修改Pos,执行
CHANGE MASTER TO
MASTER_HOST='10.0.0.132',
MASTER_USER='sync',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='rocky-bin.000024', MASTER_LOG_POS=157;
start slave
##再次执行存储过程，在源端加点东西，先创建表
use hellodb;
create table testlog (id int auto_increment primary key,name char(30),salary int default 20) character set utf8mb4;
delimiter $$
create procedure sp_testlog()
begin
declare i int;
set i = 1;
while i <= 100000
do insert into testlog(name,salary) values (concat('wang',FLOOR(RAND() * 100000)),FLOOR(RAND() * 1000000));
set i = i +1;
end while;
end$$
delimiter ;
source /root/testlog.sql
call sp_testlog();
##从节点有21s的复制同步延迟，已经优化很多了
show slave status \G;
[hellodb]>select count(*) from testlog;
+----------+
| count(*) |
+----------+
| 36626 |
+----------+
Seconds_Behind_Master: 21
##完成执行存储过程的同步
[hellodb]>select count(*) from testlog;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
show processlist;
49 | sync | 10.0.0.128:60752 | NULL | Binlog Dump | 1475 | Source has sent all binlog to replica; waiting for more updates | NULL
wait for events
ss -nt：查看已经建立的TCP连接，ESTABLISHED，10.0.0.128:业务端口已建立
7.同步堵塞(blocked)，序号冲突，跳过错误
跳过指定事件，跳过错误数量
show variables like '%sql_slave_skip_counter%';
##跳过N个错误
set global sql_slave_skip_counter=N有时候，从节点没有设置为read-only只读，然后在从节点新增了记录，比如id=9，而主节点又新增记录，序号也为9，那么这个9和从节点的9就不是同一个问题，导致冲突，造成复制堵塞(blocked)，entity的记录冲突了
show slave status \G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
##从节点写入一条记录，8号记录
insert into teachers(name,age,gender) values('tea',11,'M');
[hellodb]>select * from teachers where tid=8;
+-----+------+-----+--------+
| TID | Name | Age | Gender |
+-----+------+-----+--------+
| 8 | tea | 11 | M |
+-----+------+-----+--------+
##主节点插入一条记录
insert into teachers(name,age,gender) values('teach',20,'M');
##两边的tid-8的记录不一样了，查看一下错误
[hellodb]>select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | catyer | 16 | M |
| 7 | teach | 20 | M |
| 8 | teach | 22 | M |
+-----+---------------+-----+--------+
7 rows in set (0.00 sec)
[hellodb]>select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | catyer | 16 | M |
| 7 | uuu | 11 | M |
| 8 | tea | 11 | M |
+-----+---------------+-----+--------+
7 rows in set (0.00 sec)
##查出是entity 7序号7直接堵塞掉了，第七条出问题，导致后面的记录都无法复制过去了
##其实是7号和8号都冲突了，需要跳过两个错误
show slave status \G;
[hellodb]>select * from performance_schema.replication_applier_status_by_worker\G;
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at master log rocky-bin.000024, end_log_pos 30689864; Could not execute Write_rows event on table hellodb.teachers;
Duplicate entry '7' for key 'teachers.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log rocky-bin.000024, end_log_pos 30689864临时解决方案：跳过这个错误
stop slave;
##跳过2个错误
set global sql_slave_skip_counter=2;
select @@sql_slave_skip_counter;
start slave;
show slave status \G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
##主节点插入，有了
[hellodb]>insert into teachers(name,age,gender) values('teach9',20,'M');
[hellodb]>select * from teachers where tid=9;
+-----+--------+-----+--------+
| TID | Name | Age | Gender |
+-----+--------+-----+--------+
| 9 | teach9 | 20 | M |
+-----+--------+-----+--------+
##错误解决办法，手动改，多行记录的话，手动改多行记录就行
update xxx set where双主架构实现：互为主节点
将主节点也执行change master，使其变为从节点的从节点，容易造成数据冲突，不建议使用，双向同步，假如说用户无意间写到两个不同的主，而不是一份数据双写
最好还是写一份数据，安全，不会冲突
环境：主master 10.0.0.132，从slave 10.0.0.128，现在132也是128的从
1.从节点上查看MySQL的binlog，记录binlog日志pos
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| slave-bin.000004 | 157 | | | |
+------------------+----------+--------------+------------------+-------------------+
2.修改change master
CHANGE MASTER TO
MASTER_HOST='10.0.0.128',
MASTER_USER='sync',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='slave-bin.000004', MASTER_LOG_POS=157;
##启动slave
start slave
##重置
reset slave all半同步机制(重点)：同步到一个从或者超过延迟时间就返回成功，加快事务的返回--->用于主从(主备架构)
异步复制的优点：返回结果快，写入到主库了后就返回写入成功了，SQL query set
异步复制的缺陷：如果在同步的过程中，master dump thread挂了，则无法同步，从节点的数据也不是最新的；而且在slave库还需要完成SQL thread写入到磁盘，这段如果没完成也是导致数据不是最新的情况
半同步机制：加入了检查从库是否数据落盘的插件，只有主库---从库，从库的事务commit后，写入磁盘了，主库这边才返回写入成功；或者是超过了同步延时时间，也会返回写成功
基本过程（增强型半同步复制）：优点，先检查是否同步，解决了主库返回成功后，访问从库（可能是只读库）没有数据，没同步到位的问题
1.用户提交事务（DML），执行SQL语句；客户端可能是本地socket，也可能是远端用户
2.写二进制日志
3.先等slave dump完成复制到slave
4.提交事务，写入磁盘
5.返回写成功
模拟环境搭建一主两从：rpl_semi_sync_master_enabled
主：132 ，从1：128，从2：131
插件：rpl_semi_sync_master_enabled，show plugins
1.同步主从数据，开启slave
##Master节点操作配置
show variables like '%rpl_semi_sync_master_enabled%';
vim /etc/my.cnf
rpl_semi_sync_master_enabled=on
rpl_semi_sync_master_timeout=
##查看系统自带的插件，安装插件
1.binlog、密码插件认证、守护进程，存储引擎等
[(none)]>show plugins;
+---------------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
2.安装半同步插件rpl_semi_sync_master ##主从节点都要安装
install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected, 1 warning (0.07 sec)
show plugins;
rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL
3.修改配置文件
vim /etc/my.cnf
rpl_semi_sync_master_enabled=on ##开启半同步功能
rpl_semi_sync_master_timeout=10000 ##默认超时时间：10s，以毫秒为单位，默认10s
[(none)]>select @@rpl_semi_sync_master_timeout;
+--------------------------------+
| @@rpl_semi_sync_master_timeout |
+--------------------------------+
| 10000 |
+--------------------------------+
systemctl restart mysqld
4.查看插件状态
[(none)]>select @@rpl_semi_sync_master_enabled;
+--------------------------------+
| @@rpl_semi_sync_master_enabled |
+--------------------------------+
| 1 |
+--------------------------------+
5.完全备份到从库，确认log-bin的pos，##master和slave1都执行，scp到从库
grep -i "change master" hellodb_2022-09-12_11:45:11_all.sql
show master status;
-- CHANGE MASTER TO MASTER_LOG_FILE='rocky-bin.000030', MASTER_LOG_POS=157;
stop slave;
source /data/backup/hellodb_2022-09-12_11:45:11_all.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.132',
MASTER_USER='sync',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='rocky-bin.000030', MASTER_LOG_POS=157;
start slave;
show slave status \G;
##确认sql_io和slave_io都启动
##如果遇到以下问题，slave同步问题
stop slave;
reset slave all;
start slave;2.从节点安装插件
完成两边的半同步插件安装，现在没有配置timeout，都是10s
##安装插件和卸载插件，主节点master
install plugin rpl_semi_sync_master soname 'semisync_master.so';
uninstall plugin rpl_semi_sync_master;
##从节点安装slave
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
select @@rpl_semi_sync_master_enabled;
+--------------------------------+
| @@rpl_semi_sync_master_enabled |
+--------------------------------+
| 0 |
+--------------------------------+
1.动态设置变量
set global rpl_semi_sync_master_enabled=ON;
set global rpl_semi_sync_slave_enabled=ON;
show plugins;
[(none)]>select @@rpl_semi_sync_master_enabled;
+--------------------------------+
| @@rpl_semi_sync_master_enabled |
+--------------------------------+
| 1 |
+--------------------------------+
2.主写入配置文件，从写入配置文件
vim /etc/my.cnf
[mysqld]
rpl_semi_sync_master_enabled
systemctl restart mysqld
#从写入配置文件
[mysqld]
rpl_semi_sync_slave_enabled
systemctl restart mysqld
##主节点测试查看,clients=2才是正确的
[hellodb]>show status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 2 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)3.测试10s的延迟，如果过了10s没有slave响应，则返回成功
可以修改全局变量超时时间，单位是ms毫秒
停掉两个slave的同步看看
stop slave;
[hellodb]>select @@rpl_semi_sync_master_timeout;
+--------------------------------+
| @@rpl_semi_sync_master_timeout |
+--------------------------------+
| 10000 |
+--------------------------------+
##修改全局变量
set global rpl_semi_sync_master_timeout=2000;
Query OK, 0 rows affected (0.00 sec)
##无slave响应，需要10s返回成功，timeout值可以设置
[hellodb]>insert into teachers(name,age,gender) values('a',22,'M');
Query OK, 1 row affected (10.00 sec)
##有slave响应，秒回，证明半同步复制是成功的
start slave;
[hellodb]>insert into teachers(name,age,gender) values('a',22,'M');
Query OK, 1 row affected (0.02 sec)
##MySQL默认配置，在同步后才提交
[hellodb]>show variables like 'Rpl_semi_sync_master_wait_point';
+---------------------------------+------------+
| Variable_name | Value |
+---------------------------------+------------+
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+---------------------------------+------------+复制过滤器binlog-do-db
指定哪些库生成二进制文件日志
vim /etc/my.cnf
binlog_do_db=db1 ##白名单
binlog_ignore_db=db2 ##黑名单
实现事务的并发自动复制，自动找binlog_pos：GTID(MySQL 5.7及以上)
好处：可以在主从复制关系中，不需要管理员或者是用户去关注binlog的pos，开启gtid，自动寻找事务的binlog点，自动复制新东西过来
支持多DUMP线程自动复制，大大降低延迟
事务的唯一性：GTID=server_uuid+transaction_id，是全局唯一的
server_id来源于：/data/mysql/auto.cnf
[root@rocky mysql]#cat auto.cnf
[auto]
server-uuid=5aa76b1b-1b77-11ed-9219-000c29333d85
开启GTID，主从节点都要开启这个GTID_MODE
vim /etc/my.cnf
[mysqld]
gtid_mode=on
enforce_gtid_consistency
systemctl restart mysqld
##查看主节点的pos
[hellodb]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| rocky-bin.000032 | 157 | | | |
+------------------+----------+--------------+------------------+-------------------+
##从节点配置
stop slave;
CHANGE MASTER TO
MASTER_HOST='10.0.0.132',
MASTER_USER='sync',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
#MASTER_LOG_FILE='slave-bin.000004', MASTER_LOG_POS=157; --->这一段可以不要了
start slave;
##也可以
resrt slave all;
##已和主节点同步完成了，做复制方便很多了
[(none)]>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.132
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: rocky-bin.000032
Read_Master_Log_Pos: 157MySQL主从复制的一些监控健康性(bash实现)
使用监控软件监控的时候，需要检查一些指标，使用bash看出来就好了
##监控复制的健康，两个进程，从节点slave
mysql -uroot -p123 -e 'show slave status\G' | awk -F':' '/Slave_IO_Running/{print $2}'
mysql -uroot -p123 -e 'show slave status\G' | awk -F':' '/Slave_SQL_Running/{print $2}'
show master status; ##主节点的binlog
show binlog events; ##查询binlog日志的内容
mysqlbinlog rocky-bin.000003
show slave status \G;
show processlist;
stop slave
reset slave all
start slave
1.配置gtid，实现自动识别binlog的pos
2.减少大事务的产生，减少同步复制延迟
3.减少锁机制
4.建立主从机制造成MySQL主从数据不一致的原因
binlog格式(row级别或者是statement级别语句)、是否开启binlog、同步是否正常(show slave status)、服务是否正常，版本是否一致(最好是从节点高)等
主库的复制账号sync没有grant replication slave权限
定期监控主从之间的心跳问题，监控slave status状态
---->重新完全备份，导入，重新建立主从关系等
mycat：数据库proxy的实现
proxy服务器：用于写好逻辑，如果写请求，就到主节点；如果是读请求，就到从节点(更新机、查询机等)
垂直拆分：分库分表，关系不大的表可以拆分，如果是例如stu表和teacher表的话，那就是不太方便的；分库的话，可以和开发协商好；根据不同的业务类型进行拆分
水平拆分，放在不同的服务器中，以便解放性能瓶颈，或者读写分离等
--->通过proxy代理来区分数据需要放在什么节点上，proxy代理节点也要冗余双节点，以免出现单点故障的问题
主要功能：实现一个MySQL代理proxy的功能，通过proxy与后端多个MySQL服务器相连，实现大规模的MySQL服务器集群功能
mycat核心协议：JDBC，与多个主流数据库相连，mycat是对数据库层面的抽象
工作原理：前端（HA-proxy）负载均衡---mycat proxy代理集群（冗余节点）---转发请求到后端1-N个MySQL服务器节点（冗余节点），类似K8s集群中，我们需要两个nginx节点做转发一样，万一一个pod挂了，还有另一个nginx pod作为转发请求
mycat实现读写分离：
1.接收写操作请求：转发到后端主节点，更新机
2.接收读请求：转发到后端查询机，读节点
mycat实现读写分离
环境：客户端ubuntu 10.0.0.131
mycat节点：10.0.0.130
主节点：10.0.0.132
从节点：10.0.0.128
1.下载包，解压缩到指定目录，配置执行的环境变量，这个可用于其他的服务，例如nginx编译安装
适用于所有编译安装的服务，可以将路径写到环境变量里面去，就不用人为进入到bin目录下了，类似tomcat也一样的，nginx等很多服务
tar xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /mycat
##查看包内的内容，已经编译好的东西，可以直接使用
bin catlet conf lib logs version.txt
##修改执行文件的path路径，让他能够直接执行，不需要加上执行文件路径
原路径：/apps/mycat/bin/mycat
vim /etc/profile.d/mycat.sh，让系统执行mycat指令的时候，直接去调用环境变量的PATH
PATH=/apps/mycat/bin:$PATH
##让环境变量生效
. /etc/profile.d/mycat.sh / source /etc/profile.d/mycat.sh
[11:01:44 root@proxy bin]#mycat
Usage: /apps/mycat/bin/mycat { console | start | stop | restart | status | dump }2.安装java编译环境，修改mycat默认端口
升配一下mycat节点的内存，java进程比较吃内存
yum -y install java
##java 8版本
java -version
openjdk version "1.8.0_342"
OpenJDK Runtime Environment (build 1.8.0_342-b07)
OpenJDK 64-Bit Server VM (build 25.342-b07, mixed mode)启动mycat，监听的是8066和9066两个端口，对外暴露的，默认两个对外端口，可以修改的
对外暴露的端口默认是3306的
mycat start
ss -ntl | grep 8066
LISTEN 0 100 :::8066 :::*
##ss -ntlp：表示显示是哪个进程协议服务占用了这个端口，仅显示TCP连接，显示users，这个端口的进程
netstat -ntlp
[11:15:41 root@proxy ~]#ss -ntlp | grep 8066
LISTEN 0 100 :::8066 :::* users:(("java",pid=11766,fd=86))
[11:15:51 root@proxy ~]#ss -ntlp | grep 9066
LISTEN 0 100 :::9066 :::* users:(("java",pid=11766,fd=82))
##查看mycat的日志，/logs/wrapper.log
[11:18:26 root@proxy logs]#tail -f wrapper.log
STATUS | wrapper | 2022/09/17 11:14:07 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/09/17 11:14:07 | Launching a JVM...
INFO | jvm 1 | 2022/09/17 11:14:08 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/09/17 11:14:08 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/09/17 11:14:08 |
INFO | jvm 1 | 2022/09/17 11:14:09 | MyCAT Server startup successfully. see logs in logs/mycat.log修改mycat端口，默认这一段是注释掉的，需要复制新的，并且修改默认客户端端口号；对外暴露端口：3306，管理端口：9066
mycat默认连接用户：root，默认密码：123456，修改为123
/serverport，/user
/conf/server.xml
<!-- <property name="serverPort">8066</property> <property name="managerPort">9066</property> -->
<property name="serverPort">3306</property>
<property name="managerPort">9066</property>
<user name="root" defaultAccount="true">
<property name="password">123</property>
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>3.在后端MySQL上创建mycat连接用户
授权给到hellodb库的权限，读写权限，因为访问到proxy可以进行这个库的读写分离
[hellodb]>create user mycat@'%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
[hellodb]>grant all on hellodb.* to mycat@'%';
Query OK, 0 rows affected (0.00 sec)
[hellodb]>flush privileges;
Query OK, 0 rows affected (0.01 sec)4.在schema.xml上实现读写分离的效果
修改schema.xml文件，修改readhost和writehost，修改后短的节点
schema name：在mycat代理上显示给客户端看的DB名字
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="localhost1" database="hellodb" /> <!-- 后端DB名字 -->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host1" url="10.0.0.132:3306" user="mycat" password="123"> <!-- 后端DB1的IP -->
<readHost host="host2" url="10.0.0.128:3306" user="mycat" password="123" /> <!-- 后端DB2的IP -->
</writeHost>
</dataHost>
</mycat:schema>验证修改server和schema后的结果
##重启服务
mycat restart
##查看日志，jvm1，证明是java编译的东西
tail -f /apps/mycat/logs/wrappers.log
INFO | jvm 1 | 2022/09/17 14:15:46 | MyCAT Server startup successfully. see logs in logs/mycat.log
##查看机器的监听端口是不是修改成3306了
ss -ntlp | grep 3306
LISTEN 0 100 :::3306 :::* users:(("java",pid=50461,fd=86))
##客户端连接到mycat代理，随便找个网络通的，查询发现就是hellodb的内容，可直接进行读写操作
mysql -uroot -p123 -h 10.0.0.130
mysql> show databases;;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| stu |
| teachers |
| testlog |
| toc |
+-------------------+
8 rows in set (0.01 sec)5.验证mycat读写分离到server1和2的效果，使用general_log通用日志实现
##开启server1和server2的general_log日志
[hellodb]>select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 0 |
+---------------+
set global general_log=1;
show variables like '%general_log%';
[hellodb]>show variables like '%general_log%';
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| general_log | ON |
| general_log_file | /data/mysql/rocky.log |
+------------------+-----------------------+
[(none)]>show variables like '%general_log%';
+------------------+------------------------+
| Variable_name | Value |
+------------------+------------------------+
| general_log | ON |
| general_log_file | /data/mysql/master.log |
+------------------+------------------------+
bash上开启
tail -f /data/mysql/rocky.log
tail -f /data/mysql/master.log
由ubuntu客户端向mycat进行写入
insert into teachers(name,age,gender) values('mycat',11,'M');
##位于132主节点，日志有记录，从节点上只有一个同步的事务记录下来了
2022-09-17T06:40:37.211746Z 112 Query insert into teachers(name,age,gender) values('mycat',11,'M')
2022-09-17T06:40:37.215598Z 7 Query BEGIN
2022-09-17T06:40:37.216535Z 7 Query COMMIT /* implicit, from Xid_log_event */
##查询一下查询的操作，是哪个server，看server id，查询的是128从节点
select @@server_id;
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 128 |
+-------------+
##更新操作试试，已经修改为serverid，正是132的id，证明读写分离是OK的
mysql> update teachers set age=@@server_id where tid=13;
Query OK, 1 row affected (0.01 sec)
mysql> select *from teachers where tid=13;;
+-----+-------+-----+--------+
| TID | Name | Age | Gender |
+-----+-------+-----+--------+
| 13 | mycat | 132 | M |
+-----+-------+-----+--------+heartbeat() select user()用途
用于后端MySQL服务器的心跳检测，mycat服务器周期性的向后端MySQL机器发送健康检查的指令，=select一下用户，如果又返回结果，则证明可以MySQL服务是存活的
2022-09-17T06:33:46.730245Z 20 Query select user()
2022-09-17T06:33:56.730453Z 16 Query select user()
2022-09-17T06:34:06.731198Z 18 Query select user()mycat暂时性的痛点
1.本身的proxy节点就存在单点故障的问题
2.无法保证后端主节点挂掉后，从节点可以替换上来，可以使用MHA高可用来实现
重点：MySQL主从复制的实现，主从数据的同步，binlog的pos记录，配置，可以在5.7后的版本开启gtid自动记录binlog的pos，可以比较简便的实现主从复制的binlog pos
MySQL MHA实现高可用
实现从节点自动替换主节点工作，也就是sql server的always on方案
always on高可用方案：自动实现故障转移，A为主，B为从，A故障业务自动转向B，或者是有提出业务的方法等，建立always on集群节点，对外暴露一个虚拟的IP，程序只需要对接到这个IP，至于后端是哪台更新机/查询机工作，并不重要
---->always on高可用性组
恢复数据的办法：
通过MHA也好，alwaysOn方案也好，终究数据在同步的时候都是存在延迟的；而一个借点挂了的话，要从节点提升一个新主上来，从必然会有一部分的数据丢失，这一点可以由MHA或者是alwayson集群管理来实现，尽可能通过redo log/binlog来恢复数据，也可以人工手动恢复数据
MySQL的实现方案：MHA高可用方案---->非常常用，一般是一主一从，也可以一主多从等这样的架构，现在阿里云上面的RDS主备架构估计也就MHA机制实现的
高可用方案的可靠性：一定要做到非常可靠，当然数据完全不丢失也不太可能，最好做到尽量不丢失，主要还是看企业内的业务需要使用怎么样的数据库，对应好怎么样的高可用方案；不同类型的DB，高可用方案不同
MHA的实现原理
1.利用健康检查语句select来检查master-slave的健康性，一旦有宕机，则manager从master上保存二进制文件，监控健康情况
2.识别哪个slave同步的数据最新，因为slave同步数据，也需要经过调用SQL thread，而这个SQL进程不一定已经落盘了
3.应用中继日志relay_log到其他节点
4.将从master保存的最新binlog同步到所有slave节点--->有个同步binlog的步骤--->也可以手动将master节点的binlog拷贝过来(如果机器在坏的不多的情况下，只有服务挂了)
5.从slave内提升一个新的master(manager行为)
6.其他的slave指向新主(manager行为)
7.移除故障节点，开始修复
MHA对外的IP地址：VIP虚拟IP，类似SQL SERVER的alyways on高可用方案，集群提供虚拟IP，通过虚拟IP来决定读/写操作，VIP自动识别新的master节点，承载写操作
安装部署MHA和MySQL主从环境
MHA的包：分为管理节点和agent节点，server端和client端，管理节点负责管理各个MySQL集群，MySQL集群内包括主从节点都是管理节点的client端
server端：只能在Cent OS 7机器
node端：可以在Cent OS 8以上
MHA检查MySQL集群的健康性：也是通过select来完成健康检查，一旦无反馈，则会认为这个manager有问题，这个集群有问题，直接提升一个从节点作为主节点
类似mycat代理通过select user()，查询语句来检查，always on节点肯定也有健康检查
环境：可以加个客户端
10.0.0.130 manager/mycat
10.0.0.132 master节点
10.0.0.128 slave1
10.0.0.129 slave21.配置主从节点关系，主从复制，my.cnf文件配置
master节点基本配置：服务端，开启gtid，自动识别事务，自动发现binlog的pos，主从都要开，不开的话，从节点会报错
[mysqld]
server-id=132 ##id
datadir=/data/mysql ##数据目录
log-bin=/data/mysql/binlog/rocky-bin ##binlog目录，指定binlog文件前缀
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log ##错误日志目录
pid-file=/data/mysql/mysql.pid
#skip-grant-tables
default_authentication_plugin=mysql_native_password
#general_log ##开启general_log，这个可以在MySQL内set global general_log=1实现
rpl_semi_sync_master_enabled ##半同步复制，可以设置timeout的时间
gtid_mode=on ##开启gtid，自动识别事务，自动发现binlog的pos
enforce_gtid_consistency
[client]
socket=/data/mysql/mysql.sockslave基本配置：开启gtid，自动识别事务，自动发现binlog的pos，主从都要开，不开的话，从节点会报错
[mysqld]
server-id=128
log-bin=/data/mysql/binlog/slave-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
rpl_semi_sync_slave_enabled ##半同步复制，可以设置timeout的时间
gtid_mode=on ##开启gtid，自动识别事务，自动发现binlog的pos，主从都要开，不开的话，从节点会报错
enforce_gtid_consistency
read_only
[client]
socket=/data/mysql/mysql.sock2.安装管理端和node端
#!/bin/bash
host=(10.0.0.128
10.0.0.132
10.0.0.129)
##expect判断直接scp rpm包过去
for i in ${host[*]};do
expect <<EOF
spawn scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm $i:/root
expect {
"yes/no" { send "yes\n"; exp_continue }
"password" { send "123\n" };
}
expect eof
EOF
done
yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm3.实现MySQL主从机器之间的SSH-KEY互信，以及manager端对于MySQL主从节点的互信
先生成自己的ssh-key，再将自己的信息复制到自己的节点，known_hosts里面已经有4个机器了，刚刚复制过
将自己的.ssh复制到其他机器的:/root目录下
:/root/.ssh：代表也拷贝.ssh目录本身
:/root/.ssh/*：代表拷贝.ssh目录下的东西
ssh-keygen
ssh-copy-id 10.0.0.130
[10:34:49 root@proxy ~]#cat .ssh/known_hosts
10.0.0.132 ecdsa-sha2-nistp256 AAAAE2VjZHNhLXNoYTItbmlzdHAyNTYAAAAIbmlzdHAyNTYAAABBBOEAlD1m567k7Q2QEem7PjJOu2e91dNnnvu3BieR3f3TVFQdqsWqGI/qCgJVLQ7KWWPNFy137uYUwviZa7d8Q/Q=
10.0.0.128 ecdsa-sha2-nistp256 AAAAE2VjZHNhLXNoYTItbmlzdHAyNTYAAAAIbmlzdHAyNTYAAABBBGNiEH9kMJZU+HXl2fpHO2JQ9tIbiVh5qljJS+ZoGh1iNzX3S7pGM/Dd16BkSVJgrgMyXOdr832C7na6SIgoylM=
10.0.0.129 ecdsa-sha2-nistp256 AAAAE2VjZHNhLXNoYTItbmlzdHAyNTYAAAAIbmlzdHAyNTYAAABBBOf8Uo6Tm0vy+2g9ctYsTwc/tfWcXHQtY5EywK0odPY1KFdXvw/bi75eyTbBqMUnX90RY36V+SV2CF05KCP1FJ0=
10.0.0.130 ecdsa-sha2-nistp256 AAAAE2VjZHNhLXNoYTItbmlzdHAyNTYAAAAIbmlzdHAyNTYAAABBBOf8Uo6Tm0vy+2g9ctYsTwc/tfWcXHQtY5EywK0odPY1KFdXvw/bi75eyTbBqMUnX90RY36V+SV2CF05KCP1FJ0=
##复制ssh认证
#!/bin/bash
host=(10.0.0.128
10.0.0.132
10.0.0.129)
##expect判断直接scp rpm包过去，还是得先进行ssh-copy-id的验证
for i in ${host[*]};do
expect <<EOF
spawn ssh-copy-id $i
expect {
"yes/no" { send "yes\n"; exp_continue }
"password" { send "123\n" };
}
expect eof
EOF
scp /root/.ssh $i:/root
done4.manager端配置
app1.cnf配置
##新建配置文件的目录
mkdir -p /data/mastermha/app1
touch /data/mastermha/app1
vim /data/mastermha/app1.cnf
##app1.cnf文件配置
[server default]
user=mhauser ##在主节点上创建用户，自动同步到从节点--->完成
password=123
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log ##自动创建管理端的日志
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=sync --->完成
repl_password=123
ping_interval=1
master_ip_failover_script=/usr/local/bin/master_ip_failover
report_script=/usr/local/bin/sendmail.sh
check_repl_delay=0
master_binlog_dir=/data/mysql/binlog ##binlog位置
[server1]
hostname=10.0.0.132
candidate_master=1 ##master优先做主
[server2]
hostname=10.0.0.128
candidate_master=1 ##master优先做主
[server3]
hostname=10.0.0.129
##检查binlog，也可以开启general_log观察结果
[(none)]>show variables like '%log_bin%';
##主从都开启general_log
select @@general_log;
set global general_log=ON;MySQL集群节点授权mha账户
可以修改MySQL集群的配置，所以需要能够有所有库的所有权限，需要和app1.cnf内一致
##在主节点上创建用户，自动同步到从节点
[hellodb]>create user mhauser@'%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
[hellodb]>grant all on *.* to mhauser@'%';
Query OK, 0 rows affected (0.00 sec)
[hellodb]>flush privileges;
Query OK, 0 rows affected (0.00 sec)
[hellodb]>select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mhauser | % |
| mycat | % |
| root | % |
| sync | % |
| wp | % |
| catyer | 10.0.0.% |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| wpuser | localhost |
+------------------+-----------+
11 rows in set (0.00 sec)master节点添加一个VIP，参考perl脚本的写法
master down了可以将master飘到别的master上
将perl脚本放到/use/local/bin下面，临时加
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip"; ##网卡上加多一个IP地址，网卡名记得修改，
my $ssh_stop_vip = "/sbin/ifconfig eens33:$key down";
##master节点配置
ifconfig ens224
ifconfig ens224:1 10.0.0.100/24
2: ens224: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
link/ether 00:0c:29:33:3d:85 brd ff:ff:ff:ff:ff:ff
inet 10.0.0.132/24 brd 10.0.0.255 scope global noprefixroute ens224
valid_lft forever preferred_lft forever
inet 10.0.0.100/24 brd 10.0.0.255 scope global secondary ens224:1
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe33:3d85/64 scope link noprefixroute
valid_lft forever preferred_lft foreverperl脚本的实现：/usr/local/bin/master_ip_failover
把这个脚本放在这个路径下，记得修改执行权限
chmod +x /usr/local/bin/master_ip_failover脚本实现
#!/usr/bin/env perl
# Copyright (C) 2011 DeNA Co.,Ltd.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
## Note: This is a sample script and is not complete. Modify the script based on your environment.
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
use MHA::DBHelper;
my (
$command, $ssh_user, $orig_master_host,
$orig_master_ip, $orig_master_port, $new_master_host,
$new_master_ip, $new_master_port, $new_master_user,
$new_master_password
);
my $vip = '10.0.0.100/24'; ##VIP给程序的显示
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig eens33:$key $vip"; ##网卡上加多一个IP地址，网卡名记得修改
my $ssh_stop_vip = "/sbin/ifconfig eens33:$key down";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
'new_master_user=s' => \$new_master_user,
'new_master_password=s' => \$new_master_password,
);
exit &main();
sub main {
if ( $command eq "stop" || $command eq "stopssh" ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
my $exit_code = 1;
eval {
# updating global catalog, etc
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
`ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}自动发送告警邮件实现
配置163邮箱的auth，需要update一下postfix的包，以免postfix这个邮件服务太老了
vim /etc.mail.rc
set from=catyer.163.com
set smtp=smtp.163.com
set smtp-auth-user=catyer.163.com
set smtp-auth-password=SOKFDDZOQCDEZCYY
yum -y install postfix
vim sendmail.sh
##记得修改执行权限
chmod +x sendmail.sh
echo "MHA is down!" | mail -s "MHA Warning" catyer@163.com5.检查app1.cnf文件的语法
successfully，检查到各个节点的通信
##检查各个节点之间的SSH连接
masterha_check_ssh --conf=/root/mastermha/app1.cnf
##检查主从复制
masterha_check_repl --conf=/root/mastermha/app1.cnf
抛出异常，无法识别super_read_only变量，缺少libmysqlclient.so.18依赖文件
不用修改任何的东西，只是缺少这个libmysqlclient.so.18依赖库文件
也不是什么super_read_only没开，都不关事的
Checking if super_read_only is defined and turned on..install_driver(mysql) failed: Can't load '/usr/lib64/perl5/vendor_perl/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.18: cannot open shared object file: No such file or directory at /usr/lib64/perl5/DynaLoader.pm line 190.
at (eval 11) line 3.
Compilation failed in require at (eval 11) line 3
vim +244 /usr/share/perl5/vendor_perl/MHA/SlaveUtil.pm
my $sth = $dbh->prepare("SELECT 0 as Value");
#$dbh->do("SET GLOBAL super_read_only=off;");
$dbh->do("SET GLOBAL read_only=on;");
my $sth = $dbh->prepare("SELECT \@\@global.super_read_only as Value");
set global super_read_only=ON;
my $sth = $dbh->prepare("SELECT 1 as Value"); ##--->心跳检查
ln -s /usr/local/mysql/lib/libmysqlclient.so.21.2.30 /usr/lib64/libmysqlclient.so.21.2.30
wget -O /usr/lib/libmysqlclient.so.18 http://files.directadmin.com/services/es_7.0_64/libmysqlclient.so.18
set global global super_read_only=OFF;解决办法：因为是二进制安装的，所以缺少这个包mysql-community-libs-compat-8.0.30-1.el7.x86_64.rpm，从官网下载了，不知道为啥安装不上，缺少依赖
yum -y install mysql-community-libs
上次元数据过期检查：0:45:58 前，执行于 2022年09月18日 星期日 15时49分55秒。
未找到匹配的参数: mysql-community-libs
错误：没有任何匹配: mysql-community-libs
##从别的机器使用yum安装mysql-8.0.30，拷贝/usr/lib64/mysql/libmysqlclient.so.18过来
host=(10.0.0.128
10.0.0.132
10.0.0.129)
for i in ${host[*]};do
scp /usr/lib64/mysql/libmysqlclient.so.18 $i:/usr/lib64/mysql/
done
[root@master mysql]#ls
libmysqlclient.a libmysqlclient.so libmysqlclient.so.18 libmysqlservices.a
check ok
6.启动MHA进程，查看日志；若集群内的主机挂掉后，需要再次执行开始MHA进程
管理节点上执行
masterha_manager --conf=/root/mastermha/app1.cnf --remove_dead_master_conf --ingore_last_failover
##挂在后台执行，生产中执行
nohup masterha_manager --conf=/root/mastermha/app1.cnf --remove_dead_master_conf --ingore_last_failover
查看master的general_log，发现是有健康检查的条目的，只检查MASTER节点健康
[hellodb]>show variables like '%general_log%';
tail -f /data/mysql/rocky.log
tail -f /data/mysql/master.log
tail -f /data/mysql/slave1.log
2022-09-18T08:56:24.776722Z 205 Query SELECT 1 As Value
2022-09-18T08:56:25.777208Z 205 Query SELECT 1 As Value
2022-09-18T08:56:26.777319Z 205 Query SELECT 1 As Value
tail -f /data/mastermha/app1/manager.log
ping select until mysql doesn't respond; ##健康检查，只有MASTER节点会有健康检查，所以SLAVE1和SLAVE2都不会有
[16:53:19 root@proxy ~]#tail -f /data/mastermha/app1/manager.log
Checking the Status of the script.. OK
SIOCSIFADDR: No such device
eth0:1: ERROR while getting interface flags: No such device
SIOCSIFNETMASK: No such device
Sun Sep 18 16:55:27 2022 - [info] OK.
Sun Sep 18 16:55:27 2022 - [warning] shutdown_script is not defined.
Sun Sep 18 16:55:27 2022 - [info] Set master ping interval 1 seconds.
Sun Sep 18 16:55:27 2022 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Sun Sep 18 16:55:27 2022 - [info] Starting ping health check on 10.0.0.132(10.0.0.132:3306)..
Sun Sep 18 16:55:27 2022 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..7.测试MHA的功能，模拟主节点down机
MHA进程为一次性的，加入down掉了，就要重新启动MHA进程
在主节点上执行存储过程，会自动同步到从节点。
之前在app1.cnf上面配置了candidate_master=1，现在128替换132称为主节点了
call sp_testlog;
select count(*) from testlog;
##停止MySQL服务
systemctl stop mysqld
##生成了一个manager.log的报告，fail的报告
tail -f /data/mastermha/app1/manager.log
----- Failover Report -----
app1: MySQL Master failover 10.0.0.132 to 10.0.0.128 succeeded
Master 10.0.0.132 is down!
Check MHA Manager logs at proxy:/data/mastermha/app1/manager.log for details.
查看10.0.0.128的状态，已经切换到主了，检查数据同步的多少
如果在MASTER节点挂掉的情况下，you新写入的数据，则需要全库导出导入，备份恢复一下再加入到MySQL集群
##从节点状态没了
[hellodb]>show slave status \G;
Empty set, 1 warning (0.00 sec)
##在UP的从节点查看，已经切换成128了
[hellodb]>show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.128
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: slave-bin.000013
Read_Master_Log_Pos: 35534754
Relay_Log_File: slave1-relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: slave-bin.000013
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
##查看执行的存储过程一致性，count基本一致
[hellodb]>select count(*) from testlog;
+----------+
| count(*) |
+----------+
| 115030 |
+----------+
##新主节点查看进程，来自129的从节点
| 111 | sync | 10.0.0.129:45244 | NULL | Binlog Dump | 1413 | Source has sent all binlog to replica; waiting for more updates | NULL |
##没改动，没更新的数据的情况下，可以直接change master;
[hellodb]>flush logs;
Query OK, 0 rows affected (0.00 sec)
[hellodb]>show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-----------------------------------------------+
| slave-bin.000014 | 197 | | | 5aa76b1b-1b77-11ed-9219-000c29333d85:1-115049 |
+------------------+----------+--------------+------------------+-----------------------------------------------+
##修改主节点
CHANGE MASTER TO
MASTER_HOST='10.0.0.128',
MASTER_USER='sync',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='slave-bin.000014', MASTER_LOG_POS=197;
start slave;
##节点状态是OK的
[(none)]>show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.128
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: slave-bin.000014
Read_Master_Log_Pos: 197
Relay_Log_File: rocky-relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: slave-bin.000014
Slave_IO_Running: Yes
Slave_SQL_Running: YesTIDB：分布式的数据库
分布式表
MySQL的最大连接数：show variables like '%connection%'
最大并发连接数：151个
[(none)]>show variables like '%connection%';
+-----------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------+----------------------+
| character_set_connection | utf8mb4 |
| collation_connection | utf8mb4_0900_ai_ci |
| connection_memory_chunk_size | 8912 |
| connection_memory_limit | 18446744073709551615 |
| global_connection_memory_limit | 18446744073709551615 |
| global_connection_memory_tracking | OFF |
| max_connections | 151 |
| max_user_connections | 0 |
| mysqlx_max_connections | 100 |
+-----------------------------------+----------------------+mysqlslap：MySQL压测工具
测试用例：查看在1000并发的情况下，
mysqlslap -a -c 1000
[root@rocky ~]#mysqlslap -uroot -p123 -a -c 1000
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 17.270 seconds
Minimum number of seconds to run all queries: 17.270 seconds
Maximum number of seconds to run all queries: 17.270 seconds
Number of clients running queries: 1000
Average number of queries per client: 0调整MySQL的最大并发连接数
set global max_connections=5000;
vim /etc/my.cnf
max_connections=5000
[(none)]>set global max_connections=5000;
Query OK, 0 rows affected (0.00 sec)
[(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| catyer |
| hellodb |
| information_schema |
| mysql |
| mysqlslap |
| performance_schema |
| sys |
| trigger_pra |
| wordpress |
| zabbix |
+--------------------+
10 rows in set (0.02 sec)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
