前言
在xtrabackup使用的过程中,主要会有以下几个文件生成,本文就这几个文件的内容以及作用进行记录和说明。
xtrabackup_binlog_info xtrabackup_binlog_pos_innodb xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile xtrabackup_slave_info backup-my.cnf
实验过程
master [localhost] {msandbox} ((none)) > show master status\G *************************** 1. row *************************** File: mysql-bin.000011 Position: 1549 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)
master [localhost] {msandbox} (test) > insert into test values(1,'zhangsan'); Query OK, 1 row affected (0.04 sec) master [localhost] {msandbox} (test) > insert into test values(2,'lisi'); Query OK, 1 row affected (0.04 sec)
master [localhost] {msandbox} ((none)) > show master status\G *************************** 1. row *************************** File: mysql-bin.000011 Position: 2140 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)
innobackupex --socket=/tmp/mysql_sandbox21489.sock \ --user=msandbox \ --password=msandbox \ --no-timestamp \ --slave-info \ /data/full
170305 21:24:27 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". .... xtrabackup: using the following InnoDB configuration: xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 50331648 # 这里的信息会被记录到backup-my.cnf 文件中,backup-my.cnf 是启动备份集所需要的最小参数。 .... xtrabackup: Transaction log of lsn (2142985143) to (2142987473) was copied. # 我们可以看到我们当时插入的数据的redo log已经被拷贝 170305 21:24:33 completed OK!
master [localhost] {msandbox} (test) > show engine innodb status\G .... --- LOG --- Log sequence number 2142987473 Log flushed up to 2142987473 Pages flushed up to 2142987473 Last checkpoint at 2142987473 Max checkpoint age 80826164 Checkpoint age target 78300347 ...
[root@COS-DEV-MySQL full]# cat xtrabackup_binlog_info mysql-bin.000011 2140
[root@COS-DEV-MySQL full]# cat xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 # 由于是全量备份,所以数据是从LSN=0开始的 to_lsn = 2142987473 last_lsn = 2142987473 compact = 0 recover_binlog_info = 0
[root@COS-DEV-MySQL full]# cat xtrabackup_info uuid = 231d9d80-01a9-11e7-bcb1-080027a1fd1c name = tool_name = innobackupex tool_command = --socket=/tmp/mysql_sandbox21489.sock --user=msandbox --password=... --no-timestamp --slave-info /data/full tool_version = 2.3.7 ibbackup_version = 2.3.7 server_version = 5.6.24-72.2-log start_time = 2017-03-05 21:39:14 end_time = 2017-03-05 21:39:20 lock_time = 0 binlog_pos = filename 'mysql-bin.000011', position '2140' innodb_from_lsn = 0 innodb_to_lsn = 2142987473 partial = N incremental = N format = file compact = N compressed = N encrypted = N
[root@COS-DEV-MySQL full]# innobackupex --apply-log . 170305 21:42:17 innobackupex: Starting the apply-log operation ... InnoDB: Shutdown completed; log sequence number 2142991038 170305 21:42:21 completed OK!
[root@COS-DEV-MySQL full]# cat xtrabackup_binlog_pos_innodb mysql-bin.000011 2140 [root@COS-DEV-MySQL full]# cat xtrabackup_binlog_info mysql-bin.000011 2140
master [localhost] {msandbox} (test) > show create table test_myisam\G *************************** 1. row *************************** Table: test_myisam Create Table: CREATE TABLE `test_myisam` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) master [localhost] {msandbox} (test) > insert into test_myisam values(2,'aa'); Query OK, 1 row affected (0.00 sec) .... master [localhost] {msandbox} (test) > show master status\G *************************** 1. row *************************** File: mysql-bin.000011 Position: 3120 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) [root@COS-DEV-MySQL master]# innobackupex --socket=/tmp/mysql_sandbox21489.sock \ > --user=msandbox \ > --password=msandbox \ > --no-timestamp \ > --slave-info \ > /data/full2 170305 22:07:42 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". MySQL binlog position: filename 'mysql-bin.000011', position '3120' 170305 22:07:49 [00] Writing backup-my.cnf 170305 22:07:49 [00] ...done 170305 22:07:49 [00] Writing xtrabackup_info 170305 22:07:49 [00] ...done xtrabackup: Transaction log of lsn (2142987473) to (2142987473) was copied. # 我们发现myisam表由于是非事务型存储引擎,中间的事务日志没有产生,因此没有产生redo拷贝 # 此时只有xtrabackup_binlog_info文件生成,没有xtrabackup_binlog_pos_innodb [root@COS-DEV-MySQL full2]# cat xtrabackup_binlog_info mysql-bin.000011 3120 [root@COS-DEV-MySQL full2]# innobackupex --apply-log . .... InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 2142990651 170305 22:08:39 completed OK! [root@COS-DEV-MySQL full2]# cat xtrabackup_binlog_pos_innodb mysql-bin.000011 2140 [root@COS-DEV-MySQL full2]# cat xtrabackup_binlog_info mysql-bin.000011 3120 # 我们发现xtrabackup_binlog_pos_innodb中的位置和xtrabackup_binlog_info不一样,因为xtrabackup_binlog_pos_innodb只是记录innodb和xtradb的binlog位点,myisam产生的binlog不会计算在内,因此xtrabackup_binlog_pos_innodb的信息和第一次全备的xtrabackup_binlog_info文件中的信息是一致的。
所以如果存在多种存储引擎,需要利用xtrabackup_binlog_info 来确定备份的完成那个时刻的binlog位点。
[root@COS-DEV-MySQL full]# cat xtrabackup_checkpoints backup_type = full-prepared from_lsn = 0 to_lsn = 2142987473 last_lsn = 2142987473 compact = 0 recover_binlog_info = 0
slave1 [localhost] {msandbox} ((none)) > show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: rsandbox Master_Port: 21489 Connect_Retry: 60 Master_Log_File: mysql-bin.000011 Read_Master_Log_Pos: 3120 # slave1 和主库是实时同步,位点为11号binlog的3120位点。 slave1 [localhost] {msandbox} ((none)) > show master status\G *************************** 1. row *************************** File: mysql-bin.000007 Position: 120 # 此时的slave的 binlog位点
[root@COS-DEV-MySQL full_slave]# cat xtrabackup_binlog_info mysql-bin.000007 120 # xtrabackup_binlog_info 记录的是从库的binlog位点。 [root@COS-DEV-MySQL full_slave]# [root@COS-DEV-MySQL full_slave]# cat xtrabackup_slave_info CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=3120 # 发现xtrabackup_slave_info记录的是主库的binlog信息,该信息可以用来通过从库创建一个从库,然后指向主库!非常棒!该信息需要在备份的时候加上 --slave-info .
小结
在工作中,突然对某个文件的意义有点不大明确,导致差点踩坑,好尴尬????
通过实验明确了各个文件的含义,防止犯错。