mysql主从复制
mysql主从复制
1.mysql复制简介
复制是 MySQL的一项功能,允许服务器将更改从一个实例复制到另一个实例。
• 主服务器将所有数据和结构更改记录到二进制日志中。
• 从属服务器从主服务器请求该二进制日志并在本地应用其内容。
2.mysql主从复制工作原理
1.从库,从IO线程,读取master.info中的信息,获取数据库的连接参数(user\passwd\ip\port),与上次请求过的binlog的位置(例子:mysql-bin.000003,position=640)
2.IO线程连接主库,拿着binlog位置信息,问主库有没有比这个更新的二进制日志
3.主库查询二进制日志,对比从库发过来的位置信息,如果有新的二进制日志,会通过dump thread发给从库
4.从库通过IO线程,接收到主库发过来的二进制日志,会存储到TCP/IP缓存中,并返回ACK确认给主库,主库收到ACK之后就认变复制完成,继续其它工作
5.从库会更新master.info文件,二进制日志更新到最新的位置
6.从库IO线程会将TCP/IP缓存中的日志,存储到relaylog中继日志文件中
7.从库SQL线程,读取relaylog.info,获取到上次执行到的relaylog日志位置,以这个位置为起点,往后继续执行中继日志
8.从库SQL线程执行完所有relaylog后,会更新relaylog.inof信息为最新位置信息
9.到此为之,一次完整的复制完成
3.mysql主从复制搭建
1、主从复制的前提
1.1 两台以上mysql实例
多台物理机
多个mysql实例
1.2 主库要开启二进制日志
1.3 主库要提供复制相关的用户
replication slave,一个比较特殊的权限
grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
1.4 从库需要将和主库相差的数据,进行追加
一般情况下可以人为备份主库数据,恢复到从库上
1.5 从应该从恢复之后的时间点,开始自动从主库获取新的二进制日志开始应用
我们需要人为告诉从库,从哪开始自动开始复制二进制日志(file+position),另外还需要告诉从库user,passwd,port,ip
change master to
2、复制中的线程及文件
2.1、主库
Dump(IO) thread:在复制过程中,主库发送二进制日志的线程
2.2、从库
IO thread:向主库请求二进制日志,并且接受二进制日志的线程
SQL thread:执行请求过来的二进制的线程
2.3、主库
binlog文件:主库的二进制日志
2.4、从库
relaylog:中继日志,存储请求过来的二进制日志
master.info:
1、从库连接主库的重要参数(user,passwd,ip,port)
2、上次获取过的主库二进制日志的位置
relay-log.info
存储从库SQL线程已经执行过的relaylog日志位置
3.1 mysql主从复制搭建过程
准备环境
两个以上节点(多实例)
3307:master
3308:slave1
3309:slave2
第一步:各个节点配置,skip-name-resolve:关闭数据库的自动域名解析
[root@db02 3307]# cat my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3307
server-id=3307
port=3307
log-bin=/data/3307/mysql-bin
binlog_format=row
socket=/data/3307/mysql.sock
log-error=/data/3307/mysql.log
skip-name-resolve
[root@db02 data]# cat 3308/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3308
server-id=3308
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
skip-name-resolve
[root@db02 data]# cat 3309/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3309
server-id=3309
port=3309
socket=/data/3309/mysql.sock
log-error=/data/3309/mysql.log
skip-name-resolve
第二步:启动多实例
mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &
第三步:主库创建复制账户
连接到主库:
mysql -S /data/3307/mysql.sock
grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
第四步:从库数据的追加
(1)不需要追加的情况
主和从同时搭建的新环境,就不需要备份主库数据,恢复到从库了,直接从第一个binlog(mysql-bin.000001)的开头位置(120)
(2)如果主库已经工作了很长时间了,我们一般需要备份主库数据,恢复到从库,然后从库从备份的时间点起自动进行复制
以下针对第二种方式:
备份主库
mysqldump -S /data/3307/mysql.sock -A -R --triggers --master-data=2 --single-transaction >/backup/full.sql
sed -n '22p' /backup/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.0000010', MASTER_LOG_POS=325
恢复到从库
mysql -S /data/3308/mysql.sock
mysql> set sql_log_bin=0;
mysql> source /backup/full.sql
第五步:从库开启主库
mysql -S /data/3308/mysql.sock #连接到3308
CHANGE MASTER TO
MASTER_HOST='10.0.0.52', #主库数据库地址
MASTER_USER='repl', #主库授权的账号
MASTER_PASSWORD='123', #数据库密码
MASTER_PORT=3307, #主库的端口
MASTER_LOG_FILE='mysql-bin.0000010', #主库的binlog文件
MASTER_LOG_POS=325; #binlog位置
开启主从(开启IO和SQL线程):
start slave;
查看主从状态:
show slave status\G
查看主从状态信息介绍
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
第六步:测试,在主数据库中新建数据库,在从库查看状态
create databases oldoby;
3.2 mysql主从复制故障解决方法
3.2.1 IO线程故障
1、主库连接不上
user、password、port、ip 错误
解决方案:
stop slave;
reset slave all;
change master to
start slave;
防火墙
网络不通
skip-name-resolve
stop slave;
start slave;
2、主库二进制日志丢失或损坏
解决方案:
stop slave;
reset slave all;
重新备份恢复
change master to
start slave;
3.2.2 SQL线程故障
执行relaylog日志新事件
1、删除、修改对象的操作时,没有这个对象
2、创建对象时,对象已存在
3、主键冲突
从库做写入操作,会导致以上问题出现
解决方法:
stop slave;
set global sql_slave_skip_counter = 1;
start slave;
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
但是,以上操作有时是有风险的,最安全的做法就是重新构建主从。
怎么预防以上问题?
从库加入配置文件
set global read_only=1;
vim /etc/my.cnf
read_only=1 ---->只能控制普通用户
3.2.3 主从异常——主从延时过长
show slave status \G
Seconds_Behind_Master:0 #查看同步时间
默认的主从复制机制是异步的一个过程。
主库原因:
1、主库做修改操作之后,才会记录二进制日志。
sync_binlog=0/1
1:表示:每次事务commit,刷新binlog到磁盘
0:系统决定binlog什时候刷新到磁盘
2、主库的压力特别大(大事务、多事务)
3、从库数量多,导致dump线程繁忙
从库原因:
1、relay-log写入慢
2、SQL线程慢(主从硬件差异比较大)
尽可能的避免主从延时
1、sync_binlog=1
2、大事务拆成小事务,多事务进行分离
3、使用多级主从,分库分表架构
4、将binlog放到ssd或者flash上,高性能存储
5、将relay放到ssd或者flash上
6、尽量选择和主库一致硬件和配置
4.主从复制高级功能——半同步复制
半同步:保证主从数据一致性的问题,安全的考虑
5.5 出现的概念,但是不建议使用,性能太差
5.6以后出现group commit 组提交功能,来提升开启版同步复制的性能
5.7 增强半同步复制的新特性:after sync;
加载插件
主:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
从:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
查看是否加载成功:
show plugins;
启动:
主:
SET GLOBAL rpl_semi_sync_master_enabled = 1;
从:
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
重启从库上的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
查看是否在运行
主:
show status like 'Rpl_semi_sync_master_status';
从:
show status like 'Rpl_semi_sync_slave_status';
查看同步时间
show variables like '%rpl_sem%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
默认情况先,到达10秒钟还没有ack,主从关系自动切换为普通复制
如果是1主多从的半同步复制,只要有一台落地relaylog,返回ack,这次半同步就完成了。
5.主从复制高级功能——延时从库
为了防止逻辑损坏会专门找一个节点,配置成延时节点,一般情况下这个节点会被用备份
设置延时,一般的情况下会设置3-6小时个延时时间,为会防止逻辑损坏
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_DELAY = 120; #设置延时时间
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
SQL_Delay: 120
取消延时:
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 0;
mysql> start slave;
6.主从复制高级功能——复制过滤
主要是为了不复制一些不需要的库或表到从库
主库方面控制(不建议使用):
白名单:只记录白名单中列出的库的二进制日志
binlog-do-db
黑名单:不记录黑名单列出的库的二进制日志
binlog-ignore-db
从库方面控制:
白名单:只执行白名单中列出的库或者表的中继日志
--replicate-do-db=test #只复制test库
--replicate-do-table=test.t1 #只复制test库下面的t1表
--replicate-wild-do-table=test.x* #只复制test库下面以x开头的表
黑名单:不执行黑名单中列出的库或者表的中继日志
--replicate-ignore-db
--replicate-ignore-table
--replicate-wild-ignore-table
例子:只同步oldboy库到从库
在3308中的my.cnf配置文件中加入
replicate-do-db=oldboy
然后重启数据库
mysqladmin -S /data/3308/mysql.sock shutdown
mysql -S /data/3308/mysql.sock
在主库上建数据库
mysql> create database oldboy1;
在从库上查看,发现没有同步过来
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| performance_schema |
| test |
+--------------------+
在主库上的oldboy进行添加数据
mysql> use oldboy
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | li |
| 2 | aa |
| 3 | a1a |
+------+------+
3 rows in set (0.00 sec)
mysql> insert into t1 values(4,'12');
Query OK, 1 row affected (0.00 sec)
在从库上查看,发现已经同步过来了
mysql> use oldboy
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | li |
| 2 | aa |
| 3 | a1a |
| 4 | 12 |
+------+------+
4 rows in set (0.00 sec)
7.主从复制新特性-GTID复制
7.1 /etc/my.cnf中添加配置
gtid-mo --启用gtid类型,否则就是普通的复制架构
enforce-gtid-consistency=true --强制GTID的一致性
log-slave-updates=1 --slave更新是否记入日志
7.2 GTID搭建过程
三台机器:
db01:172.16.1.51(master)
db02:172.16.1.52(slave01)
db02:172.16.1.53(slave02)
7.2.1 编写配置文件
1.编写master主机配置文件(172.16.1.51)
[root@db01 ~]# cat /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/application/mysql/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=51
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/application/mysql/tmp/mysql.sock
[root@db01 ~]#
2.编写slave1主机配置文件(172.16.1.52)
[root@db02 ~]# cat /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
server_id=52
socket=/application/mysql/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/application/mysql/tmp/mysql.sock
3.编写slave1主机配置文件(172.16.1.53)
[root@db03 ~]# vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
server_id=53
socket=/application/mysql/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/application/mysql/tmp/mysql.sock
4.重启数据库
/etc/init.d/mysqld restart
7.2.2 配置复制环境
1.在master主库上配置(51)
grant replication slave on *.* to repl@'172.16.1.%' identified by '123';
2.在从库上配置(51\52)
change master to master_host='172.16.1.51',master_user='repl',master_password='123' ,MASTER_AUTO_POSITION=1;
3.启动slave
start slave;
4.查看从库状态
show slave status\G
7.2.3 测试GTID环境
1.在主库上新建数据库
create database oldboy;
2.在从库上查看有没有复制成功
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)
【推荐】FFA 2024大会视频回放:Apache Flink 的过去、现在及未来
【推荐】中国电信天翼云云端翼购节,2核2G云服务器一口价38元/年
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 聊一聊坑人的 C# MySql.Data SDK
· 使用 .NET Core 实现一个自定义日志记录器
· [杂谈]如何选择:Session 还是 JWT?
· 硬盘空间消失之谜:Linux 服务器存储排查与优化全过程
· JavaScript是按顺序执行的吗?聊聊JavaScript中的变量提升
· 好消息,在 Visual Studio 中可以免费使用 GitHub Copilot 了!
· 工作中这样用MQ,很香!
· 使用 .NET Core 实现一个自定义日志记录器
· 没事别想不开去创业!
· 聊一聊坑人的 C# MySql.Data SDK