MySQL逻辑备份定时任务之mysqlshell脚本，拿来即用吧！-每日运维

MySQL逻辑备份简介

MySQL逻辑备份是指将MySQL中的数据导出为SQL语句（create 数据库对象，insert表数据这样的SQL语句形式），而不是直接复制数据文件。逻辑备份通常用于备份和恢复数据库结构和数据，它允许用户选择性地备份数据，并且可以在不同的MySQL版本或不同的操作系统之间迁移数据。
在选择MySQL备份工具时，需要考虑多重因素，包括备份速度，恢复速度，可移植性，资源消耗，易用性以及运维成本等，以下是几种常见的逻辑备份工具比较：

MySQLShell简介

MySQL Shell是MySQL的高级客户端和代码编辑器，是Oracle公司提供的一个交互式命令行工具，它允许用户以多种方式与 MySQL 服务器进行交互。
MySQL Shell支持多语言：JavaScript, Python和SQL语言，为用户提供更多的选择和灵活性，而且为MySQL的不同产品（如MySQL Server,MySQL Router,MySQL Innodb Cluster等）提供了一个统一接口。与此同时MySQL Shell还集成了很多功能：数据库查询和更新，数据库管理，集群管理，插件支持，工具包（更新检查，备份恢复等）

MySQL Shell备份简介

使用MySQL Shell进行备份，备份是MySQLShell工具本身提供的一个功能之一，MySQLShell备份功能是MySQL官方对MySQL备份的一个有力补充，我认为基本上可以完全取代传统的mysqldump工具。
MySQL Shell备份工具特点如下：

多语言支持：支持 JavaScript 和 Python 两种脚本语言，用户可以根据自己的偏好选择使用。

统一接口：为 MySQL 的不同产品提供了统一的接口，简化了数据库管理。

支持SQL，X protocol：支持 SQL 语句和 X Protocol，后者是一种更高效的通信协议。

备份和恢复功：包括 util.dumpInstance()，util.dumpSchemas() 和 util.dumpTables()，util.exportTable()，util.importTable()等函数，用于备份整个实例、特定数据库或表。

MySQL Shell备份工具优点如下：

并行处理：MySQL Shell 的备份功能支持并行处理，可以同时处理多个表或数据库，从而加快备份速度。

压缩备份：支持使用高效的压缩算法（如 zstd）来压缩备份数据，减少备份文件的大小。

选择性备份：允许用户有选择地从备份中导入数据的子集，这对于需要恢复特定数据的场景非常有用。

恢复速度快：与传统的逻辑备份相比，MySQL Shell 的备份恢复速度更快，尤其是在恢复大量数据时。

MySQL Shell备份工具缺点如下：

恢复局限性：恢复时，对MySQL版本有要求，必须一致或兼容（MySQL5.7或8.0版本）。

表存储引擎限制：MySQL Shell 的备份要求表为InnoDB存储引擎。

其他：如果表没有主键，实际备份的速度会大大降低，主要是因为数据导出时需要查询数据按主键批量查询最快。

MySQL Shell备份脚本示例

本例以实际生产环境备份脚本为参考：

