一、工作原理
1.1 示意图
1.2 文字解释
- 主服务器开启二进制日志,从服务器开启中继日志
- 主服务器日志发生更新后,从服务器通过I/O线程探测到更新并发送请求二进制事件
- 主服务器通过dump线程将更新的二进制日志事件发送给从服务器
- 从服务器将事件写入中继日志,通过SQL线程将日志读取为sql语句,重放执行语句,保持数据的统一性
二、搭建
2.1 架构
模拟环境 | IP地址 | 所需工具 |
主服务器 | 192.168.13.10 | mysql-boost-5.7.20.tar |
从服务器 | 192.168.13.20 | mysql-boost-5.7.20.tar |
2.2 主服务器
- mysql安装步骤在这篇博客已经写了:msql源码安装
systemctl disable --now firewalld setenforce 0 hostnamectl set-hostname Mysql1 vim /etc/my.cnf server-id = 1 log_bin = master-bin log-slave-updates = true systemctl restart mysqld.service ln -s /usr/local/mysql/bin/mysql /usr/sbin/ ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/ mysql -uroot -pabc123 grant replication slave on *.* to 'myslave'@'192.168.13.%' identified by '123'; grant all privileges on *.* to 'mha'@'192.168.13.%' identified by 'manager'; grant all privileges on *.* to 'mha'@'Mysql1' identified by 'manager'; grant all privileges on *.* to 'mha'@'Mysql2' identified by 'manager'; grant all privileges on *.* to 'mha'@'Mysql3' identified by 'manager'; flush privileges; show master status; }
- 主服务器的日志名为mysql-bin.000001,偏移量为915
2.3 从服务器
systemctl disable --now firewalld setenforce 0 hostnamectl set-hostname Mysql2 vim /etc/my.cnf server-id = 2 log_bin = master-bin relay-log = relay-log-bin relay-log-index = slave-relay-bin.index systemctl restart mysqld.service ln -s /usr/local/mysql/bin/mysql /usr/sbin/ ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/ mysql -uroot -pabc123 grant replication slave on *.* to 'myslave'@'192.168.13.%' identified by '123'; grant all privileges on *.* to 'mha'@'192.168.13.%' identified by 'manager'; grant all privileges on *.* to 'mha'@'Mysql1' identified by 'manager'; grant all privileges on *.* to 'mha'@'Mysql2' identified by 'manager'; grant all privileges on *.* to 'mha'@'Mysql3' identified by 'manager'; flush privileges; change master to master_host='192.168.13.10',master_user='myslave',master_password='123',master_log_file='master-bin.000001',master_log_pos=915; start slave; }
2.4 验证
- 进入主服务器创建任意库;
- 进入从服务器查看所有库,发现数据同步;
三、思维导图
四、结语
- Slave_IO_Running: No:途中我遇到了两次这种情况
- 一:检查自己的防火墙是否关闭
- 二:检查自己文件名及偏移量是否输入错误
- 三:检查mysql数据库的UUID是否相同,我是源码编译,路径为:/usr/local/mysql/data//auto.cnf,若是主从的数据库UUID是相同的,则无法连接成功,随便改动一个数据库的UUID其中任意一个字母即可
- 四:/etc/my.cnf 的配置文件有问题
- 五:网络不通