MySQL搭建主从复制流程及相关问题

光头强儿

已于 2022-11-10 10:19:37 修改

阅读量974

点赞数 1

分类专栏： Mysql 文章标签： mysql 数据库服务器

于 2022-11-03 15:48:32 首次发布

本文链接：https://blog.csdn.net/qq_42249896/article/details/127663671

版权

Mysql 专栏收录该内容

15 篇文章 3 订阅

订阅专栏

一、关于主从复制

1.1 关于主从复制

主从复制是将主数据库中的 DDL 和 DML 操作通过二进制日志（binlog）传输到从数据库上，在从数据库上，将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

1.2 应用场景

单库压力大，主从同步后读写分离减轻压力。
单个数据库宕机、损坏则造成数据丢失，业务瘫痪。

1.3 优缺点

优点：

读写分离，提高效率
作为备份，避免数据丢失

缺点：

高并发情况下，加上同步问题，导致MySQL效率低。
主从复制延迟，导致数据没及时更新。

1.4 原理

Binary log：主数据库的二进制日志
Relay log：从服务器的中继日志

在这里插入图片描述
第一步：master 将操作记录到 binlog 日志中。

第二步：slave 的 IO线程读取 master 的 binlog 日志到 relay log 日志中。

第三步：slave 的 SQL 线程读取 relay log 日志，该改变应用到自己的数据库中。

二、配置主从复制

Master：主，简称 M1
Slave：从，简称 S1丶S2

ip地址	主从	在本文中的简称	MySQL版本
49.100.13.244	主库	M1	5.6.50
176.24.33.241	从库	S2	5.6.50
102.34.44.144	从库	S2	5.6.50

2.1 同步各个服务器的时间

2.2 修改主库（M1）配置

修改主库 MySQL 配置文件：vim /etc/my.cnf

server-id = 1   //指定的任意id号，服务器的唯一标识，但是和其它从库不能相同
log-slave-updates=true       //从服务器更新二进制日志

重启 MySQL 服务

systemctl restart mysqld

2.3 主库（M1）为从库（S1\S2）增加账号

GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'176.24.33.%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'102.34.44.%' IDENTIFIED BY '123456';

其中， myslave 是自定义的用户名，后面的（176.24.33.%）是从库的 IP 地址，123456 是账号密码。

PS：例如，我有两个从库（S1、S2），而且每个从库所属 IP 都不一致，根据自己的从库数量来增加相应数量账号即可。

增加好后，刷新权限列表：

flush privileges;#刷新权限列表

2.3 查看主库（M1）日志状态

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

2.4 修改从库（S1） MySQL 配置

下面只举配置从库 S1 的例子，如果你有多个从库 S2、S3、S4……Sn，配置流程都是一模一样的。

修改 S1 的 MySQL 配置文件：

server-id = 2 #指定的任意id号，服务器的唯一标识，但是不能和其它主库或者从库相同
read_only = 1 #只读不写模式

2.5 从库（S1）连接主库（M1）

建立连接：

mysql> change master to master_host='49.100.13.244',master_user='myslave',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=13242693;
Query OK, 0 rows affected, 8 warnings (0.08 sec)

开启复制：start slave

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

查看状态：show slave status\G

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 49.100.13.244
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 13242693
               Relay_Log_File: VM-0-7-centos-relay-bin.000004
                Relay_Log_Pos: 1181
        Relay_Master_Log_File: mysql-bin.000005
             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: 13242693
              Relay_Log_Space: 1396
              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: 0dbd386f-49cb-11ec-940c-00163e12a2c5
             Master_Info_File: /www/server/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)

只要 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes就表示主从复制已打通。

2.5 测试

主库（M1）新建数据库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| diary              |
| mycar              |
| mysql              |
| onlinetool         |
| performance_schema |
| video              |
+--------------------+
7 rows in set (0.00 sec)

mysql> create database slave_test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| diary              |
| mycar              |
| mysql              |
| onlinetool         |
| performance_schema |
| slave_test         |
| video              |
+--------------------+
8 rows in set (0.00 sec)

从库（S1）查看：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| slave_test         |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

三、相关问题

问题一：重启从库 MySQL后是否还要重新 start slave？

不需要，MySQL 重启后自动开启 start slave。

问题二：在搭建主从复制之前，主库本身存在其它数据库，会影响主从复制吗？

会影响。

例如搭建好主从复制后，在主库新建一个 B 数据库，并在 B 数据库新建了一个数据表，添加了一些数据。这些流程都能正常同步到从库中。

但是，

如果此时主库存在 A 数据库，修改了 A 数据库中的某个表，由于从库中原先就没该数据库，导致主从复制失败，无法进行后续的复制操作（即使主库修改的数据存在）。

解决：

方法一：

锁定主从，先将主库数据同步到从库中。

方法二：

通过修改配置文件可以跳过错误，即使主库修改了从库中不存在的数据库或数据表，也不会影响从库的后续主从复制流程。

修改从库的 MySQL 配置文件：

slave-skip-errors=all

问题三：如何给从库设置为只读

方法一：

mysql5.7.8 开始支持 super_read_only 参数，该参数权限要高于 read_only。

将 super_read_only 和 read_only 设置为 on 即可。

mysql> set global super_read_only=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like "%read_only%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | ON    |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)

方法二：

配置文件中：

read_only = 1 #只读不写模式

问题四：MySQL 配置文件的 server-id 必须不一样吗？

是的，必须不一样。

一个 MySQL 服务器对应一个 server-id，是服务器的唯一标识，不能出现相同的 server-id。

否则会报错：

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)