主从复制的基本原理

  • slave 会从mast读取binlog来进行数据同步

    三步骤+原理图

  • master将改变记录到二进制日志(binary log)。这些记录过程叫做二进制日志事件，binary log events。

  • slave将master的binary log events拷贝到它的中继日志(relay log)
  • slave重做“中继日志”中的事件，将改变应用到自己的数据库中。MySQL复制是 异步的且串行化的

image.png

复制的基本原则

  • 每个salve只有一个 master
  • 每个salve只能有一个唯一的服务器ID

  • 每个master 可以有多个salve

    复制的最大问题

  • 网络延时

MySQL 数据库一主一从

一主一从的常见配置前提条件

  • mysql 版本一致且后台以服务运行
  • 双向网络是否ping通

  • 主从复制都配置在 [mysqld]节点下面.都是小写

    修改主机配置文件 (my.ini)文件

  • 主服务器唯一ID(必须)

    • server-id=1
  • 启用二进制配置文件(必须)
    • 如下 log-bin=mysql-bin
  • 启用错误日志(可选)
    • log-err=本地mysql路径/mysqlerr
    • 如下 log-err=E:/mysql/mysql-5.7.21-winx64/data/mysqlerr
  • 根目录(可选)
    • basedir=mysql本地路径
    • 如下 basedir=E:/mysql/mysql-5.7.21-winx64
  • 临时目录(可选)
    • tmpdir=mysql本地路径
    • 如下 tmpdir=E:/mysql/mysql-5.7.21-winx64
  • 数据目录(可选)
    • datadir=mysql本地路径/data
    • mysql数据库的数据库[数据目录] 如下 datadir=E:/mysql/mysql-5.7.21-winx64/data
  • read-obly=0
    • 主机,读写都可以
  • 设置不可复制的数据库(可选,不像复制的数据库,填写在下面)
    • binlog-ignore-db=mysql
    • ….
  • 设置需要复制的数据库名
    • binlog-do-db=数据库名
    • ….

  • binlog_format=STATEMEN(默认)

    • 设置binlog格式 三种
    • 1, STATMEN (bin-log中会记录所有的写sql,如果写操作里面包含 函数 now()会造成主从复制不一致)
    • 2,row (不在bin-log中记录写的sql语句,而是记录执行完sql后每一行的改变,这样可以避免函数主从复制不一致的情况,这也是有问题的? 效率低,当表大数据的时候)
    • 3,mixed 判断写操作中是否有函数,如果有函数则使用row 行模式 没有函数则使用 statmen 写模式 问题? 如果出现 @@host_name 系统变量,mixed 也没办法判断

      修改从机my.cnf文件

  • 从服务器唯一ID(必须)

    • server-id=2

  • 启用二进制日志(可选)

    • log-bin=mysql-bin

      重启 主机和从机mysql服务

  • 注意关闭 linux的防火墙主机从机都关闭防火墙

  • 关闭虚拟机linux防火墙
    • service iptables stop
  • win手动关闭

  • linux 查看防火墙状态

    • systemctl status firewalld

      在Windows主机上建立账户并授权slave

  • grant replication slave on . to ‘zhangsan’@’192.168.73.129’ identified by ‘123456’ ;

  • 刷新权限
    • flush privileges;
  • 查看master状态
    • show master status;
  • 记录下File和Position的值

image.png

  • 执行完此操作之后不要再操作主服务器MYSQL，防止主服务器状态值变化

    在Linux从机上配置需要复制的主机

    1. change master to master_host='主机IP',master_user='zhangsan',
    2. master_password='123456', 
    3. master_log_file='File名字',
    4. master_log_pos=Position数字;

    image.png

  • 启动从服务器复制功能

    • start slave；
  • show slave status\G
    • 下面两个参数都是Yes，则说明主从配置成功！
    • Slave_IO_Runing:Yes
    • Slave_SQL_Runing:Yes

主机新建库、新建表、insert记录，测试从机复制

  1.  create database datelog; 
  2.  use datelog; 
  3.  create table dog(id int not null,name varchar(20)); 
  4.  insert into dog values (1,'ddd');
  • 从机测试数据
    1. select * from dog;

