MySQL的企业备份（MEB）

首页 > 代码库 > MySQL的企业备份（MEB）

2024-11-23 02:59:02 202人阅读

一、安装

https://edelivery.oracle.com

[root@gflinux meb]# ll

total 2936

-rw-r--r-- 1 root root 2998307 Jan 30 16:02 V59673-01.zip

[root@gflinux meb]# unzip V59673-01.zip

Archive: V59673-01.zip

extracting: meb-3.11.1-rhel5.i386.rpm

extracting: README.txt

[root@gflinux meb]# rpm -ivh meb-3.11.1-rhel5.i386.rpm

[root@gflinux meb]# find / -name mysqlbackup

/opt/mysql/meb-3.11/bin/mysqlbackup

[root@gflinux meb]# cp /opt/mysql/meb-3.11/bin/mysqlbackup /usr/bin/

二、创建备份账号并赋予特权

为了使特权用户能够利用MEB进行备份，就需要提高访问权限，这些特权包括：

mysql> CREATE USER dbbackup@localhost IDENTIFIED BY ‘123456‘;

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT RELOAD,REPLICATION CLIENT,SUPER,CREATE TEMPORARY TABLES ON *.* TO ‘dbbackup‘@‘localhost‘;

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT CREATE,INSERT,DROP ON mysql.backup_progress TO ‘dbbackup‘@‘localhost‘;

Query OK, 0 rows affected (0.01 sec)

mysql> GRANT CREATE,INSERT,DROP ON mysql.backup_history TO ‘dbbackup‘@‘localhost‘;

Query OK, 0 rows affected (0.00 sec)

三、备份

[root@gflinux backup]# time mysqlbackup --user=dbbackup --password=123456 --backup-dir=/home/backup/${HOSTNAME}_`date +"%Y%m%d_%H:%M:%S"` backup-and-apply-log

MySQL Enterprise Backup version 3.11.1 Linux-2.6.18-92.1.10.el5-i686 [2014/11/04]

mysqlbackup: INFO: Starting with following command line ...

mysqlbackup --user=dbbackup --password=xxxxxx

--backup-dir=/home/backup/gflinux_20150130_16:37:30

backup-and-apply-log

mysqlbackup: INFO:

mysqlbackup: INFO: MySQL server version is ‘5.6.21‘.

mysqlbackup: INFO: Got some server configuration information from running server.

IMPORTANT: Please check that mysqlbackup run completes successfully.

At the end of a successful ‘backup-and-apply-log‘ run mysqlbackup

prints "mysqlbackup completed OK!".

150130 16:37:30 mysqlbackup: INFO: MEB logfile created at /home/backup/gflinux_20150130_16:37:30/meta/MEB_2015-01-30.16-37-30_backup_apply_log.log

--------------------------------------------------------------------

Server Repository Options:

--------------------------------------------------------------------

datadir = /opt/mysql/data/

innodb_data_home_dir =

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = /opt/mysql/data/

innodb_log_files_in_group = 2

innodb_log_file_size = 50331648

innodb_page_size = 16384

innodb_checksum_algorithm = innodb

innodb_undo_directory = /opt/mysql/data/

innodb_undo_tablespaces = 0

innodb_undo_logs = 128

--------------------------------------------------------------------

Backup Config Options:

--------------------------------------------------------------------

datadir = /home/backup/gflinux_20150130_16:37:30/datadir

innodb_data_home_dir = /home/backup/gflinux_20150130_16:37:30/datadir

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = /home/backup/gflinux_20150130_16:37:30/datadir

innodb_log_files_in_group = 2

innodb_log_file_size = 50331648

innodb_page_size = 16384

innodb_checksum_algorithm = innodb

innodb_undo_directory = /home/backup/gflinux_20150130_16:37:30/datadir

innodb_undo_tablespaces = 0

innodb_undo_logs = 128

mysqlbackup: INFO: Unique generated backup id for this is 14226070505710696

mysqlbackup: INFO: Creating 14 buffers each of size 16777216.

150130 16:37:32 mysqlbackup: INFO: Full Backup operation starts with following threads

1 read-threads 6 process-threads 1 write-threads

150130 16:37:32 mysqlbackup: INFO: System tablespace file format is Antelope.

150130 16:37:32 mysqlbackup: INFO: Starting to copy all innodb files...

mysqlbackup: 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.

150130 16:37:32 mysqlbackup: INFO: Found checkpoint at lsn 1633585.

