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线程慢(主从硬件差异比较大)
尽可能的避免主从延时
1sync_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)
posted @   游走在边缘的人  阅读(244)  评论(0编辑  收藏  举报
编辑推荐:
· 聊一聊坑人的 C# MySql.Data SDK
· 使用 .NET Core 实现一个自定义日志记录器
· [杂谈]如何选择:Session 还是 JWT?
· 硬盘空间消失之谜:Linux 服务器存储排查与优化全过程
· JavaScript是按顺序执行的吗?聊聊JavaScript中的变量提升
阅读排行:
· 好消息,在 Visual Studio 中可以免费使用 GitHub Copilot 了!
· 工作中这样用MQ,很香!
· 使用 .NET Core 实现一个自定义日志记录器
· 没事别想不开去创业!
· 聊一聊坑人的 C# MySql.Data SDK
点击右上角即可分享
微信分享提示