mysql主从

疑犯

已于 2023-09-07 18:57:09 修改

阅读量154

点赞数 1

文章标签： mysql 数据库

于 2023-09-07 18:10:26 首次发布

本文链接：https://blog.csdn.net/qq_57012538/article/details/132743526

版权

mysql主从

1. 主从简介

MySQL主从复制是一种常见的数据库复制技术，用于实现数据的冗余备份、读写分离和高可用性。它基于主节点（Master）将数据的更改操作同步到从节点（Slave）的机制。以下是MySQL主从复制的简介：

1.角色：

主节点（Master）：主节点是数据的源头，接收并处理所有写操作（INSERT、UPDATE、DELETE）。
从节点（Slave）：从节点复制主节点的数据变更，提供读取操作服务，并可以用作故障切换和数据备份。

2.复制过程：

主节点将数据更改操作记录在二进制日志（Binary Log）中，记录了数据库中发生的修改语句。
从节点连接到主节点，并请求复制主节点的二进制日志，将主节点的数据更改操作应用到自己的数据库中。
从节点通过读取主节点的二进制日志，按照相同的顺序执行其中的数据更改操作，实现与主节点的数据同步。

3.同步方式：

基于语句的复制（Statement-based
Replication）：主节点将数据更改操作记录为SQL语句，从节点执行相同的SQL语句来实现同步。
基于行的复制（Row-based
Replication）：主节点将数据更改操作记录为行级别的改变，从节点直接复制主节点上的数据行，以确保更高的精确性。
混合复制（Mixed Replication）：结合了基于语句的复制和基于行的复制，根据具体情况自动选择合适的方式进行复制。

4.读写分离：

从节点可以独立地处理读取操作，从而减轻主节点的负载。
应用程序可以将读取请求发送到从节点，提高数据库整体的读取性能和响应速度。

5.高可用性和故障切换：

当主节点发生故障或不可用时，可以通过将从节点提升为新的主节点来实现快速的故障切换。
通过使用多个从节点，可以实现更高级别的冗余备份和系统的高可用性。

MySQL主从复制是一种可靠且常用的数据库复制技术，它提供了数据的冗余备份、读写分离和高可用性。通过配置主从复制，可以增加数据库的可扩展性、提高读取性能，并为故障恢复提供了解决方案。

1.1 主从作用

MySQL主从复制的主要作用是实现以下几个方面的功能和需求：

冗余备份：通过主从复制，可以将主节点的数据实时地复制到一个或多个从节点上，从而实现数据的冗余备份。在主节点发生故障或数据丢失的情况下，可以使用从节点作为备份源恢复数据，确保数据的安全性和可靠性。
读写分离：通过将读操作分发到从节点，可以减轻主节点的负载并提高整体系统的读取性能。主节点负责处理写操作，而从节点负责处理读取操作，使系统能够更好地应对高并发读写的场景。
高可用性：通过配置多个从节点并设置适当的故障切换机制，可以实现高可用性。当主节点发生故障或不可用时，可以快速地将一个从节点提升为新的主节点，继续提供数据库服务，从而保证系统的持续可用性。
灾难恢复：在面临数据库灾难性事件时，如硬件故障、自然灾害等，主从复制可以帮助快速恢复数据。通过从节点的数据备份，可以重新构建数据库环境，减少系统停机时间并降低数据丢失风险。
数据分析：可以使用从节点作为数据分析和报表生成的读取副本，避免在主节点上执行复杂的查询操作对业务的影响。从节点可以提供更好的性能和资源支持，以满足数据分析等处理需求。
MySQL主从复制提供了数据的冗余备份、读写分离、高可用性和灾难恢复等重要功能。这些功能可以改善MySQL数据库的性能、可靠性和可扩展性，并为业务系统提供更好的用户体验和高效的数据管理。

1.2 主从形式

一主一从
主主复制
一主多从—扩展系统读取的性能，因为读是在从库读取的
多主一从—5.7开始支持
联级复制
MySQL主从复制可以采用以下两种形式：

