mysql的热备份innobackupex问题
dbdao吧
全部回复
仅看楼主
level 3
hebin_25 楼主
innobackupex 只能恢复到备份那一时刻的数据么?
我测试怎么只能恢复到那一时刻,后面再写的都没有了? 不会自己读log-bin目录里的日志么
2015年08月24日 08点08分 1
level 9
请查看Percona网站中,Preparing a Full Backup with innobackupex一文。其中提到关于innobackupex --apply-log的使用。
---------
After creating a backup, the data is not ready to be restored. There might be uncommitted transactions to be undone or transactions in the logs to be replayed. Doing those pending operations will make the data files consistent and it is the purpose of the prepare stage. Once this has been done, the data is ready to be used.
To prepare a backup with innobackupex you have to use the --apply-log and the path to the backup directory as an argument:
----------
2015年08月28日 05点08分 2
用了,就是比如我在10点钟做了个备份,然后,备份完了,11点我插入了个数据,然后用innobackupex --apply-log 恢复备份文件 然后停库删除数据文件,再将恢复好的备份innobackupex copy-back回 去 发现 还是10点备份时的数据 11点的是没有的,只能用mysqlbinlog 一个文件一个文件的应用回去
2015年08月28日 07点08分
@hebin_25 innobackupex --apply-log 中对应目录中已经放置了11点你插入数据的binlog了?
2015年08月29日 07点08分
开启写binlog了,需要手动把binlog目录的binlog文件 拷贝到备份的目录?
2015年09月08日 07点09分
@biotwang 开启写binlog了,需要手动把binlog目录的binlog文件 拷贝到备份的目录?
2015年09月08日 07点09分
level 3
hebin_25 楼主
以下是我的测试:
备份
[mysql@gfs1 log]$ innobackupex --user=root --password=123456 /home/mysql/innbackupdir
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
150824 07:46:17 innobackupex: Executing a version check against the server...
150824 07:46:17 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
150824 07:46:17 innobackupex: Connected to MySQL server
150824 07:46:17 innobackupex: Done.
150824 07:46:17 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
150824 07:46:17 innobackupex: Connected to MySQL server
150824 07:46:17 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!".
innobackupex: Using server version 5.6.25-log
innobackupex: Created backup directory /home/mysql/innbackupdir/2015-08-24_07-46-17
150824 07:46:17 innobackupex: Starting ibbackup with command: xtrabackup --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/home/mysql/innbackupdir/2015-08-24_07-46-17 --tmpdir=/tmp --extra-lsndir='/tmp'
innobackupex: Waiting for ibbackup (pid=13613) to suspend
innobackupex: Suspend file '/home/mysql/innbackupdir/2015-08-24_07-46-17/xtrabackup_suspended_2'
xtrabackup version 2.2.11 based on MySQL server 5.6.24 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysql/innodbdb
xtrabackup: open files limit requested 65536, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = /usr/local/mysql/innodbdb
xtrabackup: innodb_data_file_path = ibdata1:2G;ibdata2:16M:autoextend
xtrabackup: innodb_log_group_home_dir = /usr/local/mysql/innodbdb
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 1073741824
xtrabackup: using O_DIRECT
>> log scanned up to (1631591)
xtrabackup: Generating a list of tablespaces
[01] Copying /usr/local/mysql/innodbdb/ibdata1 to /home/mysql/innbackupdir/2015-08-24_07-46-17/ibdata1
>> log scanned up to (1631591)
>> log scanned up to (1631591)
>> log scanned up to (1631591)
>> log scanned up to (1631591)
>> log scanned up to (1631591)
>> log scanned up to (1631591)
>> log scanned up to (1631591)
>> log scanned up to (1631591)
>> log scanned up to (1631591)
>> log scanned up to (1631591)
>> log scanned up to (1631591)
>> log scanned up to (1631591)
>> log scanned up to (1631591)
>> log scanned up to (1631591)
>> log scanned up to (1631591)
>> log scanned up to (1631591)
>> log scanned up to (1631591)
>> log scanned up to (1631591)
>> log scanned up to (1631591)
>> log scanned up to (1631591)
[01] ...done
[01] Copying /usr/local/mysql/innodbdb/ibdata2 to /home/mysql/innbackupdir/2015-08-24_07-46-17/ibdata2
[01] ...done
[01] Copying ./test/aaa.ibd to /home/mysql/innbackupdir/2015-08-24_07-46-17/test/aaa.ibd
[01] ...done
[01] Copying ./mysql/slave_master_info.ibd to /home/mysql/innbackupdir/2015-08-24_07-46-17/mysql/slave_master_info.ibd
[01] ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /home/mysql/innbackupdir/2015-08-24_07-46-17/mysql/innodb_table_stats.ibd
[01] ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /home/mysql/innbackupdir/2015-08-24_07-46-17/mysql/innodb_index_stats.ibd
[01] ...done
[01] Copying ./mysql/slave_worker_info.ibd to /home/mysql/innbackupdir/2015-08-24_07-46-17/mysql/slave_worker_info.ibd
[01] ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /home/mysql/innbackupdir/2015-08-24_07-46-17/mysql/slave_relay_log_info.ibd
[01] ...done
>> log scanned up to (1631591)
xtrabackup: Creating suspend file '/home/mysql/innbackupdir/2015-08-24_07-46-17/xtrabackup_suspended_2' with pid '13613'
150824 07:46:39 innobackupex: Continuing after ibbackup has suspended
150824 07:46:39 innobackupex: Executing FLUSH TABLES...
150824 07:46:39 innobackupex: Executing FLUSH TABLES WITH READ LOCK...
150824 07:46:39 innobackupex: All tables locked and flushed to disk
150824 07:46:39 innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/usr/local/mysql/innodbdb/'
innobackupex: Backing up file '/usr/local/mysql/innodbdb//test/aaa.frm'
innobackupex: Backing up files '/usr/local/mysql/innodbdb//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
innobackupex: Backing up files '/usr/local/mysql/innodbdb//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
>> log scanned up to (1631591)
150824 07:46:40 innobackupex: Finished backing up non-InnoDB tables and files
150824 07:46:40 innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
150824 07:46:40 innobackupex: Waiting for log copying to finish
xtrabackup: The latest check point (for incremental): '1631591'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1631591)
xtrabackup: Creating suspend file '/home/mysql/innbackupdir/2015-08-24_07-46-17/xtrabackup_log_copied' with pid '13613'
xtrabackup: Transaction log of lsn (1631591) to (1631591) was copied.
150824 07:46:41 innobackupex: All tables unlocked
innobackupex: Backup created in directory '/home/mysql/innbackupdir/2015-08-24_07-46-17'
innobackupex: MySQL binlog position: filename 'mysql-bin.000029', position 624
150824 07:46:41 innobackupex: Connection to database server closed
150824 07:46:41 innobackupex: completed OK!
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000031 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000031 | 573 | | | |
+------------------+----------+--------------+------------------+-------------------+
然后
insert into aaa values(2);
commit;
2015年09月09日 07点09分 4
level 3
hebin_25 楼主
这样太麻烦,mysql就不能在恢复时候直接读取在线日志么?发现他不光在线redolog不读 连binlog都不读?
2015年09月09日 07点09分 6
level 9
[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 549, file name mysql-bin.000029
也就是说,当时binlog info文件中应该没有之后的日志文件记录,当然不会去读了。
2015年09月10日 03点09分 7
那还是不能做到像oracle恢复一样自动读取归档和online redo log,有没有方法让他自动都读了呢? 手动改那个备份里面的binlog info文件?
2015年09月11日 01点09分
这个真没试过[吐舌]你可以去试试,可以手工写到info文件测试下。
2015年09月11日 03点09分
@biotwang 奥 传统方法是跟我这个测试一样对吧,恢复到备份时,再用mysqlbinlog 这样一个binlog一个binlog这样的导入吧
2015年09月11日 05点09分
1