Mysqlbackup 基于binlog 的恢复示例
目录
适用范围
MYSQL 5.7+
方案概述
客户环境，定期对MYSQL做全备，同进每15分钟会对做一个备份，通过Mysqlbackup 全备或增量恢复后，需要对binlog进行前滚，恢复到指定的时间点。
实施步骤
1.备份
[root@s2ahuoracle02 run]# /u01/mysql8e/mysql/bin/mysqlbackup --login-path=backup --encrypt-password=MysqlBackup.2022 --socket=/u01/mysql8e/data/run/mysql3333.sock --backup-image=/tmp/backups/fullbackup_`date +%Y%m%d`.mbi --backup-dir=/tmp/backups/temp backup-to-image
MySQL Enterprise Backup Ver 8.0.30-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2022, 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 ...
/u01/mysql8e/mysql/bin/mysqlbackup
--login-path=backup
--encrypt-password=xxxxxxxxxxxxxxxx
--socket=/u01/mysql8e/data/run/mysql3333.sock
--backup-image=/tmp/backups/fullbackup_20221109.mbi
--backup-dir=/tmp/backups/temp
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!".
221109 19:04:17 MAIN INFO: Establishing connection to server.
221109 19:04:17 MAIN INFO: No SSL options specified.
221109 19:04:17 MAIN INFO: MySQL server version is '8.0.30-commercial'
221109 19:04:17 MAIN INFO: MySQL server compile os version is 'Linux'
221109 19:04:17 MAIN INFO: Got some server configuration information from running server.
221109 19:04:17 MAIN INFO: Establishing connection to server for locking.
221109 19:04:17 MAIN INFO: No SSL options specified.
221109 19:04:17 MAIN INFO: Backup directory created: '/tmp/backups/temp'
221109 19:04:17 MAIN INFO: MySQL server version_comment is 'MySQL Enterprise Server - Commercial'
221109 19:04:17 MAIN INFO: Mysqlbackup component not installed.
221109 19:04:17 MAIN INFO: MEB logfile created at /tmp/backups/temp/meta/MEB_2022-11-09.19-04-17_backup-to-image.log
221109 19:04:17 MAIN INFO: The server's active keyring is 'keyring_file'.
221109 19:04:17 MAIN INFO: The keyring file is located at '//u01/mysql8e/data/data/mysql-keyring/keyring', size 1051.
221109 19:04:17 MAIN INFO: Exporting KUF '//u01/mysql8e/data/data/mysql-keyring/keyring' to KEF '/tmp/backups/temp/meta/keyring_kef'.
221109 19:04:17 MAIN INFO: The keyring file is exported to '/tmp/backups/temp/meta/keyring_kef'.
221109 19:04:17 MAIN INFO: Initialized keyring with 8 keys.
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /u01/mysql8e/data/data/
innodb_data_home_dir = /u01/mysql8e/data/data
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_group_home_dir = /u01/mysql8e/data/binlogs/innodb
innodb_undo_directory = /u01/mysql8e/data/data/
innodb_undo_tablespaces = 2
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /tmp/backups/temp/datadir
innodb_data_home_dir = /tmp/backups/temp/datadir
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_group_home_dir = /tmp/backups/temp/datadir
innodb_undo_directory = /tmp/backups/temp/datadir
innodb_undo_tablespaces = 2
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
Backup Image Path = /tmp/backups/fullbackup_20221109.mbi
221109 19:04:17 MAIN INFO: Unique generated backup id for this is 16679918574296632
221109 19:04:17 MAIN INFO: Copying the server config file '/u01/mysql8e/data/data/auto.cnf'
221109 19:04:17 MAIN INFO: Creating 14 buffers each of size 16777216.
221109 19:04:17 MAIN INFO: The server is not configured for redo log archiving. The system variable innodb_redo_log_archive_dirs is not set.
221109 19:04:17 MAIN INFO: Found checkpoint at lsn 21084993.
221109 19:04:17 MAIN INFO: Read redo log encryption metadata successful..
221109 19:04:17 MAIN INFO: Starting log scan from lsn = 21084672 at offset = 873793 and checkpoint = 21084993 in file /u01/mysql8e/data/binlogs/innodb/#innodb_redo/#ib_redo1.
221109 19:04:17 MAIN INFO: This backup includes a keyring.
221109 19:04:17 MAIN INFO: Full Image Backup operation starts with following threads
1 read-threads 6 process-threads 1 write-threads
221109 19:04:17 RDR1 INFO: Copying meta file /tmp/backups/temp/backup-my.cnf.
221109 19:04:17 RDR1 INFO: Copying meta file /tmp/backups/temp/meta/backup_create.xml.
221109 19:04:17 RDR1 INFO: Copying meta file /tmp/backups/temp/datadir/backup-auto.cnf.
221109 19:04:17 RDR1 INFO: Copying meta file /tmp/backups/temp/meta/keyring_kef.
221109 19:04:17 RDR1 INFO: Starting to copy all innodb files...
221109 19:04:17 RDR1 INFO: Copying /u01/mysql8e/data/data/ibdata1.
221109 19:04:17 RLP1 INFO: Starting to parse redo log at lsn = 21084690, whereas checkpoint_lsn = 21084993 and start_lsn = 21084672.
RDR1 Progress in MB: 200 400 600 800 1000
221109 19:04:34 RDR1 INFO: Starting to copy all undo files...
221109 19:04:34 RDR1 INFO: Copying /u01/mysql8e/data/data/undo_002.
221109 19:04:35 RDR1 INFO: Copying /u01/mysql8e/data/data/undo_001.
221109 19:04:35 RDR1 INFO: Starting to lock instance for backup...
221109 19:04:35 RDR1 INFO: The server instance is locked for backup.
221109 19:04:35 RDR1 INFO: The server instance is unlocked after 0.004 seconds.
221109 19:04:35 RDR1 INFO: Copying /u01/mysql8e/data/data/sys/sys_config.ibd.
221109 19:04:35 RDR1 INFO: Copying /u01/mysql8e/data/data/mysql/backup_progress.ibd.
221109 19:04:35 RDR1 INFO: Copying /u01/mysql8e/data/data/mysql/backup_history.ibd.
221109 19:04:35 RDR1 INFO: Copying /u01/mysql8e/data/data/mysql/audit_log_filter.ibd.
221109 19:04:35 RDR1 INFO: Copying /u01/mysql8e/data/data/mysql/audit_log_user.ibd.
221109 19:04:35 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/tr_bak#p#p0.ibd.
221109 19:04:35 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/tr_bak#p#p1.ibd.
221109 19:04:35 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/tr_bak#p#p2.ibd.
221109 19:04:35 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/tr_bak#p#p3.ibd.
221109 19:04:35 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/tr_bak#p#p4.ibd.
221109 19:04:35 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/tr_bak#p#p5.ibd.
221109 19:04:35 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/tr#p#p0.ibd.
221109 19:04:35 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/tr#p#p1.ibd.
221109 19:04:35 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/tr#p#p2.ibd.
221109 19:04:35 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/tr#p#p3.ibd.
221109 19:04:35 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/tr#p#p4.ibd.
221109 19:04:35 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/tr#p#p5.ibd.
221109 19:04:36 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/test_bak#p#p0.ibd.
221109 19:04:36 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/test_bak#p#p1.ibd.
221109 19:04:36 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/test_bak#p#p2.ibd.
221109 19:04:36 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/test_bak#p#p3.ibd.
221109 19:04:36 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/test_bak#p#p4.ibd.
221109 19:04:36 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/test_bak#p#p5.ibd.
221109 19:04:36 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/test#p#p0.ibd.
221109 19:04:36 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/test#p#p1.ibd.
221109 19:04:36 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/test#p#p2.ibd.
221109 19:04:36 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/test#p#p3.ibd.
221109 19:04:36 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/test#p#p4.ibd.
221109 19:04:36 RDR1 INFO: Copying /u01/mysql8e/data/data/pardb/test#p#p5.ibd.
221109 19:04:36 RDR1 INFO: Copying /u01/mysql8e/data/data/mysql.ibd.
221109 19:04:36 RDR1 INFO: Completing the copy of innodb files.
221109 19:04:36 RDR1 INFO: Requesting a dump of the InnoDB buffer pool
221109 19:04:36 RDR1 INFO: Waiting for the dump of the InnoDB buffer pool to complete
221109 19:04:36 RDR1 INFO: The dump of the InnoDB buffer pool completed
221109 19:04:36 RDR1 INFO: Binary Log Basename: '/u01/mysql8e/data/binlogs/binlog'
221109 19:04:36 RDR1 INFO: Binary Log Index: '/u01/mysql8e/data/binlogs/binlog.index'
221109 19:04:36 RDR1 INFO: Relay Channel: 'group_replication_applier'
221109 19:04:36 RDR1 INFO: Relay Log Basename: '/u01/mysql8e/data/binlogs/relay-group_replication_applier'
221109 19:04:36 RDR1 INFO: Relay Channel: 'group_replication_recovery'
221109 19:04:36 RDR1 INFO: Relay Log Basename: '/u01/mysql8e/data/binlogs/relay-group_replication_recovery'
221109 19:04:36 RDR1 INFO: Starting to copy Binlog files.
221109 19:04:36 RDR1 INFO: Starting to lock instance for backup...
221109 19:04:36 RDR1 INFO: The server instance is locked for backup.
221109 19:04:36 RDR1 INFO: Reloading the keyring.
221109 19:04:36 RDR1 INFO: The server's active keyring is 'keyring_file'.
221109 19:04:36 RDR1 INFO: The keyring file is located at '//u01/mysql8e/data/data/mysql-keyring/keyring', size 1051.
221109 19:04:36 RDR1 INFO: Exporting KUF '//u01/mysql8e/data/data/mysql-keyring/keyring' to KEF '/tmp/backups/temp/meta/keyring_kef'.
221109 19:04:36 RDR1 INFO: The keyring file is exported to '/tmp/backups/temp/meta/keyring_kef'.
221109 19:04:36 RDR1 INFO: Initialized keyring with 8 keys.
221109 19:04:36 RDR1 INFO: Requesting flush of redo log reading after LSN 21090429.
221109 19:04:36 RDR1 INFO: Requesting flush of redo log processing after LSN 21090429.
221109 19:04:36 RDR1 INFO: Completed flush of redo log reading after LSN 21090429.
221109 19:04:36 RDR1 INFO: Completed flush of redo log processing after LSN 21090429.
221109 19:04:36 RDR1 INFO: Starting to read-lock tables...
221109 19:04:36 RDR1 INFO: No tables to read-lock.
221109 19:04:36 RDR1 INFO: Opening backup source directory '/u01/mysql8e/data/data'
221109 19:04:36 RDR1 INFO: Starting to copy non-innodb files in subdirs of '/u01/mysql8e/data/data'
221109 19:04:36 WTR1 INFO: Adding database directory: datadir/mysql
221109 19:04:36 WTR1 INFO: Adding database directory: datadir/mysql-keyring
221109 19:04:36 WTR1 INFO: Adding database directory: datadir/pardb
221109 19:04:36 WTR1 INFO: Adding database directory: datadir/performance_schema
221109 19:04:37 RDR1 INFO: Completing the copy of all non-innodb files.
221109 19:04:37 WTR1 INFO: Adding database directory: datadir/sys
221109 19:04:37 RDR1 INFO: Requesting consistency information...
221109 19:04:37 RDR1 INFO: Locked the consistency point for 641 microseconds.
221109 19:04:37 RDR1 INFO: Consistency point server_uuid '75477e61-49e1-11ed-b1db-005056a6b799'.
221109 19:04:37 RDR1 INFO: Consistency point gtid_executed '594d3908-49ce-11ed-b130-005056a6b799:1-20,
75477e61-49e1-11ed-b1db-005056a6b799:1-85'.
221109 19:04:37 RDR1 INFO: Consistency point binary_log_file 'binlog.000001'.
221109 19:04:37 RDR1 INFO: Consistency point binary_log_position 26195.
221109 19:04:37 RDR1 INFO: Consistency point InnoDB lsn 21090956.
221109 19:04:37 RDR1 INFO: Consistency point InnoDB lsn_checkpoint 21089861.
221109 19:04:37 RDR1 INFO: Requesting completion of redo log copy after LSN 21090956.
221109 19:04:37 RLW1 INFO: A copied database page was modified at 21089237. (This is the highest lsn found on a page)
221109 19:04:37 RLW1 INFO: Scanned log up to lsn 21090956.
221109 19:04:37 RLW1 INFO: Was able to parse the log up to lsn 21090956.
221109 19:04:37 RLW1 INFO: Copied redo log
log_start_lsn 21084672
start_checkpoint 21084993
start_lsn 21084993
last_checkpoint 21089861
consistency_lsn 21090956
log_end_lsn 21090956
221109 19:04:37 RLR1 INFO: Redo log reader waited 2491 times for a total of 12455.00 ms for logs to generate.
221109 19:04:37 RDR1 INFO: Truncating binary log index '/tmp/backups/temp/datadir/binlog.index' to 40.
221109 19:04:37 RDR1 INFO: Truncating binary log 'binlog.000001' to 26195 plus encryption header to 26707.
221109 19:04:37 RDR1 INFO: Copying /u01/mysql8e/data/binlogs/binlog.000001.
221109 19:04:37 RDR1 INFO: Completed the copy of binlog files...
221109 19:04:37 RDR1 INFO: The server instance is unlocked after 0.788 seconds.
221109 19:04:37 RDR1 INFO: Reading all global variables from the server.
221109 19:04:37 RDR1 INFO: Completed reading of all 668 global variables from the server.
221109 19:04:37 RDR1 INFO: Writing server defaults files 'server-my.cnf' and 'server-all.cnf' for server '8.0.30-commercial' in '/tmp/backups/temp'.
221109 19:04:37 RDR1 INFO: Copying meta file /tmp/backups/temp/meta/backup_variables.txt.
221109 19:04:37 RDR1 INFO: Copying meta file /tmp/backups/temp/datadir/ibbackup_logfile.
221109 19:04:37 RDR1 INFO: Copying meta file /tmp/backups/temp/server-all.cnf.
221109 19:04:37 RDR1 INFO: Copying meta file /tmp/backups/temp/server-my.cnf.
221109 19:04:37 RDR1 INFO: Copying meta file /tmp/backups/temp/meta/backup_gtid_executed.sql.
221109 19:04:37 RDR1 INFO: Copying meta file /tmp/backups/temp/meta/backup_content.xml.
221109 19:04:37 RDR1 INFO: Copying meta file /tmp/backups/temp/meta/image_files.xml.
221109 19:04:37 MAIN INFO: Full Image Backup operation completed successfully.
221109 19:04:37 MAIN INFO: Backup image created successfully.
221109 19:04:37 MAIN INFO: Image Path = /tmp/backups/fullbackup_20221109.mbi
221109 19:04:37 MAIN INFO: MySQL binlog position: filename binlog.000001, position 26195
221109 19:04:37 MAIN INFO: GTID_EXECUTED is 594d3908-49ce-11ed-b130-005056a6b799:1-20,75477e61-49e1-11ed-b1db-005056a6b799:1-85
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 21084672
Last Checkpoint LSN : 21089861
End LSN : 21090956
-------------------------------------------------------------
mysqlbackup completed OK!
[root@s2ahuoracle02 run]# cd ../
2.插入增量测试数据
[root@s2ahuoracle02 run]# /u01/mysql8e/mysql/bin/mysql -u root -p -P3333 --socket=/u01/mysql8e/data/run/mysql3333.sock
mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.01 sec)
mysql>
mysql>
mysql>
mysql> SHOW VARIABLES LIKE '%binlog%';
+------------------------------------------------+----------------------+
| Variable_name | Value |
+------------------------------------------------+----------------------+
| binlog_cache_size | 1048576 |
| binlog_checksum | NONE |
| binlog_direct_non_transactional_updates | OFF |
| binlog_encryption | ON |
| binlog_error_action | ABORT_SERVER |
| binlog_expire_logs_auto_purge | ON |
| binlog_expire_logs_seconds | 604800 |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_rotate_encryption_master_key_at_startup | OFF |
| binlog_row_event_max_size | 8192 |
| binlog_row_image | FULL |
| binlog_row_metadata | MINIMAL |
| binlog_row_value_options | |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_compression | OFF |
| binlog_transaction_compression_level_zstd | 3 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
| innodb_api_enable_binlog | OFF |
| log_statements_unsafe_for_binlog | ON |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 104857600 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 1 |
+------------------------------------------------+----------------------+
30 rows in set (0.00 sec)
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| pardb |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> user pardb ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'user pardb' at line 1
mysql> use pardb ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables ;
+-----------------+
| Tables_in_pardb |
+-----------------+
| test |
| test_bak |
| tr |
| tr_bak |
+-----------------+
4 rows in set (0.00 sec)
mysql> select * from test ;
+----+----------------+------------+
| id | name | purchased |
+----+----------------+------------+
| 1 | bookcase | 1989-01-10 |
| 2 | sofa | 1987-06-05 |
| 3 | aquarium | 1992-08-04 |
| 4 | alarm clock | 1997-11-05 |
| 5 | lava lamp | 1998-12-25 |
| 6 | desk organiser | 2003-10-15 |
| 7 | chair | 2009-03-10 |
| 8 | study desk | 2006-09-16 |
| 9 | exercise bike | 2014-05-09 |
| 10 | espresso maker | 2011-11-22 |
+----+----------------+------------+
10 rows in set (0.00 sec)
mysql> insert into test(name,purchased) values ('backup1','2011-11-22') ;
Query OK, 1 row affected (0.01 sec)
mysql> flush logs ;
Query OK, 0 rows affected (0.04 sec)
mysql> show master ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> show master status \G
*************************** 1. row ***************************
File: binlog.000002
Position: 193
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 594d3908-49ce-11ed-b130-005056a6b799:1-20,
75477e61-49e1-11ed-b1db-005056a6b799:1-86
1 row in set (0.00 sec)
mysql> insert into test(name,purchased) values ('backup2','2011-11-22') ;
Query OK, 1 row affected (0.00 sec)
mysql> commit ;
Query OK, 0 rows affected (0.00 sec)
mysql> flush logs ;
Query OK, 0 rows affected (0.65 sec)
mysql> show master status \G
*************************** 1. row ***************************
File: binlog.000003
Position: 193
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 594d3908-49ce-11ed-b130-005056a6b799:1-20,
75477e61-49e1-11ed-b1db-005056a6b799:1-87
1 row in set (0.00 sec)
mysql> insert into test(name,purchased) values ('backup3','2011-11-22') ;
Query OK, 1 row affected (0.01 sec)
mysql> commit ;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status \G
*************************** 1. row ***************************
File: binlog.000003
Position: 471
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 594d3908-49ce-11ed-b130-005056a6b799:1-20,
75477e61-49e1-11ed-b1db-005056a6b799:1-88
1 row in set (0.00 sec)
mysql> flush logs ;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test(name,purchased) values ('backup4','2011-11-22') ;
Query OK, 1 row affected (0.01 sec)
mysql> commit ;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
[root@s2ahuoracle02 binlogs]# ll
total 72
-rw-r-----. 1 mysql mysql 27025 Nov 9 19:06 binlog.000001
-rw-r-----. 1 mysql mysql 1023 Nov 9 19:07 binlog.000002
-rw-r-----. 1 mysql mysql 1023 Nov 9 19:07 binlog.000003
-rw-r-----. 1 mysql mysql 983 Nov 9 19:07 binlog.000004
-rw-r-----. 1 mysql mysql 160 Nov 9 19:07 binlog.index
-rw-r-----. 1 mysql mysql 684 Oct 12 11:53 binlog.log.000001
-rw-r-----. 1 mysql mysql 684 Oct 12 11:53 binlog.log.000002
-rw-r-----. 1 mysql mysql 684 Oct 12 11:53 binlog.log.000003
-rw-r-----. 1 mysql mysql 665 Oct 12 11:53 binlog.log.000004
-rw-r-----. 1 mysql mysql 176 Oct 12 11:53 binlog.log.index
drwxr-xr-x. 3 mysql mysql 4096 Oct 12 11:53 innodb
3.停库
[root@s2ahuoracle02 run]# /u01/mysql8e/mysql/bin/mysqladmin shutdown -uroot -p --socket=/u01/mysql8e/data/run/mysql3333.sock
Enter password:
4.清理原目录
这里通过重命名的方式
[root@s2ahuoracle02 binlogs]# cd ..
[root@s2ahuoracle02 data]# mv binlogs binlogs_bak
[root@s2ahuoracle02 data]# mv data data_bak
[root@s2ahuoracle02 data]# mkdir data
[root@s2ahuoracle02 data]# mkdir binlogs
[root@s2ahuoracle02 data]# cd binlogs
[root@s2ahuoracle02 binlogs]# mkdir innodb
[root@s2ahuoracle02 binlogs]# chown -R mysql.mysql /u01/mysql8e/
[root@s2ahuoracle02 binlogs]# ll
total 4
drwxr-xr-x. 2 mysql mysql 4096 Nov 9 19:10 innodb
5.恢复全库
[root@s2ahuoracle02 binlogs]# cd ..
[root@s2ahuoracle02 data]# cd run/
[root@s2ahuoracle02 run]# rm -rf /tmp/backups/temp
/u01/mysql8e/mysql/bin/mysqlbackup --defaults-file=/u01/mysql8e/data/my3333.cnf --encrypt-password=MysqlBackup.2022 --datadir=/u01/mysql8e/data/data --backup-image=/tmp/backups/fullbackup_`date +%Y%m%d`.mbi --backup-dir=/tmp/backups/temp copy-back-and-apply-log
MySQL Enterprise Backup Ver 8.0.30-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2022, 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 ...
/u01/mysql8e/mysql/bin/mysqlbackup
--defaults-file=/u01/mysql8e/data/my3333.cnf
--encrypt-password=xxxxxxxxxxxxxxxx
--datadir=/u01/mysql8e/data/data
--backup-image=/tmp/backups/fullbackup_20221109.mbi
--backup-dir=/tmp/backups/temp
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!".
221109 19:12:21 MAIN INFO: Backup Image MEB version string: 8.0.30
221109 19:12:21 MAIN INFO: MySQL server version is '8.0.30'
221109 19:12:21 MAIN INFO: Backup directory created: '/tmp/backups/temp'
MAIN: Can't read dir of '/u01/mysql8e/data/binlogs/innodb/#innodb_redo' (OS errno 2 - No such file or directory)
MAIN: Can't read dir of '/u01/mysql8e/data/binlogs/innodb/#innodb_redo' (OS errno 2 - No such file or directory)
221109 19:12:21 MAIN INFO: MEB logfile created at /tmp/backups/temp/meta/MEB_2022-11-09.19-12-21_copy-back-and-apply-log.log
221109 19:12:21 MAIN INFO: The backup image has a keyring encrypted file.
221109 19:12:21 MAIN INFO: The backup server used an unencrypted keyring file.
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /u01/mysql8e/data/data
innodb_data_home_dir = /u01/mysql8e/data/data
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_group_home_dir = /u01/mysql8e/data/binlogs/innodb
innodb_undo_directory = /u01/mysql8e/data/data
innodb_undo_tablespaces = 2
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = Null
innodb_checksum_algorithm = crc32
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /tmp/backups/temp/datadir
innodb_data_home_dir = /tmp/backups/temp/datadir
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_group_home_dir = /tmp/backups/temp/datadir
innodb_undo_directory = /tmp/backups/temp/datadir
innodb_undo_tablespaces = 2
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
221109 19:12:21 MAIN INFO: Creating 14 buffers each of size 16777216.
221109 19:12:21 MAIN INFO: Copy-back-and-apply-log from image operation starts with following threads
1 read-threads 6 process-threads 1 write-threads
221109 19:12:21 PCR3 INFO: Copying database directory: meta
221109 19:12:21 RDR1 INFO: Initialized keyring with 8 keys.
221109 19:12:21 RDR1 INFO: Copying ibdata1.
RDR1 Progress in MB: 200 400 600 800 1000
221109 19:12:39 RDR1 INFO: Copying undo_002.
221109 19:12:39 RDR1 INFO: Copying undo_001.
221109 19:12:40 RDR1 INFO: Copying sys/sys_config.ibd.
221109 19:12:40 RDR1 INFO: Copying mysql/backup_progress.ibd.
221109 19:12:40 RDR1 INFO: Copying mysql/backup_history.ibd.
221109 19:12:40 RDR1 INFO: Copying mysql/audit_log_filter.ibd.
221109 19:12:40 RDR1 INFO: Copying mysql/audit_log_user.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/tr_bak#p#p0.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/tr_bak#p#p1.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/tr_bak#p#p2.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/tr_bak#p#p3.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/tr_bak#p#p4.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/tr_bak#p#p5.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/tr#p#p0.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/tr#p#p1.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/tr#p#p2.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/tr#p#p3.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/tr#p#p4.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/tr#p#p5.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/test_bak#p#p0.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/test_bak#p#p1.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/test_bak#p#p2.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/test_bak#p#p3.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/test_bak#p#p4.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/test_bak#p#p5.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/test#p#p0.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/test#p#p1.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/test#p#p2.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/test#p#p3.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/test#p#p4.ibd.
221109 19:12:40 RDR1 INFO: Copying pardb/test#p#p5.ibd.
221109 19:12:40 RDR1 INFO: Copying mysql.ibd.
221109 19:12:41 PCR1 INFO: Copying database directory: mysql
221109 19:12:41 PCR1 INFO: Copying database directory: mysql-keyring
221109 19:12:41 PCR1 INFO: Copying database directory: pardb
221109 19:12:41 PCR1 INFO: Copying database directory: performance_schema
221109 19:12:41 PCR5 INFO: Copying database directory: sys
221109 19:12:41 RDR1 INFO: log location /u01/mysql8e/data/binlogs/binlog.log
221109 19:12:41 RDR1 INFO: relay log location /u01/mysql8e/data/binlogs/relay.log
221109 19:12:41 RDR1 INFO: Copying binlog.000001.
221109 19:12:41 MAIN INFO: read_backup_variables_txt_file: '/tmp/backups/temp/meta/backup_variables.txt'
221109 19:12:41 MAIN INFO: backup variable mysql_version=8.0.30-commercial
221109 19:12:41 MAIN INFO: MySQL server version is '8.0.30-commercial'
221109 19:12:41 MAIN INFO: Restoring ...8.0.30-commercial version
221109 19:12:41 MAIN INFO: backup variable meb_version=8.0.30
221109 19:12:41 MAIN INFO: backup variable start_lsn=21084672
221109 19:12:41 MAIN INFO: backup variable last_checkpoint=21089861
221109 19:12:41 MAIN INFO: backup variable end_lsn=21090956
221109 19:12:41 MAIN INFO: backup variable apply_log_done=0
221109 19:12:41 MAIN INFO: backup variable is_incremental=0
221109 19:12:41 MAIN INFO: backup variable is_incremental_with_redo_log_only=0
221109 19:12:41 MAIN INFO: backup variable is_partial=0
221109 19:12:41 MAIN INFO: backup variable is_compressed=0
221109 19:12:41 MAIN INFO: backup variable is_skip_binlog=0
221109 19:12:41 MAIN INFO: backup variable is_skip_relaylog=0
221109 19:12:41 MAIN INFO: backup variable is_skip_unused_pages=0
221109 19:12:41 MAIN INFO: backup variable is_onlyinnodb=0
221109 19:12:41 MAIN INFO: backup variable binlog_position=binlog.000001:26195
221109 19:12:41 MAIN INFO: backup variable binlog_index=binlog.index
221109 19:12:41 MAIN INFO: backup variable binlog_master_key_seq_num=1
221109 19:12:41 MAIN INFO: backup variable gtid_executed=594d3908-49ce-11ed-b130-005056a6b799:1-20,75477e61-49e1-11ed-b1db-005056a6b799:1-85
221109 19:12:41 MAIN INFO: backup variable has_tde_tables=1
221109 19:12:41 MAIN INFO: backup variable has_encrypted_binlogs=1
221109 19:12:41 MAIN INFO: backup variable has_external_plugins=1
221109 19:12:41 MAIN INFO: backup variable start_time_utc=1667991857421560
221109 19:12:41 MAIN INFO: backup variable end_time_utc=1667991877441543
221109 19:12:41 MAIN INFO: backup variable consistency_time_utc=1667991877417584
221109 19:12:41 MAIN INFO: backup variable kuf_file_name=keyring_kuf
221109 19:12:41 MAIN INFO: backup variable mysql_version_comment=MySQL Enterprise Server - Commercial
221109 19:12:41 MAIN INFO: backup variable log_bin_name=binlog
221109 19:12:41 MAIN INFO: backup variable log_bin_path=/u01/mysql8e/data/binlogs
221109 19:12:41 MAIN INFO: backup variable redo_log_encryption_info=6c43430000000135393464333930382d343963652d313165642d623133302d3030353035366136623739396f0c14d096cbf92a381f4d914a16e2da1e36a37acb012a5d8c0650ef046664803e44102fa1c6b22e0a1ef1eb0404418cf61dd95fa42431e9bf5539a97787e261b7e51c8c00000000
221109 19:12:41 MAIN INFO: backup variable log_bin_index_name=binlog
221109 19:12:41 MAIN INFO: backup variable log_bin_index_path=/u01/mysql8e/data/binlogs
221109 19:12:41 MAIN INFO: backup variable innodb_undo_files_count=2
221109 19:12:41 MAIN WARNING: External plugins list found in meta/backup_content.xml. Please ensure that all plugins are installed in restored server.
221109 19:12:41 MAIN INFO: Copy-back operation completed successfully.
221109 19:12:41 MAIN INFO: Source Image Path = /tmp/backups/fullbackup_20221109.mbi
221109 19:12:41 MAIN INFO: read_backup_variables_txt_file: '/tmp/backups/temp/meta/backup_variables.txt'
221109 19:12:41 MAIN INFO: backup variable mysql_version=8.0.30-commercial
221109 19:12:41 MAIN INFO: MySQL server version is '8.0.30-commercial'
221109 19:12:41 MAIN INFO: Restoring ...8.0.30-commercial version
221109 19:12:41 MAIN INFO: backup variable meb_version=8.0.30
221109 19:12:41 MAIN INFO: backup variable start_lsn=21084672
221109 19:12:41 MAIN INFO: backup variable last_checkpoint=21089861
221109 19:12:41 MAIN INFO: backup variable end_lsn=21090956
221109 19:12:41 MAIN INFO: backup variable apply_log_done=0
221109 19:12:41 MAIN INFO: backup variable is_incremental=0
221109 19:12:41 MAIN INFO: backup variable is_incremental_with_redo_log_only=0
221109 19:12:41 MAIN INFO: backup variable is_partial=0
221109 19:12:41 MAIN INFO: backup variable is_compressed=0
221109 19:12:41 MAIN INFO: backup variable is_skip_binlog=0
221109 19:12:41 MAIN INFO: backup variable is_skip_relaylog=0
221109 19:12:41 MAIN INFO: backup variable is_skip_unused_pages=0
221109 19:12:41 MAIN INFO: backup variable is_onlyinnodb=0
221109 19:12:41 MAIN INFO: backup variable binlog_position=binlog.000001:26195
221109 19:12:41 MAIN INFO: backup variable binlog_index=binlog.index
221109 19:12:41 MAIN INFO: backup variable binlog_master_key_seq_num=1
221109 19:12:41 MAIN INFO: backup variable gtid_executed=594d3908-49ce-11ed-b130-005056a6b799:1-20,75477e61-49e1-11ed-b1db-005056a6b799:1-85
221109 19:12:41 MAIN INFO: backup variable has_tde_tables=1
221109 19:12:41 MAIN INFO: backup variable has_encrypted_binlogs=1
221109 19:12:41 MAIN INFO: backup variable has_external_plugins=1
221109 19:12:41 MAIN INFO: backup variable start_time_utc=1667991857421560
221109 19:12:41 MAIN INFO: backup variable end_time_utc=1667991877441543
221109 19:12:41 MAIN INFO: backup variable consistency_time_utc=1667991877417584
221109 19:12:41 MAIN INFO: backup variable kuf_file_name=keyring_kuf
221109 19:12:41 MAIN INFO: backup variable mysql_version_comment=MySQL Enterprise Server - Commercial
221109 19:12:41 MAIN INFO: backup variable log_bin_name=binlog
221109 19:12:41 MAIN INFO: backup variable log_bin_path=/u01/mysql8e/data/binlogs
221109 19:12:41 MAIN INFO: backup variable redo_log_encryption_info=6c43430000000135393464333930382d343963652d313165642d623133302d3030353035366136623739396f0c14d096cbf92a381f4d914a16e2da1e36a37acb012a5d8c0650ef046664803e44102fa1c6b22e0a1ef1eb0404418cf61dd95fa42431e9bf5539a97787e261b7e51c8c00000000
221109 19:12:41 MAIN INFO: backup variable log_bin_index_name=binlog
221109 19:12:41 MAIN INFO: backup variable log_bin_index_path=/u01/mysql8e/data/binlogs
221109 19:12:41 MAIN INFO: backup variable innodb_undo_files_count=2
221109 19:12:41 MAIN INFO: Creating 14 buffers each of size 65536.
221109 19:12:41 MAIN INFO: Apply-log operation starts with following threads
1 read-threads 1 process-threads 6 apply-threads
221109 19:12:41 MAIN INFO: Using up to 100 MB of memory.
221109 19:12:41 MAIN INFO: Read redo log encryption metadata successful..
221109 19:12:41 MAIN INFO: ibbackup_logfile's creation parameters:
start lsn 21084672, end lsn 21090956,
start checkpoint 21084993.
221109 19:12:41 MAIN INFO: Loading the space id : 0, space name : /u01/mysql8e/data/data/ibdata1.
221109 19:12:41 MAIN INFO: Loading the space id 4 name '/u01/mysql8e/data/data/mysql/audit_log_filter.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 5 name '/u01/mysql8e/data/data/mysql/audit_log_user.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 3 name '/u01/mysql8e/data/data/mysql/backup_history.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 2 name '/u01/mysql8e/data/data/mysql/backup_progress.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 48 name '/u01/mysql8e/data/data/pardb/test#p#p0.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 49 name '/u01/mysql8e/data/data/pardb/test#p#p1.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 50 name '/u01/mysql8e/data/data/pardb/test#p#p2.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 51 name '/u01/mysql8e/data/data/pardb/test#p#p3.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 52 name '/u01/mysql8e/data/data/pardb/test#p#p4.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 53 name '/u01/mysql8e/data/data/pardb/test#p#p5.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 42 name '/u01/mysql8e/data/data/pardb/test_bak#p#p0.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 43 name '/u01/mysql8e/data/data/pardb/test_bak#p#p1.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 44 name '/u01/mysql8e/data/data/pardb/test_bak#p#p2.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 45 name '/u01/mysql8e/data/data/pardb/test_bak#p#p3.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 46 name '/u01/mysql8e/data/data/pardb/test_bak#p#p4.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 47 name '/u01/mysql8e/data/data/pardb/test_bak#p#p5.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 12 name '/u01/mysql8e/data/data/pardb/tr#p#p0.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 13 name '/u01/mysql8e/data/data/pardb/tr#p#p1.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 14 name '/u01/mysql8e/data/data/pardb/tr#p#p2.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 15 name '/u01/mysql8e/data/data/pardb/tr#p#p3.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 16 name '/u01/mysql8e/data/data/pardb/tr#p#p4.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 17 name '/u01/mysql8e/data/data/pardb/tr#p#p5.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 6 name '/u01/mysql8e/data/data/pardb/tr_bak#p#p0.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 7 name '/u01/mysql8e/data/data/pardb/tr_bak#p#p1.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 8 name '/u01/mysql8e/data/data/pardb/tr_bak#p#p2.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 9 name '/u01/mysql8e/data/data/pardb/tr_bak#p#p3.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 10 name '/u01/mysql8e/data/data/pardb/tr_bak#p#p4.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 11 name '/u01/mysql8e/data/data/pardb/tr_bak#p#p5.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 1 name '/u01/mysql8e/data/data/sys/sys_config.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 4294967294 name '/u01/mysql8e/data/data/mysql.ibd'.
221109 19:12:41 MAIN INFO: Loading the space id 4294967279 name '/u01/mysql8e/data/data/undo_001'.
221109 19:12:41 MAIN INFO: Loading the space id 4294967278 name '/u01/mysql8e/data/data/undo_002'.
221109 19:12:41 PCR1 INFO: Starting to parse redo log at lsn = 21084690, whereas checkpoint_lsn = 21084993 and start_lsn = 21084672.
221109 19:12:41 PCR1 INFO: Doing recovery: scanned up to log sequence number 21090956.
221109 19:12:41 PCR1 INFO: Starting to apply a batch of log records to the database....
InnoDB: Progress in percent: 6 13 20 26 33
221109 19:12:44 PCR1 INFO: Create redo log files. target start_lsn 0 last_checkpoint 0 end_lsn 0
221109 19:12:44 PCR1 INFO: Create redo log files. source start_lsn 21084672 last_checkpoint 21089861 end_lsn 21090956
221109 19:12:44 PCR1 INFO: Updating last checkpoint to 21089861 in redo log
221109 19:12:44 PCR1 INFO: We were able to parse ibbackup_logfile up to lsn 21090956.
221109 19:12:44 PCR1 INFO: Last MySQL binlog file position 0 26195, file name binlog.000001
221109 19:12:44 PCR1 INFO: The first data file is '/u01/mysql8e/data/data/ibdata1'
and the new created log files are at '/u01/mysql8e/data/binlogs/innodb'
221109 19:12:44 MAIN INFO: The keyring file is restored at '/u01/mysql8e/data/data/keyring_kuf'.
221109 19:12:44 MAIN INFO: Apply-log operation completed successfully.
221109 19:12:44 MAIN INFO: Full Backup has been restored successfully.
mysqlbackup completed OK! with 1 warnings
[root@s2ahuoracle02 run]#
6.查看恢复的变量文件
[root@s2ahuoracle02 data]# cat backup_variables.txt
#
# This file is auto generated by mysqlbackup.
#
[backup_variables]
apply_log_done=1
binlog_index=/u01/mysql8e/data/binlogs/binlog.log.index
binlog_master_key_seq_num=1
binlog_position=binlog.000001:26195
consistency_time_utc=1667991877417584
end_lsn=21090956
end_time_utc=1667991877441543
gtid_executed=594d3908-49ce-11ed-b130-005056a6b799:1-20,75477e61-49e1-11ed-b1db-005056a6b799:1-85
has_encrypted_binlogs=1
has_external_plugins=1
has_tde_tables=1
innodb_undo_files_count=2
is_compressed=0
is_incremental=0
is_incremental_with_redo_log_only=0
is_onlyinnodb=0
is_partial=0
is_skip_binlog=0
is_skip_relaylog=0
is_skip_unused_pages=0
kuf_file_name=keyring_kuf
last_checkpoint=21089861
log_bin_index_name=binlog.log
log_bin_index_path=/u01/mysql8e/data/binlogs
log_bin_name=binlog.log
log_bin_path=/u01/mysql8e/data/binlogs
meb_version=8.0.30
mysql_version=8.0.30-commercial
mysql_version_comment=MySQL Enterprise Server - Commercial
redo_log_encryption_info=6c43430000000135393464333930382d343963652d313165642d623133302d3030353035366136623739396f0c14d096cbf92a381f4d914a16e2da1e36a37acb012a5d8c0650ef046664803e44102fa1c6b22e0a1ef1eb0404418cf61dd95fa42431e9bf5539a97787e261b7e51c8c00000000
start_lsn=21084672
start_time_utc=1667991857421560
7启动数据库
[root@s2ahuoracle02 run]# chown -R mysql.mysql /u01/mysql8e/data
[root@s2ahuoracle02 run]# /u01/mysql8e/mysql/bin/mysqld_safe --defaults-file=/u01/mysql8e/data/my3333.cnf &
mysql> use pardb ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from test ;
+----+----------------+------------+
| id | name | purchased |
+----+----------------+------------+
| 1 | bookcase | 1989-01-10 |
| 2 | sofa | 1987-06-05 |
| 3 | aquarium | 1992-08-04 |
| 4 | alarm clock | 1997-11-05 |
| 5 | lava lamp | 1998-12-25 |
| 6 | desk organiser | 2003-10-15 |
| 7 | chair | 2009-03-10 |
| 8 | study desk | 2006-09-16 |
| 9 | exercise bike | 2014-05-09 |
| 10 | espresso maker | 2011-11-22 |
+----+----------------+------------+
10 rows in set (0.00 sec)
8.指定binglog 应用恢复
8.1变更 binlog.index 指向所要恢复的文件
[root@s2ahuoracle02 binlogs]# cat binlog.index
/u01/mysql8e/data/binlogs_bak/binlog.000001
/u01/mysql8e/data/binlogs_bak/binlog.000002
/u01/mysql8e/data/binlogs_bak/binlog.000003
/u01/mysql8e/data/binlogs_bak/binlog.000004
8.2 需要手动flush logs 一次,将需要恢复的binglog 注册到DB
mysql> flush logs ;
Query OK, 0 rows affected (0.03 sec)
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 27025 | Yes |
| binlog.000002 | 1023 | Yes |
| binlog.000003 | 1023 | Yes |
| binlog.000004 | 705 | Yes |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)
8.3解析binlog
cd /u01/mysql8e/data/binlogs_bak
#指定post位置
mysqlbinlog 26195 --start-position=26195 --read-from-remote-server --host=192.168.1.54 --port=3333 --user=root --password=root --ssl-mode=required binlog.000001 binlog.000002 binlog.000003 binlog.000004 > binlog_post_26195.sql
8.4恢复binlog 数据，并验证
mysql --host=192.168.1.54 --port=3333 --user=root --password=root < binlog_post_26195.sql
mysql> select * from test ;
+----+----------------+------------+
| id | name | purchased |
+----+----------------+------------+
| 1 | bookcase | 1989-01-10 |
| 2 | sofa | 1987-06-05 |
| 3 | aquarium | 1992-08-04 |
| 4 | alarm clock | 1997-11-05 |
| 5 | lava lamp | 1998-12-25 |
| 6 | desk organiser | 2003-10-15 |
| 7 | chair | 2009-03-10 |
| 8 | study desk | 2006-09-16 |
| 9 | exercise bike | 2014-05-09 |
| 10 | espresso maker | 2011-11-22 |
| 11 | backup1 | 2011-11-22 |
| 12 | backup2 | 2011-11-22 |
| 13 | backup3 | 2011-11-22 |
| 14 | backup4 | 2011-11-22 |
+----+----------------+------------+
14 rows in set (0.00 sec)
# 参考文档
