文章目录

展开

主从复制的原因

1）备份（延时从库）

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

3）分担主库压力

传统主从

## 线程：
    dump线程：主库上的线程，从binlog中取出数据交给从库的IO线程
    IO线程：从库上的线程，连接主库dump线程取数据，取到数据写入缓存（relay log中）
    SQL线程：从库上的线程，执行relay log中的SQL语句到数据库中
## 文件：
    binlog日志：主库上的文件，记录所有更改库表的语句
    master.info：从库上的文件，记录主库的binlog名字和位置点，用于IO线程更新/读取
    relay-log.info：从库上的文件，记录relay-log里的位置点，上一次SQL线程读取到哪里了，SQL线程更新/读取
    relay-log：从库上的文件，记录从主库binlog拿来的新数据

# 主库操作
## 修改配置文件
vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=mysql-bin

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

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      445 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

# 从库操作
## 修改配置文件(从库server_id相同是不影响主从复制的)
vim /etc/my.cnf
[mysqld]
server_id=2

# 从库执行change master（告诉从库，主库的信息）
change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000001',
master_log_pos=445,
master_port=3306;

# 开启主从复制
start slave;

# 查看主从用户状态
show slave status\G

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

主从复制原理

image-20230806191745928

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: db02-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

IO线程故障

和主库建立连接出现问题

## IP
### 在从库上,去ping主库IP
ping 172.16.1.51

## 端口
### 在从库上telnet主库端口
telnet 172.16.1.51 3306

## 用户名和密码错了
### 在从库上，登陆一下主库
mysql -urep -p123 -h172.16.1.51

## 反向解析报错
ERROR 1045 (28000): Access denied for user 'root'@'db01' (using password: YES)
### 修改配置文件(跳过反向解析)
vim /etc/my.cnf
[mysqld]
skip_name_resolve

## 位置点大于binlog大小，导致IO线程连不上
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;
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'
### 解决方法
重新修改pos值

## 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
### 解决方法
修改配置文件中的server_id,使其与主库不同

## UUID相同，导致的IO线程无法连接
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.
### 解决方法
修改auto.cnf配置文件中的UUID值
vim /app/mysql/data/auto.cnf

SQL线程故障

  • 主库有从库没有的数据

    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

#### 以上方案都无法解决根本问题

# 解决方案三：
## 1.先给主库做全备
mysqldump -A -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full.sql.gz
## 2.将全备恢复到新从库中
scp /tmp/full.sql.gz 172.16.1.52:/root
zcat /root/full.sql.gz | mysql
## 3.查看全备位置点
zcat /root/full.sql.gz | head -25
### 截取显示部分进行补全
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;
### 启动主从
mysql> start slave;
### 查看主从复制状态
show slave status\G

  • 从库有主库没有的数据（原因：在从库写入数据）

    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

#### 以上方案都无法解决根本问题

# 解决方案三：
## 做完主从复制后，给从库设置为只读（临时）
set global read_only=1;

## 修改配置文件
vim /etc/my.cnf
[mysqld]
server_id=2
read_only=1

从库设置为只读存在问题(read_only=1)

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

## 必须创建普通用户，且不能给all的权限
### 可给出的权限有：insert update delete select

dev@10.0.0.52 [(none)] >create database ljy1;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

dev@10.0.0.52 [(none)] >insert into ljy.ljy1 values(2);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

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

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

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

  • 什么是GTID？

GTID(Global Transaction ID)全局事务标识符：是一个唯一的标识符，它创建并与源服务器（主）上提交的每个事务相关联。

此标识符不仅对其发起的服务器是唯一的，而且在给定复制设置中的所有服务器上都是唯一的。 所有交易和所有GTID之间都有1对1的映射。

GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。

下面是一个GTID的具体形式：

