浅谈MySQL8.0 异步复制的三种方式

本实验中分别针对空库、脱机、联机三种方式,配置一主两从的mysql标准异步复制。只做整服务器级别的复制,不考虑对个别库表或使用过滤复制的情况。

实验环境

[root@slave2 ~]# cat /etc/hosts
192.168.2.138 master
192.168.2.192 slave1
192.168.2.130 slave2
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.16  |
+-----------+
1 row in set (0.00 sec)

一、空库

1.查看主库二进制信息

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |   155 |       |         |          |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.在主库上建立复制用户

mysql> create user 'repl'@'%' identified with mysql_native_password by 'wwwwww';
Query OK, 0 rows affected (0.03 sec)
mysql> grant replication client,replication slave on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.04 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3.在从库创建主库信息

mysql> stop slave;
mysql> change master to master_host='192.168.2.138', master_port=3306, master_user='repl', master_password='wwwwww', master_log_file='mysql-bin.000004', master_log_pos=155;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G

刚才我们并没有在从库上建立repl用户,但由于create user语句是在起始位置点后执行的,因此可以正常复制到从库,查询mysql.user表即可确认。

sql> select * from mysql.user where user='repl'\G

二、脱机

如果数据库已经存在应用数据,但允许一个可接受的脱机时间窗口做复制,这种场景下常用的做法是先直接将主库的数据目录整体拷贝到从库,再启动复制。具体步骤如下。

1.在master节点创建测试库和测试表

CREATE DATABASE test;
Query OK, 1 row affected (0.04 sec)
mysql> USE test;
Database changed
mysql> CREATE TABLE t(id int(10));
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO t VALUES (111);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO t VALUES (222);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t VALUES (333);
Query OK, 1 row affected (0.00 sec)

2.在主库创建复制用户

mysql> create user 'repl'@'%' identified with mysql_native_password by 'wwwwww';
Query OK, 0 rows affected (0.03 sec)
mysql> grant replication client,replication slave on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.04 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3.停止复制的所有实例,在master、slave1、slave2分别执行

[root@master ~]# ln -s /usr/local/mysql/bin/mysqladmin /usr/bin/mysqladmin
[root@master ~]# mysqladmin -hlocalhost -uroot -pwwwwww shutdown

4.复制数据至slave1、slave2

[root@master data]# cd /data
[root@master data]# scp -r mysql/ slave1:/data/
[root@master data]# scp -r mysql/ slave2:/data/

5.在slave1、slave2从库执行命令,删除auto.cnf文件

[root@slave1 mysql]# cd /data/mysql
[root@slave1 mysql]# rm -rf auto.cnf
[root@slave2 mysql]# cd /data/mysql
[root@slave2 mysql]# rm -rf auto.cnf

6.重启实例,在三个节点都需要执行

[root@master data]# service mysqld start
Starting MySQL.. SUCCESS!

7.在主库查看二进制日志

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |   155 |       |         |          |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

8.在slave1、slave2从库执行命令

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_host='192.168.2.138', master_port=3306, master_user='repl', master_password='wwwwww', master_log_file='mysql-bin.000005', master_log_pos=155;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G

9.在slave1、slave2从库执行命令查看库和表是否同步过来

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 |
+----------------+
| t       |
+----------------+
1 row in set (0.00 sec)
mysql> select * from t;
+------+
| id  |
+------+
| 111 |
| 222 |
| 333 |
+------+
3 rows in set (0.00 sec)

三、mysqldump联机

脱机建立复制的需求太过理想化,大多数情况下,复制是被要求在不影响线上业务的情况下,联机创建的,而且还要求对线上库的影响越小越好。例如,复制过程化中对主库加锁会影响对主库的访问,因此通常是不被允许的。这种场景下有两种备选的复制方案:使用mysqldump程序或使用如XtraBackup的第三方工具。这两种方案有各自的适用场合。使用mysqldump联机建立复制的过程如下。

1.在主库创建测试的数据库和表

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.04 sec)
mysql> use test;
Database changed
mysql> CREATE TABLE t(id int(10));
Query OK, 0 rows affected (0.10 sec)
mysql> INSERT INTO t VALUES(111);
Query OK, 1 row affected (0.09 sec)
mysql> INSERT INTO t VALUES(222);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t VALUES(333);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO t VALUES(444);
Query OK, 1 row affected (0.00 sec)

2.在主库创建复制用户

mysql> create user 'repl'@'%' identified with mysql_native_password by 'wwwwww';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication client,replication slave on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

3.在slave1、slave2从库创建主库信息

