利用XtraBackup给MYSQL热备(基于数据文件)
利用 XtraBackup 快速配置主从服务器
这XtraBackup 个主要包含2个命令 xtrabackup 和 innobackupex 第一个是他的主程序 另外个他自己封装的脚本一般用脚本来完成备份
这是工作笔记 比较凌乱 凑合着看吧
获取XtraBackup
获取XtraBackup(其中1.6里面有个WIN版本的 测试后很头疼各种BUG未解决 WIN下的MYSQL还是使用远程备份吧)
http://www.percona.com/downloads/XtraBackup/
2 |
mkdir -p $HOME /downloads ; cd $HOME /downloads |
安装
5 |
rpm -qip percona-xtrabackup-2.1.5-680.rhel6.x86_64.rpm |
7 |
rpm -ivh percona-xtrabackup-2.1.5-680.rhel6.x86_64.rpm |
9 |
rpm -e percona-xtrabackup |
2 |
error: Failed dependencies: |
3 |
libaio.so.1()(64bit) is needed by percona-xtrabackup-2.1.5-680.rhel6.x86_64 |
4 |
libaio.so.1(LIBAIO_0.1)(64bit) is needed by percona-xtrabackup-2.1.5-680.rhel6.x86_64 |
5 |
libaio.so.1(LIBAIO_0.4)(64bit) is needed by percona-xtrabackup-2.1.5-680.rhel6.x86_64 |
6 |
perl(Time::HiRes) is needed by percona-xtrabackup-2.1.5-680.rhel6.x86_64 |
3 |
xtrabackup version 2.1.5 for Percona Server 5.1.70 unknown-linux-gnu (x86_64) (revision id : 680) |
2 |
开始备份(如果在MY.CNF里面配置了[XtraBackup]节的用户和密码就可以使用不带密码的方式备份) |
3 |
BACKUP_DIR= "/home/ftp/b/backup_data/wwwroot/2013/11/23/2" |
6 |
xtrabackup --backup --datadir= /var/lib/mysql/ --target- dir =$BACKUP_DIR |
7 |
xtrabackup --backup --user=root --password=密码 --datadir= /var/lib/mysql/ --target- dir = /home/ftp/b/backup_data/wwwroot/2013/11/23/-4 |
1 |
innobackupex --user=root --password=密码 --defaults- file = /etc/my .cnf /home/ftp/b/backup_data/wwwroot/2013/11/23/2 .1 |
3 |
innobackupex: Connected to MySQL server |
4 |
innobackupex: Error: Support for MySQL 5.1 with builtin InnoDB (not the plugin) was removed in Percona XtraBackup 2.1. The last version to support MySQL 5.1 with builtin InnoDB was Percona XtraBackup 2.0. |
2 |
rpm -e percona-xtrabackup |
2 |
mkdir -p $HOME /downloads ; cd $HOME /downloads |
3 |
lftp -c pget -n 30 http: //www .percona.com /redir/downloads/XtraBackup/XtraBackup-2 .0.0 /RPM/rhel6/x86_64/percona-xtrabackup-2 .0.0-417.rhel6.x86_64.rpm |
2 |
rpm -qip percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm |
2 |
rpm -ivh percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm |
3 |
warning: percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm: Header V4 DSA /SHA1 Signature, key ID cd2efd2a: NOKEY |
7 |
xtrabackup version 2.0.0 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id : 417) |
测试备份
1 |
innobackupex --user=root --password=密码 --defaults- file = /etc/my .cnf /home/ftp/b/backup_data/wwwroot/ |
好 这下对鸟。。。
01 |
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy |
02 |
and Percona Inc 2009-2012. All Rights Reserved. |
04 |
This software is published under |
05 |
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. |
07 |
131123 09:22:25 innobackupex: Starting mysql with options: --defaults- file = ‘/etc/my.cnf‘ --password=xxxxxxxx --user= ‘root‘ --unbuffered -- |
08 |
131123 09:22:25 innobackupex: Connected to database with mysql child process (pid=30774) |
09 |
131123 09:22:31 innobackupex: Connection to database server closed |
10 |
IMPORTANT: Please check that the backup run completes successfully. |
11 |
At the end of a successful backup run innobackupex |
12 |
prints "completed OK!" . |
14 |
innobackupex: Using mysql Ver 14.14 Distrib 5.1.69, for redhat-linux-gnu (x86_64) using readline 5.1 |
15 |
innobackupex: Using mysql server version Copyright (c) 2000, 2013, Oracle and /or its affiliates. All rights reserved. |
17 |
innobackupex: Created backup directory /home/ftp/b/backup_data/wwwroot/2013-11-23_09-22-31 |
18 |
131123 09:22:31 innobackupex: Starting mysql with options: --defaults- file = ‘/etc/my.cnf‘ --password=xxxxxxxx --user= ‘root‘ --unbuffered -- |
19 |
131123 09:22:31 innobackupex: Connected to database with mysql child process (pid=30841) |
20 |
131123 09:22:33 innobackupex: Connection to database server closed |
22 |
131123 09:22:33 innobackupex: Starting ibbackup with command : xtrabackup_51 --defaults- file = "/etc/my.cnf" --backup --suspend-at-end --target- dir = /home/ftp/b/backup_data/wwwroot/2013-11-23_09-22-31 |
23 |
innobackupex: Waiting for ibbackup (pid=30861) to suspend |
24 |
innobackupex: Suspend file ‘/home/ftp/b/backup_data/wwwroot/2013-11-23_09-22-31/xtrabackup_suspended‘ |
26 |
xtrabackup_51 version 2.0.0 for MySQL server 5.1.59 unknown-linux-gnu (x86_64) (revision id : 417) |
27 |
xtrabackup: uses posix_fadvise(). |
28 |
xtrabackup: cd to /var/lib/mysql |
29 |
xtrabackup: Target instance is assumed as followings. |
30 |
xtrabackup: innodb_data_home_dir = ./ |
31 |
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend |
32 |
xtrabackup: innodb_log_group_home_dir = ./ |
33 |
xtrabackup: innodb_log_files_in_group = 2 |
34 |
xtrabackup: innodb_log_file_size = 5242880 |
35 |
>> log scanned up to (0 44243) |
36 |
[01] Copying . /ibdata1 to /home/ftp/b/backup_data/wwwroot/2013-11-23_09-22-31/ibdata1 |
39 |
131123 09:22:35 innobackupex: Continuing after ibbackup has suspended |
40 |
131123 09:22:35 innobackupex: Starting mysql with options: --defaults- file = ‘/etc/my.cnf‘ --password=xxxxxxxx --user= ‘root‘ --unbuffered -- |
41 |
131123 09:22:35 innobackupex: Connected to database with mysql child process (pid=30874) |
42 |
131123 09:22:37 innobackupex: Starting to lock all tables... |
43 |
>> log scanned up to (0 44243) |
44 |
>> log scanned up to (0 44243) |
45 |
131123 09:22:47 innobackupex: All tables locked and flushed to disk |
47 |
131123 09:22:47 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI, |
48 |
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in |
49 |
innobackupex: subdirectories of ‘/var/lib/mysql‘ |
50 |
innobackupex: Backing up files ‘/var/lib/mysql/blog/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}‘ (49 files) |
51 |
innobackupex: Backing up file ‘/var/lib/mysql/info/db.opt‘ |
52 |
innobackupex: Backing up files ‘/var/lib/mysql/bd_2kdp_com/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}‘ (49 files) |
53 |
>> log scanned up to (0 44243) |
54 |
innobackupex: Backing up file ‘/var/lib/mysql/www_2kdp_com/db.opt‘ |
55 |
innobackupex: Backing up files ‘/var/lib/mysql/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}‘ (69 files) |
56 |
innobackupex: Backing up file ‘/var/lib/mysql/clean_cache/db.opt‘ |
57 |
innobackupex: Backing up file ‘/var/lib/mysql/union/db.opt‘ |
58 |
innobackupex: Backing up files ‘/var/lib/mysql/movie/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}‘ (49 files) |
59 |
innobackupex: Backing up file ‘/var/lib/mysql/link_2kdp/db.opt‘ |
60 |
innobackupex: Backing up files ‘/var/lib/mysql/silkime_com/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}‘ (295 files) |
61 |
>> log scanned up to (0 44243) |
62 |
innobackupex: Backing up file ‘/var/lib/mysql/copy_site/db.opt‘ |
63 |
innobackupex: Backing up files ‘/var/lib/mysql/sell_bdl/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}‘ (133 files) |
64 |
131123 09:22:59 innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files |
66 |
innobackupex: Resuming ibbackup |
68 |
xtrabackup: The latest check point ( for incremental): ‘0:44243‘ |
69 |
xtrabackup: Stopping log copying thread. |
70 |
.>> log scanned up to (0 44243) |
71 |
>> log scanned up to (0 44243) |
73 |
xtrabackup: Transaction log of lsn (0 44243) to (0 44243) was copied. |
74 |
131123 09:23:01 innobackupex: All tables unlocked |
75 |
131123 09:23:01 innobackupex: Connection to database server closed |
77 |
innobackupex: Backup created in directory ‘/home/ftp/b/backup_data/wwwroot/2013-11-23_09-22-31‘ |
79 |
innobackupex: MySQL binlog position: filename ‘mysql-bin.000001‘ , position 25959 |
80 |
131123 09:23:01 innobackupex: completed OK! |
04 |
xtrabackup_binlog_info |
07 |
xtrabackup_checkpoints |
09 |
cat xtrabackup_binlog_info |
10 |
mysql-bin.000001 25959 |
1 |
BACKUP_DIR= "/home/ftp/b/backup_data/wwwroot" |
5 |
LOG= "MySQL_Last_Backup.LOG" |
6 |
LAST_GZIP_BACKUP= "LASTBACKUP.TAR.GZ" |
记录备份日志
1 |
innobackupex --user=$USER --password=$PSWD --slave-info --defaults- file =$MY $BACKUP_DIR/ 2>$BACKUP_DIR/$LOG |
gzip压缩备份
1 |
innobackupex --user=$USER --password=$PSWD --slave-info --defaults- file =$MY $BACKUP_DIR/ --stream= tar | gzip >$BACKUP_DIR/$LAST_GZIP_BACKUP |
解压这个包看看或者直接SCP到从机
1 |
tar xvzfi LASTBACKUP.TAR.GZ |
2 |
cat xtrabackup_binlog_info |
在从服务器上解压这个后 开始配置同步
总结 这个东西的封装后的脚本是很强大 用来做主从配置也很快 但是有个问题 就是不支持远程备份。你比如说 你想从Win服务器备份到Linux的就蛋疼了。这个软件的主要文档和参考资料的环境几乎都是基于LINUX的。WIN下着实蛋疼。。。