MySQL主从复制

原创已于 2022-11-10 11:01:18 修改 · 646 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #服务器

于 2022-07-29 19:29:46 首次发布

本文介绍了MySQL主从复制的原理，包括AB复制的概念、工作流程，以及主从复制集群中的I/O线程和SQL线程作用。此外，还详细阐述了主从复制的配置步骤，确保数据在主从数据库间同步。

主从复制

简介
- 主从复制原理
主从复制配置

简介

数据库运行时，一些因素可能会导致服务运行不正常，用户访问数据受阻。对于互联网公司，尤其是购物网站而言，这种情况造成的损失是无法估量的。因此，对数据库进行“备份”也是必不可少的操作。当主要的数据库死机时，系统能够快速地切换到备用的数据库上。本章将详细介绍数据库集群中的主从复制原理和操作流程。

主从复制原理

主从复制又被称为AB复制，主要用于实现数据库集群中的数据同步。实现MySQL的AB复制时，数据库的版本应尽量保持一致。本节将为读者详细介绍主从复制的原理。
在主从复制集群中，主数据库把数据更改的操作记录到二进制日志中，从数据库分别启动I/O线程和SQL线程，用于将主数据库中的数据复制到从数据库中。其中，I/O线程主要将主数据库上的日志复制到自己的中继日志中，SQL线程主要用于读取中继日志中的事件，并将其重放到从数据库之上。另外，系统会将I/O线程已经读取的二进制日志的位置信息存储在master.info文件中，将SQL线程已经读取的中继日志的位置信息存储在relay-log.info文件中。随着版本的更新，在MySQL 5.6.2之后，MySQL允许将这些状态信息保存在Table中，不过在更新之前需要用户在配置文件中进行声明，具体的参数如下。
[mysqld]
master-info-repository = TABLE -----FILE表示以文件方式
relay-log-info-repository = TABLE -----FILE表示以文件方式

主从复制原理如图所示
在这里插入图片描述

MySQL实现主从复制的前提是作为主服务器的数据库服务器必须开启二进制日志。主从复制集群的工作流程如下。
（1）主服务器上面的任何修改都会通过自己的I/O线程保存在二进制日志里。
（2）从服务器上面也会启动一个I/O线程，通过配置好的用户名和密码连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个中继日志的末端，并将读取到的主服务器端的二进制日志的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚地告诉主服务器：我需要某个二进制日志的某个位置之后的日志内容，请发给我。
（3）从服务器的SQL线程检测到中继日志中新增加了内容后，会马上解析日志中的内容，并在自身执行。需要注意，每个从服务器都会收到主服务器二进制日志中的全部内容的副本，除非另行指定，否则，从服务器将执行来自主服务器二进制日志文件的所有的操作语句。另外，从服务器每次进行同步时，都会记录二进制日志坐标（坐标包含文件名和从主服务器上读取的位置，即master-info），以便下次连接使用。由于每个从服务器分别记录了当前二进制日志的位置，因此可以断开从服务器的连接，重新连接，然后恢复处理。

主从复制配置

主从复制配置步骤：

确保从数据库与主数据库里的数据一样
在主数据库里创建一个同步账号授权给从数据库使用
配置主数据库（修改配置文件）
配置从数据库（修改配置文件）

搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

环境说明：

数据库角色	IP	应用与系统版本	有无数据
主数据库	192.168.40.99	centos8/redhat8mysql-5.7	有数据
从数据库	192.168.40.100	centos8/redhat8mysql-5.7	无数据

此处我使用的mariadb和MySQL没有什么区别

mysql安装分别在主从两台服务器上安装mysql-5.7版本，此处略过安装步骤

1.为确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中

MariaDB [(none)]> use haha;
Database changed
MariaDB [haha]> create table k(id int not null,name varchar(20));
Query OK, 0 rows affected (0.003 sec)

