红茶的个人站点

  • 首页
  • 专栏
  • 开发工具
  • 其它
  • 隐私政策
Awalon
Talk is cheap,show me the code.
  1. 首页
  2. 专栏
  3. MySQL学习笔记
  4. 正文

MySQL 从入门到精通 16:主从复制

2025年8月8日 4点热度 0人点赞 0条评论

主从复制是值将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中,然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步。

MySQL 支持一台主库同时向多台从库进行复制,从库同时也可以作为其他服务器的主库,实现链状复制。

image-20250807194915465

主从复制的好处:

  • 主库出现问题,可以快速切换到从库提供服务

  • 实现读写分离,降低主库的访问压力

  • 可以在从库中执行备份,以避免备份期间影响主库服务

原理

image-20250807195353756

搭建

先准备两台 Linux 服务器,并都安装好 MySQL 8。

主库

主库的系统是 RHEL 9。

关闭防火墙:

systemctl stop firewalld
systemctl disable firewalld

或放行 3306 端口:

firewall-cmd --zone=public --add-port=3306/tcp -permanent
firewall-cmd -reload

通过其它电脑测试连接:

telnet 192.168.0.88 3306
Trying 192.168.0.88...
Connected to 192.168.0.88.
Escape character is '^]'.

修改 MySQL 配置文件:

sudo vim /etc/my.cnf.d/mysql-server.cnf

添加配置:

# 主从设置
# mysql服务ID,保证整个集群环境中唯一
server-id=1
# 是否只读,1代表只读,0代表读写
read-only=0

此外,还可以添加主从复制相关的可选参数:

#忽略的数据,指不需要同步的数据库
#binlog-jgnore-db=mysql
#指定同步的数据库
#binlog-do-db=db01

重启服务:

sudo systemctl restart mysqld

创建从库用于主从复制的帐号以及赋予相应权限:

mysql> create user 'icexmoon'@'%' identified with mysql_native_password by 'Mysql@123';
Query OK, 0 rows affected (0.07 sec)
​
mysql> grant replication slave on *.* to 'icexmoon'@'%';
Query OK, 0 rows affected (0.01 sec)

查看主库当前二进制文件写到哪个位置了:

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |      668 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

从库

从库的系统是 Ubuntu。

让防火墙放行 3306 端口:

sudo ufw allow from 192.168.0.0/24 to any port 3306 proto tcp

192.168.0.0/24表示 192.168.0.1~192.168.0.255 的所有 IP 可以访问。如果不限制 IP 可以:

sudo ufw allow 3306/tcp

查看当前的防火墙规则:

sudo ufw status numbered
状态: 激活

     至                          动作          来自
     -                          --          --
[ 1] 22/tcp                     ALLOW IN    Anywhere
[ 2] 3306/tcp                   ALLOW IN    192.168.0.0/24
[ 3] 22/tcp (v6)                ALLOW IN    Anywhere (v6)

如果要删除特定规则,可以:

sudo ufw delete 2

修改 MySQL 配置文件:

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

允许异地客户端连接,修改参数:

bind-address = 0.0.0.0

通过其它电脑测试连接:

telnet 192.168.0.133 3306
Trying 192.168.0.133...
Connected to 192.168.0.133.
Escape character is '^]'.

远程连接数据库:

mysql -h192.168.0.133 -uicexmoon -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.42-0ubuntu0.24.04.2 (Ubuntu)

修改配置文件:

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

添加主从复制相关配置:

#mysql服务ID,保证整个集群环境中唯一,取值范围:1-232-1,和主库不一样即可
server-id=2
#是否只读,1代表只读,0代表读写
read-only=1

重启服务:

sudo systemctl restart mysql

要让从库连接主库,需要在从库执行 SQL:

CHANGE REPLICATION SOURCE TO SOURCE_HOST='XXXx.XXX', SOURCE_USER='xXX', SOURCE_PASSWORD='xXX', SOURCE_LOG_FILE='xXx', SOURCE_LOG_POS=XXX

