MySQLBackup 8.0.26 备份与恢复
1
MySQL Enterprise Backup介绍
MySQL Enterprise Backup 8.0.26是MySQL 8.0.26的一个备份工具。它是一个多平台、高性能的工具，提供了丰富的功能，如热(在线)备份、增量和差异备份、选择性备份和恢复、支持直接云存储备份、备份加密和压缩，以及许多其他有价值的功能。
虽然优化使用InnoDB表，MySQL企业备份能够备份和恢复由MySQL支持的任何类型的存储引擎创建的所有类型的表。读和写进程的并行性(在独立的多线程中执行)及其块级并行性(不同的线程可以在单个文件中读取、处理或写入不同的块)允许备份和恢复进程以极高的速度完成，与使用mysqldump等工具进行逻辑备份相比，通常可以获得显著的性能提升。
2
备份整库与恢复
1.全备份数据库
[root@localhost bin]# ./mysqlbackup --user=test --password=test --socket=/tmp/mysql_sandbox8026.sock --backup-image=/data/mbackup/my_full.mbi --backup-dir=/data/mbackup backup-to-image --show-progress
MySQL Enterprise Backup Ver 8.0.26-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2021, Oracle and/or its affiliates.
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=test
--password=xxxx
--socket=/tmp/mysql_sandbox8026.sock
--backup-image=/data/mbackup/my_full.mbi
--backup-dir=/data/mbackup
backup-to-image
--show-progress
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'backup-to-image' run mysqlbackup
prints "mysqlbackup completed OK!".
210906 17:51:20 MAIN INFO: Establishing connection to server.
210906 17:51:20 MAIN INFO: No SSL options specified.
210906 17:51:20 MAIN INFO: MySQL server version is '8.0.26'
210906 17:51:20 MAIN INFO: MySQL server compile os version is 'Linux'
210906 17:51:20 MAIN INFO: Got some server configuration information from running server.
210906 17:51:20 MAIN INFO: Establishing connection to server for locking.
210906 17:51:20 MAIN INFO: No SSL options specified.
210906 17:51:20 MAIN INFO: Backup directory exists: '/data/mbackup'
210906 17:51:20 MAIN INFO: MySQL server version_comment is 'MySQL Community Server - GPL'
210906 17:51:20 MAIN INFO: Mysqlbackup component not installed.
210906 17:51:20 MAIN INFO: MEB logfile created at /data/mbackup/meta/MEB_2021-09-06.17-51-20_backup-to-image.log
210906 17:51:20 MAIN INFO: The MySQL server has no active keyring.
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /root/sandboxes/msb_8_0_26/data/
innodb_data_home_dir =
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /root/sandboxes/msb_8_0_26/data/
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_undo_directory = /root/sandboxes/msb_8_0_26/data/
innodb_undo_tablespaces = 2
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /data/mbackup/datadir
innodb_data_home_dir = /data/mbackup/datadir
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /data/mbackup/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_undo_directory = /data/mbackup/datadir
innodb_undo_tablespaces = 2
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
Backup Image Path = /data/mbackup/my_full.mbi
210906 17:51:20 MAIN INFO: Unique generated backup id for this is 16309218803692985
210906 17:51:20 MAIN INFO: Copying the server config file '/root/sandboxes/msb_8_0_26/data/auto.cnf'
210906 17:51:20 MAIN INFO: Creating 14 buffers each of size 16777216.
210906 17:51:20 MAIN INFO: The server is not configured for redo log archiving. The system variable innodb_redo_log_archive_dirs is not set.
210906 17:51:20 MAIN INFO: Found checkpoint at lsn 18223719.
210906 17:51:20 MAIN INFO: Starting log scan from lsn = 18223616 at offset = 18217472 and checkpoint = 18223719 in file /root/sandboxes/msb_8_0_26/data/ib_logfile0.
210906 17:51:20 MAIN INFO: Full Image Backup operation starts with following threads
1 read-threads 6 process-threads 1 write-threads
210906 17:51:20 RPR1 INFO: Progress: 0 of 44 MB; state: Copying metadata
210906 17:51:20 RDR1 INFO: Copying meta file /data/mbackup/backup-my.cnf.
210906 17:51:20 RDR1 INFO: Copying meta file /data/mbackup/meta/backup_create.xml.
210906 17:51:20 RDR1 INFO: Copying meta file /data/mbackup/datadir/backup-auto.cnf.
210906 17:51:20 RLP1 INFO: Starting to parse redo log at lsn = 18223701, whereas checkpoint_lsn = 18223719 and start_lsn = 18223616.
210906 17:51:20 RDR1 INFO: Starting to copy all innodb files...
210906 17:51:20 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/ibdata1.
210906 17:51:20 RDR1 INFO: Starting to copy all undo files...
210906 17:51:20 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/undo_002.
210906 17:51:20 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/undo_001.
210906 17:51:20 RDR1 INFO: Starting to lock instance for backup...
210906 17:51:20 RDR1 INFO: The server instance is locked for backup.
210906 17:51:20 RDR1 INFO: The server instance is unlocked after 0.001 seconds.
210906 17:51:20 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/sys/sys_config.ibd.
210906 17:51:20 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/test/test.ibd.
210906 17:51:20 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/mysql/backup_progress.ibd.
210906 17:51:20 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/mysql.ibd.
210906 17:51:20 RDR1 INFO: Completing the copy of innodb files.
210906 17:51:20 RDR1 INFO: Requesting a dump of the InnoDB buffer pool
210906 17:51:20 RDR1 INFO: Waiting for the dump of the InnoDB buffer pool to complete
210906 17:51:20 RDR1 INFO: The dump of the InnoDB buffer pool completed
210906 17:51:20 RDR1 INFO: Binary Log Basename: '/root/sandboxes/msb_8_0_26/data/binlog'
210906 17:51:20 RDR1 INFO: Binary Log Index: '/root/sandboxes/msb_8_0_26/data/binlog.index'
210906 17:51:20 RDR1 INFO: Relay Channel: 'group_replication_applier'
210906 17:51:20 RDR1 INFO: Relay Log Basename: '/root/sandboxes/msb_8_0_26/data/localhost-relay-bin-group_replication_applier'
210906 17:51:20 RDR1 INFO: Relay Channel: 'group_replication_recovery'
210906 17:51:20 RDR1 INFO: Relay Log Basename: '/root/sandboxes/msb_8_0_26/data/localhost-relay-bin-group_replication_recovery'
210906 17:51:20 RDR1 INFO: Starting to copy Binlog files.
210906 17:51:20 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/binlog.000001.
210906 17:51:20 RDR1 INFO: Starting to lock instance for backup...
210906 17:51:20 RDR1 INFO: The server instance is locked for backup.
210906 17:51:20 RDR1 INFO: The MySQL server has no active keyring.
210906 17:51:20 RDR1 INFO: Requesting flush of redo log reading after LSN 18292873.
210906 17:51:20 RDR1 INFO: Requesting flush of redo log processing after LSN 18292873.
210906 17:51:20 RDR1 INFO: Completed flush of redo log reading after LSN 18292873.
210906 17:51:20 RDR1 INFO: Waiting to process redo log LSN 18292873, have 18292435.
210906 17:51:20 RDR1 INFO: Completed flush of redo log processing after LSN 18292873.
210906 17:51:20 RDR1 INFO: Starting to read-lock tables...
210906 17:51:20 RDR1 INFO: No tables to read-lock.
210906 17:51:20 RDR1 INFO: Opening backup source directory '/root/sandboxes/msb_8_0_26/data'
210906 17:51:20 RDR1 INFO: Starting to copy non-innodb files in subdirs of '/root/sandboxes/msb_8_0_26/data'
210906 17:51:20 WTR1 INFO: Adding database directory: datadir/mysql
210906 17:51:20 WTR1 INFO: Adding database directory: datadir/performance_schema
210906 17:51:20 RDR1 INFO: Completing the copy of all non-innodb files.
210906 17:51:20 WTR1 INFO: Adding database directory: datadir/sys
210906 17:51:20 WTR1 INFO: Adding database directory: datadir/test
210906 17:51:20 RDR1 INFO: Requesting consistency information...
210906 17:51:20 RDR1 INFO: Locked the consistency point for 1762 microseconds.
210906 17:51:20 RDR1 INFO: Consistency point server_uuid '00008026-0000-0000-0000-000000008026'.
210906 17:51:20 RDR1 INFO: Consistency point gtid_executed ''.
210906 17:51:20 RDR1 INFO: Consistency point binary_log_file 'binlog.000002'.
210906 17:51:20 RDR1 INFO: Consistency point binary_log_position 1365.
210906 17:51:20 RDR1 INFO: Consistency point InnoDB lsn 18293311.
210906 17:51:20 RDR1 INFO: Consistency point InnoDB lsn_checkpoint 18223719.
210906 17:51:20 RDR1 INFO: Requesting completion of redo log copy after LSN 18293311.
210906 17:51:20 RLW1 INFO: A copied database page was modified at 18223719. (This is the highest lsn found on a page)
210906 17:51:20 RLW1 INFO: Scanned log up to lsn 18293311.
210906 17:51:20 RLW1 INFO: Was able to parse the log up to lsn 18293311.
210906 17:51:20 RLW1 INFO: Copied redo log
log_start_lsn 18223616
start_checkpoint 18223719
start_lsn 18223719
last_checkpoint 18223719
consistency_lsn 18293311
log_end_lsn 18293311
210906 17:51:20 RLR1 INFO: Redo log reader waited 32 times for a total of 160.00 ms for logs to generate.
210906 17:51:20 RDR1 INFO: Truncating binary log index '/data/mbackup/datadir/binlog.index' to 32.
210906 17:51:20 RDR1 INFO: Truncating binary log 'binlog.000002' to 1365.
210906 17:51:20 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/binlog.000002.
210906 17:51:20 RDR1 INFO: Completed the copy of binlog files...
210906 17:51:20 RDR1 INFO: The server instance is unlocked after 0.053 seconds.
210906 17:51:20 RDR1 INFO: Reading all global variables from the server.
210906 17:51:20 RDR1 INFO: Completed reading of all 615 global variables from the server.
210906 17:51:20 RDR1 INFO: Writing server defaults files 'server-my.cnf' and 'server-all.cnf' for server '8.0.26' in '/data/mbackup'.
210906 17:51:20 RDR1 INFO: Copying meta file /data/mbackup/meta/backup_variables.txt.
210906 17:51:20 RDR1 INFO: Copying meta file /data/mbackup/datadir/ibbackup_logfile.
210906 17:51:20 RDR1 INFO: Copying meta file /data/mbackup/server-all.cnf.
210906 17:51:20 RDR1 INFO: Copying meta file /data/mbackup/server-my.cnf.
210906 17:51:20 RDR1 INFO: Copying meta file /data/mbackup/meta/backup_content.xml.
210906 17:51:20 RDR1 INFO: Copying meta file /data/mbackup/meta/image_files.xml.
210906 17:51:20 MAIN INFO: Progress: 70 of 70 MB; state: Completed
210906 17:51:20 MAIN INFO: Full Image Backup operation completed successfully.
210906 17:51:20 MAIN INFO: Backup image created successfully.
210906 17:51:20 MAIN INFO: Image Path = /data/mbackup/my_full.mbi
210906 17:51:20 MAIN INFO: MySQL binlog position: filename binlog.000002, position 1365
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 18223616
Last Checkpoint LSN : 18223719
End LSN : 18293311
-------------------------------------------------------------
mysqlbackup completed OK
2.验证备份有效性
[root@localhost bin]# ./mysqlbackup --backup-image=/data/mbackup/my_full.mbi validate
MySQL Enterprise Backup Ver 8.0.26-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2021, Oracle and/or its affiliates.
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=/data/mbackup/my_full.mbi
validate
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'validate' run mysqlbackup
prints "mysqlbackup completed OK!".
210907 09:05:50 MAIN INFO: Backup Image MEB version string: 8.0.26
210907 09:05:50 MAIN INFO: MySQL server version is '8.0.26'
210907 09:05:50 MAIN INFO: The backup image has no keyring.
210907 09:05:50 MAIN INFO: Creating 14 buffers each of size 16777216.
210907 09:05:50 MAIN INFO: Validate operation starts with following threads
1 read-threads 6 process-threads
210907 09:05:50 MAIN INFO: Validating image ... /data/mbackup/my_full.mbi
210907 09:05:50 PCR1 INFO: Validate: [Dir]: meta
210907 09:05:51 PCR1 INFO: Validate: [Dir]: datadir/mysql
210907 09:05:51 PCR3 INFO: Validate: [Dir]: datadir/performance_schema
210907 09:05:51 PCR4 INFO: Validate: [Dir]: datadir/sys
210907 09:05:51 PCR4 INFO: Validate: [Dir]: datadir/test
210907 09:05:51 MAIN INFO: datadir/mysql.ibd validated.
210907 09:05:51 MAIN INFO: datadir/mysql/backup_progress.ibd validated.
210907 09:05:51 MAIN INFO: datadir/ibdata1 validated.
210907 09:05:51 MAIN INFO: datadir/undo_002 validated.
210907 09:05:51 MAIN INFO: datadir/undo_001 validated.
210907 09:05:51 MAIN INFO: datadir/sys/sys_config.ibd validated.
210907 09:05:51 MAIN INFO: datadir/test/test.ibd validated.
210907 09:05:51 MAIN INFO: Validate operation completed successfully.
210907 09:05:51 MAIN INFO: Backup Image validation successful.
210907 09:05:51 MAIN INFO: Source Image Path = /data/mbackup/my_full.mbi
mysqlbackup completed OK!
3.恢复全备份数据库
恢复前关闭MySQL，并清空MySQL数据目录，这一步和Xtrabackup一样。
[root@localhost bin]# ./mysqlbackup --datadir=/root/sandboxes/msb_8_0_26/data --backup-image=/data/mbackup/my_full.mbi --backup-dir=/data/mbackup/tmp copy-back-and-apply-log
MySQL Enterprise Backup Ver 8.0.26-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2021, Oracle and/or its affiliates.
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
--datadir=/root/sandboxes/msb_8_0_26/data
--backup-image=/data/mbackup/my_full.mbi
--backup-dir=/data/mbackup/tmp
copy-back-and-apply-log
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'copy-back-and-apply-log' run mysqlbackup
prints "mysqlbackup completed OK!".
210907 09:14:42 MAIN INFO: Backup Image MEB version string: 8.0.26
210907 09:14:42 MAIN INFO: MySQL server version is '8.0.26'
210907 09:14:42 MAIN INFO: Backup directory exists: '/data/mbackup/tmp'
210907 09:14:42 MAIN WARNING: If you restore to a server of a different version, the innodb_data_file_path parameter might have a different default. In that case you need to add 'innodb_data_file_path=ibdata1:12M:autoextend' to the target server configuration.
210907 09:14:42 MAIN WARNING: If you restore to a server of a different version, the innodb_log_files_in_group parameter might have a different default. In that case you need to add 'innodb_log_files_in_group=2' to the target server configuration.
210907 09:14:42 MAIN WARNING: If you restore to a server of a different version, the innodb_log_file_size parameter might have a different default. In that case you need to add 'innodb_log_file_size=50331648' to the target server configuration.
210907 09:14:42 MAIN INFO: MEB logfile created at /data/mbackup/tmp/meta/MEB_2021-09-07.09-14-42_copy-back-and-apply-log.log
210907 09:14:42 MAIN INFO: The backup image has no keyring.
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /root/sandboxes/msb_8_0_26/data
innodb_data_home_dir = /root/sandboxes/msb_8_0_26/data
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /root/sandboxes/msb_8_0_26/data
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_undo_directory = /root/sandboxes/msb_8_0_26/data
innodb_undo_tablespaces = 2
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = Null
innodb_checksum_algorithm = crc32
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /data/mbackup/tmp/datadir
innodb_data_home_dir = /data/mbackup/tmp/datadir
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /data/mbackup/tmp/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_undo_directory = /data/mbackup/tmp/datadir
innodb_undo_tablespaces = 2
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
210907 09:14:42 MAIN INFO: Creating 14 buffers each of size 16777216.
210907 09:14:42 MAIN INFO: Copy-back-and-apply-log from image operation starts with following threads
1 read-threads 6 process-threads 1 write-threads
210907 09:14:42 PCR1 INFO: Copying database directory: meta
210907 09:14:42 RDR1 INFO: Copying ibdata1.
210907 09:14:42 RDR1 INFO: Copying undo_002.
210907 09:14:42 RDR1 INFO: Copying undo_001.
210907 09:14:42 RDR1 INFO: Copying sys/sys_config.ibd.
210907 09:14:42 RDR1 INFO: Copying test/test.ibd.
210907 09:14:42 RDR1 INFO: Copying mysql/backup_progress.ibd.
210907 09:14:42 RDR1 INFO: Copying mysql.ibd.
210907 09:14:42 RDR1 INFO: Copying binlog.000001.
210907 09:14:42 PCR3 INFO: Copying database directory: mysql
210907 09:14:42 PCR6 INFO: Copying database directory: performance_schema
210907 09:14:42 RDR1 INFO: Binary Log Basename: 'binlog'
210907 09:14:42 PCR5 INFO: Copying database directory: sys
210907 09:14:42 RDR1 INFO: Binlog Log Index: '/root/sandboxes/msb_8_0_26/data/binlog.index'
210907 09:14:42 PCR1 INFO: Copying database directory: test
210907 09:14:42 RDR1 INFO: Copying binlog.000002.
210907 09:14:42 MAIN INFO: read_backup_variables_txt_file: '/data/mbackup/tmp/meta/backup_variables.txt'
210907 09:14:42 MAIN INFO: backup variable mysql_version=8.0.26
210907 09:14:42 MAIN INFO: MySQL server version is '8.0.26'
210907 09:14:42 MAIN INFO: Restoring ...8.0.26 version
210907 09:14:42 MAIN INFO: backup variable meb_version=8.0.26
210907 09:14:42 MAIN INFO: backup variable start_lsn=18223616
210907 09:14:42 MAIN INFO: backup variable last_checkpoint=18223719
210907 09:14:42 MAIN INFO: backup variable end_lsn=18293311
210907 09:14:42 MAIN INFO: backup variable apply_log_done=0
210907 09:14:42 MAIN INFO: backup variable is_incremental=0
210907 09:14:42 MAIN INFO: backup variable is_incremental_with_redo_log_only=0
210907 09:14:42 MAIN INFO: backup variable is_partial=0
210907 09:14:42 MAIN INFO: backup variable is_compressed=0
210907 09:14:42 MAIN INFO: backup variable is_skip_binlog=0
210907 09:14:42 MAIN INFO: backup variable is_skip_relaylog=0
210907 09:14:42 MAIN INFO: backup variable is_skip_unused_pages=0
210907 09:14:42 MAIN INFO: backup variable is_onlyinnodb=0
210907 09:14:42 MAIN INFO: backup variable binlog_position=binlog.000002:1365
210907 09:14:42 MAIN INFO: backup variable binlog_index=binlog.index
210907 09:14:42 MAIN INFO: backup variable has_tde_tables=0
210907 09:14:42 MAIN INFO: backup variable start_time_utc=1630921880359728
210907 09:14:42 MAIN INFO: backup variable end_time_utc=1630921880669709
210907 09:14:42 MAIN INFO: backup variable consistency_time_utc=1630921880658335
210907 09:14:42 MAIN INFO: backup variable mysql_version_comment=MySQL Community Server - GPL
210907 09:14:42 MAIN INFO: backup variable log_bin_name=binlog
210907 09:14:42 MAIN INFO: backup variable log_bin_index_name=binlog
210907 09:14:42 MAIN INFO: backup variable innodb_undo_files_count=2
210907 09:14:42 MAIN INFO: Copy-back operation completed successfully.
210907 09:14:42 MAIN INFO: Source Image Path = /data/mbackup/my_full.mbi
210907 09:14:42 MAIN INFO: read_backup_variables_txt_file: '/data/mbackup/tmp/meta/backup_variables.txt'
210907 09:14:42 MAIN INFO: backup variable mysql_version=8.0.26
210907 09:14:42 MAIN INFO: MySQL server version is '8.0.26'
210907 09:14:42 MAIN INFO: Restoring ...8.0.26 version
210907 09:14:42 MAIN INFO: backup variable meb_version=8.0.26
210907 09:14:42 MAIN INFO: backup variable start_lsn=18223616
210907 09:14:42 MAIN INFO: backup variable last_checkpoint=18223719
210907 09:14:42 MAIN INFO: backup variable end_lsn=18293311
210907 09:14:42 MAIN INFO: backup variable apply_log_done=0
210907 09:14:42 MAIN INFO: backup variable is_incremental=0
210907 09:14:42 MAIN INFO: backup variable is_incremental_with_redo_log_only=0
210907 09:14:42 MAIN INFO: backup variable is_partial=0
210907 09:14:42 MAIN INFO: backup variable is_compressed=0
210907 09:14:42 MAIN INFO: backup variable is_skip_binlog=0
210907 09:14:42 MAIN INFO: backup variable is_skip_relaylog=0
210907 09:14:42 MAIN INFO: backup variable is_skip_unused_pages=0
210907 09:14:42 MAIN INFO: backup variable is_onlyinnodb=0
210907 09:14:42 MAIN INFO: backup variable binlog_position=binlog.000002:1365
210907 09:14:42 MAIN INFO: backup variable binlog_index=binlog.index
210907 09:14:42 MAIN INFO: backup variable has_tde_tables=0
210907 09:14:42 MAIN INFO: backup variable start_time_utc=1630921880359728
210907 09:14:42 MAIN INFO: backup variable end_time_utc=1630921880669709
210907 09:14:42 MAIN INFO: backup variable consistency_time_utc=1630921880658335
210907 09:14:42 MAIN INFO: backup variable mysql_version_comment=MySQL Community Server - GPL
210907 09:14:42 MAIN INFO: backup variable log_bin_name=binlog
210907 09:14:42 MAIN INFO: backup variable log_bin_index_name=binlog
210907 09:14:42 MAIN INFO: backup variable innodb_undo_files_count=2
210907 09:14:42 MAIN INFO: Creating 14 buffers each of size 65536.
210907 09:14:42 MAIN INFO: Apply-log operation starts with following threads
1 read-threads 1 process-threads 6 apply-threads
210907 09:14:42 MAIN INFO: Using up to 100 MB of memory.
210907 09:14:42 MAIN INFO: ibbackup_logfile's creation parameters:
start lsn 18223616, end lsn 18293311,
start checkpoint 18223719.
210907 09:14:42 MAIN INFO: Loading the space id : 0, space name : /root/sandboxes/msb_8_0_26/data/ibdata1.
210907 09:14:42 MAIN INFO: Loading the space id 3 name '/root/sandboxes/msb_8_0_26/data/mysql/backup_progress.ibd'.
210907 09:14:42 MAIN INFO: Loading the space id 1 name '/root/sandboxes/msb_8_0_26/data/sys/sys_config.ibd'.
210907 09:14:42 MAIN INFO: Loading the space id 2 name '/root/sandboxes/msb_8_0_26/data/test/test.ibd'.
210907 09:14:42 MAIN INFO: Loading the space id 4294967294 name '/root/sandboxes/msb_8_0_26/data/mysql.ibd'.
210907 09:14:42 MAIN INFO: Loading the space id 4294967279 name '/root/sandboxes/msb_8_0_26/data/undo_001'.
210907 09:14:42 MAIN INFO: Loading the space id 4294967278 name '/root/sandboxes/msb_8_0_26/data/undo_002'.
210907 09:14:42 PCR1 INFO: Starting to parse redo log at lsn = 18223701, whereas checkpoint_lsn = 18223719 and start_lsn = 18223616.
210907 09:14:42 PCR1 INFO: Doing recovery: scanned up to log sequence number 18293311.
210907 09:14:42 PCR1 INFO: Starting to apply a batch of log records to the database....
InnoDB: Progress in percent: 1 2 4 5 7
210907 09:14:42 PCR1 INFO: Create redo log files. target start_lsn 0 last_checkpoint 0 end_lsn 0
210907 09:14:42 PCR1 INFO: Create redo log files. source start_lsn 18223616 last_checkpoint 18223719 end_lsn 18293311
210907 09:14:42 PCR1 INFO: Updating last checkpoint to 18223719 in redo log/
210907 09:14:42 PCR1 INFO: Setting 'ib_logfile0' file size to 50331648
210907 09:14:42 PCR1 INFO: Setting 'ib_logfile1' file size to 50331648
210907 09:14:42 PCR1 INFO: Log file header:
format = 4
pad1 = 0
start lsn = 18223616
checkpoint lsn = 18223719
checksum = 601549513
creator = MEB 8.0.26
210907 09:14:42 PCR1 INFO: We were able to parse ibbackup_logfile up to lsn 18293311.
210907 09:14:42 PCR1 INFO: Last MySQL binlog file position 0 1365, file name binlog.000002
210907 09:14:42 PCR1 INFO: The first data file is '/root/sandboxes/msb_8_0_26/data/ibdata1'
and the new created log files are at '/root/sandboxes/msb_8_0_26/data'
210907 09:14:42 MAIN INFO: Apply-log operation completed successfully.
210907 09:14:42 MAIN INFO: Full Backup has been restored successfully.
mysqlbackup completed OK! with 3 warnings
4.修改对应的权限后，启动MySQL数据库。
3
流式备份到远程服务器
[root@localhost bin]# ./mysqlbackup --defaults-file=/root/sandboxes/msb_8_0_26/my.sandbox.cnf --backup-image=- --backup-dir=/data/mbackup/ backup-to-image | ssh root@192.168.17.128 'cat > /data/mysqlback_meb/full_back.img'
MySQL Enterprise Backup Ver 8.0.26-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2021, Oracle and/or its affiliates.
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
--defaults-file=/root/sandboxes/msb_8_0_26/my.sandbox.cnf
--backup-image=-
--backup-dir=/data/mbackup/
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!".
210907 09:56:53 MAIN INFO: Establishing connection to server.
210907 09:56:53 MAIN INFO: No SSL options specified.
210907 09:56:53 MAIN INFO: MySQL server version is '8.0.26'
210907 09:56:53 MAIN INFO: MySQL server compile os version is 'Linux'
210907 09:56:53 MAIN INFO: Got some server configuration information from running server.
210907 09:56:53 MAIN INFO: Establishing connection to server for locking.
210907 09:56:53 MAIN INFO: No SSL options specified.
210907 09:56:53 MAIN INFO: Backup directory exists: '/data/mbackup/'
210907 09:56:53 MAIN INFO: MySQL server version_comment is 'MySQL Community Server - GPL'
210907 09:56:53 MAIN INFO: Mysqlbackup component not installed.
210907 09:56:53 MAIN INFO: MEB logfile created at /data/mbackup/meta/MEB_2021-09-07.09-56-53_backup-to-image.log
210907 09:56:53 MAIN INFO: The MySQL server has no active keyring.
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /root/sandboxes/msb_8_0_26/data/
innodb_data_home_dir =
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /root/sandboxes/msb_8_0_26/data/
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_undo_directory = /root/sandboxes/msb_8_0_26/data/
innodb_undo_tablespaces = 2
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /data/mbackup/datadir
innodb_data_home_dir = /data/mbackup/datadir
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /data/mbackup/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_undo_directory = /data/mbackup/datadir
innodb_undo_tablespaces = 2
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
210907 09:56:53 MAIN INFO: Backup Image Path = 'stdout'
210907 09:56:53 MAIN INFO: Unique generated backup id for this is 16309798139744045
210907 09:56:53 MAIN INFO: Copying the server config file '/root/sandboxes/msb_8_0_26/data/auto.cnf'
210907 09:56:53 MAIN INFO: Creating 14 buffers each of size 16777216.
210907 09:56:54 MAIN INFO: The server is not configured for redo log archiving. The system variable innodb_redo_log_archive_dirs is not set.
210907 09:56:54 MAIN INFO: Found checkpoint at lsn 18516859.
210907 09:56:54 MAIN INFO: Starting log scan from lsn = 18516480 at offset = 294912 and checkpoint = 18516859 in file /root/sandboxes/msb_8_0_26/data/ib_logfile0.
210907 09:56:54 MAIN INFO: Full Image Backup operation starts with following threads
1 read-threads 6 process-threads 1 write-threads
210907 09:56:54 RDR1 INFO: Copying meta file /data/mbackup/backup-my.cnf.
210907 09:56:54 RLP1 INFO: Starting to parse redo log at lsn = 18516550, whereas checkpoint_lsn = 18516859 and start_lsn = 18516480.
210907 09:56:54 RDR1 INFO: Copying meta file /data/mbackup/meta/backup_create.xml.
210907 09:56:54 RDR1 INFO: Copying meta file /data/mbackup/datadir/backup-auto.cnf.
210907 09:56:54 RDR1 INFO: Starting to copy all innodb files...
210907 09:56:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/ibdata1.
210907 09:56:54 RDR1 INFO: Starting to copy all undo files...
210907 09:56:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/undo_002.
210907 09:56:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/undo_001.
210907 09:56:54 RDR1 INFO: Starting to lock instance for backup...
210907 09:56:54 RDR1 INFO: The server instance is locked for backup.
210907 09:56:54 RDR1 INFO: The server instance is unlocked after 0.001 seconds.
210907 09:56:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/sys/sys_config.ibd.
210907 09:56:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/test/test.ibd.
210907 09:56:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/mysql/backup_progress.ibd.
210907 09:56:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/mysql/backup_history.ibd.
210907 09:56:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/mysql.ibd.
210907 09:56:54 RDR1 INFO: Completing the copy of innodb files.
210907 09:56:54 RDR1 INFO: Requesting a dump of the InnoDB buffer pool
210907 09:56:54 RDR1 INFO: Waiting for the dump of the InnoDB buffer pool to complete
210907 09:56:54 RDR1 INFO: The dump of the InnoDB buffer pool completed
210907 09:56:54 RDR1 INFO: Binary Log Basename: '/root/sandboxes/msb_8_0_26/data/binlog'
210907 09:56:54 RDR1 INFO: Binary Log Index: '/root/sandboxes/msb_8_0_26/data/binlog.index'
210907 09:56:54 RDR1 INFO: Relay Channel: 'group_replication_applier'
210907 09:56:54 RDR1 INFO: Relay Log Basename: '/root/sandboxes/msb_8_0_26/data/localhost-relay-bin-group_replication_applier'
210907 09:56:54 RDR1 INFO: Relay Channel: 'group_replication_recovery'
210907 09:56:54 RDR1 INFO: Relay Log Basename: '/root/sandboxes/msb_8_0_26/data/localhost-relay-bin-group_replication_recovery'
210907 09:56:54 RDR1 INFO: Starting to copy Binlog files.
210907 09:56:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/binlog.000001.
210907 09:56:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/binlog.000002.
210907 09:56:54 RDR1 INFO: Starting to lock instance for backup...
210907 09:56:54 RDR1 INFO: The server instance is locked for backup.
210907 09:56:54 RDR1 INFO: The MySQL server has no active keyring.
210907 09:56:54 RDR1 INFO: Requesting flush of redo log reading after LSN 18522383.
210907 09:56:54 RDR1 INFO: Requesting flush of redo log processing after LSN 18522383.
210907 09:56:54 RDR1 INFO: Waiting to read redo log LSN 18522383, have 18521991.
210907 09:56:54 RDR1 INFO: Completed flush of redo log reading after LSN 18522393.
210907 09:56:54 RDR1 INFO: Completed flush of redo log processing after LSN 18522393.
210907 09:56:54 RDR1 INFO: Starting to read-lock tables...
210907 09:56:54 RDR1 INFO: No tables to read-lock.
210907 09:56:54 RDR1 INFO: Opening backup source directory '/root/sandboxes/msb_8_0_26/data'
210907 09:56:54 RDR1 INFO: Starting to copy non-innodb files in subdirs of '/root/sandboxes/msb_8_0_26/data'
210907 09:56:54 WTR1 INFO: Adding database directory: datadir/mysql
210907 09:56:54 WTR1 INFO: Adding database directory: datadir/performance_schema
210907 09:56:54 RDR1 INFO: Completing the copy of all non-innodb files.
210907 09:56:54 WTR1 INFO: Adding database directory: datadir/sys
210907 09:56:54 WTR1 INFO: Adding database directory: datadir/test
210907 09:56:54 RDR1 INFO: Requesting consistency information...
210907 09:56:54 RDR1 INFO: Locked the consistency point for 5289 microseconds.
210907 09:56:54 RDR1 INFO: Consistency point server_uuid '2891d993-0f79-11ec-ad13-0050563345b0'.
210907 09:56:54 RDR1 INFO: Consistency point gtid_executed ''.
210907 09:56:54 RDR1 INFO: Consistency point binary_log_file 'binlog.000003'.
210907 09:56:54 RDR1 INFO: Consistency point binary_log_position 156.
210907 09:56:54 RDR1 INFO: Consistency point InnoDB lsn 18522786.
210907 09:56:54 RDR1 INFO: Consistency point InnoDB lsn_checkpoint 18517232.
210907 09:56:54 RDR1 INFO: Requesting completion of redo log copy after LSN 18522786.
210907 09:56:54 RLR1 INFO: Redo log reader waited 122 times for a total of 610.00 ms for logs to generate.
210907 09:56:54 RLW1 INFO: A copied database page was modified at 18517214. (This is the highest lsn found on a page)
210907 09:56:54 RLW1 INFO: Scanned log up to lsn 18522786.
210907 09:56:54 RLW1 INFO: Was able to parse the log up to lsn 18522786.
210907 09:56:54 RLW1 INFO: Copied redo log
log_start_lsn 18516480
start_checkpoint 18516859
start_lsn 18516859
last_checkpoint 18517232
consistency_lsn 18522786
log_end_lsn 18522786
210907 09:56:54 RDR1 INFO: Truncating binary log index '/data/mbackup/datadir/binlog.index' to 48.
210907 09:56:54 RDR1 INFO: Truncating binary log 'binlog.000003' to 156.
210907 09:56:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/binlog.000003.
210907 09:56:54 RDR1 INFO: Completed the copy of binlog files...
210907 09:56:54 RDR1 INFO: The server instance is unlocked after 0.033 seconds.
210907 09:56:54 RDR1 INFO: Reading all global variables from the server.
210907 09:56:54 RDR1 INFO: Completed reading of all 615 global variables from the server.
210907 09:56:54 RDR1 INFO: Writing server defaults files 'server-my.cnf' and 'server-all.cnf' for server '8.0.26' in '/data/mbackup/'.
210907 09:56:54 RDR1 INFO: Copying meta file /data/mbackup/meta/backup_variables.txt.
210907 09:56:54 RDR1 INFO: Copying meta file /data/mbackup/datadir/ibbackup_logfile.
210907 09:56:54 RDR1 INFO: Copying meta file /data/mbackup/server-all.cnf.
210907 09:56:54 RDR1 INFO: Copying meta file /data/mbackup/server-my.cnf.
210907 09:56:54 RDR1 INFO: Copying meta file /data/mbackup/meta/backup_content.xml.
210907 09:56:54 RDR1 INFO: Copying meta file /data/mbackup/meta/image_files.xml.
210907 09:56:54 MAIN INFO: Full Image Backup operation completed successfully.
210907 09:56:54 MAIN INFO: Backup image created successfully.
210907 09:56:54 MAIN INFO: Image Path = 'stdout'
210907 09:56:54 MAIN INFO: MySQL binlog position: filename binlog.000003, position 156
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 18516480
Last Checkpoint LSN : 18517232
End LSN : 18522786
-------------------------------------------------------------
mysqlbackup completed OK!
4
增量备份
[root@localhost bin]# ./mysqlbackup --defaults-file=/root/sandboxes/msb_8_0_26/my.sandbox.cnf \
> --incremental --incremental-base=history:last_backup \
> --backup-dir=/data/inc_meb_01 \
> --backup-image=incremental_image1.bi \
> backup-to-image
MySQL Enterprise Backup Ver 8.0.26-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2021, Oracle and/or its affiliates.
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
--defaults-file=/root/sandboxes/msb_8_0_26/my.sandbox.cnf
--incremental
--incremental-base=history:last_backup
--backup-dir=/data/inc_meb_01
--backup-image=incremental_image1.bi
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!".
210907 10:39:54 MAIN INFO: Establishing connection to server.
210907 10:39:54 MAIN INFO: No SSL options specified.
210907 10:39:54 MAIN INFO: MySQL server version is '8.0.26'
210907 10:39:54 MAIN INFO: MySQL server compile os version is 'Linux'
210907 10:39:54 MAIN INFO: Got some server configuration information from running server.
210907 10:39:54 MAIN INFO: Establishing connection to server for locking.
210907 10:39:54 MAIN INFO: No SSL options specified.
210907 10:39:54 MAIN INFO: Backup directory exists: '/data/inc_meb_01'
210907 10:39:54 MAIN INFO: MySQL server version_comment is 'MySQL Community Server - GPL'
210907 10:39:54 MAIN INFO: Mysqlbackup component not installed.
210907 10:39:54 MAIN INFO: No SSL options specified.
210907 10:39:54 MAIN INFO: Location of last successful backup: /data/mbackup/.
210907 10:39:54 MAIN INFO: End time of last successful backup: 2021-09-07 09:56:54.
210907 10:39:54 MAIN INFO: Last backup type: FULL.
210907 10:39:54 MAIN INFO: Using start_lsn=18522786, calculated from backup_history table of MySQL server.
210907 10:39:54 MAIN INFO: Using "full-scan" algorithm for this incremental backup.
210907 10:39:54 MAIN INFO: MEB logfile created at /data/inc_meb_01/meta/MEB_2021-09-07.10-39-54_backup-to-image.log
210907 10:39:54 MAIN INFO: The MySQL server has no active keyring.
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /root/sandboxes/msb_8_0_26/data/
innodb_data_home_dir =
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /root/sandboxes/msb_8_0_26/data/
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_undo_directory = /root/sandboxes/msb_8_0_26/data/
innodb_undo_tablespaces = 2
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /data/inc_meb_01/datadir
innodb_data_home_dir = /data/inc_meb_01/datadir
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /data/inc_meb_01/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_undo_directory = /data/inc_meb_01/datadir
innodb_undo_tablespaces = 2
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
Backup Image Path = /data/inc_meb_01/incremental_image1.bi
210907 10:39:54 MAIN INFO: Unique generated backup id for this is 16309823944083403
210907 10:39:54 MAIN INFO: Copying the server config file '/root/sandboxes/msb_8_0_26/data/auto.cnf'
210907 10:39:54 MAIN INFO: Creating 15 buffers each of size 17301504.
210907 10:39:54 MAIN INFO: The server is not configured for redo log archiving. The system variable innodb_redo_log_archive_dirs is not set.
210907 10:39:54 MAIN INFO: Found checkpoint at lsn 18529828.
210907 10:39:54 MAIN INFO: Starting log scan from lsn = 18529792 at offset = 308224 and checkpoint = 18529828 in file /root/sandboxes/msb_8_0_26/data/ib_logfile0.
210907 10:39:54 MAIN INFO: Incremental Image Backup operation starts with following threads
1 read-threads 6 process-threads 1 write-threads
210907 10:39:54 RDR1 INFO: Copying meta file /data/inc_meb_01/backup-my.cnf.
210907 10:39:54 RDR1 INFO: Copying meta file /data/inc_meb_01/meta/backup_create.xml.
210907 10:39:54 RDR1 INFO: Copying meta file /data/inc_meb_01/datadir/backup-auto.cnf.
210907 10:39:54 RDR1 INFO: Starting to copy all innodb files...
210907 10:39:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/ibdata1.
210907 10:39:54 RDR1 INFO: Starting to copy all undo files...
210907 10:39:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/undo_002.
210907 10:39:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/undo_001.
210907 10:39:54 RDR1 INFO: Starting to lock instance for backup...
210907 10:39:54 RDR1 INFO: The server instance is locked for backup.
210907 10:39:54 RLP1 INFO: Starting to parse redo log at lsn = 18529810, whereas checkpoint_lsn = 18529828 and start_lsn = 18529792.
210907 10:39:54 RDR1 INFO: The server instance is unlocked after 0.003 seconds.
210907 10:39:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/sys/sys_config.ibd.
210907 10:39:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/test/test.ibd.
210907 10:39:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/mysql/backup_progress.ibd.
210907 10:39:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/mysql/backup_history.ibd.
210907 10:39:54 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/mysql.ibd.
210907 10:39:55 RDR1 INFO: Completing the copy of innodb files.
210907 10:39:55 RDR1 INFO: Requesting a dump of the InnoDB buffer pool
210907 10:39:55 RDR1 INFO: Waiting for the dump of the InnoDB buffer pool to complete
210907 10:39:55 RDR1 INFO: The dump of the InnoDB buffer pool completed
210907 10:39:55 RDR1 INFO: Binary Log Basename: '/root/sandboxes/msb_8_0_26/data/binlog'
210907 10:39:55 RDR1 INFO: Binary Log Index: '/root/sandboxes/msb_8_0_26/data/binlog.index'
210907 10:39:55 RDR1 INFO: Relay Channel: 'group_replication_applier'
210907 10:39:55 RDR1 INFO: Relay Log Basename: '/root/sandboxes/msb_8_0_26/data/localhost-relay-bin-group_replication_applier'
210907 10:39:55 RDR1 INFO: Relay Channel: 'group_replication_recovery'
210907 10:39:55 RDR1 INFO: Relay Log Basename: '/root/sandboxes/msb_8_0_26/data/localhost-relay-bin-group_replication_recovery'
210907 10:39:55 RDR1 INFO: Starting to copy Binlog files.
210907 10:39:55 RDR1 INFO: Starting to lock instance for backup...
210907 10:39:55 RDR1 INFO: The server instance is locked for backup.
210907 10:39:55 RDR1 INFO: The MySQL server has no active keyring.
210907 10:39:55 RDR1 INFO: Requesting flush of redo log reading after LSN 18536275.
210907 10:39:55 RDR1 INFO: Requesting flush of redo log processing after LSN 18536275.
210907 10:39:55 RDR1 INFO: Completed flush of redo log reading after LSN 18536275.
210907 10:39:55 RDR1 INFO: Completed flush of redo log processing after LSN 18536275.
210907 10:39:55 RDR1 INFO: Starting to read-lock tables...
210907 10:39:55 RDR1 INFO: No tables to read-lock.
210907 10:39:55 RDR1 INFO: Opening backup source directory '/root/sandboxes/msb_8_0_26/data'
210907 10:39:55 RDR1 INFO: Starting to copy non-innodb files in subdirs of '/root/sandboxes/msb_8_0_26/data'
210907 10:39:55 WTR1 INFO: Adding database directory: datadir/mysql
210907 10:39:55 WTR1 INFO: Adding database directory: datadir/performance_schema
210907 10:39:55 RDR1 INFO: Completing the copy of all non-innodb files.
210907 10:39:55 WTR1 INFO: Adding database directory: datadir/sys
210907 10:39:55 WTR1 INFO: Adding database directory: datadir/test
210907 10:39:55 RDR1 INFO: Requesting consistency information...
210907 10:39:55 RDR1 INFO: Locked the consistency point for 512 microseconds.
210907 10:39:55 RDR1 INFO: Consistency point server_uuid '2891d993-0f79-11ec-ad13-0050563345b0'.
210907 10:39:55 RDR1 INFO: Consistency point gtid_executed ''.
210907 10:39:55 RDR1 INFO: Consistency point binary_log_file 'binlog.000003'.
210907 10:39:55 RDR1 INFO: Consistency point binary_log_position 444.
210907 10:39:55 RDR1 INFO: Consistency point InnoDB lsn 18536752.
210907 10:39:55 RDR1 INFO: Consistency point InnoDB lsn_checkpoint 18529828.
210907 10:39:55 RDR1 INFO: Requesting completion of redo log copy after LSN 18536752.
210907 10:39:55 RLW1 INFO: A copied database page was modified at 18530251. (This is the highest lsn found on a page)
210907 10:39:55 RLW1 INFO: Scanned log up to lsn 18536752.
210907 10:39:55 RLW1 INFO: Was able to parse the log up to lsn 18536752.
210907 10:39:55 RLW1 INFO: Copied redo log
log_start_lsn 18529792
start_checkpoint 18529828
start_lsn 18529828
last_checkpoint 18529828
consistency_lsn 18536752
log_end_lsn 18536752
210907 10:39:55 RLR1 INFO: Redo log reader waited 99 times for a total of 495.00 ms for logs to generate.
210907 10:39:55 RDR1 INFO: Truncating binary log index '/data/inc_meb_01/datadir/binlog.index' to 48.
210907 10:39:55 RDR1 INFO: Truncating binary log 'binlog.000003' to 444.
210907 10:39:55 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/binlog.000003.
210907 10:39:55 RDR1 INFO: Completed the copy of binlog files...
210907 10:39:55 RDR1 INFO: The server instance is unlocked after 0.058 seconds.
210907 10:39:55 RDR1 INFO: Reading all global variables from the server.
210907 10:39:55 RDR1 INFO: Completed reading of all 615 global variables from the server.
210907 10:39:55 RDR1 INFO: Writing server defaults files 'server-my.cnf' and 'server-all.cnf' for server '8.0.26' in '/data/inc_meb_01'.
210907 10:39:55 RDR1 INFO: Copying meta file /data/inc_meb_01/meta/backup_variables.txt.
210907 10:39:55 RDR1 INFO: Copying meta file /data/inc_meb_01/datadir/ibbackup_logfile.
210907 10:39:55 RDR1 INFO: Copying meta file /data/inc_meb_01/server-all.cnf.
210907 10:39:55 RDR1 INFO: Copying meta file /data/inc_meb_01/server-my.cnf.
210907 10:39:55 RDR1 INFO: Copying meta file /data/inc_meb_01/datadir/ibbackup_ibd_files.
210907 10:39:55 RDR1 INFO: Copying meta file /data/inc_meb_01/meta/backup_content.xml.
210907 10:39:55 RDR1 INFO: Copying meta file /data/inc_meb_01/meta/image_files.xml.
210907 10:39:55 MAIN INFO: Incremental Image Backup operation completed successfully.
210907 10:39:55 MAIN INFO: Backup image created successfully.
210907 10:39:55 MAIN INFO: Image Path = /data/inc_meb_01/incremental_image1.bi
210907 10:39:55 MAIN INFO: Backup contains changes from lsn 18522787 to lsn 18536752
210907 10:39:55 MAIN INFO: MySQL binlog position: filename binlog.000003, position 444
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 18522787
Last Checkpoint LSN : 18529828
End LSN : 18536752
-------------------------------------------------------------
mysqlbackup completed OK!
5
备份部分表
[root@localhost bin]# ./mysqlbackup --defaults-file=/root/sandboxes/msb_8_0_26/my.sandbox.cnf \
> --backup-dir=/data/parti_meb --backup-image=/data/parti_meb/test.mbi \
> --include-tables="\.test" \
> backup-to-image
MySQL Enterprise Backup Ver 8.0.26-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2021, Oracle and/or its affiliates.
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
--defaults-file=/root/sandboxes/msb_8_0_26/my.sandbox.cnf
--backup-dir=/data/parti_meb
--backup-image=/data/parti_meb/test.mbi
--include-tables=\.test
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!".
210907 10:47:24 MAIN INFO: Establishing connection to server.
210907 10:47:24 MAIN INFO: No SSL options specified.
210907 10:47:24 MAIN INFO: MySQL server version is '8.0.26'
210907 10:47:24 MAIN INFO: MySQL server compile os version is 'Linux'
210907 10:47:24 MAIN INFO: Got some server configuration information from running server.
210907 10:47:24 MAIN INFO: Establishing connection to server for locking.
210907 10:47:24 MAIN INFO: No SSL options specified.
210907 10:47:24 MAIN INFO: Backup directory exists: '/data/parti_meb'
210907 10:47:24 MAIN INFO: MySQL server version_comment is 'MySQL Community Server - GPL'
210907 10:47:24 MAIN INFO: Mysqlbackup component not installed.
210907 10:47:24 MAIN INFO: MEB logfile created at /data/parti_meb/meta/MEB_2021-09-07.10-47-24_backup-to-image.log
210907 10:47:24 MAIN INFO: The MySQL server has no active keyring.
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /root/sandboxes/msb_8_0_26/data/
innodb_data_home_dir =
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /root/sandboxes/msb_8_0_26/data/
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_undo_directory = /root/sandboxes/msb_8_0_26/data/
innodb_undo_tablespaces = 2
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /data/parti_meb/datadir
innodb_data_home_dir = /data/parti_meb/datadir
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /data/parti_meb/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_undo_directory = /data/parti_meb/datadir
innodb_undo_tablespaces = 2
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
Backup Image Path = /data/parti_meb/test.mbi
210907 10:47:24 MAIN INFO: Unique generated backup id for this is 16309828449845664
210907 10:47:24 MAIN INFO: Copying the server config file '/root/sandboxes/msb_8_0_26/data/auto.cnf'
210907 10:47:24 MAIN INFO: Creating 14 buffers each of size 16777216.
210907 10:47:25 MAIN INFO: The server is not configured for redo log archiving. The system variable innodb_redo_log_archive_dirs is not set.
210907 10:47:25 MAIN INFO: Found checkpoint at lsn 18546663.
210907 10:47:25 MAIN INFO: Starting log scan from lsn = 18546176 at offset = 324608 and checkpoint = 18546663 in file /root/sandboxes/msb_8_0_26/data/ib_logfile0.
210907 10:47:25 MAIN INFO: Full Image Backup operation starts with following threads
1 read-threads 6 process-threads 1 write-threads
210907 10:47:25 RDR1 INFO: Copying meta file /data/parti_meb/backup-my.cnf.
210907 10:47:25 RDR1 INFO: Copying meta file /data/parti_meb/meta/backup_create.xml.
210907 10:47:25 RLP1 INFO: Starting to parse redo log at lsn = 18546270, whereas checkpoint_lsn = 18546663 and start_lsn = 18546176.
210907 10:47:25 RDR1 INFO: Copying meta file /data/parti_meb/datadir/backup-auto.cnf.
210907 10:47:25 RDR1 INFO: Starting to copy all innodb files...
210907 10:47:25 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/ibdata1.
210907 10:47:25 RDR1 INFO: Starting to copy all undo files...
210907 10:47:25 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/undo_002.
210907 10:47:25 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/undo_001.
210907 10:47:25 RDR1 INFO: Starting to lock instance for backup...
210907 10:47:25 RDR1 INFO: The server instance is locked for backup.
210907 10:47:25 RDR1 INFO: The server instance is unlocked after 0.001 seconds.
210907 10:47:25 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/test/test.ibd.
210907 10:47:25 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/mysql.ibd.
210907 10:47:25 RDR1 INFO: Completing the copy of innodb files.
210907 10:47:25 RDR1 INFO: Binary Log Basename: '/root/sandboxes/msb_8_0_26/data/binlog'
210907 10:47:25 RDR1 INFO: Binary Log Index: '/root/sandboxes/msb_8_0_26/data/binlog.index'
210907 10:47:25 RDR1 INFO: Relay Channel: 'group_replication_applier'
210907 10:47:25 RDR1 INFO: Relay Log Basename: '/root/sandboxes/msb_8_0_26/data/localhost-relay-bin-group_replication_applier'
210907 10:47:25 RDR1 INFO: Relay Channel: 'group_replication_recovery'
210907 10:47:25 RDR1 INFO: Relay Log Basename: '/root/sandboxes/msb_8_0_26/data/localhost-relay-bin-group_replication_recovery'
210907 10:47:25 RDR1 INFO: Starting to copy Binlog files.
210907 10:47:25 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/binlog.000001.
210907 10:47:25 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/binlog.000002.
210907 10:47:25 RDR1 INFO: Starting to lock instance for backup...
210907 10:47:25 RDR1 INFO: The server instance is locked for backup.
210907 10:47:25 RDR1 INFO: The MySQL server has no active keyring.
210907 10:47:25 RDR1 INFO: Requesting flush of redo log reading after LSN 18548372.
210907 10:47:25 RDR1 INFO: Requesting flush of redo log processing after LSN 18548372.
210907 10:47:25 RDR1 INFO: Completed flush of redo log reading after LSN 18548382.
210907 10:47:25 RDR1 INFO: Completed flush of redo log processing after LSN 18548372.
210907 10:47:25 RDR1 INFO: Starting to read-lock tables...
210907 10:47:25 RDR1 INFO: No tables to read-lock.
210907 10:47:25 RDR1 INFO: Opening backup source directory '/root/sandboxes/msb_8_0_26/data'
210907 10:47:25 RDR1 INFO: Starting to copy non-innodb files in subdirs of '/root/sandboxes/msb_8_0_26/data'
210907 10:47:25 RDR1 INFO: Completing the copy of all non-innodb files.
210907 10:47:25 RDR1 INFO: Requesting consistency information...
210907 10:47:25 RDR1 INFO: Locked the consistency point for 371 microseconds.
210907 10:47:25 RDR1 INFO: Consistency point server_uuid '2891d993-0f79-11ec-ad13-0050563345b0'.
210907 10:47:25 RDR1 INFO: Consistency point gtid_executed ''.
210907 10:47:25 RDR1 INFO: Consistency point binary_log_file 'binlog.000003'.
210907 10:47:25 RDR1 INFO: Consistency point binary_log_position 444.
210907 10:47:25 RDR1 INFO: Consistency point InnoDB lsn 18553468.
210907 10:47:25 RDR1 INFO: Consistency point InnoDB lsn_checkpoint 18546663.
210907 10:47:25 RDR1 INFO: Requesting completion of redo log copy after LSN 18553468.
210907 10:47:25 RLW1 INFO: A copied database page was modified at 18546663. (This is the highest lsn found on a page)
210907 10:47:25 RLW1 INFO: Scanned log up to lsn 18553468.
210907 10:47:25 RLW1 INFO: Was able to parse the log up to lsn 18553468.
210907 10:47:25 RLR1 INFO: Redo log reader waited 34 times for a total of 170.00 ms for logs to generate.
210907 10:47:25 RLW1 INFO: Copied redo log
log_start_lsn 18546176
start_checkpoint 18546663
start_lsn 18546663
last_checkpoint 18546663
consistency_lsn 18553468
log_end_lsn 18553468
210907 10:47:25 RDR1 INFO: Truncating binary log index '/data/parti_meb/datadir/binlog.index' to 48.
210907 10:47:25 RDR1 INFO: Truncating binary log 'binlog.000003' to 444.
210907 10:47:25 RDR1 INFO: Copying /root/sandboxes/msb_8_0_26/data/binlog.000003.
210907 10:47:25 RDR1 INFO: Completed the copy of binlog files...
210907 10:47:25 RDR1 INFO: The server instance is unlocked after 0.031 seconds.
210907 10:47:25 RDR1 INFO: Reading all global variables from the server.
210907 10:47:25 RDR1 INFO: Completed reading of all 615 global variables from the server.
210907 10:47:25 RDR1 INFO: Writing server defaults files 'server-my.cnf' and 'server-all.cnf' for server '8.0.26' in '/data/parti_meb'.
210907 10:47:25 RDR1 INFO: Copying meta file /data/parti_meb/meta/backup_variables.txt.
210907 10:47:25 RDR1 INFO: Copying meta file /data/parti_meb/datadir/ibbackup_logfile.
210907 10:47:25 RDR1 INFO: Copying meta file /data/parti_meb/server-all.cnf.
210907 10:47:25 RDR1 INFO: Copying meta file /data/parti_meb/server-my.cnf.
210907 10:47:25 RDR1 INFO: Copying meta file /data/parti_meb/meta/backup_content.xml.
210907 10:47:25 RDR1 INFO: Copying meta file /data/parti_meb/meta/image_files.xml.
210907 10:47:25 MAIN INFO: Full Image Backup operation completed successfully.
210907 10:47:25 MAIN INFO: Backup image created successfully.
210907 10:47:25 MAIN INFO: Image Path = /data/parti_meb/test.mbi
210907 10:47:25 MAIN INFO: MySQL binlog position: filename binlog.000003, position 444
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 18546176
Last Checkpoint LSN : 18546663
End LSN : 18553468
-------------------------------------------------------------
mysqlbackup completed OK!
6
恢复增量备份
在执行增量恢复之前，要先恢复完整全量备份，即上面第2大点备份整库与恢复中的第3小点。
[root@localhost bin]# ./mysqlbackup --defaults-file=/root/sandboxes/msb_8_0_26/my.sandbox.cnf --backup-image=/data/inc_meb_01/incremental_image1.bi \
> --backup-dir=/data/inc_meb_01/tmp --datadir=/root/sandboxes/msb_8_0_26/data --incremental \
> copy-back-and-apply-log
......
210907 11:25:11 PCR1 INFO: Log file header:
format = 4
pad1 = 0
start lsn = 18539520
checkpoint lsn = 18539594
checksum = 2666310026
creator = MEB 8.0.26
210907 11:25:11 PCR1 INFO: We were able to parse ibbackup_logfile up to lsn 18544528.
210907 11:25:11 PCR1 INFO: Last MySQL binlog file position 0 443, file name binlog.000004
210907 11:25:11 PCR1 INFO: The first data file is '/root/sandboxes/msb_8_0_26/data/ibdata1'
and the new created log files are at '/root/sandboxes/msb_8_0_26/data'
210907 11:25:11 MAIN INFO: Apply-log operation completed successfully.
210907 11:25:11 MAIN INFO: Incremental backup applied successfully.
mysqlbackup completed OK! with 3 warnings7
恢复部分备份
恢复单表的前提是MySQL正在运行。
[root@localhost bin]# ./mysqlbackup --defaults-file=/root/sandboxes/msb_8_0_26/my.sandbox.cnf --backup-dir=/data/parti_meb/tmp --backup-image=/data/parti_meb/test.mbi --include-tables="^test\.test" copy-back-and-apply-log
......
210907 11:49:37 PCR1 INFO: backup variable log_bin_index_name=binlog
210907 11:49:37 PCR1 INFO: backup variable innodb_undo_files_count=2
210907 11:49:37 PCR1 INFO: Last MySQL binlog file position 0 731, file name binlog.000004
210907 11:49:37 PCR1 INFO: The first data file is '/root/sandboxes/msb_8_0_26/data/ibdata1'
and the new created log files are at '/root/sandboxes/msb_8_0_26/data/'
210907 11:49:37 MAIN INFO: Importing table: `test`.`test`.
210907 11:49:37 MAIN INFO: Analyzing table: `test`.`test`.
210907 11:49:37 MAIN INFO: Apply-log operation completed successfully.
210907 11:49:37 MAIN INFO: Backup has been restored successfully.
mysqlbackup completed OK!
8
总结
- MySQL Enterprise Backup（MEB）是收费工具，可以跨平台运行，MEB版8.0.26只能用于MySQL 8.0.26，有相应的版本对应。
- MEB功能较多，像压缩备份、并行备份、加密、流式传输、表空间传输、备份REDO归档、备份到云等，感兴趣的朋友可以自行测试。
参考 ：https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2021-09-28，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。