①主服务器将所有数据和结构更改记录到二进制日志中。
②从属服务器从主服务器请求该二进制日志并在本地应用其内容。
③IO:请求主库,获取上一次执行过的新的事件,并存放到relaylog
④SQL:从relaylog中将sql语句翻译给从库执行
二.主从复制原理
1.主从复制的前提
①两台或两台以上的数据库实例
②主库要开启二进制日志
③主库要有复制用户
④主库的server_id和从库不同
⑤从库需要在开启复制功能前,要获取到主库之前的数据(主库备份,并且记录binlog当时位置)
⑥从库在第一次开启主从复制时,时必须获知主库:ip,port,user,password,logfile,pos
⑦从库要开启相关线程:IO、SQL
⑧从库需要记录复制相关用户信息,还应该记录到上次已经从主库请求到哪个二进制日志
⑨从库请求过来的binlog,首先要存下来,并且执行binlog,执行过的信息保存下来
2.主从复制涉及到的文件和线程
主库:
①主库binlog:记录主库发生过的修改事件
②dump thread:给从库传送(TP)二进制日志线程
从库:
①relay-log(中继日志):存储所有主库TP过来的binlog事件
②master.info:存储复制用户信息,上次请求到的主库binlog位置点
③IO thread:接收主库发来的binlog日志,也是从库请求主库的线程
④SQL thread:执行主库TP过来的日志
主从复制原理图
大前提条件:做主从复制之前,一定要保证主库和从库之间数据一致性
①通过change master to语句告诉从库主库的ip,port,user,password,file,pos
②从库通过start slave命令开启复制必要的IO线程和SQL线程
③从库通过IO线程拿着change master to用户密码相关信息,连接主库,验证合法性
④从库连接成功后,会根据binlog的pos问主库,有没有比这个更新的
⑤主库接收到从库请求后,比较一下binlog信息,如果有就将最新数据通过dump线程给从库IO线程
⑥从库通过IO线程接收到主库发来的binlog事件,存储到TCP/IP缓存中,并返回ACK更新http://master.info
⑦将TCP/IP缓存中的内容存到relay-log中
⑧SQL线程读取http://relay-log.info,读取到上次已经执行过的relay-log位置点,继续执行后续的relay-log日志,执行完成后,更新http://relay-log.info
三、主从复制搭建实战
主库操作:
1)修改配置文件
#编辑mysql配置文件 [root@db01 ~]# vim /etc/my.cnf #在mysqld标签下配置 [mysqld] #主库server-id为1,从库不等于1 server_id =1 #开启binlog日志 log_bin=mysql-bin
2)创建主从复制用户
#登录数据库 [root@db01 ~]# mysql -uroot -poldboy123 #创建rep用户 mysql> grant replication slave on *.* to rep@"10.0.0.%" identified by "123";
从库操作:
1)修改配置文件
#修改db02配置文件 [root@db02 ~]# vim /etc/my.cnf #在mysqld标签下配置 [mysqld] #主库server-id为1,从库不等于1, 但从库之间的server_id可以相等 server_id =5 #重启mysql [root@db02 ~]# /etc/init.d/mysqld restart #记录主库binlog及位置点 [root@db01 ~]# mysql -uroot -poldboy123 mysql> show master status; | mysql-bin.000002 | 317 #登陆数据库 [root@db02 ~]# mysql -uroot -poldboy123 #执行change master to 语句 mysql> change master to -> master_host="10.0.0.51", -> master_user="rep", -> master_password="123", -> master_log_file="mysql-bin.000002", -> master_log_pos=317, -> master_port=3306; mysql> start slave; mysql> show slave statusG Slave_IO_Running: Yes Slave_SQL_Running: Yes
四.主从复制基本故障处理
IO线程报错解决思路
# IO线程报错: 解决思路: 1.网络 [root@db02 ~]# ping 10.0.0.51 1)硬件层,路由,交换机,网络设备 2)网线 3)安全组规则 4)插错网线口 2.端口 [root@db02 ~]# telnet 10.0.0.51 3306 #关闭防火墙 systemctl stop firewalld #防火墙添加允许mysql端口 firewalld-cmd --add-service=mysql firewalld-cmd --add-port=3306/tcp 3.用户名 mysql> grant replication slave on *.* to rep@"%" identified by "123"; 4.密码,先登录测试 [root@db03 data]# mysql -urep -p123 -h10.0.0.51 如果报错 #rep@"db03",需在参数,跳过反向解析 vim /etc/my.cnf skip_name_resolve #搭建主从时,用户名、密码、主机域、端口一定要一致。 change master to master_host="10.0.0.51",#1 master_user="rep",#2 master_password="123",#3 master_log_file="mysql-bin.000003", master_log_pos=169853, master_port=3306;
SQL线程报错
处理方法一:
#临时停止同步 mysql> stop slave; #将同步指针向下移动一个(可重复操作) mysql> set global sql_slave_skip_counter=1; #开启同步 mysql> start slave;
处理方法二:
#编辑配置文件 [root@db01 ~]# vim /etc/my.cnf #在[mysqld]标签下添加以下参数,把线程号添加到配置文件 slave-skip-errors=1032,1062,1007
但是方法一、方法二都是有风险存在的,只是跳过错误,不能从根本上解决问题
处理方法三:
1)重新备份数据库,恢复到从库
2)给从库设置为只读
#在命令行临时设置 set global read_only=1; #在配置文件中永久生效 read_only=1
注意:登录用户如果是all权限,包含了super超级权限,还是可以进行操作的
- all 权限,即使配置文件设置了只读,还是都可以操作的。
- 不加all权限。哪怕给他指定select,insert, delete ,create 权限,都是不能操作,只能只读的。
例如:
#设置配置文件永久生效 [root@db03 ~]# vim /etc/my.cnf read_only=1 #重启 [root@db03 ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! #查看 mysql> show variables like "read_only"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.00 sec)
1. all 权限
[root@db03 ~]# mysql mysql> grant all on *.* to rea@"%" identified by "123"; Query OK, 0 rows affected (0.00 sec) [root@db03 ~]# mysql -urea -p123 -h 10.0.0.53 mysql> create database aaa; Query OK, 1 row affected (0.01 sec)
2.不是all权限
mysql> grant select,create,delete,insert on *.* to rea1@"%" identified by "123"; Query OK, 0 rows affected (0.00 sec) [root@db03 ~]# mysql -urea1 -p123 -h10.0.0.53 mysql> create database bbb; ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement mysql> drop database test; ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement ...
五.延时从库
普通的主从复制可能存在不足
1)逻辑损坏怎么办?
2)不能保证主库的操作,从库一定能做
3)高可用?自动failover?
4)过滤复制
企业中一般会延时3-6小时
延时从库配置方法
#停止主从 mysql>stop slave; #设置延时为180秒 mysql> change master to master_delay= 180; #开启主从 mysql>start slave; #查看状态 mysql> show slave statusG SQL_Delay: 60 #或者做主从的时候直接指定延时 change master to master_host="10.0.0.51", ... master_delay=3600; 3.延时从库停止方法 #停止主从 mysql> stop slave; #设置延时为0 mysql> change master to master_delay = 0; #开启主从 mysql> start slave;
企业案例:
总数据量级500G,正常备份去恢复需要1.5-2小时
1)配置延时3600秒
mysql>change master to master_delay = 3600;
2)主库
drop database db;
3)怎么利用延时从库,恢复数据?
提示:
1、从库relaylog存放在datadir目录下
2、mysqlbinlog 可以截取relaylog内容
3、show relay log events in "db01-relay-bin.000001";
处理的思路:
1)停止SQL线程
2)截取relaylog到误删除之前点
- http://relay-log.info 获取到上次运行到的位置点,作为恢复起点
- 分析relay-log的文件内容,获取到误删除之前position
模拟故障处:
1)关闭sql
2)模拟数据
3)开启从库延时3600s
4)破坏,模拟删库故障。(以下步骤在5分钟内操作完成。)
5)从库,关闭SQL线程
6)截取relay-log
恢复relay.sql
方法一:取消从库身份,恢复数据,从库当主库
方法二:从库导出来的数据传给主库,恢复数据
模拟环境
1.主库
#一直写数据 [root@db01 ~]# sh mysqldump.sh #全备 [root@db01 ~]# mysqldump -uroot -p1 -A --triggers -R --master-data=2 --single-transaction |gzip >/backup/full.gz Warning: Using a password on the command line interface can be insecure. #查看 [root@db01 ~]# ll /backup/ total 376 -rw-r--r-- 1 root root 384381 Nov 17 09:22 full.gz #查看mysql-bin和起点 [root@db01 ~]# zcat /backup/full.gz |head -25 -- CHANGE MASTER TO MASTER_LOG_FILE="mysql-bin.000001", MASTER_LOG_POS=517; #scp到对端 [root@db01 ~]# scp /backup/dbdb.sql 172.16.1.54:/tmp [root@db01 ~]# mysql -uroot -p1 mysql> grant replication slave on *.* to rep@"%" identified by "123";
从库
#准备初始化环境 [root@db04 ~]# /etc/init.d/mysqld stop [root@db04 ~]# rm -fr /application/mysql/data; # at 45118 #191117 13:38:58 server id 1 end_log_pos 88964 CRC32 0x905241e7 Query thread_id=2671 exec_time=0 error_code=0 SET TIMESTAMP=1573969138; drop database db1 #截取这段数据导出并发送给主库 [root@db04 data]# mysqlbinlog --start-position=283 --stop-position=45118 /opt/mysql/data/db04-relay-bin.000002 >/tmp/db_zeng.sql [root@db04 data]# scp /tmp/db_zeng.sql 172.16.1.51:/backup/
7.主库将两段内容导入并查看数据完整性
[root@db01 data]# mysql -uroot -p1 select count(*) from db1.t1; +----------+ | count(*) | +----------+ | 900 | +----------+ 1 row in set (0.00 sec)
8.从库开启sql线程 将延时关闭查看是否同步
[root@db04 data]# mysql mysql> start slave sql_thread; mysql> stop slave; mysql> change master to master_delay=0; mysql> start slave; #查看 mysql> select count(*) from db1.t1; +----------+ | count(*) | +----------+ | 900 | +----------+ 1 row in set (0.00 sec)
9.再次开启延时
mysql> stop slave; mysql> change master to master_delay=3600; mysql> start slave; Query OK, 0 rows affected (0.01 sec)