MySQL 企业备份用户指南(版本 8.0.31) / 第二部分使用 MySQL 企业备份 / 第 4 章备份数据库服务器 / 4.2 典型的备份/验证/恢复周期 /
4.2.2 备份整个MySQL实例
在下面的示例中,我们使用命令将整个 MySQL 实例备份到单个文件,该
backup-to-image
命令出现在示例命令的末尾。--user
我们使用和--host
选项指定数据库的一些连接信息
(并使用该--password
选项告诉 MySQL 服务器提示输入用户密码)。单个文件备份的位置和文件名使用该
--backup-image
选项指定,用于存储临时文件的空文件夹的位置由该--backup-dir
选项提供。
输出回显备份操作使用的所有参数,包括使用数据库连接自动检索的几个参数。此备份作业的唯一 ID 记录在 mysqlbackup在 MySQL 实例中创建的特殊表中,允许您监视长时间运行的备份并查看以前备份的信息。最后的输出部分重复了备份数据的位置,并提供了 LSN值,您在下次对 刚刚创建 的完整备份执行 增量备份时可能会用到这些值。
$ ./mysqlbackup --user=root --password --host=127.0.0.1 --backup-image=/home/admin/backups/my.mbi \
--backup-dir=/home/admin/backup-tmp backup-to-image
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 17:38:59 MAIN INFO: A thread created with Id '140286817412928'
170214 17:38:59 MAIN INFO: Starting with following command line ...
./mysqlbackup --user=root --password --host=127.0.0.1
--backup-image=/home/admin/backups/my.mbi
--backup-dir=/home/admin/backup-tmp backup-to-image
170214 17:38:59 MAIN INFO:
Enter password:
170214 17:39:07 MAIN INFO: MySQL server version is '5.7.17'
170214 17:39:07 MAIN INFO: MySQL server compile os version is 'Linux'
170214 17:39:07 MAIN INFO: Got some server configuration information from running server.
170214 17:39:07 MAIN INFO: Server system variable 'old_alter_table' was set to '0'. Setting it to '1'.
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'backup-to-image' run mysqlbackup
prints "mysqlbackup completed OK!".
170214 17:39:07 MAIN INFO: MEB logfile created at /home/admin/backup-tmp/meta/MEB_2017-02-14.17-39-07_image_backup.log
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /var/lib/mysql/
innodb_data_home_dir =
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_undo_directory = /var/lib/mysql/
innodb_undo_tablespaces = 0
innodb_undo_logs = 128
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /home/admin/backup-tmp/datadir
innodb_data_home_dir = /home/admin/backup-tmp/datadir
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /home/admin/backup-tmp/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_undo_directory = /home/admin/backup-tmp/datadir
innodb_undo_tablespaces = 0
innodb_undo_logs = 128
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
Backup Image Path = /home/admin/backups/my.mbi
170214 17:39:07 MAIN INFO: Unique generated backup id for this is 14871119474450201
170214 17:39:07 MAIN INFO: Creating 14 buffers each of size 16777216.
170214 17:39:07 MAIN INFO: Full Image Backup operation starts with following threads
1 read-threads 6 process-threads 1 write-threads
170214 17:39:07 MAIN INFO: Found checkpoint at lsn 2543123.
170214 17:39:07 MAIN INFO: Starting log scan from lsn = 2543104 at offset = 2536448 and checkpoint = 2543123 in file /var/lib/mysql/ib_logfile0.
170214 17:39:07 RDR1 INFO: A thread created with Id '140286535427840'
170214 17:39:07 RDR1 INFO: Copying meta file /home/admin/backup-tmp/backup-my.cnf.
170214 17:39:07 PCR1 INFO: A thread created with Id '140286527035136'
170214 17:39:07 PCR2 INFO: A thread created with Id '140286518642432'
170214 17:39:07 RDR1 INFO: Copying meta file /home/admin/backup-tmp/meta/backup_create.xml.
170214 17:39:07 RDR1 INFO: System tablespace file format is Antelope.
170214 17:39:07 RDR1 INFO: Starting to copy all innodb files...
170214 17:39:07 PCR3 INFO: A thread created with Id '140286510249728'
170214 17:39:07 PCR4 INFO: A thread created with Id '140286382569216'
170214 17:39:07 PCR5 INFO: A thread created with Id '140286501857024'
170214 17:39:07 PCR6 INFO: A thread created with Id '140286493464320'
170214 17:39:07 WTR1 INFO: A thread created with Id '140286485071616'
170214 17:39:07 RLR1 INFO: A thread created with Id '140286476678912'
170214 17:39:07 RLW1 INFO: A thread created with Id '140286468286208'
170214 17:39:07 RLP1 INFO: A thread created with Id '140286459893504'
170214 17:39:07 RDR1 INFO: Copying /var/lib/mysql/ibdata1 (Barracuda file format).
170214 17:39:08 RDR1 INFO: Copying /var/lib/mysql/mysql/engine_cost.ibd (Barracuda file format).
170214 17:39:08 RDR1 INFO: Copying /var/lib/mysql/mysql/gtid_executed.ibd (Barracuda file format).
170214 17:39:08 RDR1 INFO: Copying /var/lib/mysql/mysql/help_category.ibd (Barracuda file format).
170214 17:39:08 RDR1 INFO: Copying /var/lib/mysql/mysql/help_keyword.ibd (Barracuda file format).
170214 17:39:08 RDR1 INFO: Copying /var/lib/mysql/mysql/help_relation.ibd (Barracuda file format).
170214 17:39:08 RDR1 INFO: Copying /var/lib/mysql/mysql/help_topic.ibd (Barracuda file format).
170214 17:39:09 RDR1 INFO: Copying /var/lib/mysql/mysql/innodb_index_stats.ibd (Barracuda file format).
170214 17:39:10 RDR1 INFO: Copying /var/lib/mysql/mysql/innodb_table_stats.ibd (Barracuda file format).
170214 17:39:10 RDR1 INFO: Copying /var/lib/mysql/mysql/plugin.ibd (Barracuda file format).
170214 17:39:10 RDR1 INFO: Copying /var/lib/mysql/mysql/server_cost.ibd (Barracuda file format).
170214 17:39:10 RDR1 INFO: Copying /var/lib/mysql/mysql/servers.ibd (Barracuda file format).
170214 17:39:10 RDR1 INFO: Copying /var/lib/mysql/mysql/slave_master_info.ibd (Barracuda file format).
170214 17:39:10 RDR1 INFO: Copying /var/lib/mysql/mysql/slave_relay_log_info.ibd (Barracuda file format).
170214 17:39:10 RDR1 INFO: Copying /var/lib/mysql/mysql/slave_worker_info.ibd (Barracuda file format).
170214 17:39:10 RDR1 INFO: Copying /var/lib/mysql/mysql/time_zone.ibd (Barracuda file format).
170214 17:39:10 RDR1 INFO: Copying /var/lib/mysql/mysql/time_zone_leap_second.ibd (Barracuda file format).
170214 17:39:10 RDR1 INFO: Copying /var/lib/mysql/mysql/time_zone_name.ibd (Barracuda file format).
170214 17:39:10 RDR1 INFO: Copying /var/lib/mysql/mysql/time_zone_transition.ibd (Barracuda file format).
170214 17:39:10 RDR1 INFO: Copying /var/lib/mysql/mysql/time_zone_transition_type.ibd (Barracuda file format).
170214 17:39:10 RDR1 INFO: Copying /var/lib/mysql/pets/cats.ibd (Barracuda file format).
170214 17:39:10 RDR1 INFO: Copying /var/lib/mysql/sys/sys_config.ibd (Barracuda file format).
170214 17:39:10 RDR1 INFO: Completing the copy of innodb files.
170214 17:39:10 RDR1 INFO: Requesting a dump of the InnoDB buffer pool
170214 17:39:10 RDR1 INFO: Waiting for the dump of the InnoDB buffer pool to complete
170214 17:39:11 RDR1 INFO: The dump of the InnoDB buffer pool completed
170214 17:39:11 RDR1 INFO: Could not find binlog index file. binlogs will not be copied for this backup.
Point-In-Time-Recovery will not be possible.
If this is online backup then server may not have started with --log-bin.
You may specify its location with --log-bin-index option.
170214 17:39:11 RDR1 INFO: Preparing to lock tables: Connected to mysqld server.
170214 17:39:11 RDR1 INFO: Starting to lock all the tables...
170214 17:39:11 RDR1 INFO: Flushing the default MyISAM key cache.
170214 17:39:11 RDR1 INFO: All tables are locked and flushed to disk
170214 17:39:11 RDR1 INFO: Completed the copy of binlog files...
170214 17:39:11 RDR1 INFO: Opening backup source directory '/var/lib/mysql'
170214 17:39:11 RDR1 INFO: Starting to backup all non-innodb files in
subdirectories of '/var/lib/mysql'
170214 17:39:11 WTR1 INFO: Adding database directory: datadir
170214 17:39:11 WTR1 INFO: Adding database directory: datadir
170214 17:39:11 WTR1 INFO: Adding database directory: datadir
170214 17:39:11 WTR1 INFO: Adding database directory: datadir
170214 17:39:11 RDR1 INFO: Completing the copy of all non-innodb files.
170214 17:39:11 RDR1 INFO: Requesting completion of redo log copy. Rapid: 0
170214 17:39:11 RLR1 INFO: Signaled '1' to complete log copy.
170214 17:39:11 RLR1 INFO: Signaled '1' to complete log copy.
170214 17:39:11 RLR1 INFO: Signaled '1' to complete log copy.
170214 17:39:11 RLR1 INFO: Redo log reader waited = 1270.00 ms for logs to generate.
170214 17:39:11 RLW1 INFO: A copied database page was modified at 2543123.
(This is the highest lsn found on page)
Scanned log up to lsn 2543132.
Was able to parse the log up to lsn 2543132.
170214 17:39:13 RDR1 INFO: All tables unlocked
170214 17:39:13 RDR1 INFO: All MySQL tables were locked for 1.367 seconds.
170214 17:39:13 RDR1 INFO: Setting server system variable 'old_alter_table' back to '0'.
170214 17:39:13 RDR1 INFO: Reading all global variables from the server.
170214 17:39:13 RDR1 INFO: Completed reading of all global variables from the server.
170214 17:39:13 RDR1 INFO: Writing config file for server '5.7.17'.
170214 17:39:13 RDR1 INFO: Creating server config files server-my.cnf and server-all.cnf in /home/admin/backup-tmp
170214 17:39:13 RDR1 INFO: Copying meta file /home/admin/backup-tmp/meta/backup_variables.txt.
170214 17:39:13 RDR1 INFO: Copying meta file /home/admin/backup-tmp/datadir/ibbackup_logfile.
170214 17:39:13 RDR1 INFO: Copying meta file /home/admin/backup-tmp/server-all.cnf.
170214 17:39:13 RDR1 INFO: Copying meta file /home/admin/backup-tmp/server-my.cnf.
170214 17:39:13 RDR1 INFO: Copying meta file /home/admin/backup-tmp/meta/backup_content.xml.
170214 17:39:13 RDR1 INFO: Copying meta file /home/admin/backup-tmp/meta/image_files.xml.
170214 17:39:13 MAIN INFO: Full Image Backup operation completed successfully.
170214 17:39:13 MAIN INFO: Backup image created successfully.
170214 17:39:13 MAIN INFO: Image Path = /home/admin/backups/my.mbi
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 2543104
End LSN : 2543132
-------------------------------------------------------------
mysqlbackup completed OK!