150130 16:37:32 mysqlbackup: INFO: Starting log scan from lsn 1633280.

150130 16:37:32 mysqlbackup: INFO: Copying log...

150130 16:37:32 mysqlbackup: INFO: Log copied, lsn 1633585.

150130 16:37:32 mysqlbackup: INFO: Copying /opt/mysql/data/ibdata1 (Antelope file format).

150130 16:37:32 mysqlbackup: INFO: Copying /opt/mysql/data/mysql/innodb_index_stats.ibd (Antelope file format).

150130 16:37:32 mysqlbackup: INFO: Copying /opt/mysql/data/mysql/innodb_table_stats.ibd (Antelope file format).

150130 16:37:32 mysqlbackup: INFO: Copying /opt/mysql/data/mysql/slave_master_info.ibd (Antelope file format).

150130 16:37:32 mysqlbackup: INFO: Copying /opt/mysql/data/mysql/slave_relay_log_info.ibd (Antelope file format).

150130 16:37:32 mysqlbackup: INFO: Copying /opt/mysql/data/mysql/slave_worker_info.ibd (Antelope file format).

150130 16:37:32 mysqlbackup: INFO: Copying /opt/mysql/data/test/t.ibd (Antelope file format).

150130 16:37:32 mysqlbackup: INFO: Completing the copy of innodb files.

150130 16:37:33 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.

150130 16:37:33 mysqlbackup: INFO: Starting to lock all the tables...

150130 16:37:33 mysqlbackup: INFO: All tables are locked and flushed to disk

150130 16:37:33 mysqlbackup: INFO: Opening backup source directory ‘/opt/mysql/data/‘

150130 16:37:33 mysqlbackup: INFO: Starting to backup all non-innodb files in

subdirectories of ‘/opt/mysql/data/‘

150130 16:37:33 mysqlbackup: INFO: Copying the database directory ‘mysql‘

150130 16:37:34 mysqlbackup: INFO: Copying the database directory ‘performance_schema‘

150130 16:37:34 mysqlbackup: INFO: Copying the database directory ‘test‘

150130 16:37:34 mysqlbackup: INFO: Completing the copy of all non-innodb files.

150130 16:37:35 mysqlbackup: INFO: A copied database page was modified at 1633585.

(This is the highest lsn found on page)

Scanned log up to lsn 1633585.

Was able to parse the log up to lsn 1633585.

Maximum page number for a log record 0

150130 16:37:35 mysqlbackup: INFO: All tables unlocked

150130 16:37:35 mysqlbackup: INFO: All MySQL tables were locked for 1.748 seconds.

150130 16:37:35 mysqlbackup: INFO: Reading all global variables from the server.

150130 16:37:35 mysqlbackup: INFO: Completed reading of all global variables from the server.

150130 16:37:35 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /home/backup/gflinux_20150130_16:37:30

150130 16:37:35 mysqlbackup: INFO: Full Backup operation completed successfully.

150130 16:37:35 mysqlbackup: INFO: Backup created in directory ‘/home/backup/gflinux_20150130_16:37:30‘

-------------------------------------------------------------

Parameters Summary

-------------------------------------------------------------

Start LSN : 1633280

End LSN : 1633585

-------------------------------------------------------------

mysqlbackup: INFO: Creating 14 buffers each of size 65536.

150130 16:37:35 mysqlbackup: INFO: Apply-log operation starts with following threads

1 read-threads 1 process-threads

mysqlbackup: INFO: Using up to 100 MB of memory.

150130 16:37:35 mysqlbackup: INFO: ibbackup_logfile‘s creation parameters:

start lsn 1633280, end lsn 1633585,

start checkpoint 1633585.

mysqlbackup: 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

mysqlbackup: INFO: InnoDB: Setting log file size to 50331648

150130 16:37:36 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to

lsn 1633585.

150130 16:37:36 mysqlbackup: INFO: The first data file is ‘/home/backup/gflinux_20150130_16:37:30/datadir/ibdata1‘

and the new created log files are at ‘/home/backup/gflinux_20150130_16:37:30/datadir‘

150130 16:37:36 mysqlbackup: INFO: Apply-log operation completed successfully.

150130 16:37:36 mysqlbackup: INFO: Full backup prepared for recovery successfully.

mysqlbackup completed OK!

real0m6.225s

user0m0.023s

sys0m0.659s

[root@gflinux backup]#

