蜗牛君的NoteBook

部署 MySQL 8.0 主从复制

镜像版本 mysql:8.0

参考文献: https://blog.csdn.net/why15732625998/article/details/80463041

MySQL 主从复制（也称 A/B 复制） 的原理

  1. Master将数据改变记录到二进制日志(binary log)中，也就是配置文件log-bin指定的文件，
    这些记录叫做二进制日志事件(binary log events)；

  2. Slave 通过 I/O 线程读取 Master 中的 binary log events 并写入到它的中继日志(relay log)；

  3. Slave 重做中继日志中的事件， 把中继日志中的事件信息一条一条的在本地执行一次，完
    成数据在本地的存储， 从而实现将改变反映到它自己的数据(数据重放)。

MySQL安装基础

1. 安装msyql

msyql 的一些常用配置信息

在解压的路径下查看是否含有my.ini的文件，如果没有则新建一个 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[mysqld]
# 设置3306端口
port=8307
# 设置mysql的安装目录
basedir=D:\Program Files\MySQL
# 设置mysql数据库的数据的存放目录
datadir=D:\Program Files\MySQL\Data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=8307
default-character-set=utf8

1.1 linux dockers设置初始参数

1
docker run --name mysql_master -p 3306:3306 -e MYSQL_ROOT_PASSWORD=admin -d mysql:8.0
1
docker run --name mysql_slave -p 3307:3306 -e MYSQL_ROOT_PASSWORD=admin -d mysql:8.0

2. 更改数据库默认密码并授权访问

更改密码

如果设置处初始密码,可以不更改, 视情况而定

输入mysql -u root -p后会让你输入密码，密码为前面让你记住的密码，输入正确后就会出现如下界面，表示进入了MySQL命令模式。

img

新用户需要修改密码先，输入

1
2
3
use mysql;
ALTER USER 'root'@'%' IDENTIFIED BY 'admin' PASSWORD EXPIRE NEVER; #修改加密规则, 8.0 以后navicat连接会报错, 加密方案不支持
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'admin';  #更新一下用户的密码

出现如下界面表示更改成功。

img

到此，MySQL8.0.12数据库就安装完成了。

更改权限

查询用户权限情况

1
2
use mysql;
select  User,authentication_string,Host from user;

可选配置：使用root远程登录输入

1
2
grant all privileges on *.* to 'root'@'%';
flush privileges;

3. 数据库相关配置

查看默认数据库：

1
show databases;

选择mysql数据库：

1
use mysql;

查看默认MySQL用户的权限与加密情况：

1
select host, user, authentication_string, plugin from user;

img

创建新用户并添加权限

创建新用户：

1
CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'admin';

给新用户授权：

1
GRANT ALL PRIVILEGES ON *.* TO 'slave'@'%';

l刷新权限： 

1
FLUSH PRIVILEGES;

基础安装问题详解

1. MySQL8.0.12不能连接Navicat

原因：MySQL8.0与MySQL5.0所采用的加密方式规则不一样，所以导致 Navicat打不开。可一下命令查看密码的规则。

1
select host, user, authentication_string, plugin from user;

img

如上图，plugin这一列就是对应用户的加密规则，可以看到我的root用户的加密规则是：mysql_native_password，这是因为我已经设置过了，默认的是：caching_sha2_password，所以我们只需要将默认的caching_sha2_password改为mysql_native_password即可。

解决方案：输入ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘新密码’;即可修改root用户的加密规则以及密码。

1
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'admin';
1
2
3
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Aqgj,123';
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'Aqgj,123';
FLUSH PRIVILEGES;

2. 授权出错

显示You are not allowed to create a user with GRANT

原因：在网上有很多教程说当出现The user specified as a definer (‘root’@’%’) does not exist时表示root用户权限不足，只需要执行GRANT ALL ON *.* TO ‘root’@’%’;就可以了，但是往往又会出现You are not allowed to create a user with GRANT的错误提示。这是因为GRANT ALL ON *.* TO ‘root’@’%’;这条语句中@’%’中的百分号其实是root用户对应host的名称，很多人并没有注意到他的root用户对应的其实是localhost，直接就执行了上面的语句，所以才会报错。

解决方案：只要将GRANT ALL ON *.* TO ‘root’@’%’;中的%改为对应的host名称即可，最后还要刷新一下权限FLUSH PRIVILEGES; 。

1
2
GRANT ALL ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

特别说明：网上说%表示通配所有的host，但是操作时并不成功，不明白是为什么，我猜想可能与MySQL8.0版本有关系。

配置数据库的主从复制

1.主从服务器操作系统版本和位数一致；

2.Master 和 Slave 数据库的版本要一致；

3.Master 和 Slave 数据库中的数据要一致；

4.Master 开启二进制日志， Master 和 Slave 的 server_id 在局域网内必须唯一；

!!!! 如果docker 容器缺少vi软件, 可参考这篇文章Docker实用技巧之更改软件包源提升构建速度

1. 配置主库

