4.2.4 恢复数据库_MySQL 企业备份用户指南（版本 8.0.31）

MySQL 企业备份用户指南（版本 8.0.31） / 第二部分使用 MySQL 企业备份 / 第 4 章备份数据库服务器 / 4.2 典型的备份/验证/恢复周期 / 4.2.4 恢复数据库

4.2.4 恢复数据库

要将 MySQL 实例从备份恢复到数据库服务器：

关闭数据库服务器。
删除服务器数据目录中的所有文件。如果目录与数据目录不同，还删除由、和选项 --innodb_data_home_dir指定 --innodb_log_group_home_dir的目录内的所有文件以进行恢复。--innodb_undo_directory
例如，使用 copy-back-and-apply-log命令，通过将原始备份更新为一致状态，将原始备份转换为准备好的备份，然后将表、索引、元数据和任何其他所需文件复制到目标服务器上。对于您可以为此操作指定的各种选项，请参阅第 19.3 节，“恢复操作”。

在下面的示例中，使用命令恢复在第 4.2.2 节“备份整个 MySQL 实例”中给出的示例中创建的单文件备份。copy-back-and-apply-log使用以下选项：

--datadir提供用于恢复数据的数据目录的位置。您必须在命令行或默认文件中为任何恢复操作指定此选项。
--backup-image提供单文件备份的路径。
--backup-dir提供一个空文件夹的位置来存储在恢复过程中创建的一些临时文件。

$ mysqlbackup --datadir=/home/meb/mysql/mysql-datadir \
  --backup-image=/home/meb/mysql/backups/testback3.mbi --backup-dir=/home/meb/mysql/backup-tmp2 \
    copy-back-and-apply-log
MySQL Enterprise Backup  Ver 8.0.31-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 ...
./bin/mysqlbackup
--datadir=/home/meb/mysql/mysql-datadir
--backup-image=/home/meb/mysql/backups/testback3.mbi
--backup-dir=/home/meb/mysql/backup-tmp2
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!".

220913 15:02:34 MAIN     INFO: Backup Image MEB version string: 8.0.31
220913 15:02:34 MAIN     INFO: MySQL server version is '8.0.31'
220913 15:02:34 MAIN     INFO: Backup directory created: '/home/meb/mysql/backup-tmp2'
220913 15:02:34 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.
220913 15:02:34 MAIN     INFO: MEB logfile created at /home/meb/mysql/backup-tmp2/meta/MEB_2022-09-13.15-02-34_copy-back-and-apply-log.log

220913 15:02:34 MAIN     INFO: The backup image has no keyring.
--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir                        = /home/meb/mysql/mysql-datadir
  innodb_data_home_dir           = /home/meb/mysql/mysql-datadir
  innodb_data_file_path          = ibdata1:12M:autoextend
  innodb_log_group_home_dir      = /home/meb/mysql/mysql-datadir
  innodb_undo_directory          = /home/meb/mysql/mysql-datadir
  innodb_undo_tablespaces        = 2
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = Null
  innodb_checksum_algorithm      = crc32

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir                        = /home/meb/mysql/backup-tmp2/datadir
  innodb_data_home_dir           = /home/meb/mysql/backup-tmp2/datadir
  innodb_data_file_path          = ibdata1:12M:autoextend
  innodb_log_group_home_dir      = /home/meb/mysql/backup-tmp2/datadir
  innodb_undo_directory          = /home/meb/mysql/backup-tmp2/datadir
  innodb_undo_tablespaces        = 2
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32

220913 15:02:34 MAIN     INFO: Creating 14 buffers each of size 16777216.
220913 15:02:34 MAIN     INFO: Copy-back-and-apply-log from image operation starts with following threads
                               1 read-threads    6 process-threads    1 write-threads
