meb4.0.3(mysql enterprise backup)原理分析

mysql企业版有两个值得付费的工具：mysql enterprise backup、thread pool。

下面来分析下MEB工作原理：

1.先来个全备

/usr/local/meb5.7/bin/mysqlbackup --user=root --password=root  --with-timestamp  --backup-dir=/tmp/  backup 2>/tmp/meb.log

通过查看mysqlbackup屏幕输出，可以看到先拷贝所有innodb files。

160823 12:35:36 mysqlbackup: INFO: Full Backup operation starts with following threads
                1 read-threads    6 process-threads    1 write-threads160823 12:35:36 mysqlbackup: INFO: Starting to copy all innodb files...
160823 12:35:36 mysqlbackup: INFO: Copying /data/mysql/mysql3306/data/ibdata1 (Barracuda file format).
160823 12:35:36 mysqlbackup: INFO: Found checkpoint at lsn 2530796.
160823 12:35:36 mysqlbackup: INFO: Starting log scan from lsn 2530304.
160823 12:35:36 mysqlbackup: INFO: Copying log...
160823 12:35:36 mysqlbackup: INFO: Log copied, lsn 2530805.
160823 12:35:42 mysqlbackup: INFO: Copying /data/mysql/mysql3306/data/mysql/engine_cost.ibd (Barracuda file format).
160823 12:35:43 mysqlbackup: INFO: Completing the copy of innodb files.

然后开始拷贝binlog文件，拷贝完后锁表（注意最后一个binlog文件在锁表后拷贝）#xtrabackup没有备份binlog文件

160823 12:35:43 mysqlbackup: INFO: Starting to copy Binlog files...
160823 12:35:43 mysqlbackup: INFO: Copying /data/mysql/mysql3306/logs/mysql-bin.000001.
160823 12:35:43 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
160823 12:35:43 mysqlbackup: INFO: Starting to lock all the tables...
160823 12:35:43 mysqlbackup: INFO: All tables are locked and flushed to disk
160823 12:35:43 mysqlbackup: INFO: Copying /data/mysql/mysql3306/logs/mysql-bin.000004.
160823 12:35:43 mysqlbackup: INFO: Completed the copy of binlog files...

最后拷贝非innodb 信息（表结构定义信息、字典信息等）并解锁

160823 12:35:43 mysqlbackup: INFO: Starting to backup all non-innodb files in
        subdirectories of '/data/mysql/mysql3306/data'
160823 12:35:43 mysqlbackup: INFO: Copying the database directory 'zhangshuo'
160823 12:35:43 mysqlbackup: INFO: Completing the copy of all non-innodb files.
160823 12:35:43 mysqlbackup: INFO: All tables unlocked

查看备份期间生成的general log，创建了备份信息表

2016-08-23T04:35:36.640964Z        13 Query     USE mysql
2016-08-23T04:35:36.641747Z        13 Query     CREATE TABLE IF NOT EXISTS mysql.backup_progress( `backup_id` BIGINT NOT NULL, `tool_name` VARCHAR(4096) NOT NULL, `error_code` INT NOT NULL, `error_message` VARCHAR(4096) NOT NULL, `current_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP               ON UPDATE CURRENT_TIMESTAMP,`current_state` VARCHAR(200) NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COLLATE=utf8_bin
2016-08-23T04:35:36.642264Z        13 Prepare   INSERT INTO mysql.backup_progress( backup_id, tool_name, error_code, error_message, current_state ) VALUES (?,?,?,?,?)

2.apply-log后在datadir目录下可以看到恢复的数据

/usr/local/meb5.7/bin/mysqlbackup  --backup-dir=/tmp/2016-08-23_12-35-36/  apply-log

[root@localhost datadir]# ls -lh
总用量 2.6G
-rw-r--r--. 1 root root 1.0K 8月  23 12:35 ibbackup_logfile
-rw-r--r--. 1 root root  362 8月  23 12:35 ib_buffer_pool
-rw-r--r--. 1 root root 1.0G 8月  23 12:35 ibdata1
-rw-r-----. 1 root root 512M 8月  23 12:42 ib_logfile0
-rw-r-----. 1 root root 512M 8月  23 12:42 ib_logfile1
-rw-r-----. 1 root root 512M 8月  23 12:42 ib_logfile2
drwxr-x---. 2 root root 4.0K 8月  23 12:35 mysql
-rw-r--r--. 1 root root  177 8月  23 12:35 mysql-bin.000001
-rw-r--r--. 1 root root  445 8月  23 12:35 mysql-bin.000002
-rw-r--r--. 1 root root  177 8月  23 12:35 mysql-bin.000003
-rw-r--r--. 1 root root 1.4K 8月  23 12:35 mysql-bin.000004
-rw-r--r--. 1 root root   76 8月  23 12:35 mysql-bin.index
drwxr-x---. 2 root root 4.0K 8月  23 12:35 sys
drwxr-x---. 2 root root 4.0K 8月  23 12:35 zhangshuo

3.将apply好的数据恢复到实例

[root@localhost tmp]# /usr/local/meb5.7/bin/mysqlbackup --defaults-file=/etc/my.cnf  --backup-dir=/tmp/2016-08-23_12-35-36/ copy-back

160823 14:02:35 mysqlbackup: INFO: Copying /tmp/2016-08-23_12-35-36/datadir/ibdata1.
 mysqlbackup: Progress in MB: 200 400 600 800 1000 
160823 14:02:43 mysqlbackup: INFO: Copying /tmp/2016-08-23_12-35-36/datadir/mysql/engine_cost.ibd.
160823 14:02:43 mysqlbackup: INFO: Copying /tmp/2016-08-23_12-35-36/datadir/mysql/gtid_executed.ibd.
160823 14:02:43 mysqlbackup: INFO: Copying /tmp/2016-08-23_12-35-36/datadir/mysql/help_category.ibd

160823 14:02:43 mysqlbackup: INFO: Starting to copy Binlog files...
160823 14:02:43 mysqlbackup: INFO: Copying /tmp/2016-08-23_12-35-36/datadir/mysql-bin.000001.
160823 14:02:43 mysqlbackup: INFO: Copying /tmp/2016-08-23_12-35-36/datadir/mysql-bin.000002.

160823 14:02:43 mysqlbackup: INFO: Copying the database directory 'mysql'
160823 14:02:43 mysqlbackup: INFO: Copying the database directory 'sys'
160823 14:02:43 mysqlbackup: INFO: Copying the database directory 'zhangshuo'
160823 14:02:43 mysqlbackup: INFO: Completing the copy of all non-innodb files.
160823 14:02:43 mysqlbackup: INFO: Copying the log file 'ib_logfile0'
 mysqlbackup: Progress in MB: 1200 1400 
160823 14:02:47 mysqlbackup: INFO: Copying the log file 'ib_logfile1'
 mysqlbackup: Progress in MB: 1600 1800 2000 
160823 14:02:54 mysqlbackup: INFO: Copying the log file 'ib_logfile2'

160823 14:03:07 mysqlbackup: INFO: Finished copying backup files to '/data/mysql/mysql3306/data'
mysqlbackup completed OK!

meb4.0.3(mysql enterprise backup)原理分析

相关推荐