单主单从：这是最常见的主从复制形式。在这种模式下，一个主节点（Master）对应一个从节点（Slave）。主节点负责处理写操作，从节点复制主节点的数据变更，并提供读取操作的服务。这种形式适用于大多数应用场景，能够提供数据冗余备份、读写分离和故障切换的优势。

±----------+ ±----------+
| Master | <–> | Slave |
±----------+ ±----------+
主主复制：主主复制也称为双主复制，是一种更为高级和复杂的主从复制形式。在主主复制中，存在两个主节点，每个主节点都负责接收和处理写操作，并将数据变更复制到另一个主节点。这样可以实现双向复制，使得系统更为灵活和可靠。主主复制适用于需要跨数据中心或地理位置的应用场景。

±----------+ ±----------+
| Master | <–> | Master |
±----------+ ±----------+
无论是单主单从还是主主复制，主从复制都通过主节点的二进制日志（Binary Log）记录数据更改操作，并通过从节点读取和应用这些二进制日志实现数据的同步。从节点可以是物理服务器、虚拟机或者基于云平台的实例，它与主节点通过网络连接进行数据同步。

在配置主从复制时，需要考虑主节点和从节点的硬件资源、网络带宽、数据同步延迟等因素，以及合理设置故障切换和监控机制，以确保复制的稳定性和可靠性。

2. 主从复制原理

主从复制步骤：

主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程
从库生成两个线程，一个I/O线程，一个SQL线程
- I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志）文件中
- SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的

3. 主从复制配置

主从复制配置步骤：

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

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

环境说明：

数据库角色	IP	应用与系统版本	有无数据
主数据库	192.168.198.130	centos8/redhat8 mysql-5.7	有数据
从数据库	192.168.198.113	centos8/redhat8 mysql-5.7	无数据

3.1 mysql安装

MySQL的安装与使用

3.2 mysql主从配置

3.2.1 确保从数据库与主数据库里的数据一样

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

//先查看主库有哪些库
[root@RHEL8 ~]# mysql -uroot -pZhouwei123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zhouwei            |
+--------------------+

//查看从库有哪些库
[root@node1 ~]# mysql -uroot -pZhouwei123! -e 'show databases'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+


[root@RHEL8 ~]# mysql -uroot -pZhouwei123!
//全备主库
//全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
mysql> use zhouwei;
Database changed
mysql> create table student(id int not null,name varchar(100) not null,age tinyint);
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_zhouwei |
+-------------------+
| student           |
+-------------------+
1 row in set (0.00 sec)

mysql> insert into student(id,name,age) values(2,'jerry',23),(3,'zhouwei',25),(4,'lisi',NULL);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show tables;
+-------------------+
| Tables_in_zhouwei |
+-------------------+
| student           |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from student;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  2 | jerry   |   23 |
|  3 | zhouwei |   25 |
|  4 | lisi    | NULL |
+----+---------+------+
3 rows in set (0.00 sec)

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> 
//此锁表的终端必须在备份完成以后才能退出

备份主库并将备份文件传送到从库

[root@RHEL8 ~]# mysqldump -uroot -pZhouwei123! --all-databases > /opt/all-20230907.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@RHEL8 ~]# ls /opt/
all-20230907.sql
[root@RHEL8 ~]# scp /opt/all-20230907.sql root@192.168.198.113:/opt/
The authenticity of host '192.168.198.113 (192.168.198.113)' can't be established.
ECDSA key fingerprint is SHA256:OgNuDfFJtB9mhg8ezO1FOIzsog9izo9/pUYxxS+HCjU.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.198.113' (ECDSA) to the list of known hosts.
root@192.168.198.113's password: 
all-20230907.sql                 100%  869KB 121.8MB/s   00:00   

//解除主库的锁表状态，直接退出交互式界面即可
mysql> quit
Bye

从库上面恢复主库