mysql> change master to master_host='192.168.2.138', master_port=3306, master_user='repl', master_password='wwwwww';
Query OK, 0 rows affected, 2 warnings (0.04 sec)

4.在slave1、slave2从库使用mysqldump命令复制数据

[root@slave2 ~]# mysqldump --single-transaction --all-databases --master-data=1 --host=192.168.2.138 --user=root --password=wwwwww --apply-slave-statements | mysql -uroot -pwwwwww -hlocalhost
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.

参数说明

–single-transaction参数可以对Innodb表执行非锁定导出。此选项将事务隔离模式设置为REPEATABLE READ,并在转储数据之前向服务器发送START TRANSACTION SQL语句。它仅适用于Innodb等事务表,因为它会在发出START TRANSACTION时转储数据库的一致状态,而不会阻塞任何应用程序。因此这里假定:1. 所有的应用数据表都使用Innodb引擎。2. 所有系统表数据在备份过程中不会发生变化。

–master-data参数会导致转储输出包含类似 CHANGE MASTER TO MASTER_LOG_FILE=‘binlog.000004', MASTER_LOG_POS=1480; 的SQL语句,该语句指示主库的二进制日志坐标(文件名和位置)。如果选项值为2,则CHANGE MASTER TO语句将写为SQL注释,因此仅提供信息,不会执行。如果参数值为1,则该语句不会写为注释,并在重新加载转储文件时执行。如果未指定选项值,则默认值为1。

–apply-slave-statements参数会在CHANGE MASTER TO语句之前添加STOP SLAVE语句,并在输出结尾处添加START SLAVE语句,用来自动开启复制。

通过管道操作符,导出导入一步进行,不需要中间落盘生成文件。

5.在从库确认复制状态

mysql> show slave status\G

6.在从库查看库和表是否复制成功

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> select * from t;
+------+
| id  |
+------+
| 111 |
| 222 |
| 333 |
| 444 |
| 555 |
+------+
5 rows in set (0.00 sec)

mysqldump方式的优点是可以进行部分复制,如在配置文件中定义replicate-do-table=db1.*,则用这种方法可以只复制db1库而忽略其它复制事件。缺点是由于mysqldump会生成主库转储数据的SQL语句,实际是一种逻辑备份方式所以速度较慢,不适用于大库。

四、XtraBackup联机复制

联机建立复制的另一种可选方案是使用XtraBackup。XtraBackup是Percona公司的开源项目,用以实现类似Innodb官方的热备份工具InnoDB Hot Backup的功能,它支持在线热备份,备份时不影响数据读写。到目前为止,最新的版本为Percona XtraBackup 8.0.6,可以从https://www.percona.com/downloads/下载安装包。XtraBackup有很多功能和优点,例如支持全备、增量备份、部分备份;支持压缩备份;备份不影响数据读写、事务等,但是也有缺陷不足:例如不支持脱机备份、不支持直接备份到磁带设备、不支持Cloud Back,MyISAM的备份也会阻塞。不过这些小瑕疵不影响XtraBackup成为一款流行的MySQL备份工具。另外,注意XtraBackup只支持Linux平台,不支持Windows平台。下面演示用XtraBackup联机搭建主从复制的过程,主库已经建立了用于执行复制的用户repl。

在主库创建复制用户

mysql> create user 'repl'@'%' identified with mysql_native_password by 'wwwwww';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication client,replication slave on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

1.在主库和从库都安装XtraBackupv

[root@master ~]# yum -y install libev
[root@master home]# yum localinstall percona-xtrabackup-80-8.0.6-1.el7.x86_64.rpm -y

2.配置主库到从库的SSH免密码连接

[root@master home]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:GBLbrw17UVck8RyCa/fbYyLkSNZIRc5p+jPQmpkD+bI root@master
The key's randomart image is:
+---[RSA 2048]----+
|  .   .o+o+ |
|   +  +..* . |
|  o o  o*. o |
|   . +.o*..  |
|   ooS+oo .  |
|    =o=Bo . |
|   o.=B++  o |
|    .o..oo..o.|
|    E  . o .|
+----[SHA256]-----+
[root@master home]# ssh-copy-id 192.168.2.138
[root@master home]# ssh-copy-id 192.168.2.192
[root@master home]# ssh-copy-id 192.168.2.130

3.停止从库,并删除从库里面的数据

[root@slave1 home]# service mysql stop
[root@slave2 home]# service mysql stop
[root@slave1 home]# rm -rf /data/mysql/*
[root@slave2 home]# rm -rf /data/mysql/*

