MySQL 支持一台主库同时向多台从库进行复制,从库同时也可以作为其他服务器的主库,实现链状复制。
主从复制的好处:
-
主库出现问题,可以快速切换到从库提供服务
-
实现读写分离,降低主库的访问压力
-
可以在从库中执行备份,以避免备份期间影响主库服务
原理
搭建
先准备两台 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;
如果返回信息中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 |
+----+---------+------+
如果需要将主库在开启主从复制之前的数据也同步到从库,要在开启主从复制前,将主库的数据导出并导入从库,然后再开启主从复制。
文章评论