百度360必应搜狗淘宝本站头条
mqtt服务器xftp6vscode使用技巧mybatiscollectionkeyerror
当前位置：网站首页 > 热门文章 > 正文

MySQL --13 主从复制

bigegpt 2024-08-05 11:30 5 浏览

一.主从复制简介

复制是 MySQL 的一项功能，允许服务器将更改从一个实例复制到另一个实例。

1）主服务器将所有数据和结构更改记录到二进制日志中。
2）从属服务器从主服务器请求该二进制日志并在本地应用其内容。
3）IO：请求主库，获取上一次执行过的新的事件，并存放到relaylog
4）SQL：从relaylog中将sql语句翻译给从库执行

二.主从复制原理

主从复制的前提

1）两台或两台以上的数据库实例
2）主库要开启二进制日志
3）主库要有复制用户
4）主库的server_id和从库不同
5）从库需要在开启复制功能前，要获取到主库之前的数据（主库备份，并且记录binlog当时位置）
6）从库在第一次开启主从复制时，时必须获知主库：ip，port，user，password，logfile，pos
7）从库要开启相关线程：IO、SQL
8）从库需要记录复制相关用户信息，还应该记录到上次已经从主库请求到哪个二进制日志
9）从库请求过来的binlog，首先要存下来，并且执行binlog，执行过的信息保存下来

主从复制涉及到的文件和线程

主库：

1）主库binlog：记录主库发生过的修改事件
2）dump thread：给从库传送（TP）二进制日志线程

从库：

1）relay-log（中继日志）：存储所有主库TP过来的binlog事件
2）master.info：存储复制用户信息，上次请求到的主库binlog位置点
3）IO thread：接收主库发来的binlog日志，也是从库请求主库的线程
4）SQL thread：执行主库TP过来的日志

主从复制原理图

大前提条件：做主从复制之前，一定要保证主库和从库之间数据一致性

1）通过change master to语句告诉从库主库的ip，port，user，password，file，pos
2）从库通过start slave命令开启复制必要的IO线程和SQL线程
3）从库通过IO线程拿着change master to用户密码相关信息，连接主库，验证合法性
4）从库连接成功后，会根据binlog的pos问主库，有没有比这个更新的
5）主库接收到从库请求后，比较一下binlog信息，如果有就将最新数据通过dump线程给从库IO线程
6）从库通过IO线程接收到主库发来的binlog事件，存储到TCP/IP缓存中，并返回ACK更新master.info
7）将TCP/IP缓存中的内容存到relay-log中
8）SQL线程读取relay-log.info，读取到上次已经执行过的relay-log位置点，继续执行后续的relay-log日志，执行完成后，更新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 status\G
             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 status\G
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到误删除之前点

  • 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';
  • 2.从库
#准备初始化环境
[root@db04 ~]# /etc/init.d/mysqld  stop
[root@db04 ~]# rm -fr /application/mysql/data/*
[root@db04 ~]# ./mysql_install_db  --user=mysql --basedir=/opt/mysql --datadir=/opt/mysql/data/
[root@db04 ~]# /etc/init.d/mysqld  start

#导库
[root@db04 ~]# zcat /tmp/dbdb.sql  |mysql
#主从
[root@db04 ~]# mysql -uroot -p1
mysql> change master to
        master_host='10.0.0.51',
        master_user='db',
        master_password='123',
        master_log_file='mysql-bin.000001',
        master_log_pos=517,
        master_port=3306,	
        master_delay=3600;
  • 3.主库模拟删库故障
[root@db01 ~]# mysql -uroot -p1
mysql> select count(*) from db1.t1;
+----------+
| count(*) |
+----------+
|      888 |
+----------+
1 row in set (0.00 sec)

mysql> drop database db1;
Query OK, 1 row affected (0.02 sec)
  • 4.从库先关闭sql线程
[root@db04 ~]# mysql
           SQL_Delay: 3600

mysql> select count(*) from db1.t1;
+----------+
| count(*) |
+----------+
|      655 |
+----------+
1 row in set (0.00 sec)

mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.01 sec)
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
  • 5.从库将误删除的主库导出拷贝到主库
[root@db04 data]# mysqldump -B db1>/tmp/db_quan.sql
[root@db04 data]# scp /tmp/db_quan.sql  172.16.1.51:/backup/
  • 6.截取未同步的数据到删库之前的操作.
#获取起点
[root@db04 data]# cat relay-log.info 
7
./db04-relay-bin.000002
283
mysql-bin.000003
44040
3600
0
1


#获取终点
方法一：
[root@db04 data]# mysql
#查看relaylog事件
mysql> show relaylog events in 'db04-relay-bin.000002';
| db04-relay-bin.000002 | 45118 | Query       |         1 |      161795 | drop database db1  
#或者在命令行执行
[root@db04 ~]# mysql -e "show relaylog events in 'db04-relay-bin.000002'";

