1 主从复制

1.1 架构图

1.2 原理

1、主服务器MySQL服务将所有的写操作记录在 binlog 日志中，并生成 log dump 线程，将 binlog 日志传给从服务器MySQL服务的 I/O 线程。

2、从服务器MySQL服务生成两个线程，一个是 I/O 线程，另一个是 SQL 线程。

3、从库 I/O 线程去请求主库的 binlog 日志，并将 binlog 日志中的文件写入 relaylog（中继日志）中。

4、从库的 SQL 线程会读取 relaylog 中的内容，并解析成具体的操作，来实现主从的操作一致，达到最终两个数据库数据一致的目的。

注意点：

• 主从复制是异步的逻辑的 SQL 语句级的复制；
• 复制时，主库有一个 I/O 线程，从库有两个线程，及 I/O 和 SQL 线程；
• 实现主从复制的必要条件是主库要开启记录 binlog 的功能；
• 作为复制的所有 MySQL 节点的 server-id 都不能相同；
• binlog 文件只记录对数据内容有更改的 SQL 语句，不记录任何查询语句。
• 因为binlog模式选择了ROW，不仅支持DDL语句，甚至还同步了用户权限修改语句，要避免用户权限不同导致的同步出错。

1.3 配置步骤

准备两台局域网内的虚拟机

删除原有mysql并安装新的mysql

以下为master配置，slave类似

1. apt list --installed | grep mysql
2. apt autoremove mysql*
3. whereis mysql
4. 最后完全清理下查出来的文件rm -rf /etc/mysql
5. 创建配置目录 /usr/local/docker_etc/mysql/conf
6. touch my.cnf
7. 在docker 中拉取镜像docker pull mysql:5.7
8. 安装docker run -itd -p 3306:3306 --name mysql-master -v /usr/local/docker_etc/mysql/conf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
9. 进入容器docker exec -it mysql-master bash

知识点：

• 在 docker exec 命令中使用 -it 是为了获取一个交互式的终端会话连接到容器。

• -i 表示 Keep STDIN open even if not attached,也就是保持标准输入打开。

• -t 表示 Allocate a pseudo-TTY,分配一个伪终端。

• 最后的 bash 是启动一个交互式 bash shell 会话。

配置文件修改（my.cnf）

• master

[mysqld]
port = 3306
server-id = 1
#要同步的数据库
binlog-do-db = demo
#要生成二进制日志文件 主服务器一定要开启
log-bin = mysql-bin
binlog_format=ROW
#设置时间和字符
default-time-zone=+08:00
character-set-server=utf8mb4
# 是否只读
read-only=0

• slave

[mysqld]
port = 3306
server-id = 2
#要同步的数据库
binlog-do-db = demo
#要生成二进制日志文件（从服务器可选）
log-bin = mysql-bin
binlog_format=ROW
#设置时间和字符
default-time-zone=+08:00
character-set-server=utf8mb4
# 是否只读
read-only=1
# 是否记录主库发送过来的更新事件的日志。
log-slave-updates = 1

查看是否配置成功：

• SHOW VARIABLES LIKE 'log_bin';
• show variables like '%time_zone%';
• SELECT NOW(); 查看时间

知识点： 关于时间

• 因为使用的是docker中的mysql，mysql默认使用的是docker中的系统时间UTC，所以必须更改，加八个小时，使用中国时间，不然java程序运行会报错。
• CST同名的有4个时区，在 mysql 里被理解为 China Standard Time（GMT+8），但是在Java里被理解为Central Standard Time (USA)（GMT-6）。在java程序中会报错。解决办法是mysql就别用CST时区，改成 +08:00 以免造成误解。

创建主服务器(master)复制用户及相关权限

1. 创建用户create user 'slave'@'%' identified by '123456';
2. 设置用户权限grant replication slave,replication client on *.* to 'slave'@'%';
3. 刷新权限flush privileges;
4. 查看用户权限show grants for 'slave'@'%';

