mysql主从复制

最新推荐文章于 2024-08-29 23:09:51 发布

shuair

最新推荐文章于 2024-08-29 23:09:51 发布

阅读量186

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/shuair/article/details/118314814

版权

mysql 专栏收录该内容

12 篇文章 1 订阅

订阅专栏

准备与说明

搭建一主一从的主从服务
在两台服务器安装两个相同的mysql。可以参考文章：https://blog.csdn.net/shuair/article/details/118393294

约定简称

主机：主mysql所在服务器，主mysql一般用作增删改
从机：从mysql所在服务器，从mysql一般用作查

网络环境

保证主从两台服务器可以互相访问，要分别关闭防火墙，如果服务器有安全组，还要在安全组配置安全策略

查看主机（从机）防火墙状态

systemctl status firewalld

如果防火墙开启，要关闭

systemctl stop firewalld

验证主从服务器之间是否可以相互访问

在主机访问从mysql

mysql -h从机ip -uroot -p

在从机访问主mysql

mysql -h主机ip -uroot -p

主从复制的原理

主mysql（master）把所有的写操作写到Binary log日志文件中，从mysql（slave）读取主mysql的Binary log日志，并写到自己的Relay log（中继日志）里，写完后，再到数据库中执行

mysql主从复制是从接入点开始复制

配置主mysql

编辑主mysql配置文件

vim /etc/my.cnf

在[mysqld]节点添加以下配置

#主服务唯一ID，不能与从机等重复
server-id=1
#启用二进制日志
log-bin=mysql-bin
#设置不要复制的数据库（可配置多个）
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库，设置了需要复制的数据库，就可以不配置忽略的数据库了
binlog-do-db=testdb
#设置binlog日志模式
binlog_format=STATEMENT

只有binlog-do-db配置的数据库才会主从复制，先不要创建主从复制的数据库

binlog日志的三种模式（binlog_format的值）

STATEMENT：复制sql语句，当sql语句中包含获取当前时间、获取当前主机名称等函数时，可能导致数据不一致
ROW：行模式，记录每一行数据的变化，更新语句修改多条数据等操作时，效率较低
MIXED：sql语句中有的函数的时候，使用ROW模式，没有函数时切换成STATEMENT模式。但是该模式识别不了系统变量，如@@hostname（获取当前主机名称）

重启主mysql

systemctl restart mysqld

查看主mysql状态

systemctl status mysqld

配置从mysql

编辑从mysql配置文件

vim /etc/my.cnf

在[mysqld]节点添加以下配置

#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay

重启从mysql

systemctl restart mysqld

查看从mysql状态

systemctl status mysqld

配置好主从mysql后，还需要在主从mysql执行相应的sql语句，才能真正做到主从复制

主mysql执行命令

创建一个只有复制权限的用户，该用户设置为不可给其它用户授权（不包含with grant option）

grant REPLICATION SLAVE on *.* to 'slave（用户名）'@'%' identified by '123123（密码）';

查看主mysql二进制日志文件的状态信息

show master status;

记录binlog日志的文件名（File的值），及接入点（Position的值），暂时不要再操作主mysql，防止主msyql二进制文件状态发生变化

从mysql执行命令

配置执行复制操作的接入信息

CHANGE MASTER TO MASTER_HOST='主机ip',
MASTER_USER='slave（主msyql创建的主从复制的用户）',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='binlog日志名（mysql-bin.数字）',
MASTER_LOG_POS=数值（接入点）;

MASTER_LOG_FILE（二进制文件）、MASTER_LOG_POS（接入点）就是上面主mysql查询的二进制日志文件信息的内容

如果之前配置过主从复制，可能会报错，先停止主从复制功能，再执行配置接入信息

停止主从复制

stop slave;

执行完配置接入信息后，启动主从复制

start slave;

reset master（在主机执行）、reset slave等操作先自行查阅含义，日后补充

查看从机主从复制状态

show slave status\G;

\G表示查询出来的字段分行显示

截图显示部分字段，如果Slave_IO_Running、Slave_SQL_Running 的值都为Yes，则说明主从复制配置成功了

此时在主mysql创建约定好的需要复制的数据库，从库就会同步创建了。

快速验证主从复制

在主mysql执行命令

create database testdb;

use testdb;

create table mytbl(id int,name varchar(20));

insert into mytbl values(1,'zhang3');
insert into mytbl values(2,@@hostname);

观察主从数据库的数据，两个数据库中存储的hostname的值会不同（前提：两台机器的hostname确实不同），原因就在于binlog日志模式配置的是STATEMENT

到此mysql主从复制就配置完成了。