xtrabackup使用手册
xtrabackup使用手册
#下载xtrabackup
mkdir xtrabackup
cd xtrabackup/
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
yum localinstall percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm -y
#补充：如果yum失败请设置yum源
#提示：如果下载出错检查dns服务器
mkdir /etc/yum.repos.d/bak
mv /etc/yum.repos.d/* /etc/yum.repos.d/bak
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
#创建备份用户
mysql -uroot -p
CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'H1tUU1M5M8Us';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost'; FLUSH PRIVILEGES;
#查看所有用户的所有权限
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
#进行备份操作 二选一即可 个人推荐第二个
xtrabackup --user=bkpuser --password=H1tUU1M5M8Us --backup --target-dir=/data/bkps/
innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=H1tUU1M5M8Us --socket=/data/mysql/run/mysql.sock /data/pxb
#注意：报错时解决方法记录https://blog.51cto.com/huangfuff/1610115
#进行还原
systemctl stop mysqld
#注意：把之前的数据目录删除或者移动
mv /data/mysql/data/ /data/mysql/databak
#指定备份文件夹进行恢复
xtrabackup --copy-back --target-dir=/root/2021-03-15_11-34-04
#重新授权目录权限并启动
chown -R mysql:mysql /data/mysql/data/
systemctl start mysqld
#连接进行验证
mysql -uroot -p
批量导入sql
CREATE TABLE userinfo
(
uuid varchar(36) NOT NULL,
name varchar(64) NOT NULL DEFAULT '',
email varchar(64) NOT NULL DEFAULT '',
password varchar(64) NOT NULL DEFAULT '',
dob date DEFAULT NULL,
address varchar(255) NOT NULL DEFAULT '',
city varchar(64) NOT NULL DEFAULT '',
state_id tinyint unsigned NOT NULL DEFAULT '0',
zip varchar(8) NOT NULL DEFAULT '',
country_id smallint unsigned NOT NULL DEFAULT '0',
gender enum('M','F') NOT NULL DEFAULT 'M',
account_type varchar(32) NOT NULL DEFAULT '',
verified tinyint NOT NULL DEFAULT '0',
allow_mall tinyint unsigned NOT NULL DEFAULT '0',
parrent_account int unsigned NOT NULL DEFAULT '0',
closest_airport varchar(3) NOT NULL DEFAULT '',
PRIMARY KEY(uuid),
UNIQUE KEY email (email),
KEY country_id (country_id),
KEY state_id (state_id),
KEY state_id_2 (state_id,city,address)
)ENGINE=InnoDB;
set global log_bin_trust_function_creators = 1;
DROP FUNCTION IF EXISTS rand_string;
DELIMITER $$
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE return_str varchar(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));
SET i = i +1;
END WHILE;
RETURN return_str;
END $$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `insert_userinfo_uuid`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_userinfo_uuid`(IN item INTEGER)
BEGIN
DECLARE counter INT;
SET counter = item;
WHILE counter >= 1 DO
insert into userinfo (uuid,name,email,password,dob,address,city,state_id,zip,country_id,gender,account_type,verified,allow_mall,parrent_account,closest_airport)
values(uuid(),rand_string(64), rand_string(64), rand_string(64), '2010-10-10', rand_string(255), rand_string(64), ceil(rand() * 100), rand_string(8),
ceil(rand() * 100), 'M', rand_string(32), 0, 0, 0, rand_string(3));
SET counter = counter - 1;
END WHILE;
END$$
DELIMITER ;
call `insert_userinfo_uuid`(1000000);
全量备份脚本
#!/bin/sh
#chmod +x /data/xtrabackup/backup.sh && echo "1 1 * * * /data/xtrabackup/backup.sh" >>/var/spool/cron/root
INNOBACKUPEX=innobackupex
INNOBACKUPEXFULL=/usr/bin/$INNOBACKUPEX
TODAY=`date +%Y%m%d%H%M`
USEROPTIONS="--user=bkpuser --password=H1tUU1M5M8Us"
TMPFILE="/tmp/innobackup_$TODAY.$$.tmp"
MYCNF=/etc/my.cnf
MYSQL=/data/mysql/mysql/bin/mysql
MYSQLADMIN=/data/mysql/mysql/bin/mysqladmin
BACKUPDIR=/backup/mysql # 备份的主目录
FULLBACKUPDIR=$BACKUPDIR/full # 全库备份的目录
INCRBACKUPDIR=$BACKUPDIR/incr # 增量备份的目录
KEEP=1 # 保留几个全库备份
mkdir -p $BACKUPDIR
# Grab start time
#############################################################################
# Display error message and exit
#############################################################################
error()
{
echo "$1" 1>&2
exit 1
}
# Check options before proceeding
if [ ! -x $INNOBACKUPEXFULL ]; then
error "$INNOBACKUPEXFULL does not exist."
fi
if [ ! -d $BACKUPDIR ]; then
error "Backup destination folder: $BACKUPDIR does not exist."
fi
if [ -z "`$MYSQLADMIN $USEROPTIONS status | grep 'Uptime'`" ] ; then
error "HALTED: MySQL does not appear to be running."
fi
if ! `echo 'exit' | $MYSQL -s $USEROPTIONS` ; then
error "HALTED: Supplied mysql username or password appears to be incorrect (not copied here for security, see script)."
fi
# Some info output
echo "----------------------------"
echo
echo "$0: MySQL backup script"
echo "started: `date`"
echo
# Create full and incr backup directories if they not exist.
for i in $FULLBACKUPDIR $INCRBACKUPDIR
do
if [ ! -d $i ]; then
mkdir -p $i
fi
done
# 压缩上传前一天的备份
echo "备份压缩，scp到远程主机"
cd $BACKUPDIR
if [ $? = 0 ]; then
rm -rf $BACKUPDIR/full $BACKUPDIR/incr
echo "Running new full backup."
innobackupex --defaults-file=$MYCNF $USEROPTIONS $FULLBACKUPDIR > $TMPFILE 2>&1
tar -zcvf $TODAY.tar.gz ./full/ #./incr/
scp -P 22 $TODAY.tar.gz root@192.168.10.183:/data/backup/mysql/
else
echo "Error with scp."
fi
if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then
echo "$INNOBACKUPEX failed:"; echo
echo "---------- ERROR OUTPUT from $INNOBACKUPEX ----------"
# cat $TMPFILE
# rm -f $TMPFILE
exit 1
fi
# 这里获取这次备份的目录
THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" $TMPFILE`
echo "THISBACKUP=$THISBACKUP"
#rm -f $TMPFILE
echo "Databases backed up successfully to: $THISBACKUP"
# Cleanup
echo "delete tar files of 3 days ago"
find $BACKUPDIR/ -mtime +3 -name "*.tar.gz" -exec rm -rf {} \;
-------------------------------
cat delbak.sh
#!/bin/bash
#delete bakmysqltar
# chmod +x /data/backup/mysql/delbak.sh && echo "1 1 * * * /data/backup/mysql/delbak.sh" >>/var/spool/cron/root
find /data/backup/mysql -mtime +7 -name "*.tar.gz" -exec rm -rf {} \;
# 恢复mysql备份参考
# systemctl stop mysqld
# mv /data/mysql/data/ /data/mysql/databak
# cd /data/backup/mysql/
# tar xvf 202103261204.tar.gz
# xtrabackup --copy-back --target-dir=./full/2021-03-26_12-04-38/
# chown -R mysql:mysql /data/mysql/data/
# systemctl start mysqld
# mysql -uroot -p
参考文档
官方文档：https://www.percona.com/doc/percona-xtrabackup/2.4/index.html
MySQL—物理备份数据库：https://blog.csdn.net/weixin_45916564/article/details/105001321
使用Xtrabackup远程备份Mysql：http://blog.itpub.net/22621861/viewspace-2643725/
Xtrabackup每周增量备份脚本程序(含附件)：http://www.ttlsa.com/mysql/xtrabackup-daily-backup/
