MySQL主从同步(十一)

第1章 MySQL主从同步介绍

1.1 数据库损坏介绍

数据库损坏可以理解为业务不能使用数据库的情况,主要有外在原因和数据库本身的原因两方面。

  • 外在原因:
  1. 网络原因
  2. 业务应用存在问题导致的客户端损坏
  • 数据库本身的原因:
  1. 物理损坏:服务器、硬盘、存储、数据文件的损坏
  2. 逻辑损坏:误 drop 、delete 、truncate ……update等的操作命令
  • 解决方法:
  1. 备份
  2. 主从同步

1.2 MySQL同步简介

复制是 MySQL 的一项功能,允许服务器将更改从一个实例复制到另一个实例。主服务器将所有数据和结构更改记录到二进制日志中。从属服务器从主服务器请求该二进制日志并在本地应用其内容。

1.3 主从同步的作用

  • 高可用
  • 辅助备份
  • 负载分担

1.4 MySQL同步应用场景

  1. 从服务器作为主服务器的实时数据备份
  2. 主从服务器实现读写分离,从服务器实现负载均衡
  3. 把多个从服务器根据业务重要性进行拆分访问

第2章 MySQL同步的结构原理

2.1 MySQL同步原理总览

  1. 至少两台服务器(一主一从或多主多从)通过二进制日志文件进行主从同步
  2. 主服务器的二进制日志自动同步到从服务器上再运行一遍
提示:通过网络连接两台服务器,一般都会出现延迟的状态,所以也可以说是异步的

图片[1]|MySQL主从同步(十一)|leon的博客

2.2 MySQL同步原理详解(第一次开启主从同步)

  1. 从库通过手工执行change master to 语句连接主库,提供了连接的用户一切条件(user、password、port、ip),并且让从库知道,二进制日志的起点位置(file名、position号)
  2. 从库的IO和主库的dump线程建立连接
  3. 从库根据change master to 语句提供的file名和position号,IO线程向主库发起binlog的请求
  4. 主库dump线程根据从库的请求,将本地binlog以events的方式发给从库IO线程
  5. 从库IO线程接收binlog evnets,并放到本地relay-log中,传送过来的信息会记录到info中。
  6. 从库应用relay-log,并且把应用过的记录存放到relay-log.info,默认情况下已经应用过的relay会自动被清理purge

至此第一次主从同步就已经完成,一旦主从运行起来就不需要再手工执行change master to了,因为信息都会被存放到master.info中,之后的每次同步其他的过程都是一样的。

图片[2]|MySQL主从同步(十一)|leon的博客

2.3 主从同步的必要条件

  • 主服务期一定要打开二进制日志
  • 必须两台以上服务器(或者是多个实例)
  • 从服务器需要一次数据初始化
  1. 如果主从服务器都是新搭建的话,可以不做初始化
  2. 如果主服务器已经运行了很长时间,可以通过备份将主库数据恢复到从库。
  • 主库必须要有对从库复制请求的用户。
  • 从库需要有relay-log设置,存放从主库传送过来的二进制日志
  • 在第一次的时候,从库需要change master to 去连接主库。
  • change master信息需要存放到info中
  • 从库是通过relay-log.info记录的已经应用过的relay-log信息感知主库发生了新的变化。
  • 在复制过程中涉及到的线程:
  1. 从库会开启一个IO thread,负责连接主库,请求binlog,接收binlog并写入relay-log。
  2. 从库会开启一个SQL thread,负责执行relay-log中的事件。
  3. 主库会开启一个dump thrad,负责响应从IO thread的请求。

2.3.1 复制中的线程

  • 主库:Dump thread
  • 从库:IO thread、SQL thread

2.3.2 复制中的日志及文件

  • 主库:binlog
  • 从库:Relay-log、Relay-index、info、Relay-log.info

第3章 基本主从同步配置实践

3.1 配置思路

  1. 两个节点
  2. 主库binlog开启,从库开启relay-log
  3. server-id不同
  4. 主库开启复制账户
  5. 主库备份并记录二进制文件和position
  6. 从库change master to连接主库
  7. 启动从库复制
  8. 验证主从

