CentOS7.5MySQL5.7主从复制

最新推荐文章于 2024-03-03 16:20:41 发布

小糊涂神0610

最新推荐文章于 2024-03-03 16:20:41 发布

阅读量76

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/qq_42035206/article/details/134093943

版权

1.主从复制简介

1.1.主从复制优点

读写分离。在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
做数据的热备
架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

1.2.主从复制类型

基于语句的复制。在服务器上执行sql语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制，执行效率高。
基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

1.3.主从复制的原理

MySQL的主从复制是一个异步的复制过程，数据将从一个MySQL数据库（Master）复制到另一个MySQL数据库（Slave），在Master和Slave之间实现整个主从复制的过程是由三个线程参与完成的。

Slave端有两个线程：SQL线程和 IO线程。
Master端有一个线程：I/O线程。

要实现MySQL的主从复制，首先必须打开Master端的binlog记录功能，否则就无法实现。 binlog： binary log，是主库中保存所有更新事件日志的二进制文件。因为整个复制过程实际上就是Slave从Master端获取binlog日志，然后在Slave上以相同顺序执行获取的binlog日志中的记录的各种SQL操作。

在 Master上更新数据，写入二进制日志（Binary log），并通知存储引擎提交事务。
Slave 启用 I/O 线程将Master 的 binary log 复制到 Slave 的中继日志（Relay log）。
Slave 启用 SQL 线程从 Relay log 中读取事件，并重放其中的事件从而更新 Slave 数据，使其与Master 中的数据一致，只要该线程与 I/O 线程保持一致，中继日志通常会位于 OS 缓存中，所以中继日志的开销很小。

1.4.主从复制的基本原则

每个Slave只有一个Master
每个Slave只能有一个唯一的服务ID
每个Master可以有多个Slave

2. 主从复制案例

2.1. 环境准备

CentOS 7.5 （三台虚拟机：一主两从）
MySQL 5.7.30
JDK-8u202

2.2. CentOS 7.5 环境

	IP地址	主机名
master	192.168.8.30	mysql30
slave1	192.168.8.31	mysql31
slave2	192.168.8.32	mysql32

master上已经把MySQL安装完成。剩下两台就直接进行克隆。

第一个slave1克隆完成以后，进行以下操作。

设置主机名

# 设置主机名
hostnamectl set-hostname mysql31

# 查看主机名
hostname

设置IP地址（虚拟机网络设置为NAT）

# vim /etc/sysconfig/network-scripts/ifcfg-ens33

IPADDR=192.168.8.31

重启网络服务

systemctl restart network

# 测试网络
ping www.baidu.com

关机防火墙（实验需求临时关闭，生产环境还是要打开）

systemctl stop firewalld        # 停止防火墙服务
systemctl disable firewalld     # 开机不启动防火墙服务

重点

在 /usr/local/mysql/data/ 目录里有个auto.cnf文件，两台虚拟机中的MySQLserver-uuid一定不能不一样。

# slave从服务器
[root@mysql31 ~]# cd /usr/local/mysql/data
[root@mysql31 data]# cat auto.cnf 
[auto]
server-uuid=596c15fb-b4ee-11ec-aec9-000c2930ab81
[root@mysql31 data]# vim auto.cnf 
[root@mysql31 data]# cat auto.cnf 
[auto]
server-uuid=596c15fb-b4ee-11ec-aec9-000c2930ab82

重启MySQL服务

[root@mysql31 data]# systemctl stop mysqld
[root@mysql31 data]# systemctl start mysqld
[root@mysql31 data]#

2.3. master环境配置

配置选项：

配置唯一的 server.id（建议使用IP最后3位）

开启二进制日志

获取master二进制文件名及位置

创建一个用于slave和master通信的用户账号

配置文件 my.cnf 修改

[root@mysql30 mysql]# vim /etc/my.cnf


# 在[mysqld]中添加：
server-id=1
log_bin=mysql-bin
log_bin_index=mysql-bin.index
binlog_ignore_db=mysql

# 备注：
# server-id：服务器唯一标识。
# log_bin：启动MySQL二进制日志，即数据同步语句，从数据库会一条一条的执行这些语句。
# binlog_do_db：指定记录二进制日志的数据库，即需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可。
# binlog_ignore_db 指定不记录二进制日志的数据库，即不需要复制的数据库名，如果有多个数据库，重复设置这个选项即可。
# 其中需要注意的是，binlog_do_db和binlog_ignore_db为互斥选项，一般只需要一个即可。

创建从服务器的用户和权限

