Mysql主从复制
2019-11-19

1.主从复制简介

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

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

2.主从复制原理

1.__主从复制的前提__

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

2.__从复制涉及到的文件和线程__

++主库:++

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

3.__主从复制搭建实战__

主库操作

修改主库的配置文件

vim /etc/my.cnf#在[mysqld]标签下开启binlog和server_idlog_bin=mysql-binserver_id =1

重启数据库(两种方式)后进入

[root@db01 ~]# /etc/init.d/mysqld restart [root@db01 ~]# systemctl restart mysqld

查看日志状态

mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000003 | 120 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

创建一个复制用户

mysql> grant replication slave on *.* to rep@"10.0.0.%" identified by "123";Query OK, 0 rows affected (0.00 sec)mysql> show master status; #binlog pos 变成了324+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000003 | 324 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

从库操作

修改从库的配置文件

vim /etc/my.cnf#在[mysqld]标签下开启server_id 从库server_id不可与主库相同,从库之间可以一致server_id =2

重启数据库后进入执行change master to 语句

mysql> change master tomaster_host="10.0.0.51", #主库ipmaster_user="rep", #主从复制用户master_password="123", #主从复制用户密码master_log_file="mysql-bin.000003", #binlogmaster_log_pos=324, #binlog 起始点master_port=3306; #数据库端口

开启slave

mysql> start slave;

查看slave状态(yes开启成功)

mysql> show slave statusG

4.验证主从复制

查看主库上的库

mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test |+--------------------+4 rows in set (0.00 sec)

创建一个新库zeq

mysql> create database zeq;Query OK, 1 row affected (0.00 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test || zeq |+--------------------+5 rows in set (0.00 sec)

查看从库

mysql> show databases; #从库上已经复制过来了+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test || zeq |+--------------------+5 rows in set (0.01 sec)

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

IO线程

++连接主库++

1)user password ip port2)网络:不通,延时高,防火墙

++请求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;

处理方法二:

#编辑配置文件vim /etc/my.cnf#在[mysqld]标签下添加以下参数slave-skip-errors=1032,1062,1007

但是以上操作都是有风险存在的

处理方法三:

1)重新备份数据库,恢复到从库2)给从库设置为只读

#在命令行临时设置mysql> set global read_only=1;#在配置文件中永久生效/etc/my.cnfread_only=1

6.延时从库

企业中一般会延时3-6小时

延时从库配置方法从库操作

#停止主从mysql>stop slave;#设置延时为180秒mysql>change master to master_delay = 180;#开启主从mysql>start slave;#查看状态mysql> show slave status GSQL_Delay: 603.延时从库停止方法#停止主从mysql> stop slave;#设置延时为0mysql> CHANGE MASTER TO MASTER_DELAY = 0;#开启主从mysql> start slave;

关闭半同步:

主库执行SET GLOBAL rpl_semi_sync_master_enabled = 0;SET GLOBAL rpl_semi_sync_master_wait_no_slave = 0;从库执行:SET GLOBAL rpl_semi_sync_slave_enabled = 0;