MySQL 运维实战系列（六）备份与恢复、主从同步

内容概览模块核心内容01 备份与恢复逻辑备份mysqldump、物理备份冷备/XBK、增量备份02 主从同步原理、架构搭建、配置管理01 数据库服务备份与恢复1.1 备份方式对比维度逻辑备份物理备份原理扫描数据生成SQL语句直接复制数据文件备份速度慢50G需3-5小时快文件复制恢复速度慢逐条执行SQL快直接替换文件灵活性高分库/分表/部分数据低整库恢复典型工具mysqldump, mysqlpumpXtraBackup, cp适用场景小数据量、部分数据备份大数据量、快速恢复1.2 逻辑备份mysqldump备份方式分类备份类型命令示例恢复方式全量备份mysqldump -uroot -A /backup/all.sqlmysql all.sql分库备份mysqldump -uroot -B db1 db2 /backup/db.sqlsource db.sql分表备份mysqldump -uroot db1 t1 t2 /backup/tables.sqlmysql db1 tables.sql# 常用选项-A, --all-databases# 备份所有数据库-B,--databasesdb1 db2# 备份指定数据库-F# 备份时刷新binlog切割日志--single-transaction# 使用事务保证一致性InnoDB热备--source-data# 记录binlog位置点用于主从--no-data# 仅备份表结构-E,--events# 备份事件调度器-R,--routines# 备份存储过程、函数--triggers# 备份触发器默认开启备份一致性保证# 方式一锁表备份适合MyISAM影响写入mysqldump --lock-tablesbackup.sql# 方式二事务快照适合InnoDB热备mysqldump --single-transactionbackup.sql原理--single-transaction在备份时开启RR隔离级别的事务保证备份数据一致性不影响业务写入。1.3 物理备份冷备# 冷备步骤mkdir/databackup/ /etc/init.d/mysqld stop# 停止数据库cp-a/data/3307/data/* /databackup/# 复制数据目录# 或使用锁表方式不停服务mysql-eflush tables with read lock;# 全局锁cp-a/data/3307/data/* /databackup/ mysql-eunlock tables;1.4 物理热备Percona XtraBackupXBK安装XtraBackup# 下载解压CentOS 7.9使用glibc2.17版本# https://www.percona.com/downloads/wgethttps://downloads.percona.com/downloads/Percona-XtraBackup-8.0/.../percona-xtrabackup-8.0.35-34-Linux-x86_64.glibc2.17.tar.gztarxf percona-xtrabackup-*.tar.gzln-spercona-xtrabackup-* /usr/local/xbk# 配置环境变量echoexport PATH$PATH:/usr/local/xbk/bin/etc/profilesource/etc/profile# 验证xtrabackup--version全量备份与恢复# 1. 全量备份mkdir-p/databackup/full xtrabackup --defaults-file/etc/my.cnf\--host10.0.0.191--port3306\--userroot--password123456\--backup--target-dir/databackup/full# 2. 模拟故障systemctl stop mysqldrm-rf/data/3307/data/*# 3. 恢复准备应用redo logxtrabackup--prepare--target-dir/databackup/full# 4. 恢复数据xtrabackup --copy-back --target-dir/databackup/full# 5. 修复权限并启动chown-Rmysql.mysql /data/3307/data/ systemctl start mysqld命令作用--backup执行备份--prepare准备备份应用redo log使数据一致--copy-back将数据复制回数据目录--target-dir备份文件存储目录增量备份与恢复# 1. 全量备份xtrabackup--backup--target-dir/databackup/full# 2. 第一次增量修改数据后xtrabackup--backup--target-dir/databackup/add01\--incremental-basedir/databackup/full# 3. 第二次增量xtrabackup--backup--target-dir/databackup/add02\--incremental-basedir/databackup/add01# 4. 恢复准备逐层应用增量xtrabackup--prepare--apply-log-only --target-dir/databackup/full xtrabackup--prepare--apply-log-only --target-dir/databackup/full\--incremental-dir/databackup/add01 xtrabackup--prepare--apply-log-only --target-dir/databackup/full\--incremental-dir/databackup/add02# 5. 最终准备并恢复xtrabackup--prepare--target-dir/databackup/full xtrabackup --copy-back --target-dir/databackup/full参数说明--incremental-basedir指定基准备份目录--apply-log-only仅应用日志不回滚未提交事务用于逐层合并--parallel4并行备份线程数02 数据库主从同步2.1 什么是主从同步主从同步是指多台数据库之间实现数据实时同步的技术架构。2.2 主从同步的作用作用说明数据备份数据冗余防止单点故障高可用架构主库故障时从库可快速切换读写分离主库写从库读提升吞吐量离线分析从库用于分析查询不影响主库2.3 主从同步原理核心组件组件位置作用binlog主库记录所有事务操作dump线程主库监听binlog变化发送给从库IO线程从库接收binlog写入relaylogrelaylog从库中继日志暂存主库传来的事务SQL线程从库读取relaylog并执行master.info从库记录主库连接信息relaylog.info从库记录relaylog执行位点同步流程图主库(Master) 从库(Slave) │ │ │ ┌─────────────┐ │ │ │ binlog │ │ │ └──────┬──────┘ │ │ │ │ │ dump线程 │ │ (监听发送) │ │ │ │ └─────────┼────────────────────────────────┼─┐ │ 网络传输 │ │ ┌─────────┼────────────────────────────────┼─┘ │ ▼ │ │ IO线程 │ (接收写入) │ │ │ ┌─────▼─────┐ │ │ relaylog │ │ └─────┬─────┘ │ │ │ SQL线程 │ (读取执行) │ │ │ ┌─────▼─────┐ │ │ data │ │ └───────────┘一句话总结主库将binlog通过dump线程发送给从库从库IO线程接收写入relaylogSQL线程读取执行实现数据同步。