主从复制

server/2025/1/21 12:33:14/

简述mysql 主从复制原理及其工作过程，配置一主两从并验证。

主从原理：MySQL 主从同步是一种数据库复制技术，它通过将主服务器上的数据更改复制到一个或多个从服务器，实现数据的自动同步。

主从同步的核心原理是将主服务器上的二进制日志复制到从服务器，并在从服务器上执行这些日志中的操作。

1.主：

[root@openEuler ~]# yum install -y mysql-server 安装mysql

[root@openEuler ~]# vim /etc/my.cnf.d 编辑my.cnf.d

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id =11

[root@openEuler ~]# systemctl enable --now mysqld 启动服务

[root@openEuler ~]# mysql 进入mysql

mysql> create user rep@'192.168.23.%' identified with mysql_native_password by '123456';

mysql> grant replication slave on *.* to rep@'192.168.23.%'; 创建用户

2.从1：

[root@openEuler-2 ~]# vim /etc/my.cnf.d/mysql-server.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id =12

[root@openEuler-2 ~]# systemctl restart mysqld 重启
[root@openEuler-2 ~]# mysql 进入mysql

mysql> change master to 配置
-> master_host='192.168.23.11',
-> master_user='rep',
-> master_password='123456',
-> master_log_file='binlog.000006',
-> master_log_pos=157;

mysql> start slave；启动复制

mysql> show slave status \G; 查看状态

Slave_IO_Running: Yes 看这两个是否为yes
Slave_SQL_Running: Yes

3.从3

[root@openEuler-3~]# vim /etc/my.cnf.d/mysql-server.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id =13

[root@openEuler-3 ~]# systemctl restart mysqld 重启
[root@openEuler-3 ~]# mysql 进入mysql

mysql> change master to 配置
-> master_host='192.168.23.11',
-> master_user='rep',
-> master_password='123456',
-> master_log_file='binlog.000006',
-> master_log_pos=157;

mysql> start slave；启动复制

mysql> show slave status \G; 查看状态

Slave_IO_Running: Yes 看这两个是否为yes
Slave_SQL_Running: Yes