如果 MySQL 版本<8.0.23,需要使用下面的SQL:

CHANGE MASTER TO MASTER_HOST=/*xXx.xXX.xXX.xXx', MASTER_USER='xxx', MASTER_PASSWORD='xxx', MASTER_LOG_FILE=xxx", MASTER_LOG_POS=XXXx

参数说明:

  • SOURCE_HOST,主库IP

  • SOURCE_USER,从主库拉取数据时使用的账号

  • SOURCE_PASSWORD,帐号的密码

  • SOURCE_LOG_FILE,从哪个二进制日志开始同步

  • SOURCE_LOG_POS,从日志文件的哪个位置开始同步

对于这个示例,实际执行的是:

mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.0.88', SOURCE_USER='icexmoon', SOURCE_PASSWORD='Mysql@123', SOURCE_LOG_FIL
E='binlog.000003', SOURCE_LOG_POS=668;
Query OK, 0 rows affected, 2 warnings (0.06 sec)

最后,执行 SQL 以开始同步:

mysql> start replica;
Query OK, 0 rows affected (0.06 sec)

如果 MySQL 版本<8.0.22,可以执行start slave;。

查看主从复制状态:

mysql> show replica status\G;

image-20250808172139706

如果返回信息中Replica_IO_Running(从主库读取数据的IO线程)与Replica_SQL_Runing(在从库上执行 SQL 以进行数据同步的线程)都是Yes,说明主从同步已经正常运行。

测试

使用 SQL 在主库创建一个数据库,并添加表和数据:

create database db01;

use db01;

create table tb_user(
	id int(11) not null,
	name varchar(50) not null,
	sex varchar(1),
	primary key (id)
)engine=innodb default charset=utf8;

insert into tb_user(id,name,sex) values(1,'Tom','1');
insert into tb_user(id,name,sex) values(2,'Trigger','0');
insert into tb_user(id,name,sex) values(3,'Dawn','1');

insert into tb_user(id,name,sex) values(4,'Jack Ma','1');
insert into tb_user(id,name,sex) values(5,'Coco','0');
insert into tb_user(id,name,sex) values(6,'Jerry','1');

可以在主库和从库都看到这些表和数据。

修改主库的表数据:

mysql> update tb_user set sex=0;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 6  Changed: 4  Warnings: 0

mysql> select * from tb_user;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
|  1 | Tom     | 0    |
|  2 | Trigger | 0    |
|  3 | Dawn    | 0    |
|  4 | Jack Ma | 0    |
|  5 | Coco    | 0    |
|  6 | Jerry   | 0    |
+----+---------+------+

查看从库:

mysql> select * from tb_user;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
|  1 | Tom     | 0    |
|  2 | Trigger | 0    |
|  3 | Dawn    | 0    |
|  4 | Jack Ma | 0    |
|  5 | Coco    | 0    |
|  6 | Jerry   | 0    |
+----+---------+------+

如果需要将主库在开启主从复制之前的数据也同步到从库,要在开启主从复制前,将主库的数据导出并导入从库,然后再开启主从复制。

参考资料

  • 黑马程序员 MySQL数据库入门到精通

  • Ubuntu 安装 MySQL - 红茶的个人站点

  • 使用 SSH 连接 Ubuntu 服务器 - 红茶的个人站点

本作品采用 知识共享署名 4.0 国际许可协议 进行许可
标签: MySQL 主从复制
最后更新:2025年8月8日

魔芋红茶

加一点PHP,加一点Go,加一点Python......

点赞
< 上一篇

文章评论

razz evil exclaim smile redface biggrin eek confused idea lol mad twisted rolleyes wink cool arrow neutral cry mrgreen drooling persevering
取消回复

COPYRIGHT © 2021 icexmoon.cn. ALL RIGHTS RESERVED.
本网站由提供CDN加速/云存储服务

Theme Kratos Made By Seaton Jiang

宁ICP备2021001508号

宁公网安备64040202000141号