3.2 配置实践

3.2.1 修改配置文件

3.2.1.1 主库配置文件

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server-id = 41

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
port = 3306

log-bin = /usr/local/mysql/data/mysql-bin

[mysqld_safe]
log-error= /tmp/mysql.err
pid-file= /usr/local/mysql/data/mysql.pid

[client]
port = 3306
socket = /tmp/mysql.sock

3.2.1.2 从库配置文件

[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server-id = 42

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
port = 3306

log-bin = /usr/local/mysql/data/mysql-bin

[mysqld_safe]
log-error= /tmp/mysql.err
pid-file= /usr/local/mysql/data/mysql.pid

[client]
port = 3306
socket = /tmp/mysql.sock

3.2.2 主库中创建从库用户

[root@db01 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.17 sec)

3.2.3 全备主库并复制给从库

[root@db01 ~]# mysqldump -uroot -p123456 -A -B -F --master-data=2 > /tmp/full.sql
[root@db01 ~]# scp /tmp/full.sql 10.0.0.42:/tmp/

3.2.4 查看备份文件使用的log-bin文件和position号

[root@db02 ~]# vim /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=120;

3.2.5 从库还原主库备份

[root@db02 ~]# mysql -uroot -p123456
mysql> source /tmp/full.sql;

3.2.6 从库连接主库

[root@db02 ~]# mysql -uroot -p123456
mysql> CHANGE MASTER TO
    ->   MASTER_HOST='10.0.0.41',               # 主库IP地址
    ->   MASTER_USER='repl',                    # 主库中连接从库的用户
    ->   MASTER_PASSWORD='123456',              # 主库中连接从库的密码
    ->   MASTER_PORT=3306,                      # 主库数据库端口号
    ->   MASTER_LOG_FILE='mysql-bin.000002',    # 备份文件中的log-bin文件
    ->   MASTER_LOG_POS=120;                    # 备份文件中的position号
Query OK, 0 rows affected, 2 warnings (0.21 sec)
  • 相关命令:
CHANGE MASTER TO
  MASTER_HOST='10.0.0.41',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=120;

3.2.7 开启从库进程

[root@db02 ~]# mysql -uroot -p123456
mysql> start slave;
Query OK, 0 rows affected (0.13 sec)

3.2.8 验证从库状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.41
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 120
               Relay_Log_File: db02-relay-bin.000003
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes                  # 显示为Yes表示从库线程状态正常
            Slave_SQL_Running: Yes                  # 显示为Yes表示从库线程状态正常
                ...省略部分输出内容...
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
                ...省略部分输出内容...

3.2.9 查看同步情况

3.2.9.1 主库更新数据库

[root@db01 ~]# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.13 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> create table test (              
    -> id int(4) not null auto_increment,
    -> name varchar(20),                
    -> age int(3),                      
    -> primary key (id)                  
    -> );                               
Query OK, 0 rows affected (0.61 sec)

mysql> insert into test values(1,'leon',18),(2,'shadow',20),(3,'shuaige',26),(4,'meinv',19),(5,'wangzi',28);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from test.test;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | leon    |   18 |
|  2 | shadow  |   20 |
|  3 | shuaige |   26 |
|  4 | meinv   |   19 |
|  5 | wangzi  |   28 |
+----+---------+------+
5 rows in set (0.00 sec)

3.2.9.2 从库同步数据库

[root@db02 ~]# mysql -uroot -p123456
mysql> select * from test.test;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | leon    |   18 |
|  2 | shadow  |   20 |
|  3 | shuaige |   26 |
|  4 | meinv   |   19 |
|  5 | wangzi  |   28 |
+----+---------+------+
5 rows in set (0.00 sec)
提示:注意同步时注意需要关闭防火墙和SELinux。

第4章 主从同步问题汇总

4.1 忘记root密码

【问题现象】:

