当前位置:首页 > Mysql > 正文内容

MySQL主从复制

5年前 (2019-09-08)Mysql405

主从复制简介

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:qmf123

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


3:从库操作:

#修改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 -poldboy123

#执行change master to 语句
mysql> change master to
-> master_host='10.0.0.51',
-> master_user='rep',
-> master_password='oldboy123',
-> master_auto_position=1;


四.主从复制基本故障处理


IO线程


连接主库

1:user password ip port

2:网络:不通,延时高,防火墙


请求binlog

1: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


五.延时从库


普通的主从复制可能存在不足

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



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线程

mysql> stop slave sql_thread;


2:截取relaylog到误删除之前点

relay-log.info 获取到上次运行到的位置点,作为恢复起点

分析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;


六.半同步复制

从MYSQL5.5开始,支持半自动复制。之前版本的MySQL Replication都是异步(asynchronous:的,主库在执行完一些事务后,是不会管备库的进度的。如果备库不幸落后,而更不幸的是主库此时又出现Crash(例如宕机:,这时备库中的数据就是不完整的。简而言之,在主库发生故障的时候,我们无法使用备库来继续提供数据一致的服务了。


半同步复制(Semi synchronous Replication:则一定程度上保证提交的事务已经传给了至少一个备库。

出发点是保证主从数据一致性问题,安全的考虑。


5.5 出现概念,但是不建议使用,性能太差

5.6出现group commit 组提交功能,来提升开启半同步复制的性能

5.7更加完善了,在group commit基础上出现了MGR

5.7的增强半同步复制的新特性:after commit; after sync;


半同步复制开启方法


1:安装(主库)

#登录数据库
[root@db01 ~]# mysql -uroot -poldboy123

#查看是否有动态支持
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 -poldboy123

#安装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就会恢复到异步复制。


测试半同步


#创建两个数据库,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)

注:不难发现,在查询半同步状态是,开启半同步,查询会有延迟时间,关闭之后则没有


七.过滤复制


主库:


白名单:只记录白名单中列出的库的二进制日志

binlog-do-db


黑名单:不记录黑名单列出的库的二进制日志

binlog-ignore-db


从库:


白名单:只执行白名单中列出的库或者表的中继日志

--replicate-do-db=test

--replicate-do-table=test.t1

--replicate-wild-do-table=test.t2


黑名单:不执行黑名单中列出的库或者表的中继日志

--replicate-ignore-db

--replicate-ignore-table

--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;


“MySQL主从复制” 的相关文章

MySQL用户、权限简单管理

MySQL管理MySQL用户的作用:1、登陆MySQL数据库2、管理数据库对象MySQL用户管理:1、创建用户:create user2、删除用户:delete user drop user3、修改用户:update用户的定义:1、username@’主机域’2、主机域:可以理解为是MySQL登陆的...

mysql数据库SQL语句及字符集定义

mysql数据库SQL语句及字符集定义

SQL简单语句用于数据库的连接管理:mysql命令行管理工具:mysqladmin备份数据库和表的内容:mysqldumpMySQL接口自带的命令\h 或 help 或?    查看帮助 \G   ...

MySQL索引管理及执行计划

MySQL索引管理及执行计划

一.索引介绍 1.索引1)索引就好比一本书的目录,它能让你更快的找到自己想要的内容。2)让获取的数据更有目的性,从而提高数据库检索数据的性能。2.索引类型1.BTREE:B+树索引2.HASH:HASH索引3.FULLTEXT:全文索引4.RTREE:R树索引3.索引管理索引建立在表的列上...

MySQL日志管理

MySQL日志管理

一.MySQL日志简介二.错误日志作用:记录mysql数据库的一般状态信息及报错信息,是我们对于数据库常规报错处理的常用日志。默认位置:$MYSQL_HOME/data/开启方式:(MySQL安装完后默认开启)#编辑配置文件 [root@db01 ~]# vim /...

MySQL集群——MHA高可用及读写分离

MySQL集群——MHA高可用及读写分离

一.MHA简介MHA能够在较短的时间内实现自动故障检测和故障转移,通常在10-30秒以内;在复制框架中,MHA能够很好地解决复制过程中的数据一致性问题,由于不需要在现有的replication中添加额外的服务器,仅需要一个manager节点,而一个Manager能管理多套复制,所以能大大地节约服务器...