MySQL Shell实用备份程序

╰+攻爆jí腚メ 2022-11-25 00:36 192阅读 0赞

### 文章目录 ###

*  简介
 *  语法格式
 *  示例
 *   *  util.dumpInstance()备份整个实例
     *  util.dumpInstance()备份整个实例
 *  参考文档

# 简介 #

MySQL Shell提供了`util.dumpInstance()` 和`util.dumpSchemas()`用于导出数据，支持导出所有的`schema`或者选择部分`schame`到本地文件中，后续可以通过`util.loadDump()`进行恢复。支持多线程并行转储和文件压缩，这些都是mysqldump备份不提供的。

可以指定`schema`进行备份，默认不备份`information_schema`, `mysql`, `ndbinfo`, `performance_schema`, 和 `sys` schemas

使用核心要求如下:

*  MySQL5.7或更新的版本
 *  实例/schema的对象名称必须是`latin1` 或 `utf8` 字符集.
 *  只有使用InnoDB存储引擎的表才能保证数据一致性。

# 语法格式 #

# 备份实例
    util.dumpInstance(outputUrl[, options]) 
    # 备份schema
    util.dumpSchemas(schemas, outputUrl[, options])

# 示例 #

## util.dumpInstance()备份整个实例 ##

# 使用msyqlsh连接实例
    # mysqlsh root@127.0.0.1:3306
    # 备份整个实例
     MySQL  127.0.0.1:3306  JS > util.dumpInstance('/tmp/instanceDump',{ "threads":4})
    Acquiring global read lock
    All transactions have been started
    Locking instance for backup
    Global read lock has been released
    Writing global DDL files
    Writing users DDL
    Preparing data dump for table `monitor`.`digests_prev`
    Writing DDL for schema `monitor`
    Writing DDL for view `monitor`.`my_statements`
    Writing DDL for table `monitor`.`digests_prev`
    Writing DDL for schema `sbtest`
    WARNING: Could not select a column to be used as an index for table `monitor`.`digests_prev`. Chunking has been disabled for this table, data will be dumped to a single file.
    Preparing data dump for table `sbtest`.`sbtest1`
    Data dump for table `sbtest`.`sbtest1` will be chunked using column `id`
    Preparing data dump for table `sbtest`.`sbtest10`
    Data dump for table `sbtest`.`sbtest10` will be chunked using column `id`
    Preparing data dump for table `sbtest`.`sbtest2`
    Data dump for table `sbtest`.`sbtest2` will be chunked using column `id`
    Preparing data dump for table `sbtest`.`sbtest3`
    Writing DDL for table `sbtest`.`sbtest1`
    Writing DDL for table `sbtest`.`sbtest10`
    Data dump for table `sbtest`.`sbtest3` will be chunked using column `id`
    Preparing data dump for table `sbtest`.`sbtest4`
    Data dump for table `sbtest`.`sbtest4` will be chunked using column `id`
    Preparing data dump for table `sbtest`.`sbtest5`
    Data dump for table `sbtest`.`sbtest5` will be chunked using column `id`
    Preparing data dump for table `sbtest`.`sbtest6`
    Data dump for table `sbtest`.`sbtest6` will be chunked using column `id`
    Preparing data dump for table `sbtest`.`sbtest7`
    Data dump for table `sbtest`.`sbtest7` will be chunked using column `id`
    Preparing data dump for table `sbtest`.`sbtest8`
    Data dump for table `sbtest`.`sbtest8` will be chunked using column `id`
    Preparing data dump for table `sbtest`.`sbtest9`
    Writing DDL for table `sbtest`.`sbtest2`
    Data dump for table `sbtest`.`sbtest9` will be chunked using column `id`
    Running data dump using 4 threads.
    NOTE: Progress information uses estimated values and may not be accurate.
    Writing DDL for table `sbtest`.`sbtest3`
    Writing DDL for table `sbtest`.`sbtest4`
    Writing DDL for table `sbtest`.`sbtest5`
    Writing DDL for table `sbtest`.`sbtest6`
    Writing DDL for table `sbtest`.`sbtest7`
    Writing DDL for table `sbtest`.`sbtest8`
    Writing DDL for table `sbtest`.`sbtest9`
    Data dump for table `monitor`.`digests_prev` will be written to 1 file
    Data dump for table `sbtest`.`sbtest1` will be written to 1 file
    Data dump for table `sbtest`.`sbtest4` will be written to 3 files
    Data dump for table `sbtest`.`sbtest2` will be written to 3 files
    Data dump for table `sbtest`.`sbtest10` will be written to 3 files
    Data dump for table `sbtest`.`sbtest3` will be written to 3 files
    Data dump for table `sbtest`.`sbtest5` will be written to 3 files
    Data dump for table `sbtest`.`sbtest7` will be written to 3 files
    Data dump for table `sbtest`.`sbtest8` will be written to 3 files
    Data dump for table `sbtest`.`sbtest6` will be written to 3 files
    Data dump for table `sbtest`.`sbtest9` will be written to 3 files
    1 thds dumping - 113% (4.58M rows / ~4.05M rows), 70.94K rows/s, 13.69 MB/s uncompressed, 6.20 MB/s compressed
    Duration: 00:01:53s
    Schemas dumped: 2
    Tables dumped: 11
    Uncompressed data size: 883.48 MB
    Compressed data size: 400.76 MB
    Compression ratio: 2.2
    Rows written: 4582747
    Bytes written: 400.76 MB
    Average uncompressed throughput: 7.78 MB/s
    Average compressed throughput: 3.53 MB/s

