MySQL主从复制实践-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_15764943/article/details/90046036

MySQL_主从复制

简介
方案一
方案二

简介

Mysql主从复制能够实现数据库高可用

方案一

架构图

在这里插入图片描述

搭建主从复制架构

使用的MySQL版本依然是衍生版Percona，版本为5.7.23。并且通过docker进行搭建服务。

主从复制原理

mysql主(称master)从(称slave)复制的原理：
1、master将数据改变记录到二进制日志(binary log)中,也即是配置文件log-bin指定的文件(这些记录叫做二进制日志事件，binary log events)
2、slave将master的binary log events拷贝到它的中继日志(relay log)
3、slave重做中继日志中的事件,将改变反映它自己的数据(数据重演)

基本环境

主DB server和从DB server数据库的版本一致
主DB server和从DB server数据库数据一致
主DB server开启二进制日志,主DB server和从DB server的server_id都必须唯一

步骤

1、主库配置文件my.conf

#开启主从复制，主库的配置 
log-bin = mysql-bin 
#指定主库
serverid server-id=1 
#指定同步的数据库，如果不指定则同步全部数据库 
binlog-do-db=my_test
#执行SQL语句查询状态 
SHOW MASTER STATUS

2、在主库创建同步用户

#授权用户slave01使用123456密码登录
mysql grant replication slave on *.* to 'slave01'@'127.0.0.1' identified by '123456'; 
#刷新配置 
flush privileges;

3、从库配置文件my.conf

#指定serverid，只要不重复即可，从库也只有这一个配置，其他都在SQL语句中操作 
server-id=2
#以下执行SQL： 
CHANGE MASTER TO 
master_host='127.0.0.1', 
master_user='slave01', 
master_password='123456', 
master_port=3306, 
master_log_file='mysql-bin.000006',
master_log_pos=1120;
#启动slave同步 
START SLAVE;
#查看同步状态 
SHOW SLAVE STATUS;

4、搭建主库

#创建目录 
mkdir /data/mysql/master01
cd /data/mysql/master01 
mkdir conf data chmod 777 * -R

#创建配置文件 
cd /data/mysql/master01/conf vim my.cnf

#输入如下内容 
[mysqld] 
log-bin=mysql-bin  #开启二进制日志 
server-id=1  #服务id，不可重复

#创建容器 
docker create --name percona-master01 -v /data/mysql/master01/data:/var/lib/mysql -v /data/mysql/master01/conf:/etc/my.cnf.d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root percona:5.7.23
#启动 
docker start percona-master01 && docker logs -f percona-master01
#创建同步账户以及授权 
create user 'itcast'@'%' identified by 'itcast'; grant replication slave on *.* to 'itcast'@'%'; flush privileges;
#出现 [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and错误解 决方案，在my.cnf配置文件中设置 sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO ,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

#查看master状态 
show master status;
#查看二进制日志相关的配置项 
show global variables like 'binlog%';
#查看server相关的配置项 
show global variables like 'server%';

5、搭建从库

#创建目录 
mkdir /data/mysql/slave01 
cd /data/mysql/slave01 
mkdir conf data 
chmod 777 * -R
#创建配置文件 
cd /data/mysql/slave01/conf 
vim my.cnf
#输入如下内容 
[mysqld] 
server-id=2  #服务id，不可重复
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO ,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
#创建容器 
docker create --name percona-slave01 -v /data/mysql/slave01/data:/var/lib/mysql -v /data/mysql/slave01/conf:/etc/my.cnf.d -p 3307:3306 -e MYSQL_ROOT_PASSWORD=root percona:5.7.23
#启动 
docker start percona-slave01 && docker logs -f percona-slave01
#设置master相关信息 
CHANGE MASTER TO 
master_host='192.168.1.18', 
master_user='itcast', 
master_password='itcast', 
master_port=3306, 
master_log_file='mysql-bin.000002', 
master_log_pos=648;
#启动同步 
start slave;
#查看master状态 
show slave status;

可以进行创建数据库、表，插入数据等操作，可以发现实现了主从。

主从复制模式

参数：binlog_format

在查看二进制日志相关参数内容中，会发现默认的模式为ROW，其实在MySQL中提供了有3种模式，基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixedbased replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。
1、STATEMENT模式（SBR）每一条会修改数据的sql语句会记录到binlog中。
优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-deﬁned functions(udf)等会出现问题)
2、ROW模式（RBR）不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。
3、MIXED模式（MBR）以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用 ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

建议使用MIXED模式。

修改方式：

#修改主库的配置 
binlog_format=MIXED
#重启 
docker restart percona-master01 && docker logs -f percona-master01
#查看二进制日志相关的配置项 
show global variables like 'binlog%';