要将 MySQL 实例从备份恢复到数据库服务器:
关闭数据库服务器。
删除服务器数据目录中的所有文件。如果目录与数据目录不同,还删除由 、 和 选项
--innodb_data_home_dir
指定--innodb_log_group_home_dir
的 目录内的所有文件以 进行恢复。--innodb_undo_directory
例如,使用
copy-back-and-apply-log
命令,通过将原始备份更新为一致状态,将原始备份转换为准备好的备份,然后将表、索引、元数据和任何其他所需文件复制到目标服务器上。对于您可以为此操作指定的各种选项,请参阅 第 15.3 节,“恢复操作”。
在下面的示例中,使用命令恢复
在第 4.2.2 节“备份整个 MySQL 实例”中
给出的示例中创建的单文件备份。copy-back-and-apply-log
除了常用的连接参数外,还使用了以下选项:
--defaults-file
提供恢复数据的配置。 如果曾经使用过,它必须是出现在mysqlbackup命令中的第一个选项。在大多数情况下,您可以使用此选项向 mysqlbackup提供要将数据恢复到的目标服务器的配置文件。但是,当备份的以下 InnoDB 设置与目标服务器上的设置不同时,重要的是在还原期间将备份值提供给mysqlbackup并提供给mysqld当您启动已还原的服务器时(否则,还原可能会失败,并且您可能无法启动已还原的服务器):如果您不确定备份的这些设置,它们会在备份期间存储在文件中 — 您可以在创建单映像备份时
backup-my.cnf
指定的临时目录中找到该文件,或者在 您创建的备份目录中找到该文件--backup-dir
可以通过使用extract
命令解压备份镜像来创建。如果这些选项的值与目标服务器上的不同,请将它们添加到您提供给 mysqlbackup的配置文件中以及之后将用于启动服务器的配置文件;或者,您也可以将它们作为命令行选项提供给mysqlbackup 和mysqld。对于上面列出的某些选项(即 、
innodb_data_file_path
、innodb_log_file_size
和innodb_log_files_in_group
)innodb_undo_tablespaces
, mysqlbackup检查您为它们提供的值,以确保您之后能够使用这些值启动目标服务器:如果其中任何一个提供,它会抛出错误与备份的实际值不匹配。如果未在配置文件或命令行中 为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 节,“执行恢复操作”。