背景
接上篇你还在使用mysqldump备份吗? 因为篇幅有限.所以分2篇来写.
备份目录下文件介绍
@.users.sql 这个文件时备份的数据库内的用户.但是有一些权限不兼容或者不支持没有备份下来
查看文件内容
[root@xunjian mysqlsh]# cat @.users.sql
-- MySQLShell dump 1.0.2 Distrib Ver 8.0.26 for Linux on x86_64 - for MySQL 8.0.26 (MySQL Community Server (GPL)), for Linux (x86_64)
--
-- Host: 10.10.119.63
-- ------------------------------------------------------
-- Server version 8.0.21
--
-- Dumping user accounts
--
-- begin user 'donor'@'%'
CREATE USER IF NOT EXISTS 'donor'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
-- end user 'donor'@'%'
-- begin user 'lzm'@'%'
CREATE USER IF NOT EXISTS 'lzm'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
-- end user 'lzm'@'%'
-- begin grants 'donor'@'%'
GRANT USAGE ON *.* TO `donor`@`%`;
-- end grants 'donor'@'%'
对比源库的donor用户权限:
mysql> show grants for donor@'%';
+------------------------------------------+
| Grants for donor@% |
+------------------------------------------+
| GRANT USAGE ON *.* TO `donor`@`%` |
| GRANT BACKUP_ADMIN ON *.* TO `donor`@`%` |
+------------------------------------------+
2 rows in set (0.00 sec)
发现donor用户少了BACKUP_ADMIN权限.这些权限需要恢复到目标库手动给予了
其实.在我们使用mysqlshell备份的输出中也能看到这些信息:
NOTE: User 'donor'@'%' had restricted privilege (BACKUP_ADMIN) removed
NOTE: User 'lzm'@'%' had restricted privileges (AUDIT_ADMIN, BACKUP_ADMIN, BINLOG_ADMIN, BINLOG_ENCRYPTION_ADMIN, CLONE_ADMIN, CREATE TABLESPACE, ENCRYPTION_KEY_ADMIN, FILE, GROUP_REPLICATION_ADMIN, INNODB_REDO_LOG_ARCHIVE, INNODB_REDO_LOG_ENABLE, PERSIST_RO_VARIABLES_ADMIN, RELOAD, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SERVICE_CONNECTION_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SHOW_ROUTINE, SHUTDOWN, SUPER, SYSTEM_USER, SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN) removed
NOTE: User 'repl_mgr'@'%' had restricted privilege (BACKUP_ADMIN) removed
NOTE: User 'root'@'127.0.0.1' had restricted privileges (AUDIT_ADMIN, BACKUP_ADMIN, BINLOG_ADMIN, BINLOG_ENCRYPTION_ADMIN, CLONE_ADMIN, CREATE TABLESPACE, ENCRYPTION_KEY_ADMIN, FILE, GROUP_REPLICATION_ADMIN, INNODB_REDO_LOG_ARCHIVE, INNODB_REDO_LOG_ENABLE, PERSIST_RO_VARIABLES_ADMIN, RELOAD, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SERVICE_CONNECTION_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SHOW_ROUTINE, SHUTDOWN, SUPER, SYSTEM_USER, SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN) removed
NOTE: User 'root'@'localhost' had restricted privileges (AUDIT_ADMIN, BACKUP_ADMIN, BINLOG_ADMIN, BINLOG_ENCRYPTION_ADMIN, CLONE_ADMIN, CREATE TABLESPACE, ENCRYPTION_KEY_ADMIN, FILE, GROUP_REPLICATION_ADMIN, INNODB_REDO_LOG_ARCHIVE, INNODB_REDO_LOG_ENABLE, PERSIST_RO_VARIABLES_ADMIN, PROXY, RELOAD, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SERVICE_CONNECTION_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SHOW_ROUTINE, SHUTDOWN, SUPER, SYSTEM_USER, SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN) removed
NOTE: Database test_1 had unsupported ENCRYPTION option commented out
NOTE: Database test_2 had unsupported ENCRYPTION option commented out
NOTE: Database test had unsupported ENCRYPTION option commented out
NOTE: Database lzm had unsupported ENCRYPTION option commented out
在这里mysqlshell提醒我们这些权限被移除了.需要我们到了目标端使用root用户重新授予以下这些文件是因为我们指定了bytesPerChunk:"1M" 切片为1M 所以数据和索引数据文件会被切割成一个一个的文件不超过1M
-rw-r----- 1 root root 395551 Aug 15 10:25 lzm@sbtest1@0.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@0.tsv.zst.idx
-rw-r----- 1 root root 397706 Aug 15 10:25 lzm@sbtest1@10.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@10.tsv.zst.idx
-rw-r----- 1 root root 397756 Aug 15 10:25 lzm@sbtest1@11.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@11.tsv.zst.idx
-rw-r----- 1 root root 397680 Aug 15 10:25 lzm@sbtest1@12.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@12.tsv.zst.idx
-rw-r----- 1 root root 397719 Aug 15 10:25 lzm@sbtest1@13.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@13.tsv.zst.idx
-rw-r----- 1 root root 397721 Aug 15 10:25 lzm@sbtest1@14.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@14.tsv.zst.idx
-rw-r----- 1 root root 397777 Aug 15 10:25 lzm@sbtest1@15.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@15.tsv.zst.idx
-rw-r----- 1 root root 397694 Aug 15 10:25 lzm@sbtest1@16.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@16.tsv.zst.idx
-rw-r----- 1 root root 397689 Aug 15 10:25 lzm@sbtest1@17.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@17.tsv.zst.idx
-rw-r----- 1 root root 397671 Aug 15 10:25 lzm@sbtest1@18.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@18.tsv.zst.idx
-rw-r----- 1 root root 397641 Aug 15 10:25 lzm@sbtest1@19.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@19.tsv.zst.idx
-rw-r----- 1 root root 395973 Aug 15 10:25 lzm@sbtest1@1.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@1.tsv.zst.idx
-rw-r----- 1 root root 397751 Aug 15 10:25 lzm@sbtest1@20.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@20.tsv.zst.idx
-rw-r----- 1 root root 397746 Aug 15 10:25 lzm@sbtest1@21.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@21.tsv.zst.idx
-rw-r----- 1 root root 907 Aug 15 10:25 lzm@sbtest1@@22.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@@22.tsv.zst.idx
-rw-r----- 1 root root 397341 Aug 15 10:25 lzm@sbtest1@2.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@2.tsv.zst.idx
-rw-r----- 1 root root 397684 Aug 15 10:25 lzm@sbtest1@3.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@3.tsv.zst.idx
-rw-r----- 1 root root 397654 Aug 15 10:25 lzm@sbtest1@4.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@4.tsv.zst.idx
-rw-r----- 1 root root 397733 Aug 15 10:25 lzm@sbtest1@5.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@5.tsv.zst.idx
-rw-r----- 1 root root 397687 Aug 15 10:25 lzm@sbtest1@6.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@6.tsv.zst.idx
-rw-r----- 1 root root 397721 Aug 15 10:25 lzm@sbtest1@7.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@7.tsv.zst.idx
-rw-r----- 1 root root 397726 Aug 15 10:25 lzm@sbtest1@8.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@8.tsv.zst.idx
-rw-r----- 1 root root 397758 Aug 15 10:25 lzm@sbtest1@9.tsv.zst
-rw-r----- 1 root root 8 Aug 15 10:25 lzm@sbtest1@9.tsv.zst.idx
使用mysqlshell 进行单库和单表备份
语法:
util.dumpSchemas(schemas, outputUrl[, options])
util.dumpTables(schema, tables, outputUrl[, options])备份指定的单库或者指定的多库:
MySQL 10.10.119.63:33060+ ssl JS > util.dumpSchemas(["lzm"],'/mysqlsh/',{threads:8,bytesPerChunk:"10M"})
Acquiring global read lock
Global read lock acquired
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Preparing data dump for table `lzm`.`sbtest1`
Writing DDL for schema `lzm`
Data dump for table `lzm`.`sbtest1` will be chunked using column `id`
Running data dump using 8 threads.
Writing DDL for table `lzm`.`sbtest1`
NOTE: Progress information uses estimated values and may not be accurate.
Data dump for table `lzm`.`sbtest1` will be written to 3 files
1 thds dumping - 101% (100.00K rows / ~98.71K rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Duration: 00:00:00s
Schemas dumped: 1
Tables dumped: 1
Uncompressed data size: 19.18 MB
Compressed data size: 8.75 MB
Compression ratio: 2.2
Rows written: 100000
Bytes written: 8.75 MB
Average uncompressed throughput: 19.18 MB/s
Average compressed throughput: 8.75 MB/s MySQL 10.10.119.63:33060+ ssl JS > util.dumpSchemas(["lzm","test"],'/mysqlsh/',{threads:8,bytesPerChunk:"10M"})
Acquiring global read lock
Global read lock acquired
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Preparing data dump for table `lzm`.`sbtest1`
Writing DDL for schema `lzm`
Data dump for table `lzm`.`sbtest1` will be chunked using column `id`
Writing DDL for table `lzm`.`sbtest1`
Writing DDL for schema `test`
Preparing data dump for table `test`.`t1`
Data dump for table `test`.`t1` will be chunked using column `c1`
Writing DDL for table `test`.`t1`
Running data dump using 8 threads.
NOTE: Progress information uses estimated values and may not be accurate.
NOTE: Table statistics not available for `test`.`t1`, chunking operation may be not optimal. Please consider running 'ANALYZE TABLE `test`.`t1`;' first.
Data dump for table `lzm`.`sbtest1` will be written to 3 files
Data dump for table `test`.`t1` will be written to 1 file
1 thds dumping - 101% (100.00K rows / ~98.71K rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Duration: 00:00:00s
Schemas dumped: 2
Tables dumped: 2
Uncompressed data size: 19.18 MB
Compressed data size: 8.75 MB
Compression ratio: 2.2
Rows written: 100001
Bytes written: 8.75 MB
Average uncompressed throughput: 19.18 MB/s
Average compressed throughput: 8.75 MB/s 备份指定的表或多表:
语法:util.dumpTables(schema, tables, outputUrl[, options]) MySQL 10.10.119.63:33060+ ssl JS > util.dumpTables("lzm",["sbtest1","t1"],"/mysqlsh/",{dryRun:true,threads:8,bytesPerChunk:"10M"})
dryRun enabled, no locks will be acquired and no files will be created.
Acquiring global read lock
Global read lock acquired
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Preparing data dump for table `lzm`.`t1`
NOTE: Could not select columns to be used as an index for table `lzm`.`t1`. Chunking has been disabled for this table, data will be dumped to a single file.
Writing DDL for schema `lzm`
Writing DDL for table `lzm`.`t1`
Writing DDL for table `lzm`.`sbtest1`
Preparing data dump for table `lzm`.`sbtest1`
Data dump for table `lzm`.`sbtest1` will be chunked using column `id`
使用mysqlshell备份的其他介绍
只备份表结构或者库结构:
只备份表结构
MySQL 10.10.119.63:33060+ ssl JS > util.dumpTables("lzm",["sbtest1","t1"],"/mysqlsh/",{dryRun:true,threads:8,bytesPerChunk:"10M",ddlOnly:true})
dryRun enabled, no locks will be acquired and no files will be created.
Acquiring global read lock
Global read lock acquired
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing DDL for table `lzm`.`t1`
Writing DDL for schema `lzm`
Writing DDL for table `lzm`.`sbtest1`
只备份库和库下表结构:
MySQL 10.10.119.63:33060+ ssl JS > util.dumpSchemas(["lzm","test"],'/mysqlsh/',{threads:8,bytesPerChunk:"10M",ddlOnly:true})
Acquiring global read lock
Global read lock acquired
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing DDL for schema `lzm`
Running data dump using 8 threads.
Writing DDL for table `lzm`.`t1`
NOTE: Progress information uses estimated values and may not be accurate.
Writing DDL for table `lzm`.`sbtest1`
Writing DDL for schema `test`
Writing DDL for table `test`.`t1`
0% (0 rows / ~98.71K rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Duration: 00:00:00s
Schemas dumped: 2
Tables dumped: 3
Uncompressed data size: 0 bytes
Compressed data size: 0 bytes
Compression ratio: 0.0
Rows written: 0
Bytes written: 0 bytes
Average uncompressed throughput: 0.00 B/s
Average compressed throughput: 0.00 B/s 只备份表数据:
MySQL 10.10.119.63:33060+ ssl JS > util.dumpSchemas(["lzm","test"],'/mysqlsh/',{dryRun:true,threads:8,bytesPerChunk:"10M",dataOnly:true})
dryRun enabled, no locks will be acquired and no files will be created.
Acquiring global read lock
Global read lock acquired
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Preparing data dump for table `lzm`.`t1`
NOTE: Could not select columns to be used as an index for table `lzm`.`t1`. Chunking has been disabled for this table, data will be dumped to a single file.
Preparing data dump for table `lzm`.`sbtest1`
Data dump for table `lzm`.`sbtest1` will be chunked using column `id`
Preparing data dump for table `test`.`t1`
Data dump for table `test`.`t1` will be chunked using column `c1`
排除指定库备份:
MySQL 10.10.119.63:33060+ ssl JS > util.dumpInstance("/mysqlsh",{dryRun:true,threads : 8,ocimds:true,compatibility:["strip_restricted_grants"],bytesPerChunk:"1M",excludeSchemas:["test"]})注意: mysqlshell备份整个库的时候.默认是排除了系统库备份的整个实例.也就是排除了
information_schema, mysql, ndbinfo, performance_schema, and sys schemas
如果使用mysqlshell进行生产备份.请记得使用脚本时.在备份完了之后将备份路径下的备份文件打包挪走.或者每次备份都新建备份目录.
总结
这两篇文章你还在使用mysqldump备份吗?主要介绍了使用mysqlshell进行备份.相比于mysqldump.mysqlshell的备份方式更加灵活.且备份速度更快.大家可以去官网自行了解.下一篇文章.我们使用mysqlshell导入备份数据.敬请关注!!!!
本文暂时没有评论，来添加一个吧(●'◡'●)