mysql的主从复制

关注

原创最新推荐文章于 2026-03-03 00:05:27 发布 458 阅读 AI 写同款· GEO 优化 ›

(二十四)全解MySQL之主从篇：死磕主从复制中数据同步原理与优化不论任何技术栈，但凡一提高可用、高性能、高稳定这些词汇，必然会牵扯到集群、主从架构的概念，如任何技术栈中都会有，而MySQL中同样不例外，官方也提供了主从架构的支持，通过调整多个MySQL节点的配置信息，即可将一个节点的数据同步给另一个或多个节点，但这种方式同步的是所有数据！有人也许会疑惑：为什么要同步所有数据呀？难道不能对数据作分片，不同的节点同步不同的数据嘛？阅读详情

1.主从简介

在现代企业中，数据显得尤为重要，而存储数据的数据库选择又五花八门，但无论是何种数据库，均存在着一种隐患。

想几个问题：

用一台数据库存放数据，若此数据库服务器宕机了导致数据丢失怎么办？
业务量大了，数据多了，访问的人多了，一台数据库无法保证服务质量了怎么办？

主从作用

实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务

主从形式

一主一从
主主复制
一主多从—扩展系统读取的性能，因为读是在从库读取的
多主一从—5.7开始支持
联级复制

2. 主从复制原理

主从复制步骤：

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

3. 主从复制配置

主从复制配置步骤：

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

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

环境说明：

数据库角色	IP	应用与系统版本	有无数据
主数据库	192.168.10.200	centos8 mysql-5.7	无数据
从数据库	192.168.10.201	centos8 mysql-5.7	无数据

mysql安装

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

mysql主从配置（数据一致）

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

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

##主数据库
[root@master ~]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@master ~]# 

##从数据库
[root@local ~]#  mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

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

mysql>  create user 'repl'@'192.168.10.201' identified by 'repl123';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.10.201';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.10.201';
Query OK, 0 rows affected (0.00 sec)

配置主数据库

[root@master ~]# vim /etc/my.cnf 
[root@master ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

log-bin=mysql-bin
server-id=10

[root@master ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@master ~]# 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         128                   [::]:22                  [::]:*                  
LISTEN    0         80                       *:3306                   *:*

配置从数据库

[root@local ~]# vim /etc/my.cnf 
[root@local ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id=20
relay-log=mysql-relay-bin
[root@local ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.Logging to '/opt/data/local.err'.
 SUCCESS! 
[root@local ~]# 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         128                   [::]:22                  [::]:*                  
LISTEN    0         80                       *:3306                   *:*  

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.10.200',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl123',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> 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> 

mysql> show slave  status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.200
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table:

测试验证

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

mysql> use luochuran;
Database changed
mysql> 
mysql> create table lcr(id int not null,name varchar(50) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into lcr values(1,'ll'),(2,'cc'),(3,'rr');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from lcr;
+----+------+
| id | name |
+----+------+
|  1 | ll   |
|  2 | cc   |
|  3 | rr   |
+----+------+
3 rows in set (0.01 sec)

mysql>

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

mysql> use luochuran;
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> select * from lcr;
+----+------+
| id | name |
+----+------+
|  1 | ll   |
|  2 | cc   |
|  3 | rr   |
+----+------+
3 rows in set (0.00 sec)

mysql>

主从复制配置（数据不一致）

主从复制配置步骤：

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

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

环境说明：

数据库角色	IP	应用与系统版本	有无数据
主数据库	192.168.10.200	centos8 mysql-5.7	有数据
从数据库	192.168.10.201	centos8 mysql-5.7	无数据

mysql主从配置

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

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

##主数据库
[root@localhost ~]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| LQL                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

##从数据库
[root@localhost ~]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

##全备主从数据库
##全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
##此锁表的终端必须在备份完成以后才能退出（解除主库的锁表状态，直接退出交互式界面即可）
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)
##备份主库
[root@localhost ~]# mysqldump -uroot -p123 --all-databases > /opt/all-$(date '+%Y%m%d').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls /opt/
all-20210830.sql  data
##将主库的备份文件传送到从数据库
[root@localhost ~]# scp /opt/all-20210830.sql root@192.168.240.60:/opt/
The authenticity of host '192.168.10.201 (192.168.10.201)' can't be established.
ECDSA key fingerprint is SHA256:YDPxPR7Nqao+hwjww9kCBH/G0mbpOsvhSahI6YKiRhQ.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.10.201' (ECDSA) to the list of known hosts.
root@192.168.10.201's password: 
all-20210830.sql                                         100%  852KB  93.9MB/s   00:00    

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

剩下的跟mysql主从配置（数据一致）一样步骤