当前位置: 首页 > article >正文

MHA binlog server

GTID模式下切换的时候,默认是不会去Master上获取binlog,如果配置了Binlog Server,MHA机会去binlog-server上获取,下面创建一个binlog server,并验证binlog server能够在主库binlog被清理的情况下提供日志恢复.

搭建binlog_server

创建目录:创建binlog_server用于存放master上的binlog

[root@mha2 binlog_server]# mkdir binlog_server
[root@mha2 binlog_server]# pwd
/export/binlog_server

开启远程备份binlog

nohup mysqlbinlog --no-defaults --read-from-remote-server --raw --result-file=/export/binlog_server/ --host=192.168.10.129 --port=3358 --user=mha --password=abcd1234 --stop-never mysql-bin.000079 &

--raw: 以原始格式输出,而不是解析为SQL语句

--result-file: 本地路径,用于保存binlog

--user=mha: mysql账户,用于连接mysql并备份binlog,需要有replication cilent权限

--stop-never: 不停

mysql-bin.000060: 在master上第一个binlog,从这个binlog开始向后备份

更新配置:配置中添加了[binlog1] 部分,且[server2]添加了no_master=1

binlog server和master不要放在同一个机器,否则主库挂了同时binlog server也挂了;

[server default]
manager_workdir=/export/mha/app1
manager_log=/export/mha/app1/manager.log
master_binlog_dir=/export/data/mysql
log_level=debug
user=mha
password=abcd1234
ping_interval=3
ping_type=INSERT
remote_workdir=/export/mha/app1
ssh_user=root
repl_user=repl
repl_password=abcd1234
 
[server1]
hostname=192.168.10.129
candidate_master=1
port=3358
master_binlog_dir=/export/data/mysql
 
[server2]
no_master=1
hostname=192.168.10.163
port=3358
master_binlog_dir=/export/data/mysql
 
[server3]
hostname=192.168.10.162
port=3358
master_binlog_dir=/export/data/mysql

[binlog1]
hostname=192.168.10.163
master_binlog_dir=/export/binlog_server

查看同步

查看master中的binlog和binlog_server中对比,是否获取了最新的binlog,切master flush的时候从库也会同步更新.

# master
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000079 |      3699 |
| mysql-bin.000080 |       257 |
| mysql-bin.000081 |       234 |
+------------------+-----------+
3 rows in set (0.00 sec)

# slave
[root@mha2 binlog_server]# ls
mysql-bin.000079  mysql-bin.000080  mysql-bin.000081 

模拟故障

1,slave: stop slave io_thread

2, 开启MHA

3, master: 执行一些事务