MariaDB [haha]> insert k(id,name) value(1,'haha'),(2,'hehe');
Query OK, 2 rows affected (0.001 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [haha]> select * from k;
+----+------+
| id | name |
+----+------+
|  1 | haha |
|  2 | hehe |
+----+------+
2 rows in set (0.000 sec)

MariaDB [haha]>

2.全备主库
  全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
MariaDB [haha]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.000 sec)  //此锁表的终端必须在备份完成以后才能退出

3.备份主库并将备份文件传送到从库
[root@master ~]# mysqldump -uroot -p123456 --all-databases > all-$(date '+%Y').sql
[root@master ~]# ls
公共  模板  视频  图片  文档  下载  音乐  桌面  all-2022.sql  anaconda-ks.cfg  initial-setup-ks.cfg
[root@master ~]#
[root@master ~]# scp all-2022.sql root@192.168.40.100:/
The authenticity of host '192.168.40.100 (192.168.40.100)' can't be established.
ECDSA key fingerprint is SHA256:CpwzTyA+TDEgnsVkB3eHsGG+klmfSTzcJXmbNJpj5pY.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.40.100' (ECDSA) to the list of known hosts.
root@192.168.40.100's password:
all-2022.sql                                                                                                                                           100%  467KB  91.5MB/s   00:00
[root@master ~]#

解除主库的锁表状态，直接退出交互式界面即可
MariaDB [(none)]> exit
Bye
[root@master ~]#

4.在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@slave ~]# mysql -uroot -p123456 < /all-2022.sql
[root@slave ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.28-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| haha               |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]>

5.在主数据库里创建一个同步账号授权给从数据库使用
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'sb'@'192.168.40.100' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]>

6.配置主数据库
[root@master ~]# vim /etc/my.cnf.d/mariadb-server.cnf
log_bin=zhuku-bin
binlog_ignore_db=mysql
server_id=44    //主库的server-id的大小必须比从库的小

7.重启服务
[root@master ~]# systemctl restart mariadb.service

8.查看主库的状态
MariaDB [(none)]> show master status;
Connection id:    9
Current database: *** NONE ***

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| zhuku-bin.000002 |      342 |              | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.001 sec)

MariaDB [(none)]>

9.配置从数据库
[root@slave ~]# vim /etc/my.cnf.d/mariadb-server.cnf
log_bin=congku-bin
binlog_ignore_db=mysql
server_id=66

10.重启服务
[root@slave ~]# systemctl restart mariadb.service

11.配置并启动主从复制

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.40.99', MASTER_USER='sb', MASTER_PASSWORD='123456', MASTER_LOG_FILE='zhuku-bin.000002 ', MASTER_LOG_POS=342;
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> start slave;

12.查看从服务器状态
MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.40.99
                   Master_User: sb
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: zhuku-bin.000002
           Read_Master_Log_Pos: 342
                Relay_Log_File: mariadb-relay-bin.000010
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: zhuku-bin.000002
              Slave_IO_Running: Yes                //I/O和sql必须yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB:
           Replicate_Ignore_DB:
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:

13.测试验证
主库插入数据
MariaDB [haha]> insert k(id,name) values (8,'hh'),(9,'gg'),(10,'uu'),(11,'ii');
Query OK, 4 rows affected (0.001 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [haha]> select * from k;
+----+--------+
| id | name   |
+----+--------+
|  1 | haha   |
|  2 | hehe   |
|  3 | hll    |
|  4 | hehlle |
|  3 | hll    |
|  4 | hehlle |
|  8 | hh     |
|  9 | gg     |
| 10 | uu     |
| 11 | ii     |
+----+--------+
10 rows in set (0.000 sec)

MariaDB [haha]>

从库查看
MariaDB [(none)]> use haha;
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
MariaDB [haha]> select * from k;
+----+--------+
| id | name   |
+----+--------+
|  1 | haha   |
|  2 | hehe   |
|  3 | hll    |
|  4 | hehlle |
|  8 | hh     |
|  9 | gg     |
| 10 | uu     |
| 11 | ii     |
+----+--------+
8 rows in set (0.000 sec)

MariaDB [haha]>