大圣Blog

MySQL

数据库操作

数据表操作

Mysql列类型及列属性

MySQL常用函数

MySQL增删改

MySQL查询

JOSN的支持

MySQL流程控制语句

视图

存储过程及存储函数

变量与游标

事务

触发器

权限及安全管理

MySQL日志

二进制日志

Mysql5.7复制功能

数据备份与恢复

性能优化

课程导航

二进制日志

数据备份与恢复

MySQL复制是异步的，所以肯定有延迟

MySQL复制可以是整个实例进行复制，也可以对实例中的某个库或是某个表进行复制  
**Master端**  
--binlog-do-db 哪些库要进行binlog写操作  
--binlog-ignore-db 哪些库不需要进行binlog写操作  
**Salve端**  
--replicate-do-db  
--replicate-ignore-db  
--replicate-do-table  
--replicate-ignore-table  
--replicate-wild-do-table  
--replicate-wild-ignore-table

## 复制类型
存在两种复制类型：  
1 基于二进制日志的复制  
2 使用GTID完成基于事务的复制  
在以前的mysql版本中，读写分离的实现一般都是基于日志的主从复制实现的，这样会产生一个问题，就是master宕机之后，slave由于同步延时的问题，会导致master和slave内容不同，甚至会多个slave之间互相不同。所以为了解决这个问题，再mysql5.7.6版本之后加入了基于GTID的事务控制，具体的说就是每个事务由一个唯一的gtid标识，当slave都成功执行之后master才写入硬盘完成该事务，如果master突然宕机，那么就自动回滚。数据的一致性得到保证。

## 半同步复制
MySQL支持半同步复制
![图片](https://img3.mukewang.com/5cc0443b0001054905000421.jpg)
## 实现基于事务GTID的复制
在gtid模式下，可以自动定位master binlog的文件名和行数，所以省去了去master服务器查看的麻烦。只要master和slave数据首先一模一样（通过mysqldump导入），同时master没有任何操作（需要将3306上所有连接都断开）。然后直接使用change master命令即可。详细过程如下

1、Master的配置还和上面一样。需要打开binlog，并开启enforce_gtid_consistency和gtid_mode（如果嫌每次启动master都要手动开启太麻烦，那么就把这两项写到my.cnf中，上面有提到）

2、Slave的配置文件my.cnf中加入

enforce_gtid_consistency = ON  
gtid_mode = ON  
并且别忘了修改serverid

server-id               = 2  
启动之后，通过change master语句设置master的IP地址，无需设置binlog的位置和行数，只需保证目前的库和master库内容一致即可

change master to master_host='Master的IP地址',  
    -> master_user='dba',  
    -> master_password='123456'  
start slave;  
然后再检查一下是否都是yes即可
mysql> show slave status \G
### 实践
#### 准备工作
准备好两台linux服务器，ip为192.168.0.220及221，均搭建好lnmpa环境，mysql版本均为5.7.22
#### 配置MySQL
220为master，221为slave

```
220部分配置如下：
log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
expire_logs_days = 10
early-plugin-load = ""
enforce_gtid_consistency=ON
gtid_mode=ON

221部分配置如下
#log-bin=mysql-bin
#binlog_format=mixed
server-id   = 2
replicate-do-db = test
expire_logs_days = 10
early-plugin-load = ""
enforce_gtid_consistency = ON
gtid_mode = ON
```
修改后重启220和221的mysql服务
#### 创建进行复制的账号
```
mysql> CREATE USER 'repl'@'192.168.0.221';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.221'
    -> ;
```
#### 准备数据
```
220复制操作
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table test_repl( num int );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test_repl values(3),(2),(4),(45);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

# 导出sql文件
[root@localhost ~]# mysqldump -uroot -p --single-transaction --master-data=2 test > test.sql
Enter password:

# 将sql文件复制到221服务器上
[root@localhost ~]# scp test.sql root@192.168.0.221:/root/
```

```
221服务器操作
[root@localhost ~]# mysql -uroot -p < test.sql 
Enter password
```
#### 开启主从复制功能
```
mysql> change master to
    -> master_user='repl',
    -> master_host='192.168.0.220',
    -> master_password='',
    -> master_log_file='mysql-bin.000008',
    -> master_log_pos=1545;

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.220
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 1545
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
```
####            Slave_SQL_Running: Yes

测试复制功能
```
220服务器插入数据
mysql> use test;
Database changed
mysql> insert into test_repl values(3),(32),(3232);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

221上看数据有没有同步
mysql> select * from test_repl;
+------+
| num  |
+------+
|    3 |
|    2 |
|    4 |
|   45 |
|    3 |
|   32 |
| 3232 |
+------+
7 rows in set (0.00 sec)
```

二进制日志

数据备份与恢复

MySQL

课程导航

Mysql5.7复制功能

文章目录