3E11FA47-71CA-11E1-9E33-C80AA9429562:23

  • GTID新特性

    • 支持多线程复制:事实上是针对每个database开启相应的独立线程,即每个库有一个单独的(sqlthread)。

    • 支持启用GTID,在配置主从复制,传统的方式里,你需要找到binlog和POS点,然后change master to指向 。在mysql5.6里,无须再知道binlog和POS点,只需要知道master的IP/端口/账号密码即可,因为同步复制是自动的,MySQL通过内部机制GTID自动找点同步。

    • 基于Row复制只保存改变的列,大大节省Disk Space/Network resources和Memory usage。

    • 支持把Master 和Slave的相关信息记录在Table中

    原来是记录在文件里,记录在表里,增强可用性

    • 支持延迟复制

从库操作

## 基于GTID主从复制
mysql> change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_port=3306,
master_auto_position=1;
## 报错
ERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 cannot be executed because @@GLOBAL.GTID_MODE = OFF.

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

## 临时开启
mysql> set global gtid_mode=1;
mysql> show variables like '%gtid%';
+----------------------------------+----------------+
| Variable_name                    | Value          |
+----------------------------------+----------------+
| binlog_gtid_simple_recovery      | ON             |
| enforce_gtid_consistency         | OFF            |
| gtid_executed_compression_period | 1000           |
| gtid_mode                        | OFF_PERMISSIVE |
| gtid_next                        | AUTOMATIC      |
| gtid_owned                       |                |
| gtid_purged                      |                |
| session_track_gtids              | OFF            |
+----------------------------------+----------------+

## 永久开启
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
gtid_mode=1

## 重启
[root@db02 ~]# /etc/init.d/mysqld restart 

## 重新输入
mysql> change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_port=3306,
master_auto_position=1;

## 启动从库复制
mysql> start slave

##查看
mysql> show slave status\G
Slave_IO_Running: NO
Slave_SQL_Running: Yes
Last_IO_Error: The replication receiver thread cannot start in AUTO_POSITION mode: the master has GTID_MODE = OFF instead of ON.
## 主库也要开启GTID

主库操作

## 修改配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
gtid_mode=ON
enforce_gtid_consistency

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

GITD

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
mysql> create database sw;
mysql> create table sw.hg(id int);
mysql> commit;
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 |      466 |              |                  | 0cb38c20-31e2-11ee-b018-000c294b61a6:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+

mysql> show slave status\G
Retrieved_Gtid_Set: 0cb38c20-31e2-11ee-b018-000c294b61a6:1-2
Executed_Gtid_Set: 0cb38c20-31e2-11ee-b018-000c294b61a6:1-2
##***GTID是由 主库UUID + 事务提交号txid***
[root@db01 ~]# cat /app/mysql/data/auto.cnf
[auto]
server-uuid=0cb38c20-31e2-11ee-b018-000c294b61a6.

MySQL延时从库

## 停库
root@localhost [(none)] >stop slave;

# 可以修改从库信息
root@localhost [(none)] >reset slave;

# 配置延时从库
root@localhost [(none)] >change master to master_delay=600;
root@localhost [(none)] >start slave;

## 传统
change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_port=3306,
master_log_file='mysql-bin.000002',
master_log_pos='466',
master_delay=600;

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

# 6.验证延时情况 看倒计时
mysql> show slave status\G
SQL_Remaining_Delay: 93

# 取消从库身份
root@localhost [(none)] >reset slave all;

延时从库原理

image-20230807180702979

MySQL的半同步复制

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

半同步复制（Semi synchronous Replication）则一定程度上保证提交的事务已经传给了至少一个备库。
出发点是保证主从数据一致性问题，安全的考虑。

至少等一个备库的数据同步完了 IO线程才会去那新的数据

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

开启半同步

  • 主库操作
## 查看是否动态支持半同步复制
mysql> show global variables like 'have_dynamic_loading';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+

## 插件的路径
[root@db01 ~]# ll /app/mysql/lib/plugin/
-rwxr-xr-x 1 7161 31415   722101 Mar 17 00:08 semisync_master.so
-rwxr-xr-x 1 7161 31415   163772 Mar 17 00:08 semisync_slave.so

