本文主要介绍MySQL Enterprise Backup的安装以及使用。
1、安装
从官方网站下载MySQL Enterprise Backup安装包，推荐下载rpm软件包。
[root@mydb01 ~]# rpm -qpl meb-4.1.1-el7.x86_64.rpm
warning: meb-4.1.1-el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
/opt
/opt/mysql
/opt/mysql/meb-4.1
/opt/mysql/meb-4.1/LICENSE.mysql
/opt/mysql/meb-4.1/LICENSE.third_party
/opt/mysql/meb-4.1/README.txt
/opt/mysql/meb-4.1/bin/mysqlbackup
/opt/mysql/meb-4.1/manual.html
/opt/mysql/meb-4.1/mvl.css
[root@mydb01 ~]# yum -y install meb-4.1.1-el7.x86_64.rpm
[root@mydb01 ~]# vi .bash_profile
export PATH=$PATH:$HOME/bin:/opt/mysql/meb-4.1/bin
2、备份
如果不想用root用户进行备份操作，则使用下面的语句创建专用的备份用户并赋予相关权限：
mysql> CREATE USER 'bakuser'@'localhost' IDENTIFIED BY 'abcABC@12';
mysql> GRANT RELOAD ON *.* TO 'bakuser'@'localhost';
mysql> GRANT CREATE, INSERT, DROP, UPDATE ON mysql.ibbackup_binlog_marker TO 'bakuser'@'localhost';
mysql> GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'bakuser'@'localhost';
mysql> GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'bakuser'@'localhost';
mysql> GRANT REPLICATION CLIENT ON *.* TO 'bakuser'@'localhost';
mysql> GRANT SUPER ON *.* TO 'bakuser'@'localhost';
mysql> GRANT CREATE TEMPORARY TABLES ON mysql.* TO 'bakuser'@'localhost';
mysql> FLUSH PRIVILEGES;
2.1 全量备份
[root@mydb01 ~]# mysqlbackup --defaults-file=/etc/my.cnf --user=root --password --host=127.0.0.1 --with-timestamp --backup-dir=/u02/full backup-and-apply-log
MySQL Enterprise Backup version 4.1.1 Linux-4.1.12-61.1.16.el7uek.x86_64-x86_64 [2018/04/24 07:54:03]
Copyright (c) 2003, 2018, Oracle and/or its affiliates. All Rights Reserved.
[Build ID : 12942442.c465cfd7261c781502aec204494abf138f9d59e6]
180627 11:00:02 MAIN INFO: A thread created with Id '140048649107264'
180627 11:00:02 MAIN INFO: Starting with following command line ...
mysqlbackup --defaults-file=/etc/my.cnf --user=root --password
--host=127.0.0.1 --backup-dir=/u02/full backup-and-apply-log
180627 11:00:02 MAIN INFO:
Enter password:
180627 11:00:04 MAIN INFO: Not using SSL.
180627 11:00:04 MAIN INFO: MySQL server version is '5.7.22-log'
180627 11:00:04 MAIN INFO: MySQL server compile os version is 'Linux'
180627 11:00:04 MAIN INFO: SSL: SSL version used for connection is 'TLSv1.1'
180627 11:00:04 MAIN INFO: Got some server configuration information from running server.
180627 11:00:04 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-and-apply-log' run mysqlbackup
prints "mysqlbackup completed OK!".
180627 11:00:05 MAIN INFO: 0 active keyring plugins found.
180627 11:00:05 MAIN INFO: KEF target path:'/u02/full/2018-06-27_11-00-05/meta/keyring_kef'
180627 11:00:05 MAIN INFO: TDE Keyring service initialized.
180627 11:00:05 MAIN INFO: MEB logfile created at /u02/full/2018-06-27_11-00-05/meta/MEB_2018-06-27.11-00-05_backup_apply_log.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 = /u02/full/2018-06-27_11-00-05/datadir
innodb_data_home_dir = /u02/full/2018-06-27_11-00-05/datadir
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /u02/full/2018-06-27_11-00-05/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_undo_directory = /u02/full/2018-06-27_11-00-05/datadir
innodb_undo_tablespaces = 0
innodb_undo_logs = 128
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
180627 11:00:05 MAIN INFO: Unique generated backup id for this is 1530068404×××868
180627 11:00:05 MAIN INFO: Creating 14 buffers each of size 16777216.
180627 11:00:05 MAIN INFO: Full Backup operation starts with following threads
1 read-threads 6 process-threads 1 write-threads
180627 11:00:05 MAIN INFO: Found checkpoint at lsn 75536184611.
180627 11:00:05 MAIN INFO: Starting log scan from lsn = 75536184320 at offset = 41777664 and checkpoint = 75536184611 in file /var/lib/mysql/ib_logfile0.
180627 11:00:05 MAIN INFO: System tablespace file format is Antelope.
180627 11:00:05 MAIN INFO: Starting to copy all innodb files...
180627 11:00:05 RDR1 INFO: A thread created with Id '140048264505088'
180627 11:00:05 PCR2 INFO: A thread created with Id '140048247719680'
180627 11:00:05 PCR3 INFO: A thread created with Id '140048239326976'
180627 11:00:05 PCR5 INFO: A thread created with Id '140048222541568'
180627 11:00:05 PCR4 INFO: A thread created with Id '140048230934272'
180627 11:00:05 WTR1 INFO: A thread created with Id '140048003491584'
180627 11:00:05 RLR1 INFO: A thread created with Id '140047995098880'
180627 11:00:05 PCR6 INFO: A thread created with Id '140048011884288'
180627 11:00:05 PCR1 INFO: A thread created with Id '140048256112384'
180627 11:00:05 RLW1 INFO: A thread created with Id '140047986706176'
180627 11:00:05 RLP1 INFO: A thread created with Id '140047978313472'
180627 11:00:05 RDR1 INFO: Copying /var/lib/mysql/ibdata1 (Barracuda file format).
......
180627 11:15:43 RDR1 INFO: Completed the copy of binlog files...
180627 11:15:43 RDR1 INFO: Opening backup source directory '/var/lib/mysql'
180627 11:15:43 RDR1 INFO: Starting to backup all non-innodb files in
subdirectories of '/var/lib/mysql'
180627 11:15:43 RDR1 INFO: Copying the database directory '.oracle_jre_usage'
180627 11:15:43 RDR1 INFO: Copying the database directory 'hjy_zxzy'
180627 11:15:43 RDR1 INFO: Copying the database directory 'lepus'
180627 11:15:43 RDR1 INFO: Copying the database directory 'mysql'
180627 11:15:44 RDR1 INFO: Copying the database directory 'performance_schema'
180627 11:15:44 RDR1 INFO: Copying the database directory 'sys'
180627 11:15:44 RDR1 INFO: Completing the copy of all non-innodb files.
180627 11:15:44 RDR1 INFO: Requesting completion of redo log copy. Rapid: 0
180627 11:15:44 RLR1 INFO: Signaled '1' to complete log copy.
180627 11:15:44 RLR1 INFO: Signaled '1' to complete log copy.
180627 11:15:44 RLR1 INFO: Signaled '1' to complete log copy.
180627 11:15:44 RLR1 INFO: Redo log reader waited = 913270.00 ms for logs to generate.
180627 11:15:44 RLW1 INFO: A copied database page was modified at 75536184611.
(This is the highest lsn found on page)
Scanned log up to lsn 75536184696.
Was able to parse the log up to lsn 75536184696.
180627 11:15:45 RDR1 INFO: All tables unlocked
180627 11:15:45 RDR1 INFO: All MySQL tables were locked for 2.455 seconds.
180627 11:15:45 RDR1 INFO: Setting server system variable 'old_alter_table' back to '0'.
180627 11:15:45 RDR1 INFO: Reading all global variables from the server.
180627 11:15:45 RDR1 INFO: Completed reading of all global variables from the server.
180627 11:15:45 RDR1 INFO: Writing config file for server '5.7.22-log'.
180627 11:15:45 RDR1 INFO: Creating server config files server-my.cnf and server-all.cnf in /u02/full/2018-06-27_11-00-05
180627 11:15:45 MAIN INFO: Full Backup operation completed successfully.
180627 11:15:45 MAIN INFO: Backup created in directory '/u02/full/2018-06-27_11-00-05'
180627 11:15:45 MAIN INFO: MySQL binlog position: filename master-bin.005828, position 154
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 75536184320
End LSN : 75536184696
-------------------------------------------------------------
180627 11:15:45 MAIN INFO: Creating 14 buffers each of size 65536.
180627 11:15:45 MAIN INFO: Apply-log operation starts with following threads
1 read-threads 1 process-threads 6 apply-threads
180627 11:15:45 MAIN INFO: Using up to 100 MB of memory.
180627 11:15:45 MAIN INFO: ibbackup_logfile's creation parameters:
start lsn 75536184320, end lsn 75536184696,
start checkpoint 75536184611.
180627 11:15:47 ALW1 INFO: A thread created with Id '140047978313472'
180627 11:15:47 ALW3 INFO: A thread created with Id '140047995098880'
180627 11:15:47 ALW4 INFO: A thread created with Id '140048003491584'
180627 11:15:47 ALW2 INFO: A thread created with Id '140047986706176'
180627 11:15:47 RDR1 INFO: A thread created with Id '140048500758272'
180627 11:15:47 ALW6 INFO: A thread created with Id '140048509150976'
180627 11:15:47 ALW5 INFO: A thread created with Id '140048517543680'
180627 11:15:47 PCR1 INFO: A thread created with Id '140048492365568'
180627 11:15:47 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 75536184696.
180627 11:15:47 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
180627 11:15:47 PCR1 INFO: InnoDB: Setting log file size to 50331648.
180627 11:15:47 PCR1 INFO: InnoDB: Setting log file size to 50331648.
180627 11:15:47 PCR1 INFO: We were able to parse ibbackup_logfile up to
lsn 75536184696.
180627 11:15:47 PCR1 INFO: Last MySQL binlog file position 0 154, file name master-bin.005828
180627 11:15:47 PCR1 INFO: The first data file is '/u02/full/2018-06-27_11-00-05/datadir/ibdata1'
and the new created log files are at '/u02/full/2018-06-27_11-00-05/datadir'
180627 11:15:47 MAIN INFO: Apply-log operation completed successfully.
180627 11:15:47 MAIN INFO: Full backup prepared for recovery successfully.
mysqlbackup completed OK!
--defaults-file：指定MySQL的配置文件 --user：指定备份用户 --password：备份用户的密码 --host：MySQL实例运行的主机 --backup-dir：MySQL备份文件的存放路径 --with-timestam：加上时间戳
2.2 增量备份
[root@mydb01 ~]# mysqlbackup --defaults-file=/etc/my.cnf --user=root --password --host=127.0.0.1 --with-timestamp --incremental-backup-dir=/u02/incr --start-lsn=75536184696 --incremental backup
MySQL Enterprise Backup version 4.1.1 Linux-4.1.12-61.1.16.el7uek.x86_64-x86_64 [2018/04/24 07:54:03]
Copyright (c) 2003, 2018, Oracle and/or its affiliates. All Rights Reserved.
[Build ID : 12942442.c465cfd7261c781502aec204494abf138f9d59e6]
180627 11:47:55 MAIN INFO: A thread created with Id '140596443768640'
180627 11:47:55 MAIN INFO: Starting with following command line ...
mysqlbackup --defaults-file=/etc/my.cnf --user=root --password
--host=127.0.0.1 --incremental-backup-dir=/u02/incr
--start-lsn=75536184696 --incremental backup
180627 11:47:55 MAIN INFO:
Enter password:
180627 11:47:58 MAIN INFO: Not using SSL.
180627 11:47:58 MAIN INFO: MySQL server version is '5.7.22-log'
180627 11:47:58 MAIN INFO: MySQL server compile os version is 'Linux'
180627 11:47:58 MAIN INFO: SSL: SSL version used for connection is 'TLSv1.1'
180627 11:47:58 MAIN INFO: Got some server configuration information from running server.
180627 11:47:58 MAIN INFO: Server system variable 'old_alter_table' was set to '0'. Setting it to '1'.
180627 11:47:58 MAIN WARNING: Binlog files will not be copied for this backup as --incremental-base is not specified.
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'backup' run mysqlbackup
prints "mysqlbackup completed OK!".
180627 11:47:58 MAIN INFO: 0 active keyring plugins found.
180627 11:47:58 MAIN INFO: KEF target path:'/u02/incr/2018-06-27.11-47-58/meta/keyring_kef'
180627 11:47:58 MAIN INFO: TDE Keyring service initialized.
180627 11:47:58 MAIN INFO: MEB logfile created at /u02/incr/2018-06-27.11-47-58/meta/MEB_2018-06-27.11-47-58_inc_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 = /u02/incr/2018-06-27.11-47-58/datadir
innodb_data_home_dir = /u02/incr/2018-06-27.11-47-58/datadir
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /u02/incr/2018-06-27.11-47-58/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_undo_directory = /u02/incr/2018-06-27.11-47-58/datadir
innodb_undo_tablespaces = 0
innodb_undo_logs = 128
innodb_buffer_pool_filename = ib_buffer_pool
innodb_page_size = 16384
innodb_checksum_algorithm = crc32
180627 11:47:58 MAIN INFO: Unique generated backup id for this is 15300712787259227
180627 11:47:58 MAIN INFO: Creating 14 buffers each of size 17301504.
180627 11:47:58 MAIN INFO: Incremental Backup operation starts with following threads
1 read-threads 6 process-threads 1 write-threads
180627 11:47:58 MAIN INFO: Found checkpoint at lsn 75536192617.
180627 11:47:58 MAIN INFO: Starting log scan from lsn = 75536192512 at offset = 41785856 and checkpoint = 75536192617 in file /var/lib/mysql/ib_logfile0.
180627 11:47:58 MAIN INFO: System tablespace file format is Antelope.
180627 11:47:58 MAIN INFO: Starting to copy all innodb files...
180627 11:47:58 RDR1 INFO: A thread created with Id '140596051826432'
180627 11:47:58 PCR1 INFO: A thread created with Id '140596043433728'
180627 11:47:58 WTR1 INFO: A thread created with Id '140595922188032'
180627 11:47:58 RDR1 INFO: Copying /var/lib/mysql/ibdata1 (Barracuda file format).
180627 11:47:58 RLW1 INFO: A thread created with Id '140595905402624'
180627 11:47:58 PCR2 INFO: A thread created with Id '140596035041024'
180627 11:47:58 PCR5 INFO: A thread created with Id '140595938973440'
180627 11:47:58 PCR4 INFO: A thread created with Id '140595947366144'
180627 11:47:58 RLP1 INFO: A thread created with Id '140595553105664'
180627 11:47:58 PCR6 INFO: A thread created with Id '140595930580736'
180627 11:47:58 RLR1 INFO: A thread created with Id '140595913795328'
180627 11:47:58 PCR3 INFO: A thread created with Id '140595955758848'
......
180627 11:53:07 RDR1 INFO: Completing the copy of innodb files.
180627 11:53:07 RDR1 INFO: Requesting a dump of the InnoDB buffer pool
180627 11:53:07 RDR1 INFO: Waiting for the dump of the InnoDB buffer pool to complete
180627 11:53:07 RDR1 INFO: The dump of the InnoDB buffer pool completed
180627 11:53:07 RDR1 INFO: Preparing to lock tables: Connected to mysqld server.
180627 11:53:07 RDR1 INFO: Starting to lock all the tables...
180627 11:53:07 RDR1 INFO: Flushing the default MyISAM key cache.
180627 11:53:07 RDR1 INFO: All tables are locked and flushed to disk
180627 11:53:07 RDR1 INFO: Completed the copy of binlog files...
180627 11:53:07 RDR1 INFO: Opening backup source directory '/var/lib/mysql'
180627 11:53:07 RDR1 INFO: Starting to backup all non-innodb files in
subdirectories of '/var/lib/mysql'
180627 11:53:07 RDR1 INFO: Copying the database directory '.oracle_jre_usage'
180627 11:53:07 RDR1 INFO: Copying the database directory 'hjy_zxzy'
180627 11:53:08 RDR1 INFO: Copying the database directory 'lepus'
180627 11:53:08 RDR1 INFO: Copying the database directory 'mysql'
180627 11:53:08 RDR1 INFO: Copying the database directory 'performance_schema'
180627 11:53:08 RDR1 INFO: Copying the database directory 'sys'
180627 11:53:08 RDR1 INFO: Completing the copy of all non-innodb files.
180627 11:53:08 RDR1 INFO: Requesting completion of redo log copy. Rapid: 0
180627 11:53:08 RLR1 INFO: Signaled '1' to complete log copy.
180627 11:53:08 RLR1 INFO: Signaled '1' to complete log copy.
180627 11:53:08 RLR1 INFO: Signaled '1' to complete log copy.
180627 11:53:08 RLR1 INFO: Redo log reader waited = 303270.00 ms for logs to generate.
180627 11:53:08 RLW1 INFO: A copied database page was modified at 75536192493.
(This is the highest lsn found on page)
Scanned log up to lsn 75536192626.
Was able to parse the log up to lsn 75536192626.
180627 11:53:09 RDR1 INFO: All tables unlocked
180627 11:53:09 RDR1 INFO: All MySQL tables were locked for 1.994 seconds.
180627 11:53:09 RDR1 INFO: Setting server system variable 'old_alter_table' back to '0'.
180627 11:53:09 RDR1 INFO: Reading all global variables from the server.
180627 11:53:09 RDR1 INFO: Completed reading of all global variables from the server.
180627 11:53:09 RDR1 INFO: Writing config file for server '5.7.22-log'.
180627 11:53:09 RDR1 INFO: Creating server config files server-my.cnf and server-all.cnf in /u02/incr/2018-06-27.11-47-58
180627 11:53:09 MAIN INFO: Incremental Backup operation completed successfully.
180627 11:53:09 MAIN INFO: Backup created in directory '/u02/incr/2018-06-27.11-47-58'
180627 11:53:09 MAIN INFO: Backup contains changes from lsn 75536184697 to lsn 75536192626
180627 11:53:09 MAIN INFO: MySQL binlog position: filename master-bin.005828, position 809
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 75536184697
End LSN : 75536192626
-------------------------------------------------------------
mysqlbackup completed OK! with 1 warnings
--defaults-file：指定MySQL的配置文件 --user：指定备份用户 --password：备份用户的密码 --host：MySQL实例运行的主机 --incremental-backup-dir：MySQL增量备份文件的存放路径 --with-timestam：加上时间戳 --start-lsn：指上次备份完成后的LSN，可以在备份目录里的meta/backup_variables.txt文件中找到。 在初始备份过程中，系统会在mysql数据库中创建backup_history以及backup_progress两张表，备份的相关信息都会记录在这两张表中。
3、恢复
3.1 合并增量备份
如果mysql的备份有全量备份以及增量备份，在恢复之前必须整合增量备份到全量备份。
[root@mydb01 ~]# mysqlbackup --incremental-backup-dir=/u02/incr/2018-06-27.11-47-58 --backup-dir=/u02/full/2018-06-27_11-00-05/ apply-incremental-backup
MySQL Enterprise Backup version 4.1.1 Linux-4.1.12-61.1.16.el7uek.x86_64-x86_64 [2018/04/24 07:54:03]
Copyright (c) 2003, 2018, Oracle and/or its affiliates. All Rights Reserved.
[Build ID : 12942442.c465cfd7261c781502aec204494abf138f9d59e6]
180627 12:30:14 MAIN INFO: A thread created with Id '140486177048384'
180627 12:30:14 MAIN INFO: Starting with following command line ...
mysqlbackup --incremental-backup-dir=/u02/incr/2018-06-27.11-47-58
--backup-dir=/u02/full/2018-06-27_11-00-05/ apply-incremental-backup
180627 12:30:14 MAIN INFO:
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'apply-incremental-backup' run mysqlbackup
prints "mysqlbackup completed OK!".
180627 12:30:14 MAIN INFO: MySQL server version is '5.7.22-log'
180627 12:30:14 MAIN INFO: Restoring ...5.7.22-log version
180627 12:30:14 MAIN INFO: MySQL server version is '5.7.22-log'
180627 12:30:14 MAIN INFO: Restoring ...5.7.22-log version
180627 12:30:14 MAIN INFO: KEF source path:'/u02/incr/meta'
180627 12:30:14 MAIN INFO: KEF target path:'/u02/full/20180627/meta'
180627 12:30:14 MAIN INFO: TDE Keyring service initialized.
180627 12:30:14 MAIN INFO: MEB logfile created at /u02/full/20180627/meta/MEB_2018-06-27.12-30-14_apply_inc.log
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /u02/full/20180627/datadir
innodb_data_home_dir = /u02/full/20180627/datadir
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /u02/full/20180627/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
180627 12:30:14 MAIN INFO: Creating 14 buffers each of size 17301504.
180627 12:30:14 MAIN INFO: Apply-Incremental-Pages operation starts with following threads
1 read-threads 1 write-threads
180627 12:30:14 MAIN INFO: Starting to copy all innodb files...
180627 12:30:14 MAIN INFO: Starting deletion of obsolete ibd files in backup-dir...
180627 12:30:14 MAIN INFO: Deleting dropped ibd files.
180627 12:30:14 MAIN INFO: Deletion of obsolete ibd files in backup-dir is completed.
180627 12:30:14 MAIN INFO: Deleting non-innodb files in backup-dir...
180627 12:30:14 RDR1 INFO: A thread created with Id '140485805418240'
180627 12:30:14 WTR1 INFO: A thread created with Id '140485797025536'
180627 12:30:14 RDR1 INFO: Applying diff pages from `/u02/incr/datadir/ibdata1` to `/u02/full/20180627/datadir/ibdata1`.
180627 12:30:14 RDR1 INFO: Applying incremental pages to innodb data files...
180627 12:30:14 RDR1 INFO: Applying diff pages from `/u02/incr/datadir/lepus/chapter.ibd` to `/u02/full/20180627/datadir/lepus/chapter.ibd`.
180627 12:30:14 RDR1 INFO: Applying diff pages from `/u02/incr/datadir/mysql/innodb_index_stats.ibd` to `/u02/full/20180627/datadir/mysql/innodb_index_stats.ibd`.
180627 12:30:14 RDR1 INFO: Applying diff pages from `/u02/incr/datadir/mysql/innodb_table_stats.ibd` to `/u02/full/20180627/datadir/mysql/innodb_table_stats.ibd`.
180627 12:30:14 RDR1 INFO: This incremental backup does not have binlogs. Applying this incremental backups ignore binlogs for all .
180627 12:30:14 RDR1 INFO: Completed the copy of binlog files...
180627 12:30:14 RDR1 INFO: Starting to copy all non-innodb files in
subdirectories of '/u02/incr/datadir'
180627 12:30:14 RDR1 INFO: Copying the database directory '.oracle_jre_usage'
180627 12:30:14 RDR1 INFO: Copying the database directory 'hjy_zxzy'
180627 12:30:14 RDR1 INFO: Copying the database directory 'lepus'
180627 12:30:14 RDR1 INFO: Copying the database directory 'mysql'
180627 12:30:14 RDR1 INFO: Copying the database directory 'performance_schema'
180627 12:30:14 RDR1 INFO: Copying the database directory 'sys'
180627 12:30:14 RDR1 INFO: Completing the copy of all non-innodb files.
180627 12:30:14 RDR1 INFO: Copying server configuration files to backup_dir.
180627 12:30:14 MAIN INFO: No Keyring file to process.
180627 12:30:14 MAIN INFO: Apply-Incremental-Pages operation completed successfully.
180627 12:30:14 MAIN INFO: Creating 14 buffers each of size 65536.
180627 12:30:14 MAIN INFO: Apply-log operation starts with following threads
1 read-threads 1 process-threads 6 apply-threads
180627 12:30:14 MAIN INFO: Using up to 100 MB of memory.
180627 12:30:14 MAIN INFO: ibbackup_logfile's creation parameters:
start lsn 75536192512, end lsn 75536192626,
start checkpoint 75536192617.
180627 12:30:15 ALW1 INFO: A thread created with Id '140485797025536'
180627 12:30:15 ALW2 INFO: A thread created with Id '140485805418240'
180627 12:30:15 ALW3 INFO: A thread created with Id '140486043895552'
180627 12:30:15 ALW6 INFO: A thread created with Id '140486018717440'
180627 12:30:15 ALW4 INFO: A thread created with Id '140486035502848'
180627 12:30:15 ALW5 INFO: A thread created with Id '140486027110144'
180627 12:30:15 PCR1 INFO: A thread created with Id '140486001932032'
180627 12:30:15 RDR1 INFO: A thread created with Id '140486010324736'
180627 12:30:15 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 75536192626.
180627 12:30:15 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
180627 12:30:15 PCR1 INFO: InnoDB: Setting log file size to 50331648.
180627 12:30:15 PCR1 INFO: InnoDB: Setting log file size to 50331648.
180627 12:30:16 PCR1 INFO: We were able to parse ibbackup_logfile up to
lsn 75536192626.
180627 12:30:16 PCR1 INFO: Last MySQL binlog file position 0 809, file name master-bin.005828
180627 12:30:16 PCR1 INFO: The first data file is '/u02/full/20180627/datadir/ibdata1'
and the new created log files are at '/u02/full/20180627/datadir'
180627 12:30:16 MAIN INFO: No Keyring file to process.
180627 12:30:16 MAIN INFO: Apply-log operation completed successfully.
180627 12:30:16 MAIN INFO: Full backup prepared for recovery successfully.
mysqlbackup completed OK!
--incremental-backup-dir：指增量备份的目录 --backup-dir：指全量备份的目录
3.2 使用全备份恢复
[root@mydb01 lib]# mysqlbackup --user=root --datadir=/var/lib/mysql --backup-dir=/u02/full/2018-06-27_11-00-05 copy-back
MySQL Enterprise Backup version 4.1.1 Linux-4.1.12-61.1.16.el7uek.x86_64-x86_64 [2018/04/24 07:54:03]
Copyright (c) 2003, 2018, Oracle and/or its affiliates. All Rights Reserved.
[Build ID : 12942442.c465cfd7261c781502aec204494abf138f9d59e6]
180627 12:32:19 MAIN INFO: A thread created with Id '140697770657600'
180627 12:32:19 MAIN INFO: Starting with following command line ...
mysqlbackup --user=root --datadir=/var/lib/mysql
--backup-dir=/u02/full/2018-06-27_11-00-05 copy-back
180627 12:32:19 MAIN INFO:
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'copy-back' run mysqlbackup
prints "mysqlbackup completed OK!".
180627 12:32:19 MAIN INFO: MySQL server version is '5.7.22-log'
180627 12:32:19 MAIN INFO: Restoring ...5.7.22-log version
180627 12:32:19 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.
180627 12:32:19 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.
180627 12:32:19 MAIN WARNING: If you restore to a server of a different version, the innodb_log_file_size parameter might have a different default. In that case you need to add 'innodb_log_file_size=50331648' to the target server configuration.
180627 12:32:19 MAIN INFO: KEF source path:'/u02/full/2018-06-27_11-00-05/meta'
180627 12:32:19 MAIN INFO: KEF target path:'/var/lib/mysql'
180627 12:32:19 MAIN INFO: TDE Keyring service initialized.
180627 12:32:19 MAIN INFO: MEB logfile created at /u02/full/2018-06-27_11-00-05/meta/MEB_2018-06-27.12-32-19_copy_back.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 = /u02/full/2018-06-27_11-00-05/datadir
innodb_data_home_dir = /u02/full/2018-06-27_11-00-05/datadir
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /u02/full/2018-06-27_11-00-05/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
180627 12:32:19 MAIN INFO: Creating 14 buffers each of size 16777216.
180627 12:32:19 MAIN INFO: Copy-back operation starts with following threads
1 read-threads 1 write-threads
180627 12:32:19 MAIN INFO: Starting to copy all innodb files...
180627 12:32:19 RDR1 INFO: A thread created with Id '140697406367488'
180627 12:32:19 WTR1 INFO: A thread created with Id '140697397974784'
180627 12:32:19 RDR1 INFO: Copying /u02/full/2018-06-27_11-00-05/datadir/ibdata1.
......
180627 12:41:55 RDR1 INFO: Copying /u02/full/2018-06-27_11-00-05/datadir/lepus/questions.ibd.
180627 12:41:55 RDR1 INFO: Copying /u02/full/2018-06-27_11-00-05/datadir/mysql/engine_cost.ibd.
180627 12:41:55 RDR1 INFO: Copying /u02/full/2018-06-27_11-00-05/datadir/mysql/gtid_executed.ibd.
180627 12:41:55 RDR1 INFO: Copying /u02/full/2018-06-27_11-00-05/datadir/mysql/innodb_index_stats.ibd.
180627 12:41:55 RDR1 INFO: Copying /u02/full/2018-06-27_11-00-05/datadir/mysql/innodb_table_stats.ibd.
180627 12:41:55 RDR1 INFO: Copying /u02/full/2018-06-27_11-00-05/datadir/mysql/server_cost.ibd.
180627 12:41:55 RDR1 INFO: Copying /u02/full/2018-06-27_11-00-05/datadir/mysql/slave_master_info.ibd.
180627 12:41:55 RDR1 INFO: Copying /u02/full/2018-06-27_11-00-05/datadir/mysql/slave_relay_log_info.ibd.
180627 12:41:55 RDR1 INFO: Copying /u02/full/2018-06-27_11-00-05/datadir/mysql/slave_worker_info.ibd.
180627 12:41:55 RDR1 INFO: Copying /u02/full/2018-06-27_11-00-05/datadir/sys/sys_config.ibd.
180627 12:41:55 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.
180627 12:41:55 RDR1 INFO: Completed the copy of binlog files...
180627 12:41:55 RDR1 INFO: Copying the database directory '.oracle_jre_usage'
180627 12:41:55 RDR1 INFO: Copying the database directory 'hjy_zxzy'
180627 12:41:55 RDR1 INFO: Copying the database directory 'lepus'
180627 12:41:55 RDR1 INFO: Copying the database directory 'mysql'
180627 12:41:55 RDR1 INFO: Copying the database directory 'performance_schema'
180627 12:41:55 RDR1 INFO: Copying the database directory 'sys'
180627 12:41:55 RDR1 INFO: Completing the copy of all non-innodb files.
180627 12:41:55 RDR1 INFO: Copying the log file 'ib_logfile0'
180627 12:41:55 RDR1 INFO: Copying the log file 'ib_logfile1'
180627 12:41:55 MAIN INFO: No Keyring file to process.
180627 12:41:55 MAIN INFO: MySQL server version is '5.7.22-log'
180627 12:41:55 MAIN INFO: MySQL server compile os version is 'Linux'
180627 12:41:55 MAIN INFO: Writing config file for server '5.7.22-log'.
180627 12:41:55 MAIN INFO: Creating server config files server-my.cnf and server-all.cnf in /var/lib/mysql
180627 12:41:55 MAIN INFO: Variable 'datadir'='/var/lib/mysql/'->'/var/lib/mysql' option 'datadir' type 0
180627 12:41:55 MAIN INFO: Variable 'datadir'='/var/lib/mysql/'->'/var/lib/mysql' option 'datadir' type 0
180627 12:41:55 MAIN INFO: Variable 'innodb_buffer_pool_filename'='ib_buffer_pool'->'ib_buffer_pool' option 'innodb_buffer_pool_filename' type 9
180627 12:41:55 MAIN INFO: Variable 'innodb_buffer_pool_filename'='ib_buffer_pool'->'ib_buffer_pool' option 'innodb_buffer_pool_filename' type 9
180627 12:41:55 MAIN INFO: Variable 'innodb_checksum_algorithm'='crc32'->'crc32' option 'innodb_checksum_algorithm' type 11
180627 12:41:55 MAIN INFO: Variable 'innodb_checksum_algorithm'='crc32'->'crc32' option 'innodb_checksum_algorithm' type 11
180627 12:41:55 MAIN INFO: Variable 'innodb_checksums'='ON'->'ON' option 'innodb_checksums' type 12
180627 12:41:55 MAIN INFO: Variable 'innodb_checksums'='ON'->'ON' option 'innodb_checksums' type 12
180627 12:41:55 MAIN INFO: Variable 'innodb_data_file_path'='ibdata1:12M:autoextend'->'ibdata1:12M:autoextend' option 'innodb_data_file_path' type 2
180627 12:41:55 MAIN INFO: Variable 'innodb_data_file_path'='ibdata1:12M:autoextend'->'ibdata1:12M:autoextend' option 'innodb_data_file_path' type 2
180627 12:41:55 MAIN INFO: Variable 'innodb_data_home_dir'=''->'/var/lib/mysql' option 'innodb_data_home_dir' type 1
180627 12:41:55 MAIN INFO: Variable 'innodb_log_file_size'='50331648'->'50331648' option 'innodb_log_file_size' type 5
180627 12:41:55 MAIN INFO: Variable 'innodb_log_file_size'='50331648'->'50331648' option 'innodb_log_file_size' type 5
180627 12:41:55 MAIN INFO: Variable 'innodb_log_files_in_group'='2'->'2' option 'innodb_log_files_in_group' type 4
180627 12:41:55 MAIN INFO: Variable 'innodb_log_files_in_group'='2'->'2' option 'innodb_log_files_in_group' type 4
180627 12:41:55 MAIN INFO: Variable 'innodb_log_group_home_dir'='./'->'/var/lib/mysql' option 'innodb_log_group_home_dir' type 3
180627 12:41:55 MAIN INFO: Variable 'innodb_log_group_home_dir'='./'->'/var/lib/mysql' option 'innodb_log_group_home_dir' type 3
180627 12:41:55 MAIN INFO: Variable 'innodb_page_size'='16384'->'16384' option 'innodb_page_size' type 10
180627 12:41:55 MAIN INFO: Variable 'innodb_page_size'='16384'->'16384' option 'innodb_page_size' type 10
180627 12:41:55 MAIN INFO: Variable 'innodb_undo_directory'='./'->'/var/lib/mysql' option 'innodb_undo_directory' type 6
180627 12:41:55 MAIN INFO: Variable 'innodb_undo_directory'='./'->'/var/lib/mysql' option 'innodb_undo_directory' type 6
180627 12:41:55 MAIN INFO: Variable 'innodb_undo_logs'='128'->'128' option 'innodb_undo_logs' type 8
180627 12:41:55 MAIN INFO: Variable 'innodb_undo_logs'='128'->'128' option 'innodb_undo_logs' type 8
180627 12:41:55 MAIN INFO: Variable 'innodb_undo_tablespaces'='0'->'0' option 'innodb_undo_tablespaces' type 7
180627 12:41:55 MAIN INFO: Variable 'innodb_undo_tablespaces'='0'->'0' option 'innodb_undo_tablespaces' type 7
180627 12:41:55 MAIN INFO: Copy-back operation completed successfully.
180627 12:41:55 MAIN INFO: Finished copying backup files to '/var/lib/mysql'
mysqlbackup completed OK!
3.3 启动MySQL服务
恢复后的数据库文件的属主是root用户，必须改成mysql用户才能启动MySQL服务。
[root@mydb01 ~]# chown -R mysql:mysql /var/lib/mysql
[root@mydb01 ~]# systemctl start mysqld
[root@mydb01 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2018-06-27 13:00:15 CST; 2h 29min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 6482 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 6464 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 6485 (mysqld)
CGroup: /system.slice/mysqld.service
└─6485 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Jun 27 13:00:14 mydb01 systemd[1]: Starting MySQL Server...
Jun 27 13:00:15 mydb01 systemd[1]: Started MySQL Server.
4、备份脚本
4.1 全量备份脚本
#!/bin/bash
BACKUP_DIR=/u02/full
BACKUP_PASS=abcABC@12
BACKUP_USER=root
/opt/mysql/meb-4.1/bin/mysqlbackup --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=$BACKUP_USER --password=$BACKUP_PASS --with-timestamp --backup-dir=$BACKUP_DIR backup-and-apply-log
4.2 增量备份脚本
#!/bin/bash
BACKUP_DIR=/u02/incr
BACKUP_PASS=abcABC@12
BACKUP_USER=root
/opt/mysql/meb-4.1/bin/mysqlbackup --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=$BACKUP_USER --password=$BACKUP_PASS --incremental=optimistic --incremental-base=history:last_backup --incremental-backup-dir=/u02/incr --with-timestamp backup
4.3 全备份成功发邮件脚本
#!/bin/bash
BACKUP_DIR=/u02/full
BACKUP_PASS=abcABC@12
BACKUP_USER=root
DATE_DAY=$(date +"%Y-%m-%d")
DATE_HOUR=$(date +"%H")
EMAIL_RECIPIENT=root@localhost
/opt/mysql/meb-4.1/bin/mysqlbackup --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=$BACKUP_USER --password=$BACKUP_PASS --with-timestamp --backup-dir=$BACKUP_DIR backup-and-apply-log
NO_OF_COMPLETE_OK_MESSAGES=$(cat $BACKUP_DIR/${DATE_DAY}_${DATE_HOUR}*/meta/MEB_${DATE_DAY}.${DATE_HOUR}*.log | grep "mysqlbackup completed OK" | wc -l)
# Note that the string "mysqlbackup completed OK" must occur 2 times in the log in order for the backup to be OK
if [ $NO_OF_COMPLETE_OK_MESSAGES -eq 2 ]; then
# Backup successful, find backup directory
echo "Backup succeeded"
exit 0
else
echo "MySQL backup failed, please check logfile" | mail -s "ERROR: MySQL Backup Failed!" ${EMAIL_RECIPIENT}
exit 1
fi
