开发者

MySQL主从同步+binlog详解

目录
  • 一、简介
    • 主从复制原理
  • 二、原理
    • 二、配置主从同步
      • 2.1环境准备
      • 2.2主从配置
    • 三、基于binlog的主从同步
      • 3.1【主服务器】
      • 3.2【从服务器】
      • 3.3 GTID 从库误写入操作处理
      • 3.4  IO进程错误
    • 四、延时同步
      • 4.1简介
      • 4.2配置延时同步
    • 总结

      一、简介

      mysql内建的复制功能是构建大型,高性能应用程序的基础 通过将MySQL的某一台主机(master)的数据复制到其他主机(slaves)上,并重新执行一遍来执行 复制过程中一台服务器充当主服务器,而其他一个或多个其他服务器充当从服务器

      主从复制原理

      主要基于MySQL二进制日志 主要包括三个线程(2个I/O线程,1个SQL线程)

      LNMP(Centos7,mysql5.6)

      vmware workstation pro配置了3个虚拟机,均安装了LNMP环境:

      • master: 192.168.0.105       
      • slave:192.168.0.106 、192.168.0.107

      二、原理

      (1)主数据库master进行增删改操作后,相应操作记录的语句(比如 create database test)会记录到binlog日志文件中(binlog日志文件一般和数据库data文件夹在一起)。

      (2)从数据库Slave会请求主数据库的binlog日志文件,拷贝到Slave的中继日志中,然后在自己的从数据库上自动执行相同的操作语句,进而实现主从的同步。

      注:这里,我们所需要配置的只是主从环境以及开启binlog日志,其他的mysql会自动完成。

      二、配置主从同步

      2.1环境准备

      克隆三台没有下过mysql的虚拟机(或恢复快照)

      ip为 

      • master   192.168.1.112 
      • slave1     192.168.1.113
      •  slave2     192.168.1.114

      下载mysql:yum install mysql-server -y

      不会下载或者下载报错:MySQL数据库安装---离线下载

      2.2主从配置

      2.2.1【master】(主服务器)

      1.)选举112为主服务器

      vim /etc/my.cnf
       
      [client]
      user=root
      password=ABc1234
       
      [mysqld]
      datadir=/var/lib/mysql
      socket=/var/lib/mysql/mysql.sock
       
      log-error=/var/log/mysqld. log
      pid-file=/var/run/mysqld/mysqld.pid
       
      server_id=11 #可以是任意正整数,一般比slave大

      然后开启MySQL服务

      2.)在数据库中创建用户,以及授权,修改master库的密码加密方式

      • repl 你所创建的用户名
      • MySQL@123 用户密码
      mysql> create user repl@'%'identified with mysql_native_password by 'MySQL@123'
          -> ;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> grant replication slave on *.* to repl@'%';
      Query OK, 0 rows affected (0.00 sec)
      

      查看主配置状态:show master status;

      MySQL主从同步+binlog详解

      3 )关闭防火墙,禁用SElinux 

      [root@openeuler-1 ~]  systemctl stop firewalld
      python
      [root@openeuler-1 ~]  setenforce 0
      setenforce: SELinux is disabled  #已被禁用
      
      [root@openeuler-1 ~]  getenforce #查看SElinux状态
      Disabled
      

      2.2.2【slave】从服务器

      【slave1】和【slave2】

      1.)配置主配置文件,添加一个server_id(唯一标识)

      vim /etc/my.cnf

      • slave1的   113

      MySQL主从同步+binlog详解

      • slave2的php   114

      MySQL主从同步+binlog详解

      配置后开启/重启mysql服务:systemctl start mysql.service/systemctl restart mysql.service

      2.)在从mysql上进行部署,连接主库(因为连接的是同一个主库,两个slave操作一样)

      ps:如果start slave后,要重新连接主库(change maste to), 必须先stop slave

      • 从库指向主库创建的host,用户名,密码,二进制文件,以及节点
      mysql> change master to
          -> master_host='192.168.1.112',
          -> master_user='repl',
          -> master_password='MySQL@123',
          -> master_log_file='binlog.000001',
          -> master_log_pos=980;
      Query OK, 0 rows affected, 8 warnings (0.01 sec)

      3.)开启主从

      mysql> start slave;
      Query OK, 0 rows affected, 1 warning (0.00 sec)

      4.) 关闭防火墙,禁用SElinux  

      [root@openeuler-1 ~]  systemctl stop firewalld
      
      [root@openeuler-1 ~]  setenforce 0
      setenforce: SELinux is disabled  #已被禁用
      
      [root@openeuler-1 ~]  getenforce #查看SElinux状态
      Disabled
      

      5.) 查看是否成功并测试

      MySQL主从同步+binlog详解

       

      MySQL主从同步+binlog详解

      三、基于binlog的主从同步

      3.1【主服务器】

      1.) 开启binlog

      在id后面添加两行

      [root@openeuler-1 Mysql]# vim /etc/my.cnf
      
      server_id=11
      
      gtid_mode=ON
      enforce-gtid-consistency=ON
      
      [root@openeuler-1 Mysql]# systemctl restart mysqld.service
      

      MySQL主从同步+binlog详解

      3.2【从服务器】

      1.)暂停slave,进入配置文件添加两行,重启服务(重启更改后的配置才会生效)

      mysql> stop slave;
      Query OK, 0 rows affected, 1 warning (0.01 sec)
      
      mysql> \q
      Bye
      
      [root@openeuler-1 Mysql]# vim /etc/my.cnf
      
      server_id=113
      
      gtid_mode=ON
      enforce-gtid-consistency=ON
      
      [randroidoot@openeuler-1 Mysql]# systemctl restart mysqld.service 

      2.)测试gid是否开启

      mysql> show variables like '%gtid%';

      MySQL主从同步+binlog详解

      3.) 暂停slave,连接主库

      mysql> stop slave;
      Query OK, 0 rows affected, 1 warning (0.00 sec)
      
      mysql> change master to
          -> master_host='192.168.1.112',
          -> master_user='repl',
          -> master_password='MySQL@123',
          -> master_auto_position=1;        #使用 GTID 自动定位。
      Query OK, 0 rows affected, 7 warnings (0.00 sec)
      
      mysql> start slave;
      Query OK, 0 rows affected, 1 warning (0.02 sec)

      3.3 GTID 从库误写入操作处理

      从库一般是不需要写入数据的,如果不小心写入一般会报错

      Last_SQL_Error: Error 'Can't create database 'db4'; database exists' on query. Default database: 'db4'. Query: 'create database db4'

      Retrieved_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-3                #说明gid3报错

      Executed_Gtid_Set:  71bfa52e-4aae-11e9-ab8c-000c293b577e:1-2,

      7ca4a2b7-4aae-11e9-859d-000c298720f6:1

      解决方法:注入空事务

      暂停salve:
      stop slave;
      
      #3是报错的ID号
      set gtid_next='99279e1e-61b7-11e9-a9fc-000c2928f5dd:3';
      begin;commit; 
      set gtid_next='AUTOMATIC';
          
      这里的xxxxx:N 也就是你的slave sql thread报错的GTID,或者说是你想要跳过的GTID。
      

      最好的解决方案:重新构建主从环境

      3.4  IO进程错误

      发现IO进程错误,检查日志,排除故障:

      tail localhost.localdomain.err

      常见错误:各个复制的虚拟机server_id不能相同

      解决方法:修改id,重启mysql

      MySQL主从同步+binlog详解

      四、延时同步

      4.1简介

      是我们人为配置的一种特殊同步,从库和主库同步延时N小时

      为什么要有延时同步?

      数据库有两种故障:

      • 物理损坏:可以用主从复制解决。主库rm删除,直接将应用切换到从库
      • 逻辑损坏:普通主从同步无法解决。eg:主库drop database ns,从库也没了

      4.2配置延时同步

      主库加一个参数即可,其他不变

       change master to master_delay=300;  #延时300s

      4.3故障模拟恢复

      MySQL主从同步+binlog详解

      1.)主库数据操作

      mysql> stop slave;
      Query Ok, 0 rows affected, l warning(0.00 sec)
      
      mysal> change master to master_delay=300;  #延时300s
      Query OK, 0 rows affected, 2 warnings (0.0l sec)
      
      mysql> start slave;
      Query OK, 0 rows affected, 1 warning (0.02 sec)
      
      mysq1> show slave status \G
      SQL_Delay: 300
      SQL_Remaining_Delay: NULL

      2.)停止从库SQL线程

      stop slave sql_thread;

      3.) 找relaylog的截取点和终点

      起点:

      Relay_Log_File:db01-relay-bin.000002
      Relay_Log_Pos:482

      终点:

      MySQL主从同步+binlog详解

      4.)  从库恢复relaylog

      source /tmp/relay.sq1

      5.)  从库身份解除

      db0l [relay]>stop slave ;
      db0l [relay]>reset slave all

      总结

      以上为个人经验,希望能给大家一个参考,也希望大家多多android支持编程客栈(www.cpp编程客栈cns.com)。

      0

      上一篇:

      下一篇:

      精彩评论

      暂无评论...
      验证码 换一张
      取 消

      最新数据库

      数据库排行榜