知识点：

• REPLICATION SLAVE: 用于从库复制和应用主库的日志事件。
• REPLICATION CLIENT: 用于配置主库参数和启动/停止复制线程。

数据备份同步

1. 登录master，创建数据库和表，新建测试数据

create database demo;
use demo;
create table user( id int(10) auto_increment, name varchar(30), primary key (id) )charset=utf8mb4;
insert into user(name) values('kom');

2. 执行锁表操作FLUSH TABLES WITH READ LOCK;
3. 退出登录，将master中需要同步的db的数据dump出来
   
   mysqldump -uroot -p demo > demo.dump
4. 将数据导入slave
   
   mysql -uroot -h10.132.xxx.xxx -p demo < demo.dump
5. 登录master，解锁master
   
   UNLOCK TABLES;

知识点：

• 为什么要锁表？

  • 这是一种通过锁表创建主从服务器数据一致的方法。

  • 在主服务器master上执行FLUSH TABLES WITH READ LOCK,这会锁定全部表只允许读取,不允许写入。在master上lock期间,通过mysqldump导出需要同步的database。将导出的数据库数据导入从服务器slave。最后在master上执行UNLOCK TABLES,解锁表,允许读写。这样就可以保证在dump数据时,master和slave中的数据是一致的。

  • 因为主从复制默认是增量同步，所以要先做一下全量同步，保证一致性。

  • 缺点：同步期间会导致数据无法写入

• mysqldump

  • mysqldump是MySQL提供的一个命令行工具,可以用来备份MySQL数据库。
  • 将MySQL数据库备份成一个文件。mysqldump可以导出整个数据库或者指定的表,并生成SQL语句或CSV文件格式。
  • 将数据备份并转移到另一个MySQL服务器。可以在另一台服务器上用mysqldump备份后导入。

主从复制控制的相关命令

• master
  • 查看主服务器 binlog 记录状态
    
    show master status\G;
    
    记下File和Position的值，下面要用
  • 如果Position不是初始值154，可以用reset master重置一下
    
    命令功能说明： 删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。用于第一次进行搭建主从库时，进行主库binlog初始化工作。
    
    注意： 务必确保所有slave都处于停止状态。
  • stop master和start master
    
    这两个命令不能在mysql客户端直接输入，要在命令行里用mysqladmin输入。还没试过，一般用不到。
• slave
  • 查看slave同步状态
    
    show slave status\G;
    
  • stop slave
  • reset slave
    
    命令功能说明： 用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件。
    
    注意： 务必确保该slave处于停止状态。
  • start slave

从服务器(slave)开始复制

1. 连接主服务器(master)及设置复制的起始节点

change master to master_host='10.132.xxx.xxx',
master_port=3306,
master_user='slave',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=154;

file和pos是刚刚在主服务器中记下的。
2. 开始复制start slave;
3. 查看复制状态show slave status \G

// 注意这两行
Slave_IO_Running: Yes //表示I/O线程读取成功
Slave_SQL_Running: Yes //表示SQL线程执行成功

4. 查看数据表结构show create table user\G
   注： 这种MySQL服务器内部实现的信息显示语句,不需要以分号;结尾。
5. 查看数据表数据select * from user;

1.4 GTID主从复制

配置文件修改（my.cnf）

• master

[mysqld]
port = 3306
server-id = 1
#要同步的数据库
binlog-do-db = demo
#要生成二进制日志文件 主服务器一定要开启
log-bin = mysql-bin
binlog_format=ROW
#设置时间和字符
default-time-zone=+08:00
character-set-server=utf8mb4
# 是否只读
read-only=0
#开启gtid模式
gtid_mode=on   
#强制gtid一致性，开启后对于特定create table不被支持
enforce_gtid_consistency=on  

• slave

