配置binlog server
原创
©著作权归作者所有:来自51CTO博客作者wx5d4124a358e8a的原创作品,请联系作者获取转载授权,否则将追究法律责任
1创建一个用于存放主库binlog二进制日志的目录并授权mysql用户(本例在db03中):
注意:一定要和原主库的二进制日志目录不一样
[root@db03 ~]# mkdir -p /data/mysql/binlog
[root@db03 ~]# chown -R mysql.mysql /data/*
2添加一个manager管理的binlog节点,当连接主库的那个节点无法访问登录后,manager会自动访问我们配置的这个binlog节点,以保证主从二进制日志的一致性(本例在db03中):
[root@db03 ~]# vim /etc/mha/appl.cnf
[root@db03 ~]# cat /etc/mha/appl.cnf
[server default]
manager_log=/var/log/mha/appl/manager
manager_workdir=/var/log/mha/appl
master_binlog_dir=/data/binlog #原主库二进制目录
master_ip_failover_script=/usr/local/bin/master_ip_failover
password=mha
ping_interval=2
repl_password=123
repl_user=repl
report_script=/usr/local/bin/send
ssh_user=root
user=mha
[server1]
hostname=10.0.0.201
port=3306
[server2]
hostname=10.0.0.202
port=3306
[server3]
hostname=10.0.0.203
port=3306
[binlog1]
no_master=1
hostname=10.0.0.203
master_binlog_dir=/data/mysql/binlog
3登录从库查看主库正在使用的文件名:
db03 [(none)]>show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.201
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
4进入到自己创建用于存放主库binlog二进制日志的目录,拉去二进制日志的起点
[root@db03 ~]# cd /data/mysql/binlog
[root@db03 binlog]# mysqlbinlog -R --host=10.0.0.201 --user=mha --password=mha --raw --stop-never mysql-bin.000011 &
[root@db03 binlog]# ll
总用量 4
-rw-r-----. 1 root root 234 3月 9 23:26 mysql-bin.000011
[root@db03 binlog]#
5测试一下如果有新的日志,会不会从主库实时的拉到我们配置的binlog server的目录里
5.1登录主库刷新日志:
[root@db01 mysql]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
db01 [(none)]>flush logs;
5.2进入到binlog server所在的机器中进入存放主库日志的目录查看是否有新的日志同步过来
[root@db03 ~]# cd /data/mysql/binlog
[root@db03 binlog]# ll
总用量 8
-rw-r-----. 1 root root 281 3月 9 23:31 mysql-bin.000011
-rw-r-----. 1 root root 234 3月 9 23:31 mysql-bin.000012