MySQL主从复制-摩杜云开发者社区

主从复制的原因

1）备份（延时从库）（正常起不到备份作用，主库删除，从库也被删除，用延时从库，会存在时间差，主库删除，从库还能保留一段时间，恢复的黄金时间就是在这段时间差里）

2）集群。解决数据库单点故障

3）分担主库压力

MySQL传统主从复制

# 主库
## 修改主库配置文件（必须开启binlog，server_id不能是0）
vim /etc/my.cnf
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
log-bin=mysql-bin
server_id=1

# 从库
## 修改从库配置文件（server_id必须大于主库，可以相同，binlog可以开）
vim /etc/my.cnf
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
server_id=2

[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
server_id=2

[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
server_id=2

# 主库创建主从复制用户
mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by '123';

# 查看主库binlog信息
mysql> show master status;  （如果这条命令看不到信息是因为没开binlog(配置文件没指定)，还有记得重启，检查端口）
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      445 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

# 从库连接数据库执行change master语句（告诉从库，主库的信息，即告诉从库去连接谁）
mysql> change master to
	   master_host='172.16.1.51',
	   master_user='rep',
 	   master_password='123',
	   master_log_file='mysql-bin.000004',
	   master_log_pos=445,
	   master_port=3306;
（如果change master里面有内容写错了,slave开了就先stop slave,然后reset slave all,再重新change master再开启slave）
   
# 从库开启主从复制slave
mysql> start slave;

# 查看主从复制状态（检查从库是否有两个Yes）
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.51
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 445
               Relay_Log_File: db04-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

----！！！注意-----------------------
1）即使从库开启了binlog，从库写入数据，也不会更新binlog
2）server_id就是一个标识符，用来区分逐鹿和从库，从库server_id和主库不相同即可，从库之间可以相同
3）主库需要有主从复制用户，从库不需要创建

主从复制原理

主从复制的前提

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：oldboy123 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

主从复制基本故障

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.51
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 445
               Relay_Log_File: db04-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes   (如果这两有不是Yes的就说明有故障)

IO线程故障

# IO线程故障一般是因为和主库建立连接时出现问题（change master 时某个写错了）
1）## IP
在从库上ping主库IP
ping 172.16.1.51

2）## 端口
从库上telnet主库端口
telnet 172.16.1.51 3306

3）## 用户名和密码错误
在从库上登陆主库
mysql -urep -p123 -h172.16.1.51

4）## 方向解析报错
ERROR 1045 (28000): Access denied for user 'root'@'db01' (using password: YES)

配置文件里加入跳过反向解析
vim /etc/my.cnf
[mysqld]
skip_name_resolve

change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000001',
master_log_pos=616,
master_port=3306;

5）## server_id相同，导致IO无法连接
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different
6）## UUID相同，导致IO线程无法连接（比如安装好一台MySQL之后，其他机器克隆此台）
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
7）## 位置点大于binlog大小，导致IO线程链接不上
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:'Client requested master to start replication from position > file size'

SQL故障

主库没有从库有的数据

# 主库没有从库有的数据（原因，在从库写入数据）
Last_Error: Error 'Can't create database 'prod'; database exists' on query. Default database: 'prod'. Query: 'create database prod'

-# 解决方案一：
# 从库临时停止同步（不建议使用）
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,1146
（这些数字是报错show slave status\G可查看到）

-# 解决方案三：
# 做完主从复制后，给从库设置为只读（临时的，重启会消失）
mysql> set global read_only=1;

# 修改从库配置文件
vim /etc/my.cnf
在[mysqld]标签下添加read_only=1 （重启才能生效，实际生产中不可以轻易停库，所以必须结合上面的临时设置）

主库有从库没有的数据

# 主库有从库没有的数据（比如有两台从库，又加了一台，那么这台新加的从库的数据就不全，主库有，从库没有）
Last_Error: Error executing row event: 'Table 'prod5.prod5' doesn't exist'

-# 解决方案一：
# 从库临时停止同步（不建议使用）
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,1146
（这些数字是报错show slave status\G可查看到）

-# 解决方案三：
# 1.给主库做全备（实际生产环境用打点备份）
mysqldump -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full.sql.gz

# 2.将全备恢复到新添加的从库
[root@db01 ~]# scp /tmp/full.sql.gz 172.16.1.52:/root
[root@db02 ~]# zcat /root/full.sql.gz |mysql

# 3.查看全备位置点
[root@db02 ~]# zcat /root/full.sql.gz |head -25

# 4.去从库执行change master(位置点根据上一步改)
mysql> CHANGE MASTER TO
       MASTER_HOST='172.16.1.51',
	   MASTER_USER='rep',
	   MASTER_PASSWORD='123',
	   MASTER_LOG_FILE='mysql-bin.000001', 
	   MASTER_LOG_POS=33854;
	   
	  
# 从库开启主从复制slave
mysql> start slave;

# 查看主从复制状态（检查从库是否有两个Yes）
mysql> show slave status\G

从库设置为只读存在问题

mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+

## 即便设置了read_only，root用户也有权限，所以必须创建普通用户，却不能给all权限
（能给的权限：insert update select （delete权限最好不给））

## 设置完只读，root用户不受影响，或者all权限用户也不受影响

## 设置完只读后，千万不要使用root用户或all权限的用户在从库中执行SQL语句，所有操作都要在主库上执行

基于GTID主从复制（MySQL5.7）

# 主库
## 创建主从复制用户
grant replication slave on *.* to rep@'%' identified by '123';

## 修改配置文件开启gtid mode
-# 临时开启
set global gtid_mode=ON;
-#永久开启（记得重启）
vim /etc/my.cnf
[mysqld]
gtid_mode=ON                #配置文件这里=ON还需要依赖，如果=1，则不用?
enforce_gtid_consistency

## 重启数据库
systemctl restart mysqld


# 从库
## 修改配置文件开启gtid mode（和binlog 不确定要不要开？应该不开）
-# 临时开启
set global gtid_mode=ON;
-#永久开启（记得重启）
vim /etc/my.cnf
[mysqld]
gtid_mode=ON                 #配置文件这里=ON则不用，如果=1，还需要依赖?
enforce_gtid_consistency
#log_bin=mysql-bin （?）

## 重启数据库
systemctl restart mysqld

## 执行change master to
change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_port=3306,
master_auto_position=1;

## 打开
start slave;

# 检查主库从库是否都开启了gtid mode
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+

# 是否有连个Yes
show slave status\G

--------------------------------------------------------------
【如果想取消从库身份就先进入数据库stop slave;停掉，再取消从库身份reset slave all;，然后重新打开start slave即可，（如果想修改从库信息就先进入数据库stop slave;停掉，再reset slave;，再重新change master to，然后重新打开start slave即可，例root@localhost [(none)] >change master to master_delay=600;）】
---------------------------------------------------------------

MySQL主从复制_MySQL主从复制

# 事务提交号Executed_Gtid_Set
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
（如果提交了事务，则会显示事务提交号）
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000001 |      154 |              |                  | 1b35c3c0-31ed-11ee-94e9-000c291dc4f5:1 |
+------------------+----------+--------------+------------------+----------------------------------------+


# GITD
888a0451-3265-11ee-a9a7-000c2923970d:1-3(事务提交号)
888a0451-3265-11ee-a9a7-000c2923970d(主库的UUID)

[root@db01 ~]# cat /app/mysql/data/auto.cnf
[auto]
server-uuid=888a0451-3265-11ee-a9a7-000c2923970d

GTID是由 主库UUID + 事务提交号txid
GTID(Global Transaction ID)全局事务标识符：是一个唯一的标识符，它创建并与源服务器（主）上提交的每个事务相关联。
此标识符不仅对其发起的服务器是唯一的，而且在给定复制设置中的所有服务器上都是唯一的。 所有交易和所有GTID之间都有1对1的映射。
GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。
下面是一个GTID的具体形式：
3E11FA47-71CA-11E1-9E33-C80AA9429562:23

# GTID新特性
(1).支持多线程复制:事实上是针对每个database开启相应的独立线程,即每个库有一个单独的(sql thread).
(2).支持启用GTID,在配置主从复制,传统的方式里,你需要找到binlog和POS点,然后change master to指向.
在mysql5.6里,无须再知道binlog和POS点,只需要知道master的IP/端口/账号密码即可,因为同步复制是自动的,MySQL通过内部机制GTID自动找点同步.
(3).基于Row复制只保存改变的列,大大节省Disk Space/Network resources和Memory usage.
(4).支持把Master 和Slave的相关信息记录在Table中原来是记录在文件里,记录在表里,增强可用性
(5).支持延迟复制

# log-slave-update参数作用
用法：
1）MySQL5.6中做GTID复制
2）级联复制
3）双主+keepalived

-----------------------------------------------------------------------------------------------------
这个报错：[ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires --log-bin and --log-slave-updates
# MySQL5.6解决办法
所有从库要开启binlog
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
gtid_mode=ON
enforce_gtid_consistency
log-bin=mysql-bin
log-slave-update

MySQL延时从库

搞一台机器做延时从库即可，一般延时3-6小时
# 设置延时从库
## 在执行change master to时加上
change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_port=3306,
master_auto_position=1,
master_delay=600;
---------如果从库已经存在，想后期加入-----------
## 可以修改从库信息
root@localhost [(none)] >stop slave;
root@localhost [(none)] >reset slave;
## 配置延时从库
root@localhost [(none)] >change master to master_delay=600;
root@localhost [(none)] >start slave;

## 查看效果
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.51
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 313
               Relay_Log_File: db03-relay-bin.000002
                Relay_Log_Pos: 414
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                  ...................

                    SQL_Delay: 600     #延时时间
          SQL_Remaining_Delay: NULL

# 如何取消延时从库
root@localhost [(none)] >stop slave;
root@localhost [(none)] >reset slave;
root@localhost [(none)] >change master to master_delay=0;
root@localhost [(none)] >start slave;

-------------------------------------------------------------------------------------------
【取消从库身份：reset slave all;】

MySQL半同步复制

MySQL传统主从复制，异步复制

5.5 出现概念，但是不建议使用，性能太差 5.6出现group commit 组提交功能，来提升开启半同步复制的性能 5.7更加完善了，在group commit基础上出现了MGR 5.7的增强半同步复制的新特性：after commit； after sync；

开启半同步

# 主库操作

## 需要用到的插件
[root@db01 ~]# ll /app/mysql/lib/plugin/
semisync_master.so
semisync_slave.so

## 先进入数据库
## 1.安装插件
install plugin rpl_semi_sync_master soname'semisync_master.so';
## 2.启动插件
set global rpl_semi_sync_master_enabled = 1; 
## 3.设置超时时间
set global rpl_semi_sync_master_timeout = 1000;
## 4.修改配置文件
vim /etc/my.cnf
[mysqld]
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000
## 5.查看安装
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| 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      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)



# 从库操作
## 1.安装插件
install plugin rpl_semi_sync_slave soname'semisync_slave.so';
## 2.开启插件
set global rpl_semi_sync_slave_enabled = 1;
## 3.重启IO线程使其生效
stop slave io_thread;
start slave io_thread;
## 4.去`主库`查看安装
show global status like 'rpl_semi%';
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |  # 有数据
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 11    |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 3     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 879   |  # 有等待时间
| Rpl_semi_sync_master_tx_wait_time          | 8797  |  # 有等待时间
| Rpl_semi_sync_master_tx_waits              | 10    |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 10    |
+--------------------------------------------+-------+

过滤复制

过滤复制：一般用于测试环境，实际生产环境不用
------设置黑白名单都是在配置文件里改，改完记得重启------
# 主库（不推荐在主库设置黑白名单）
-白名单 
-#主库设置完白名单后，只记录指定库的binlog
	在配置文件/etc/my.cnf里加
	binlog-do-db=prod
	binlog-do-db=prod1
	(添加多个时必须一条一条，不能一条用逗号隔开)
-#从库只复制主库白名单指定的库
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000005 |      356 | db           |                  | 1b35c3c0-31ed-11ee-94e9-000c291dc4f5:1-15 |
+------------------+----------+--------------+------------------+-------------------------------------------+


-黑名单
-#主库设置完黑名单后只记录除黑名单之外的库
binlog-ignore-db=prod
binlog-ignore-db=prod2
-#从库也只复制除黑名单之外的库



# 从库（强烈推荐）
-白名单
-#从库设置完白名单后，IO线程只管主库要指定的库的binlog
replicate-ignore-db=prod   指定到库
replicate-ignore-table=prod.t1 指定到某个库下的某个表
replicate-wild-ignore-table=prod.t* 指定到某个库的所有以t开头的表


-黑名单
-#从库的IO线程只管主库要除了黑名单之外的库的binlog
replicate-ignore-db=prod   指定到库
replicate-ignore-table=prod.t1 指定到某个库下的某个表
replicate-wild-ignore-table=prod.t* 指定到某个库的所有以t开头的表


-------------注意：要先use到指定的库里-----------------
use 库 -先进入这个库
insert into 表 -在插入数据，这样的数据拿取时才正确