# 启动MySQL服务
[root@mysql30 ~]# systemctl status mysqld

# 登录mysql数据库

#进入mysql数据库
[root@mysql30 ~]# mysql -uroot -p
Enter password:
 
#创建从数据库的masterbackup用户和权限
mysql> grant replication slave on *.* to masterbackup@'192.168.8.%' identified by '000000'; 
Query OK, 0 rows affected, 1 warning (0.01 sec)

# 192.168.8.%通配符，表示0-255的IP都可访问主服务器，正式环境请配置指定从服务器IP。
# 若将 192.168.8.% 改为 %，则任何ip均可作为其从数据库来访问主服务器。
 
#退出mysql
mysql> exit;
Bye

重启MySQL服务

[root@mysql30 ~]# systemctl restart mysqld
[root@mysql30 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2022-04-10 21:51:06 CST; 12s ago
     Docs: man:mysqld(8)
 Main PID: 1509 (mysqld_safe)
   CGroup: /system.slice/mysqld.service
           ├─1509 /bin/sh /usr/local/mysql/bin/mysqld_safe
           └─1856 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/...

Apr 10 21:51:06 mysql30 systemd[1]: Stopped MySQL Server.
Apr 10 21:51:06 mysql30 systemd[1]: Started MySQL Server.
Apr 10 21:51:06 mysql30 mysqld_safe[1509]: 2022-04-10T13:51:06.509340Z mysqld_safe Log...'.
Apr 10 21:51:06 mysql30 mysqld_safe[1509]: 2022-04-10T13:51:06.547444Z mysqld_safe Sta...ta
Hint: Some lines were ellipsized, use -l to show in full.

查看主服务器状态

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 |      154 |              | mysql            |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

2.4. slave从服务器的配置

配置选项：

配置唯一的 server.id （建议使用IP最后3位）

使用master分配的用户账号读取master二进制日志

启动slave服务

配置文件 my.cnf 的修改

[root@mysql31 ~]# vim /etc/my.cnf
 
#在[mysqld]中添加：
server-id=2
log-bin=/usr/local/mysql/data/binlog/mysql-bin
relay-log=/usr/local/mysql/data/relay/slave-relay-bin
relay-log-index=/usr/local/mysql/data/relay/slave-relay-bin.index
#replicate-do-db=db1
#replicate-do-db=db2

#备注：
# server-id：服务器唯一标识，如果有多个从服务器，每个服务器的server-id不能重复，跟IP一样是唯一标识，如果你没设置server-id或者设置为0，则从服务器不会连接到主服务器。

# log-bin：开启二进制日志功能，binlog数据位置。如果只写“log-bin=mysql-bin”，文件是放在“/usr/local/mysql/data/”目录下面

# relay-log：启动 MySQL二进制日志，可以用来做数据备份和崩溃恢复，或主服务器挂掉了，将此从服务器作为其他从服务器的主服务器。如果只写“relay-bin=slave-relay-bin”，文件是放在“/usr/local/mysql/data/”目录下面

# relay-log-index：binlog传到备机被写道relaylog里，备机的slave sql线程从relaylog里读取然后应用到本地。

# replicate-do-db：指定同步的数据库，如果复制多个数据库，重复设置这个选项即可。若在master端不指定binlog-do-db，则在slave端可用 replication-do-db来过滤。

# replicate-ignore-db：不需要同步的数据库，如果有多个数据库，重复设置这个选项即可。

# 注意：replicate-do-db和replicate-ignore-db为互斥选项，一般只需要一个即可。

重启mysql服务

# 启动服务
[root@mysql31 ~]# systemctl start mysqld

# 查看mysql服务状态
[root@mysql31 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2022-04-10 21:57:48 CST; 6s ago
     Docs: man:mysqld(8)
 Main PID: 1145 (mysqld_safe)
   CGroup: /system.slice/mysqld.service
           ├─1145 /bin/sh /usr/local/mysql/bin/mysqld_safe
           └─1480 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/...

Apr 10 21:57:48 mysql31 systemd[1]: Started MySQL Server.
Apr 10 21:57:48 mysql31 mysqld_safe[1145]: 2022-04-10T13:57:48.653093Z mysqld_safe Log...'.
Apr 10 21:57:48 mysql31 mysqld_safe[1145]: 2022-04-10T13:57:48.699989Z mysqld_safe Sta...ta
Hint: Some lines were ellipsized, use -l to show in full.

连接master主服务器

#进入mysql数据库
[root@mysql31 ~]# mysql -uroot -p
Enter password:
 
#连接master主服务器
mysql> change master to master_host='192.168.8.30',master_port=3306,master_user='masterbackup',master_password='000000',master_log_file='master-bin.000002',master_log_pos=154;

# 备注：
# master_host对应主服务器的IP地址。
# master_port对应主服务器的端口。
# master_log_file对应show master status显示的File列：master-bin.000002。
# master_log_pos对应show master status显示的Position列：154。

启动slave数据同步

mysql> start slave;

# 停止slave数据同步。
# mysql> start slave;

启动slave

[root@mysql31 data]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.30-log MySQL Community Server (GPL)

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.8.30
                  Master_User: masterbackup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000003
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 528
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 596c15fb-b4ee-11ec-aec9-000c2930ab81
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

Slave_IO_Running 和 Slave_SQL_Running 都为 YES，则表示同步成功。

2.5. 测试

在master创建测试数据

mysql> create database db410;
Query OK, 1 row affected (0.00 sec)
mysql> 
mysql> use db410;
Database changed
mysql> 
mysql> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (1),(2),(3);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

在slave进行查询测试

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db410              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> 

mysql> use db410;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql>

3. 其它

3.1. 常见问题

3.1.1. 主从数据库的UUID相同

一般Slave_IO_Running为No的原因：

主从网络不通；
my.cnf 配置文件有问题；
密码、file文件名、pos偏移量不正确；
防火墙没有关闭；
auto.cnf文件中的 service-uuid 主从相同。

查看slave信息

mysql> show slave status\G;

主从两台数据库的UUID相同了，即data目录下的 auto.cnf 中的 server-uuid值相同了找个字母随便改一下就行，然后重启数据库。

# master主服务器
[root@mysql30 ~]# cd /usr/local/mysql/data/
[root@mysql30 data]# cat auto.cnf 
[auto]
server-uuid=596c15fb-b4ee-11ec-aec9-000c2930ab81
[root@mysql30 data]#

# slave从服务器
[root@mysql31 data]# cat auto.cnf 
[auto]
server-uuid=596c15fb-b4ee-11ec-aec9-000c2930ab81
[root@mysql31 data]# vim auto.cnf 
[root@mysql31 data]# cat auto.cnf 
[auto]
server-uuid=596c15fb-b4ee-11ec-aec9-000c2930ab82

重启MySQL服务

[root@mysql31 data]# systemctl stop mysqld
[root@mysql31 data]# systemctl start mysqld
[root@mysql31 data]#

3.1.2. ERROR 1198

数据库之前已经设置过了主从同步，并且也意见开启了主从同步。

stop slave;
change master to ... （检查最新的File、Position）

3.1.3. ERROR 1201

数据库之前意见设置过了主从同步导致此问题发生。

解决：关闭并充值slave（在slave mysql中执行。）

stop slave;
reset slave;
change master to ... （检查最新的File、Position）

3.2. slave还原独立服务器

slave停止服务

systemctl stop mysqld

删除slave库文件

[root@mysql31 ~]# rm -rf /var/lib/mysql/master.info 
[root@mysql31 ~]# rm -rf /var/lib/mysql/mysql53-relay-bin.*
[root@mysql31 ~]# rm -rf /var/lib/mysql/relay-log.info

修改slave配置文件

[root@mysql31 ~]# vim /etc/my.cnf
[mysqld]
#server_id=2    //删除此行

重启服务查看主从状态

[root@mysql31 ~]# systemctl start mysqld
[root@mysql31 ~]# mysql -uroot -proot -e "show slave  status\G"

3.3. show slave status 参数详解

mysql> show slave status\G;
*************************** 1. row ***************************
# 等待 master 发生事件
Slave_IO_State: Waiting for master to send event

# 当前的主服务器主机
Master_Host: 192.168.8.30

# 被用于连接主服务器的当前用户
Master_User: masterbackup

# 当前的主服务器接口
Master_Port: 3306

# master-connect-retry选项的当前值
Connect_Retry: 60

# “I/O线程”当前正在读取的主服务器二进制日志文件的名称。
Master_Log_File: master-bin.000002

# 在当前的主服务器二进制日志中，I/O线程已经读取的位置
Read_Master_Log_Pos: 154

# SQL线程当前正在读取和执行的中继日志文件的名称
Relay_Log_File: slave-relay-bin.000003

# 在当前的中继日志中，SQL线程已读取和执行的位置   
Relay_Log_Pos: 321

# 由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称    
Relay_Master_Log_File: master-bin.000002

# I/O线程是否被启动并成功地连接到主服务器上。（Yes表示正常，No表示异常）
Slave_IO_Running: Yes

# SQL线程是否被启动 
Slave_SQL_Running: Yes

# replicate-do-db选项的当前值
Replicate_Do_DB: 

# replicate-ignore-db选项的当前值  
Replicate_Ignore_DB: 

# replicate-do-table选项的当前值
Replicate_Do_Table: 

# replicate-ignore-table选项的当前值
Replicate_Ignore_Table: 

# replicate-wild-do-table选项的当前值
Replicate_Wild_Do_Table: 

# replicate-wild-ignore_table选项的当前值
Replicate_Wild_Ignore_Table: 

# 最近一次错误码
Last_Errno: 0

# 最近一次错误内容          
Last_Error: 

# 最近被使用的用于SQL_SLAVE_SKIP_COUNTER的值        
Skip_Counter: 0

# 来自主服务器的二进制日志的由SQL线程执行的上一个时间的位置(Relay_Master_Log_File)。
# 在主服务器的二进制日志中的(Relay_Master_Log_File,Exec_Master_Log_Pos)对应于在中继日志中的(Relay_Log_File,Relay_Log_Pos)               
Exec_Master_Log_Pos: 154

# 所有原有的中继日志结合起来的总大小
Relay_Log_Space: 528

# 如果没有指定UNTIL子句，则没有值。如果从属服务器正在读取，直到达到主服务器的二进制日志的给定位置为止，则值为Master。
# 如果从属服务器正在读取，直到达到其中继日志的给定位置为止，则值为Relay 。  
Until_Condition: None

# 用于指示日志文件名，日志文件名和位置值定义了SQL线程在哪个点中止执行。              
Until_Log_File: 

# 用于指示日志位置值，日志文件名和位置值定义了SQL线程在哪个点中止执行               
Until_Log_Pos: 0

# 如果允许对主服务器进行SSL连接，则值为Yes。如果不允许对主服务器进行SSL连接，则值为No。如果允许SSL连接，但是从属服务器没有让SSL支持被启用，则值为Ignored。
Master_SSL_Allowed: No

# 
Master_SSL_CA_File: 

# 
Master_SSL_CA_Path: 

#
Master_SSL_Cert: 

#
Master_SSL_Cipher: 

#
Master_SSL_Key: 

# 本字段是从属服务器“落后”多少的一个指示。当从属SQL线程正在运行时(处理更新)，本字段为在主服务器上由此线程执行的最近的一个事件的时间标记开始，已经过的秒数。当此线程被从属服务器I/O线程赶上，并进入闲置状态，等待来自I/O线程的更多的事件时，本字段为零。
Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error: 

Last_SQL_Errno: 0

Last_SQL_Error: 

Replicate_Ignore_Server_Ids: 

Master_Server_Id: 1

Master_UUID: 596c15fb-b4ee-11ec-aec9-000c2930ab81

Master_Info_File: /usr/local/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind: 

Last_IO_Error_Timestamp: 

Last_SQL_Error_Timestamp: 

Master_SSL_Crl: 

Master_SSL_Crlpath: 

Retrieved_Gtid_Set: 

Executed_Gtid_Set: 

Auto_Position: 0

Replicate_Rewrite_DB: 

Channel_Name: 

Master_TLS_Version:

3.4.常用语句

# 查看master的状态，尤其是当前的日志及位置
show master status;

# 查看slave的状态
show slave status;

# 重置slave状态
reset slave;

# 启动slave状态
start slave;

# 暂停slave状态
stop slave;

3.5 判断主备无延迟方案

第一种

每次从库执行查询请求前，先判断 seconds_behind_master 是否已经等于0。如果还不等于0，那就必须等到这个参数变为0才能执行查询请求。

第二种

Master_Log_File和Read_Master_Log_Pos表示的是读到的主库的最新位点
Relay_Master_Log_File和Exec_Master_Log_Pos表示的是备库执行的最新位点

如果Master_Log_File和Read_Master_Log_Pos、Relay_Master_Log_File和Exec_Master_Log_Pos这两组值完全相同，就表示接收到的日志已经同步完成。

第三种

对比GTID集合确保主备无延迟：

Auto_Position=1表示这堆主备关系使用了GTID协议
Retrieved_Gitid_Set是备库收到的所有日志的GTID集合
Executed_Gitid_Set是备库所有已经执行完成的GTID集合

如果这两个集合相同，也表示备库接收到的日志都已经同步完成

小糊涂神0610

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
CentOS7.5MySQL5.7主从复制

读写分离。在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。做数据的热备架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
复制链接

扫一扫