4.备份数据并传输

[root@master tmp]# xtrabackup -uroot -pwwwwww --socket=/data/mysql/mysql.sock --no-lock --backup --compress --stream=xbstream --parallel=4 --target-dir=./ | ssh root@192.168.2.192 "xbstream -x -C /data/mysql/ --decompress"

执行过程中报错,

190606 01:21:47 >> log scanned up to (19597291)

190606 01:21:47 Selecting LSN and binary log position from p_s.log_status

Error: failed to fetch query result SELECT server_uuid, local, replication, storage_engines FROM performance_schema.log_status: Access denied; you need (at least one of) the BACKUP_ADMIN privilege(s) for this operation

mysql> grant BACKUP_ADMIN on *.* to 'root'@'%';
Query OK, 0 rows affected (0.01 sec)

行如下命令,删除192.168.2.192:/data/mysql/*的内容,再次执行命令,发现已经正确了。成功执行如下所示:

这条命令连接主库,进行并行压缩流式备份,同时将备份通过管道操作符传输到从库,并直接解压缩到从库的数据目录。所有操作一条命令完成,不需要中间落盘生成文件。

5.在从库恢复备份

[root@slave1 /]# xtrabackup --prepare --target-dir=/data/mysql
[root@slave2 /]# xtrabackup --prepare --target-dir=/data/mysql

6.在从库查看二进制bin-log日志

[root@slave1 mysql]# cat xtrabackup_binlog_info
mysql-bin.000008    155
[root@slave2 mysql]# cat xtrabackup_binlog_info
mysql-bin.000009    155

7.启动从库

[root@slave1 data]# service mysqld start
Starting MySQL... SUCCESS!
[root@slave2 data]# service mysqld start
Starting MySQL... SUCCESS!

8.创建主库信息,其中的master_log_file和master_log_pos值来自第6步

mysql> change master to master_host='192.168.2.138', master_port=3306, master_user='repl', master_password='wwwwww', master_log_file='mysql-bin.000008', master_log_pos=155;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G

9.在从库测试数据

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> select * from t;
+------+
| id  |
+------+
| 111 |
| 222 |
| 333 |
| 444 |
| 555 |
+------+
5 rows in set (0.00 sec)

XtraBackup是物理复制,性能比mysqldump高的多,而且对主库的影响极小,非常适用于从头联机创建高负载、大数据量、全实例从库的场景。

到此这篇关于浅谈MySQL8.0 异步复制的三种方式的文章就介绍到这了,更多相关MySQL8.0 异步复制内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • 深入解析半同步与异步的MySQL主从复制配置

    简单来讲MySQL的主从复制就是一个C/S架构的应用.master可以认为是我们通常意义上所认为的server,slave可以当作是一台client.slave上的I/O线程去请求master上数据,而master验证通过slave的信息后就允许slave接入,然后进行数据变化信息的发送. 一.MySQL主从复制原理 这里我以MySQL5.5为例来说一下MySQL的主从复制的原理: 首先由备节点的I/O线程负责向主节点请求数据,主节点验证通过以后会由dump线程把数据发送给备用节点.备用节点的I

  • 浅谈MySQL8.0 异步复制的三种方式

    本实验中分别针对空库.脱机.联机三种方式,配置一主两从的mysql标准异步复制.只做整服务器级别的复制,不考虑对个别库表或使用过滤复制的情况. 实验环境 [root@slave2 ~]# cat /etc/hosts 192.168.2.138 master 192.168.2.192 slave1 192.168.2.130 slave2 mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.16 |

  • 浅谈Spring解决循环依赖的三种方式

    引言:循环依赖就是N个类中循环嵌套引用,如果在日常开发中我们用new 对象的方式发生这种循环依赖的话程序会在运行时一直循环调用,直至内存溢出报错.下面说一下Spring是如果解决循环依赖的. 第一种:构造器参数循环依赖 表示通过构造器注入构成的循环依赖,此依赖是无法解决的,只能抛出BeanCurrentlyIn CreationException异常表示循环依赖. 如在创建TestA类时,构造器需要TestB类,那将去创建TestB,在创建TestB类时又发现需要TestC类,则又去创建Test

  • 浅谈Java实现分布式事务的三种方案

    一.问题描述 用户支付完成会将支付状态及订单状态保存在订单数据库中,由订单服务去维护订单数据库.由库存服务去维护库存数据库的信息.下图是系统结构图: 如何实现两个分布式服务(订单服务.库存服务)共同完成一件事即订单支付成功自动减库存,这里的关键是如何保证两个分布式服务的事务的一致性. 尝试解决上边的需求,在订单服务中远程调用减库存接口,伪代码如下: 订单支付结果通知方法{ ​ 更新支付表中支付状态为"成功". ​ 远程调用减库存接口减库存. } 上边的逻辑说明: 1.更新支付表状态为本

  • 浅谈Redis对于过期键的三种清除策略

    目录 Pre Redis Key的超时设置处理 被动删除 主动删除 当前已用内存超过maxmemory限定时,触发主动清理策略 对于过期键一般有三种删除策略 定时删除:在设置键的过期时间的同时,创建一个定时器(timer),让定时器在键的过期时间来临时,立即执行对键的删除操作: 惰性删除:放任键过期不管,但是每次从键空间中获取键时,都检查取得的键是否过期,如果过期的话,就删除该键:如果没有过期,那就返回该键: 定期删除:每隔一段时间,程序就对数据库进行一次检查,删除里面的过期键.至于删除多少过期

  • 浅谈java中math类中三种取整函数的区别

    math类中三大取整函数 1.ceil 2.floor 3.round 其实三种取整函数挺简单的.只要记住三个函数名翻译过来的汉语便能轻松理解三大函数,下面一一介绍 1.ceil,意思是天花板,java中叫做向上取整,大于等于该数字的最接近的整数 例: math.ceil(13.2)=14 math.ceil(-13.2)=-13 2.floor,意思是地板,java中叫做向下取整,小于等于该数字的最接近的整数 例: math.floor(13.2)=13 math.floor(-13.2)=-

  • 浅谈react路由传参的几种方式

    第一种传参方式,动态路由传参 通过设置link的path属性,进行路由的传参,当点击link标签的时候,会在上方的url地址中显示传递的整个url <Link to='/home?name=dx'>首页</Link> 如果想真正获取到传递过来的参数,需要在对应的子组件中 this.props.location.search 获取字符串,再手动解析 因为传参能够被用户看见,传递获取比较麻烦,所以不推荐 第二种传参方式,隐式路由传参 <Link to={{ pathname: '

  • 浅谈前端JS沙箱实现的几种方式

    目录 前言 iframe实现沙箱 diff方式实现沙箱 基于代理(Proxy)实现单实例沙箱 基于代理(Proxy)实现多实例沙箱 结束语 参考 前言 在微前端领域当中,沙箱是很重要的一件事情.像微前端框架single-spa没有实现js沙箱,我们在构建大型微前端应用的时候,很容易造成一些变量的冲突,对应用的可靠性面临巨大的风险.在微前端当中,有一些全局对象在所有的应用中需要共享,如document,location,等对象.子应用开发的过程中可能是多个团队在做,很难约束他们使用全局变量.有些页

  • 浅谈Springboot实现拦截器的两种方式

    目录 一.拦截器方式 1.配置HandlerInterceptor 2.注册拦截器 3.使用拦截器的坑 二.过滤器方式 1.实现Filter接口 2.使用过滤器需要注意的 实现过滤请求有两种方式: 一种就是用拦截器,一种就是过滤器 拦截器相对来说比较专业,而过滤器虽然不专业但是也能完成基本的拦截请求要求. 一.拦截器方式 1.配置HandlerInterceptor 下面这个也是我们公司项目拦截器的写法,总体来说感觉还不错,我就记录了下来. 利用了一个静态Pattern变量存储不走拦截器的路径,

  • 浅谈mysql8.0新特性的坑和解决办法(小结)

    一.创建用户和授权 在mysql8.0创建用户和授权和之前不太一样了,其实严格上来讲,也不能说是不一样,只能说是更严格,mysql8.0需要先创建用户和设置密码,然后才能授权. #先创建一个用户 create user 'hong'@'%' identified by '123123'; #再进行授权 grant all privileges on *.* to 'hong'@'%' with grant option; 如果还是用原来5.7的那种方式,会报错误: grant all privi

  • 浅谈Android Studio 解析XML的三种方法

    一丶概述 文件解析要求,json解析和xml解析,前面文章说过Json转实体类,这里就说说解析XML 内容: Android Studio 解析XML常见的三种方式:DOM PULL SAX (实现XML转实体类并打印输出) 效果演示: 二丶正文 SAX(Simple API for XML) 使用流式处理的方式,它并不记录所读内容的相关信息.它是一种以事件为驱动的XML API,解析速度快,占用内存少.使用回调函数来实现. 缺点是不能倒退. DOM(Document Object Model)

随机推荐