[root@db01 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

【解决方法】:

[root@db01 ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --skip-networking &
[root@db01 ~]# mysql -S /tmp/mysql.sock
mysql> update user set password=password('123456') where user='root' and host='localhost';
mysql> flush privileges;

4.2 Slave_IO_Running: Connecting

【问题现象】:

mysql> show slave status\G;
Last_IO_Error: error reconnecting to master 'repl@10.0.0.52:3306' - retry-time: 60  retries: 1

【原因分析】:

1、主机没启动,或者宕机

2、网络通信问题

3、防火墙未关闭

4、repl用户名或密码发生了变更

4.3 主从库UUID相同

【问题现象】:

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

【解决办法】:

更改任意主库或从库的UUID信息即可:

[root@db02 ~]# uuidgen
0e728e35-2a8a-43a1-ba9a-c1d2b680cf51
[root@db02 ~]# vim /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=0e728e35-2a8a-43a1-ba9a-c1d2b680cf51
[root@db02 ~]# /etc/init.d/mysqld restart

4.4 Master_Log_File中的二进制日志文件落后主库

【问题现象】:

从库中记录的,主库已经给从库传送的binlog事件的坐标,一般在繁忙的生产环境下,会落后于主库(show master status)

Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 729

【原因分析】:

若落后的很多(20以上)的原因可能为:

  1. 网络问题
  2. 主库DUMP太繁忙
  3. 从库IO线程太忙

4.5 Last_SQL_Error

【原因分析】:

  1. 主库做操作的对象,在从库不存在
  2. 主库做操作的对象属性不一致
  3. 主库做操作的对象,从库已经存在
  4. ……等等

第5章 监控主从同步状态

5.1 主从同步关键信息

5.1.1 Slave_*_Running

  • Slave_IO_Running I/O 线程正在运行、未运行还是正在运行但尚未连接到主服务器。可能值分别为Yes、No 或 Connecting。
  • Slave_SQL_Running SQL 线程当前正在运行、未运行,可能值分别为 Yes、No

5.1.2 主服务器日志坐标

  • Master_Log_File 和 Read_Master_Log_Pos 标识主服务器二进制日志中 I/O 线程已经传输的最近事件的坐标。
  • 如果Master_Log_File和Read_Master_Log_Pos 的值远远落后于主服务器上的那些值,这表示主服务器与从属服务器之间事件的网络传输可能存在延迟。

5.1.3 中继日志坐标

  • Relay_Log_File 和 Relay_Log_Pos 列标识从属服务器中继日志中 SQL 线程已经执行的最近事件的坐标。这些坐标对应于 Relay_Master_Log_File 和 Exec_Master_Log_Pos 列标识的主服务器二进制日志中的坐标。
  • 如果 Relay_Master_Log_File 和 Exec_Master_Log_Pos 列的输出远远落后于 Master_Log_File 和Read_Master_Log_Pos 列(表示 I/O 线程的坐标),这表示 SQL 线程(而不是 I/O 线程)中存在延迟。即,它表示复制日志事件快于执行这些事件。

5.1.4 Last_IO_Error、Last_SQL_Error

  • 分别导致 I/O 线程或 SQL 线程停止的最新错误的错误消息。在正常复制过程中,这些字段是空的。如果发生错误并导致消息显示在以上任一字段中,则错误值也显示在错误日志中。

5.1.5 Last_IO_Errno、Last_SQL_Errno

  • 与分别导致 I/O 线程或 SQL 线程停止的最新错误关联的错误编号。在正常复制过程中,这些字段包含编号 0。

5.1.6 Last_IO_Error_Timestamp、Last_SQL_Error_Timestamp

  • 分别导致 I/O 线程或 SQL 线程停止的最新错误的时间戳,格式为 YYMMDD HH:MM:SS。在正常复制过程中,这些字段是空的。

5.2 查看整体状态

[root@db02 ~]# mysql -uroot -p123456 -e "show slave status\G;"
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.41
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 748
               Relay_Log_File: db02-relay-bin.000003
                Relay_Log_Pos: 911
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 748
              Relay_Log_Space: 1083
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 41
                  Master_UUID: 0b1a934f-a67f-11e7-95e2-000c2982d598
             Master_Info_File: /usr/local/mysql-5.6.34/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0

5.3 查看关键线程状态

[root@db02 ~]# mysql -uroot -p123456 -e "show slave status\G;" | egrep "Running|Behind_Master" | head -3
             Slave_IO_Running: Yes              # 获取bin-log并存储到relay-log的线程
            Slave_SQL_Running: Yes              # 运行relay-log中的events到从库的线程
        Seconds_Behind_Master: 0                # 落后主库的时间

第6章 主从同步故障企业案例实战

6.1 企业环境说明

  • 背景:标准主从复制结构,在业务逻辑中有leon数据库,leon数据库下有t1表为生产表。
  • 故障原因:开发人员在从库创建了一个leon1库,觉得不对,后又在主库中做了相同的操作。导致了从库复制失效。

6.2 解决方案

在mysql中可以跳过某些错误。

6.2.1 解决方法一:MySQL语句

mysql> stop slave;                          # 临时停止同步开关
mysql> set global sql_slave_skip_counter=1; # 将同步指针向下移动一个,如果多次不同步可以重复操作
mysql>start slave;                          # 重新启动服务

6.2.2 解决方法二:配置文件

[root@db01 ~]# vim /etc/my.cnf
slave-skip-errors = 1032,1062,1007      # 跳过错误的代码

6.2.3 解决办法三:重新搭建主从复制(推荐)

过程略。

第7章 主从复制架构演变

7.1 基本结构

  • 一主一从:使用从库实时备份。如果从库只做备份服务器用,那么主库的压力会不减反增,因为所有的读写业务都在主库实现,dump线程读取并投递binlog会增加。
  • 一主多从:应对读多的业务需求。一旦发展成这个架构,会出现dump线程读取并投递binlog,导致主库的压力更大了。
  • 多级主从:采用中间库缓解主库dump的压力。但会出现中间库瓶颈的问题:权衡性能和安全后中间库可以选择blackhole引擎适当的提高MySQL的效率。
  • 双主:双主未必能提升写入性能,因为每一个主的写入都会实时同步到另外的上,因此一般生产用的不多。
  • 循环复制

7.2 高级应用架构演变

  • 读写分离:MySQL proxy、amoeba、xx-dbproxy等。
  • 分库分表:cobar、自主研发等。
  • MMM架构:mysql-mmm(google,已经淘汰了)
  • MHA架构:mysql-master-ha(日本DeNa,有人说已经过时,但还可以使用)
  • MGR7新特性 MySQL Group replication
  • PXC、MySQL Cluster架构

7.3 读写分离简介

7.3.1 实现思路

  • 判断语句类型,根据语句类型进行分发
  • 分发的负载均衡
  • 会话持续性
  • 判断语句是否执行过,如果执行过,就不再进行负载均衡。

7.3.2 读写分离软件

  • atlas:配置简单,比较实用
  • 自主开发:依赖于业务
  • cober:依赖与业务

7.4 防止从库误操作

7.4.1 设置从库为只读库

[root@db03 ~]# vim /etc/my.cnf
[mysqld]
read_only = 1   # 重启数据库即可

7.4.2 给从库创建只读用户

跟开发人员明确说明读的时候只给 ruser,写的时候使用wuser(开发制度),添加完成后只要使用新建的ruser和wuser登录数据库进行测试即可:

[root@db01 ~]# mysql -uroot -p123456
# 创建读用户
mysql> grant select on leon.* to ruser@'localhost' identified by '123456';
# 创建写用户
mysql> grant select,insert,update,delete,alter,drop on leon.* to wuser@'localhost' identified by '123456';

7.4.3 选择方式

  • 推荐使用第二种方式:
  • 配置时不用重启
  • 将来如果主库宕机,故障切换时也不需要重启数据库。

7.5 多级主从复制实践

7.5.1 编辑MySQL配置文件

7.5.1.1 主库配置文件

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server-id = 41

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
port = 3306

log-bin = /usr/local/mysql/data/mysql-bin

[mysqld_safe]
log-error= /tmp/mysql.err
pid-file= /usr/local/mysql/data/mysql.pid

[client]
port = 3306
socket = /tmp/mysql.sock

7.5.1.2 中间库配置文件

[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server-id = 42
log-slave-updates

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
port = 3306

log-bin = /usr/local/mysql/data/mysql-bin

[mysqld_safe]
log-error= /tmp/mysql.err
pid-file= /usr/local/mysql/data/mysql.pid

[client]
port = 3306
socket = /tmp/mysql.sock

7.5.1.3 从库配置文件

[root@db03 ~]# vim /etc/my.cnf
[mysqld]
server-id = 43
read_only = 1

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
port = 3306

log-bin = /usr/local/mysql/data/mysql-bin

[mysqld_safe]
log-error= /tmp/mysql.err
pid-file= /usr/local/mysql/data/mysql.pid

[client]
port = 3306
socket = /tmp/mysql.sock

7.5.2 重启服务器

[root@db01 ~]# /etc/init.d/mysqld restart
[root@db02 ~]# /etc/init.d/mysqld restart
[root@db03 ~]# /etc/init.d/mysqld restart

7.5.3 备份当前主库数据

[root@db01 ~]# mysqldump -uroot -p123456 -A -B -F --master-data=2 > /tmp/full.sql

7.5.4 查看备份文件中二进制文件信息

[root@db01 ~]# vim /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=120;

7.5.5 备份文件拷贝到中间库和从库

[root@db01 ~]# scp /tmp/full.sql 10.0.0.42:/tmp
[root@db01 ~]# scp /tmp/full.sql 10.0.0.43:/tmp

7.5.6 还原备份文件并连接主库

7.5.6.1 中间库

[root@db02 ~]# mysql
mysql> source /tmp/full.sql;

mysql> CHANGE MASTER TO
    ->   MASTER_HOST='10.0.0.41',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='123456',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mysql-bin.000011',
    ->   MASTER_LOG_POS=120;

mysql> start slave;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.41
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000011
          Read_Master_Log_Pos: 444
               Relay_Log_File: db02-relay-bin.000030
                Relay_Log_Pos: 607
        Relay_Master_Log_File: mysql-bin.000011
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
             ...省略部分输出内容...

7.5.6.2 从库

[root@db03 ~]# mysql
mysql> source /tmp/full.sql;

mysql> CHANGE MASTER TO
    ->   MASTER_HOST='10.0.0.42',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='123456',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mysql-bin.000011',
    ->   MASTER_LOG_POS=120;

mysql> start slave;

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.42
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000012
          Read_Master_Log_Pos: 120
               Relay_Log_File: db03-relay-bin.000015
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000012
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
             ...省略部分输出内容...

7.5.7 进行测试

7.5.7.1 主库中操作

[root@db01 ~]# mysql -uroot -p123456
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

mysql> create table t1(name varchar(20));
Query OK, 0 rows affected (0.36 sec)

mysql> insert into t1 values('xiaoming');
Query OK, 1 row affected (0.21 sec)

mysql> select * from t1;
+----------+
| name     |
+----------+
| xiaoming |
+----------+
1 row in set (0.00 sec)

7.5.7.2 从库中查看

[root@db03 ~]# mysql -uroot -p123456
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| test           |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from t1;
+----------+
| name     |
+----------+
| xiaoming |
+----------+
1 row in set (0.00 sec)

第8章 主从复制高级功能

8.1 延时复制

8.1.1 延时复制介绍

对于误删除、误修改(delete drop update)类似的逻辑性错误,普通从库可能很快就会应用此错误操作,对于业务是灾难性的。常规处理方法是找备份恢复并应用binlog到错误操作之前。

由此产生了延时从库。正常主从复制的过程是:误操作binlog—>dump THREAD—>IO THREAD—>relay—>SQL THREAD—>从数据库;而延时从库是控制SQL THREAD不实时的进行应用relay-log操作,从而进行延时同步。

通俗的理解延时同步就是人为设置从库延迟一段时间同步主库,以保证防止主库中的误操作对数据的影响。bin-log文件是实时同步到从库,但是延时一段时间再执行,而不是延时一段时间再同步bin-log文件。

延时同步主要应用在备份库中,一般生产中延时3-6小时。

8.1.2 延时复制配置

[root@db02 ~]# mysql -uroot -p123456
mysql> stop slave;
Query OK, 0 rows affected (0.22 sec)

mysql> change master to master_delay = 30;
Query OK, 0 rows affected (0.20 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

8.1.3 查看测试结果

8.1.3.1 主库操作

[root@db01 ~]# mysql -uroot -p123456
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| test           |
+----------------+
2 rows in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.11 sec)

8.1.3.2 从库查看

[root@db02 ~]# mysql -uroot -p123456

mysql> show slave status\G;
*************************** 1. row ***************************
        Seconds_Behind_Master: 22       # 提示还有22秒进行更新

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.01 sec)

8.2 半同步复制

8.2.1 半同步复制介绍

普通主从是异步的,意味着从库同步到什么程度是不可控的,对于安全性要求比较高的应用场景,如金融、运营商等要求数据安全性极高的企业,为了让mysql更加能够适用于高安全性的场景,产生了半同步复制,半同步复制是为了安全,不是为了性能。

8.2.2 配置半同步复制

8.2.2.1 主库配置

[root@db01 ~]# mysql -uroot -p123456
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.29 sec)

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.01 sec)
# 查看是否已经开启延时
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)

