mysql 主从服务-实现主从复制

主节点与从节点配置

一、主节点与从节点简介

单 MySQL 问题：

1. 性能问题
2. 数据备份问题

多 MySQL 好处：

1. 性能问题 – 不一定提高
2. 数据冗余

MySQL 支持一台主服务器同时向多台从服务器进行复制操作，从服务器同时可以作为其他从服务器的主服务器，如果MySQL主服务器访问量比较大，可以通过复制数据，然后在从服务器上进行查询操作，从而降低主服务器的访问压力，同时从服务器作为主服务器的备份，可以避免主服务器因为故障数据丢失的问题。

MySQL 数据库复制操作大致可以分成三个步骤：

1. 主服务器将数据的更改，记录到二进制日志（binary log）中。
2. 从服务器将主服务器的 binary log events 复制到它的中继日志（real log）中。
3. 从服务器重做中继日志中的事件，将数据的改变与从服务器保持同步。

首先，主服务器会记录二进制日志，每个事务更新数据完成之前，主服务器将这些操作的信息记录在二进制日志里面，在事件写入二进制日志完成后主服务器通知存储引擎提交事务。

准备：了解 binlog 日志，MySQL 用户-权限 ，MySQL服务器配置复制不难，但是因为场景不同可能会存在一定的差异化，总的来说分为以下几步：

1. 在服务器上创建复制账号
2. 通知备库连接到主库并从主库复制数据。

准备服务器 角色 IP 操作系统 mysql版本 端口 复制账号 密码

角色	IP	操作系统	mysql版本	端口	复制账号	复制密码
主 Master	192.168.183.180	CentOS7.6.1810	mysql8.0.21	3306	slave	slave
从 slave1	192.168.183.181	CentOS7.6.1810	mysql8.0.21	3306	…	…
从 slave2	192.168.183.182	CentOS7.6.1810	mysql8.0.21	3306	…	…

对于主从复制，在本质上就是通过从数据库复制主数据库的 binlog 日志文件，通过重做实现的同步；但是一定要注意尽量保证主从服务器上安装了相同的版本的数据库，设定主从的服务器IP地址为192.168.183.180，从服务器的IP地址为192.168.183.181、192.168.183.182…。然后在主服务器上设置一个复制使用的账号，并授予 replication slave 权限。我们可以根据 IP 创建账号为 slave 。

二、Master 主节点配置

主库：

# 进入MySQL库
mysql> use mysql;

#创建账号
mysql> create user 'slave'@'192.168.183.%' identified with mysql_native_password by 'slave';

# 查询用户
mysql> select host,user,plugin from user;
+---------------+------------------+-----------------------+
| host          | user             | plugin                |
+---------------+------------------+-----------------------+
| %             | root             | caching_sha2_password |
| %             | starsky          | mysql_native_password |
| 192.168.183.% | slave            | mysql_native_password |
| localhost     | mysql.infoschema | caching_sha2_password |
| localhost     | mysql.session    | caching_sha2_password |
| localhost     | mysql.sys        | caching_sha2_password |
+---------------+------------------+-----------------------+
6 rows in set (0.00 sec)

# 给用户赋予权限
mysql> grant replication slave on *.* to 'slave'@'192.168.183.%';

对于 MySQL 的主从复制来说最重要的主要就是 binlog 日志，所以我们就需要开启 binlog 日志，并设置 server-id 的值。需要重启服务器之后才生效二进制日志，也就是我们常说的 binlog。

二进制日志记录了 MySQL 所有修改数据库的操作，然后以二进制的形式记录在日志文件中，其中还包括每条语句所执行的时间和消耗的资源，以及相关的事务信息。默认情况下二进制日志功能是没有开启的，启动可以配置 log-bin[=file_name] 开启。

mysql> show global variables like '%log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON （ON 代表开启了日志 ）                |
| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
+---------------------------------+---------------------------------------+
5 rows in set (0.00 sec)

作用就是：

1. 增量备份（不是所有数据备份，而是最近的写操作）
2. 用于 MySQL 主从复制

添加 MySQL 配置文件内容：

[root@localhost ~]# vi /etc/my.cnf

