高速备份还原MYSQL数据库

时间:2022-02-03 03:18:31

#安装innobackupex
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
rpm -ivh percona-xtrabackup-24-2.4.8-1.el6.x86_64.rpm
yum install numactl libaio rsync zip -y

#备份一下
innobackupex --user=root --password=DsideaL147258369 --databases="dsideal_db mysql" /usr/local/software
开始时间:13:16:37
结束时间:13:27:50

#压缩一下

cd /usr/local/software
tar -zcvf 2017-08-09_13-16-30.tar.gz 2017-08-09_13-16-30


innobackupex通过backup-my.cnf(这个文件存在于上面的备份目录下)来获取DATADIR目录的相关信息。

#还原一下
cd /usr/local/software
tar -xzvf 2017-08-09_13-16-30.tar.gz

cd /usr/local/db/mysql/data && rm -rf *.*

vi /etc/my.cnf  # 加入 datadir 配置项

datadir=/usr/local/db/mysql/data

innobackupex --defaults-file=/etc/my.cnf --copy-back /usr/local/software/2017-08-09_13-16-30/
chown -R mysql:mysql ./*
service mysql restart


#自动备份
http://www.cnblogs.com/jiangwenju/p/4919633.html
===========================================================================================================================================
SELECT CONCAT('ALTER TABLE ',table_name,' ENGINE=Aria;') FROM information_schema.tables WHERE table_schema='dsideal_db' AND ENGINE='InnoDB';

SELECT CONCAT('ALTER TABLE ',table_name,' ENGINE=Aria;') FROM information_schema.tables WHERE table_schema='dsideal_db' AND ENGINE='InnoDB' and table_name not like '%_sphinx%';