高速备份还原MYSQL数据库

#安装依赖包
yum -y update gcc
yum -y install gcc+ gcc-c++

#安装
cd /usr/local/software
tar -jxvf p7zip_16.02_src_all.tar.bz2
cd p7zip_16.02
make && make install
============================================================================

#安装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
yum install numactl libaio rsync -y

rpm -ivh percona-xtrabackup-24-2.4.8-1.el6.x86_64.rpm

#备份一下
innobackupex --user=root --password=** --databases="dsideal_db mysql" /usr/local/software

开始时间：13:16:37
结束时间：13:27:50

#压缩一下

cd /usr/local/software

#压缩(不带全称路径)
7za a -t7z 2017-08-09_13-16-30.7z  2017-08-09_13-16-30/

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

#还原一下
cd /usr/local/software

#解压缩
7za x 2017-08-09_13-16-30.7z

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

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

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

 

应用一下日志

innobackupex --apply-log /usr/local/software/2017-08-09_13-16-30/

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

chmod -R 777 data

service mysql restart

链接: http://pan.baidu.com/s/1bozIo15 密码: uhfv

#自动备份
http://www.cnblogs.com/jiangwenju/p/4919633.html

http://navyaijm.blog.51cto.com/4647068/1422229
===========================================================================================================================================
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%' and table_name not like '% %';

如果提示错误：

 Specified key was too long; max key length is 1000 bytes

解决思路：
1.DB engine 是MyIsAm
2.字符集是 utf8 ,1个utf8=3bytes
3.最后就是 (100+255)*3>1000 所以报错
解决方案很多 ,修改DB engine 至 innodb,或者是更改字符集,或者是减小字段长度 皆可.:)

 

初始化数据库，重新还原

cd /usr/local/db/mysql/

rm -rf data

#注释掉 

vi /etc/my.cnf
#innodb_force_recovery = 4

cd /usr/local/mysql
scripts/mysql_install_db --user=mysql --datadir=/usr/local/db/mysql/data

chmod -R 777 /usr/local/db

service mysql start

mysqladmin -u root password 'D**********'



GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "DsideaL147258369";

flush privileges;