注意！注意！注意！：配置文件一定要保存成ASNI存储方式，不然会报错

在主从的服务器上分别找到以下路径的my.ini文件

1.1 主库conf配置文件的mysqld节点下加入

a) 普通模式配置
1
2
3
4
5
6
7
8
9
[mysqld]
log-bin=master-bin #开启二进制日志 binlog
#binlog-do-db=mytest #用于读写分离的具体数据库，这里我创建了mytest作测试
log-bin-index=master-bin.index
#binlog_ignore_db=mysql #不用于读写分离的具体数据库
#binlog_ignore_db=information_schema #和binlog-do-db一样，可以设置多个
#选择row模式 
#binlog-format=ROW
server-id=1 #配置唯一的server-id
b) git模式配置
1
2
3
4
5
6
-----使用gtid模式 才使用如下配置---------
[mysqld]
server-id=1   #除了server-id不同外，其他配置主从都相同
log-bin=master-bin
gtid-mode = on
enforce-gtid-consistency = on

1.2 重启mysql服务

1.3 在主数据库中创建用于从数据连接的用户

1
2
3
CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'admin';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
FLUSH PRIVILEGES;

1.4 获取主节点当前binary log文件名和位置（position）

查询主数据库状态，并记录下File和Position字段的值 从数据库中会用到

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000002 |      155 | mytest       | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
row in set (0.02 sec)

2. 配置从库

2.1 从数据库conf配置

从数据库 (同主库，必须指定中继日志的名称) , 从数据库配置的mysqld节点下加入

a) 传统模式
1
2
3
4
5
6
7
8
9
10
11
12
13
[mysqld]
#在[mysqld]中添加：
server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index

#replicate-do-db=test
#备注：
#server-id 服务器唯一标识，如果有多个从服务器，每个服务器的server-id不能重复，跟IP一样是唯一标识，如果你没设置server-id或者设置为0，则从服务器不会连接到主服务器。
#relay-log 启动MySQL二进制日志，可以用来做数据备份和崩溃恢复，或主服务器挂掉了，将此从服务器作为其他从服务器的主服务器。
#replicate-do-db 指定同步的数据库，如果复制多个数据库，重复设置这个选项即可。若在master端不指定binlog-do-db，则在slave端可用replication-do-db来过滤。
#replicate-ignore-db 不需要同步的数据库，如果有多个数据库，重复设置这个选项即可。
#其中需要注意的是，replicate-do-db和replicate-ignore-db为互斥选项，一般只需要一个即可。

修改完server-id 之后最好重启一下从库, 因为从库默认的server-id可能是1 与之前主库设置的id冲突了, 从库还没有读取新的设置项,需要重启更新一下

b) GIT模式
1
2
3
4
5
6
7
-----使用gtid模式 才使用如下配置---------
[mysqld]
server-id=2   #除了server-id不同外，其他配置主从都相同
log-bin=master-bin

gtid-mode=on
enforce-gtid-consistency=on

开启从数据库服务，手动创建测试读写分离的库，这边不会帮你自动创建，同时也创建和主库一样的用户，我这里还是先停止从库

2.2 在从数据库的msyql命令模式种执行以下命令

1
stop slave;

2.3 从（Slave）节点上设置主节点参数

a)传统模式(下面的信息,一定要和自己的master的信息一直才行)

1
2
3
4
5
6
7
mysql> change master to
		master_host='138.138.0.95',
		master_port=3307,
		master_user='slave',
		master_password='admin',
		master_log_file='master-bin.000006',
		master_log_pos=822;

注：这里的 master_log_file和master_log_pos就是配置主数据库查询到的File和Position

b) GIT模式

如果使用了gtid模式, 则改为 

1
2
3
4
5
mysql> change master to MASTER_HOST='121.42.14.45',
						master_port=3306,
                        MASTER_USER='slave',
                        MASTER_PASSWORD='Aqgj,123',
                        MASTER_AUTO_POSITION=1;

上面的命令是为了示例的完整性，如果使用GITD配置之前，两台服务器已经是传统的binlog主从服务器，则命令可以更简单：

如果上述报错了 关于@@global.gtid_mode=off, 则先执行

1
set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
c) 传统该GIT模式

如果设置gtid模式以前已经是常规的binlog模式,则只执行以下语句即可

1
mysql> change master to MASTER_AUTO_POSITION=1; #自动跟踪日志名称和位置

2.4 (Slave节点)启动从库

1
start slave;

2.5 (Slave节点)检查是否启动成功

1
show slave status\G;

如果Slave_IO_State字段显示 Waiting for master to send event说明成功，当然你也可以在主库表中插入一条数据，看看从库是否有同步，到这里，已经配置好主从同步了。

Slave_IO_Running和Slave_SQL_Running的状态都为YES才表示同步成功！！！！

般io的报错，都是server_id重复了

查看server_id的方法

1
show variables like 'server_id';

至此，主从同步完成。可自行测试同步效果。