# 在 [mysqld] 下方添加或修改
[mysqld]
log-bin=mysql-bin
server-id=1

# 更改 mysql 配置文件需要重启 mysql 服务

查看 server-id ，主从服务不能重复 server-id

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

三、Slave 从节点配置

注意：对于使用虚拟机的朋友 – 注意克隆之后的系统你需要稍微修改一下系统的 IP 地址

配置网络看这个文章的自行配置IP地址：https://blog.csdn.net/qq_39408664/article/details/118692696#IP_17

要先明确配置的架构 Master-slave

1. 配置主节点 -> 配置账号 / 开启 binlog 日志
2. 配置从节点 -> 配置同步日志 / 指定主节点IP、端口、用户等 / 启动从节点

修改配置

# 打开配置文件
[root@localhost ~]# vi /etc/my.cnf

# 在配置文件中添加或修改
[mysqld]
server-id = 2
relay_log = /usr/local/mysql/data/mysql-relay-bin
relay_log-index = /usr/local/mysql/data/mysql-relay-bin.index
log_slave_updates = 1
read_only = 1

# 更改 mysql 配置文件需要重启 mysql 服务

参数介绍：

1. server_id：这是服务id系统会自动命名的，但如果机器名边画画肯能回答导致问题。可以讲你主库和从库上的log-bin设置为相同的值。
2. relay_log：指定中继日志的位置和命名。

去主服务器查看一下使用的是哪个 binlog 日志

# 查看使用哪个 binlog 日志命令
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 |      156 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从库指定主节点的ip，端口，用户

# 指定主服务器的IP  端口  用户  密码  使用的哪个binlog日志文件  master_log_pos是主库查询使用哪个日志文件的：Position
mysql> change master to master_host='192.168.183.180',master_port=3306,master_user='slave',master_password='slave',master_log_file='mysql-bin.000013',master_log_pos=156;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

# 启动从节点
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

# 查看信息
mysql> show slave status \G;

# 对我们来说信息注意关注的是 第十一行和十二行
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# 这两行同为 Yes ，主从复制就成功了，如果有一个为 No 都不行
# 出现的问题在页面的最后面，往下看

# 停止从节点
mysql> stop slave;

reset slave all 清楚 slave 信息，测试的方法就是在主服务器中添加一些数据测试观察从服务中的数据变化情况。

执行顺序	主库	从库
①
②
③

注：因为测试，所以没做数据统一；上面的数据显示就有区别。

注意：

一、Slave_IO_Running 为 No

Slave_IO_Running: No
Slave_SQL_Running: Yes

报错：对应的错误，大概在第三十四 三十五行

Last_IO_Errno: 13117
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 for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

解决：server-id 重复了

1. 先停止从服务，stop slave; 然后退出连接MySQL。
2. 把从服务器 mysql 的配置文件中更改 server-id ，不能与主库的一样（多个从库也不能一样，只能唯一）。
3. 更改完之后，重启 MySQL 服务。
4. 连接上MySQL，再执行连接主库流程。

二、Slave_SQL_Running: No

Slave_IO_Running: Yes
Slave_SQL_Running: No

报错：对应的错误，大概在第三十六 三十七行
Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows event on table mysql_dump.products; Duplicate entry ‘133893’ for key ‘products.PRIMARY’, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event’s master log mysql-bin.000013, end_log_pos 473

解决：mysql-bin 的文件不一致，需更改

1. 先停止从服务，stop slave;。
2. 去主库查看当前使用的是哪个 mysql-bin 日志，show master status;。
3. 再去从库更改连接主库的信息即可。

三、重启机器

重启机器后，需要重新连接主服务器信息；如果主服务器信息重启，需要检查最新的 bin log 的日志文件，从服务器再进行连接。

四、写入与修改数据问题

写入或修改数据一定要通过主库写入或修改才能同步到从库，一定不能在从库中写入或更改数据，同步不到主库；除非从库改为主库。

五、连接主库问题

报错：

ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL ‘’ first.

解决：

1. 先停止服务，stop slave;。
2. 然后再执行连接，再执行启动：start slave;。