# 1.安装插件
mysql> install plugin rpl_semi_sync_master soname'semisync_master.so';

# 2.启动插件
mysql> set global rpl_semi_sync_master_enabled = 1;

# 3.设置超时时间
mysql> set global rpl_semi_sync_master_timeout = 1000;

# 4.修改配置文件
[root@db01 ~]# 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     |
+--------------------------------------------+-------+
  • 从库操作
# 1.安装插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';
# 2.开启插件
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
# 3.重启io线程使其生效
mysql> stop slave io_thread;
mysql> start slave io_thread;

## 主库查看
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             | 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     |
+--------------------------------------------+-------+

## 建库
mysql> create database zzz;
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     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 1     |
| 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      | 540   |
| Rpl_semi_sync_master_tx_wait_time          | 540   |
| Rpl_semi_sync_master_tx_waits              | 1     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 1     |
+--------------------------------------------+-------+

半同步复制原理

image-20230807192417214

注：相关参数说明

rpl_semi_sync_master_timeout=milliseconds

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

rpl_semi_sync_master_wait_no_slave={ON|OFF}

如果一个事务被提交,但Master没有任何Slave的连接，这时不可能将事务发送到其它地方保护起来。默认情况下，Master会在时间限制范围内继续等待Slave的连接，并确认该事务已经被正确的写到磁盘上。

可以使用此参数选项关闭这种行为，在这种情况下，如果没有Slave连接，Master就会恢复到异步复制。

过滤复制

主库

  • 黑名单：主库拒绝让从库，复制指定库的数据
    • 原理：不记录指定的数据库数据到binlog日志中
    • binlog-ignore-db
  • 白名单：主库只想让从库，复制指定库的数据
    • 原理：只记录指定的库数据到binlog日志中
    • binlog-do-db
## 主库白名单配置
[root@m01 ~]# vim /etc/my.cnf
[mysqld]
binlog-do-db=xz

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

## 主库黑名单配置
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
binlog-ignore-db=lq

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

# 查看名单 Binlog_Do_DB是白名单 Binlog_Ignore_DB是黑名单
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000005 |      194 | xz           | lq               | 0cb38c20-31e2-11ee-b018-000c294b61a6:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+

## 主库名单配置多个库方法
[root@m01 ~]# vim /etc/my.cnf
[mysqld]
binlog-do-db=xz
binlog-do-db=lq

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

从库

  • 黑名单：从库SQL线程，不拿relay log中的指定库语句
    • replicate-ignore-db=mysql
    • replicate-ignore-table=mysql.user
    • replicate-wild-ignore-table=mysql.t*
  • 白名单：从库SQL线程，只拿relay log中的指定库语句
    • replicate-do-db=mysql
    • replicate-do-table=mysql.user
    • replicate-wild-do-table=mysql.t*
## 从库白名单配置的三种写法
  - replicate-ignore-db=mysql             （指定库名）
  - replicate-ignore-table=mysql.user     （指定库名.表名）
  - replicate-wild-ignore-table=mysql.t*   (支持通配符 mysql库下所有t开头的表)

[root@db02 ~]# vim /etc/my.cnf
[mysqld]
replicate-do-db=hy

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

## 从库黑名单配置的三种写法
  - replicate-ignore-db=mysql            （指定库名）
  - replicate-ignore-table=mysql.user    （指定库名.表名）
  - replicate-wild-ignore-table=mysql.t*  (支持通配符 mysql库下所有t开头的表)

[root@db02 ~]# vim /etc/my.cnf
[mysqld]
binlog-ignore-db=bg

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

# 查看名单 Binlog_Do_DB是白名单 Binlog_Ignore_DB是黑名单
mysql> show slave status\G
              Replicate_Do_DB: 这是白名单
          Replicate_Ignore_DB: 这是黑名单

## 从库名单配置多个库方法
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
replicate-do-db=yd
replicate-do-db=sltn
replicate-do-db=sy

[root@db02 ~]# /etc/init.d/mysqld restart
赏