8.2.2.2 从库配置

[root@db02 ~]# mysql -uroot -p123456
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.26 sec)

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.10 sec)

mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.11 sec)

mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

# 查看是否已经开启延时
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.11 sec)

8.3 复制故障切换(failover)

8.3.1 故障切换的原理

  • 选择新的主库:尽量找一个数据相对新的节点
  • 数据补偿:判断新主库和其他从节点数据的新旧,将自己没有拿过来,尽量恢复到比较新或者去数据补偿服务器,获取到自己没有的那段binlog。binlog server
  • 启动新主库:将其他从库change master to 到新主库上
  • 告诉应用服务器原来的主库已经不适用了,需要连接新的主库

8.4 5.6新特性:GTID复制

8.4.1 GTID复制简介

全局事务ID,对于一个事务组里的events 打以一个唯一标签,是为了更好地做failover而出现的复制方式,从5.6版本开始支持,5.7版本进行完善。GTID(Global Transaction ID)是对于一个已提交事务的编号,并且是一个全局唯一的编号。

8.4.2 GTID复制配置

8.4.2.1 配置主节点

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server-id = 41                          # 同一个复制拓扑中的所有服务器的id号必须惟一

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
port = 3306

log-bin = /usr/local/mysql/data/mysql-bin
binlog-format=ROW                       # 二进制日志格式,强烈建议为ROW

