贵州省住房和城乡建设部官方网站,如何接广告赚钱,wordpress快速安装,网站建设的7个基本流程[toc]1. 需求部分1.1 基于MySQL复制同步特性#xff0c;尝试使用Replication的SQL线程来回放binlog#xff0c;可基于以下逻辑模拟场景做全量xtrabackup备份模拟日常备份执行sysbench压测4张表#xff0c;20个线程#xff0c;压测10分钟#xff0c;模拟大量binlog删除实例…[toc]1. 需求部分1.1 基于MySQL复制同步特性尝试使用Replication的SQL线程来回放binlog可基于以下逻辑模拟场景做全量xtrabackup备份模拟日常备份执行sysbench压测4张表20个线程压测10分钟模拟大量binlog删除实例模拟数据库被误删除或硬件故障(binlog需要保留)使用xtrabackup恢复全量备份使用MySQL Replication SQL线程回放binlog(提示:恢复前需要将relay_log_recocery参数设置为0)1.2 基于题目1.1考虑是否可以做到以下场景的恢复只恢复单个库的数据只恢复单个表的数据将数据恢复到指定的GTID或者position点(如恢复到误操作drop之前的GTID)是否可以通过参数调整提升回放效率列出提升效率的参数并实际测试用SQL线程回放binlog和直接使用mysqlbinlog回放binlog的性能对比2.操作部分2.1 环境准备及故障模拟使用mysqlbinlog将binlog文件实时转储到备份目录(当前环境备份目录配置在/data/mysql/backup)[root10-186-61-162 ~]# mkdir -p /data/mysql/backup/binlog[root10-186-61-162 ~]# mysqlbinlog --raw --read-from-remote-server -h10.186.61.162 -urepl -pxxx -P3306 --result-file/data/mysql/backup/binlog/ mysql-bin.000001 --stop-never --stop-never-slave-server-id100 生成sysbench测试数据,创建4张表每张各100W数据库并压测一分钟模拟数据变化## 插入基础数据[root10-186-61-162 ~]# sysbench /usr/local/share/sysbench/oltp_read_write.lua --db-ps-modedisable --mysql-host127.0.0.1 --mysql-port3306 --mysql-usersysbench --mysql-passwordxxx --mysql-dbsbtest --tables4 --table-size1000000 --report-interval1 --time600 --threads20 prepare## 执行sysbench压测模拟数据变化压测1分钟[root10-186-61-162 ~]# sysbench /usr/local/share/sysbench/oltp_read_write.lua --db-ps-modedisable --mysql-host127.0.0.1 --mysql-port3306 --mysql-usersysbench --mysql-passwordxxx --mysql-dbsbtest --tables4 --table-size1000000 --report-interval1 --time60 --threads20 run执行物理备份模拟每天的全量备份[root10-186-61-162 ~]# innobackupex --host10.186.61.162 --port3306 --userbackup --passwordxxx /data/mysql/backup/继续执行压测模拟备份后的数据变化压测10分钟模拟大量binlog[root10-186-61-162 ~]# sysbench /usr/local/share/sysbench/oltp_read_write.lua --db-ps-modedisable --mysql-host127.0.0.1 --mysql-port3306 --mysql-usersysbench --mysql-passwordxxx --mysql-dbsbtest --tables4 --table-size1000000 --report-interval1 --time600 --threads20 run停止并删除数据库数据库模拟误操作或硬件故障[root10-186-61-162 ~]# systemctl stop mysql_3306[root10-186-61-162 ~]# rm -rf /data/mysql/data/*2.2 数据恢复还原xtrabackup全量备份[root10-186-61-162 ~]# cd /data/mysql/backup[root10-186-61-162 backup]# innobackupex --apply-log 2020-03-02_17-39-07/[root10-186-61-162 backup]# innobackupex --defaults-file/etc/my.cnf --copy-back 2020-03-02_17-39-07/将binlog转换为relay log并拷贝到数据目录以便SQL线程回放数据[root10-186-61-162 backup]# cd /data/mysql/backup/binlog/## 将binlog重命名为relay log[root10-186-61-162 binlog]# rename mysql-bin mysql-relay mysql-bin*## 创建relay log index文件[root10-186-61-162 binlog]# ls ./mysql-relay.0000* mysql-relay.index## 拷贝relay log到数据目录[root10-186-61-162 binlog]# cp mysql-relay.* /data/mysql/data/## 修改数据目录权限[root10-186-61-162 binlog]# chown -R mysql:mysql /data/mysql/data/修改relay_log_recovery参数设置为0并启动数据库,relay_log_recovery为1时relay log会在复制线程启动时被清除重新拉## 获取备份文件中的binlog位点信息及GTID点对应的relay log文件[root10-186-61-162 ~]# cd /data/mysql/backup/2020-03-02_17-39-07/[root10-186-61-162 2020-03-02_17-39-07]# cat xtrabackup_binlog_infomysql-bin.00000780456866f41abe78-5c62-11ea-abf1-02000aba3da2:1-52013## 启动数据库[root10-186-61-162 2020-03-02_17-39-07]# systemctl start mysql_3306## change master指定一个空的主库创建SQL线程rootlocalhost[(none)] reset master;## 指定备份文件中对应的binlog位点rootlocalhost[(none)] CHANGE MASTER TO MASTER_HOST1.1.1.1,RELAY_LOG_FILEmysql-relay.000007,RELAY_LOG_POS80456866;## 查看指定的位点是否生效rootlocalhost[(none)] select * from mysql.slave_relay_log_info;---------------------------------------------------------------------------------------------------------------------------------------| Number_of_lines | Relay_log_name | Relay_log_pos | Master_log_name | Master_log_pos | Sql_delay | Number_of_workers | Id | Channel_name |---------------------------------------------------------------------------------------------------------------------------------------| 7 | ./mysql-relay.000007 | 80456866 | | 0 | 0 | 0 | 1 | |---------------------------------------------------------------------------------------------------------------------------------------## 只需要开启SQL线程对指定的relay log开始回放即可rootlocalhost[(none)] START SLAVE SQL_THREAD;## 持续执行可看到binlog数据开始回放rootlocalhost[(none)] show slave status\G## 待binlog恢复完成且数据校验无问题后后可将复制关系重置rootlocalhost[(none)] stop slave;rootlocalhost[(none)] reset slave;rootlocalhost[(none)] reset slave all;2.3 只恢复单个库的数据由于备份是全量备份无法单库还原(如果已知表结构可使用可传输表空间方式单库还原)可先将全量恢复后对只对单库做binlog还原只需在2.2数据恢复的基础上增加一条以下命令即可实现对sbtest单库的binlog还原CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE (sbtest.%);2.4 只恢复单个表的数据该步骤与单库还原类似只需将复制过滤属性指定为具体的表如CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE (sbtest.sbtest1,sbtest.sbtest2);可在开启binlog还原前后对sbtest1-4表做checksum验证是否只有sbtest1和sbtest2做了binlog还原checksum table sbtest1,sbtest2,sbtest3,sbtest4;2.5 恢复到指定的GTID或position点该步骤只需在2.2数据恢复的基础上将START SLAVE SQL_THREAD改为START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS如只恢复到GTID499999START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS f41abe78-5c62-11ea-abf1-02000aba3da2:500000;2.6 提升恢复效率的参数优化set global sync_binlog0;set global innodb_flush_log_at_trx_commit0;set global slave_parallel_typeLOGICAL_CLOCK;set global slave_parallel_workers8;关闭binlog2.7 使用复制线程与使用mysqlbinlog恢复的效率对比mysqlbinlog恢复耗时 722sSQL线程恢复耗时 151s2.8 总结使用SQL线程恢复binlog数据相对比mysqlbinlog有以下优势只需配置好复制让数据库自动恢复数据即可可以使用MySQL并行复制特性提升恢复效率可以精细化的控制恢复的库表可以指定恢复到具体的GTID点以及跳过误操作的GTID2.9 恢复优化假设一个每天凌晨备份某天早上8点数据库被误操作drop了业务库请说明恢复的流程保持主库当前状态使用主库的备份恢复一个实例使用change master to后在启动开启START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS方式让复制同步数据到drop操作之前跳过drop操作的gtid继续同步复制
