mysql8复制报错_MySQL8基于日志点和GTID方式的主从复制及错误解决

[学习猿地出品]

一、为什么要做主从复制

1. 在业务复制的系统中，有这么一个情景，有一句SQL语句需要锁表，导致占时不能使用读服务，那么久很影响运行中的业务，使用主从复制，让主库负责写，从库复制读。这样，即使出库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

2. 做数据的热备

3. 架构的扩展，随着业务量增大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

二、主从复制原理图主从复制原理图

1. 主服务器master将SQL写入到binlog中。

2. 从服务器slave连接主服务器master。

3. 主服务器master将binlog发到从服务器中。

4. 从服务器将binlog写入到relaylog中继日志中。

5. 从服务器读取主服务器传过来的binlog日志，对从服务器数据进行操作。

三、基于日志点方式复制配置步骤

1. 在主服务器上创建复制用户，并且复制权限

//创建用户命令

CREATE user‘user_name’@‘x.x.x.x’IDENTIFIED WITH mysql_native_password BY ‘password’

//赋值权限命令

GRANT REPLICATION SLAVE ON *.* TO ‘user_name’@’x.x.x.x’;

注意：

mysql8之前的版本加密规则是mysql_native_password，而mysql8之后，加密规则是caching_sha2_password。如果使用新的加密规则。在后期从服务器进行连接主服务器时会连接不上。新的加密规则要求密码强度。

赋值创建用户权限，只需要给复制权限即可，即：REPLICATION SLAVE；

在MySQL8中不支持赋值权限的同时创建用户，必须分开写。

2. 配置主服务器MySQL配置文件My.ini(windows) 或 my.cnf(Linux)

设置bin_log

Bin_log = mysql二进制日志文件路径

设置server_id

Server_id = 可以选择主机IP地址后段。表示唯一编号。

注意：主机server_id不能与从机server_id一致

3.配置从服务器MySQL配置文件

设置bin_log

Bin_log = mysql日志文件路径

设置server_id

Server_id 可以选择从机IP地址后段，表示唯一编号

设置relay_log中继日志

Relay_log = 中继日志路径

[可选]设置log_slave_updates 允许日志记录到从服务器本机二进制文件中

Log_slave_updates = on

[可选]设置read_only 只读属性，可以控制没有权限的用户进行写操作

Read_only = on

4. 初始化从服务器数据

a)注意：从服务器数据必须与主服务器数据一直

1.1导出主服务器数据

Mysqldump 参数 >> 导出SQL文件路径及名称

参数：

--master-data 必选 可以吧binlog的位置和文件名添加到输出中，设置等于2会加上注释前缀

--single-transaction 设置事务的隔离级别、重复读取。不会对数据造成影响，innodb引擎必须加上此选项

--triggers 备份所有的触发器

--routines 备份所有的存储过程和函数

--all-databases 所有的库

--flush-logs 刷新日志

-u  用户名

-p  密码

1.2 将导出主服务器数据导入到从服务器

Mysql -u 用户名 -p密码 < 要导入的SQL文件

注：此导入导出过程必须退出mysql命令行在执行。

5. 启动从服务器复制连路

从服务器中设置

CHANGE MASTER TO

MASTER_HOST = ‘主服务器IP地址’，

MASTER_USER = ‘创建复制用户的名称’，

MASTER_PASSWORD = ‘创建复制用户的密码’，

MASTER_LOG_FILE = ‘mysql_log_filename’,

MASTER_LOG_POS = ‘读取偏移量’;

注意：如果在导出数据时设置了—master-data, MASTER_LOG_FILE 与 MASTER_LOG_POS的信息可以在导出数据库的SQL文件中查询。

2.也可以在主服务中输入命令SHOW MASTER STATUS；来查看

3. 在从服务器中设置连路输入MASTER_LOG_FILE与MASTER_LOG_POS设置的值，必须与读取的sql文件中设置的值或者SHOW MASTER STATUS设置的值保持一致。

6 . 启动从服务

//启动或停止命令：

START SLAVE | STOP SLAVE

//查看从服务状态：

SHOW SLAVE STATUS\G

注意：当启动从服务时。可以通过SHOW SLAVE STATUS\G来查看状态。

当slave_io_running 与 slave_sql_running 都为YES时。表示主从复制成功。

有一方为no 或者 connecting 都将失败。

失败原因：

Slave_io_running 如果为no或者connecting

- 关闭防火墙

- 主从Server_id一致

- 数据库目录(data)目录下 auto.cnf一致

- 主服务器mysql权限

- 复制账户用户名或密码错误

- 网络不通

- Mysql8中存在：创建用户时密码加密规则。

Slave_SQL_RUNNING如果为NO

- 表示主服务器二进制名称不对或者读取数据便宜位置不对

解决方式：从新导入SQL文件，并且准确记录MASTER_LOG_FILE与MASTER_LOG_POS的值

四、基于日志点复制与GTID复制的区别

1. 什么是GTID

a) GTID既全局事务ID，其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID

b) GTID = source_id:transaction_id

2. GTID复制步骤

1. 主服务器上创建复制用户、赋值权限

CREATE user ‘user_name’@’x.x.x.x’ IDENTIFIED WITH mysql_native_password BY ‘password’;

GRANT REPLICATION SLAVE ON *.* TO ‘user_name’@’%’;

注：在使用日志点复制时，已经提到过在mysql8中需要更改加密规则，另外，在msyql8中不允许赋值权限的同时创建用户，两句必须分开写。

2. 基于GTID配置主数据库服务器

a) Log_bin = mysql二进制日志

b) Server_id = 唯一id

c) Gtid_mode = on 开启GTID

d) Enforce-gtid-consistency = on 强制GTID一致性(安全)

该选项开启后，以下两种语句将不能使用。

CREATE TABLE …SELECT

CREATE tempporary table

e) Log-slave-updates = on 在从服务器中记录传过来的主服务器修改日志

3. 基于GTID配置从数据库服务器

a) Log_bin = mysql二进制日志

b) Server_id = 唯一id

c) Relay_log =中继日志

d) Gtid_mode = on

e) Enforce-gtid-consistency = on 强制GTID一致性(安全)

f) Log-slave-updates = on

g) Read_only = on [建议]

h) Master_info_repository = TABLE [建议]

i) Relay_log_info_repository = TABLE[建议]

4. 初始化从服务器数据

a) mysqldump --master-data = 2 –single-transaction

5. 启动基于GTID的复制

a) CHANGE MASTER TO

b) MASTER_HOST = ‘master_host_ip’,

c) MASTER_PASSWORD = ‘password’,

d) MASTER_AUTO_POSITION = 1；

注意：MASTER_AUTO_POSITION可以导出的SQL文件中查询到。在导出命令时设置参数—master-data 并且 配置文件中开启 gtid_mode = on既可以查看到

GTID_PURGED = ‘’及代表没有，在设置MASTER_AUTO_POSITION 既可以设置为1

6. 启动从复制

START SLAVE;

7. 查看状态

SHOW SLAVE STATUS;

如果slave_io_running 与 slave_sql_running 都为YES时。表示主从复制成功

如果为no或者connecting，解决方式与复制日志点方式一样。

学习猿地，成就自己的只需一套精品!祝各位学子，早日登上巅峰。