MySQL Enterprise Backup 简称MEB
MEB8 对应的是MySQL8.0版本 （8.0.28）,备份工具的版本和MySQL版本要一致。
MEB8 新特性 参考链接 https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/what-is-new.html
link
部分信息：
| 不再支持脱机备份。备份服务器的二进制日志。备份副本服务器的中继日志。对于 MySQL Enterprise Backup 8.0.19 及更高版本：现在可以安全地在服务器上与备份操作并行进行DDL 操作（ CREATE TABLE、 RENAME TABLE、 DROP TABLE、 ALTER TABLE以及映射到ALTER TABLE的操作，例如CREATE INDEX ） |
|---|
软件下载需要oracle账号且会员未过期
下面是使用方法
对应MySQL版本8.0.19 , 工具版本mysqlbackup8.0.19
软件下载链接：http://10.186.18.11/confluence/pages/viewpage.action?pageId=18284910
解压之后添加环境就可以使用。
示例：
mysqlbackup --user=root --password=123456 --port=6666 --host=localhost --socket=/data/mysql/data/6666/mysqld.sock --backup-image=/tools/backup/my.mbi --backup-dir=/tools/backup backup-to-image # 为了便于观察 可追加 --debug --verbose
MySQL Enterprise Backup Ver 8.0.19-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Starting with following command line ...
mysqlbackup --user=root --password=xxxxxx --port=6666 --host=localhost
--socket=/data/mysql/data/6666/mysqld.sock
--backup-image=/tools/backup/my.mbi --backup-dir=/tools/backup
backup-to-image --debug --verbose
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'backup-to-image' run mysqlbackup
prints "mysqlbackup completed OK!".
220225 17:43:17 MAIN INFO: Starting to log actions.
220225 17:43:17 MAIN INFO: No SSL options specified.
220225 17:43:17 MAIN INFO: MySQL server version is '8.0.19'
220225 17:43:17 MAIN INFO: MySQL server compile os version is 'linux-glibc2.12'
220225 17:43:17 MAIN INFO: Compression algorithm used for connection is: 'uncompressed'
220225 17:43:17 MAIN INFO: Got some server configuration information from running server.
220225 17:43:17 MAIN INFO: No SSL options specified.
220225 17:43:17 MAIN INFO: Backup directory exists: '/tools/backup'
220225 17:43:17 MAIN INFO: MySQL server version_comment is 'MySQL Community Server - GPL'
220225 17:43:17 MAIN INFO: Mysqlbackup component not installed.
220225 17:43:17 MAIN INFO: Server is a community server.
220225 17:43:17 MAIN INFO: KEF target path:'/tools/backup/meta/keyring_kef'
220225 17:43:17 MAIN INFO: TDE Keyring service initialized.
220225 17:43:17 MAIN INFO: MEB logfile created at /tools/backup/meta/MEB_2022-02-25.17-43-17_image_backup.log
Backup Image Path = /tools/backup/my.mbi
220225 17:43:17 MAIN INFO: Unique generated backup id for this is 16457821973270968
220225 17:43:17 MAIN INFO: Copying the server config file '/data/mysql/data/6666/auto.cnf'
220225 17:43:17 MAIN INFO: Creating 14 buffers each of size 16777216.
220225 17:43:17 MAIN INFO: Failed to start redo log archiving...
220225 17:43:17 MAIN INFO: Found checkpoint at lsn 20441105.
220225 17:43:17 MAIN INFO: Starting log scan from lsn = 20441088 at offset = 20434944 and checkpoint = 20441105 in file /data/mysql/log/redolog/6666/ib_logfile0.
220225 17:43:17 MAIN INFO: Full Image Backup operation starts with following threads
1 read-threads 6 process-threads 1 write-threads
220225 17:43:17 MAIN INFO: No SSL options specified.
220225 17:43:17 RDR1 INFO: Copying meta file /tools/backup/backup-my.cnf.
220225 17:43:17 RDR1 INFO: Copying meta file /tools/backup/meta/backup_create.xml.
Adding file [Size: 314]: backup-my.cnf
220225 17:43:17 RLP1 INFO: Starting to parse redo log at lsn = 20441105, whereas checkpoint_lsn = 20441105.
220225 17:43:17 RDR1 INFO: Copying meta file /tools/backup/datadir/backup-auto.cnf.
Adding file [Size: 6308]: meta/backup_create.xml
Adding file [Size: 56]: datadir/backup-auto.cnf
220225 17:43:17 RDR1 INFO: Starting to copy all innodb files...
220225 17:43:17 RDR1 INFO: Copying /data/mysql/data/6666/ibdata1.
Adding file [Size: 1073741824]: datadir/ibdata1 space_id: 0
220225 17:43:18 RDR1 Progress in MB: 200 400 600 800 1000
220225 17:43:22 RDR1 INFO: Starting to copy all undo files...
220225 17:43:22 RDR1 INFO: Copying /data/mysql/data/6666/undo_002.
220225 17:43:22 RDR1 INFO: Copying /data/mysql/data/6666/undo_001.
Adding file [Size: 11534336]: datadir/undo_002
Adding file [Size: 10485760]: datadir/undo_001
220225 17:43:22 RDR1 INFO: Copying /data/mysql/data/6666/sys/sys_config.ibd.
Adding file [Size: 114688]: datadir/sys/sys_config.ibd space_id: 1
220225 17:43:22 RDR1 INFO: Copying /data/mysql/data/6666/universe/u_delay.ibd.
Adding file [Size: 114688]: datadir/universe/u_delay.ibd space_id: 2
220225 17:43:22 RDR1 INFO: Copying /data/mysql/data/6666/mysql/backup_progress.ibd.
Adding file [Size: 131072]: datadir/mysql/backup_progress.ibd space_id: 3
220225 17:43:22 RDR1 INFO: Copying /data/mysql/data/6666/mysql/backup_history.ibd.
Adding file [Size: 114688]: datadir/mysql/backup_history.ibd space_id: 4
220225 17:43:22 RDR1 INFO: Copying /data/mysql/data/6666/mysql.ibd.
Adding file [Size: 25165824]: datadir/mysql.ibd space_id: 4294967294
220225 17:43:22 RDR1 INFO: Completing the copy of innodb files.
220225 17:43:22 RDR1 INFO: Requesting a dump of the InnoDB buffer pool
220225 17:43:22 RDR1 INFO: Waiting for the dump of the InnoDB buffer pool to complete
220225 17:43:22 RDR1 INFO: The dump of the InnoDB buffer pool completed
220225 17:43:23 RDR1 INFO: Completing the copy of all non-innodb files.
Adding file [Size: 2240]: datadir/test01/t01_348.sdi
220225 17:43:23 WTR1 INFO: Adding database directory: datadir/universe
220225 17:43:23 RDR1 INFO: Requesting consistency information...
220225 17:43:23 RDR1 INFO: Locked the consistency point for 21765 microseconds.
220225 17:43:23 RDR1 INFO: Consistency point server_uuid '3f554b42-9617-11ec-8380-02000aba3e24'.
220225 17:43:23 RDR1 INFO: Consistency point gtid_executed '3f554b42-9617-11ec-8380-02000aba3e24:1-137'.
220225 17:43:23 RDR1 INFO: Consistency point binary_log_file 'mysql-bin.000002'.
220225 17:43:23 RDR1 INFO: Consistency point binary_log_position 30789.
220225 17:43:23 RDR1 INFO: Consistency point InnoDB lsn 20466016.
220225 17:43:23 RDR1 INFO: Consistency point InnoDB lsn_checkpoint 20441105.
220225 17:43:23 RDR1 INFO: Requesting completion of redo log copy after LSN 20466016.
220225 17:43:23 RLR1 INFO: Redo log reader waited 411 times for a total of 2055.00 ms for logs to generate.
220225 17:43:23 RDR1 INFO: Read-locked tables are unlocked.
Adding file [Size: 90]: datadir/mysql-bin.index
220225 17:43:23 RDR1 INFO: Copying /data/mysql/log/binlog/6666/mysql-bin.000002.
220225 17:43:23 RDR1 INFO: Completed the copy of binlog files...
Adding file [Size: 30789]: datadir/mysql-bin.000002
220225 17:43:23 RLW1 INFO: A copied database page was modified at 20447939. (This is the highest lsn found on a page)
220225 17:43:23 RLW1 INFO: Scanned log up to lsn 20466016.
220225 17:43:23 RLW1 INFO: Was able to parse the log up to lsn 20466016.
220225 17:43:23 RDR1 INFO: The server instance is unlocked after 0.246 seconds.
220225 17:43:23 RDR1 INFO: Reading all global variables from the server.
220225 17:43:23 RDR1 INFO: Completed reading of all 540 global variables from the server.
220225 17:43:23 RDR1 INFO: Writing server defaults files 'server-my.cnf' and 'server-all.cnf' for server '8.0.19' in '/tools/backup'.
220225 17:43:23 RDR1 INFO: Copying meta file /tools/backup/meta/backup_variables.txt.
220225 17:43:23 MAIN INFO: Full Image Backup operation completed successfully.
220225 17:43:23 MAIN INFO: Backup image created successfully.
220225 17:43:23 MAIN INFO: Image Path = /tools/backup/my.mbi
220225 17:43:23 MAIN INFO: MySQL binlog position: filename mysql-bin.000002, position 30789
220225 17:43:23 MAIN INFO: GTID_EXECUTED is 3f554b42-9617-11ec-8380-02000aba3e24:1-137
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 20441088
End LSN : 20466016
-------------------------------------------------------------
mysqlbackup completed OK!
上面的步骤包含：
copy 配置文件
copy all innodb files 也就是/data/mysql/data/6666/ibdata1.
copy all undo files
copy 表空间文件表ibd文件
dump of the InnoDB buffer pool to complete
copy Binlog files
lock instance for backup # 重点是这个 会做flush of redo log。
read-lock tables （1 tables are read-locked. 表示我们有1个Myisam表，本例中是我们手动创建了1张，对非事务表 执行了FLUSH TABLES `test01`.`t01` WITH READ LOCK） 如果全是innodb表 就没有这个锁，也不会有之后的UNLOCK TABLES
copy non-innodb files in subdirs of '/data/mysql/data/6666' 包括一下日志表
220225 17:43:23 WTR1 INFO: Adding database directory: datadir/mysql
Adding file [Size: 35]: datadir/mysql/general_log.CSM
Adding file [Size: 300455]: datadir/mysql/general_log.CSV
Adding file [Size: 5594]: datadir/mysql/general_log_201.sdi
Adding file [Size: 35]: datadir/mysql/slow_log.CSM
Adding file [Size: 0]: datadir/mysql/slow_log.CSV
Adding file [Size: 11803]: datadir/mysql/slow_log_202.sdi
220225 17:43:23 WTR1 INFO: Adding database directory: datadir/test01
Adding file [Size: 60]: datadir/test01/t01.MYD
Adding file [Size: 1024]: datadir/test01/t01.MYI
Completing the copy of all non-innodb files
Locked the consistency point for 21765 microseconds # 记录一致性信息
Read-locked tables are unlocked. 释放上面的锁 （UNLOCK TABLES 和 UNLOCK INSTANCE）
Completed the copy of binlog files 最后还要在copy 一次
备份过程对应的general_log信息
2022-02-25T17:43:17.316917+08:00 370 Connect root@localhost on using Socket
2022-02-25T17:43:17.317220+08:00 370 Query set autocommit=1
2022-02-25T17:43:17.317422+08:00 370 Query SET SESSION wait_timeout = 31536000
2022-02-25T17:43:17.317535+08:00 370 Query SET SQL_LOG_BIN = OFF
2022-02-25T17:43:17.317745+08:00 370 Query SELECT @@GLOBAL.version_compile_os
2022-02-25T17:43:17.317927+08:00 370 Query SELECT @@GLOBAL.server_uuid
2022-02-25T17:43:17.318067+08:00 370 Query SHOW SESSION STATUS LIKE 'Compression_algorithm'
2022-02-25T17:43:17.319244+08:00 370 Query SHOW SESSION STATUS LIKE 'ssl_version'
2022-02-25T17:43:17.319908+08:00 370 Query SELECT member_host, member_role, member_port FROM performance_schema.replication_group_members
2022-02-25T17:43:17.320172+08:00 370 Query SELECT @@GLOBAL.super_read_only
2022-02-25T17:43:17.320384+08:00 370 Query SELECT @@GLOBAL.innodb_undo_log_encrypt
2022-02-25T17:43:17.320550+08:00 370 Query SELECT @@GLOBAL.datadir
2022-02-25T17:43:17.320701+08:00 370 Query SELECT @@GLOBAL.innodb_data_home_dir
2022-02-25T17:43:17.320854+08:00 370 Query SELECT @@GLOBAL.innodb_data_file_path
2022-02-25T17:43:17.320950+08:00 370 Query SELECT @@GLOBAL.innodb_log_group_home_dir
2022-02-25T17:43:17.321048+08:00 370 Query SELECT @@GLOBAL.innodb_log_files_in_group
2022-02-25T17:43:17.321132+08:00 370 Query SELECT @@GLOBAL.innodb_log_file_size
2022-02-25T17:43:17.321259+08:00 370 Query SELECT @@GLOBAL.innodb_undo_directory
2022-02-25T17:43:17.321375+08:00 370 Query SELECT @@GLOBAL.innodb_undo_tablespaces
2022-02-25T17:43:17.321473+08:00 370 Query SELECT @@GLOBAL.innodb_buffer_pool_filename
2022-02-25T17:43:17.321557+08:00 370 Query SELECT @@GLOBAL.innodb_page_size
2022-02-25T17:43:17.321645+08:00 370 Query SELECT @@GLOBAL.innodb_checksum_algorithm
2022-02-25T17:43:17.322323+08:00 371 Connect root@localhost on using Socket
2022-02-25T17:43:17.322544+08:00 371 Query set autocommit=1
2022-02-25T17:43:17.322714+08:00 371 Query SET SESSION wait_timeout = 31536000
2022-02-25T17:43:17.322836+08:00 371 Query SET SQL_LOG_BIN = OFF
2022-02-25T17:43:17.323856+08:00 370 Query SELECT engine FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'backup_progress'
2022-02-25T17:43:17.325942+08:00 370 Query CREATE TABLE IF NOT EXISTS mysql.backup_progress ( `id` INT AUTO_INCREMENT PRIMARY KEY, `backup_id` BIGINT NOT NULL, `tool_name` VARCHAR(4096) NOT NULL, `error_code` INT NOT NULL, `error_message` VARCHAR(4096) NOT NULL, `current_time` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),`current_state` VARCHAR(200) NOT NULL, INDEX idx (`backup_id`, `current_time`)) ENGINE = INNODB
2022-02-25T17:43:17.326949+08:00 370 Prepare INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (?,?,?,?,?)
2022-02-25T17:43:17.425720+08:00 370 Query SELECT @@GLOBAL.version_comment
2022-02-25T17:43:17.426169+08:00 370 Query SELECT component_id FROM mysql.component WHERE component_urn LIKE '%component_mysqlbackup'
2022-02-25T17:43:17.426844+08:00 370 Query SELECT plugin_name FROM information_schema.plugins WHERE plugin_type='KEYRING' AND plugin_status='ACTIVE'
2022-02-25T17:43:17.470306+08:00 370 Query SELECT engine FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'backup_history'
2022-02-25T17:43:17.471476+08:00 370 Query SHOW SLAVE STATUS
2022-02-25T17:43:17.471702+08:00 370 Execute INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (16457821973270968,'mysqlbackup',0,'NO_ERROR','Started mysqlbackup.')
2022-02-25T17:43:17.609424+08:00 370 Query SELECT @@GLOBAL.innodb_redo_log_archive_dirs
2022-02-25T17:43:17.609725+08:00 370 Query SHOW ENGINE INNODB STATUS
2022-02-25T17:43:17.611813+08:00 370 Query SELECT @@GLOBAL.innodb_log_buffer_size
2022-02-25T17:43:17.612067+08:00 370 Query SELECT @@GLOBAL.innodb_log_checksums
2022-02-25T17:43:17.613420+08:00 372 Connect root@localhost on using Socket
2022-02-25T17:43:17.613758+08:00 372 Query set autocommit=1
2022-02-25T17:43:17.614025+08:00 372 Query SET SESSION wait_timeout = 31536000
2022-02-25T17:43:17.614195+08:00 372 Query SET SQL_LOG_BIN = OFF
2022-02-25T17:43:17.654914+08:00 370 Query SHOW ENGINE INNODB STATUS
2022-02-25T17:43:18.049277+08:00 153 Query show global status
2022-02-25T17:43:18.049431+08:00 156 Query show slave status
2022-02-25T17:43:18.049681+08:00 250 Query show global variables
2022-02-25T17:43:18.051178+08:00 159 Query select * from performance_schema.replication_group_members
2022-02-25T17:43:18.051221+08:00 155 Query select @@server_uuid
2022-02-25T17:43:18.052256+08:00 158 Query select * from information_schema.processlist where ((command='Binlog Dump') or (command = 'Binlog Dump GTID'))
2022-02-25T17:43:18.052336+08:00 157 Query show master status
2022-02-25T17:43:18.058067+08:00 154 Query select @@gtid_purged
2022-02-25T17:43:18.064720+08:00 159 Query show slave status
2022-02-25T17:43:18.232163+08:00 155 Query select @@version
2022-02-25T17:43:19.049198+08:00 156 Query show global status
2022-02-25T17:43:19.049570+08:00 153 Query show global variables
2022-02-25T17:43:19.049675+08:00 158 Query select * from information_schema.processlist where ((command='Binlog Dump') or (command = 'Binlog Dump GTID'))
2022-02-25T17:43:19.049713+08:00 157 Query select @@server_uuid
2022-02-25T17:43:19.049769+08:00 250 Query show slave status
2022-02-25T17:43:19.050222+08:00 154 Query select * from performance_schema.replication_group_members
2022-02-25T17:43:19.124945+08:00 159 Query show master status
2022-02-25T17:43:22.655495+08:00 370 Query SELECT FILE_ID, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND ENGINE = 'InnoDB' AND FILE_ID IS NOT NULL
2022-02-25T17:43:22.740396+08:00 370 Query SELECT FILE_ID, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE ENGINE = 'InnoDB' AND STATUS = 'NORMAL' AND FILE_TYPE <> 'TEMPORARY' AND FILE_TYPE <> 'UNDO LOG' AND FILE_ID <> 0
2022-02-25T17:43:22.833169+08:00 372 Query START TRANSACTION
2022-02-25T17:43:22.833427+08:00 372 Query SELECT 1 FROM `sys`.`sys_config` LIMIT 1
2022-02-25T17:43:22.845926+08:00 372 Query COMMIT
2022-02-25T17:43:22.846477+08:00 372 Query START TRANSACTION
2022-02-25T17:43:22.846607+08:00 372 Query SELECT 1 FROM `universe`.`u_delay` LIMIT 1
2022-02-25T17:43:22.847317+08:00 372 Query COMMIT
2022-02-25T17:43:22.847790+08:00 372 Query START TRANSACTION
2022-02-25T17:43:22.848035+08:00 372 Query SELECT 1 FROM `mysql`.`backup_progress` LIMIT 1
2022-02-25T17:43:22.848812+08:00 372 Query COMMIT
2022-02-25T17:43:22.849422+08:00 372 Query START TRANSACTION
2022-02-25T17:43:22.849534+08:00 372 Query SELECT 1 FROM `mysql`.`backup_history` LIMIT 1
2022-02-25T17:43:22.850192+08:00 372 Query COMMIT
2022-02-25T17:43:22.873842+08:00 370 Query SELECT @@GLOBAL.innodb_read_only
2022-02-25T17:43:22.874104+08:00 370 Query SET @@GLOBAL.innodb_buffer_pool_dump_now=ON
2022-02-25T17:43:22.974383+08:00 370 Query SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_dump_status'
2022-02-25T17:43:22.982988+08:00 370 Query SELECT @@GLOBAL.log_bin
2022-02-25T17:43:22.983221+08:00 370 Query SELECT @@GLOBAL.log_bin_basename
2022-02-25T17:43:22.983348+08:00 370 Query SELECT @@GLOBAL.log_bin_index
2022-02-25T17:43:22.983434+08:00 370 Query SELECT @@GLOBAL.relay_log_basename
2022-02-25T17:43:22.983520+08:00 370 Query SELECT @@GLOBAL.relay_log_index
2022-02-25T17:43:22.983602+08:00 370 Query SHOW SLAVE STATUS
2022-02-25T17:43:23.019272+08:00 370 Query SHOW MASTER STATUS
2022-02-25T17:43:23.020283+08:00 370 Query SHOW SLAVE STATUS
2022-02-25T17:43:23.020520+08:00 371 Query LOCK INSTANCE FOR BACKUP
2022-02-25T17:43:23.020788+08:00 370 Query SHOW ENGINE INNODB STATUS
2022-02-25T17:43:23.026598+08:00 370 Query SELECT FILE_ID, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE ENGINE = 'InnoDB' AND STATUS = 'NORMAL' AND FILE_TYPE <> 'TEMPORARY' AND FILE_ID <> 0
2022-02-25T17:43:23.032706+08:00 370 Query SELECT FILE_ID, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND ENGINE = 'InnoDB' AND FILE_ID IS NOT NULL
2022-02-25T17:43:23.039681+08:00 370 Execute INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (16457821973270968,'mysqlbackup',0,'NO_ERROR','mysqlbackup locking tables and copying other engines data.')
2022-02-25T17:43:23.040914+08:00 370 Query SELECT CONCAT('`', REPLACE(table_schema, '`','``'), '`.`', REPLACE(table_name,'`','``'), '`') AS full_tbl_name, CONCAT(table_schema, '.', table_name) AS tbl_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND engine <> 'InnoDB' AND engine <> 'PERFORMANCE_SCHEMA' AND (table_schema <> 'mysql' OR (table_name <> 'general_log' AND table_name <> 'slow_log' AND table_name <> 'backup_progress'))
2022-02-25T17:43:23.043136+08:00 371 Query FLUSH TABLES `test01`.`t01` WITH READ LOCK
2022-02-25T17:43:23.049061+08:00 153 Query show global status
2022-02-25T17:43:23.065193+08:00 159 Query show global variables
2022-02-25T17:43:23.065278+08:00 158 Query select @@server_uuid
2022-02-25T17:43:23.065214+08:00 156 Query show slave status
2022-02-25T17:43:23.067366+08:00 154 Query select * from information_schema.processlist where ((command='Binlog Dump') or (command = 'Binlog Dump GTID'))
2022-02-25T17:43:23.068010+08:00 157 Query show master status
2022-02-25T17:43:23.068393+08:00 155 Query select @@gtid_purged
2022-02-25T17:43:23.068533+08:00 250 Query select * from performance_schema.replication_group_members
2022-02-25T17:43:23.068882+08:00 153 Query show slave status
2022-02-25T17:43:23.071035+08:00 156 Query select @@version
2022-02-25T17:43:23.233947+08:00 371 Query SELECT SERVER_UUID, LOCAL, REPLICATION, STORAGE_ENGINES FROM performance_schema.log_status
2022-02-25T17:43:23.258173+08:00 371 Query UNLOCK TABLES
2022-02-25T17:43:23.266784+08:00 371 Query UNLOCK INSTANCE
2022-02-25T17:43:23.267133+08:00 370 Execute INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (16457821973270968,'mysqlbackup',0,'NO_ERROR','mysqlbackup unlocked the tables.')
2022-02-25T17:43:23.268503+08:00 370 Query SHOW PLUGINS
2022-02-25T17:43:23.269276+08:00 370 Query SHOW GLOBAL VARIABLES
2022-02-25T17:43:23.273328+08:00 370 Query SELECT variable_name, variable_source FROM performance_schema.variables_info
2022-02-25T17:43:23.334279+08:00 370 Query SELECT engine FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'backup_history'
2022-02-25T17:43:23.335379+08:00 370 Query SET SESSION SQL_MODE=''
2022-02-25T17:43:23.335537+08:00 370 Query USE mysql
2022-02-25T17:43:23.335804+08:00 370 Query CREATE TABLE IF NOT EXISTS mysql.backup_history( `backup_id` BIGINT NOT NULL, `tool_name` VARCHAR(4096) NOT NULL, `start_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', `end_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', `binlog_pos` BIGINT NOT NULL, `binlog_file` VARCHAR(255) NOT NULL, `compression_level` INT NOT NULL, `engines` VARCHAR(100) NOT NULL, `innodb_data_file_path` VARCHAR(2048) NOT NULL, `start_lsn` BIGINT NOT NULL, `end_lsn` BIGINT NOT NULL, `backup_type` VARCHAR(50) NOT NULL, `backup_format` VARCHAR(50) NOT NULL, `mysql_data_dir` VARCHAR(2048) NOT NULL, `innodb_data_home_dir` VARCHAR(2048) NOT NULL, `innodb_log_group_home_dir` VARCHAR(2048) NOT NULL, `innodb_log_files_in_group` VARCHAR(100) NOT NULL, `innodb_log_file_size` VARCHAR(100) NOT NULL, `backup_destination` VARCHAR(4096) NOT NULL, `lock_time` DOUBLE PRECISION (7,3) NOT NULL, `exit_state` VARCHAR(10) NOT NULL, `last_error` VARCHAR(4096) NOT NULL, `last_error_code` INT NOT NULL, `start_time_utc` BIGINT NOT NULL, `end_time_utc` BIGINT NOT NULL, `consistency_time_utc` BIGINT NOT NULL, `meb_version` VARCHAR(20) NOT NULL DEFAULT '0.0.0', `server_uuid` VARCHAR(36) NOT NULL, `binlog_master_key_seq_num` BIGINT NOT NULL DEFAULT 0, PRIMARY KEY (`backup_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
2022-02-25T17:43:23.336362+08:00 370 Query SHOW COLUMNS FROM mysql.backup_history LIKE 'innodb_file_format'
2022-02-25T17:43:23.337910+08:00 370 Query SHOW COLUMNS FROM mysql.backup_history LIKE 'incremental_base_lsn'
2022-02-25T17:43:23.338876+08:00 370 Query SHOW COLUMNS FROM mysql.backup_history LIKE 'binlog_master_key_seq_num'
2022-02-25T17:43:23.339840+08:00 370 Prepare INSERT INTO mysql.backup_history(backup_id, tool_name, start_time, end_time, binlog_pos, binlog_file, compression_level, engines, innodb_data_file_path, start_lsn, end_lsn, backup_type, backup_format, mysql_data_dir, innodb_data_home_dir, innodb_log_group_home_dir, innodb_log_files_in_group, innodb_log_file_size, backup_destination, lock_time, exit_state, last_error, last_error_code, start_time_utc, end_time_utc, consistency_time_utc, meb_version, server_uuid, binlog_master_key_seq_num) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
2022-02-25T17:43:23.340005+08:00 370 Execute INSERT INTO mysql.backup_history(backup_id, tool_name, start_time, end_time, binlog_pos, binlog_file, compression_level, engines, innodb_data_file_path, start_lsn, end_lsn, backup_type, backup_format, mysql_data_dir, innodb_data_home_dir, innodb_log_group_home_dir, innodb_log_files_in_group, innodb_log_file_size, backup_destination, lock_time, exit_state, last_error, last_error_code, start_time_utc, end_time_utc, consistency_time_utc, meb_version, server_uuid, binlog_master_key_seq_num) VALUES(16457821973270968,'mysqlbackup --user=root --password=xxxxxx --port=6666 --host=localhost --socket=/data/mysql/data/6666/mysqld.sock --backup-image=/tools/backup/my.mbi --backup-dir=/tools/backup backup-to-image --debug --verbose ','2022-02-25 17:43:17','2022-02-25 17:43:23',30789,'mysql-bin.000002',0,'CSV:InnoDB:MyISAM:PERFORMANCE_SCHEMA','ibdata1:1G:autoextend',20441088,20466016,'FULL','IMAGE','/data/mysql/data/6666/','','/data/mysql/log/redolog/6666','2','2147483648','/tools/backup',0.2463116,'SUCCESS','NO_ERROR',0,1645782197295060,1645782203316105,1645782203255603,'8.0.19','3f554b42-9617-11ec-8380-02000aba3e24',0)
2022-02-25T17:43:23.341029+08:00 370 Execute INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (16457821973270968,'mysqlbackup',0,'NO_ERROR','mysqlbackup returns success.')
2022-02-25T17:43:23.341809+08:00 372 Quit
2022-02-25T17:43:23.377439+08:00 370 Close stmt
2022-02-25T17:43:23.377458+08:00 371 Quit
2022-02-25T17:43:23.377621+08:00 370 Close stmt
2022-02-25T17:43:23.377675+08:00 370 Quit
mysqlbackup --backup-image=/tools/backup/my.mbi validate # 验证备份文件
MySQL Enterprise Backup Ver 8.0.19-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Starting with following command line ...
mysqlbackup --backup-image=/tools/backup/my.mbi validate
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'validate' run mysqlbackup
prints "mysqlbackup completed OK!".
220225 19:08:01 MAIN INFO: Starting to log actions.
220225 19:08:01 MAIN INFO: Backup Image MEB version string: 8.0.19 [2019-12-09 20:29:43]
220225 19:08:01 MAIN INFO: MySQL server version is '8.0.19'
220225 19:08:01 MAIN INFO: TDE Keyring service initialized.
220225 19:08:01 MAIN INFO: Creating 14 buffers each of size 16777216.
220225 19:08:01 MAIN INFO: Validate operation starts with following threads
1 read-threads 6 process-threads
220225 19:08:01 MAIN INFO: Validating image ... /tools/backup/my.mbi
220225 19:08:01 PCR1 INFO: Validate: [Dir]: meta
220225 19:08:02 RDR1 Progress in MB: 200 400 600 800 1000
220225 19:08:06 PCR1 INFO: Validate: [Dir]: datadir/mysql
220225 19:08:06 PCR1 INFO: Validate: [Dir]: datadir/performance_schema
220225 19:08:06 PCR1 INFO: Validate: [Dir]: datadir/sys
220225 19:08:06 PCR1 INFO: Validate: [Dir]: datadir/test01
220225 19:08:06 PCR5 INFO: Validate: [Dir]: datadir/universe
220225 19:08:06 MAIN INFO: datadir/mysql/backup_history.ibd validated.
220225 19:08:06 MAIN INFO: datadir/universe/u_delay.ibd validated.
220225 19:08:06 MAIN INFO: datadir/ibdata1 validated.
220225 19:08:06 MAIN INFO: datadir/undo_001 validated.
220225 19:08:06 MAIN INFO: datadir/mysql.ibd validated.
220225 19:08:06 MAIN INFO: datadir/mysql/backup_progress.ibd validated.
220225 19:08:06 MAIN INFO: datadir/undo_002 validated.
220225 19:08:06 MAIN INFO: datadir/sys/sys_config.ibd validated.
220225 19:08:06 MAIN INFO: Validate operation completed successfully.
220225 19:08:06 MAIN INFO: Backup Image validation successful.
220225 19:08:06 MAIN INFO: Source Image Path = /tools/backup/my.mbi
mysqlbackup completed OK!
Mysqlbackup 增量备份
page-track
3.解释下page-track 原理，在增量备份时page-track相对Optimistic Incremental Backup有什么优势，构造合适的场景并对比full-scan 和page-track 增量备份时的效率差异，给出操作步骤。
--incremental[=ALGORITHM]
Specifies the algorithm used for incremental backup. The
algorithm is either 'full-scan' 'optimistic' or 'page-track'.
If no value is given and if (i) mysqlbackup component is
installed and (ii) page-tracking is enabled and (iii)
all changed pages between the base the incremental backup
are tracked and (iv) approximate number of changed pages
is less than 50% of total InnoDB pages, then 'page-track
algorithm is used else will default to 'full-scan'.
The 'full-scan' algorithm looks into all InnoDB datafiles
for modified pages.
The 'page-track' algorithm will get the list of changed
from the component and copies them.
The 'optimistic' algorithm looks only into those InnoDB
datafiles that have been modified since the previous backup.
Refer [OPTIMISTIC-BACKUP-OPTIONS] section for more details
on latter algorithm.
个人理解page-track就是"页面跟踪"：如果更改页面的数量少于总InnoDB页面的50%。否则默认为'full-scan'。“full-scan”算法会检查所有InnoDB修改过的数据文件页面。
page-track 更适合低频率，数据量增长不大的数据库。
官方解释：对于 MySQL Enterprise Backup 8.0.18 及更高版本： mysqlbackup支持使用 MySQL 服务器的页面跟踪功能创建增量备份，通过该功能， mysqlbackup在 InnoDB 数据文件中查找自上次备份以来已修改的更改页面，然后复制它们。一般来说， 如果数据库中的大部分数据没有被修改，使用页面跟踪的增量备份比mysqlbackup执行的其他类型的增量备份更快
以下备份命令显示的相关信息:
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 6 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 7 for /data/mysql/data/6666/ibdata1
以下general 日志中显示相关查询:
2022-02-28T16:50:35.495442+08:00 14512 Query SELECT mysqlbackup_page_track_get_changed_page_count(22392546,0)
2022-02-28T16:50:35.495761+08:00 14512 Query SELECT mysqlbackup_page_track_get_changed_pages(22392546,0)
Mysqlbackup 页面跟踪增量备份 命令显示信息
mysqlbackup --user=root --password=123456 --port=6666 --host=localhost --socket=/data/mysql/data/6666/mysqld.sock --incremental=page-track --incremental-base=history:last_full_backup --with-timestamp --backup-dir=/tools/backup/incr_backup --backup-image=/tools/backup/incr_backup/incremental_image.mbi backup-to-image
MySQL Enterprise Backup Ver 8.0.19-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Starting with following command line ...
mysqlbackup --user=root --password=xxxxxx --port=6666 --host=localhost
--socket=/data/mysql/data/6666/mysqld.sock --incremental=page-track
--incremental-base=history:last_full_backup --with-timestamp
--backup-dir=/tools/backup/incr_backup
--backup-image=/tools/backup/incr_backup/incremental_image.mbi
backup-to-image
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'backup-to-image' run mysqlbackup
prints "mysqlbackup completed OK!".
220228 16:50:35 MAIN INFO: Starting to log actions.
220228 16:50:35 MAIN INFO: No SSL options specified.
220228 16:50:35 MAIN INFO: MySQL server version is '8.0.19'
220228 16:50:35 MAIN INFO: MySQL server compile os version is 'linux-glibc2.12'
220228 16:50:35 MAIN INFO: Got some server configuration information from running server.
220228 16:50:35 MAIN INFO: No SSL options specified.
220228 16:50:35 MAIN INFO: Backup directory created: '/tools/backup/incr_backup/2022-02-28_16-50-35'
220228 16:50:35 MAIN INFO: MySQL server version_comment is 'MySQL Community Server - GPL'
220228 16:50:35 MAIN INFO: Mysqlbackup component version : 8.0.19
220228 16:50:35 MAIN INFO: No SSL options specified.
220228 16:50:35 MAIN INFO: Location of last successful backup: /tools/backup.
220228 16:50:35 MAIN INFO: End time of last successful backup: 2022-02-28 16:25:49.
220228 16:50:35 MAIN INFO: Last backup type: FULL.
220228 16:50:35 MAIN INFO: Using start_lsn=22392546, calculated from backup_history table of MySQL server.
220228 16:50:35 MAIN INFO: Page tracking start lsn :20561585 backup start lsn: 22392546.
220228 16:50:35 MAIN INFO: Server is a community server.
220228 16:50:35 MAIN INFO: KEF target path:'/tools/backup/incr_backup/2022-02-28_16-50-35/meta/keyring_kef'
220228 16:50:35 MAIN INFO: TDE Keyring service initialized.
220228 16:50:35 MAIN INFO: MEB logfile created at /tools/backup/incr_backup/2022-02-28_16-50-35/meta/MEB_2022-02-28.16-50-35_inc_img_backup.log
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /data/mysql/data/6666/
innodb_data_home_dir =
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_group_home_dir = /data/mysql/log/redolog/6666
innodb_log_files_in_group = 2
innodb_log_file_size = 2147483648
innodb_undo_directory = /data/mysql/data/6666/
innodb_undo_tablespaces = 2
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /tools/backup/incr_backup/2022-02-28_16-50-35/datadir
innodb_data_home_dir = /tools/backup/incr_backup/2022-02-28_16-50-35/datadir
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_group_home_dir = /tools/backup/incr_backup/2022-02-28_16-50-35/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 2147483648
innodb_undo_directory = /tools/backup/incr_backup/2022-02-28_16-50-35/datadir
innodb_undo_tablespaces = 2
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
Backup Image Path = /tools/backup/incr_backup/incremental_image.mbi
220228 16:50:35 MAIN INFO: Unique generated backup id for this is 16460382351842397
220228 16:50:35 MAIN INFO: Copying the server config file '/data/mysql/data/6666/auto.cnf'
220228 16:50:35 MAIN INFO: Creating 15 buffers each of size 17301504.
220228 16:50:35 MAIN INFO: Failed to start redo log archiving...
220228 16:50:35 MAIN INFO: Found checkpoint at lsn 40454072.
220228 16:50:35 MAIN INFO: Starting log scan from lsn = 40453632 at offset = 40447488 and checkpoint = 40454072 in file /data/mysql/log/redolog/6666/ib_logfile0.
220228 16:50:35 MAIN INFO: Incremental Image Backup operation starts with following threads
1 read-threads 6 process-threads 1 write-threads
220228 16:50:35 MAIN INFO: No SSL options specified.
220228 16:50:35 MAIN INFO: [page-track] Getting changed page list.
220228 16:50:35 MAIN INFO: [page-track] 1383 pages modified after lsn 22392546.
220228 16:50:35 MAIN INFO: [page-track] Sorting file /data/mysql/data/6666/#meb/16460382351842397.idx to /tools/backup/incr_backup/2022-02-28_16-50-35/meta/16460382351842397.idx, with 259538944 bytes.
220228 16:50:35 RDR1 INFO: Copying meta file /tools/backup/incr_backup/2022-02-28_16-50-35/backup-my.cnf.
220228 16:50:35 RLP1 INFO: Starting to parse redo log at lsn = 40453696, whereas checkpoint_lsn = 40454072.
220228 16:50:35 RDR1 INFO: Copying meta file /tools/backup/incr_backup/2022-02-28_16-50-35/meta/backup_create.xml.
220228 16:50:35 RDR1 INFO: Copying meta file /tools/backup/incr_backup/2022-02-28_16-50-35/datadir/backup-auto.cnf.
220228 16:50:35 RDR1 INFO: Starting to copy all innodb files...
220228 16:50:35 RDR1 INFO: Copying /data/mysql/data/6666/ibdata1.
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 6 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 7 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 8 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 9 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 10 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 11 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 12 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 13 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 14 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 15 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 16 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 17 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 Progress in MB: 200
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 18 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 19 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 20 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 21 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 22 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 23 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 24 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 25 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 26 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 27 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 28 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 29 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 Progress in MB: 400
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 30 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 31 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 32 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 33 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 34 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 35 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 36 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 37 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 38 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 39 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 40 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 41 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 42 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 Progress in MB: 600
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 43 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 44 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 45 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 46 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 47 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 48 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 49 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 50 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 51 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 52 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 53 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 54 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 Progress in MB: 800
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 55 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 56 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 57 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 58 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 59 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 60 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 61 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 62 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 63 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 64 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 65 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 66 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 67 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 Progress in MB: 1000
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 68 for /data/mysql/data/6666/ibdata1
220228 16:50:35 RDR1 INFO: Starting to copy all undo files...
220228 16:50:35 RDR1 INFO: Copying /data/mysql/data/6666/undo_002.
220228 16:50:35 RDR1 INFO: Copying /data/mysql/data/6666/undo_001.
220228 16:50:35 RDR1 INFO: Copying /data/mysql/data/6666/sys/sys_config.ibd.
220228 16:50:35 RDR1 INFO: Copying /data/mysql/data/6666/universe/u_delay.ibd.
220228 16:50:35 RDR1 INFO: Copying /data/mysql/data/6666/mysql/backup_progress.ibd.
220228 16:50:35 RDR1 INFO: Copying /data/mysql/data/6666/mysql/backup_history.ibd.
220228 16:50:35 RDR1 INFO: Copying /data/mysql/data/6666/mysql.ibd.
220228 16:50:35 RDR1 INFO: No incremental pages in fragment 76 for /data/mysql/data/6666/mysql.ibd
220228 16:50:35 RDR1 INFO: Completing the copy of innodb files.
220228 16:50:35 RDR1 INFO: Requesting a dump of the InnoDB buffer pool
220228 16:50:35 RDR1 INFO: Waiting for the dump of the InnoDB buffer pool to complete
220228 16:50:36 RDR1 INFO: The dump of the InnoDB buffer pool completed
220228 16:50:36 RDR1 INFO: Binary Log Basename: '/data/mysql/log/binlog/6666/mysql-bin'
220228 16:50:36 RDR1 INFO: Binary Log Index: '/data/mysql/log/binlog/6666/mysql-bin.index'
220228 16:50:36 RDR1 INFO: Relay Channel: 'group_replication_applier'
220228 16:50:36 RDR1 INFO: Relay Log Basename: '/data/mysql/log/relaylog/6666/mysql-relay-group_replication_applier'
220228 16:50:36 RDR1 INFO: Relay Log Index: '/data/mysql/log/relaylog/6666/mysql-relay-group_replication_applier.index'
220228 16:50:36 RDR1 INFO: Relay Channel: 'group_replication_recovery'
220228 16:50:36 RDR1 INFO: Relay Log Basename: '/data/mysql/log/relaylog/6666/mysql-relay-group_replication_applier-group_replication_recovery'
220228 16:50:36 RDR1 INFO: Relay Log Index: '/data/mysql/log/relaylog/6666/mysql-relay-group_replication_recovery.index'
220228 16:50:36 RDR1 INFO: Starting to copy Binlog files.
220228 16:50:36 RDR1 INFO: Starting to lock instance for backup...
220228 16:50:36 RDR1 INFO: The server instance is locked for backup.
220228 16:50:36 RDR1 INFO: Requesting flush of redo log reading after LSN 40454341.
220228 16:50:36 RDR1 INFO: Requesting flush of redo log processing after LSN 40454341.
220228 16:50:36 RDR1 INFO: Completed flush of redo log reading after LSN 40454341.
220228 16:50:36 RDR1 INFO: Completed flush of redo log processing after LSN 40454341.
220228 16:50:36 RDR1 INFO: Starting to read-lock tables...
220228 16:50:36 RDR1 INFO: 1 tables are read-locked.
220228 16:50:36 RDR1 INFO: Opening backup source directory '/data/mysql/data/6666'
220228 16:50:36 RDR1 INFO: Starting to copy non-innodb files in subdirs of '/data/mysql/data/6666'
220228 16:50:36 WTR1 INFO: Adding database directory: datadir/mysql
220228 16:50:36 WTR1 INFO: Adding database directory: datadir/performance_schema
220228 16:50:36 WTR1 INFO: Adding database directory: datadir/sys
220228 16:50:36 WTR1 INFO: Adding database directory: datadir/test01
220228 16:50:36 RDR1 INFO: Completing the copy of all non-innodb files.
220228 16:50:36 WTR1 INFO: Adding database directory: datadir/universe
220228 16:50:36 RDR1 INFO: Requesting consistency information...
220228 16:50:36 RDR1 INFO: Locked the consistency point for 1293 microseconds.
220228 16:50:36 RDR1 INFO: Consistency point server_uuid '3f554b42-9617-11ec-8380-02000aba3e24'.
220228 16:50:36 RDR1 INFO: Consistency point gtid_executed '3f554b42-9617-11ec-8380-02000aba3e24:1-21307'.
220228 16:50:36 RDR1 INFO: Consistency point binary_log_file 'mysql-bin.000002'.
220228 16:50:36 RDR1 INFO: Consistency point binary_log_position 11751014.
220228 16:50:36 RDR1 INFO: Consistency point InnoDB lsn 40454624.
220228 16:50:36 RDR1 INFO: Consistency point InnoDB lsn_checkpoint 40454072.
220228 16:50:36 RDR1 INFO: Requesting completion of redo log copy after LSN 40454624.
220228 16:50:36 RLR1 INFO: Redo log reader waited 61 times for a total of 305.00 ms for logs to generate.
220228 16:50:36 RLW1 INFO: A copied database page was modified at 0. (This is the highest lsn found on a page)
220228 16:50:36 RLW1 INFO: Scanned log up to lsn 40454624.
220228 16:50:36 RLW1 INFO: Was able to parse the log up to lsn 40454624.
220228 16:50:36 RDR1 INFO: Read-locked tables are unlocked.
220228 16:50:36 RDR1 INFO: Copying /data/mysql/log/binlog/6666/mysql-bin.000002.
220228 16:50:36 RDR1 INFO: Completed the copy of binlog files...
220228 16:50:36 RDR1 INFO: The server instance is unlocked after 0.437 seconds.
220228 16:50:36 RDR1 INFO: Reading all global variables from the server.
220228 16:50:36 RDR1 INFO: Completed reading of all 541 global variables from the server.
220228 16:50:36 RDR1 INFO: Writing server defaults files 'server-my.cnf' and 'server-all.cnf' for server '8.0.19' in '/tools/backup/incr_backup/2022-02-28_16-50-35'.
220228 16:50:36 RDR1 INFO: Copying meta file /tools/backup/incr_backup/2022-02-28_16-50-35/meta/backup_variables.txt.
220228 16:50:36 RDR1 INFO: Copying meta file /tools/backup/incr_backup/2022-02-28_16-50-35/datadir/ibbackup_logfile.
220228 16:50:36 RDR1 INFO: Copying meta file /tools/backup/incr_backup/2022-02-28_16-50-35/server-all.cnf.
220228 16:50:36 RDR1 INFO: Copying meta file /tools/backup/incr_backup/2022-02-28_16-50-35/server-my.cnf.
220228 16:50:36 RDR1 INFO: Copying meta file /tools/backup/incr_backup/2022-02-28_16-50-35/datadir/ibbackup_ibd_files.
220228 16:50:36 RDR1 INFO: Copying meta file /tools/backup/incr_backup/2022-02-28_16-50-35/meta/backup_gtid_executed.sql.
220228 16:50:36 RDR1 INFO: Copying meta file /tools/backup/incr_backup/2022-02-28_16-50-35/meta/backup_content.xml.
220228 16:50:36 RDR1 INFO: Copying meta file /tools/backup/incr_backup/2022-02-28_16-50-35/meta/image_files.xml.
220228 16:50:36 MAIN INFO: Reset the backup id.
220228 16:50:36 MAIN INFO: Incremental Image Backup operation completed successfully.
220228 16:50:36 MAIN INFO: Backup image created successfully.
220228 16:50:36 MAIN INFO: Image Path = /tools/backup/incr_backup/incremental_image.mbi
220228 16:50:36 MAIN INFO: Backup contains changes from lsn 22392547 to lsn 40454624
220228 16:50:36 MAIN INFO: MySQL binlog position: filename mysql-bin.000002, position 11751014
220228 16:50:36 MAIN INFO: GTID_EXECUTED is 3f554b42-9617-11ec-8380-02000aba3e24:1-21307
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 22392547
End LSN : 40454624
-------------------------------------------------------------
mysqlbackup completed OK!
Mysqlbackup 页面跟踪增量备份general_log
mysqlbackup --user=root --password=123456 --port=6666 --host=localhost --socket=/data/mysql/data/6666/mysqld.sock --incremental=page-track --incremental-base=history:last_full_backup --with-timestamp --backup-dir=/tools/backup/incr_backup --backup-image=/tools/backup/incr_backup/incremental_image.mbi backup-to-image
2022-02-28T16:50:35.173868+08:00 14512 Connect root@localhost on using Socket
2022-02-28T16:50:35.174254+08:00 14512 Query set autocommit=1
2022-02-28T16:50:35.174486+08:00 14512 Query SET SESSION wait_timeout = 31536000
2022-02-28T16:50:35.174607+08:00 14512 Query SET SQL_LOG_BIN = OFF
2022-02-28T16:50:35.174898+08:00 14512 Query SELECT @@GLOBAL.version_compile_os
2022-02-28T16:50:35.175152+08:00 14512 Query SELECT @@GLOBAL.server_uuid
2022-02-28T16:50:35.175324+08:00 14512 Query SHOW SESSION STATUS LIKE 'ssl_version'
2022-02-28T16:50:35.177292+08:00 14512 Query SELECT member_host, member_role, member_port FROM performance_schema.replication_group_members
2022-02-28T16:50:35.177524+08:00 14512 Query SELECT @@GLOBAL.super_read_only
2022-02-28T16:50:35.177682+08:00 14512 Query SELECT @@GLOBAL.innodb_undo_log_encrypt
2022-02-28T16:50:35.177810+08:00 14512 Query SELECT @@GLOBAL.datadir
2022-02-28T16:50:35.177921+08:00 14512 Query SELECT @@GLOBAL.innodb_data_home_dir
2022-02-28T16:50:35.178034+08:00 14512 Query SELECT @@GLOBAL.innodb_data_file_path
2022-02-28T16:50:35.178133+08:00 14512 Query SELECT @@GLOBAL.innodb_log_group_home_dir
2022-02-28T16:50:35.178242+08:00 14512 Query SELECT @@GLOBAL.innodb_log_files_in_group
2022-02-28T16:50:35.178347+08:00 14512 Query SELECT @@GLOBAL.innodb_log_file_size
2022-02-28T16:50:35.178432+08:00 14512 Query SELECT @@GLOBAL.innodb_undo_directory
2022-02-28T16:50:35.178514+08:00 14512 Query SELECT @@GLOBAL.innodb_undo_tablespaces
2022-02-28T16:50:35.178600+08:00 14512 Query SELECT @@GLOBAL.innodb_buffer_pool_filename
2022-02-28T16:50:35.178695+08:00 14512 Query SELECT @@GLOBAL.innodb_page_size
2022-02-28T16:50:35.178783+08:00 14512 Query SELECT @@GLOBAL.innodb_checksum_algorithm
2022-02-28T16:50:35.179569+08:00 14513 Connect root@localhost on using Socket
2022-02-28T16:50:35.179888+08:00 14513 Query set autocommit=1
2022-02-28T16:50:35.180083+08:00 14513 Query SET SESSION wait_timeout = 31536000
2022-02-28T16:50:35.180308+08:00 14513 Query SET SQL_LOG_BIN = OFF
2022-02-28T16:50:35.180622+08:00 14512 Query SELECT engine FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'backup_progress'
2022-02-28T16:50:35.182799+08:00 14512 Query CREATE TABLE IF NOT EXISTS mysql.backup_progress ( `id` INT AUTO_INCREMENT PRIMARY KEY, `backup_id` BIGINT NOT NULL, `tool_name` VARCHAR(4096) NOT NULL, `error_code` INT NOT NULL, `error_message` VARCHAR(4096) NOT NULL, `current_time` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),`current_state` VARCHAR(200) NOT NULL, INDEX idx (`backup_id`, `current_time`)) ENGINE = INNODB
2022-02-28T16:50:35.183981+08:00 14512 Prepare INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (?,?,?,?,?)
2022-02-28T16:50:35.210428+08:00 14512 Query SELECT @@GLOBAL.version_comment
2022-02-28T16:50:35.211038+08:00 14512 Query SELECT component_id FROM mysql.component WHERE component_urn LIKE '%component_mysqlbackup'
2022-02-28T16:50:35.211486+08:00 14512 Query SHOW GLOBAL STATUS LIKE 'mysqlbackup.component_version'
2022-02-28T16:50:35.213206+08:00 14512 Query SET @@GLOBAL.mysqlbackup.backupid='16460382351842397'
2022-02-28T16:50:35.213476+08:00 14512 Query SELECT mysqlbackup_page_track_get_start_lsn()
2022-02-28T16:50:35.214484+08:00 14514 Connect root@localhost on using Socket
2022-02-28T16:50:35.214783+08:00 14514 Query set autocommit=1
2022-02-28T16:50:35.215009+08:00 14514 Query SET SESSION wait_timeout = 31536000
2022-02-28T16:50:35.215162+08:00 14514 Query SET SQL_LOG_BIN = OFF
2022-02-28T16:50:35.215581+08:00 14514 Prepare SELECT end_lsn, backup_destination, end_time, backup_type, consistency_time_utc FROM mysql.backup_history WHERE exit_state = 'SUCCESS' AND backup_type = 'FULL' AND server_uuid = @@server_uuid ORDER BY end_time DESC, end_lsn DESC LIMIT 0,1
2022-02-28T16:50:35.215688+08:00 14514 Execute SELECT end_lsn, backup_destination, end_time, backup_type, consistency_time_utc FROM mysql.backup_history WHERE exit_state = 'SUCCESS' AND backup_type = 'FULL' AND server_uuid = @@server_uuid ORDER BY end_time DESC, end_lsn DESC LIMIT 0,1
2022-02-28T16:50:35.216448+08:00 14514 Close stmt
2022-02-28T16:50:35.216548+08:00 14514 Quit
2022-02-28T16:50:35.216613+08:00 14512 Query SELECT binlog_file FROM mysql.backup_history WHERE exit_state='SUCCESS' AND backup_type = 'FULL' AND server_uuid = @@server_uuid ORDER BY end_time DESC, end_lsn DESC LIMIT 0,1
2022-02-28T16:50:35.217365+08:00 14512 Query SELECT plugin_name FROM information_schema.plugins WHERE plugin_type='KEYRING' AND plugin_status='ACTIVE'
2022-02-28T16:50:35.278468+08:00 14512 Query SELECT engine FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'backup_history'
2022-02-28T16:50:35.279629+08:00 14512 Query SHOW SLAVE STATUS
2022-02-28T16:50:35.279868+08:00 14512 Execute INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (16460382351842397,'mysqlbackup',0,'NO_ERROR','Started mysqlbackup.')
2022-02-28T16:50:35.490494+08:00 14512 Query SELECT @@GLOBAL.innodb_redo_log_archive_dirs
2022-02-28T16:50:35.490838+08:00 14512 Query SHOW ENGINE INNODB STATUS
2022-02-28T16:50:35.493090+08:00 14512 Query SELECT @@GLOBAL.innodb_log_buffer_size
2022-02-28T16:50:35.493344+08:00 14512 Query SELECT @@GLOBAL.innodb_log_checksums
2022-02-28T16:50:35.494458+08:00 14515 Connect root@localhost on using Socket
2022-02-28T16:50:35.494640+08:00 14515 Query set autocommit=1
2022-02-28T16:50:35.494824+08:00 14515 Query SET SESSION wait_timeout = 31536000
2022-02-28T16:50:35.494956+08:00 14515 Query SET SQL_LOG_BIN = OFF
2022-02-28T16:50:35.495442+08:00 14512 Query SELECT mysqlbackup_page_track_get_changed_page_count(22392546,0)
2022-02-28T16:50:35.495761+08:00 14512 Query SELECT mysqlbackup_page_track_get_changed_pages(22392546,0)
2022-02-28T16:50:35.611574+08:00 14512 Query SHOW ENGINE INNODB STATUS
2022-02-28T16:50:35.625878+08:00 14512 Query SELECT FILE_ID, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND ENGINE = 'InnoDB' AND FILE_ID IS NOT NULL
2022-02-28T16:50:35.706796+08:00 14512 Query SELECT FILE_ID, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE ENGINE = 'InnoDB' AND STATUS = 'NORMAL' AND FILE_TYPE <> 'TEMPORARY' AND FILE_TYPE <> 'UNDO LOG' AND FILE_ID <> 0
2022-02-28T16:50:35.965017+08:00 14515 Query START TRANSACTION
2022-02-28T16:50:35.965470+08:00 14515 Query SELECT 1 FROM `sys`.`sys_config` LIMIT 1
2022-02-28T16:50:35.966552+08:00 14515 Query COMMIT
2022-02-28T16:50:35.967073+08:00 14515 Query START TRANSACTION
2022-02-28T16:50:35.967264+08:00 14515 Query SELECT 1 FROM `universe`.`u_delay` LIMIT 1
2022-02-28T16:50:35.968279+08:00 14515 Query COMMIT
2022-02-28T16:50:35.969248+08:00 14515 Query START TRANSACTION
2022-02-28T16:50:35.969397+08:00 14515 Query SELECT 1 FROM `mysql`.`backup_progress` LIMIT 1
2022-02-28T16:50:35.970905+08:00 14515 Query COMMIT
2022-02-28T16:50:35.971403+08:00 14515 Query START TRANSACTION
2022-02-28T16:50:35.971519+08:00 14515 Query SELECT 1 FROM `mysql`.`backup_history` LIMIT 1
2022-02-28T16:50:35.973749+08:00 14515 Query COMMIT
2022-02-28T16:50:35.994261+08:00 14512 Query SELECT @@GLOBAL.innodb_read_only
2022-02-28T16:50:35.994561+08:00 14512 Query SET @@GLOBAL.innodb_buffer_pool_dump_now=ON
2022-02-28T16:50:36.012599+08:00 154 Query select * from performance_schema.replication_group_members
2022-02-28T16:50:36.012646+08:00 155 Query show slave status
2022-02-28T16:50:36.012680+08:00 159 Query select @@server_uuid
2022-02-28T16:50:36.012604+08:00 156 Query show global variables
2022-02-28T16:50:36.012614+08:00 157 Query show global status
2022-02-28T16:50:36.013430+08:00 250 Query select * from information_schema.processlist where ((command='Binlog Dump') or (command = 'Binlog Dump GTID'))
2022-02-28T16:50:36.013910+08:00 153 Query show master status
2022-02-28T16:50:36.015603+08:00 158 Query select @@gtid_purged
2022-02-28T16:50:36.016155+08:00 154 Query show slave status
2022-02-28T16:50:36.020207+08:00 155 Query select @@version
2022-02-28T16:50:36.094928+08:00 14512 Query SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_dump_status'
2022-02-28T16:50:36.096448+08:00 14512 Query SELECT @@GLOBAL.log_bin
2022-02-28T16:50:36.096614+08:00 14512 Query SELECT @@GLOBAL.log_bin_basename
2022-02-28T16:50:36.096714+08:00 14512 Query SELECT @@GLOBAL.log_bin_index
2022-02-28T16:50:36.096806+08:00 14512 Query SELECT @@GLOBAL.relay_log_basename
2022-02-28T16:50:36.096894+08:00 14512 Query SELECT @@GLOBAL.relay_log_index
2022-02-28T16:50:36.096987+08:00 14512 Query SHOW SLAVE STATUS
2022-02-28T16:50:36.110368+08:00 14512 Query SHOW MASTER STATUS
2022-02-28T16:50:36.110756+08:00 14512 Query SELECT binlog_file FROM mysql.backup_history WHERE exit_state='SUCCESS' AND backup_type = 'FULL' AND server_uuid = @@server_uuid ORDER BY end_time DESC, end_lsn DESC LIMIT 0,1
2022-02-28T16:50:36.111253+08:00 14512 Query SELECT binlog_master_key_seq_num FROM mysql.backup_history WHERE exit_state='SUCCESS' AND backup_type = 'FULL' AND server_uuid = @@server_uuid ORDER BY end_time DESC, end_lsn DESC LIMIT 0, 1
2022-02-28T16:50:36.111652+08:00 14512 Query SHOW SLAVE STATUS
2022-02-28T16:50:36.111844+08:00 14513 Query LOCK INSTANCE FOR BACKUP
2022-02-28T16:50:36.111988+08:00 14512 Query SHOW ENGINE INNODB STATUS
2022-02-28T16:50:36.122057+08:00 14512 Query SELECT FILE_ID, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE ENGINE = 'InnoDB' AND STATUS = 'NORMAL' AND FILE_TYPE <> 'TEMPORARY' AND FILE_ID <> 0
2022-02-28T16:50:36.126221+08:00 14512 Query SELECT FILE_ID, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND ENGINE = 'InnoDB' AND FILE_ID IS NOT NULL
2022-02-28T16:50:36.145197+08:00 14512 Execute INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (16460382351842397,'mysqlbackup',0,'NO_ERROR','mysqlbackup locking tables and copying other engines data.')
2022-02-28T16:50:36.149112+08:00 14512 Query SELECT CONCAT('`', REPLACE(table_schema, '`','``'), '`.`', REPLACE(table_name,'`','``'), '`') AS full_tbl_name, CONCAT(table_schema, '.', table_name) AS tbl_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND engine <> 'InnoDB' AND engine <> 'PERFORMANCE_SCHEMA' AND (table_schema <> 'mysql' OR (table_name <> 'general_log' AND table_name <> 'slow_log' AND table_name <> 'backup_progress'))
2022-02-28T16:50:36.150958+08:00 14513 Query FLUSH TABLES `test01`.`t01` WITH READ LOCK
2022-02-28T16:50:36.473722+08:00 14513 Query SELECT SERVER_UUID, LOCAL, REPLICATION, STORAGE_ENGINES FROM performance_schema.log_status
2022-02-28T16:50:36.479366+08:00 14513 Query UNLOCK TABLES
2022-02-28T16:50:36.548464+08:00 14513 Query UNLOCK INSTANCE
2022-02-28T16:50:36.548789+08:00 14512 Execute INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (16460382351842397,'mysqlbackup',0,'NO_ERROR','mysqlbackup unlocked the tables.')
2022-02-28T16:50:36.555296+08:00 14512 Query SHOW PLUGINS
2022-02-28T16:50:36.556221+08:00 14512 Query SHOW GLOBAL VARIABLES
2022-02-28T16:50:36.559846+08:00 14512 Query SELECT variable_name, variable_source FROM performance_schema.variables_info
2022-02-28T16:50:36.664731+08:00 14512 Query SELECT engine FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'backup_history'
2022-02-28T16:50:36.666218+08:00 14512 Query SET SESSION SQL_MODE=''
2022-02-28T16:50:36.666428+08:00 14512 Query USE mysql
2022-02-28T16:50:36.666805+08:00 14512 Query CREATE TABLE IF NOT EXISTS mysql.backup_history( `backup_id` BIGINT NOT NULL, `tool_name` VARCHAR(4096) NOT NULL, `start_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', `end_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', `binlog_pos` BIGINT NOT NULL, `binlog_file` VARCHAR(255) NOT NULL, `compression_level` INT NOT NULL, `engines` VARCHAR(100) NOT NULL, `innodb_data_file_path` VARCHAR(2048) NOT NULL, `start_lsn` BIGINT NOT NULL, `end_lsn` BIGINT NOT NULL, `backup_type` VARCHAR(50) NOT NULL, `backup_format` VARCHAR(50) NOT NULL, `mysql_data_dir` VARCHAR(2048) NOT NULL, `innodb_data_home_dir` VARCHAR(2048) NOT NULL, `innodb_log_group_home_dir` VARCHAR(2048) NOT NULL, `innodb_log_files_in_group` VARCHAR(100) NOT NULL, `innodb_log_file_size` VARCHAR(100) NOT NULL, `backup_destination` VARCHAR(4096) NOT NULL, `lock_time` DOUBLE PRECISION (7,3) NOT NULL, `exit_state` VARCHAR(10) NOT NULL, `last_error` VARCHAR(4096) NOT NULL, `last_error_code` INT NOT NULL, `start_time_utc` BIGINT NOT NULL, `end_time_utc` BIGINT NOT NULL, `consistency_time_utc` BIGINT NOT NULL, `meb_version` VARCHAR(20) NOT NULL DEFAULT '0.0.0', `server_uuid` VARCHAR(36) NOT NULL, `binlog_master_key_seq_num` BIGINT NOT NULL DEFAULT 0, PRIMARY KEY (`backup_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
2022-02-28T16:50:36.667307+08:00 14512 Query SHOW COLUMNS FROM mysql.backup_history LIKE 'innodb_file_format'
2022-02-28T16:50:36.668789+08:00 14512 Query SHOW COLUMNS FROM mysql.backup_history LIKE 'incremental_base_lsn'
2022-02-28T16:50:36.669671+08:00 14512 Query SHOW COLUMNS FROM mysql.backup_history LIKE 'binlog_master_key_seq_num'
2022-02-28T16:50:36.670749+08:00 14512 Prepare INSERT INTO mysql.backup_history(backup_id, tool_name, start_time, end_time, binlog_pos, binlog_file, compression_level, engines, innodb_data_file_path, start_lsn, end_lsn, backup_type, backup_format, mysql_data_dir, innodb_data_home_dir, innodb_log_group_home_dir, innodb_log_files_in_group, innodb_log_file_size, backup_destination, lock_time, exit_state, last_error, last_error_code, start_time_utc, end_time_utc, consistency_time_utc, meb_version, server_uuid, binlog_master_key_seq_num) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
2022-02-28T16:50:36.670947+08:00 14512 Execute INSERT INTO mysql.backup_history(backup_id, tool_name, start_time, end_time, binlog_pos, binlog_file, compression_level, engines, innodb_data_file_path, start_lsn, end_lsn, backup_type, backup_format, mysql_data_dir, innodb_data_home_dir, innodb_log_group_home_dir, innodb_log_files_in_group, innodb_log_file_size, backup_destination, lock_time, exit_state, last_error, last_error_code, start_time_utc, end_time_utc, consistency_time_utc, meb_version, server_uuid, binlog_master_key_seq_num) VALUES(16460382351842397,'mysqlbackup --user=root --password=xxxxxx --port=6666 --host=localhost --socket=/data/mysql/data/6666/mysqld.sock --incremental=page-track --incremental-base=history:last_full_backup --with-timestamp --backup-dir=/tools/backup/incr_backup --backup-image=/tools/backup/incr_backup/incremental_image.mbi backup-to-image ','2022-02-28 16:50:35','2022-02-28 16:50:36',11751014,'mysql-bin.000002',0,'CSV:InnoDB:MyISAM:PERFORMANCE_SCHEMA','ibdata1:1G:autoextend',22392547,40454624,'DIFFERENTIAL','IMAGE','/data/mysql/data/6666/','','/data/mysql/log/redolog/6666','2','2147483648','/tools/backup/incr_backup/2022-02-28_16-50-35',0.4367299,'SUCCESS','NO_ERROR',0,1646038235171213,1646038236563425,1646038236474782,'8.0.19','3f554b42-9617-11ec-8380-02000aba3e24',0)
2022-02-28T16:50:36.671835+08:00 14512 Execute INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (16460382351842397,'mysqlbackup',0,'NO_ERROR','mysqlbackup returns success.')
2022-02-28T16:50:36.672380+08:00 14512 Query SELECT mysqlbackup_page_track_set(true)
2022-02-28T16:50:36.672755+08:00 14512 Query SET @@GLOBAL.mysqlbackup.backupid=NULL
2022-02-28T16:50:36.682504+08:00 14515 Quit
2022-02-28T16:50:36.694342+08:00 14513 Quit
2022-02-28T16:50:36.694529+08:00 14512 Close stmt
2022-02-28T16:50:36.694648+08:00 14512 Close stmt
2022-02-28T16:50:36.694687+08:00 14512 Quit
full-scan
mysqlbackup --user=root --password=123456 --port=6666 --host=localhost --socket=/data/mysql/data/6666/mysqld.sock --incremental --incremental-base=history:last_full_backup --with-timestamp --backup-dir=/tools/backup/incr_backup_full_scan --backup-image=/tools/backup/incr_backup_full_scan/incremental_image.mbi backup-to-image
MySQL Enterprise Backup Ver 8.0.19-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Starting with following command line ...
mysqlbackup --user=root --password=xxxxxx --port=6666 --host=localhost
--socket=/data/mysql/data/6666/mysqld.sock --incremental
--incremental-base=history:last_full_backup --with-timestamp
--backup-dir=/tools/backup/incr_backup_full_scan
--backup-image=/tools/backup/incr_backup_full_scan/incremental_image.mbi
backup-to-image
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'backup-to-image' run mysqlbackup
prints "mysqlbackup completed OK!".
220228 17:40:29 MAIN INFO: Starting to log actions.
220228 17:40:29 MAIN INFO: No SSL options specified.
220228 17:40:29 MAIN INFO: MySQL server version is '8.0.19'
220228 17:40:29 MAIN INFO: MySQL server compile os version is 'linux-glibc2.12'
220228 17:40:29 MAIN INFO: Got some server configuration information from running server.
220228 17:40:29 MAIN INFO: No SSL options specified.
220228 17:40:29 MAIN INFO: Backup directory created: '/tools/backup/incr_backup_full_scan/2022-02-28_17-40-29'
220228 17:40:29 MAIN INFO: MySQL server version_comment is 'MySQL Community Server - GPL'
220228 17:40:29 MAIN INFO: Mysqlbackup component version : 8.0.19
220228 17:40:29 MAIN INFO: No SSL options specified.
220228 17:40:29 MAIN INFO: Location of last successful backup: /tools/backup.
220228 17:40:29 MAIN INFO: End time of last successful backup: 2022-02-28 16:25:49.
220228 17:40:29 MAIN INFO: Last backup type: FULL.
220228 17:40:29 MAIN INFO: Using start_lsn=22392546, calculated from backup_history table of MySQL server.
220228 17:40:29 MAIN INFO: Page tracking start lsn :20561585 backup start lsn: 22392546.
220228 17:40:29 MAIN INFO: [page-track] 1846 pages modified after lsn 22392546.
220228 17:40:29 MAIN INFO: [page-track] Total pages:1565, 1846 pages changed after lsn 22392546, change %: 117
220228 17:40:29 MAIN INFO: Using "full-scan" algorithm for this incremental backup.
220228 17:40:29 MAIN INFO: Server is a community server.
220228 17:40:29 MAIN INFO: KEF target path:'/tools/backup/incr_backup_full_scan/2022-02-28_17-40-29/meta/keyring_kef'
220228 17:40:29 MAIN INFO: TDE Keyring service initialized.
220228 17:40:29 MAIN INFO: MEB logfile created at /tools/backup/incr_backup_full_scan/2022-02-28_17-40-29/meta/MEB_2022-02-28.17-40-29_inc_img_backup.log
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /data/mysql/data/6666/
innodb_data_home_dir =
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_group_home_dir = /data/mysql/log/redolog/6666
innodb_log_files_in_group = 2
innodb_log_file_size = 2147483648
innodb_undo_directory = /data/mysql/data/6666/
innodb_undo_tablespaces = 2
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /tools/backup/incr_backup_full_scan/2022-02-28_17-40-29/datadir
innodb_data_home_dir = /tools/backup/incr_backup_full_scan/2022-02-28_17-40-29/datadir
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_group_home_dir = /tools/backup/incr_backup_full_scan/2022-02-28_17-40-29/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 2147483648
innodb_undo_directory = /tools/backup/incr_backup_full_scan/2022-02-28_17-40-29/datadir
innodb_undo_tablespaces = 2
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
Backup Image Path = /tools/backup/incr_backup_full_scan/incremental_image.mbi
220228 17:40:29 MAIN INFO: Unique generated backup id for this is 16460412291573084
220228 17:40:29 MAIN INFO: Copying the server config file '/data/mysql/data/6666/auto.cnf'
220228 17:40:29 MAIN INFO: Creating 15 buffers each of size 17301504.
220228 17:40:29 MAIN INFO: Failed to start redo log archiving...
220228 17:40:29 MAIN INFO: Found checkpoint at lsn 55178303.
220228 17:40:29 MAIN INFO: Starting log scan from lsn = 55178240 at offset = 55172096 and checkpoint = 55178303 in file /data/mysql/log/redolog/6666/ib_logfile0.
220228 17:40:29 MAIN INFO: Incremental Image Backup operation starts with following threads
1 read-threads 6 process-threads 1 write-threads
220228 17:40:29 MAIN INFO: No SSL options specified.
220228 17:40:29 RDR1 INFO: Copying meta file /tools/backup/incr_backup_full_scan/2022-02-28_17-40-29/backup-my.cnf.
220228 17:40:29 RDR1 INFO: Copying meta file /tools/backup/incr_backup_full_scan/2022-02-28_17-40-29/meta/backup_create.xml.
220228 17:40:29 RDR1 INFO: Copying meta file /tools/backup/incr_backup_full_scan/2022-02-28_17-40-29/datadir/backup-auto.cnf.
220228 17:40:29 RDR1 INFO: Starting to copy all innodb files...
220228 17:40:29 RDR1 INFO: Copying /data/mysql/data/6666/ibdata1.
220228 17:40:30 RDR1 Progress in MB: 200 400 600 800 1000
220228 17:40:32 RDR1 INFO: Starting to copy all undo files...
220228 17:40:32 RDR1 INFO: Copying /data/mysql/data/6666/undo_002.
220228 17:40:32 RDR1 INFO: Copying /data/mysql/data/6666/undo_001.
220228 17:40:32 RDR1 INFO: Copying /data/mysql/data/6666/sys/sys_config.ibd.
220228 17:40:32 RDR1 INFO: Copying /data/mysql/data/6666/universe/u_delay.ibd.
220228 17:40:32 RDR1 INFO: Copying /data/mysql/data/6666/mysql/backup_progress.ibd.
220228 17:40:32 RDR1 INFO: Copying /data/mysql/data/6666/mysql/backup_history.ibd.
220228 17:40:32 RDR1 INFO: Copying /data/mysql/data/6666/mysql.ibd.
220228 17:40:33 RDR1 INFO: Completing the copy of innodb files.
220228 17:40:33 RDR1 INFO: Requesting a dump of the InnoDB buffer pool
220228 17:40:33 RDR1 INFO: Waiting for the dump of the InnoDB buffer pool to complete
220228 17:40:33 RDR1 INFO: The dump of the InnoDB buffer pool completed
220228 17:40:33 RDR1 INFO: Binary Log Basename: '/data/mysql/log/binlog/6666/mysql-bin'
220228 17:40:33 RDR1 INFO: Binary Log Index: '/data/mysql/log/binlog/6666/mysql-bin.index'
220228 17:40:33 RDR1 INFO: Relay Channel: 'group_replication_applier'
220228 17:40:33 RDR1 INFO: Relay Log Basename: '/data/mysql/log/relaylog/6666/mysql-relay-group_replication_applier'
220228 17:40:33 RDR1 INFO: Relay Log Index: '/data/mysql/log/relaylog/6666/mysql-relay-group_replication_applier.index'
220228 17:40:33 RDR1 INFO: Relay Channel: 'group_replication_recovery'
220228 17:40:33 RDR1 INFO: Relay Log Basename: '/data/mysql/log/relaylog/6666/mysql-relay-group_replication_applier-group_replication_recovery'
220228 17:40:33 RDR1 INFO: Relay Log Index: '/data/mysql/log/relaylog/6666/mysql-relay-group_replication_recovery.index'
220228 17:40:33 RDR1 INFO: Starting to copy Binlog files.
220228 17:40:33 RDR1 INFO: Starting to lock instance for backup...
220228 17:40:33 RDR1 INFO: The server instance is locked for backup.
220228 17:40:33 RDR1 INFO: Requesting flush of redo log reading after LSN 55178556.
220228 17:40:33 RDR1 INFO: Requesting flush of redo log processing after LSN 55178556.
220228 17:40:33 RDR1 INFO: Waiting to read redo log LSN 55178556, have 55178240.
220228 17:40:33 RLP1 INFO: Starting to parse redo log at lsn = 55178293, whereas checkpoint_lsn = 55178303.
220228 17:40:33 RDR1 INFO: Completed flush of redo log reading after LSN 55184037.
220228 17:40:33 RDR1 INFO: Completed flush of redo log processing after LSN 55184037.
220228 17:40:33 RDR1 INFO: Starting to read-lock tables...
220228 17:40:33 RDR1 INFO: 1 tables are read-locked.
220228 17:40:33 RDR1 INFO: Opening backup source directory '/data/mysql/data/6666'
220228 17:40:33 RDR1 INFO: Starting to copy non-innodb files in subdirs of '/data/mysql/data/6666'
220228 17:40:33 WTR1 INFO: Adding database directory: datadir/mysql
220228 17:40:33 WTR1 INFO: Adding database directory: datadir/performance_schema
220228 17:40:33 WTR1 INFO: Adding database directory: datadir/sys
220228 17:40:33 WTR1 INFO: Adding database directory: datadir/test01
220228 17:40:33 RDR1 INFO: Completing the copy of all non-innodb files.
220228 17:40:33 WTR1 INFO: Adding database directory: datadir/universe
220228 17:40:33 RDR1 INFO: Requesting consistency information...
220228 17:40:33 RDR1 INFO: Locked the consistency point for 872 microseconds.
220228 17:40:33 RDR1 INFO: Consistency point server_uuid '3f554b42-9617-11ec-8380-02000aba3e24'.
220228 17:40:33 RDR1 INFO: Consistency point gtid_executed '3f554b42-9617-11ec-8380-02000aba3e24:1-38114'.
220228 17:40:33 RDR1 INFO: Consistency point binary_log_file 'mysql-bin.000002'.
220228 17:40:33 RDR1 INFO: Consistency point binary_log_position 21466931.
220228 17:40:33 RDR1 INFO: Consistency point InnoDB lsn 55186323.
220228 17:40:33 RDR1 INFO: Consistency point InnoDB lsn_checkpoint 55178303.
220228 17:40:33 RDR1 INFO: Requesting completion of redo log copy after LSN 55186323.
220228 17:40:33 RLR1 INFO: Redo log reader waited 356 times for a total of 1780.00 ms for logs to generate.
220228 17:40:33 RLW1 INFO: A copied database page was modified at 55178303. (This is the highest lsn found on a page)
220228 17:40:33 RLW1 INFO: Scanned log up to lsn 55186323.
220228 17:40:33 RLW1 INFO: Was able to parse the log up to lsn 55186323.
220228 17:40:33 RDR1 INFO: Read-locked tables are unlocked.
220228 17:40:33 RDR1 INFO: Copying /data/mysql/log/binlog/6666/mysql-bin.000002.
220228 17:40:33 RDR1 INFO: Completed the copy of binlog files...
220228 17:40:33 RDR1 INFO: The server instance is unlocked after 0.416 seconds.
220228 17:40:33 RDR1 INFO: Reading all global variables from the server.
220228 17:40:33 RDR1 INFO: Completed reading of all 541 global variables from the server.
220228 17:40:33 RDR1 INFO: Writing server defaults files 'server-my.cnf' and 'server-all.cnf' for server '8.0.19' in '/tools/backup/incr_backup_full_scan/2022-02-28_17-40-29'.
220228 17:40:33 RDR1 INFO: Copying meta file /tools/backup/incr_backup_full_scan/2022-02-28_17-40-29/meta/backup_variables.txt.
220228 17:40:33 RDR1 INFO: Copying meta file /tools/backup/incr_backup_full_scan/2022-02-28_17-40-29/datadir/ibbackup_logfile.
220228 17:40:33 RDR1 INFO: Copying meta file /tools/backup/incr_backup_full_scan/2022-02-28_17-40-29/server-all.cnf.
220228 17:40:33 RDR1 INFO: Copying meta file /tools/backup/incr_backup_full_scan/2022-02-28_17-40-29/server-my.cnf.
220228 17:40:33 RDR1 INFO: Copying meta file /tools/backup/incr_backup_full_scan/2022-02-28_17-40-29/datadir/ibbackup_ibd_files.
220228 17:40:33 RDR1 INFO: Copying meta file /tools/backup/incr_backup_full_scan/2022-02-28_17-40-29/meta/backup_gtid_executed.sql.
220228 17:40:33 RDR1 INFO: Copying meta file /tools/backup/incr_backup_full_scan/2022-02-28_17-40-29/meta/backup_content.xml.
220228 17:40:33 RDR1 INFO: Copying meta file /tools/backup/incr_backup_full_scan/2022-02-28_17-40-29/meta/image_files.xml.
220228 17:40:33 MAIN INFO: Reset the backup id.
220228 17:40:33 MAIN INFO: Incremental Image Backup operation completed successfully.
220228 17:40:33 MAIN INFO: Backup image created successfully.
220228 17:40:33 MAIN INFO: Image Path = /tools/backup/incr_backup_full_scan/incremental_image.mbi
220228 17:40:33 MAIN INFO: Backup contains changes from lsn 22392547 to lsn 55186323
220228 17:40:33 MAIN INFO: MySQL binlog position: filename mysql-bin.000002, position 21466931
220228 17:40:33 MAIN INFO: GTID_EXECUTED is 3f554b42-9617-11ec-8380-02000aba3e24:1-38114
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 22392547
End LSN : 55186323
-------------------------------------------------------------
mysqlbackup completed OK!
full-scan general 日志输出
2022-02-28T17:40:29.148495+08:00 14976 Connect root@localhost on using Socket
2022-02-28T17:40:29.148927+08:00 14976 Query set autocommit=1
2022-02-28T17:40:29.149220+08:00 14976 Query SET SESSION wait_timeout = 31536000
2022-02-28T17:40:29.149451+08:00 14976 Query SET SQL_LOG_BIN = OFF
2022-02-28T17:40:29.149742+08:00 14976 Query SELECT @@GLOBAL.version_compile_os
2022-02-28T17:40:29.149925+08:00 14976 Query SELECT @@GLOBAL.server_uuid
2022-02-28T17:40:29.150084+08:00 14976 Query SHOW SESSION STATUS LIKE 'ssl_version'
2022-02-28T17:40:29.151814+08:00 14976 Query SELECT member_host, member_role, member_port FROM performance_schema.replication_group_members
2022-02-28T17:40:29.152217+08:00 14976 Query SELECT @@GLOBAL.super_read_only
2022-02-28T17:40:29.152379+08:00 14976 Query SELECT @@GLOBAL.innodb_undo_log_encrypt
2022-02-28T17:40:29.152480+08:00 14976 Query SELECT @@GLOBAL.datadir
2022-02-28T17:40:29.152579+08:00 14976 Query SELECT @@GLOBAL.innodb_data_home_dir
2022-02-28T17:40:29.152684+08:00 14976 Query SELECT @@GLOBAL.innodb_data_file_path
2022-02-28T17:40:29.152804+08:00 14976 Query SELECT @@GLOBAL.innodb_log_group_home_dir
2022-02-28T17:40:29.152898+08:00 14976 Query SELECT @@GLOBAL.innodb_log_files_in_group
2022-02-28T17:40:29.152983+08:00 14976 Query SELECT @@GLOBAL.innodb_log_file_size
2022-02-28T17:40:29.153063+08:00 14976 Query SELECT @@GLOBAL.innodb_undo_directory
2022-02-28T17:40:29.153155+08:00 14976 Query SELECT @@GLOBAL.innodb_undo_tablespaces
2022-02-28T17:40:29.153259+08:00 14976 Query SELECT @@GLOBAL.innodb_buffer_pool_filename
2022-02-28T17:40:29.153354+08:00 14976 Query SELECT @@GLOBAL.innodb_page_size
2022-02-28T17:40:29.153436+08:00 14976 Query SELECT @@GLOBAL.innodb_checksum_algorithm
2022-02-28T17:40:29.153951+08:00 14977 Connect root@localhost on using Socket
2022-02-28T17:40:29.154134+08:00 14977 Query set autocommit=1
2022-02-28T17:40:29.154297+08:00 14977 Query SET SESSION wait_timeout = 31536000
2022-02-28T17:40:29.154396+08:00 14977 Query SET SQL_LOG_BIN = OFF
2022-02-28T17:40:29.154578+08:00 14976 Query SELECT engine FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'backup_progress'
2022-02-28T17:40:29.156202+08:00 14976 Query CREATE TABLE IF NOT EXISTS mysql.backup_progress ( `id` INT AUTO_INCREMENT PRIMARY KEY, `backup_id` BIGINT NOT NULL, `tool_name` VARCHAR(4096) NOT NULL, `error_code` INT NOT NULL, `error_message` VARCHAR(4096) NOT NULL, `current_time` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),`current_state` VARCHAR(200) NOT NULL, INDEX idx (`backup_id`, `current_time`)) ENGINE = INNODB
2022-02-28T17:40:29.157199+08:00 14976 Prepare INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (?,?,?,?,?)
2022-02-28T17:40:29.245627+08:00 14976 Query SELECT @@GLOBAL.version_comment
2022-02-28T17:40:29.245994+08:00 14976 Query SELECT component_id FROM mysql.component WHERE component_urn LIKE '%component_mysqlbackup'
2022-02-28T17:40:29.246492+08:00 14976 Query SHOW GLOBAL STATUS LIKE 'mysqlbackup.component_version'
2022-02-28T17:40:29.247604+08:00 14976 Query SET @@GLOBAL.mysqlbackup.backupid='16460412291573084'
2022-02-28T17:40:29.247822+08:00 14976 Query SELECT mysqlbackup_page_track_get_start_lsn()
2022-02-28T17:40:29.248457+08:00 14978 Connect root@localhost on using Socket
2022-02-28T17:40:29.248700+08:00 14978 Query set autocommit=1
2022-02-28T17:40:29.248923+08:00 14978 Query SET SESSION wait_timeout = 31536000
2022-02-28T17:40:29.249055+08:00 14978 Query SET SQL_LOG_BIN = OFF
2022-02-28T17:40:29.249413+08:00 14978 Prepare SELECT end_lsn, backup_destination, end_time, backup_type, consistency_time_utc FROM mysql.backup_history WHERE exit_state = 'SUCCESS' AND backup_type = 'FULL' AND server_uuid = @@server_uuid ORDER BY end_time DESC, end_lsn DESC LIMIT 0,1
2022-02-28T17:40:29.249506+08:00 14978 Execute SELECT end_lsn, backup_destination, end_time, backup_type, consistency_time_utc FROM mysql.backup_history WHERE exit_state = 'SUCCESS' AND backup_type = 'FULL' AND server_uuid = @@server_uuid ORDER BY end_time DESC, end_lsn DESC LIMIT 0,1
2022-02-28T17:40:29.250163+08:00 14978 Close stmt
2022-02-28T17:40:29.250224+08:00 14978 Quit
2022-02-28T17:40:29.250436+08:00 14976 Query SELECT binlog_file FROM mysql.backup_history WHERE exit_state='SUCCESS' AND backup_type = 'FULL' AND server_uuid = @@server_uuid ORDER BY end_time DESC, end_lsn DESC LIMIT 0,1
2022-02-28T17:40:29.250961+08:00 14976 Query SELECT mysqlbackup_page_track_get_changed_page_count(22392546,0)
2022-02-28T17:40:29.251345+08:00 14976 Query SELECT SUM(file_size), page_size FROM information_schema.innodb_tablespaces GROUP BY page_size
2022-02-28T17:40:29.252589+08:00 14976 Query SELECT plugin_name FROM information_schema.plugins WHERE plugin_type='KEYRING' AND plugin_status='ACTIVE'
2022-02-28T17:40:29.296773+08:00 14976 Query SELECT engine FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'backup_history'
2022-02-28T17:40:29.297925+08:00 14976 Query SHOW SLAVE STATUS
2022-02-28T17:40:29.298170+08:00 14976 Execute INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (16460412291573084,'mysqlbackup',0,'NO_ERROR','Started mysqlbackup.')
2022-02-28T17:40:29.445558+08:00 14976 Query SELECT @@GLOBAL.innodb_redo_log_archive_dirs
2022-02-28T17:40:29.445889+08:00 14976 Query SHOW ENGINE INNODB STATUS
2022-02-28T17:40:29.448021+08:00 14976 Query SELECT @@GLOBAL.innodb_log_buffer_size
2022-02-28T17:40:29.448264+08:00 14976 Query SELECT @@GLOBAL.innodb_log_checksums
2022-02-28T17:40:29.458109+08:00 14979 Connect root@localhost on using Socket
2022-02-28T17:40:29.458377+08:00 14979 Query set autocommit=1
2022-02-28T17:40:29.458587+08:00 14979 Query SET SESSION wait_timeout = 31536000
2022-02-28T17:40:29.458708+08:00 14979 Query SET SQL_LOG_BIN = OFF
2022-02-28T17:40:29.469681+08:00 14976 Query SHOW ENGINE INNODB STATUS
2022-02-28T17:40:30.012794+08:00 156 Query show slave status
2022-02-28T17:40:30.012880+08:00 158 Query select @@server_uuid
2022-02-28T17:40:30.013034+08:00 157 Query show global variables
2022-02-28T17:40:30.013067+08:00 159 Query show global status
2022-02-28T17:40:30.014311+08:00 155 Query select * from performance_schema.replication_group_members
2022-02-28T17:40:30.016154+08:00 250 Query show master status
2022-02-28T17:40:30.016730+08:00 153 Query select @@gtid_purged
2022-02-28T17:40:30.017153+08:00 157 Query show slave status
2022-02-28T17:40:30.017432+08:00 154 Query select * from information_schema.processlist where ((command='Binlog Dump') or (command = 'Binlog Dump GTID'))
2022-02-28T17:40:30.021459+08:00 155 Query select @@version
2022-02-28T17:40:31.012539+08:00 158 Query show global status
2022-02-28T17:40:31.012884+08:00 159 Query show slave status
2022-02-28T17:40:31.013087+08:00 250 Query show global variables
2022-02-28T17:40:31.013600+08:00 153 Query select * from performance_schema.replication_group_members
2022-02-28T17:40:31.014189+08:00 156 Query select @@server_uuid
2022-02-28T17:40:31.014199+08:00 157 Query select * from information_schema.processlist where ((command='Binlog Dump') or (command = 'Binlog Dump GTID'))
2022-02-28T17:40:31.014913+08:00 154 Query show master status
2022-02-28T17:40:31.015177+08:00 155 Query select @@gtid_purged
2022-02-28T17:40:31.015426+08:00 153 Query show slave status
2022-02-28T17:40:31.018437+08:00 159 Query select @@version
2022-02-28T17:40:32.015502+08:00 157 Query select * from information_schema.processlist where ((command='Binlog Dump') or (command = 'Binlog Dump GTID'))
2022-02-28T17:40:32.015699+08:00 156 Query show global status
2022-02-28T17:40:32.015709+08:00 154 Query show global variables
2022-02-28T17:40:32.015511+08:00 158 Query select @@server_uuid
2022-02-28T17:40:32.016638+08:00 153 Query show master status
2022-02-28T17:40:32.016954+08:00 155 Query select * from performance_schema.replication_group_members
2022-02-28T17:40:32.017177+08:00 250 Query show slave status
2022-02-28T17:40:32.016960+08:00 159 Query select @@gtid_purged
2022-02-28T17:40:32.019604+08:00 158 Query show slave status
2022-02-28T17:40:32.021350+08:00 157 Query select @@version
2022-02-28T17:40:32.749551+08:00 14976 Query SELECT FILE_ID, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND ENGINE = 'InnoDB' AND FILE_ID IS NOT NULL
2022-02-28T17:40:32.813696+08:00 14976 Query SELECT FILE_ID, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE ENGINE = 'InnoDB' AND STATUS = 'NORMAL' AND FILE_TYPE <> 'TEMPORARY' AND FILE_TYPE <> 'UNDO LOG' AND FILE_ID <> 0
2022-02-28T17:40:32.962495+08:00 14979 Query START TRANSACTION
2022-02-28T17:40:32.962904+08:00 14979 Query SELECT 1 FROM `sys`.`sys_config` LIMIT 1
2022-02-28T17:40:32.969732+08:00 14979 Query COMMIT
2022-02-28T17:40:32.970388+08:00 14979 Query START TRANSACTION
2022-02-28T17:40:32.970658+08:00 14979 Query SELECT 1 FROM `universe`.`u_delay` LIMIT 1
2022-02-28T17:40:32.971660+08:00 14979 Query COMMIT
2022-02-28T17:40:32.973297+08:00 14979 Query START TRANSACTION
2022-02-28T17:40:32.973518+08:00 14979 Query SELECT 1 FROM `mysql`.`backup_progress` LIMIT 1
2022-02-28T17:40:32.974410+08:00 14979 Query COMMIT
2022-02-28T17:40:32.974936+08:00 14979 Query START TRANSACTION
2022-02-28T17:40:32.975158+08:00 14979 Query SELECT 1 FROM `mysql`.`backup_history` LIMIT 1
2022-02-28T17:40:32.976144+08:00 14979 Query COMMIT
2022-02-28T17:40:33.012470+08:00 153 Query show global variables
2022-02-28T17:40:33.012512+08:00 154 Query select @@server_uuid
2022-02-28T17:40:33.012533+08:00 250 Query show global status
2022-02-28T17:40:33.012471+08:00 155 Query select * from performance_schema.replication_group_members
2022-02-28T17:40:33.012868+08:00 156 Query show slave status
2022-02-28T17:40:33.013240+08:00 159 Query select * from information_schema.processlist where ((command='Binlog Dump') or (command = 'Binlog Dump GTID'))
2022-02-28T17:40:33.013603+08:00 158 Query show master status
2022-02-28T17:40:33.015964+08:00 157 Query select @@gtid_purged
2022-02-28T17:40:33.016252+08:00 154 Query show slave status
2022-02-28T17:40:33.018762+08:00 14976 Query SELECT @@GLOBAL.innodb_read_only
2022-02-28T17:40:33.019799+08:00 156 Query select @@version
2022-02-28T17:40:33.020429+08:00 14976 Query SET @@GLOBAL.innodb_buffer_pool_dump_now=ON
2022-02-28T17:40:33.120891+08:00 14976 Query SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_dump_status'
2022-02-28T17:40:33.122557+08:00 14976 Query SELECT @@GLOBAL.log_bin
2022-02-28T17:40:33.122904+08:00 14976 Query SELECT @@GLOBAL.log_bin_basename
2022-02-28T17:40:33.123067+08:00 14976 Query SELECT @@GLOBAL.log_bin_index
2022-02-28T17:40:33.123260+08:00 14976 Query SELECT @@GLOBAL.relay_log_basename
2022-02-28T17:40:33.123424+08:00 14976 Query SELECT @@GLOBAL.relay_log_index
2022-02-28T17:40:33.123572+08:00 14976 Query SHOW SLAVE STATUS
2022-02-28T17:40:33.154343+08:00 14976 Query SHOW MASTER STATUS
2022-02-28T17:40:33.154789+08:00 14976 Query SELECT binlog_file FROM mysql.backup_history WHERE exit_state='SUCCESS' AND backup_type = 'FULL' AND server_uuid = @@server_uuid ORDER BY end_time DESC, end_lsn DESC LIMIT 0,1
2022-02-28T17:40:33.155252+08:00 14976 Query SELECT binlog_master_key_seq_num FROM mysql.backup_history WHERE exit_state='SUCCESS' AND backup_type = 'FULL' AND server_uuid = @@server_uuid ORDER BY end_time DESC, end_lsn DESC LIMIT 0, 1
2022-02-28T17:40:33.155691+08:00 14976 Query SHOW SLAVE STATUS
2022-02-28T17:40:33.155921+08:00 14977 Query LOCK INSTANCE FOR BACKUP
2022-02-28T17:40:33.156176+08:00 14976 Query SHOW ENGINE INNODB STATUS
2022-02-28T17:40:33.157070+08:00 14976 Query FLUSH ENGINE LOGS
2022-02-28T17:40:33.163858+08:00 14976 Query SELECT FILE_ID, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE ENGINE = 'InnoDB' AND STATUS = 'NORMAL' AND FILE_TYPE <> 'TEMPORARY' AND FILE_ID <> 0
2022-02-28T17:40:33.178088+08:00 14976 Query SELECT FILE_ID, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND ENGINE = 'InnoDB' AND FILE_ID IS NOT NULL
2022-02-28T17:40:33.211375+08:00 14976 Execute INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (16460412291573084,'mysqlbackup',0,'NO_ERROR','mysqlbackup locking tables and copying other engines data.')
2022-02-28T17:40:33.259911+08:00 14976 Query SELECT CONCAT('`', REPLACE(table_schema, '`','``'), '`.`', REPLACE(table_name,'`','``'), '`') AS full_tbl_name, CONCAT(table_schema, '.', table_name) AS tbl_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND engine <> 'InnoDB' AND engine <> 'PERFORMANCE_SCHEMA' AND (table_schema <> 'mysql' OR (table_name <> 'general_log' AND table_name <> 'slow_log' AND table_name <> 'backup_progress'))
2022-02-28T17:40:33.262495+08:00 14977 Query FLUSH TABLES `test01`.`t01` WITH READ LOCK
2022-02-28T17:40:33.488712+08:00 14977 Query SELECT SERVER_UUID, LOCAL, REPLICATION, STORAGE_ENGINES FROM performance_schema.log_status
2022-02-28T17:40:33.493180+08:00 14977 Query UNLOCK TABLES
2022-02-28T17:40:33.571548+08:00 14977 Query UNLOCK INSTANCE
2022-02-28T17:40:33.571816+08:00 14976 Execute INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (16460412291573084,'mysqlbackup',0,'NO_ERROR','mysqlbackup unlocked the tables.')
2022-02-28T17:40:33.573502+08:00 14976 Query SHOW PLUGINS
2022-02-28T17:40:33.574571+08:00 14976 Query SHOW GLOBAL VARIABLES
2022-02-28T17:40:33.579265+08:00 14976 Query SELECT variable_name, variable_source FROM performance_schema.variables_info
2022-02-28T17:40:33.691534+08:00 14976 Query SELECT engine FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'backup_history'
2022-02-28T17:40:33.692734+08:00 14976 Query SET SESSION SQL_MODE=''
2022-02-28T17:40:33.692910+08:00 14976 Query USE mysql
2022-02-28T17:40:33.693246+08:00 14976 Query CREATE TABLE IF NOT EXISTS mysql.backup_history( `backup_id` BIGINT NOT NULL, `tool_name` VARCHAR(4096) NOT NULL, `start_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', `end_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', `binlog_pos` BIGINT NOT NULL, `binlog_file` VARCHAR(255) NOT NULL, `compression_level` INT NOT NULL, `engines` VARCHAR(100) NOT NULL, `innodb_data_file_path` VARCHAR(2048) NOT NULL, `start_lsn` BIGINT NOT NULL, `end_lsn` BIGINT NOT NULL, `backup_type` VARCHAR(50) NOT NULL, `backup_format` VARCHAR(50) NOT NULL, `mysql_data_dir` VARCHAR(2048) NOT NULL, `innodb_data_home_dir` VARCHAR(2048) NOT NULL, `innodb_log_group_home_dir` VARCHAR(2048) NOT NULL, `innodb_log_files_in_group` VARCHAR(100) NOT NULL, `innodb_log_file_size` VARCHAR(100) NOT NULL, `backup_destination` VARCHAR(4096) NOT NULL, `lock_time` DOUBLE PRECISION (7,3) NOT NULL, `exit_state` VARCHAR(10) NOT NULL, `last_error` VARCHAR(4096) NOT NULL, `last_error_code` INT NOT NULL, `start_time_utc` BIGINT NOT NULL, `end_time_utc` BIGINT NOT NULL, `consistency_time_utc` BIGINT NOT NULL, `meb_version` VARCHAR(20) NOT NULL DEFAULT '0.0.0', `server_uuid` VARCHAR(36) NOT NULL, `binlog_master_key_seq_num` BIGINT NOT NULL DEFAULT 0, PRIMARY KEY (`backup_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
2022-02-28T17:40:33.693623+08:00 14976 Query SHOW COLUMNS FROM mysql.backup_history LIKE 'innodb_file_format'
2022-02-28T17:40:33.695034+08:00 14976 Query SHOW COLUMNS FROM mysql.backup_history LIKE 'incremental_base_lsn'
2022-02-28T17:40:33.696187+08:00 14976 Query SHOW COLUMNS FROM mysql.backup_history LIKE 'binlog_master_key_seq_num'
2022-02-28T17:40:33.697293+08:00 14976 Prepare INSERT INTO mysql.backup_history(backup_id, tool_name, start_time, end_time, binlog_pos, binlog_file, compression_level, engines, innodb_data_file_path, start_lsn, end_lsn, backup_type, backup_format, mysql_data_dir, innodb_data_home_dir, innodb_log_group_home_dir, innodb_log_files_in_group, innodb_log_file_size, backup_destination, lock_time, exit_state, last_error, last_error_code, start_time_utc, end_time_utc, consistency_time_utc, meb_version, server_uuid, binlog_master_key_seq_num) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
2022-02-28T17:40:33.697479+08:00 14976 Execute INSERT INTO mysql.backup_history(backup_id, tool_name, start_time, end_time, binlog_pos, binlog_file, compression_level, engines, innodb_data_file_path, start_lsn, end_lsn, backup_type, backup_format, mysql_data_dir, innodb_data_home_dir, innodb_log_group_home_dir, innodb_log_files_in_group, innodb_log_file_size, backup_destination, lock_time, exit_state, last_error, last_error_code, start_time_utc, end_time_utc, consistency_time_utc, meb_version, server_uuid, binlog_master_key_seq_num) VALUES(16460412291573084,'mysqlbackup --user=root --password=xxxxxx --port=6666 --host=localhost --socket=/data/mysql/data/6666/mysqld.sock --incremental --incremental-base=history:last_full_backup --with-timestamp --backup-dir=/tools/backup/incr_backup_full_scan --backup-image=/tools/backup/incr_backup_full_scan/incremental_image.mbi backup-to-image ','2022-02-28 17:40:29','2022-02-28 17:40:33',21466931,'mysql-bin.000002',0,'CSV:InnoDB:MyISAM:PERFORMANCE_SCHEMA','ibdata1:1G:autoextend',22392547,55186323,'DIFFERENTIAL','IMAGE','/data/mysql/data/6666/','','/data/mysql/log/redolog/6666','2','2147483648','/tools/backup/incr_backup_full_scan/2022-02-28_17-40-29',0.4156632,'SUCCESS','NO_ERROR',0,1646041229146110,1646041233583796,1646041233489455,'8.0.19','3f554b42-9617-11ec-8380-02000aba3e24',0)
2022-02-28T17:40:33.698515+08:00 14976 Execute INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (16460412291573084,'mysqlbackup',0,'NO_ERROR','mysqlbackup returns success.')
2022-02-28T17:40:33.699386+08:00 14976 Query SELECT mysqlbackup_page_track_set(true)
2022-02-28T17:40:33.699627+08:00 14976 Query SET @@GLOBAL.mysqlbackup.backupid=NULL
2022-02-28T17:40:33.700288+08:00 14979 Quit
2022-02-28T17:40:33.729287+08:00 14977 Quit
2022-02-28T17:40:33.729338+08:00 14976 Close stmt
2022-02-28T17:40:33.729495+08:00 14976 Close stmt
2022-02-28T17:40:33.729557+08:00 14976 Quit
| 参数 | 耗时 |
|---|---|
| full-scan | 4s |
| page-track | 1s |
Redo Log Archiving原理
也称redo log 备份原理
Redo Log Archiving原理：利用重做日志文件进行备份。利用的就是redo log 中记录了数据的DML 变更日志。(弊端就是全备和此次增备期间数据量变化的大小不能超过日志文件的大小，因为redo log是循环利用的)
好处是：备份耗时较少，故可以增加增备的频率，有利于我们恢复至更细粒度的时间点。
mysqlbackup --user=root --password=123456 --port=6666 --host=localhost --socket=/data/mysql/data/6666/mysqld.sock --incremental-with-redo-log-only --incremental-base=history:last_full_backup --with-timestamp --backup-dir=/tools/backup/incr_backup_redo_log --backup-image=/tools/backup/incr_backup_redo_log/incremental_image.mbi backup-to-image
Backup Image Path = /tools/backup/incr_backup_redo_log/incremental_image.mbi
220301 19:55:44 MAIN INFO: Unique generated backup id for this is 16461357439553059
220301 19:55:44 MAIN INFO: Copying the server config file '/data/mysql/data/6666/auto.cnf'
220301 19:55:44 MAIN INFO: Creating 14 buffers each of size 17301504.
220301 19:55:44 MAIN INFO: Failed to start redo log archiving...
220301 19:55:44 MAIN INFO: Found checkpoint at lsn 55345488.
220301 19:55:44 MAIN INFO: Starting log scan from lsn = 22392320 at offset = 22386176 and checkpoint = 55345488 in file /data/mysql/log/redolog/6666/ib_logfile0.
220301 19:55:44 MAIN INFO: Incremental redo log Image Backup operation starts with following threads
1 read-threads 1 write-threads
220301 19:55:44 MAIN INFO: No SSL options specified.
220301 19:55:44 RDR1 INFO: Copying meta file /tools/backup/incr_backup_redo_log/2022-03-01_19-55-43/backup-my.cnf.
220301 19:55:44 RDR1 INFO: Copying meta file /tools/backup/incr_backup_redo_log/2022-03-01_19-55-43/meta/backup_create.xml.
220301 19:55:44 RDR1 INFO: Copying meta file /tools/backup/incr_backup_redo_log/2022-03-01_19-55-43/datadir/backup-auto.cnf.
220301 19:55:44 RLP1 INFO: Starting to parse redo log at lsn = 22392350, whereas checkpoint_lsn = 22392546.
220301 19:55:44 RLP1 INFO: Recovery parsing buffer extended to 4194304..
220301 19:55:44 RLP1 INFO: Recovery parsing buffer extended to 8388608..
220301 19:55:44 RDR1 INFO: Requesting a dump of the InnoDB buffer pool
220301 19:55:44 RDR1 INFO: Waiting for the dump of the InnoDB buffer pool to complete
220301 19:55:44 RDR1 INFO: The dump of the InnoDB buffer pool completed
220301 19:55:44 RDR1 INFO: Binary Log Basename: '/data/mysql/log/binlog/6666/mysql-bin'
220301 19:55:44 RDR1 INFO: Binary Log Index: '/data/mysql/log/binlog/6666/mysql-bin.index'
220301 19:55:44 RDR1 INFO: Relay Channel: 'group_replication_applier'
220301 19:55:44 RDR1 INFO: Relay Log Basename: '/data/mysql/log/relaylog/6666/mysql-relay-group_replication_applier'
220301 19:55:44 RDR1 INFO: Relay Log Index: '/data/mysql/log/relaylog/6666/mysql-relay-group_replication_applier.index'
220301 19:55:44 RDR1 INFO: Relay Channel: 'group_replication_recovery'
220301 19:55:44 RDR1 INFO: Relay Log Basename: '/data/mysql/log/relaylog/6666/mysql-relay-group_replication_applier-group_replication_recovery'
220301 19:55:44 RDR1 INFO: Relay Log Index: '/data/mysql/log/relaylog/6666/mysql-relay-group_replication_recovery.index'
220301 19:55:44 RDR1 INFO: Starting to copy Binlog files.
220301 19:55:44 RDR1 INFO: Copying /data/mysql/log/binlog/6666/mysql-bin.000002.
220301 19:55:44 RDR1 INFO: Copying /data/mysql/log/binlog/6666/mysql-bin.000003.
220301 19:55:44 RDR1 INFO: Copying /data/mysql/log/binlog/6666/mysql-bin.000004.
220301 19:55:44 RDR1 INFO: Copying /data/mysql/log/binlog/6666/mysql-bin.000005.
220301 19:55:44 RDR1 INFO: Starting to lock instance for backup...
220301 19:55:44 RDR1 INFO: The server instance is locked for backup.
220301 19:55:44 RDR1 INFO: Requesting flush of redo log reading after LSN 55347300.
220301 19:55:44 RDR1 INFO: Requesting flush of redo log processing after LSN 55347300.
220301 19:55:44 RDR1 INFO: Completed flush of redo log reading after LSN 55347300.
220301 19:55:44 RDR1 INFO: Completed flush of redo log processing after LSN 55347300.
220301 19:55:44 RDR1 INFO: Starting to read-lock tables...
220301 19:55:44 RDR1 INFO: 1 tables are read-locked.
220301 19:55:44 RDR1 INFO: Opening backup source directory '/data/mysql/data/6666'
220301 19:55:44 RDR1 INFO: Starting to copy non-innodb files in subdirs of '/data/mysql/data/6666'
220301 19:55:44 WTR1 INFO: Adding database directory: datadir/mysql
220301 19:55:44 WTR1 INFO: Adding database directory: datadir/performance_schema
220301 19:55:45 WTR1 INFO: Adding database directory: datadir/sys
220301 19:55:45 WTR1 INFO: Adding database directory: datadir/test01
220301 19:55:45 RDR1 INFO: Completing the copy of all non-innodb files.
220301 19:55:45 WTR1 INFO: Adding database directory: datadir/universe
220301 19:55:45 RDR1 INFO: Requesting consistency information...
220301 19:55:45 RDR1 INFO: Locked the consistency point for 846 microseconds.
220301 19:55:45 RDR1 INFO: Consistency point server_uuid '3f554b42-9617-11ec-8380-02000aba3e24'.
220301 19:55:45 RDR1 INFO: Consistency point gtid_executed '3f554b42-9617-11ec-8380-02000aba3e24:1-38114'.
220301 19:55:45 RDR1 INFO: Consistency point binary_log_file 'mysql-bin.000006'.
220301 19:55:45 RDR1 INFO: Consistency point binary_log_position 191.
220301 19:55:45 RDR1 INFO: Consistency point InnoDB lsn 55347582.
220301 19:55:45 RDR1 INFO: Consistency point InnoDB lsn_checkpoint 55345488.
220301 19:55:45 RDR1 INFO: Requesting completion of redo log copy after LSN 55347582.
220301 19:55:45 RLR1 INFO: Redo log reader waited 23 times for a total of 115.00 ms for logs to generate.
220301 19:55:45 RDR1 INFO: Read-locked tables are unlocked.
220301 19:55:45 RDR1 INFO: Copying /data/mysql/log/binlog/6666/mysql-bin.000006.
220301 19:55:45 RDR1 INFO: Completed the copy of binlog files...
220301 19:55:45 RLW1 INFO: A copied database page was modified at 0. (This is the highest lsn found on a page)
220301 19:55:45 RLW1 INFO: Scanned log up to lsn 55347582.
220301 19:55:45 RLW1 INFO: Was able to parse the log up to lsn 55347582.
220301 19:55:45 RDR1 INFO: The server instance is unlocked after 0.442 seconds.
220301 19:55:45 RDR1 INFO: Reading all global variables from the server.
220301 19:55:45 RDR1 INFO: Completed reading of all 541 global variables from the server.
220301 19:55:45 RDR1 INFO: Writing server defaults files 'server-my.cnf' and 'server-all.cnf' for server '8.0.19' in '/tools/backup/incr_backup_redo_log/2022-03-01_19-55-43'.
220301 19:55:45 RDR1 INFO: Copying meta file /tools/backup/incr_backup_redo_log/2022-03-01_19-55-43/meta/backup_variables.txt.
220301 19:55:45 RDR1 INFO: Copying meta file /tools/backup/incr_backup_redo_log/2022-03-01_19-55-43/datadir/ibbackup_logfile.
220301 19:55:45 RDR1 INFO: Copying meta file /tools/backup/incr_backup_redo_log/2022-03-01_19-55-43/server-all.cnf.
220301 19:55:45 RDR1 INFO: Copying meta file /tools/backup/incr_backup_redo_log/2022-03-01_19-55-43/server-my.cnf.
220301 19:55:45 RDR1 INFO: Copying meta file /tools/backup/incr_backup_redo_log/2022-03-01_19-55-43/datadir/ibbackup_ibd_files.
220301 19:55:45 RDR1 INFO: Copying meta file /tools/backup/incr_backup_redo_log/2022-03-01_19-55-43/datadir/ibbackup_redo_log_only.
220301 19:55:45 RDR1 INFO: Copying meta file /tools/backup/incr_backup_redo_log/2022-03-01_19-55-43/meta/backup_gtid_executed.sql.
220301 19:55:45 RDR1 INFO: Copying meta file /tools/backup/incr_backup_redo_log/2022-03-01_19-55-43/meta/backup_content.xml.
220301 19:55:45 RDR1 INFO: Copying meta file /tools/backup/incr_backup_redo_log/2022-03-01_19-55-43/meta/image_files.xml.
220301 19:55:45 MAIN INFO: Reset the backup id.
220301 19:55:45 MAIN INFO: Incremental redo log Image Backup operation completed successfully.
220301 19:55:45 MAIN INFO: Image Path = /tools/backup/incr_backup_redo_log/incremental_image.mbi
220301 19:55:45 MAIN INFO: Backup contains changes from lsn 22392547 to lsn 55347582
220301 19:55:45 MAIN INFO: MySQL binlog position: filename mysql-bin.000006, position 191
220301 19:55:45 MAIN INFO: GTID_EXECUTED is 3f554b42-9617-11ec-8380-02000aba3e24:1-38114
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 22392547
End LSN : 55347582
-------------------------------------------------------------
mysqlbackup completed OK!
其中有备份redo的过程：
220301 19:55:44 RLP1 INFO: Starting to parse redo log at lsn = 22392350, whereas checkpoint_lsn = 22392546.
220301 19:55:44 RLP1 INFO: Recovery parsing buffer extended to 4194304..
220301 19:55:44 RLP1 INFO: Recovery parsing buffer extended to 8388608..
220301 19:55:44 RDR1 INFO: Requesting a dump of the InnoDB buffer pool
220301 19:55:44 RDR1 INFO: Waiting for the dump of the InnoDB buffer pool to complete
220301 19:55:44 RDR1 INFO: The dump of the InnoDB buffer pool completed
其general log中包含立即刷新页面缓存池的操作：
2022-03-01T19:55:44.342574+08:00 1663 Query SET @@GLOBAL.innodb_buffer_pool_dump_now=ON
2022-03-01T19:55:44.442959+08:00 1663 Query SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_dump_status'
Redo Log Archiving 的general log 信息
2022-03-01T19:55:43.951427+08:00 1664 Connect root@localhost on using Socket
2022-03-01T19:55:43.951666+08:00 1664 Query set autocommit=1
2022-03-01T19:55:43.951828+08:00 1664 Query SET SESSION wait_timeout = 31536000
2022-03-01T19:55:43.951957+08:00 1664 Query SET SQL_LOG_BIN = OFF
2022-03-01T19:55:43.952185+08:00 1663 Query SELECT engine FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'backup_progress'
2022-03-01T19:55:43.954039+08:00 1663 Query CREATE TABLE IF NOT EXISTS mysql.backup_progress ( `id` INT AUTO_INCREMENT PRIMARY KEY, `backup_id` BIGINT NOT NULL, `tool_name` VARCHAR(4096) NOT NULL, `error_code` INT NOT NULL, `error_message` VARCHAR(4096) NOT NULL, `current_time` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),`current_state` VARCHAR(200) NOT NULL, INDEX idx (`backup_id`, `current_time`)) ENGINE = INNODB
2022-03-01T19:55:43.955187+08:00 1663 Prepare INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (?,?,?,?,?)
2022-03-01T19:55:43.966924+08:00 1663 Query SELECT @@GLOBAL.version_comment
2022-03-01T19:55:43.967268+08:00 1663 Query SELECT component_id FROM mysql.component WHERE component_urn LIKE '%component_mysqlbackup'
2022-03-01T19:55:43.967751+08:00 1663 Query SHOW GLOBAL STATUS LIKE 'mysqlbackup.component_version'
2022-03-01T19:55:43.968950+08:00 1663 Query SET @@GLOBAL.mysqlbackup.backupid='16461357439553059'
2022-03-01T19:55:43.969168+08:00 1663 Query SELECT mysqlbackup_page_track_get_start_lsn()
2022-03-01T19:55:43.969905+08:00 1665 Connect root@localhost on using Socket
2022-03-01T19:55:43.970134+08:00 1665 Query set autocommit=1
2022-03-01T19:55:43.970317+08:00 1665 Query SET SESSION wait_timeout = 31536000
2022-03-01T19:55:43.970437+08:00 1665 Query SET SQL_LOG_BIN = OFF
2022-03-01T19:55:43.970710+08:00 1665 Prepare SELECT end_lsn, backup_destination, end_time, backup_type, consistency_time_utc FROM mysql.backup_history WHERE exit_state = 'SUCCESS' AND backup_type = 'FULL' AND server_uuid = @@server_uuid ORDER BY end_time DESC, end_lsn DESC LIMIT 0,1
2022-03-01T19:55:43.970826+08:00 1665 Execute SELECT end_lsn, backup_destination, end_time, backup_type, consistency_time_utc FROM mysql.backup_history WHERE exit_state = 'SUCCESS' AND backup_type = 'FULL' AND server_uuid = @@server_uuid ORDER BY end_time DESC, end_lsn DESC LIMIT 0,1
2022-03-01T19:55:43.971456+08:00 1665 Close stmt
2022-03-01T19:55:43.971487+08:00 1663 Query SELECT binlog_file FROM mysql.backup_history WHERE exit_state='SUCCESS' AND backup_type = 'FULL' AND server_uuid = @@server_uuid ORDER BY end_time DESC, end_lsn DESC LIMIT 0,1
2022-03-01T19:55:43.971506+08:00 1665 Quit
2022-03-01T19:55:43.971977+08:00 1663 Query SELECT plugin_name FROM information_schema.plugins WHERE plugin_type='KEYRING' AND plugin_status='ACTIVE'
2022-03-01T19:55:44.011107+08:00 1663 Query SELECT engine FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'backup_history'
2022-03-01T19:55:44.012094+08:00 1663 Query SHOW SLAVE STATUS
2022-03-01T19:55:44.012325+08:00 1663 Execute INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (16461357439553059,'mysqlbackup',0,'NO_ERROR','Started mysqlbackup.')
2022-03-01T19:55:44.174968+08:00 1663 Query SELECT @@GLOBAL.innodb_redo_log_archive_dirs
2022-03-01T19:55:44.175542+08:00 1663 Query SHOW ENGINE INNODB STATUS
2022-03-01T19:55:44.187737+08:00 1663 Query SELECT @@GLOBAL.innodb_log_buffer_size
2022-03-01T19:55:44.187973+08:00 1663 Query SELECT @@GLOBAL.innodb_log_checksums
2022-03-01T19:55:44.189461+08:00 1666 Connect root@localhost on using Socket
2022-03-01T19:55:44.189703+08:00 1666 Query set autocommit=1
2022-03-01T19:55:44.189902+08:00 1666 Query SET SESSION wait_timeout = 31536000
2022-03-01T19:55:44.190046+08:00 1666 Query SET SQL_LOG_BIN = OFF
2022-03-01T19:55:44.191201+08:00 1663 Query SHOW ENGINE INNODB STATUS
2022-03-01T19:55:44.191732+08:00 1663 Query SELECT FILE_ID, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND ENGINE = 'InnoDB' AND FILE_ID IS NOT NULL
2022-03-01T19:55:44.286455+08:00 1663 Query SELECT FILE_ID, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE ENGINE = 'InnoDB' AND STATUS = 'NORMAL' AND FILE_TYPE <> 'TEMPORARY' AND FILE_TYPE <> 'UNDO LOG' AND FILE_ID <> 0
2022-03-01T19:55:44.342261+08:00 1663 Query SELECT @@GLOBAL.innodb_read_only
2022-03-01T19:55:44.342574+08:00 1663 Query SET @@GLOBAL.innodb_buffer_pool_dump_now=ON
2022-03-01T19:55:44.442959+08:00 1663 Query SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_dump_status'
2022-03-01T19:55:44.444641+08:00 1663 Query SELECT @@GLOBAL.log_bin
2022-03-01T19:55:44.444875+08:00 1663 Query SELECT @@GLOBAL.log_bin_basename
2022-03-01T19:55:44.445018+08:00 1663 Query SELECT @@GLOBAL.log_bin_index
2022-03-01T19:55:44.445118+08:00 1663 Query SELECT @@GLOBAL.relay_log_basename
2022-03-01T19:55:44.445213+08:00 1663 Query SELECT @@GLOBAL.relay_log_index
2022-03-01T19:55:44.445320+08:00 1663 Query SHOW SLAVE STATUS
2022-03-01T19:55:44.522594+08:00 1663 Query SHOW MASTER STATUS
2022-03-01T19:55:44.541512+08:00 1663 Query SELECT binlog_file FROM mysql.backup_history WHERE exit_state='SUCCESS' AND backup_type = 'FULL' AND server_uuid = @@server_uuid ORDER BY end_time DESC, end_lsn DESC LIMIT 0,1
2022-03-01T19:55:44.542087+08:00 1663 Query SELECT binlog_master_key_seq_num FROM mysql.backup_history WHERE exit_state='SUCCESS' AND backup_type = 'FULL' AND server_uuid = @@server_uuid ORDER BY end_time DESC, end_lsn DESC LIMIT 0, 1
2022-03-01T19:55:44.699556+08:00 1663 Query SHOW SLAVE STATUS
2022-03-01T19:55:44.699985+08:00 1664 Query LOCK INSTANCE FOR BACKUP
2022-03-01T19:55:44.700222+08:00 1663 Query SHOW ENGINE INNODB STATUS
2022-03-01T19:55:44.717658+08:00 1663 Query SELECT FILE_ID, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE ENGINE = 'InnoDB' AND STATUS = 'NORMAL' AND FILE_TYPE <> 'TEMPORARY' AND FILE_ID <> 0
2022-03-01T19:55:44.764535+08:00 1663 Query SELECT FILE_ID, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND ENGINE = 'InnoDB' AND FILE_ID IS NOT NULL
2022-03-01T19:55:44.810302+08:00 17 Query select THREAD_ID as thd_id, PROCESSLIST_INFO as current_statement, PROCESSLIST_TIME as time from performance_schema.threads where PROCESSLIST_COMMAND != 'Sleep' and PROCESSLIST_COMMAND != 'Daemon' and PROCESSLIST_COMMAND != 'Connect' and PROCESSLIST_COMMAND != 'Binlog Dump GTID' and NAME not like 'thread/sql/slave%' and PROCESSLIST_TIME > 600
2022-03-01T19:55:44.814204+08:00 18 Prepare select user from mysql.user where user in (?,?,?,?,?,?,?)
2022-03-01T19:55:44.815051+08:00 21 Query select trx_id,trx_mysql_thread_id,unix_timestamp() - unix_timestamp(trx_started) as time from information_schema.INNODB_TRX where unix_timestamp() - unix_timestamp(trx_started) > 600
2022-03-01T19:55:44.815580+08:00 11 Query select * from performance_schema.replication_group_members
2022-03-01T19:55:44.816226+08:00 9 Query show global variables
2022-03-01T19:55:44.816279+08:00 16 Query SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, DATA_FREE FROM information_schema.tables
2022-03-01T19:55:44.816351+08:00 20 Query SELECT POOL_ID,FREE_BUFFERS FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS
2022-03-01T19:55:44.816567+08:00 18 Execute select user from mysql.user where user in ('root','universe_op','universe_op','universe_op','universe_op','universe_op','universe_op')
2022-03-01T19:55:44.816928+08:00 19 Prepare SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME IN (?,?,?)
2022-03-01T19:55:44.817035+08:00 12 Query show slave status
2022-03-01T19:55:44.817114+08:00 8 Query select * from information_schema.processlist where ((command='Binlog Dump') or (command = 'Binlog Dump GTID'))
2022-03-01T19:55:44.817379+08:00 22 Query SELECT time FROM INFORMATION_SCHEMA.PROCESSLIST WHERE state LIKE 'Waiting for semi-sync ACK from slave'
2022-03-01T19:55:44.817800+08:00 23 Query show master status
2022-03-01T19:55:44.817966+08:00 13 Query show global status
2022-03-01T19:55:44.818023+08:00 7 Query select @@server_uuid
2022-03-01T19:55:44.826203+08:00 19 Execute SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME IN ('log_max_modified_age_async','log_lsn_checkpoint_age','log_max_modified_age_sync')
2022-03-01T19:55:44.827532+08:00 18 Close stmt
2022-03-01T19:55:44.827631+08:00 17 Query show variables like 'max_binlog_size'
2022-03-01T19:55:44.831194+08:00 10 Query show master status
2022-03-01T19:55:44.832475+08:00 11 Query select @@gtid_purged
2022-03-01T19:55:44.832947+08:00 19 Close stmt
2022-03-01T19:55:44.833128+08:00 8 Query show slave status
2022-03-01T19:55:44.931402+08:00 12 Query select @@version
2022-03-01T19:55:44.932675+08:00 1663 Execute INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (16461357439553059,'mysqlbackup',0,'NO_ERROR','mysqlbackup locking tables and copying other engines data.')
2022-03-01T19:55:44.936396+08:00 1663 Query SELECT CONCAT('`', REPLACE(table_schema, '`','``'), '`.`', REPLACE(table_name,'`','``'), '`') AS full_tbl_name, CONCAT(table_schema, '.', table_name) AS tbl_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND engine <> 'InnoDB' AND engine <> 'PERFORMANCE_SCHEMA' AND (table_schema <> 'mysql' OR (table_name <> 'general_log' AND table_name <> 'slow_log' AND table_name <> 'backup_progress'))
2022-03-01T19:55:44.937916+08:00 1664 Query FLUSH TABLES `test01`.`t01` WITH READ LOCK
2022-03-01T19:55:45.136170+08:00 1664 Query SELECT SERVER_UUID, LOCAL, REPLICATION, STORAGE_ENGINES FROM performance_schema.log_status
2022-03-01T19:55:45.141018+08:00 1664 Query UNLOCK TABLES
2022-03-01T19:55:45.142284+08:00 1664 Query UNLOCK INSTANCE
2022-03-01T19:55:45.142458+08:00 1663 Execute INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (16461357439553059,'mysqlbackup',0,'NO_ERROR','mysqlbackup unlocked the tables.')
2022-03-01T19:55:45.144681+08:00 1663 Query SHOW PLUGINS
2022-03-01T19:55:45.145454+08:00 1663 Query SHOW GLOBAL VARIABLES
2022-03-01T19:55:45.148981+08:00 1663 Query SELECT variable_name, variable_source FROM performance_schema.variables_info
2022-03-01T19:55:45.297644+08:00 1663 Query SELECT engine FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name = 'backup_history'
2022-03-01T19:55:45.299569+08:00 1663 Query SET SESSION SQL_MODE=''
2022-03-01T19:55:45.299789+08:00 1663 Query USE mysql
2022-03-01T19:55:45.300166+08:00 1663 Query CREATE TABLE IF NOT EXISTS mysql.backup_history( `backup_id` BIGINT NOT NULL, `tool_name` VARCHAR(4096) NOT NULL, `start_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', `end_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', `binlog_pos` BIGINT NOT NULL, `binlog_file` VARCHAR(255) NOT NULL, `compression_level` INT NOT NULL, `engines` VARCHAR(100) NOT NULL, `innodb_data_file_path` VARCHAR(2048) NOT NULL, `start_lsn` BIGINT NOT NULL, `end_lsn` BIGINT NOT NULL, `backup_type` VARCHAR(50) NOT NULL, `backup_format` VARCHAR(50) NOT NULL, `mysql_data_dir` VARCHAR(2048) NOT NULL, `innodb_data_home_dir` VARCHAR(2048) NOT NULL, `innodb_log_group_home_dir` VARCHAR(2048) NOT NULL, `innodb_log_files_in_group` VARCHAR(100) NOT NULL, `innodb_log_file_size` VARCHAR(100) NOT NULL, `backup_destination` VARCHAR(4096) NOT NULL, `lock_time` DOUBLE PRECISION (7,3) NOT NULL, `exit_state` VARCHAR(10) NOT NULL, `last_error` VARCHAR(4096) NOT NULL, `last_error_code` INT NOT NULL, `start_time_utc` BIGINT NOT NULL, `end_time_utc` BIGINT NOT NULL, `consistency_time_utc` BIGINT NOT NULL, `meb_version` VARCHAR(20) NOT NULL DEFAULT '0.0.0', `server_uuid` VARCHAR(36) NOT NULL, `binlog_master_key_seq_num` BIGINT NOT NULL DEFAULT 0, PRIMARY KEY (`backup_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
2022-03-01T19:55:45.300729+08:00 1663 Query SHOW COLUMNS FROM mysql.backup_history LIKE 'innodb_file_format'
2022-03-01T19:55:45.302301+08:00 1663 Query SHOW COLUMNS FROM mysql.backup_history LIKE 'incremental_base_lsn'
2022-03-01T19:55:45.303455+08:00 1663 Query SHOW COLUMNS FROM mysql.backup_history LIKE 'binlog_master_key_seq_num'
2022-03-01T19:55:45.304507+08:00 1663 Prepare INSERT INTO mysql.backup_history(backup_id, tool_name, start_time, end_time, binlog_pos, binlog_file, compression_level, engines, innodb_data_file_path, start_lsn, end_lsn, backup_type, backup_format, mysql_data_dir, innodb_data_home_dir, innodb_log_group_home_dir, innodb_log_files_in_group, innodb_log_file_size, backup_destination, lock_time, exit_state, last_error, last_error_code, start_time_utc, end_time_utc, consistency_time_utc, meb_version, server_uuid, binlog_master_key_seq_num) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
2022-03-01T19:55:45.304818+08:00 1663 Execute INSERT INTO mysql.backup_history(backup_id, tool_name, start_time, end_time, binlog_pos, binlog_file, compression_level, engines, innodb_data_file_path, start_lsn, end_lsn, backup_type, backup_format, mysql_data_dir, innodb_data_home_dir, innodb_log_group_home_dir, innodb_log_files_in_group, innodb_log_file_size, backup_destination, lock_time, exit_state, last_error, last_error_code, start_time_utc, end_time_utc, consistency_time_utc, meb_version, server_uuid, binlog_master_key_seq_num) VALUES(16461357439553059,'mysqlbackup --user=root --password=xxxxxx --port=6666 --host=localhost --socket=/data/mysql/data/6666/mysqld.sock --incremental-with-redo-log-only --incremental-base=history:last_full_backup --with-timestamp --backup-dir=/tools/backup/incr_backup_redo_log --backup-image=/tools/backup/incr_backup_redo_log/incremental_image.mbi backup-to-image ','2022-03-01 19:55:43','2022-03-01 19:55:45',191,'mysql-bin.000006',0,'','ibdata1:1G:autoextend',22392547,55347582,'DIFFERENTIAL','IMAGE','/data/mysql/data/6666/','','/data/mysql/log/redolog/6666','2','2147483648','/tools/backup/incr_backup_redo_log/2022-03-01_19-55-43',0.4422603,'SUCCESS','NO_ERROR',0,1646135743943957,1646135745181859,1646135745136903,'8.0.19','3f554b42-9617-11ec-8380-02000aba3e24',0)
2022-03-01T19:55:45.306055+08:00 1663 Execute INSERT INTO mysql.backup_progress ( `backup_id`, `tool_name`, `error_code`, `error_message`, `current_state` ) VALUES (16461357439553059,'mysqlbackup',0,'NO_ERROR','mysqlbackup returns success.')
2022-03-01T19:55:45.318891+08:00 1663 Query SELECT mysqlbackup_page_track_set(true)
2022-03-01T19:55:45.319487+08:00 1663 Query SET @@GLOBAL.mysqlbackup.backupid=NULL
2022-03-01T19:55:45.319923+08:00 1666 Quit
2022-03-01T19:55:45.330872+08:00 1663 Close stmt
2022-03-01T19:55:45.331008+08:00 1663 Close stmt
2022-03-01T19:55:45.331039+08:00 1663 Quit
2022-03-01T19:55:45.331047+08:00 1664 Quit
不适合 Redo Log Archiving 的场景
不适合Redo Log Archiving 备份的场景：综合来说取决于数据的大小、DML 活动的数量以及重做日志文件的大小。
1.数据变化的速度超过了InnoDB重做 日志文件的总大小。innodb_log_file_size * innodb_log_files_in_group 默认是4G
2.如果需要频繁备份，重做日志的增量远小于重做日志文件的大小时，不适合备份。因为每次备份都会去设置innodb_buffer_pool_dump_now=ON，刷新缓冲池可能会影响业务。
3.巨大的重做日志文件，在增量备份过程中读取它们可能需要与使用传统增量技术读取 InnoDB 数据文件一样长的时间，也不适合。
4.不支持压缩备份。--compress-method=lz4 默认。
模拟不适合的场景：
1. 设置innodb_log_file_size = 48M 重启数据库。
2.使用sysbench 生成大于redo log文件总大小的数据量。
3.使用Redo Log Archiving 进行备份。(会有如下报错：InnoDB log at LSN 22392546 is already overwritten)
mysqlbackup --user=root --password=123456 --port=6666 --host=localhost --socket=/data/mysql/data/6666/mysqld.sock --incremental-with-redo-log-only --incremental-base=history:last_full_backup --with-timestamp --backup-dir=/tools/backup/incr_backup_redo_log01 --backup-image=/tools/backup/incr_backup_redo_log01/incremental_image.mbi backup-to-image
Backup Image Path = /tools/backup/incr_backup_redo_log01/incremental_image.mbi
220302 14:37:13 MAIN INFO: Unique generated backup id for this is 16462030334584038
220302 14:37:13 MAIN INFO: Copying the server config file '/data/mysql/data/6666/auto.cnf'
220302 14:37:13 MAIN INFO: Creating 14 buffers each of size 17301504.
220302 14:37:13 MAIN INFO: Failed to start redo log archiving...
220302 14:37:13 MAIN INFO: Found checkpoint at lsn 559913830.
220302 14:37:13 MAIN INFO: Starting log scan from lsn = 22392320 at offset = 22386176 and checkpoint = 559913830 in file /data/mysql/log/redolog/6666/ib_logfile0.
220302 14:37:13 MAIN INFO: Incremental redo log Image Backup operation starts with following threads
1 read-threads 1 write-threads
220302 14:37:13 MAIN INFO: No SSL options specified.
220302 14:37:13 RLR1 ERROR: Cannot do incremental redo-only backup of InnoDB tables: InnoDB log at LSN 22392546 is already overwritten. InnoDB saves the only the latest redo log records in InnoDB log files. Older records are overwritten when their distance from the tail of the log exceeds the combined size of InnoDB log files.
220302 14:37:13 RDR1 INFO: Copying meta file /tools/backup/incr_backup_redo_log01/2022-03-02_14-37-13/backup-my.cnf.
220302 14:37:13 RDR1 INFO: Copying meta file /tools/backup/incr_backup_redo_log01/2022-03-02_14-37-13/meta/backup_create.xml.
220302 14:37:13 RLR1 INFO: Rethrowing the exception.
220302 14:37:13 RLR1 INFO: Requesting rapid completion of redo log copy.
220302 14:37:13 RLR1 INFO: Requesting completion of redo log copy after LSN 0.
220302 14:37:13 MAIN INFO: Reset the backup id.
Mysqlbackup 恢复
补充一个增量备份，第5次增备 # 为的是备份sysbech
mysqlbackup --user=root --password=123456 --port=6666 --host=localhost --socket=/data/mysql/data/6666/mysqld.sock --incremental=page-track --incremental-base=history:last_full_backup --with-timestamp --backup-dir=/tools/backup/incr_backup_0302 --backup-image=/tools/backup/incr_backup_0302/incremental_image.mbi backup-to-image
其备份工具还支持压缩和加密。可以参考官网：https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/restore.html
以下示例是增量+全量恢复(将上面的备份的) 将数据恢复到10.186.62.56 主机上的空实例中。
备份信息：
drwxr-x--- 2 root root 27 Mar 2 15:35 datadir
drwxr-x--- 3 root root 62 Feb 28 16:50 incr_backup 第1次增备
drwxr-x--- 3 root root 62 Feb 28 17:40 incr_backup_full_scan 第2次增备
drwxr-x--- 3 root root 62 Mar 1 19:55 incr_backup_redo_log 第3次增备
drwxr-x--- 3 root root 62 Mar 2 14:37 incr_backup_redo_log01 第4次增备 这是上面验证不适合Redo Log Archiving 备份的场景的失败备份(可以忽略)
drwxr-x--- 3 root root 62 Mar 2 17:59 incr_backup_0302 第5次增备 # 为的是备份sysbech
drwxr-x--- 2 root root 188 Feb 28 16:25 meta
-rw-r----- 1 root root 1128850446 Feb 28 16:25 my.mbi 全备单文件
-rw-r--r-- 1 root root 17076 Feb 28 16:25 server-all.cnf
-rw-r--r-- 1 root root 3908 Feb 28 16:25 server-my.cnf
1.首选方法是首先恢复完整备份，并使用命令将其更新到执行完整备份的时间。然后使用再次在刚刚恢复的完整备份之上恢复增量备份映像：
将备份文件复制到10.186.62.56
关闭数据库服务：
systemctl stop mysqld_8888
备份以下文件：需要移除数据目录和redo文件
cp -rp U_MYSQL_DATA_INSTALLED /tmp
mv 8888 8888bak0302
rm -rf /data/mysql/log/redolog/8888/*
记录一下目录属主：
actiontech-mysql actiontech-mysql 56 Mar 2 15:02 auto.cnf # 数据目录权限
actiontech-universe actiontech 114 Mar 2 15:02 U_MYSQL_DATA_INSTALLED
-rw-r----- 1 actiontech-mysql actiontech-mysql 2147483648 Mar 2 15:19 ib_logfile0
命令信息：
mysqlbackup --defaults-file=/data/mysql/etc/8888/my.cnf --datadir=/data/mysql/data/8888 --backup-image=/tools/backup/my.mbi --backup-dir=/tools/backup_recovery copy-back-and-apply-log # 会生成数据目录和redo文件
220302 15:48:08 MAIN INFO: Apply-log operation completed successfully.
220302 15:48:08 MAIN INFO: Full Backup has been restored successfully.
注意此处：--backup-dir=/tools/backup_recovery 为恢复命令新建的目录，存放恢复的数据
2.恢复增备，按照备份顺序进行，--backup-dir为临时目录(命令会自动创建)
mysqlbackup --defaults-file=/data/mysql/etc/8888/my.cnf --datadir=/data/mysql/data/8888 --backup-image=/tools/backup/incr_backup_full_scan/incremental_image.mbi --backup-dir=/tools/backup/incr_backup_full_scan_recovery --incremental copy-back-and-apply-log --force
恢复第3次增备的数据：
mysqlbackup --defaults-file=/data/mysql/etc/8888/my.cnf --datadir=/data/mysql/data/8888 --backup-image=/tools/backup/incr_backup_redo_log/incremental_image.mbi --backup-dir=/tools/backup/incr_backup_redo_log_recovery --incremental copy-back-and-apply-log --force
恢复第5次增备的数据：
mysqlbackup --defaults-file=/data/mysql/etc/8888/my.cnf --datadir=/data/mysql/data/8888 --backup-image=/tools/backup/incr_backup_0302/incremental_image.mbi --backup-dir=/tools/backup/incr_backup_0302_recovery --incremental copy-back-and-apply-log --force
上面命令添加 --force 选项的原因是提示全备的LSN 值比增备的大。我们是根据备份目录时间来确定数据时间的，之后我们登录数据库验证一下，最后数据停留的位置应该是sysbench 造数据之后的位置。(我们通过观察每次增备恢复操作之后redo log 和数据目录的时间戳可以判断数据进行了更新)
incremental start-LSN: 22392546
full backup end-LSN: 55186323
3.移动回U_MYSQL_DATA_INSTALLED文件，并更改redo log 和数据目录属主，启动数据库
chown -R actiontech-mysql.actiontech-mysql 8888
mv /tmp/U_MYSQL_DATA_INSTALLED /data/mysql/data/8888
cd /data/mysql/log/redolog/8888
chown actiontech-mysql. ib_logfile*
启动数据库并验证数据：
systemctl start mysqld_8888
和10.186.62.36 的数据进行对比：checksum table sbtest1;checksum table sbtest2;结果是一致的。
这里补充一个场景：
上面第5次增备之前的数据根据目录时间戳（Mar 1 19:55）是没有sysbench 数据的，之后为了数据对比，有单独补充了第5次增备。这里有个小插曲，在恢复完第3次增备之后，错误的以为数据已经是包含sysbench数据的。所以启动了数据库，之后登录进去发现没有数据才进行了第5次增备的操作。
操作上只是在第5次增备之后 关闭了数据库，没有备份或是删除数据目录和redo log ,直接进行的“恢复第5次增备的数据”的操作。之后查看数据目录和redo目录，恢复操作对应数据目录中的文件是追加的，对于rodo目录中的文件是替换的。只需要我们修改对应文件的属主就可以了。
说明Mybackup copy-back-and-apply-log命令实现了两件事：
从映像文件中提取备份并将其复制到要恢复的服务器上的数据目录中。(追加进去的)
对恢复的数据执行应用日志操作以使它们保持最新。
Mybackup 只用于一步，联想Xtrabackup的操作步骤是分为了两步，先合并数据apply-log，再copy-back。
完结撒花。
276
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