## util.dumpInstance()备份整个实例 ##

# 使用msyqlsh连接实例
    # mysqlsh root@127.0.0.1:3306
    # 只备份sbtest库
     MySQL  127.0.0.1:3306  sbtest  JS >  util.dumpSchemas(['sbtest'], '/tmp/sbtest',{ "threads":8})
    Acquiring global read lock
    All transactions have been started
    Locking instance for backup
    Global read lock has been released
    Writing global DDL files
    Preparing data dump for table `sbtest`.`sbtest1`
    Writing DDL for schema `sbtest`
    Writing DDL for table `sbtest`.`sbtest1`
    Writing DDL for table `sbtest`.`sbtest10`
    Writing DDL for table `sbtest`.`sbtest2`
    Writing DDL for table `sbtest`.`sbtest3`
    Writing DDL for table `sbtest`.`sbtest4`
    Writing DDL for table `sbtest`.`sbtest5`
    Writing DDL for table `sbtest`.`sbtest6`
    Data dump for table `sbtest`.`sbtest1` will be chunked using column `id`
    Preparing data dump for table `sbtest`.`sbtest10`
    Data dump for table `sbtest`.`sbtest10` will be chunked using column `id`
    Preparing data dump for table `sbtest`.`sbtest2`
    Data dump for table `sbtest`.`sbtest2` will be chunked using column `id`
    Preparing data dump for table `sbtest`.`sbtest3`
    Data dump for table `sbtest`.`sbtest3` will be chunked using column `id`
    Preparing data dump for table `sbtest`.`sbtest4`
    Data dump for table `sbtest`.`sbtest4` will be chunked using column `id`
    Preparing data dump for table `sbtest`.`sbtest5`
    Data dump for table `sbtest`.`sbtest5` will be chunked using column `id`
    Preparing data dump for table `sbtest`.`sbtest6`
    Data dump for table `sbtest`.`sbtest6` will be chunked using column `id`
    Preparing data dump for table `sbtest`.`sbtest7`
    Data dump for table `sbtest`.`sbtest7` will be chunked using column `id`
    Preparing data dump for table `sbtest`.`sbtest8`
    Data dump for table `sbtest`.`sbtest8` will be chunked using column `id`
    Preparing data dump for table `sbtest`.`sbtest9`
    Data dump for table `sbtest`.`sbtest9` will be chunked using column `id`
    Running data dump using 8 threads.
    NOTE: Progress information uses estimated values and may not be accurate.
    Writing DDL for table `sbtest`.`sbtest7`
    Writing DDL for table `sbtest`.`sbtest8`
    Writing DDL for table `sbtest`.`sbtest9`
    Data dump for table `sbtest`.`sbtest1` will be written to 1 file
    Data dump for table `sbtest`.`sbtest3` will be written to 3 files
    Data dump for table `sbtest`.`sbtest10` will be written to 3 files
    Data dump for table `sbtest`.`sbtest2` will be written to 3 files
    Data dump for table `sbtest`.`sbtest5` will be written to 3 files
    Data dump for table `sbtest`.`sbtest6` will be written to 3 files
    Data dump for table `sbtest`.`sbtest4` will be written to 3 files
    Data dump for table `sbtest`.`sbtest9` will be written to 3 files
    Data dump for table `sbtest`.`sbtest7` will be written to 3 files
    Data dump for table `sbtest`.`sbtest8` will be written to 3 files
    1 thds dumping - 113% (4.58M rows / ~4.05M rows), 99.32K rows/s, 19.15 MB/s uncompressed, 8.69 MB/s compressed
    Duration: 00:00:32s
    Schemas dumped: 1
    Tables dumped: 10
    Uncompressed data size: 883.46 MB
    Compressed data size: 400.76 MB
    Compression ratio: 2.2
    Rows written: 4582718
    Bytes written: 400.76 MB
    Average uncompressed throughput: 27.58 MB/s
    Average compressed throughput: 12.51 MB/s

# 参考文档 #

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