生活百科
mysql备份数据库命令(mysql数据库备份方法)
2022-11-05 15:54

背景

前边几篇文章我简单介绍了使用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 在配置文件中设置
发表评论
0评