220913 15:02:34 PCR1     INFO: Copying database directory: meta
220913 15:02:34 RDR1     INFO: Copying ibdata1.
220913 15:02:34 RDR1     INFO: Copying undo_002.
220913 15:02:34 RDR1     INFO: Copying undo_001.
220913 15:02:34 RDR1     INFO: Copying sys/sys_config.ibd.
220913 15:02:34 RDR1     INFO: Copying Test/test1.ibd.
220913 15:02:34 RDR1     INFO: Copying mysql/backup_progress.ibd.
220913 15:02:34 RDR1     INFO: Copying mysql/backup_history.ibd.
220913 15:02:34 RDR1     INFO: Copying mysql.ibd.
220913 15:02:34 RDR1     INFO: Copying binlog.000001.
220913 15:02:34 PCR2     INFO: Copying database directory: Test
220913 15:02:34 PCR2     INFO: Copying database directory: mysql
220913 15:02:34 PCR2     INFO: Copying database directory: performance_schema
220913 15:02:34 RDR1     INFO: Binary Log Basename: 'binlog'
220913 15:02:34 RDR1     INFO: Binlog Log Index:    '/home/meb/mysql/mysql-datadir/binlog.index'
220913 15:02:34 RDR1     INFO: Copying binlog.000002.
220913 15:02:34 PCR3     INFO: Copying database directory: sys
220913 15:02:34 MAIN     INFO: read_backup_variables_txt_file: '/home/meb/mysql/backup-tmp2/meta/backup_variables.txt'
220913 15:02:34 MAIN     INFO: backup variable mysql_version=8.0.31-commercial
220913 15:02:34 MAIN     INFO: MySQL server version is '8.0.31-commercial'
220913 15:02:34 MAIN     INFO: Restoring ...8.0.31-commercial version
220913 15:02:34 MAIN     INFO: backup variable meb_version=8.0.31
220913 15:02:34 MAIN     INFO: backup variable start_lsn=19778048
220913 15:02:34 MAIN     INFO: backup variable last_checkpoint=19778447
220913 15:02:34 MAIN     INFO: backup variable end_lsn=19785442
220913 15:02:34 MAIN     INFO: backup variable apply_log_done=0
220913 15:02:34 MAIN     INFO: backup variable is_incremental=0
220913 15:02:34 MAIN     INFO: backup variable is_incremental_with_redo_log_only=0
220913 15:02:34 MAIN     INFO: backup variable is_partial=0
220913 15:02:34 MAIN     INFO: backup variable is_compressed=0
220913 15:02:34 MAIN     INFO: backup variable is_skip_binlog=0
220913 15:02:34 MAIN     INFO: backup variable is_skip_relaylog=0
220913 15:02:34 MAIN     INFO: backup variable is_skip_unused_pages=0
220913 15:02:34 MAIN     INFO: backup variable is_onlyinnodb=0
220913 15:02:34 MAIN     INFO: backup variable binlog_position=binlog.000002:157
220913 15:02:34 MAIN     INFO: backup variable binlog_index=binlog.index
220913 15:02:34 MAIN     INFO: backup variable has_tde_tables=0
220913 15:02:34 MAIN     INFO: backup variable start_time_utc=1663094608919023
220913 15:02:34 MAIN     INFO: backup variable end_time_utc=1663094944996741
220913 15:02:34 MAIN     INFO: backup variable consistency_time_utc=1663094944976610
220913 15:02:34 MAIN     INFO: backup variable mysql_version_comment=MySQL Enterprise Server - Commercial
220913 15:02:34 MAIN     INFO: backup variable log_bin_name=binlog
220913 15:02:34 MAIN     INFO: backup variable log_bin_index_name=binlog
220913 15:02:34 MAIN     INFO: backup variable innodb_undo_files_count=2
220913 15:02:34 MAIN     INFO: Copy-back operation completed successfully.
220913 15:02:34 MAIN     INFO: Source Image Path = /home/meb/mysql/backups/testback3.mbi


220913 15:02:34 MAIN     INFO: read_backup_variables_txt_file: '/home/meb/mysql/backup-tmp2/meta/backup_variables.txt'
220913 15:02:34 MAIN     INFO: backup variable mysql_version=8.0.31-commercial
220913 15:02:34 MAIN     INFO: MySQL server version is '8.0.31-commercial'
220913 15:02:34 MAIN     INFO: Restoring ...8.0.31-commercial version
220913 15:02:34 MAIN     INFO: backup variable meb_version=8.0.31
220913 15:02:34 MAIN     INFO: backup variable start_lsn=19778048
220913 15:02:34 MAIN     INFO: backup variable last_checkpoint=19778447
220913 15:02:34 MAIN     INFO: backup variable end_lsn=19785442
220913 15:02:34 MAIN     INFO: backup variable apply_log_done=0
220913 15:02:34 MAIN     INFO: backup variable is_incremental=0
220913 15:02:34 MAIN     INFO: backup variable is_incremental_with_redo_log_only=0
220913 15:02:34 MAIN     INFO: backup variable is_partial=0
220913 15:02:34 MAIN     INFO: backup variable is_compressed=0
220913 15:02:34 MAIN     INFO: backup variable is_skip_binlog=0
220913 15:02:34 MAIN     INFO: backup variable is_skip_relaylog=0
220913 15:02:34 MAIN     INFO: backup variable is_skip_unused_pages=0
220913 15:02:34 MAIN     INFO: backup variable is_onlyinnodb=0
220913 15:02:34 MAIN     INFO: backup variable binlog_position=binlog.000002:157
220913 15:02:34 MAIN     INFO: backup variable binlog_index=binlog.index
220913 15:02:34 MAIN     INFO: backup variable has_tde_tables=0
220913 15:02:34 MAIN     INFO: backup variable start_time_utc=1663094608919023
220913 15:02:34 MAIN     INFO: backup variable end_time_utc=1663094944996741
220913 15:02:34 MAIN     INFO: backup variable consistency_time_utc=1663094944976610
220913 15:02:34 MAIN     INFO: backup variable mysql_version_comment=MySQL Enterprise Server - Commercial
220913 15:02:34 MAIN     INFO: backup variable log_bin_name=binlog
220913 15:02:34 MAIN     INFO: backup variable log_bin_index_name=binlog
220913 15:02:34 MAIN     INFO: backup variable innodb_undo_files_count=2
220913 15:02:34 MAIN     INFO: Creating 14 buffers each of size 65536.
220913 15:02:34 MAIN     INFO: Apply-log operation starts with following threads
                               1 read-threads    1 process-threads    6 apply-threads