gtid-mode=on                            # 启用gtid类型,否则就是普通的复制架构
enforce-gtid-consistency=true           # 强制GTID的一致性
log-slave-updates=1                     # slave更新是否记入日志

[mysqld_safe]
log-error= /tmp/mysql.err
pid-file= /usr/local/mysql/data/mysql.pid

[client]
port = 3306
socket = /tmp/mysql.sock

8.4.2.2 配置从节点

[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server-id = 42

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
port = 3306

log-bin = /usr/local/mysql/data/mysql-bin
binlog-format=ROW

gtid-mode=on
enforce-gtid-consistency=true
log_slave_updates = 1

[mysqld_safe]
log-error= /tmp/mysql.err
pid-file= /usr/local/mysql/data/mysql.pid

[client]
port = 3306
socket = /tmp/mysql.sock

8.4.2.3 在主节点创建用户

[root@db01 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.17 sec)

8.4.2.4 从库连接主库

[root@db02 ~]# mysql -uroot -p123456
mysql> CHANGE MASTER TO
    ->   MASTER_HOST='10.0.0.41',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='123456',
    ->   MASTER_PORT=3306,
    ->   MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

8.4.2.5 开启从库线程

[root@db02 ~]# mysql -uroot -p123456
mysql>start slave;
Query OK, 0 rows affected (0.12 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
Auto_Position: 1
温馨提示:本文最后更新于2022-12-20 20:57:51,已超过431天没有更新。某些文章具有时效性,若文章内容或图片资源有错误或已失效,请联系站长。谢谢!
转载请注明本文链接:https://blog.leonshadow.cn/763482/758.html
© 版权声明
THE END
喜欢就支持一下吧
点赞0 分享