MySQL_主从复制

分手后的思念是犯贱 2021-10-06 04:20 521阅读 0赞

### MySQL\_主从复制 ###

*  简介
 *  方案一
 *   *  架构图
     *  搭建主从复制架构
     *  主从复制原理
     *  基本环境
     *  步骤
     *  主从复制模式
 *  方案二
 *   *  准备
     *  主库配置
     *  从库配置

# 简介 #

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

# 方案一 #

## 架构图 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE1NzY0OTQz_size_16_color_FFFFFF_t_70]

## 搭建主从复制架构 ##

使用的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%';

# 方案二 #

## 准备 ##

![在这里插入图片描述][20190509191945526.png]

## 主库配置 ##

1、修改my.cnf，添加以下配置  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE1NzY0OTQz_size_16_color_FFFFFF_t_70 1]  
2、创建账号  
![在这里插入图片描述][20190509192028977.png]  
3、授权  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE1NzY0OTQz_size_16_color_FFFFFF_t_70 2]

## 从库配置 ##

1、修改my.cnf，添加以下配置  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE1NzY0OTQz_size_16_color_FFFFFF_t_70 3]  
2、配置主库连接  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE1NzY0OTQz_size_16_color_FFFFFF_t_70 4]  
3、查看主从复制是否完成  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE1NzY0OTQz_size_16_color_FFFFFF_t_70 5]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE1NzY0OTQz_size_16_color_FFFFFF_t_70]: /images/20211006/52c106a5040146af88656227e305c643.png
[20190509191945526.png]: /images/20211006/37e6bbecb34b49fa87eed664a8e8cdbc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE1NzY0OTQz_size_16_color_FFFFFF_t_70 1]: /images/20211006/df8baaa24c4b4fd3aef261c0da0097eb.png
[20190509192028977.png]: /images/20211006/7c1f55713596493ba9985f431c9fe1b3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE1NzY0OTQz_size_16_color_FFFFFF_t_70 2]: /images/20211006/cdb3c962806441c7bb178adf224d061b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE1NzY0OTQz_size_16_color_FFFFFF_t_70 3]: /images/20211006/791d2e7b1d8142daaf096a3601d1d1ed.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE1NzY0OTQz_size_16_color_FFFFFF_t_70 4]: /images/20211006/cb20b4e661cb4c009306c3bdfe30bf82.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE1NzY0OTQz_size_16_color_FFFFFF_t_70 5]: /images/20211006/ae54095c20144784a7e62e4bea5e8797.png