==注意,从节点的同步,只会同步postion之后的操作, 必须在进行同步之前保证主从的数据是一直的, 否则就会导致主节点中的操作在从节点中不存在某表或者某数据导致异常==

3. MySQL主从复制的复制方式

MySQL的主从复制并不完美，存在着几个由来已久的问题，首先一个问题是复制方式：

  • 基于SQL语句的复制（statement-based replication,SBR）
  • 基于行的复制（row-based replication,RBR）
  • 混合模式复制（mixed-based replication,MBR）
  • 全局事务标识符 GTID（Global Transaction Identifier,GTID）

基于SQL语句的方式是最古老的方式，也是目前默认的复制方式，后来的三种是MySQL 5以后才出现的复制方式。

3.1 SBR方式的优缺点

SBR的优点

历史悠久，技术成熟
binlog文件较小
binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况
binlog可以用于实时的还原，而不仅仅用于复制
主从版本可以不一样，从服务器版本可以比主服务器版本高
SBR的缺点：

不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候
复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时，需要比 RBR 请求更多的行级锁
对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记
录产生影响
数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错
执行复杂语句如果出错的话，会消耗更多资源

3.2 RBR方式的优缺点

RBR的优点

任何情况都可以被复制，这对复制来说是最安全可靠的
和其他大多数数据库系统的复制技术一样
多数情况下，从服务器上的表如果有主键的话，复制就会快了很多
RBR 的缺点：binlog 大了很多

复杂的回滚时 binlog 中会包含大量的数据
主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会
导致频繁发生 binlog 的并发写问题
无法从 binlog 中看到都复制了写什么语句

3.3 混合方式

混合方式就是有mysql自动选择RBR方式和SBR方式，能够充分发挥两种方式的优点，一般情况下都使用该种方式实现主从复制

3.4 全局事务标识符 GTID

这种方式虽然能够大大提高主从复制的效率，减小主从复制的延时，但也存在问题，具体请参看下面的博客。
https://blog.csdn.net/guotao521/article/details/45483833
http://hamilton.duapp.com/detail?articleId=47

主从配置异常解决

1. server-id报错

报错内容：The slave I/O thread stops because master and slave have equal MySQL server id

问题重现 执行命令 show slave status\G;

异常点: 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

1.1 server-id排查

主库： 

1
2
3
4
5
6
7
mysql>  show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.01 sec)

从库： 

1
2
3
4
5
6
7
mysql>  show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.01 sec)

若此处id重复了, 则是配置错了, 去修改配置即可, 参考上文第三节的配置

1.2 File 排查

主库:

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从库：

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      306 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

File一样，排除。

1.3 auto.cnf中的server-uuid排查

最后检查发现他们的auto.cnf中的server-uuid是一样的。。。

1
2
3
[root@localhost ~]# vim /var/lib/mysql/auto.cnf
[auto]
server-uuid=4f37a731-9b79-11e8-8013-000c29f0700f

问题解决
停止从库的mysqld服务，删除他的auto.cnf文件，再启动数据库服务即可：

1
2
3
[root@localhost mysql]# systemctl stop mysqld.service
[root@localhost mysql]# mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak
[root@localhost mysql]# systemctl start mysqld.service

此时再去查看从库auto.cnf，已自动生成新的server-uuid：

1
2
3
[root@localhost mysql]# vim /var/lib/mysql/auto.cnf
[auto]
server-uuid=2682888d-994a-11e8-aaf0-000c298cdafc

再查看从库状态，已正常

2. 重置主库的bin-log

手动删除

可以直接删除binlog文件，但是可以通过mysql提供的工具来删除更安全，因为purge会更新mysql-bin.index中的条目，而直接删除的话，mysql-bin.index文件不会更新。mysql-bin.index的作用是加快查找binlog文件的速度。

（1）直接删除

找到binlog所在目录，用rm binglog名 直接删除

例：rm mysql-bin.010

（2）通过mysql提供的工具来删除

删除之前可以先看一下purge的用法：help purge;

删除举例(3种方法)：

  1. RESET MASTER; #删除所有binlog日志，新日志编号从头开始
flush logs; 

    1
    2
    3
    4
    5
    6
    7
    8
    9

    2. 

    3. PURGE MASTER LOGS TO 'mysql-bin.010';[//删除mysql-bin.010之前所有日志](https://xn--mysql-bin-ku4om121d.xn--010-db9d86pgtxn1dj3icvc/)

    4. PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';// 删除2003-04-02 22:46:26之前产生的所有日志


    ### 3. 重置从库
    
MySQL彻底清除slave信息

在我们的MySQL，Master和Slave进行主从切换的时候，Slave成功升级为主库，那么这个时候就需要彻底清理从库的信息，不然监控系统会认为这台服务器是Slave，而且会报主从同步失败。
其实非常的简单，只需要以下两步：

mysql> stop slave;
mysql> reset slave all;

reset slave all;是清除从库的同步复制信息、包括连接信息和二进制文件名、位置。
从库上执行这个命令后，使用show slave status将不会有输出。


 Previous

 Comments