`##说明：这里定时任务设置在每天凌晨2点开始备份。 [root@localhost mysql]# crontab -l 00 2 * * * /data/mysql/mysqlshellbackup.sh`
##说明：备份脚本内容如下： [root@localhost mysql]# cat /data/mysql/mysqlshellbackup.sh backupdate=`date +%Y%m%d` mkdir -p /localhdd/backup_${backupdate} backupdir=/localhdd/backup_${backupdate} /data/mysqlshell8036/bin/mysqlsh -uroot p'real_password' -S /data/mysql/3306/run/mysql.sock -- util dump-instance ${backupdir}/dbdump --excludeSchemas='dbname' --threads=8 > ${backupdir}/backup.log 2>&1 #删除15天之前的备份 find /backup/ -name 'backup_*' -type d -mtime +15 -exec rm -rf {}; >/dev/null 2>&1 ----------------参数说明---------------- 1. dump参数 defaultCharacterSet：缺省字符集，默认为utf8mb4 where：设置导出数据的条件，可以指定通过where条件来导出表的数据 ddlOnly：仅仅导出表的ddl语句，默认为false dataOnly：仅仅导出数据，默认false users：导出用户，缺省为true, (Instance dump utility only) excludeUsers：排除用户，缺省为false, (Instance dump utility only) includeUsers：导出时指定包含的用户, (Instance dump utility only) excludeSchemas：导出时指定排除的DB, (Instance dump utility only) includeSchemas：导出时指定包含的DB, (Instance dump utility only) excludeTables：导出时指定排除的表, (Instance dump utility and schema dump utility only) includeTables：导出时指定包含的表, (Instance dump utility and schema dump utility only)
2. load参数 excludeEvents：忽略某些定时器的导入。 excludeRoutines：忽略某些函数和存储过程的导入。 excludeSchemas：忽略某些库的导入。 excludeTables：忽略某些表的导入。 excludeTriggers：忽略某些触发器的导入。 excludeUsers：忽略某些账号的导入。 includeEvents：导入指定定时器。 includeRoutines：导入指定函数和存储过程。 includeSchemas：导入指定库。 includeTables：导入指定表。 includeTriggers：导入指定触发器。 includeUsers：导入指定账号。 loadData：是否导入数据，默认为 true。 loadDdl：是否导入 DDL 语句，默认为 true。 loadUsers：是否导入账号，默认为 false。注意，即使将 loadUsers 设置为 true，也不会导入当前正在执行导入操作的用户。 ignoreExistingObjects：是否忽略已经存在的对象，默认为 off。 backgroundThreads：获取元数据和 DDL文件内容的线程数。备份集如果存储在本地，backgroundThreads 默认和 threads 一致。 threads：并发线程数，默认为 4。 maxBytesPerTransaction：指定单个 LOAD DATA 操作可加载的最大字节数。默认与 bytesPerChunk 一致。这个参数可用来规避大事务。 progressFile：在导入的过程中，会在备份目录生成一个progressFile，用于记录加载过程中的进度信息，这个进度信息可用来实现断点续传功能。默认为load-progress…progress。 resetProgress：如果备份目录中存在progressFile，默认会从上次完成的地方继续执行。如果要从头开始执行，需将resetProgress 设置为 true。该参数默认为 off。 skipBinlog：是否设置 sql_log_bin=0 ，默认 false。这一点与 mysqldump、mydumper 不同，后面这两个工具默认会禁用 Binlog。 updateGtidSet：更新 GTID_PURGED。可设置：off（不更新，默认值）, replace（替代目标实例的 GTID_PURGED）, append（追加）。

截图如下：

具体备份信息如下：

[root@localhost backup]# pwd /localhdd/backup [root@localhost backup]# ls -lhrt 总用量 0 drwxr-xr-x 3 root root 50 4月 13 05:00 backup_20240413 drwxr-xr-x 3 root root 50 4月 14 05:00 backup_20240414 drwxr-xr-x 3 root root 50 4月 15 05:00 backup_20240415 drwxr-xr-x 3 root root 50 4月 16 05:00 backup_20240416 drwxr-xr-x 3 root root 50 4月 17 05:00 backup_20240417 drwxr-xr-x 3 root root 50 4月 18 05:00 backup_20240418 drwxr-xr-x 3 root root 38 4月 19 05:00 backup_20240419 drwxr-xr-x 3 root root 50 4月 20 05:00 backup_20240420 drwxr-xr-x 3 root root 50 4月 21 05:00 backup_20240421 drwxr-xr-x 3 root root 50 4月 22 05:00 backup_20240422 drwxr-xr-x 3 root root 50 4月 23 05:00 backup_20240423 drwxr-xr-x 3 root root 50 4月 24 05:00 backup_20240424 drwxr-xr-x 3 root root 50 4月 25 05:00 backup_20240425 drwxr-xr-x 3 root root 50 4月 26 05:00 backup_20240426 drwxr-xr-x 3 root root 50 4月 27 05:00 backup_20240427 drwxr-xr-x 3 root root 50 4月 28 05:00 backup_20240428 [root@localhost backup]#
[root@localhost backup]# cat backup_20240428/backup.log WARNING: Using a password on the command line interface can be insecure. Initializing... NOTE: Backup lock is not supported in MySQL 5.7 and DDL changes will not be blocked. The dump may fail with an error if schema changes are made while dumping. Acquiring global read lock Global read lock acquired Initializing - done Gathering information... 4 out of 9 schemas will be dumped and within them 23051 tables, 0 views, 11 routines. 5 out of 7 users will be dumped. Gathering information - done All transactions have been started Global read lock has been released Writing global DDL files Writing users DDL Writing schema metadata... Writing DDL... Writing table metadata... Running data dump using 20 threads. Dumping data... Writing schema metadata - done Writing DDL - done Writing table metadata - done Starting data dump Dumping data - done Dump duration: 00:09:46s Total duration: 00:10:01s Schemas dumped: 4 Tables dumped: 23051 Uncompressed data size: 351.94 GB Compressed data size: 67.51 GB Compression ratio: 5.2 Rows written: 1223645064 Bytes written: 67.51 GB Average uncompressed throughput: 599.86 MB/s Average compressed throughput: 115.07 MB/s ##说明：备份的原始数据为351.94GB，备份后的数据量压缩后为67.51GB，备份总时间为10分1秒!

截图如下：

MySQL逻辑备份定时任务之mysqlshell脚本，拿来即用吧！

参考文档

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html

MySQL逻辑备份定时任务之mysqlshell脚本，拿来即用吧！

参考文档 https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html

参考文档

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html