四、监控：

mysqlbackup的命令结果除了以文本形式输出外，相关信息也被记录到mysql模式中：

mysql> select * from backup_history\G;

*************************** 1. row ***************************

backup_id: 14226039516325379

tool_name: mysqlbackup --user=root --password=xxxxxx --backup-dir=/home/backup/test1 backup-and-apply-log

start_time: 2015-01-30 15:45:51

end_time: 2015-01-30 15:45:56

binlog_pos: -1

binlog_file: BINLOG-DISABLED

compression_level: 0

engines: MEMORY:MyISAM:CSV:InnoDB:PERFORMANCE_SCHEMA:

innodb_data_file_path: ibdata1:12M:autoextend

innodb_file_format: Antelope

start_lsn: 1633280

end_lsn: 1633585

incremental_base_lsn: 0

backup_type: FULL

backup_format: DIRECTORY

mysql_data_dir: /opt/mysql/data/

innodb_data_home_dir:

innodb_log_group_home_dir: /opt/mysql/data/

innodb_log_files_in_group: 2

innodb_log_file_size: 50331648

backup_destination: /home/backup/test1

lock_time: 2.444

exit_state: SUCCESS

last_error: NO_ERROR

last_error_code: 0

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> select * from backup_progress where backup_id=14226039516325379\G;

*************************** 1. row ***************************

backup_id: 14226039516325379

tool_name: mysqlbackup

error_code: 0

error_message: NO_ERROR

current_time: 2015-01-30 15:45:53

current_state: Started mysqlbackup.

*************************** 2. row ***************************

backup_id: 14226039516325379

tool_name: mysqlbackup

error_code: 0

error_message: NO_ERROR

current_time: 2015-01-30 15:45:54

current_state: mysqlbackup locking tables and copying .frm + other engines data.

*************************** 3. row ***************************

backup_id: 14226039516325379

tool_name: mysqlbackup

error_code: 0

error_message: NO_ERROR

current_time: 2015-01-30 15:45:56

current_state: mysqlbackup unlocked the tables.

*************************** 4. row ***************************

backup_id: 14226039516325379

tool_name: mysqlbackup

error_code: 0

error_message: NO_ERROR

current_time: 2015-01-30 15:45:56

current_state: mysqlbackup returns success.

*************************** 5. row ***************************

backup_id: 14226039516325379

tool_name: mysqlbackup

error_code: 0

error_message: NO_ERROR

current_time: 2015-01-30 15:45:56

current_state: mysqlbackup applying log.

5 rows in set (0.00 sec)

这个可以使用--no-history-logging选项来终止。

五、恢复

[root@gflinux gflinux_20150130_16:37:30]# mysqlbackup --backup-dir=/home/backup/gflinux_20150130_16:37:30 copy-back-and-apply-log

MySQL Enterprise Backup version 3.11.1 Linux-2.6.18-92.1.10.el5-i686 [2014/11/04]

mysqlbackup: INFO: Starting with following command line ...

mysqlbackup --backup-dir=/home/backup/gflinux_20150130_16:37:30

copy-back-and-apply-log

mysqlbackup: 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!".

150130 16:58:40 mysqlbackup: INFO: MEB logfile created at /home/backup/gflinux_20150130_16:37:30/meta/MEB_2015-01-30.16-58-40_copy_back_dir_to_datadir.log

mysqlbackup: 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.

mysqlbackup: 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.

mysqlbackup: 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.

--------------------------------------------------------------------

Server Repository Options:

--------------------------------------------------------------------

datadir = /opt/mysql/data

innodb_data_home_dir = /opt/mysql/data

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = /opt/mysql/data

innodb_log_files_in_group = 2

innodb_log_file_size = 50331648

innodb_page_size = Null

innodb_checksum_algorithm = innodb

--------------------------------------------------------------------

Backup Config Options:

--------------------------------------------------------------------

datadir = /home/backup/gflinux_20150130_16:37:30/datadir

innodb_data_home_dir = /home/backup/gflinux_20150130_16:37:30/datadir

innodb_data_file_path = ibdata1:12M:autoextend

innodb_log_group_home_dir = /home/backup/gflinux_20150130_16:37:30/datadir

innodb_log_files_in_group = 2

innodb_log_file_size = 50331648

innodb_page_size = 16384

innodb_checksum_algorithm = innodb

mysqlbackup: INFO: Creating 14 buffers each of size 16777216.