如何停止从服务复制功能

  • stop slave;

MySQL 数据库双主双从

一个主机 m1 用于处理所有写请求，它的从机 s1 和另一台主机 m2 还有它的从机 s2 负责所有读请 求。当 m1 主机宕机后，m2 主机负责写请求，m1、m2 互为备机。架构图如下
image.png

编号 角色 IP地址
1 Master1 192.168.140.128
2 Slave1 192.168.140.127
3 Master2 192.168.140.126
4 Slave2 192.168.140.125

双主机配置

Master1配置

  1. 修改配置文件：vim /etc/my.cnf
  3. #主服务器唯一ID
  4. server-id=1
  5. #启用二进制日志
  6. log-bin=mysql-bin
  7. # 设置不要复制的数据库(可设置多个)
  8. binlog-ignore-db=mysql
  9. binlog-ignore-db=information_schema
  10. #设置需要复制的数据库
  11. binlog-do-db=需要复制的主数据库名字
  12. #设置logbin格式
  13. binlog_format=STATEMENT
  14. # 在作为从数据库的时候，有写入操作也要更新二进制日志文件
  15. log-slave-updates 
  16. #表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
  17. auto-increment-increment=2 
  18. # 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
  19. auto-increment-offset=1

Master2配置

  1. 修改配置文件：vim /etc/my.cnf
  3. #主服务器唯一ID
  4. server-id=3 #启用二进制日志
  5. log-bin=mysql-bin
  6. # 设置不要复制的数据库(可设置多个)
  7. binlog-ignore-db=mysql
  8. binlog-ignore-db=information_schema
  9. #设置需要复制的数据库
  10. binlog-do-db=需要复制的主数据库名字
  11. #设置logbin格式
  12. binlog_format=STATEMENT
  13. # 在作为从数据库的时候，有写入操作也要更新二进制日志文件
  14. log-slave-updates 
  15. #表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
  16. auto-increment-increment=2 
  17. # 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
  18. auto-increment-offset=2

双从机配置

Slave1配置

  1. 修改配置文件：vim /etc/my.cnf
  3. #从服务器唯一ID
  4. server-id=2
  5. #启用中继日志
  6. relay-log=mysql-relay

Slave2配置

  1. 修改配置文件：vim /etc/my.cnf
  3. #从服务器唯一ID
  4. server-id=4 #启用中继日志
  5. relay-log=mysql-relay

双主机、双从机重启 mysql 服务

主机从机都关闭防火墙

在两台主机上建立帐户并授权 slave

  1. #在主机MySQL里执行授权命令
  2. GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';
  3. #查询Master1的状态
  4. show master status;

image.png

  1. #查询Master2的状态
  2. show master status;

image.png

  • 分别记录下File和Position的值

  • 执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

    在从机上配置需要复制的主机

    Slava1 复制 Master1，Slava2 复制 Master2

    1. #复制主机的命令
    2. CHANGE MASTER TO MASTER_HOST='主机的IP地址',
    3. MASTER_USER='slave',
    4. MASTER_PASSWORD='123123',
    5. MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

  • Slava1的复制命令

image.png

  • Slava2的复制命令

image.png

启动两台从服务器复制功能

  • start slave;

    查看从服务器状态

  • show slave status\G;

  • Slava1的复制Master1

image.png

  • Slava2的复制Master2

image.png

  • 下面两个参数都是Yes，则说明主从配置成功！

    • Slave_IO_Running: Yes
    • Slave_SQL_Running: Yes

      两个主机互相复制

      Master2 复制 Master1，Master1 复制 Master2

  • Master2的复制命令

image.png

  • Master1的复制命令

image.png

启动两台主服务器复制功能

  • start slave;

    查看从服务器状态

  • show slave status\G;

  • Master2的复制Master1

image.png

  • Master1的复制Master2

image.png

  • 下面两个参数都是Yes，则说明主从配置成功！
    • Slave_IO_Running: Yes
    • Slave_SQL_Running: Yes
  • Master1 主机新建库、新建表、insert 记录，Master2 和从机复制

image.png

如何停止从服务复制功能

  • stop slave;

如何重新配置主从

  • stop slave;
  • reset master;