220913 15:02:34 MAIN     INFO: Using up to 100 MB of memory.
220913 15:02:34 MAIN     INFO: ibbackup_logfile's creation parameters:
          start lsn 19778048, end lsn 19785442,
          start checkpoint 19778447.
220913 15:02:34 MAIN     INFO: Loading the space id : 0, space name : /home/meb/mysql/mysql-datadir/ibdata1.
220913 15:02:34 MAIN     INFO: Loading the space id 2 name '/home/meb/mysql/mysql-datadir/Test/test1.ibd'.
220913 15:02:34 MAIN     INFO: Loading the space id 4 name '/home/meb/mysql/mysql-datadir/mysql/backup_history.ibd'.
220913 15:02:34 MAIN     INFO: Loading the space id 3 name '/home/meb/mysql/mysql-datadir/mysql/backup_progress.ibd'.
220913 15:02:34 MAIN     INFO: Loading the space id 1 name '/home/meb/mysql/mysql-datadir/sys/sys_config.ibd'.
220913 15:02:34 MAIN     INFO: Loading the space id 4294967294 name '/home/meb/mysql/mysql-datadir/mysql.ibd'.
220913 15:02:34 MAIN     INFO: Loading the space id 4294967279 name '/home/meb/mysql/mysql-datadir/undo_001'.
220913 15:02:34 MAIN     INFO: Loading the space id 4294967278 name '/home/meb/mysql/mysql-datadir/undo_002'.
220913 15:02:34 PCR1     INFO: Starting to parse redo log at lsn = 19778077, whereas checkpoint_lsn = 19778447 and start_lsn = 19778048.
220913 15:02:34 PCR1     INFO: Doing recovery: scanned up to log sequence number 19785442.
220913 15:02:34 PCR1     INFO: Starting to apply a batch of log records to the database....
InnoDB: Progress in percent: 5 11 17 23 29 
220913 15:02:34 PCR1     INFO: Create redo log files. target start_lsn 0 last_checkpoint 0 end_lsn 0
220913 15:02:34 PCR1     INFO: Create redo log files. source start_lsn 19778048 last_checkpoint 19778447 end_lsn 19785442
220913 15:02:34 PCR1     INFO: Updating last checkpoint to 19778447 in redo log
220913 15:02:34 PCR1     INFO: We were able to parse ibbackup_logfile up to lsn 19785442.
220913 15:02:34 PCR1     INFO: Last MySQL binlog file position 0 157, file name binlog.000002
220913 15:02:34 PCR1     INFO: The first data file is '/home/meb/mysql/mysql-datadir/ibdata1'
                               and the new created log files are at '/home/meb/mysql/mysql-datadir'
220913 15:02:34 MAIN     INFO: Apply-log operation completed successfully.
220913 15:02:34 MAIN     INFO: Full Backup has been restored successfully.

mysqlbackup completed OK! with 1 warnings

现在原始数据库目录已从备份中恢复。

启动还原服务器。 当备份服务器和还原服务器上的以下 InnoDB 设置不同时，使用备份服务器的设置配置还原服务器很重要（否则，还原服务器可能无法启动）：

如果您不确定备份服务器的这些设置，它们实际上在备份期间存储在文件中 — 您可以在还原单映像备份时backup-my.cnf指定的临时目录中找到该文件，或者在--backup-dir您可以通过使用 extract命令解压缩备份映像来创建备份目录。如果这些选项的值与目标服务器上的值不同，请将它们添加到您之后用来启动目标服务器的配置文件中；或者，您也可以将它们作为命令行选项提供给mysqld。

根据您打算如何启动恢复的服务器，您可能需要调整恢复的数据目录的所有权。例如服务器要由用户启动mysql，使用如下命令将数据目录及其下的文件的owner属性改为mysql用户，group属性改为mysql组。

$ chown -R mysql:mysql /path/to/datadir