mysql热备份xtrabackup

转载自 梦里香巴拉
最终编辑 lemoncoketea
XtraBackup 是一个用来备份 MySQL 的 InnoDB 数据库的开源工具。
开发语言： C/C++
项目主页： https://launchpad.net/percona-xtrabackup/
文档地址： http://www.percona.com/docs/wiki/percona-xtrabackup:start
下载地址：http://www.percona.com/mysql/xtrabackup/0.7/
example:vi ~/xtrabackup_cp.sh
#!/bin/sh
# echo "in master host xtrabackup"
#xtrabackup -- --prepare --target-dir=/data/dbbak/xtrabackup
#xtrabackup --prepare --target-dir=/data/dbbak/xtrabackup
#echo "scp data"
#scp -r xtrabackup root@10.x.x.x:/data/dbbak/
#scp -r mysql root@10.x.x.x:/data/dbbak/xtrabackup
#scp ./*.frm root@10.x.x.x:/data/var/app/
echo "copy xtrbackup data"
cd /data/dbbak/xtrabackup
echo "cp -R ./ibdata* /data/ibdata/"
cp -R ./ibdata* /data/ibdata/
echo "cp -R ./ib_logfile* ./data/iblogs/"
cp -R ./ib_logfile* ./data/iblogs/
echo "copy mysql data"
cp -R /data/dbbak/xtrabackup/app /data/var/
echo "chown -R mysql.mysql /data/*"
chown -R mysql.mysql /data/*

#grant reload,Super,REPLICATION SLAVE on *.* to 'fbrep'@'10.x.x.x' identified by 'xxxxxxx';
#change master to master_host='parker',master_user='fbrep',master_password='4rep0u1y',master_log_file='fb-bin.000272',master_log_po
s=xxx #only one time important!!
#slave start;

故障处理

#handle error mysql can't start, Dubplicate key,
#InnoDB: Doing recovery: scanned up to log sequence number 194 4057720832
#Error 'Duplicate entry '720778891-24997-708487842' for key 1' on query. Default database: 'app'. Query: 'insert into app_invitefrie
nd (app_id, from_id, from_name, to_id, to_name, invitation_id) values(24997,708487842,'36')'
#killall -9 mysqld
#tail -200 oneal-slow.log
#mysqlbinlog fb-bin.000003 | tail -200
#tail -200 oneal.err
#/usr/bin/mysqld_safe &/etc/init.d/mysql restart;
#rm -rf fb-*.* relay-* master.info
#rm -rf app/*
#rm -f /data/ibdata/*
#rm -f /data/iblogs/*
#cd /data/dbbak/xtrabackup
#cp -R ./ibdata* /data/ibdata/
#cp -R ./ib_logfile* /data/iblogs/
#ls -ll /data/var/app | grep "Jun 21"
# cp app_bean_order.ibd app_hourly_active_user_stat.ibd app_outsite_invitation.ibd /data/var/app/
#chown -R mysql.mysql /data/*
#
echo "mysql command"
# grant all privileges on app.* to 'ipo2008'@'10.22.198.85' identified by 'meiguiyuan';
#flush privileges;
#show slave status\G;
#./insert_app_active_user_log_data.sh | tee insert_app_active_user_log_data.log

安装Mysql

#!/bin/sh
echo "install mysql sh"
#scp taohuixiang@10.x.xx.x:/home/taohuixiang/MySQL-client-standard-5.0.27-0.rhel4.x86_64.rpm ./
#scp taohuixiang@10.x.xx.x:/home/taohuixiang/MySQL-server-standard-5.0.27-0.rhel4.x86_64.rpm ./
#scp taohuixiang@10.x.xx.xx:/home/taohuixiang/soft_bk/my.cnf.exmple ./
echo "1. bak old my.cnf"
mv /etc/my.cnf /etc/my.cnf_bk
echo "2. remove old mysql"
#killall -9 mysqld
#/etc/init.d/mysql start
rpm -e MySQL-client-standard
rpm -e MySQL-server-standard
echo "3. install mysql "
rpm -Uvh ~/MySQL-client-standard-5.0.27-0.rhel4.x86_64.rpm
rpm -Uvh ~/MySQL-server-standard-5.0.27-0.rhel4.x86_64.rpm
echo "3. finished."
echo "4. stop mysql"
/etc/init.d/mysql stop
echo "5. mkdir /data/ibdata /data/iblogs /data/var"
mkdir /data/ibdata /data/iblogs /data/var
echo "6. cp -rf /var/lib/mysql /var/lib/mysql_bk "
cp -rf /var/lib/mysql /var/lib/mysql_bk
echo "7. mv /var/lib/mysql /data/var"
mv /var/lib/mysql /data/var
echo "8. change chown -R mysql.mysql /data/var/*"
cd /data/var
chown -R mysql.mysql ./*
echo "9. start mysql"
#/usr/bin/mysql_install_db --user=mysql
/usr/bin/mysqld_safe &
# vi /etc/sysconfig/network