4.2.4 恢复数据库

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

在下面的示例中,使用命令恢复 在第 4.2.2 节“备份整个 MySQL 实例”中 给出的示例中创建的单文件备份。copy-back-and-apply-log除了常用的连接参数外,还使用了以下选项:

  • --defaults-file提供恢复数据的配置。 如果曾经使用过,它必须是出现在mysqlbackup命令中的第一个选项。在大多数情况下,您可以使用此选项向 mysqlbackup提供要将数据恢复到的目标服务器的配置文件。但是,当备份的以下 InnoDB 设置与目标服务器上的设置不同时,重要的是在还原期间将备份值提供给mysqlbackup并提供给mysqld当您启动已还原的服务器时(否则,还原可能会失败,并且您可能无法启动已还原的服务器):

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

    对于上面列出的某些选项(即 、 innodb_data_file_pathinnodb_log_file_sizeinnodb_log_files_in_groupinnodb_undo_tablespacesmysqlbackup检查您为它们提供的值,以确保您之后能够使用这些值启动目标服务器:如果其中任何一个提供,它会抛出错误与备份的实际值不匹配。如果未在配置文件或命令行中 为mysqlbackup指定这些值(下例中就是这种情况),则会给出警告。

  • --datadir提供用于恢复数据的数据目录的位置。您必须为任何还原操作指定此选项。

  • --backup-image提供单文件备份的路径。

  • --backup-dir提供一个空文件夹的位置来存储在恢复过程中创建的一些临时文件。

$ ./mysqlbackup  --defaults-file=/etc/mysql/my.cnf --datadir=/var/lib/mysql  \
   --backup-image=/home/admin/backups/my.mbi --backup-dir=/home/admin/restore-tmp copy-back-and-apply-log
MySQL Enterprise Backup version 4.1.0 Linux-2.6.39-400.215.10.el5uek-x86_64 [2017/02/13] 
Copyright (c) 2003, 2017, Oracle and/or its affiliates. All Rights Reserved.

170214 22:11:38 MAIN    INFO: A thread created with Id '140027243398976' 
170214 22:11:38 MAIN    INFO: Starting with following command line ...
 ./mysqlbackup --defaults-file=/etc/mysql/my.cnf --datadir=/var/lib/mysql 
        --backup-image=/home/admin/backups/my.mbi 
        --backup-dir=/home/admin/restore-tmp copy-back-and-apply-log 

170214 22:11:38 MAIN    INFO: 
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!".

170214 22:11:38 MAIN    INFO: Backup Image MEB version string: 4.1.0 [2017/02/13]
170214 22:11:38 MAIN    INFO: MySQL server version is '5.7.17'
170214 22:11:38 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.
170214 22:11:38 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.
170214 22:11:38 MAIN WARNING: If you restore to a server of a different version, the innodb_log_file_size parameter might have a different default. In th
at case you need to add 'innodb_log_file_size=50331648' to the target server configuration.
170214 22:11:38 MAIN    INFO: Keyring service not initialized. MEB cannot validate the encryption keys.
170214 22:11:38 MAIN    INFO: Valid keyring to be specified on server start to access the encrypted tables.
170214 22:11:38 MAIN    INFO: MEB logfile created at /home/admin/restore-tmp/meta/MEB_2017-02-14.22-11-38_copy_back_img_to_datadir.log

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir                        = /var/lib/mysql
  innodb_data_home_dir           = /var/lib/mysql
  innodb_data_file_path          = ibdata1:12M:autoextend
  innodb_log_group_home_dir      = /var/lib/mysql
  innodb_log_files_in_group      = 2
  innodb_log_file_size           = 50331648
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = Null
  innodb_checksum_algorithm      = crc32

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir                        = /home/admin/restore-tmp/datadir
  innodb_data_home_dir           = /home/admin/restore-tmp/datadir
  innodb_data_file_path          = ibdata1:12M:autoextend
  innodb_log_group_home_dir      = /home/admin/restore-tmp/datadir
  innodb_log_files_in_group      = 2
  innodb_log_file_size           = 50331648
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32

170214 22:11:38 MAIN    INFO: Creating 14 buffers each of size 16777216.
170214 22:11:38 MAIN    INFO: Copy-back-and-apply-log operation starts with following threads
		1 read-threads    6 process-threads    1 write-threads
170214 22:11:38 RDR1    INFO: A thread created with Id '140026981721856' 
170214 22:11:38 PCR1    INFO: A thread created with Id '140026973329152' 
170214 22:11:38 PCR1    INFO: Copying database directory: meta
170214 22:11:38 PCR2    INFO: A thread created with Id '140026964936448' 
170214 22:11:38 PCR3    INFO: A thread created with Id '140026956543744' 
170214 22:11:38 PCR4    INFO: A thread created with Id '140026948151040' 
170214 22:11:38 PCR5    INFO: A thread created with Id '140026939758336' 
170214 22:11:38 PCR6    INFO: A thread created with Id '140026931365632' 
170214 22:11:38 WTR1    INFO: A thread created with Id '140026922972928' 
170214 22:11:38 RDR1    INFO: Copying ibdata1.
170214 22:11:38 RDR1    INFO: Copying mysql/engine_cost.ibd.
170214 22:11:38 RDR1    INFO: Copying mysql/gtid_executed.ibd.
170214 22:11:38 RDR1    INFO: Copying mysql/help_category.ibd.
170214 22:11:38 RDR1    INFO: Copying mysql/help_keyword.ibd.
170214 22:11:38 RDR1    INFO: Copying mysql/help_relation.ibd.
170214 22:11:38 RDR1    INFO: Copying mysql/help_topic.ibd.
170214 22:11:38 PCR2    INFO: Copying database directory: mysql
170214 22:11:39 RDR1    INFO: Copying mysql/innodb_index_stats.ibd.
170214 22:11:39 RDR1    INFO: Copying mysql/innodb_table_stats.ibd.
170214 22:11:39 RDR1    INFO: Copying mysql/plugin.ibd.
170214 22:11:39 RDR1    INFO: Copying mysql/server_cost.ibd.
170214 22:11:39 RDR1    INFO: Copying mysql/servers.ibd.
170214 22:11:39 RDR1    INFO: Copying mysql/slave_master_info.ibd.
170214 22:11:39 RDR1    INFO: Copying mysql/slave_relay_log_info.ibd.
170214 22:11:39 RDR1    INFO: Copying mysql/slave_worker_info.ibd.
170214 22:11:39 RDR1    INFO: Copying mysql/time_zone.ibd.
170214 22:11:39 RDR1    INFO: Copying mysql/time_zone_leap_second.ibd.
170214 22:11:39 RDR1    INFO: Copying mysql/time_zone_name.ibd.
170214 22:11:39 RDR1    INFO: Copying mysql/time_zone_transition.ibd.
170214 22:11:39 RDR1    INFO: Copying mysql/time_zone_transition_type.ibd.
170214 22:11:40 PCR5    INFO: Copying database directory: performance_schema
170214 22:11:40 PCR5    INFO: Copying database directory: pets
170214 22:11:40 RDR1    INFO: Copying pets/cats.ibd.
170214 22:11:40 PCR5    INFO: Copying database directory: sys
170214 22:11:40 RDR1    INFO: Copying sys/sys_config.ibd.
170214 22:11:40 PCR5    INFO: Copying database directory: mysql
170214 22:11:40 PCR5    INFO: Copying database directory: performance_schema
170214 22:11:40 PCR5    INFO: Copying database directory: pets
170214 22:11:40 PCR5    INFO: Copying database directory: sys
170214 22:11:40 MAIN    INFO: Total files as specified in image: 297
170214 22:11:40 MAIN    INFO: MySQL server version is '5.7.17'
170214 22:11:40 MAIN    INFO: MySQL server compile os version is 'Linux'
170214 22:11:40 MAIN    INFO: Writing config file for server '5.7.17'.
170214 22:11:40 MAIN    INFO: Creating server config files server-my.cnf and server-all.cnf in /var/lib/mysql

[Some lines of output not shown]

170214 22:11:40 MAIN    INFO: Copy-back operation completed successfully.
170214 22:11:40 MAIN    INFO: Source Image Path = /home/admin/backups/my.mbi


170214 22:11:40 MAIN    INFO: MySQL server version is '5.7.17'
170214 22:11:40 MAIN    INFO: Restoring ...5.7.17 version
170214 22:11:40 MAIN    INFO: Creating 14 buffers each of size 65536.
170214 22:11:40 MAIN    INFO: Apply-log operation starts with following threads
		1 read-threads    1 process-threads    6 apply-threads
170214 22:11:40 MAIN    INFO: Using up to 100 MB of memory.
170214 22:11:40 MAIN    INFO: ibbackup_logfile's creation parameters:
          start lsn 2543104, end lsn 2543132,
          start checkpoint 2543123.
170214 22:11:40 ALW1    INFO: A thread created with Id '140026922972928' 
170214 22:11:40 ALW6    INFO: A thread created with Id '140027204466432' 
170214 22:11:40 ALW5    INFO: A thread created with Id '140027212859136' 
170214 22:11:40 ALW4    INFO: A thread created with Id '140026948151040' 
170214 22:11:40 ALW3    INFO: A thread created with Id '140026939758336' 
170214 22:11:40 ALW2    INFO: A thread created with Id '140026931365632' 
170214 22:11:40 RDR1    INFO: A thread created with Id '140027196073728' 
170214 22:11:40 PCR1    INFO: A thread created with Id '140027187681024' 
170214 22:11:40 PCR1    INFO: InnoDB: Doing recovery: scanned up to log sequence number 2543132.
170214 22:11:40 PCR1    INFO: InnoDB: Starting an apply batch of log records to the database...
.
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 
 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 
 94 95 96 97 98 99 
170214 22:11:40 PCR1    INFO: InnoDB: Setting log file size to 50331648.
170214 22:11:45 PCR1    INFO: InnoDB: Setting log file size to 50331648.
170214 22:11:50 PCR1    INFO: We were able to parse ibbackup_logfile up to
          lsn 2543132.
170214 22:11:50 PCR1    INFO: The first data file is '/var/lib/mysql/ibdata1'
                              and the new created log files are at '/var/lib/mysql'
170214 22:11:50 MAIN    INFO: MySQL server version is '5.7.17'
170214 22:11:50 MAIN    INFO: Restoring ...5.7.17 version
170214 22:11:50 MAIN    INFO: Apply-log operation completed successfully.
170214 22:11:50 MAIN    INFO: Full Backup has been restored successfully.

mysqlbackup completed OK! with 3 warnings

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

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

您现在已准备好启动恢复的数据库服务器。有关如何执行不同类型的恢复的更多讨论,请参阅第 5.1 节,“执行恢复操作”