备份和还原
备份策略
MySQL的逻辑备份和物理备份是两种不同的备份策略，它们在备份方式、适用场景以及恢复机制上有所不同。以下是关于这两种备份策略的详细解释：
逻辑备份
- 定义：逻辑备份保存的是创建数据库（CREATE DATABASE）和生成数据（INSERT 语句或者文本文件）的SQL脚本。它备份的是建表、建库、插入等操作所执行的SQL语句（DDL、DML、DCL）。
- 特点：
- 适用于中小型数据库，效率相对较低。
- 提供三种级别的备份：表级、库级和全库级。
- 备份文件是SQL脚本，因此可读性较高，且可以跨不同版本的MySQL进行恢复（在SQL语法兼容的前提下）。
- 备份方法：常用工具是
mysqldump，通过命令行或脚本执行，可指定要备份的数据库或表，以及备份选项（如是否导出数据、是否压缩等）。
物理备份
- 定义：物理备份由数据库目录和文件的副本组成，直接复制数据库文件。
- 特点：
- 适用于大型数据库环境，备份速度快。
- 不受存储引擎的限制。
- 但通常不能恢复到不同的MySQL版本。
- 物理备份包括完整备份、增量备份和差异备份。
- 完整备份：每次将所有数据进行一次完整的复制，恢复时一次恢复到位，速度快。
- 增量备份：只备份上一次备份到现在产生的数据，备份体积小，恢复时需要按备份顺序逐个恢复，恢复时间长。
- 差异备份：只备份和完整备份不一样的数据，恢复时仅需恢复第一个完整版和最后一个差异版，恢复速度介于完整备份和增量备份之间。
- 备份方法：常用工具包括
xtrabackup等，需要安装并配置相应的软件。备份过程通常涉及创建备份目录、执行备份命令等步骤。
总结：逻辑备份和物理备份各有优缺点，选择哪种备份策略取决于具体的业务需求、数据库大小、恢复时间要求等因素。在实际使用中，可以根据需要灵活选择或结合使用这两种备份策略。
高并发mysql该使用xtrabackup还是mysqldump备份呢？
在高并发的MySQL环境中，备份策略的选择尤为关键。针对Xtrabackup和mysqldump这两种备份工具，我们可以从以下几个方面进行比较和分析，以确定哪种工具更适合高并发环境。
一、备份原理与特点
- Xtrabackup：
- 原理：物理备份工具，直接拷贝数据页进行备份。
- 特点：
- 支持在线热备份，备份过程中不影响数据库的正常读写。
- 备份速度快，特别是在数据量大的情况下。
- 提供增量备份和压缩备份等功能，有助于减少备份文件的大小。
- 主要适用于InnoDB和XtraDB存储引擎。
- mysqldump：
- 原理：逻辑备份工具，通过生成SQL脚本来备份数据库。
- 特点：
- 备份过程可能涉及锁等待，特别是在高并发环境下可能影响数据库性能。
- 备份和恢复速度相对较慢，尤其是在处理大型数据库时。
- 支持跨版本恢复，SQL脚本可读性强，易于编辑和传输。
二、高并发环境下的适用性
- Xtrabackup：
- 由于其物理备份的特性，Xtrabackup在备份过程中不会对数据库产生额外的I/O压力，非常适合高并发环境。
- 备份速度快，可以减少备份窗口，降低对生产环境的影响。
- mysqldump：
- 在高并发环境下，mysqldump的备份过程可能会因锁等待而降低数据库性能。
- 备份和恢复速度慢，可能会增加停机时间，对高可用性要求较高的系统不利。
三、总结与建议
在高并发的MySQL环境中，推荐使用Xtrabackup进行备份。其主要原因如下：
- 备份过程对数据库性能影响小，不会导致额外的I/O压力。
- 备份速度快，可以减少备份窗口，降低对生产环境的影响。
- 提供增量备份和压缩备份等功能，有助于节省存储空间和带宽资源。
然而，也需要注意以下几点：
- Xtrabackup主要适用于InnoDB和XtraDB存储引擎，如果数据库中存在大量MyISAM表，可能需要考虑其他备份方案或工具。
- 在进行备份前，务必确保Xtrabackup的版本与MySQL数据库版本兼容。
- 定期对备份进行验证和恢复测试，以确保备份的完整性和可用性。
- Xtrabackup：
逻辑备份和还原mysqldump
准备实验环境
创建用于测试的数据库和数据表
sqlCREATE DATABASE IF NOT EXISTS demo_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE demo_test; CREATE TABLE IF NOT EXISTS `test1`( id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64) NOT NULL COMMENT '名称', create_time DATETIME NOT NULL COMMENT '创建时间' ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; INSERT INTO `test1`(name,create_time) VALUES('用户1',now()); INSERT INTO `test1`(name,create_time) VALUES('用户2',now());使用
mysqldump导出数据--master-data选项与复制和主从复制相关，它允许你在备份中包含二进制日志（binlog）的坐标，以便稍后可以将这个备份用于基于时间点的恢复或设置新的从服务器。--master-data默认值是1。--master-data有几个可能的值：1或=1：这将在输出中添加 CHANGE MASTER TO 语句，其中包含二进制日志的文件名和位置。这通常用于设置新的从服务器。2或=2：这也将添加 CHANGE MASTER TO 语句，但输出会被注释掉。这允许你手动检查或编辑这些值，然后再用于设置从服务器。
--lock-tables是一个用于确保备份数据一致性的选项，它通过为表加上读锁来防止在备份过程中其他进程对表进行修改（如INSERT、UPDATE、DELETE）。但在高并发环境中，长时间持有读锁可能会对性能产生轻微影响。对于支持事务的存储引擎（如InnoDB），更推荐使用--single-transaction选项。对于MyISAM表，--lock-tables可以确保在备份期间数据的一致性。但对于支持事务的InnoDB表，更推荐使用--single-transaction选项（它会在一个事务中执行mysqldump操作，以确保数据的一致性，而不会锁定表）。--single-transaction选项，mysqldump会发送一个START TRANSACTIONSQL 语句给 MySQL 服务器。这会使得接下来的导出操作都在一个事务的上下文中进行。对于 InnoDB 存储引擎，这意味着在mysqldump运行期间，导出的数据会保持一致性快照（完成备份后的数据是mysqldump开始备份时间点的数据，即使备份期间数据被增、删、改也不会在备份中出现），即使其他事务在此期间修改了数据。--quick选项，mysqldump不会将整个结果集加载到内存中，而是逐行地写入输出文件。这可以显著减少mysqldump进程所需的内存量，特别是在处理非常大的表时。假设你有一个非常大的表，包含数百万或数千万行数据，并且你的服务器内存有限。如果你尝试使用mysqldump不带--quick选项来备份这个表，mysqldump可能会尝试将整个结果集加载到内存中，这可能导致内存不足错误（OOM，Out of Memory）。sh# 导出数据文件 demo_test.sql mysqldump -uroot -p --single-transaction --quick --master-data=2 demo_test > demo_test.sql # 压缩数据文件 tar -czf demo_test.sql.tar.gz demo_test.sql # 解压数据文件 tar -xvzf demo_test.sql.tar.gz使用导出数据文件导入数据到新的数据库
创建 demo_test2 数据库
sqlCREATE DATABASE IF NOT EXISTS demo_test2 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;导入数据到 demo_test2 数据库
bashmysql -uroot -p demo_test2 < demo_test.sql
物理备份和还原xtrabackup
todo 未做实验 ...
还原到指定时间点
注意：还原数据时目标数据库名称需要和源数据库名称一致，因为 binlog 中有 use xxx; sql 语句。
启用 binlog，MySQL 或者 MariaDB 配置文件添加如下配置：
# 一旦提供log_bin参数无论是何值或者不提供值时，表示启用binlog功能
# 不提供log_bin表示禁用binlog功能
log_bin
expire_logs_days=10
binlog_format=mixed
max_binlog_size=1024m
# 指定binlog文件的前缀
log_basename=master1
server_id=10001模拟创建业务数据库
创建 demo 数据库
sqlcreate database if not exists demo character set utf8mb4 collate utf8mb4_general_ci; use demo; create table if not exists t1 ( id bigint not null auto_increment primary key, `name` varchar(128) not null unique ) engine=InnoDB character set utf8mb4 collate utf8mb4_general_ci;插入测试数据
sqlinsert into t1(`name`) values("a"); insert into t1(`name`) values("b");
全量备份业务数据库
mysqldump -uroot -p --single-transaction --quick --master-data=2 demo > demo.sql--master-data=2表示记录备份数据库时的 binlog 位置信息到 sql 文件中。
模拟在业务数据库 demo 全量备份后有数据变动
insert into t1(`name`) values("c");
insert into t1(`name`) values("d");模拟还原业务数据库到指定时间
从 binlog 提取 sql
sql-- 获取数据库全量备份 binlog position -- 输出结果为 CHANGE MASTER TO MASTER_LOG_FILE='master1-bin.000003', MASTER_LOG_POS=1441，表示数据库全量备份时 binlog 文件为 master1-bin.000003，binlog position 为 1441 grep -r -i "master" demo.sql -- 从 demo 数据库全量备份位置开始提取 binlog 文件中所有 sql -- --start-position 1441 和 master1-bin.000003 是 demo 数据库全量备份时的开始位置，master1-bin.000004 和 master1-bin.000005 为需要提取这两个文件中的所有 sql -- --database 表示只提取在 demo 数据库中执行的 sql cd /var/lib/mysql/demo mysqlbinlog --database demo --start-position 1441 master1-bin.000003 master1-bin.000004 master-bin.000005 > binlog.sql -- 从 demo 数据库全量备份位置开始提取 binlog 文件中指定结束 position 2010（不包含 2010 position 的 sql） 之间的 sql -- --database 表示只提取在 demo 数据库中执行的 sql cd /var/lib/mysql/demo mysqlbinlog --database demo --start-position 1441 --stop-position 2010 master1-bin.000003 master1-bin.000004 master-bin.000005 > binlog.sql -- 从 demo 数据库全量备份位置开始提取 binlog 文件中指定结束时间 2010-01-07 21:00:00（不包含 21:00:00 的 sql） 之间的 sql -- --database 表示只提取在 demo 数据库中执行的 sql cd /var/lib/mysql/demo mysqlbinlog --database demo --start-position 1441 --stop-dateime '2010-01-07 21:00:00' master1-bin.000003 master1-bin.000004 master-bin.000005 > binlog.sql使用全量备份还原数据库
sqlcreate database if not exists demo character set utf8mb4 collate utf8mb4_general_ci; use demo; source demo.sql;还原数据到指定时间
sqlmysql -uroot -p demo < binlog.sql