150130 16:58:40 mysqlbackup: INFO: Copy-back-and-apply-log operation starts with following threads

1 read-threads 1 write-threads

mysqlbackup: 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.

150130 16:58:41 mysqlbackup: INFO: Copying /home/backup/gflinux_20150130_16:37:30/datadir/ibdata1.

150130 16:58:41 mysqlbackup: INFO: Copying /home/backup/gflinux_20150130_16:37:30/datadir/mysql/innodb_index_stats.ibd.

150130 16:58:41 mysqlbackup: INFO: Copying /home/backup/gflinux_20150130_16:37:30/datadir/mysql/innodb_table_stats.ibd.

150130 16:58:41 mysqlbackup: INFO: Copying /home/backup/gflinux_20150130_16:37:30/datadir/mysql/slave_master_info.ibd.

150130 16:58:41 mysqlbackup: INFO: Copying /home/backup/gflinux_20150130_16:37:30/datadir/mysql/slave_relay_log_info.ibd.

150130 16:58:41 mysqlbackup: INFO: Copying /home/backup/gflinux_20150130_16:37:30/datadir/mysql/slave_worker_info.ibd.

150130 16:58:41 mysqlbackup: INFO: Copying /home/backup/gflinux_20150130_16:37:30/datadir/test/t.ibd.

150130 16:58:41 mysqlbackup: INFO: Copying the database directory ‘mysql‘

150130 16:58:42 mysqlbackup: INFO: Copying the database directory ‘performance_schema‘

150130 16:58:42 mysqlbackup: INFO: Copying the database directory ‘test‘

150130 16:58:42 mysqlbackup: INFO: Completing the copy of all non-innodb files.

150130 16:58:43 mysqlbackup: INFO: Apply-log operation has already been done on that backup.

150130 16:58:43 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /opt/mysql/data

150130 16:58:43 mysqlbackup: INFO: Copy-back operation completed successfully.

mysqlbackup completed OK! with 3 warnings

[root@gflinux gflinux_20150130_16:37:30]#

六、设置权限

设置mysql权限：

[root@gflinux data]# chown -R mysql:mysql test

[root@gflinux data]# ll

total 110724

-rw-rw---- 1 mysql mysql 56 Jan 27 15:15 auto.cnf

-rw-rw---- 1 mysql mysql 12582912 Jan 30 16:55 ibdata1

-rw-rw---- 1 mysql mysql 50331648 Jan 30 16:55 ib_logfile0

-rw-rw---- 1 mysql mysql 50331648 Jan 27 15:15 ib_logfile1

drwx------ 2 mysql mysql 4096 Jan 30 15:45 mysql

drwx------ 2 mysql mysql 4096 Jan 27 15:15 performance_schema

[root@gflinux data]# ll

total 110752

-rw-rw---- 1 mysql mysql 56 Jan 27 15:15 auto.cnf

-rw-r--r-- 1 root root 264 Jan 30 16:58 backup_variables.txt

-rw-rw---- 1 mysql mysql 12582912 Jan 30 16:58 ibdata1

-rw-rw---- 1 mysql mysql 50331648 Jan 30 16:55 ib_logfile0

-rw-rw---- 1 mysql mysql 50331648 Jan 27 15:15 ib_logfile1

drwx------ 2 mysql mysql 4096 Jan 30 15:45 mysql

drwx------ 2 mysql mysql 4096 Jan 27 15:15 performance_schema

-rw-r--r-- 1 root root 12559 Jan 30 16:58 server-all.cnf

-rw-r--r-- 1 root root 3201 Jan 30 16:58 server-my.cnf

drwx------ 2 root root 4096 Jan 30 16:58 test

七、重启服务

[root@gflinux data]# service mysqld start

Starting MySQL.... [ OK ]

[root@gflinux data]#

mysql> select count(*),now() from t;

+----------+---------------------+

| count(*) | now() |

+----------+---------------------+

| 2 | 2015-01-30 17:05:44 |

+----------+---------------------+

1 row in set (0.00 sec)

mysql> select count(*),now() from t;

+----------+---------------------+

| count(*) | now() |

+----------+---------------------+

| 2 | 2015-01-30 17:06:08 |

+----------+---------------------+

1 row in set (0.00 sec)

MySQL的企业备份（MEB）

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > MySQL的企业备份（MEB）

MySQL的企业备份（MEB）

看完仍有疑问？有类似问题直接问程序猿