MySQL主从复制

2020-01-19 分类:数据库相关 阅读(60) 评论(0)

主从复制简介

1)高可用
2)辅助备份
3)分担负载

复制是 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

IP:10.0.0.51
Port:3306
User:rep
Password:nfsnobody123
logFile:mysql-bin.000002
Pos:120

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 -p123
#创建rep用户
mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by 'nfsnobody123';

从库的操作

修改配置文件

#修改db02配置文件
[root@db02 ~]# vim /etc/my.cnf
#在mysqld标签下配置
[mysqld]
#主库server-id为1,从库不等于1
server_id =5
#重启mysql
[root@db02 ~]# /etc/init.d/mysqld restart
#记录主库binlog及位置点
mysql> show master status;
#登陆数据库
[root@db02 ~]# mysql -uroot -pnfsnobody123
#执行change master to 语句
mysql> change master to
-> master_host='10.0.0.51',
-> master_user='rep',
-> master_password='nfsnobody123',
-> master_auto_position=1;

主从复制基本故障处理

IO线程连接不上主库:

1)IP 网络  检测方式:ping IP
2)port端口 检测方式:telnet IP port
3)用户名或者密码的问题
检测方式:mysql -urep -p123 -h172.16.1.51
连接主库
1)user password ip port
2)网络:不通,延时高,防火墙

请求binlog

binlog不存在或者损坏

更新relay-log和master.info
> ```
SQL线程
1)relay-log出现问题
2)从库做写入了


操作对象已存在(create) 操作对象不存在(insert update delete drop truncate alter) 约束问题、数据类型、列属性 **处理方法一:**

临时停止同步

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


<a name="toc_3"></a> # 延时从库 **普通的主从复制可能存在不足** 1)逻辑损坏怎么办? 2)不能保证主库的操作,从库一定能做 3)高可用?自动failover? 4)过滤复制 **企业中一般会延时3-6小时** <a name="3edskx"></a> ## **延时从库配置方法**

停止主从

mysql>stop slave;

设置延时为180秒

mysql>CHANGE MASTER TO MASTER_DELAY = 180;

开启主从

mysql>start slave;

查看状态

mysql> show slave status \G
SQL_Delay: 60
3.延时从库停止方法

停止主从

mysql> stop slave;

设置延时为0

mysql> CHANGE MASTER TO MASTER_DELAY = 0;

开启主从

mysql> start slave;


<a name="rf75wr"></a> ## 故障处理 总数据量级500G,正常备份去恢复需要1.5-2小时 1)配置延时3600秒

mysql>CHANGE MASTER TO MASTER_DELAY = 3600;


2)主库

drop database db;

**3)如何利用延时从库,恢复数据?**
&gt; 提示:
1、从库relaylog存放在datadir目录下
2、mysqlbinlog 可以截取relaylog内容
3、show relay log events in 'db01-relay-bin.000001';

**处理的思路:**
1)停止SQL线程

mysql> stop slave sql_thread;

2)截取relaylog到误删除之前点
&gt; relay-log.info 获取到上次运行到的位置点,作为恢复起点
&gt; 分析relay-log的文件内容,获取到误删除之前position

**模拟故障处:**
1)关闭延时

mysql -S /data/3308/mysql.sock
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 0;
mysql> start slave;

2)模拟数据

mysql -S /data/3307/mysql.sock
source /root/world.sql
use world;
create table c1 select * from city;
create table c2 select * from city;

3)开启从库延时5分钟

mysql -S /data/3308/mysql.sock
show slave status \G

mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;
mysql>start slave;

mysql -S /data/3307/mysql.sock
use world;
create table c3 select * from city;
create table c4 select * from city;

4)破坏,模拟删库故障。(以下步骤在5分钟内操作完成。)

mysql -S /data/3307/mysql.sock
drop database world;

5)从库,关闭SQL线程

mysql -S /data/3308/mysql.sock
stop slave sql_thread;

6)截取relay-log

起点:
cd /data/3308/data/
cat relay-log.info
./db01-relay-bin.000002
283
终点:
mysql -S /data/3308/mysql.sock
show relaylog events in 'db01-relay-bin.000002'
db01-relay-bin.000002 | 268047
mysqlbinlog --start-position=283 --stop-position=268047 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql


**恢复relay.sql** 1)取消从库身份

mysql> stop slave;
mysql> reset slave all;

2)恢复数据

mysql> set sql_log_bin=0;
mysql> source /tmp/relay.sql
mysql> use world
mysql> show tables;

<a name="toc_4"></a>
# 半同步复制
<a name="dwyznr"></a>
## 半同步复制发展
&gt; 5.5&nbsp;出现概念,但是不建议使用,性能太差
5.6出现group&nbsp;commit&nbsp;组提交功能,来提升开启半同步复制的性能
5.7更加完善了,在group&nbsp;commit基础上出现了MGR
5.7的增强半同步复制的新特性:after&nbsp;commit;&nbsp;after&nbsp;sync;

从MYSQL5.5开始,支持半自动复制。
半同步复制(Semi synchronous Replication)则一定程度上保证提交的事务已经传给了至少一个备库。

<a name="8eflng"></a>
## **半同步复制开启方法**
1)安装(主库)

登录数据库

[root@db01 ~]# mysql -uroot -pnfsnobody123

查看是否有动态支持

mysql> show global variables like 'have_dynamic_loading';

安装自带插件

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so';

启动插件

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

设置超时

mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;

修改配置文件

[root@db01 ~]# vim /etc/my.cnf

在[mysqld]标签下添加如下内容(不用重启库)

[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
检查安装:
mysql> show variables like'rpl%';
mysql> show global status like 'rpl_semi%';

2)安装(从库)

登录数据库

[root@mysql-db02 ~]# mysql -uroot -pnfsnobody123

安装slave半同步插件

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';

启动插件

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

重启io线程使其生效

mysql> stop slave io_thread;
mysql> start slave io_thread;

编辑配置文件(不需要重启数据库)

[root@mysql-db02 ~]# vim /etc/my.cnf

在[mysqld]标签下添加如下内容

[mysqld]
rpl_semi_sync_slave_enabled =1

**相关参数说明**

rpl_semi_sync_master_timeout=milliseconds
设置此参数值(ms),为了防止半同步复制在没有收到确认的情况下发生堵塞,如果Master在超时之前没有收到任何确认,将恢复到正常的异步复制,并继续执行没有半同步的复制操作。

rpl_semi_sync_master_wait_no_slave={ON|OFF}
如果一个事务被提交,但Master没有任何Slave的连接,这时不可能将事务发送到其它地方保护起来。默认情况下,Master会在时间限制范围内继续等待Slave的连接,并确认该事务已经被正确的写到磁盘上。
可以使用此参数选项关闭这种行为,在这种情况下,如果没有Slave连接,Master就会恢复到异步复制。

<a name="l3e6aq"></a>
## **测试半同步**

创建两个数据库,test1和test2

mysql> create database test1;
Query OK, 1 row affected (0.04 sec)
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)

查看复制状态

mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 768 |
| Rpl_semi_sync_master_net_wait_time | 1497 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 884 |
| Rpl_semi_sync_master_tx_wait_time | 1769 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |

此行显示2,表示刚才创建的两个库执行了半同步

| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.06 sec)

从库查看

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test1 |
| test2 |
+--------------------+

关闭半同步(1:开启 0:关闭)

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 0;

查看半同步状态

mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 768 |
| Rpl_semi_sync_master_net_wait_time | 1497 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | OFF | #状态为关闭
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 884 |
| Rpl_semi_sync_master_tx_wait_time | 1769 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

再一次创建两个库

mysql> create database test3;
Query OK, 1 row affected (0.00 sec)
mysql> create database test4;
Query OK, 1 row affected (0.00 sec)

再一次查看半同步状态

mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 768 |
| Rpl_semi_sync_master_net_wait_time | 1497 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 884 |
| Rpl_semi_sync_master_tx_wait_time | 1769 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |

此行还是显示2,则证明,刚才的那两条并没有执行半同步否则应该是4

| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
注:不难发现,在查询半同步状态是,开启半同步,查询会有延迟时间,关闭之后则没有

<a name="toc_5"></a>
# 过滤复制
**主库:**
&gt; 白名单:只记录白名单中列出的库的二进制日志
&gt; binlog-do-db

&gt; 黑名单:不记录黑名单列出的库的二进制日志
&gt; binlog-ignore-db

**从库:**
&gt; 白名单:只执行白名单中列出的库或者表的中继日志
&gt; --replicate-do-db=test
&gt; --replicate-do-table=test.t1
&gt; --replicate-wild-do-table=test.t2
&gt; 黑名单:不执行黑名单中列出的库或者表的中继日志
&gt; --replicate-ignore-db
&gt; --replicate-ignore-table
&gt; --replicate-wild-ignore-table

**复制过滤配置:**

[root@db01 data]# vim /data/3307/my.cnf

在[mysqld]标签下添加

replicate-do-db=world

关闭MySQL

mysqladmin -S /data/3307/mysql.sock shutdown

启动MySQL

mysqld_safe --defaults-file=/data/3307/my.cnf &

**测试复制过滤:**

第一次测试:
1)主库:

[root@db02 ~]# mysql -uroot -p123 -S /data/3308/mysql.sock
mysql> use world
mysql> create table t1(id int);

2)从库查看结果:

[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock
mysql> use world
mysql> show tables;


第二次测试: 1)主库:

[root@db02 ~]# mysql -uroot -p123 -S /data/3308/mysql.sock
mysql> use test
mysql> create table tb1(id int);

2)从库查看结果:

[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock
mysql> use test
mysql> show tables;
```