#方法二：
[root@db04 data]# mysqlbinlog  -d db1 --base64-output=decode-rows -vvv db04-relay-bin.000002 |grep -i -B 5 'drop database'
#191117 13:38:56 server id 1  end_log_pos 88875 CRC32 0xcfd701dd 	Xid = 15750
COMMIT/*!*/;
# 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 </backup/db_quan.sql 
[root@db01 data]# mysql -uroot  -p1 </backup/db_zeng.sql 
mysql> 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)

相关推荐

Redis集群对比:主从复制、哨兵模式、Cluster一文看懂所有优缺点

在分布式系统中，Redis作为高性能的内存数据库，其集群方案的选择直接影响到系统的稳定性、可用性和扩展性。本文将全面对比Redis的三种主流集群方案：主从复制、哨兵模式和Cluster模式，帮助开发者...

redis的主从复制，读写分离，主从切换

当数据量变得庞大的时候，读写分离还是很有必要的。同时避免一个redis服务宕机，导致应用宕机的情况，我们启用sentinel(哨兵)服务，实现主从切换的功能。redis提供了一个master,多个sl...

# Redis 入门到精通(九)-- 主从复制(3)

#Redis入门到精通（九）--主从复制（3）##一、redis主从复制-常见问题（1）###1、伴随着redis系统的运行，master的数据量会越来越大，一旦master重启...

redis - 主从复制（Redis主从复制时序图）

1引言在上一篇文章中，我们了解了Redis两种不同的持久化方式，Redis服务器通过持久化，把Redis内存中持久化到硬盘当中，当Redis宕机时，我们重启Redis服务器时，可以由RDB文件或AO...

# Redis 入门到精通(九)-- 主从复制(2)

#Redis入门到精通（九）--主从复制（2）##一、redis主从复制--数据同步阶段注意事项###1、数据同步阶段master说明1）如果master数据量巨大，数据同步阶段应...

Redis主从复制（redis主从复制主节点挂了）

介绍Redis有两种不同的持久化方式，Redis服务器通过持久化，把Redis内存中持久化到硬盘当中，当Redis宕机时，我们重启Redis服务器时，可以由RDB文件或AOF文件恢复内存中的数据。不过...

深入解析 Redis 集群的主从复制实现方式

在互联网大厂的后端开发领域，Redis作为一款高性能的内存数据库，被广泛应用于缓存、消息队列等场景。而Redis集群中的主从复制机制，更是保障数据安全、实现读写分离以及提升系统性能的关键所在。今...

Redis主从架构详解（redis主从架构高可用如何实现）

Redis主从架构搭建Redis主节点配置创建主节点目录（/opt/redis-master），复制redis.conf到该目录下，redis.conf配置项修改#后台启动daemonizeyes...

抖音“四大包塘战神”:承包了全网的快乐

在抖音钓鱼垂类领域，"包塘战神"军团正掀起一场黑色幽默风暴。空军华、大表坑、李赔光、透心良四位创作者，以承包鱼塘为舞台，用连续翻车的钓鱼直播构筑起流量奇观。当钓鱼佬在抖音集体转型喜剧人...

ORACLE 11G RAC 安装-通过VM配置共享磁盘

简介：在自己的电脑上通过VM软件搭建Oracle11GRAC，通过修改VM的参数文件来实现磁盘共享！目标：搭建RAC环境实现：使用VMwareWorkstation8.0.0+ORACLE...

Linux操作系统安全配置（linux系统安全配置包括）

一、服务相关命令systemctlenable服务名#开机自启动systemctldisable服务名#禁用开机自启动systemctlstop服务名#停止服务systemctls...

关于Linux性能调优中网络I/O的一些笔记

写在前面和小伙伴分享一些Linux网络优化的笔记,内容很浅，可以用作入门博文内容结合《Linux性能优化》读书笔记整理涉及内容包括常用的优化工具(mii-tool,ethtool,ifconfig,i...

从 Sonatype Nexus Repository Manager 迁移到 Artifactory

1.Nexus1.1下载下载链接：https://help.sonatype.com/repomanager3/product-information/download/download-archiv...

Ubuntu20安装zabbix5.0企业监控系统亲测教程

前言示例主机：zabbix10.0.100.10，将安装在UbuntuServer上教程说明：因使用官方教程无法安装成功，所以本教程与官方教程有所不同安装前提：已安装UbuntuServer2...

Linux内核设计与实现—进程管理（linux内核程序设计）

进程进程就是处于执行期的程序(目标码存放在某种存储介质上)。进并不仅仅局限于一段可执行程序代码(Unix称其为代码段，textsection)。通常进程还要包含其他资源，像打开的文件，挂起的信号，...

一周热门
最近发表
标签列表