在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@node1 ~]# ls /opt/
all-20230907.sql  data
[root@node1 ~]# mysql -uroot -pZhouwei123! < /opt/all-20230907.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@node1 ~]# mysql -uroot -pZhouwei123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zhouwei            |
+--------------------+

3.2.2 在主数据库里创建一个同步账号授权给从数据库使用

mysql> create user 'repl'@'192.168.198.113' identified by 'Zhouwei123!';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'repl'@'192.168.198.113';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3.2.3 配置主数据库

[root@RHEL8 ~]# vim /etc/my.cnf
//在[mysqld]这段的后面加上如下内容
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=mysql-bin   //启用binlog日志
server-id=10     //数据库服务器唯一标识符，主库的server-id值必须比从库的小

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

重启MySQL服务

[root@RHEL8 ~]# systemctl restart mysqld
[root@RHEL8 ~]# ss -antl
State  Recv-Q Send-Q Local Address:Port  Peer Address:Port Process 
LISTEN 0      128          0.0.0.0:22         0.0.0.0:*            
LISTEN 0      80                 *:3306             *:*            
LISTEN 0      128             [::]:22            [::]:*

查看主库状态

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)

3.2.4 配置从数据库

[root@node1 ~]# vim /etc/my.cnf
//添加如下内容
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=2     //设置从库的唯一标识符，从库的server-id值必须小于主库的该值
relay-log=mysql-relay-bin       //启用中继日志relay-log

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

//重启从库的mysql服务
[root@node1 ~]# systemctl restart mysqld
[root@node1 ~]# ss -antl
State  Recv-Q Send-Q Local Address:Port  Peer Address:Port Process 
LISTEN 0      128          0.0.0.0:111        0.0.0.0:*            
LISTEN 0      32     192.168.122.1:53         0.0.0.0:*            
LISTEN 0      128          0.0.0.0:22         0.0.0.0:*            
LISTEN 0      5          127.0.0.1:631        0.0.0.0:*            
LISTEN 0      128        127.0.0.1:6010       0.0.0.0:*            
LISTEN 0      80                 *:3306             *:*            
LISTEN 0      128             [::]:111           [::]:*            
LISTEN 0      511                *:80               *:*            
LISTEN 0      128             [::]:22            [::]:*            
LISTEN 0      5              [::1]:631           [::]:*            
LISTEN 0      128            [::1]:6010          [::]:*

配置并启动主从复制

mysql> change master to
    -> master_host='192.168.198.130',
    -> master_user='repl',
    -> master_password='Zhouwei123!',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

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


mysql> show slave status \G         //查看从服务器状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.198.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

3.2.5 测试验证

在主服务器的student库的home表中插入数据：

mysql> use zhouwei;
Database changed
mysql> create table home(id int not null,name varchar(100) not null,,age tinyint);
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+-------------------+
| Tables_in_zhouwei |
+-------------------+
| home              |
| student           |
+-------------------+
2 rows in set (0.00 sec)

mysql> insert into home values (1,'xiaojing',18),(2,'zhouwei',23),(3,'zhoujingchu',1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from home;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | xiaojing    |   18 |
|  2 | zhouwei     |   23 |
|  3 | zhoujingchu |    1 |
+----+-------------+------+
3 rows in set (0.00 sec)

在从数据库中查看数据是否同步：

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

mysql> use zhouwei;
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> show tables;
+-------------------+
| Tables_in_zhouwei |
+-------------------+
| home              |
| student           |
+-------------------+
2 rows in set (0.00 sec)

mysql> select * from home;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | xiaojing    |   18 |
|  2 | zhouwei     |   23 |
|  3 | zhoujingchu |    1 |
+----+-------------+------+
3 rows in set (0.00 sec)
-----------------+
| home              |
| student           |
+-------------------+
2 rows in set (0.00 sec)

mysql> select * from home;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | xiaojing    |   18 |
|  2 | zhouwei     |   23 |
|  3 | zhoujingchu |    1 |
+----+-------------+------+
3 rows in set (0.00 sec)