[mysqld]
port = 3306
server-id = 2
#要同步的数据库
binlog-do-db = demo
#要生成二进制日志文件（从服务器可选）
log-bin = mysql-bin
binlog_format=ROW
#设置时间和字符
default-time-zone=+08:00
character-set-server=utf8mb4
# 是否只读
read-only=1
# 是否记录主库发送过来的更新事件的日志。
log-slave-updates = 1
# 控制GTID功能的启用
gtid_mode = on 
enforce_gtid_consistency = on 

1. 重启mysql服务
2. show global variables like '%gtid%';检查主库gtid配置是否生效

从服务器(slave)开始复制

1. 连接主服务器(master)及设置复制的起始节点

change master to master_host='10.132.xxx.xxx',
master_port=3306,
master_user='slave',
master_password='123456',
master_auto_position=1;

2. 开始复制start slave;
3. 查看复制状态show slave status \G
4. 查看数据表数据select * from user;
5. 主服务器中show master status; 可以查看Executed_Gtid_Set

知识点： 强制gtid一致性开启，被禁止的建表语句

• 创建临时表：CREATE TEMPORARY TABLE语句会被禁止,因为临时表不参与GTID复制。
• 创建带有自增主键的表：因为自增主键会导致主从的表数据不一致。
• 引擎为MEMORY的表：使用内存型引擎的表也会被禁止，因为内存表数据无法进行持久化复制。
• 创建带有SRID的空间数据的表：CREATE TABLE t1 (gis GEOMETRY SRID 4326)

备份

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

Warning: A dump from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that were executed during its extraction and might not be represented in the dumped data. This might result in an inconsistent data dump.
In order to ensure a consistent backup of the database, pass --single-transaction or --lock-all-tables or --master-data.

部分导出的备份会包含所有表的GTID,如果不需要这些不相关表的GTID,可以使用--set-gtid-purged=OFF不导出。

所以用mysqldump -uroot -p demo > demo.dump --set-gtid-purged=OFF

1.5 报错

1. 未创建相同的表结构

mysql> show create table user\G

ERROR 1146 (42S02): Table 'demo.user' doesn't exist

必须保证同步之前数据库和表结构全部相同，建议在复制前先用mysqldump全量同步
2. stop master

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'master' at line 1

不能在mysql客户端里面使用这个命令。但是在命令行里通过mysqladmin我也没试成功，不过这个命令一般来说是用不到的，用reset master一般就能解决问题。
3. 通过slave数据库同步失败，只读到了全量同步阶段的数据，原因不明。
从库需要配置 log-slave-updates = 1 使从实例也能将从主实例同步的数据写入从库的 binlog 文件中，如果主库开启了gtid mode，从库也需要开启。

# 是否记录主库发送过来的更新事件的日志。
log-slave-updates = 1
# 控制GTID功能的启用
gtid_mode = on 
enforce_gtid_consistency = on 

4. 重置主从连接出错

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replicated to the slave. Suggest to replicate any transactions that master has rolled back from slave to master, and/or commit empty transactions on master to account for transactions that have been'

从库的GTID大于主库的GTID，必须重置一下gtid_executed

master：reset master;

slave：

stop slave;
reset slave;
# 这个命令会重置所有与复制相关的信息,包括gtid_executed的值。
reset master;
start slave;

2 MySQL知识

2.1 timestamp

timestamp有两个属性，分别是CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP两种，使用情况分别如下：

1. CURRENT_TIMESTAMP

当要向数据库执行insert操作时，如果有个timestamp字段属性设为 CURRENT_TIMESTAMP，则无论这个字段有没有set值都插入当前系统时间

2. ON UPDATE CURRENT_TIMESTAMP
   当执行update操作是，并且字段有ON UPDATE CURRENT_TIMESTAMP属性。则字段无论值有没有变化，它的值也会跟着更新为当前UPDATE操作时的时间。

参考文章

MySQL 的主从复制实践

MySQL主从复制的简单搭建

关闭mysql主从步骤以及reset master和reset slave

配置MySQL GTID 主从复制