mysql> insert into hero3(name,age) values(name,age);
Query OK, 1 row affected (0.03 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into hero3(name,age) values(name,age);
Query OK, 1 row affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000079 |      3699 |
| mysql-bin.000080 |       257 |
| mysql-bin.000081 |    123737 |
| mysql-bin.000082 |     89801 |
| mysql-bin.000083 |       281 |
| mysql-bin.000084 |      2667 |
| mysql-bin.000085 |       546 |
| mysql-bin.000086 |       234 |
+------------------+-----------+
8 rows in set (0.00 sec)

4, 查看主从数据差异

# master
mysql> select count(1) from hero3;
+----------+
| count(1) |
+----------+
|     7170 |
+----------+
1 row in set (0.01 sec)

# slave:
mysql> select count(1) from hero3;
+----------+
| count(1) |
+----------+
|     7168 |
+----------+
1 row in set (0.02 sec)

5, 清理主库所有binlog

mysql> purge binary logs to 'mysql-bin.000086';
Query OK, 0 rows affected (0.00 sec)

6, 将主库关闭实例

此时触发故障切换

7, 查看新主从上的数据,同步了刚刚丢失的数据

mysql> select count(1) from hero3;
+----------+
| count(1) |
+----------+
|     7170 |
+----------+
1 row in set (0.00 sec)

8,查看切换日志

From:
192.168.10.129(192.168.10.129:3358) (current master)
 +--192.168.10.163(192.168.10.163:3358)
 +--192.168.10.162(192.168.10.162:3358)

To:
192.168.10.162(192.168.10.162:3358) (new master)
 +--192.168.10.163(192.168.10.163:3358)
on Dec 23 23:58:36 2024 - [info] Fetching binary logs from binlog server 192.168.10.163..
Mon Dec 23 23:58:36 2024 - [info] Executing binlog save command: save_binary_logs --command=save --start_file=mysql-bin.000082  --start_pos=89047 --output_file=/export/mha/app1/saved_binlog_binlog1_20241223235834.binlog --handle_raw_binlog=0 --skip_filter=1 --disable_log_bin=0 --manager_version=0.58 --oldest_version=5.7.19-log  --debug  --binlog_dir=/export/binlog_server 
  Creating /export/mha/app1 if not exists..    ok.
 Concat binary/relay logs from mysql-bin.000082 pos 89047 to mysql-bin.000086 EOF into /export/mha/app1/saved_binlog_binlog1_20241223235834.binlog ..
Executing command: mysqlbinlog --start-position=89047  /export/binlog_server/mysql-bin.000082 >> /export/mha/app1/saved_binlog_binlog1_20241223235834.binlog
Executing command: mysqlbinlog --start-position=4  /export/binlog_server/mysql-bin.000083 >> /export/mha/app1/saved_binlog_binlog1_20241223235834.binlog
Executing command: mysqlbinlog --start-position=4  /export/binlog_server/mysql-bin.000084 >> /export/mha/app1/saved_binlog_binlog1_20241223235834.binlog
Executing command: mysqlbinlog --start-position=4  /export/binlog_server/mysql-bin.000085 >> /export/mha/app1/saved_binlog_binlog1_20241223235834.binlog
Executing command: mysqlbinlog --start-position=4  /export/binlog_server/mysql-bin.000086 >> /export/mha/app1/saved_binlog_binlog1_20241223235834.binlog
 Concat succeeded.
Mon Dec 23 23:58:37 2024 - [info] scp from root@192.168.10.163:/export/mha/app1/saved_binlog_binlog1_20241223235834.binlog to local:/export/mha/app1/saved_binlog_192.168.10.163_binlog1_20241223235834.binlog succeeded.
Mon Dec 23 23:58:37 2024 - [info] End of log messages from 192.168.10.163.
Mon Dec 23 23:58:37 2024 - [info] Saved mysqlbinlog size from 192.168.10.163 is 134787 bytes.
Mon Dec 23 23:58:37 2024 - [info] Checking if super_read_only is defined and turned on..
Mon Dec 23 23:58:37 2024 - [info]  not present or turned off, ignoring.
Mon Dec 23 23:58:37 2024 - [info] Applying differential binlog /export/mha/app1/saved_binlog_192.168.10.163_binlog1_20241223235834.binlog ..
Mon Dec 23 23:58:38 2024 - [info] Differential log apply from binlog server succeeded.
Mon Dec 23 23:58:38 2024 - [info] Getting new master's binlog name and position..

日志中可以看到去访问binlog,获取binlog,并应用binlog


http://www.kler.cn/a/449829.html

相关文章:

  • 网络管理 详细讲解
  • 2、光同步数字传送网的特点
  • DCN-DCN路由器online_list.php存在任意文件读取漏洞
  • STM32F407寄存器点灯
  • 【蓝桥杯】43688-《Excel地址问题》
  • ChatGPT之父:奥尔特曼
  • 代码随想录day22 | 回溯算法理论基础 leetcode 77.组合 77.组合 加剪枝操作 216.组合总和III 17.电话号码的字母组合
  • 【蓝碳】基于GEE云计算、多源遥感、高光谱遥感技术、InVEST模型、PLUS模型的蓝碳储量估算;红树林植被指数计算及提取
  • vue中的css深度选择器v-deep 配合!important
  • 【MySQL】MySQL 官方安装包形式
  • 日志以及MVCC
  • Linux(Ubuntu)命令大全——已分类整理,学习、查看更加方便直观!(2024年最新编制)
  • Linux Shell 脚本编程基础知识篇—shell 运算命令详解
  • Vue2四、 scoped样式冲突,data是一个函数,组件通信-父传子-子传父-非父子
  • 每天学习一个思维模型 - 直觉
  • 什么是根服务器?有什么作用?
  • 搜索引擎蜘蛛池的原理是什么,蜘蛛池搭建教程(蜘蛛池.中国)
  • 运维工程师面试系统监控与优化自动化与脚本云计算的理解虚拟化技术的优点和缺点
  • docker 安装openresty
  • CentOS7系统下部署tomcat,浏览器访问localhost:8080/
  • 网络安全检测
  • 无需公网IP!如何在威联通NAS上实现SFTP远程访问管理传输文件
  • c++--------------------------------接口实现
  • 新能源汽车锂离子电池各参数的时间序列关系
  • C# 范围判断函数
  • 嵌入式Linux QT+OpenCV基于人脸识别的考勤系统 项目