标签:

您可能也喜欢:

Redis主从复制及高可用

Redis主从复制 Redis复制功能介绍 1)使用异步复制。 2)一个主服务器可以有多个从服务器。 3)从服务器也可以有自己的从服务器。 4)复制功能不会阻塞主服务器。 5)可以通过复制功能来让主服务器免于执行持久化操作,由从服务器去执行持久化操作即可。 Redis复制功能介绍(重点了解) 1...

more

Redis事务、锁、管理命令

事务介绍 Redis的事务与关系型数据库中的事务区别 1)在MySQL中讲过的事务,具有A、C、I、D四个特性 Atomic(原子性) > 所有语句作为一个单元全部成功执行或全部取消。 Consistent(一致性) > 如果数据库在事务开始时处于一致状态,则在执行该。
事务期间将保...

more

Redis消息队列

生产消费模型 什么是消息队列 在生活中,其实有很多的例子,都类似消息队列。 比如:工厂生产出来的面包,交给超市,商场来出售,客户通过超市,商场来买面包,客户不会针对某一个工厂去选择,只管从超市买出来,工厂也不会管是哪一个客户买了面包,只管生产出来之后,交给超市,商场来处理。 消息队列(Messa...

more

欢迎新朋友你的到来!
还没有人抢沙发呢~
昵称
邮箱
网站

切换注册

登录

忘记密码 ?

切换登录

注册