MySQL主从

Cinnamorollwq

已于 2022-08-02 10:06:39 修改

阅读量201

点赞数

文章标签： mysql 数据库服务器

于 2022-08-02 10:01:28 首次发布

本文链接：https://blog.csdn.net/cinnamorollwq/article/details/126116224

版权

MySQL主从复制

MySQL主从复制概述

MySQL作为世界上使用最为广泛的数据库之一，免费是其原因之一；但不可忽略的是它本身的功能的确很强大。随着技术的发展，在实际的生产环境中，由单台MySQL数据库服务器不能满足实际的需求。此时数据库集群就很好的解决了这个问题了。采用MySQL分布式集群，能够搭建一个高并发、负载均衡的集群服务器。在此之前我们必须要保证每台MySQL服务器里的数据同步。

MySQL主从复制原理

MySQL服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。

主从复制架构图：

在这里插入图片描述

主从复制又被称为AB复制，主要用于实现数据库集群中的数据同步。实现MySQL的AB复制时，数据库的版本应尽量保持一致。本节将为读者详细介绍主从复制的原理。
在主从复制集群中，主数据库把数据更改的操作记录到二进制日志中，从数据库分别启动I/O线程和SQL线程，用于将主数据库中的数据复制到从数据库中。其中，I/O线程主要将主数据库上的日志复制到自己的中继日志中，SQL线程主要用于读取中继日志中的事件，并将其重放到从数据库之上。另外，系统会将I/O线程已经读取的二进制日志的位置信息存储在master.info文件中，将SQL线程已经读取的中继日志的位置信息存储在relay-log.info文件中。

MySQL主从复制配置

配置环境

配置项	主服务器	从服务器
IP地址	192.168.153.139	192.168.153.140
主机名称	master	slave
版本号	MySQL5.7.38	MySQL5.7.38

MySQL主库配置

关闭防火墙和selinux

[root@master ~]# systemctl disable --now firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@master ~]# setenforce 0
[root@master ~]# vim /etc/selinux/config 
SELINUX=disabled

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

[root@master ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'wq'@'192.168.153.140' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

主库配置文件

[root@master ~]# vim /etc/my.cnf 
log-bin=mysql_bin   
server-id=10
 
[root@master ~]# systemctl restart mysqld.service 
[root@master ~]# mysql -uroot -p1234567 -e "show master status;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

从库配置

关闭防火墙和selinux

[root@slave ~]# systemctl disable --now firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@slave ~]# setenforce 0
[root@slave ~]# vim /etc/selinux/config 
SELINUX=disabled

从库配置文件

[root@slave ~]# vim /etc/my.cnf 
server-id=20						//id要比主库大
relay-log=mysql-relay-bin	//开启中继日志
[root@slave ~]# systemctl restart mysqld.service 
 
 
 
mysql> change master to master_host='192.168.202.139',master_user='wq，master_password='123456',master_log_file='mysql_bin.000001',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 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.202.139
                  Master_User: wq
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000005
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql_bin.000002
             Slave_IO_Running: Yes			//IO进程启动成功
            Slave_SQL_Running: Yes			//SQL进程启动成功

测试验证

//主库：
[root@master ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.38-log MySQL Community Server (GPL)
 
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> create database wq;
Query OK, 1 row affected (0.01 sec)
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wq                 |
+--------------------+
5 rows in set (0.00 sec)
 
mysql> 
 
//从库：
[root@slave ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.38 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wq                 |
+--------------------+
5 rows in set (0.00 sec)

GTID主从

GTID复制简介

功能：主从之间自动校验GTID一致性；将主库binlog，从库binlog，relay-log之间的GTID进行校验，确保主和从之间复制进度保持一致。复制时可以设置从库从第几个GTID开始自动复制。

GTID的工作流程

master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
如果有记录，说明该GTID的事务已经执行，slave会忽略。
如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

GTID主库配置

关闭防火墙和selinux

[root@master ~]# systemctl disable --now firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@master ~]# setenforce 0
[root@master ~]# vim /etc/selinux/config 
SELINUX=disabled

主库配置文件

[root@master ~]# vim /etc/my.cnf 
log-bin=mysql_bin
server-id=10
gtid_mode=on
enforce-gtid-consistency=true
log-slave-updates=on
[root@master ~]# systemctl restart mysqld.service

主库授权复制用户。

mysql> grant replication slave on *.* to 'wq'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

从库配置

关闭防火墙和selinux

[root@slave ~]# systemctl disable --now firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@slave ~]# setenforce 0
[root@slave ~]# vim /etc/selinux/config 
SELINUX=disabled

从库配置文件

[root@slave ~]# vim /etc/my.cnf 
server-id=20
relay-log=myrelay
gtid_mode=on
enforce-gtid-consistency=true
log-slave-updates=on
read_only=on
master-info-repository=TABLE
relay-log-info-repository=TABLE
[root@slave ~]# systemctl restart mysqld.service

从库设置要同步的主库信息，并开启同步

mysql> change master to master_host='192.168.153.139',master_port=3306,master_user='wq',master_password='123456',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.153.139
                  Master_User: wq
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000002
          Read_Master_Log_Pos: 194
               Relay_Log_File: myrelay.000003
                Relay_Log_Pos: 407
        Relay_Master_Log_File: mysql_bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试验证

//主库：
[root@master ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.38-log MySQL Community Server (GPL)
 
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> create database wq;
Query OK, 1 row affected (0.00 sec)
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wq                 |
+--------------------+
5 rows in set (0.00 sec)
 
mysql> 
 
//从库：
[root@slave ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.38 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wq                 |
+--------------------+
5 rows in set (0.00 sec)
 
mysql>