背景
前边几篇文章我简单介绍了使用shell备份及恢复备份数据的方式.这篇文章我们简单探讨一下mysqlshell备份数据库的原理.主要结合mysqlshell的输出以及mysql general_log
mysqlshell备份整个实例的原理
mysqlshell输出:
MySQL 10.10.119.63:33060+ ssl JS > util.dumpInstance("/mysqlsh",{threads : 8,ocimds:true,compatibility:["strip_restricted_grants"],bytesPerChunk:"1M"})
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
Checking for compatibility with MySQL Database Service 8.0.26
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
Compatibility issues with MySQL Database Service 8.0.26 were found and repaired. Please review the changes made before loading them.
Writing global DDL files
Writing users DDL
Writing 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 files
Data dump for table `test_2`.`checkpoint_lox` will be written to 1 file
Data dump for table `test_2`.`T_HM_PropertyRight` will be written to 1 file
Data dump for table `test_2`.`T_HM_OWNER` will be written to 1 file
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 `test_2`.`T_HM_HOUSE` will be written to 1 file
Data dump for table `test`.`t1` will be written to 1 file
Data dump for table `test_2`.`T_HM_HOUSEDEL` will be written to 4 files
Data dump for table `lzm`.`sbtest1` will be written to 23 files
Data dump for table `test_2`.`checkpoint` will be written to 1 file
1 thds dumping - 102% (128.91K rows / ~125.17K rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Duration: 00:00:00s
Schemas dumped: 4
Tables dumped: 9
Uncompressed data size: 25.67 MB
Compressed data size: 9.88 MB
Compression ratio: 2.6
Rows written: 128906
Bytes written: 9.88 MB
Average uncompressed throughput: 25.67 MB/s
Average compressed throughput: 9.88 MB/s mysql general_log的输出:
2022-08-18T02:54:47.923098Z 10758 Query SELECt @@GLOBAL.port
2022-08-18T02:54:47.926847Z 10774 Connect [email protected] on using SSL/TLS
2022-08-18T02:54:47.927053Z 10774 Query SET SQL_MODE = ''
2022-08-18T02:54:47.927204Z 10774 Query SET NAMES 'utf8mb4'
2022-08-18T02:54:47.927351Z 10774 Query SET SESSION net_write_timeout = 1800
2022-08-18T02:54:47.927471Z 10774 Query SET SESSION wait_timeout = 31536000
2022-08-18T02:54:47.927580Z 10774 Query SET TIME_ZONE = '+00:00'
2022-08-18T02:54:47.927722Z 10774 Query SELECT CURRENT_USER()
2022-08-18T02:54:47.927911Z 10774 Query SELECT PRIVILEGE_TYPE FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERe GRANTEE=''lzm'@'%'' LIMIT 1
2022-08-18T02:54:47.928440Z 10774 Query SHOW PRIVILEGES
2022-08-18T02:54:47.928687Z 10774 Query SELECt @@GLOBAL.activate_all_roles_on_login
2022-08-18T02:54:47.928855Z 10774 Query SELECT default_role_user, default_role_host FROM mysql.default_roles WHERe user='lzm' AND host='%'
2022-08-18T02:54:47.929228Z 10774 Query SHOW GRANTS FOR 'lzm'@'%'
2022-08-18T02:54:47.929518Z 10774 Query FLUSH NO_WRITE_TO_BINLOG TABLES
2022-08-18T02:54:47.933815Z 10774 Query FLUSH TABLES WITH READ LOCK
2022-08-18T02:54:47.933978Z 10774 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2022-08-18T02:54:47.934108Z 10774 Query START TRANSACTION WITH CONSISTENT SNAPSHOT
2022-08-18T02:54:47.934507Z 10774 Query SELECt @@GLOBAL.VERSION
2022-08-18T02:54:47.934821Z 10774 Query SELECT SCHEMA_NAME,DEFAULT_COLLATION_NAME FROM information_schema.schemata WHERe (STRCMP(SCHEMA_NAME COLLATE utf8_bin,'information_schema')&STRCMP(SCHEMA_NAME COLLATE utf8_bin,'mysql')&STRCMP(SCHEMA_NAME COLLATE utf8_bin,'ndbinfo')&STRCMP(SCHEMA_NAME COLLATE utf8_bin,'performance_schema')&STRCMP(SCHEMA_NAME COLLATE utf8_bin,'sys'))!=0
2022-08-18T02:54:47.937729Z 10774 Query SELECt TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,TABLE_ROWS,AVG_ROW_LENGTH,ENGINE,CREATE_OPTIONS,TABLE_COMMENT FROM information_schema.tables WHERe (STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'test')&STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'lzm')&STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'scsales_prd')&STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'crm_prd'))=0
2022-08-18T02:54:47.939327Z 10780 Connect [email protected] on using SSL/TLS
2022-08-18T02:54:47.939500Z 10780 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2022-08-18T02:54:47.939644Z 10780 Query START TRANSACTION WITH CONSISTENT SNAPSHOT
2022-08-18T02:54:47.939768Z 10780 Query SET SQL_MODE = ''
2022-08-18T02:54:47.939875Z 10780 Query SET NAMES 'utf8mb4'
2022-08-18T02:54:47.940003Z 10780 Query SET SESSION net_write_timeout = 1800
2022-08-18T02:54:47.940106Z 10780 Query SET SESSION wait_timeout = 31536000
2022-08-18T02:54:47.940125Z 10774 Query SHOW VARIABLES LIKE 'ndbinfo_version'
2022-08-18T02:54:47.940225Z 10780 Query SET TIME_ZONE = '+00:00'
2022-08-18T02:54:47.940636Z 10776 Connect [email protected] on using SSL/TLS
2022-08-18T02:54:47.940945Z 10778 Connect [email protected] on using SSL/TLS
2022-08-18T02:54:47.940985Z 10776 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2022-08-18T02:54:47.941056Z 10782 Connect [email protected] on using SSL/TLS
2022-08-18T02:54:47.941133Z 10776 Query START TRANSACTION WITH CONSISTENT SNAPSHOT
2022-08-18T02:54:47.941180Z 10775 Connect [email protected] on using SSL/TLS
2022-08-18T02:54:47.941258Z 10776 Query SET SQL_MODE = ''
2022-08-18T02:54:47.941311Z 10782 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2022-08-18T02:54:47.941379Z 10781 Connect [email protected] on using SSL/TLS
2022-08-18T02:54:47.941192Z 10778 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2022-08-18T02:54:47.941459Z 10775 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2022-08-18T02:54:47.941536Z 10782 Query START TRANSACTION WITH CONSISTENT SNAPSHOT
2022-08-18T02:54:47.941543Z 10776 Query SET NAMES 'utf8mb4'
2022-08-18T02:54:47.941539Z 10781 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2022-08-18T02:54:47.941646Z 10775 Query START TRANSACTION WITH CONSISTENT SNAPSHOT
2022-08-18T02:54:47.941654Z 10779 Connect [email protected] on using SSL/TLS
2022-08-18T02:54:47.941708Z 10782 Query SET SQL_MODE = ''
2022-08-18T02:54:47.941723Z 10781 Query START TRANSACTION WITH CONSISTENT SNAPSHOT
2022-08-18T02:54:47.941791Z 10776 Query SET SESSION net_write_timeout = 1800
2022-08-18T02:54:47.941799Z 10775 Query SET SQL_MODE = ''
2022-08-18T02:54:47.941847Z 10782 Query SET NAMES 'utf8mb4'
2022-08-18T02:54:47.941898Z 10779 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2022-08-18T02:54:47.941921Z 10775 Query SET NAMES 'utf8mb4'
2022-08-18T02:54:47.941979Z 10782 Query SET SESSION net_write_timeout = 1800
2022-08-18T02:54:47.942020Z 10779 Query START TRANSACTION WITH CONSISTENT SNAPSHOT
2022-08-18T02:54:47.942090Z 10776 Query SET SESSION wait_timeout = 31536000
2022-08-18T02:54:47.942109Z 10782 Query SET SESSION wait_timeout = 31536000
2022-08-18T02:54:47.942150Z 10779 Query SET SQL_MODE = ''
2022-08-18T02:54:47.942205Z 10776 Query SET TIME_ZONE = '+00:00'
2022-08-18T02:54:47.942264Z 10779 Query SET NAMES 'utf8mb4'
2022-08-18T02:54:47.942318Z 10781 Query SET SQL_MODE = ''
2022-08-18T02:54:47.942407Z 10779 Query SET SESSION net_write_timeout = 1800
2022-08-18T02:54:47.942465Z 10781 Query SET NAMES 'utf8mb4'
2022-08-18T02:54:47.942510Z 10775 Query SET SESSION net_write_timeout = 1800
2022-08-18T02:54:47.942524Z 10779 Query SET SESSION wait_timeout = 31536000
2022-08-18T02:54:47.942573Z 10781 Query SET SESSION net_write_timeout = 1800
2022-08-18T02:54:47.942635Z 10782 Query SET TIME_ZONE = '+00:00'
2022-08-18T02:54:47.942657Z 10779 Query SET TIME_ZONE = '+00:00'
2022-08-18T02:54:47.942580Z 10777 Connect [email protected] on using SSL/TLS
2022-08-18T02:54:47.942738Z 10781 Query SET SESSION wait_timeout = 31536000
2022-08-18T02:54:47.941543Z 10778 Query START TRANSACTION WITH CONSISTENT SNAPSHOT
2022-08-18T02:54:47.942829Z 10775 Query SET SESSION wait_timeout = 31536000
2022-08-18T02:54:47.942857Z 10781 Query SET TIME_ZONE = '+00:00'
2022-08-18T02:54:47.942950Z 10777 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2022-08-18T02:54:47.943020Z 10775 Query SET TIME_ZONE = '+00:00'
2022-08-18T02:54:47.943116Z 10777 Query START TRANSACTION WITH CONSISTENT SNAPSHOT
2022-08-18T02:54:47.943239Z 10777 Query SET SQL_MODE = ''
2022-08-18T02:54:47.943309Z 10778 Query SET SQL_MODE = ''
2022-08-18T02:54:47.943408Z 10774 Query SELECt @@GLOBAL.HOSTNAME
2022-08-18T02:54:47.943437Z 10777 Query SET NAMES 'utf8mb4'
2022-08-18T02:54:47.943473Z 10778 Query SET NAMES 'utf8mb4'
2022-08-18T02:54:47.943549Z 10774 Query SELECT @@GLOBAL.GTID_EXECUTED
2022-08-18T02:54:47.943630Z 10777 Query SET SESSION net_write_timeout = 1800
2022-08-18T02:54:47.943637Z 10778 Query SET SESSION net_write_timeout = 1800
2022-08-18T02:54:47.943768Z 10778 Query SET SESSION wait_timeout = 31536000
2022-08-18T02:54:47.943804Z 10777 Query SET SESSION wait_timeout = 31536000
2022-08-18T02:54:47.943928Z 10777 Query SET TIME_ZONE = '+00:00'
2022-08-18T02:54:47.943934Z 10774 Query SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,ORDINAL_POSITION,IS_NULLABLE,COLUMN_TYPE,EXTRA FROM information_schema.columns WHERe ((STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'test')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'t1')=0)OR(STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'lzm')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'sbtest1')=0)OR(STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'T_HM_HOUSE')=0)OR(STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'T_HM_HOUSEDEL')=0)OR(STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'T_HM_OWNER')=0)OR(STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'T_HM_PropertyRight')=0)OR(STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'checkpoint')=0)OR(STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'checkpoint_lox')=0)OR(STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'crm_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'T_CM_CUST')=0))
2022-08-18T02:54:47.943950Z 10778 Query SET TIME_ZONE = '+00:00'
2022-08-18T02:54:47.947852Z 10774 Query SELECt TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,COLUMN_NAME,SEQ_IN_INDEX FROM information_schema.statistics WHERe COLUMN_NAME IS NOT NULL AND NON_UNIQUE=0 AND ((STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'test')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'t1')=0)OR(STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'lzm')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'sbtest1')=0)OR(STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'T_HM_HOUSE')=0)OR(STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'T_HM_HOUSEDEL')=0)OR(STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'T_HM_OWNER')=0)OR(STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'T_HM_PropertyRight')=0)OR(STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'checkpoint')=0)OR(STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'checkpoint_lox')=0)OR(STRCMP(TABLE_SCHEMA COLLATE utf8_bin,'crm_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'T_CM_CUST')=0))
2022-08-18T02:54:47.950157Z 10774 Query SELECt SCHEMA_NAME,TABLE_NAME,COLUMN_NAME,JSON_EXTRACT(HISTOGRAM,'$."number-of-buckets-specified"') FROM information_schema.column_statistics WHERe ((STRCMP(SCHEMA_NAME COLLATE utf8_bin,'test')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'t1')=0)OR(STRCMP(SCHEMA_NAME COLLATE utf8_bin,'lzm')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'sbtest1')=0)OR(STRCMP(SCHEMA_NAME COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'T_HM_HOUSE')=0)OR(STRCMP(SCHEMA_NAME COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'T_HM_HOUSEDEL')=0)OR(STRCMP(SCHEMA_NAME COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'T_HM_OWNER')=0)OR(STRCMP(SCHEMA_NAME COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'T_HM_PropertyRight')=0)OR(STRCMP(SCHEMA_NAME COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'checkpoint')=0)OR(STRCMP(SCHEMA_NAME COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'checkpoint_lox')=0)OR(STRCMP(SCHEMA_NAME COLLATE utf8_bin,'crm_prd')=0 AND STRCMP(TABLE_NAME COLLATE utf8_bin,'T_CM_CUST')=0))
2022-08-18T02:54:47.950647Z 10774 Query SELECt DISTINCT user, host from mysql.user WHERe NOT(user.user='mysql.infoschema')AND NOT(user.user='mysql.session')AND NOT(user.user='mysql.sys')
2022-08-18T02:54:47.951329Z 10774 Query SELECt DISTINCT user, host FROM mysql.user WHERe authentication_string='' AND account_locked='Y' AND password_expired='Y' AND NOT(user.user='mysql.infoschema')AND NOT(user.user='mysql.session')AND NOT(user.user='mysql.sys')
2022-08-18T02:54:47.951598Z 10774 Query SELECt EVENT_SCHEMA,EVENT_NAME FROM information_schema.events WHERe (STRCMP(EVENT_SCHEMA COLLATE utf8_bin,'test')&STRCMP(EVENT_SCHEMA COLLATE utf8_bin,'lzm')&STRCMP(EVENT_SCHEMA COLLATE utf8_bin,'scsales_prd')&STRCMP(EVENT_SCHEMA COLLATE utf8_bin,'crm_prd'))=0
2022-08-18T02:54:47.952499Z 10774 Query SELECt ROUTINE_SCHEMA,ROUTINE_TYPE,ROUTINE_NAME FROM information_schema.routines WHERe (STRCMP(ROUTINE_SCHEMA COLLATE utf8_bin,'test')&STRCMP(ROUTINE_SCHEMA COLLATE utf8_bin,'lzm')&STRCMP(ROUTINE_SCHEMA COLLATE utf8_bin,'scsales_prd')&STRCMP(ROUTINE_SCHEMA COLLATE utf8_bin,'crm_prd'))=0
2022-08-18T02:54:47.953913Z 10774 Query SELECt TRIGGER_SCHEMA,EVENT_OBJECT_TABLE,TRIGGER_NAME,ACTION_ORDER FROM information_schema.triggers WHERe ((STRCMP(TRIGGER_SCHEMA COLLATE utf8_bin,'test')=0 AND STRCMP(EVENT_OBJECT_TABLE COLLATE utf8_bin,'t1')=0)OR(STRCMP(TRIGGER_SCHEMA COLLATE utf8_bin,'lzm')=0 AND STRCMP(EVENT_OBJECT_TABLE COLLATE utf8_bin,'sbtest1')=0)OR(STRCMP(TRIGGER_SCHEMA COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(EVENT_OBJECT_TABLE COLLATE utf8_bin,'T_HM_HOUSE')=0)OR(STRCMP(TRIGGER_SCHEMA COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(EVENT_OBJECT_TABLE COLLATE utf8_bin,'T_HM_HOUSEDEL')=0)OR(STRCMP(TRIGGER_SCHEMA COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(EVENT_OBJECT_TABLE COLLATE utf8_bin,'T_HM_OWNER')=0)OR(STRCMP(TRIGGER_SCHEMA COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(EVENT_OBJECT_TABLE COLLATE utf8_bin,'T_HM_PropertyRight')=0)OR(STRCMP(TRIGGER_SCHEMA COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(EVENT_OBJECT_TABLE COLLATE utf8_bin,'checkpoint')=0)OR(STRCMP(TRIGGER_SCHEMA COLLATE utf8_bin,'scsales_prd')=0 AND STRCMP(EVENT_OBJECT_TABLE COLLATE utf8_bin,'checkpoint_lox')=0)OR(STRCMP(TRIGGER_SCHEMA COLLATE utf8_bin,'crm_prd')=0 AND STRCMP(EVENT_OBJECT_TABLE COLLATE utf8_bin,'T_CM_CUST')=0))
2022-08-18T02:54:47.954901Z 10774 Query SHOW MASTER STATUS
2022-08-18T02:54:47.955153Z 10774 Query LOCK INSTANCE FOR BACKUP
2022-08-18T02:54:47.955271Z 10774 Query UNLOCK TABLES
2022-08-18T02:54:47.955493Z 10774 Query SHOW CREATE USER 'donor'@'%'
2022-08-18T02:54:47.955724Z 10774 Query SHOW CREATE USER 'lzm'@'%'
2022-08-18T02:54:47.955908Z 10774 Query SHOW CREATE USER 'proxyjiankong'@'%'
2022-08-18T02:54:47.956078Z 10774 Query SHOW CREATE USER 'repl_mgr'@'%'
2022-08-18T02:54:47.956247Z 10774 Query SHOW CREATE USER 'root'@'127.0.0.1'
2022-08-18T02:54:47.956436Z 10774 Query SHOW CREATE USER 'root'@'localhost'
2022-08-18T02:54:47.956624Z 10774 Query SHOW GRANTS FOR 'donor'@'%'
2022-08-18T02:54:47.956780Z 10774 Query SHOW GRANTS FOR 'lzm'@'%'
2022-08-18T02:54:47.957020Z 10774 Query SHOW GRANTS FOR 'proxyjiankong'@'%'
2022-08-18T02:54:47.957142Z 10774 Query SHOW GRANTS FOR 'repl_mgr'@'%'
2022-08-18T02:54:47.957267Z 10774 Query SHOW GRANTS FOR 'root'@'127.0.0.1'
2022-08-18T02:54:47.957490Z 10774 Query SHOW GRANTS FOR 'root'@'localhost'
2022-08-18T02:54:47.957836Z 10774 Query SHOW CREATE DATABASE IF NOT EXISTS crm_prd
2022-08-18T02:54:47.958018Z 10774 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.958140Z 10774 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.958279Z 10774 Query SHOW CREATE DATABASE IF NOT EXISTS scsales_prd
2022-08-18T02:54:47.958422Z 10774 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.958532Z 10774 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.958656Z 10774 Query SHOW CREATE DATABASE IF NOT EXISTS test
2022-08-18T02:54:47.958801Z 10774 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.958906Z 10774 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.959027Z 10774 Query SHOW CREATE DATABASE IF NOT EXISTS lzm
2022-08-18T02:54:47.959175Z 10774 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.959277Z 10774 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.959394Z 10774 Query USE `crm_prd`
2022-08-18T02:54:47.959518Z 10774 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-18T02:54:47.959620Z 10774 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.959722Z 10774 Query show create table `T_CM_CUST`
2022-08-18T02:54:47.960924Z 10774 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.961496Z 10774 Query show fields from `T_CM_CUST`
2022-08-18T02:54:47.962590Z 10774 Query USE `scsales_prd`
2022-08-18T02:54:47.962747Z 10774 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-18T02:54:47.962863Z 10774 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.962972Z 10774 Query show create table `checkpoint_lox`
2022-08-18T02:54:47.963509Z 10774 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.963714Z 10774 Query show fields from `checkpoint_lox`
2022-08-18T02:54:47.964505Z 10774 Query USE `scsales_prd`
2022-08-18T02:54:47.964649Z 10774 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-18T02:54:47.964765Z 10774 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.964870Z 10774 Query show create table `checkpoint`
2022-08-18T02:54:47.965515Z 10774 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.965784Z 10774 Query show fields from `checkpoint`
2022-08-18T02:54:47.966688Z 10774 Query USE `scsales_prd`
2022-08-18T02:54:47.966834Z 10774 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-18T02:54:47.966946Z 10774 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.967056Z 10774 Query show create table `T_HM_PropertyRight`
2022-08-18T02:54:47.968003Z 10774 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.968392Z 10774 Query show fields from `T_HM_PropertyRight`
2022-08-18T02:54:47.969391Z 10774 Query USE `scsales_prd`
2022-08-18T02:54:47.969545Z 10774 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-18T02:54:47.969654Z 10774 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.969768Z 10774 Query show create table `T_HM_OWNER`
2022-08-18T02:54:47.970455Z 10774 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.970746Z 10774 Query show fields from `T_HM_OWNER`
2022-08-18T02:54:47.971629Z 10774 Query USE `scsales_prd`
2022-08-18T02:54:47.971773Z 10774 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-18T02:54:47.971879Z 10774 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.971988Z 10774 Query show create table `T_HM_HOUSE`
2022-08-18T02:54:47.974079Z 10774 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.974989Z 10774 Query show fields from `T_HM_HOUSE`
2022-08-18T02:54:47.976496Z 10774 Query USE `scsales_prd`
2022-08-18T02:54:47.976642Z 10774 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-18T02:54:47.976758Z 10774 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.976863Z 10774 Query show create table `T_HM_HOUSEDEL`
2022-08-18T02:54:47.978141Z 10774 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.978664Z 10774 Query show fields from `T_HM_HOUSEDEL`
2022-08-18T02:54:47.979805Z 10774 Query USE `test`
2022-08-18T02:54:47.979945Z 10774 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-18T02:54:47.980055Z 10774 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.980176Z 10774 Query show create table `t1`
2022-08-18T02:54:47.980557Z 10774 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.980725Z 10774 Query show fields from `t1`
2022-08-18T02:54:47.981470Z 10774 Query USE `lzm`
2022-08-18T02:54:47.981618Z 10774 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-18T02:54:47.981735Z 10774 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.981846Z 10774 Query show create table `sbtest1`
2022-08-18T02:54:47.982288Z 10774 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.982477Z 10774 Query show fields from `sbtest1`
2022-08-18T02:54:47.983926Z 10774 Query SHOW CREATE USER 'donor'@'%'
2022-08-18T02:54:47.984156Z 10774 Query SHOW CREATE USER 'lzm'@'%'
2022-08-18T02:54:47.984331Z 10774 Query SHOW CREATE USER 'proxyjiankong'@'%'
2022-08-18T02:54:47.984497Z 10774 Query SHOW CREATE USER 'repl_mgr'@'%'
2022-08-18T02:54:47.984657Z 10774 Query SHOW CREATE USER 'root'@'127.0.0.1'
2022-08-18T02:54:47.984820Z 10774 Query SHOW CREATE USER 'root'@'localhost'
2022-08-18T02:54:47.984979Z 10774 Query SHOW GRANTS FOR 'donor'@'%'
2022-08-18T02:54:47.985114Z 10774 Query SHOW GRANTS FOR 'lzm'@'%'
2022-08-18T02:54:47.985340Z 10774 Query SHOW GRANTS FOR 'proxyjiankong'@'%'
2022-08-18T02:54:47.985466Z 10774 Query SHOW GRANTS FOR 'repl_mgr'@'%'
2022-08-18T02:54:47.985593Z 10774 Query SHOW GRANTS FOR 'root'@'127.0.0.1'
2022-08-18T02:54:47.985816Z 10774 Query SHOW GRANTS FOR 'root'@'localhost'
2022-08-18T02:54:47.986271Z 10780 Query SHOW CREATE DATABASE IF NOT EXISTS crm_prd
2022-08-18T02:54:47.986286Z 10778 Query USE `scsales_prd`
2022-08-18T02:54:47.986313Z 10782 Query SHOW CREATE DATABASE IF NOT EXISTS scsales_prd
2022-08-18T02:54:47.986440Z 10776 Query USE `crm_prd`
2022-08-18T02:54:47.986459Z 10775 Query USE `scsales_prd`
2022-08-18T02:54:47.986526Z 10780 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.986536Z 10778 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-18T02:54:47.986567Z 10782 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.986634Z 10776 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-18T02:54:47.986686Z 10780 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.986720Z 10775 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-18T02:54:47.986761Z 10776 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.986815Z 10782 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.986749Z 10779 Query USE `scsales_prd`
2022-08-18T02:54:47.986724Z 10778 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.987039Z 10776 Query show create table `T_CM_CUST`
2022-08-18T02:54:47.987160Z 10782 Query SHOW CREATE DATABASE IF NOT EXISTS test
2022-08-18T02:54:47.987290Z 10778 Query show create table `T_HM_PropertyRight`
2022-08-18T02:54:47.987337Z 10782 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.986742Z 10777 Query USE `scsales_prd`
2022-08-18T02:54:47.986955Z 10775 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.987046Z 10780 Query USE `scsales_prd`
2022-08-18T02:54:47.987510Z 10776 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.987526Z 10782 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.987493Z 10779 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-18T02:54:47.987660Z 10778 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.987680Z 10780 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-18T02:54:47.987666Z 10777 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-18T02:54:47.987744Z 10779 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.987687Z 10775 Query show create table `checkpoint`
2022-08-18T02:54:47.986782Z 10781 Query USE `scsales_prd`
2022-08-18T02:54:47.987818Z 10780 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.987869Z 10779 Query show create table `checkpoint_lox`
2022-08-18T02:54:47.987950Z 10780 Query show create table `T_HM_HOUSEDEL`
2022-08-18T02:54:47.988012Z 10782 Query USE `test`
2022-08-18T02:54:47.988124Z 10777 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.988152Z 10781 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-18T02:54:47.988130Z 10778 Query show fields from `T_HM_PropertyRight`
2022-08-18T02:54:47.988238Z 10775 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.988323Z 10777 Query show create table `T_HM_HOUSE`
2022-08-18T02:54:47.988369Z 10780 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.988653Z 10782 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-18T02:54:47.988759Z 10782 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.988862Z 10782 Query show create table `t1`
2022-08-18T02:54:47.988997Z 10775 Query show fields from `checkpoint`
2022-08-18T02:54:47.989071Z 10782 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.989168Z 10780 Query show fields from `T_HM_HOUSEDEL`
2022-08-18T02:54:47.989670Z 10781 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.989782Z 10776 Query show fields from `T_CM_CUST`
2022-08-18T02:54:47.990394Z 10777 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.990492Z 10782 Query show fields from `t1`
2022-08-18T02:54:47.991172Z 10778 Query SHOW CREATE DATABASE IF NOT EXISTS lzm
2022-08-18T02:54:47.991252Z 10781 Query show create table `T_HM_OWNER`
2022-08-18T02:54:47.991400Z 10777 Query show fields from `T_HM_HOUSE`
2022-08-18T02:54:47.991427Z 10778 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.991506Z 10779 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.991559Z 10781 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.991615Z 10775 Query USE `lzm`
2022-08-18T02:54:47.991774Z 10780 Query SELECt SQL_NO_CACHE `PKID` FROM `crm_prd`.`T_CM_CUST` ORDER BY `PKID` LIMIT 1
2022-08-18T02:54:47.991827Z 10775 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-18T02:54:47.991931Z 10779 Query show fields from `checkpoint_lox`
2022-08-18T02:54:47.992013Z 10775 Query SET SESSION character_set_results = 'binary'
2022-08-18T02:54:47.992112Z 10780 Query SELECt SQL_NO_CACHE `PKID` FROM `crm_prd`.`T_CM_CUST` ORDER BY `PKID` DESC LIMIT 1
2022-08-18T02:54:47.992176Z 10776 Query SELECt SQL_NO_CACHE `group_name`, `group_key`, `log_cmplt_csn`, `log_cmplt_xids_seq` FROM `scsales_prd`.`checkpoint_lox` ORDER BY `group_name`, `group_key`, `log_cmplt_csn`, `log_cmplt_xids_seq` LIMIT 1
2022-08-18T02:54:47.992423Z 10775 Query show create table `sbtest1`
2022-08-18T02:54:47.992542Z 10780 Query SELECt SQL_NO_CACHE `pkid` FROM `scsales_prd`.`T_HM_PropertyRight` ORDER BY `pkid` LIMIT 1
2022-08-18T02:54:47.992640Z 10776 Query SELECt SQL_NO_CACHE `pkid` FROM `scsales_prd`.`T_HM_OWNER` ORDER BY `pkid` LIMIT 1
2022-08-18T02:54:47.992863Z 10780 Query SELECt SQL_NO_CACHE `pkid` FROM `scsales_prd`.`T_HM_PropertyRight` ORDER BY `pkid` DESC LIMIT 1
2022-08-18T02:54:47.992882Z 10775 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.992963Z 10776 Query SELECt SQL_NO_CACHE `pkid` FROM `scsales_prd`.`T_HM_OWNER` ORDER BY `pkid` DESC LIMIT 1
2022-08-18T02:54:47.993163Z 10780 Query SELECt SQL_NO_CACHE `pkid` FROM `scsales_prd`.`T_HM_HOUSE` ORDER BY `pkid` LIMIT 1
2022-08-18T02:54:47.993178Z 10782 Query SELECt SQL_NO_CACHE `group_name`, `group_key` FROM `scsales_prd`.`checkpoint` ORDER BY `group_name`, `group_key` LIMIT 1
2022-08-18T02:54:47.993300Z 10778 Query SET SESSION character_set_results = 'utf8mb4'
2022-08-18T02:54:47.993303Z 10781 Query show fields from `T_HM_OWNER`
2022-08-18T02:54:47.993401Z 10776 Query SELECt SQL_NO_CACHE `pkid` FROM `scsales_prd`.`T_HM_HOUSEDEL` ORDER BY `pkid` LIMIT 1
2022-08-18T02:54:47.993528Z 10780 Query SELECt SQL_NO_CACHE `pkid` FROM `scsales_prd`.`T_HM_HOUSE` ORDER BY `pkid` DESC LIMIT 1
2022-08-18T02:54:47.993569Z 10777 Query SELECt SQL_NO_CACHE `id` FROM `lzm`.`sbtest1` ORDER BY `id` LIMIT 1
2022-08-18T02:54:47.993739Z 10775 Query show fields from `sbtest1`
2022-08-18T02:54:47.993774Z 10778 Query SELECt SQL_NO_CACHE `PKID`,`CUST_CODE`,`CUST_CATEGORY`,`CUST_STATUS`,`INVALID`,`INVALID_TIME`,`ORIGIN`,`CREATE_REASON_TYPE`,`BROKERAGECONTRACT_NO`,`IS_VIP`,`IS_INPOOL`,`IS_IMPORT`,`IMPORT_SRC`,`ORI_CUST_CODE`,`ORI_AGENT_ID`,`ORI_GRP_ID`,`REFER_USERID`,`CREATED_BY`,`CREATED_TIME`,`ACTIVE_TIME`,`INPOOL_TIME`,`BATCH_IMPORT`,`OLD_AGENCY_CODE`,`INVALID_USER`,`UPDATE_BY`,`UPDATE_TIME`,`BUSINESS_TYPE`,`APP_SOURCE`,`COMPANY_ID`,`ORIGIN_SECOND` FROM `crm_prd`.`T_CM_CUST` WHERe (`PKID` BETWEEN 58434474 AND 58438395) ORDER BY `PKID`
2022-08-18T02:54:47.993904Z 10780 Query SELECt SQL_NO_CACHE `PKID`,`CUST_CODE`,`CUST_CATEGORY`,`CUST_STATUS`,`INVALID`,`INVALID_TIME`,`ORIGIN`,`CREATE_REASON_TYPE`,`BROKERAGECONTRACT_NO`,`IS_VIP`,`IS_INPOOL`,`IS_IMPORT`,`IMPORT_SRC`,`ORI_CUST_CODE`,`ORI_AGENT_ID`,`ORI_GRP_ID`,`REFER_USERID`,`CREATED_BY`,`CREATED_TIME`,`ACTIVE_TIME`,`INPOOL_TIME`,`BATCH_IMPORT`,`OLD_AGENCY_CODE`,`INVALID_USER`,`UPDATE_BY`,`UPDATE_TIME`,`BUSINESS_TYPE`,`APP_SOURCE`,`COMPANY_ID`,`ORIGIN_SECOND` FROM `crm_prd`.`T_CM_CUST` WHERe (`PKID` BETWEEN 58438396 AND 58442317) ORDER BY `PKID`
2022-08-18T02:54:47.994686Z 10782 Query SELECt SQL_NO_CACHE `group_name`, `group_key` FROM `scsales_prd`.`checkpoint` ORDER BY `group_name` DESC, `group_key` DESC LIMIT 1
2022-08-18T02:54:47.994763Z 10779 Query SELECt SQL_NO_CACHE `c1` FROM `test`.`t1` ORDER BY `c1` LIMIT 1
2022-08-18T02:54:47.994977Z 10775 Query SELECt SQL_NO_CACHE `PKID`,`CUST_CODE`,`CUST_CATEGORY`,`CUST_STATUS`,`INVALID`,`INVALID_TIME`,`ORIGIN`,`CREATE_REASON_TYPE`,`BROKERAGECONTRACT_NO`,`IS_VIP`,`IS_INPOOL`,`IS_IMPORT`,`IMPORT_SRC`,`ORI_CUST_CODE`,`ORI_AGENT_ID`,`ORI_GRP_ID`,`REFER_USERID`,`CREATED_BY`,`CREATED_TIME`,`ACTIVE_TIME`,`INPOOL_TIME`,`BATCH_IMPORT`,`OLD_AGENCY_CODE`,`INVALID_USER`,`UPDATE_BY`,`UPDATE_TIME`,`BUSINESS_TYPE`,`APP_SOURCE`,`COMPANY_ID`,`ORIGIN_SECOND` FROM `crm_prd`.`T_CM_CUST` WHERe (`PKID` BETWEEN 58442318 AND 58446239) ORDER BY `PKID`
2022-08-18T02:54:47.995042Z 10779 Query SELECt SQL_NO_CACHE `c1` FROM `test`.`t1` ORDER BY `c1` DESC LIMIT 1
2022-08-18T02:54:47.994981Z 10776 Query SELECt SQL_NO_CACHE `pkid` FROM `scsales_prd`.`T_HM_HOUSEDEL` ORDER BY `pkid` DESC LIMIT 1
2022-08-18T02:54:47.995344Z 10781 Query SELECt SQL_NO_CACHE `PKID`,`CUST_CODE`,`CUST_CATEGORY`,`CUST_STATUS`,`INVALID`,`INVALID_TIME`,`ORIGIN`,`CREATE_REASON_TYPE`,`BROKERAGECONTRACT_NO`,`IS_VIP`,`IS_INPOOL`,`IS_IMPORT`,`IMPORT_SRC`,`ORI_CUST_CODE`,`ORI_AGENT_ID`,`ORI_GRP_ID`,`REFER_USERID`,`CREATED_BY`,`CREATED_TIME`,`ACTIVE_TIME`,`INPOOL_TIME`,`BATCH_IMPORT`,`OLD_AGENCY_CODE`,`INVALID_USER`,`UPDATE_BY`,`UPDATE_TIME`,`BUSINESS_TYPE`,`APP_SOURCE`,`COMPANY_ID`,`ORIGIN_SECOND` FROM `crm_prd`.`T_CM_CUST` WHERe (`PKID` BETWEEN 58446240 AND 58446241) ORDER BY `PKID`
2022-08-18T02:54:47.995642Z 10779 Query SELECt SQL_NO_CACHE `group_name`,`group_key`,`log_cmplt_csn`,`log_cmplt_xids_seq`,`log_cmplt_xids` FROM `scsales_prd`.`checkpoint_lox` ORDER BY `group_name`, `group_key`, `log_cmplt_csn`, `log_cmplt_xids_seq`
2022-08-18T02:54:47.995825Z 10776 Query SELECt SQL_NO_CACHE `pkid`,`HOUSE_ID`,`PR_TYPE`,`PR_CODE`,`PR_DATE`,`PR_ADDRESS`,`PR_OWNER`,`PR_OWNER_NAME`,`LAND_CERT_CODE`,`PARTI_OWNER`,`PARTI_TYPE`,`CREATE_BY`,`CREATE_TIME`,`IS_LATEST`,`PRICE_ORIGINAL`,`IS_LOAN`,`IS_LOCK`,`IS_FIRST_LOAN`,`SURPLUS_LOAN`,`flag`,`PR_MORTGAGE_TYPE`,`PR_PEOPLE_TYPE`,`PR_TOTAL`,`PR_SCHOOL`,`PR_LOAN_PRICE` FROM `scsales_prd`.`T_HM_PropertyRight` WHERe (`pkid` BETWEEN 3451171 AND 3453451) ORDER BY `pkid`
2022-08-18T02:54:48.003541Z 10781 Query SELECt SQL_NO_CACHE `pkid`,`STANDARD_ID`,`CITY_CODE`,`DISTRICT_CODE`,`RESBLOCK_ID`,`BUILDING_ID`,`BUILDING_NAME`,`ESTATE_ID`,`ESTATE_NAME`,`TOTAL_FLOOR`,`FLOOR_ALIAS`,`FLOOR`,`HOU_ADDR`,`ROOM_NO`,`MAILBOX_NO`,`IS_EXCHANGE`,`BEDROOM_AMOUNT`,`PARLOR_AMOUNT`,`COOKROOM_AMOUNT`,`TOILET_AMOUNT`,`BALCONY_AMOUNT`,`WAREHOUSE_AMOUNT`,`GARDEN_AMOUNT`,`TERRACE_AMOUNT`,`PROPERTY_FEE`,`BUILD_SIZE`,`USE_SIZE`,`NET_REG_SIZE`,`BUILD_END_YEAR`,`BUILDING_TYPE`,`PROPERTY_TYPE`,`ORIENTATION`,`USAGE_TYPE`,`SCENE`,`IS_PIVOT_FRAME`,`FRAME_CODE`,`FRAME_CODE_TYPE`,`PARLOR_SIZE`,`BEDROOM_SIZE`,`SANCTUM_SIZE`,`COOKROOM_SIZE`,`TOILET_SIZE`,`BALCONY_SIZE`,`WAREHOUSE_SIZE`,`GARDEN_SIZE`,`TERRACE_SIZE`,`IS_ANEAR_SUBWAY`,`IS_SCHOOL_DISTRICT`,`SCHOOL_QUOTA`,`IS_ECESIS`,`FITMENT_TYPE`,`FITMENT_AGE`,`IS_REBUILD`,`CREATED_BY`,`CREATED_TIME`,`UPDATED_BY`,`UPDATED_TIME`,`AGENCY_ID`,`OWENER_TIME`,`AGENCY_MARK`,`IS_HIDDEN_HOUSEROOM`,`IS_HIDDEN`,`IS_HIDDEN_USER`,`IS_HIDDEN_TIME`,`IS_HIDDEN_ORGID`,`isSold`,`isRent`,`IS_LOCK_AREA`,`isSoldOrRent`,`SUBBUILDINGSIZETYPE`,`SUBBUILDINGSIZE`,`is_deleted`,`flag`,`HAS_HOUDEL`,`UNIT_ID`,`UNIT_NAME`,`UNLUCKY` FROM `scsales_prd`.`T_HM_HOUSE` WHERe (`pkid` BETWEEN 700272664 AND 700273833) ORDER BY `pkid`
2022-08-18T02:54:48.004694Z 10777 Query SELECt SQL_NO_CACHE `id` FROM `lzm`.`sbtest1` ORDER BY `id` DESC LIMIT 1
2022-08-18T02:54:48.005040Z 10779 Query SELECt SQL_NO_CACHE `pkid`,`PR_ID`,`PARTI_ID`,`CONTRACT_TYPE`,`CREATE_BY`,`CREATE_TIME`,`STATUS`,`UPDATE_BY`,`UPDATE_TIME`,`GROUP_ID`,`PARTI_ID_TEL`,`NAME`,`COMPANYID`,`FIRST_TAG`,`SECOND_TAG` FROM `scsales_prd`.`T_HM_OWNER` WHERe (`pkid` BETWEEN 13893535 AND 13899359) ORDER BY `pkid`
2022-08-18T02:54:48.008704Z 10776 Query SELECt SQL_NO_CACHE `pkid`,`HOUSE_ID`,`PR_ID`,`HOUDEL_CODE`,`DELEGATION_CLIENT_ID`,`DELEGATION_TYPE`,`DELEGATION_SOURCE`,`DEL_DATE`,`RANK`,`SALE_REASON`,`STATE`,`CREAT_TIME`,`DESCR`,`NO_DISTURB`,`UPDATE_BY`,`UPDATE_TIME`,`ACTIVE_TIME`,`GRP_ID`,`CREATE_BY`,`price`,`UNIT_PRICE`,`CHANGE_ORIENT`,`refer_id`,`NO_DISTURB_USER`,`NO_DISTURB_MGRLINE`,`NO_DISTURB_DATE`,`LOOK_HOUSE_TIME`,`TRANSFER_HOUSE_TIME`,`MORTGAGE`,`PAY`,`RENT_TYPE`,`companyid`,`SELL_DESCR`,`DELEGATION_SOURCE_SECOND`,`REMARK_GRADE`,`REMARK_CONTENT`,`TRADE_YEAR`,`ONLY_HOUSE` FROM `scsales_prd`.`T_HM_HOUSEDEL` WHERe (`pkid` BETWEEN 45391988 AND 45394607) ORDER BY `pkid`
2022-08-18T02:54:48.009505Z 10777 Query SELECt SQL_NO_CACHE `c1`,`c2` FROM `test`.`t1` WHERe (`c1` BETWEEN 1 AND 1) ORDER BY `c1`
2022-08-18T02:54:48.009698Z 10782 Query SELECt SQL_NO_CACHE `group_name`,`group_key` FROM `scsales_prd`.`checkpoint` WHERe (`group_name`>'REPA' OR(`group_name`='REPA' AND(`group_key`>='1699064716'))) ORDER BY `group_name`, `group_key` LIMIT 3905,2
2022-08-18T02:54:48.011500Z 10782 Query SELECt SQL_NO_CACHE `pkid`,`HOUSE_ID`,`PR_ID`,`HOUDEL_CODE`,`DELEGATION_CLIENT_ID`,`DELEGATION_TYPE`,`DELEGATION_SOURCE`,`DEL_DATE`,`RANK`,`SALE_REASON`,`STATE`,`CREAT_TIME`,`DESCR`,`NO_DISTURB`,`UPDATE_BY`,`UPDATE_TIME`,`ACTIVE_TIME`,`GRP_ID`,`CREATE_BY`,`price`,`UNIT_PRICE`,`CHANGE_ORIENT`,`refer_id`,`NO_DISTURB_USER`,`NO_DISTURB_MGRLINE`,`NO_DISTURB_DATE`,`LOOK_HOUSE_TIME`,`TRANSFER_HOUSE_TIME`,`MORTGAGE`,`PAY`,`RENT_TYPE`,`companyid`,`SELL_DESCR`,`DELEGATION_SOURCE_SECOND`,`REMARK_GRADE`,`REMARK_CONTENT`,`TRADE_YEAR`,`ONLY_HOUSE` FROM `scsales_prd`.`T_HM_HOUSEDEL` WHERe (`pkid` BETWEEN 45394608 AND 45397227) ORDER BY `pkid`
2022-08-18T02:54:48.013940Z 10777 Query SELECt SQL_NO_CACHE `pkid`,`HOUSE_ID`,`PR_ID`,`HOUDEL_CODE`,`DELEGATION_CLIENT_ID`,`DELEGATION_TYPE`,`DELEGATION_SOURCE`,`DEL_DATE`,`RANK`,`SALE_REASON`,`STATE`,`CREAT_TIME`,`DESCR`,`NO_DISTURB`,`UPDATE_BY`,`UPDATE_TIME`,`ACTIVE_TIME`,`GRP_ID`,`CREATE_BY`,`price`,`UNIT_PRICE`,`CHANGE_ORIENT`,`refer_id`,`NO_DISTURB_USER`,`NO_DISTURB_MGRLINE`,`NO_DISTURB_DATE`,`LOOK_HOUSE_TIME`,`TRANSFER_HOUSE_TIME`,`MORTGAGE`,`PAY`,`RENT_TYPE`,`companyid`,`SELL_DESCR`,`DELEGATION_SOURCE_SECOND`,`REMARK_GRADE`,`REMARK_CONTENT`,`TRADE_YEAR`,`ONLY_HOUSE` FROM `scsales_prd`.`T_HM_HOUSEDEL` WHERe (`pkid` BETWEEN 45397228 AND 45399847) ORDER BY `pkid`
2022-08-18T02:54:48.019504Z 10778 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 1 AND 4545) ORDER BY `id`
2022-08-18T02:54:48.019755Z 10781 Query SELECt SQL_NO_CACHE `pkid`,`HOUSE_ID`,`PR_ID`,`HOUDEL_CODE`,`DELEGATION_CLIENT_ID`,`DELEGATION_TYPE`,`DELEGATION_SOURCE`,`DEL_DATE`,`RANK`,`SALE_REASON`,`STATE`,`CREAT_TIME`,`DESCR`,`NO_DISTURB`,`UPDATE_BY`,`UPDATE_TIME`,`ACTIVE_TIME`,`GRP_ID`,`CREATE_BY`,`price`,`UNIT_PRICE`,`CHANGE_ORIENT`,`refer_id`,`NO_DISTURB_USER`,`NO_DISTURB_MGRLINE`,`NO_DISTURB_DATE`,`LOOK_HOUSE_TIME`,`TRANSFER_HOUSE_TIME`,`MORTGAGE`,`PAY`,`RENT_TYPE`,`companyid`,`SELL_DESCR`,`DELEGATION_SOURCE_SECOND`,`REMARK_GRADE`,`REMARK_CONTENT`,`TRADE_YEAR`,`ONLY_HOUSE` FROM `scsales_prd`.`T_HM_HOUSEDEL` WHERe (`pkid` BETWEEN 45399848 AND 45399850) ORDER BY `pkid`
2022-08-18T02:54:48.024131Z 10781 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 4546 AND 9090) ORDER BY `id`
2022-08-18T02:54:48.028736Z 10775 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 9091 AND 13635) ORDER BY `id`
2022-08-18T02:54:48.029177Z 10780 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 13636 AND 18180) ORDER BY `id`
2022-08-18T02:54:48.029739Z 10779 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 18181 AND 22725) ORDER BY `id`
2022-08-18T02:54:48.034423Z 10776 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 22726 AND 27270) ORDER BY `id`
2022-08-18T02:54:48.038865Z 10777 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 27271 AND 31815) ORDER BY `id`
2022-08-18T02:54:48.044310Z 10778 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 31816 AND 36360) ORDER BY `id`
2022-08-18T02:54:48.044400Z 10782 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 36361 AND 40905) ORDER BY `id`
2022-08-18T02:54:48.058642Z 10781 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 40906 AND 45450) ORDER BY `id`
2022-08-18T02:54:48.058713Z 10775 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 54541 AND 59085) ORDER BY `id`
2022-08-18T02:54:48.058713Z 10779 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 49996 AND 54540) ORDER BY `id`
2022-08-18T02:54:48.059698Z 10776 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 59086 AND 63630) ORDER BY `id`
2022-08-18T02:54:48.060463Z 10780 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 45451 AND 49995) ORDER BY `id`
2022-08-18T02:54:48.062447Z 10777 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 63631 AND 68175) ORDER BY `id`
2022-08-18T02:54:48.072659Z 10782 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 68176 AND 72720) ORDER BY `id`
2022-08-18T02:54:48.080263Z 10778 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 72721 AND 77265) ORDER BY `id`
2022-08-18T02:54:48.083572Z 10775 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 81811 AND 86355) ORDER BY `id`
2022-08-18T02:54:48.084676Z 10779 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 77266 AND 81810) ORDER BY `id`
2022-08-18T02:54:48.088715Z 10781 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 86356 AND 90900) ORDER BY `id`
2022-08-18T02:54:48.088763Z 10776 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 90901 AND 95445) ORDER BY `id`
2022-08-18T02:54:48.089072Z 10777 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 95446 AND 99990) ORDER BY `id`
2022-08-18T02:54:48.091615Z 10780 Query SELECt SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `lzm`.`sbtest1` WHERe (`id` BETWEEN 99991 AND 100000) ORDER BY `id`
2022-08-18T02:54:48.094077Z 10780 Query SELECt SQL_NO_CACHE `group_name`,`group_key`,`seqno`,`rba`,`audit_ts`,`create_ts`,`last_update_ts`,`current_dir`,`log_bsn`,`log_csn`,`log_xid`,`log_cmplt_csn`,`log_cmplt_xids`,`version` FROM `scsales_prd`.`checkpoint` WHERe ((`group_name`>'REPA' OR(`group_name`='REPA' AND(`group_key`>='1699064716')))AND(`group_name`<'REPA' OR(`group_name`='REPA' AND(`group_key`<='1699064716')))) ORDER BY `group_name`, `group_key`
2022-08-18T02:54:48.096581Z 10780 Quit
2022-08-18T02:54:48.098476Z 10782 Quit
2022-08-18T02:54:48.104479Z 10778 Quit
2022-08-18T02:54:48.105438Z 10775 Quit
2022-08-18T02:54:48.109171Z 10779 Quit
2022-08-18T02:54:48.114663Z 10781 Quit
2022-08-18T02:54:48.114686Z 10777 Quit
2022-08-18T02:54:48.114693Z 10776 Quit
2022-08-18T02:54:48.115061Z 10774 Quit
2022-08-18T02:54:52.123209Z 10783 Connect [email protected] on using SSL/TLS
2022-08-18T02:54:52.123534Z 10783 Query SET NAMES utf8
2022-08-18T02:54:52.123743Z 10783 Query set autocommit=0
2022-08-18T02:54:52.123915Z 10783 Init DB information_schema
2022-08-18T02:54:52.124073Z 10783 Query show global variables
2022-08-18T02:54:52.129188Z 10783 Query show global status
2022-08-18T02:54:53.133325Z 10783 Query show global status
2022-08-18T02:54:53.136369Z 10783 Query select count(1) from processlist where state <> '' and user <> 'repl' and time > 2
2022-08-18T02:54:53.137221Z 10783 Query show slave status
2022-08-18T02:54:53.154499Z 10783 Query select * from information_schema.processlist where DB !='information_schema' and command !='Sleep'
2022-08-18T02:54:53.154990Z 10783 Query select SUBSTRING_INDEX(host,':',1) as connect_server, user connect_user,db connect_db, count(SUBSTRING_INDEX(host,':',1)) as connect_count from information_schema.processlist where db is not null and db!='information_schema' and db !='performance_schema' group by connect_server
2022-08-18T02:54:56.164583Z 10784 Connect [email protected] on using SSL/TLS
2022-08-18T02:54:56.164965Z 10784 Query SET NAMES utf8
2022-08-18T02:54:56.165182Z 10784 Query set autocommit=0
2022-08-18T02:54:56.165341Z 10784 Init DB information_schema
2022-08-18T02:54:56.173207Z 10783 Quit
2022-08-18T02:54:56.173216Z 10784 Quit
原理:
结合两个日志可以看到mysqlshell的备份分为以下几步:
1. 连接目标数据库.目标数据库会检查连接用户的权限.如果连接用户权限不足会报错.比如没有backup_admin权限
2. 执行FLUSH TABLES WITH READ LOCK,获取一致性快照.利用MVCC.
3. 获取各种系统信息,包括但不限于用户信息及schema以及二进制日志位点信息
4. 执行LOCK INSTANCE FOR BACKUP 开始备份,释放全局只读锁
5. 先备份用户信息及权限
6. 开始备份schema.先获取schema的DDL语句.再获取表的DDL语句
7. 如果开启并行.则开始并行备份表数据.如果开启了analyze table则在备份完毕.并行执行analyze
8. 在最后备份完毕时通过show slave status将binlog位点信息写入对应文件
mysqlshell备份的注意项
1. 必须是mysql的GA版本
2. mysqlshell8026之前不能备份5.6的用户信息.从8026开始支持备份5.6的用户
3. 只支持innodb热备.一致性备份
4. 备份用户最小权限:backup_admin event reload select show view trigger
5. 从mysqlshell8024版本开始备份用户需要有replication client权限.用于获取binlog位点等信息
6. 一定要开始兼容性检查,兼容性检查必须都是innodb的
7. 尽量让每个表都有主键.mysqlshell备份导入注意项
1. 必须开启local_infile 在配置文件中设置
