现在我们给MySQL做备份的时候经常会考虑到数据量的增长,数据量较小的时候用mysqldump,随着数据量越来越大mysqldump也就不太合适了.第一不支持增量备份,第二恢复的时候也较慢.这里推荐使用Percona公司的XtraBackup.
简单介绍一下热备份工具XtraBackup,它是Percona公司推出的一款热备份工具,备份的时候不影响数据读写操作,是商业工具HotBackup的一个替代(现在应该叫MySQL Enterprise Backup这个工具不止可以备份InnoDB还可以备份MyISAM等等)
XtraBackup有两个工具:xtrabackup和innobackupex
xtrabackup本身只能备份InnoDB和XtraDB,不能备份MyISAM.
innobackupex本身是Hot Backup脚本修改而来,同时可以备份MyISAM和InnoDB,但是备份MyISAM需要加读锁.
下面简单来实验XtraBackup备份
一、安装XtraBackup(这步略过不说了.^_^)
二、使用XtraBackup备份DB
(1)全库备份
[mysql@localhost mysql]$ innobackupex-1.5.1 --user=root --password=xxxxxxx /mysql/backup --no-timestamp --parallel=4
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.
Get the latest version of Percona XtraBackup, documentation, and help resources:
140226 16:09:06 innobackupex-1.5.1: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
140226 16:09:06 innobackupex-1.5.1: Connected to MySQL server
140226 16:09:06 innobackupex-1.5.1: Executing a version check against the server...
140226 16:09:06 innobackupex-1.5.1: Done.
140226 16:09:12 innobackupex-1.5.1: Finished backing up non-InnoDB tables and files
..........
..........
..........
140226 16:09:12 innobackupex-1.5.1: Waiting for log copying to finish
xtrabackup: The latest check point (for incremental): '183748588'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (183748588)
xtrabackup: Creating suspend file '/mysql/backup/xtrabackup_log_copied' with pid '3759'
xtrabackup: Transaction log of lsn (183748588) to (183748588) was copied.
140226 16:09:13 innobackupex-1.5.1: All tables unlocked
innobackupex-1.5.1: Backup created in directory '/mysql/backup'
innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000021', position 1649
140226 16:09:13 innobackupex-1.5.1: Connection to database server closed
140226 16:09:13 innobackupex-1.5.1: completed OK!
[mysql@localhost mysql]$
查看一下备份出来的文件
[mysql@localhost mysql]$ ls
backup data log mysqld.log mysqld.pid slowquery.log
[mysql@localhost mysql]$ cd backup/
[mysql@localhost backup]$ ls
backup-my.cnf ibdata1 mysql percona performance_schema test xtrabackup_binary xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_logfile
尝试恢复一下
恢复前的准备
[mysql@localhost backup]$ innobackupex --apply-log --use-memory=10M /mysql/backup/
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.
Get the latest version of Percona XtraBackup, documentation, and help resources:
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
140226 16:12:16 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-file="/mysql/backup/backup-my.cnf" --defaults-group="mysqld" --prepare --target-dir=/mysql/backup --use-memory=10M --tmpdir=/tmp
xtrabackup_56 version 2.1.6 for MySQL server 5.6.11 Linux (x86_64) (revision id: 702)
xtrabackup: cd to /mysql/backup
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(183748588)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
.......
.......
.......
.......
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 10.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=183749693
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.11 started; log sequence number 183750156
[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 1426, file name mysql-bin.000021
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 183751245
140226 16:12:22 innobackupex: completed OK!
恢复备份
停止MySQL,清空DATADIR
[mysql@localhost backup]$ mysqladmin -uroot -pxxxxxx shutdown
Warning: Using a password on the command line interface can be insecure.
140226 16:30:03 mysqld_safe mysqld from pid file /mysql/mysqld.pid ended
[1]+ Done mysqld_safe (wd: ~)
(wd now: /mysql/backup)
[mysql@localhost mysql]$ mv data/ data.bak
[mysql@localhost mysql]$ ls
backup data.bak log mysqld.log slowquery.log
[mysql@localhost mysql]$ mkdir -p data
[mysql@localhost mysql]$ ls
backup data data.bak log mysqld.log slowquery.log
恢复备份数据
[mysql@localhost mysql]$ innobackupex-1.5.1 --copy-back --rsync /mysql/backup/
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.
Get the latest version of Percona XtraBackup, documentation, and help resources:
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex-1.5.1
prints "completed OK!".
innobackupex-1.5.1: Starting to copy files in '/mysql/backup'
innobackupex-1.5.1: back to original data directory '/mysql/data'
innobackupex-1.5.1: Copying '/mysql/backup/xtrabackup_binlog_pos_innodb' to '/mysql/data/xtrabackup_binlog_pos_innodb'
........
........
........
........
innobackupex-1.5.1: Starting to copy InnoDB log files
innobackupex-1.5.1: in '/mysql/backup'
innobackupex-1.5.1: back to original InnoDB log directory '/mysql/data'
innobackupex-1.5.1: Copying '/mysql/backup/ib_logfile0' to '/mysql/data/ib_logfile0'
innobackupex-1.5.1: Copying '/mysql/backup/ib_logfile1' to '/mysql/data/ib_logfile1'
innobackupex-1.5.1: Finished copying back files.
140226 16:32:59 innobackupex-1.5.1: completed OK!
启动MySQL
[mysql@localhost mysql]$ mysqld_safe &
[1] 3990
[mysql@localhost mysql]$ 140226 16:34:12 mysqld_safe Logging to '/mysql/mysqld.log'.
140226 16:34:12 mysqld_safe Starting mysqld daemon with databases from /mysql/data
(2)增量备份
创建0级备份
[mysql@localhost mysql]$ innobackupex-1.5.1 --user=root --password=xxxxx /mysql/hotbackup/zero --no-timestamp --parallel=4
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.
.....
.....
.....
innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000022', position 120
140226 16:38:11 innobackupex-1.5.1: Connection to database server closed
140226 16:38:11 innobackupex-1.5.1: completed OK!
创建1级备份
mysql> select * from t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)
mysql>
mysql> insert into t values (5);
Query OK, 1 row affected (0.17 sec)
[mysql@localhost hotbackup]$ innobackupex-1.5.1 --user=root --password=xxxxxx --incremental /mysql/hotbackup/one --incremental-basedir=/mysql/hotbackup/zero --no-timestamp --parallel=4
.........
.........
.........
.........
基于1级备份,创建2级备份
mysql> insert into t values (6);
Query OK, 1 row affected (0.03 sec)
mysql> select * from t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+----+
6 rows in set (0.00 sec)
mysql>
[mysql@localhost hotbackup]$ innobackupex-1.5.1 --user=root --password=xxxxx --incremental /mysql/hotbackup/two --incremental-basedir=/mysql/hotbackup/one --no-timestamp --parallel=4
.........
.........
.........
.........
恢复前的准备,应用日志中提交的事物.
[mysql@localhost hotbackup]$ innobackupex-1.5.1 --apply-log --redo-only --use-memory=10M /mysql/hotbackup/zero
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.
Get the latest version of Percona XtraBackup, documentation, and help resources:
..........
..........
..........
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 183751255
140226 16:50:32 innobackupex-1.5.1: completed OK!
将1级备份应用到0级备份
[mysql@localhost hotbackup]$ innobackupex-1.5.1 --apply-log --redo-only --use-memory=10M /mysql/hotbackup/zero --incremental-dir=/mysql/hotbackup/one/
将2级备份应用到0级备份
[mysql@localhost hotbackup]$ innobackupex-1.5.1 --apply-log --redo-only --use-memory=10M /mysql/hotbackup/zero --incremental-dir=/mysql/hotbackup/two/
当应用0级备份恢复增量备份的时候记住要使用--redo-only参数
当恢复完增量备份,在一次应用0级备份回滚没有提交的事物
innobackupex --apply-log --use-memory=10M /mysql/hotbackup/zero
停止MySQL恢复备份,启动MySQL
mysql@localhost hotbackup]$ mysqladmin -u root -proot shutdown
Warning: Using a password on the command line interface can be insecure.
[mysql@localhost hotbackup]$ 140226 17:00:40 mysqld_safe mysqld from pid file /mysql/mysqld.pid ended
[1]+ Done mysqld_safe (wd: /mysql)
(wd now: /mysql/hotbackup)
[mysql@localhost hotbackup]$
[mysql@localhost mysql]$ mv data data.bak1
[mysql@localhost mysql]$ mkdir data
[mysql@localhost mysql]$ innobackupex-1.5.1 --copy-back --rsync /mysql/hotbackup/zero
[mysql@localhost mysql]$ mysqld_safe &
[1] 6722
[mysql@localhost mysql]$ 140226 16:34:12 mysqld_safe Logging to '/mysql/mysqld.log'.
140226 16:34:12 mysqld_safe Starting mysqld daemon with databases from /mysql/data
1级备份恢复完毕,看看数据是否正常.查看一下t表
mysql> select * from t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
5 rows in set (0.00 sec)
mysql>
XtraBackup选择性备份
选择性备份有三个参数
--databases=LIST
例子:--databases="mysql percona.t"
--tables-file=FILE
例子:--tables-file=tables.sql
[mysql@localhost ~]$ cat tables.sql
percona.check
--include=REGEXP
例子:--include='^database(7|8)\.sorts'
实验中涉及到的参数英文解释如下:
--apply-log
Prepare a backup in BACKUP-DIR by applying the transaction log file
named "xtrabackup_logfile" located in the same directory. Also,
create new transaction logs. The InnoDB configuration is read from
the file "backup-my.cnf".
--copy-back
Copy all the files in a previously made backup from the backup
directory to their original locations.
--incremental
This option tells xtrabackup to create an incremental backup, rather
than a full one. It is passed to the xtrabackup child process. When
this option is specified, either --incremental-lsn or
--incremental-basedir can also be given. If neither option is given,
option --incremental-basedir is passed to xtrabackup by default, set
to the first timestamped backup directory in the backup base
directory.
--no-timestamp
This option prevents creation of a time-stamped subdirectory of the
BACKUP-ROOT-DIR given on the command line. When it is specified, the
backup is done in BACKUP-ROOT-DIR instead.
--redo-only
This option should be used when preparing the base full backup and
when merging all incrementals except the last one. This option is
passed directly to xtrabackup's --apply-log-only option. This forces
xtrabackup to skip the "rollback" phase and do a "redo" only. This
is necessary if the backup will have incremental changes applied to
it later. See the xtrabackup documentation for details.
--rsync
Uses the rsync utility to optimize local file transfers. When this
option is specified, innobackupex uses rsync to copy all non-InnoDB
files instead of spawning a separate cp for each file, which can be
much faster for servers with a large number of databases or tables.
This option cannot be used together with --stream.
三、XtraBackup备份从库
使用XtraBackup备份从库的时候可以使用以下两个参数
--safe-slave-backup
此参数在备份的时候会暂停Slave的SQL线程,等到没有打开的临时表的时候开始备份.备份完成后SQL线程会自动启动,这样来保证备份一致性.
--safe-slave-backup-timeout
此参数设置Slave_open_temp_tables超时时间,默认300
--slave-info
此参数在备份的时候会记录主Master的BINLOG位置和名称,它会把信息记录到"xtrabackup_slave_info" ,作为CHANGE MASTER的时候从xtrabackup_slave_info查看二进制日志的位置.
(未完)^_^
阅读(3994) | 评论(0) | 转发(2) |
