mysql数据库备份方法 mysql备份数据库命令

文章插图
背景前边几篇文章我简单介绍了使用mysqlshell备份数据库及恢复备份数据的方式.这篇文章我们简单探讨一下mysqlshell备份数据库的原理.主要结合mysqlshell的输出以及mysql general_log
mysqlshell备份整个实例的原理mysqlshell输出:
MySQL10.10.119.63:33060+ sslJS > util.dumpInstance("/mysqlsh",{threads : 8,ocimds:true,compatibility:["strip_restricted_grants"],bytesPerChunk:"1M"})Acquiring global read lockGlobal read lock acquiredGathering information - doneAll transactions have been startedLocking instance for backupGlobal read lock has been releasedChecking for compatibility with MySQL Database Service 8.0.26NOTE: User 'donor'@'%' had restricted privilege (BACKUP_ADMIN) removedNOTE: 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) removedNOTE: User 'repl_mgr'@'%' had restricted privilege (BACKUP_ADMIN) removedNOTE: 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) removedNOTE: 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) removedNOTE: Database test_1 had unsupported ENCRYPTION option commented outNOTE: Database test_2 had unsupported ENCRYPTION option commented outNOTE: Database test had unsupported ENCRYPTION option commented outNOTE: Database lzm had unsupported ENCRYPTION option commented outCompatibility issues with MySQL Database Service 8.0.26 were found and repaired. Please review the changes made before loading them.Writing global DDL filesWriting users DDLWriting DDL for schema `test_1`Writing DDL for table `test_1`.`T_CM_CUST`Writing DDL for schema `test_2`Writing DDL for table `test_2`.`checkpoint_lox`Writing DDL for table `test_2`.`T_HM_PropertyRight`Writing DDL for table `test_2`.`checkpoint`Writing DDL for table `test_2`.`T_HM_OWNER`Preparing data dump for table `test_1`.`T_CM_CUST`Data dump for table `test_1`.`T_CM_CUST` will be chunked using column `PKID`Preparing data dump for table `test_2`.`checkpoint_lox`Data dump for table `test_2`.`checkpoint_lox` will be chunked using columns `group_name`, `group_key`, `log_cmplt_csn`, `log_cmplt_xids_seq`Preparing data dump for table `test_2`.`checkpoint`Data dump for table `test_2`.`checkpoint` will be chunked using columns `group_name`, `group_key`Preparing data dump for table `test_2`.`T_HM_PropertyRight`Data dump for table `test_2`.`T_HM_PropertyRight` will be chunked using column `pkid`Preparing data dump for table `test_2`.`T_HM_OWNER`Data dump for table `test_2`.`T_HM_OWNER` will be chunked using column `pkid`Preparing data dump for table `test_2`.`T_HM_HOUSE`Data dump for table `test_2`.`T_HM_HOUSE` will be chunked using column `pkid`Preparing data dump for table `test_2`.`T_HM_HOUSEDEL`Data dump for table `test_2`.`T_HM_HOUSEDEL` will be chunked using column `pkid`Preparing data dump for table `test`.`t1`Data dump for table `test`.`t1` will be chunked using column `c1`Preparing data dump for table `lzm`.`sbtest1`Data dump for table `lzm`.`sbtest1` will be chunked using column `id`Writing DDL for table `test_2`.`T_HM_HOUSE`Running data dump using 8 threads.NOTE: Progress information uses estimated values and may not be accurate.Writing DDL for table `test_2`.`T_HM_HOUSEDEL`Writing DDL for schema `test`Writing DDL for table `test`.`t1`Writing DDL for schema `lzm`Writing DDL for table `lzm`.`sbtest1`NOTE: Table statistics not available for `test_2`.`checkpoint_lox`, chunking operation may be not optimal. Please consider running 'ANALYZE TABLE `test_2`.`checkpoint_lox`;' first.NOTE: Table statistics not available for `test_2`.`checkpoint`, chunking operation may be not optimal. Please consider running 'ANALYZE TABLE `test_2`.`checkpoint`;' first.Data dump for table `test_1`.`T_CM_CUST` will be written to 4 filesData dump for table `test_2`.`checkpoint_lox` will be written to 1 fileData dump for table `test_2`.`T_HM_PropertyRight` will be written to 1 fileData dump for table `test_2`.`T_HM_OWNER` will be written to 1 fileNOTE: 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 `test_2`.`T_HM_HOUSE` will be written to 1 fileData dump for table `test`.`t1` will be written to 1 fileData dump for table `test_2`.`T_HM_HOUSEDEL` will be written to 4 filesData dump for table `lzm`.`sbtest1` will be written to 23 filesData dump for table `test_2`.`checkpoint` will be written to 1 file1 thds dumping - 102% (128.91K rows / ~125.17K rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressedDuration: 00:00:00sSchemas dumped: 4Tables dumped: 9Uncompressed data size: 25.67 MBCompressed data size: 9.88 MBCompression ratio: 2.6Rows written: 128906Bytes written: 9.88 MBAverage uncompressed throughput: 25.67 MB/sAverage compressed throughput: 9.88 MB/s mysql general_log的输出:

以上关于本文的内容，仅作参考！温馨提示：如遇健康、疾病相关的问题，请您及时就医或请专业人士给予相关指导!

「四川龙网」www.sichuanlong.com小编还为您精选了以下内容，希望对您有所帮助：