有一个项目要从云上整体迁移到公司机房内，里面有mysql5.6.20，这个mysql没做过备份，也没主从，然后打算通过xtrabackup先做个全备，然后再做个主从（因为在迁移的阶段，云上服务器还会有新的数据生成，主从是为了确保迁移的数据完整）
如有问题、可联系公众号：阿牛哥在厦门
一、安装mysql5.6.20
在新机器上部署mysql5.6.20，需确保和之前的mysql版本一致
my.cnf也要基本一致
二、xtrabackup的安装
# 先安装需要的rpm包。在安装 xtrabackup
rpm -Uvh --force --nodeps --replacepkgs *.rpm
# 安装的版本比较旧，为了迎合 mysql5.6.20
yum localinstall percona-xtrabackup-2.2.12-1.el7.x86_64.rpm
验证是否安装成功：输入命令innob，按tab键，若自动补全innobackupex ，则说明安装成功。
验证是否安装成功：输入命令qp，按tab键，若自动补全qpress，则说明安装成功。
验证是否安装成功：输入命令pt-online，按tab键，若自动补全pt-online-schema-change，则说明安装成功。
输入pig，按tab键能自动补全pigz则说明安装成功！
创建备份目录：
mkdir -pv /devbdata//mysql_backup/xbstream_no_month_tables/full
数据库数据准备
###### mysql 创建库，并生成 记录
mysql -uroot -p'P@SSw0rdnqt123' -h 127.0.0.1
####################### 多创建几个库
CREATE DATABASE tdata;
use tdata;
#建测试表
drop table if exists t;
CREATE TABLE t (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY comment '自增主键',
dept tinyint not null comment '部门id',
age tinyint not null comment '年龄',
name varchar(30) comment '用户名称',
create_time datetime not null comment '注册时间',
last_login_time datetime comment '最后登录时间'
) comment '测试表';
#手工插入第一条测试数据，后面数据会根据这条数据作为基础生成
insert into t values(1,1, 25, 'user_1', '2018-01-01 00:00:00', '2018-03-01 12:00:00');
#初始化序列变量
set @i=1;
#==================此处拷贝反复执行，直接符合预想的数据量===================
#执行20次即2的20次方=1048576 条记录
#执行23次即2的23次方=8388608 条记录
#执行24次即2的24次方=16777216 条记录
# 执行的时候，记得 把后面的 #注释 去掉，不然会执行有问题
insert into t(dept, age, name, create_time, last_login_time)
select left(rand()*10,1) as dept, #随机生成1~10的整数
FLOOR(20+RAND() *(50 - 20 + 1)) as age, #随机生成20~50的整数
concat('user_',@i:=@i+1), #按序列生成不同的name
date_add(create_time,interval +@i*cast(rand()*100 as signed) SECOND), #生成有时间大顺序随机注册时间
date_add(date_add(create_time,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND) #生成有时间大顺序的随机的最后登录时间
from t;
select count(1) from t;
#==================此处结束反复执行========================================
# 检查一下
select * from t limit 10;
#创建索引(视情况执行)
create index idx_dept on t(dept);
create index idx_create_time on t(create_time);
create index idx_last_login_time on t(last_login_time);
数据备份
nohup innobackupex --no-timestamp --user=root --password='P@SSw0rdnqt123' --host=192.168.56.111 --port=3306 --stream=xbstream --compress --extra-lsndir=/devbdata/mysql_backup/xbstream_no_month_tables/full /devbdata/mysql_backup/xbstream_no_month_tables/full 1> /devbdata/mysql_backup/xbstream_no_month_tables/xbstream_log 2>&1 >/devbdata/mysql_backup/xbstream_no_month_tables/full/full.xbstream &
上面参数的说明：
--user= #指定数据库备份用户
--password= #指定数据库备份用户密码
--port= #指定数据库端口
--host= #指定备份主机
--socket= #指定socket文件路径
--databases= #备份指定数据库,多个空格隔开，如–databases=”dbname1 dbname2″，不加备份所有库
--defaults-file= #指定my.cnf配置文件
--apply-log #日志回滚
--incremental= #增量备份，后跟增量备份路径
--incremental-basedir= #增量备份，指上次增量备份路径
--redo-only #合并全备和增量备份数据文件
--copy-back #将备份数据复制到数据库，数据库目录要为空
--move-back #这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项移除backup文件，用时候必须小心。使用场景：没有足够的磁盘空间同时保留数据文件和Backup副本
--no-timestamp #生成备份文件不以时间戳为目录名
--stream= #指定流的格式做备份，–stream=tar，将备份文件归档
--remote-host=user@ip DST_DIR #备份到远程主机
查看备份日志
# cat /devbdata/mysql_backup/xbstream_no_month_tables/xbstream_log
innobackupex: Backup created in directory '/devbdata/mysql_backup/xbstream_no_month_tables/full'
会记录二进制文件的位置，方便后期做主从
innobackupex: MySQL binlog position: filename 'mysql-bin.000003', position 62586
231110 10:43:49 innobackupex: Connection to database server closed
231110 10:43:49 innobackupex: completed OK!
库的表结构备份
cd /devbdata/mysql_backup/
mysqldump -h127.0.0.1 -uroot -p'P@SSw0rdnqt123' -B -R -E --triggers -d tdata > tdata.sql
mysqldump -h127.0.0.1 -uroot -p'P@SSw0rdnqt123' -B -R -E --triggers -d tdatabak > tdatabak.sql
上面参数的说明;
********** 确保数据库该有的 存储过程、函数、触发器、事件、视图 都被导出，可使用 SQLyog软件，不要导出 数据即可
mysqldump -h127.0.0.1 -uroot -proot@123 -P5320 -B -R -E --triggers -d 数据库名 > ad.sql
总结一下：
-d 结构(–no-data:不导出任何数据，只导出数据库表结构)
-t 数据(–no-create-info:只导出数据，而不添加CREATE TABLE 语句)
-n (–no-create-db:只导出数据，而不添加CREATE DATABASE 语句）
-R (–routines:导出存储过程以及自定义函数)
-E (–events:导出事件)
–triggers (默认导出触发器，使用–skip-triggers屏蔽导出)
-B (–databases:导出数据库列表，单个库时可省略）
–tables 表列表（单个表时可省略）
①同时导出结构以及数据时可同时省略-d和-t
②同时 不 导出结构和数据可使用-ntd
③只导出存储过程和函数可使用-R -ntd
④导出所有(结构&数据&存储过程&函数&事件&触发器)使用-R -E(相当于①，省略了-d -t;触发器默认导出)
⑤只导出结构&函数&事件&触发器使用 -R -E -d
模拟删除数据库
mysql -uroot -p'P@SSw0rdnqt123' -h 127.0.0.1
drop database tdata;
drop database tdatabak;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql>
三、数据恢复
先还原表结构
mysql -uroot -p'P@SSw0rdnqt123' -h 127.0.0.1
mysql> source /devbdata/mysql_backup/tdata.sql;
mysql> source /devbdata/mysql_backup/tdatabak.sql;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| tdata |
| tdatabak |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql>
# 检查一下
mysql> select * from tdata.t limit 10;
Empty set (0.00 sec)
mysql>
解压备份的文件 full.xbstream
===== 解压 脚本 /devbdata/mysql_backup/xbstream_decompression.sh ===============
#!/bin/sh
#对备份的文件，进行解压
#source ~/.cshrc
source /etc/profile
export PATH=$PATH:/bin:/usr/bin:/usr/local/bin
BEGINTIME=`date +"%Y-%m-%d %H:%M:%S"`
format_time=`date +"%Y-%m-%d_%H:%M:%S"`
# 存放备份文件的绝对路径 ；在full 层级目录前
backdir=/devbdata/mysql_backup/xbstream_no_month_tables
# 日志目录
out_log=$backdir/xbstream_recovery_log_$format_time
# 创建一个用于存放解压文件的 目录
if [ ! -d "${backdir}/full/Unzip" ]; then
mkdir ${
backdir}/full/Unzip
fi
# 第一次解压，至指定的目录，解压后的文件后缀名为 .qp
xbstream -x < ${
backdir}/full/full.xbstream -C ${
backdir}/full/Unzip
cd ${
backdir}/full/Unzip
for f in `find ./ -iname "*\.qp"`;
do
echo "qpress ${f}"
# 第二次 使用 qpress 解压
qpress -d ${
f} $(dirname ${
f}) && rm -rf ${
f};
done
# 把 备份的时候 数据库 增量的数据 写入到 对应库的 文件里
innobackupex --apply-log --redo-only --use-memory=1G $backdir/full/Unzip 1>>$out_log 2>&1
innobackupex --apply-log --export $backdir/full/Unzip 1>>$out_log 2>&1
========================================================
执行解压 脚本
nohup sh +x /devbdata/mysql_backup/xbstream_decompression.sh &
# tail -f nohup.out
执行的时候，如果开着 终端，qpress 解压的时候 ，可能会一直在终端 打印 输出信息
需要另外打开一个终端界面，在操作其他命令
解压后的文件目录
/devbdata/mysql_backup/xbstream_no_month_tables/full/Unzip/
恢复数据库
按照单库恢复；脚本
===================== /devbdata/mysql_backup/xbstream_recovery.sh =====================
#!/bin/sh
#恢复
#source ~/.cshrc
source /etc/profile
export PATH=$PATH:/bin:/usr/bin:/usr/local/bin
BEGINTIME=`date +"%Y-%m-%d %H:%M:%S"`
format_time=`date +"%Y-%m-%d_%H:%M:%S"`
# 存放解压后库文件的目录 ，full 层级目录 前
backdir=/devbdata/mysql_backup/xbstream_no_month_tables
out_log=$backdir/xbstream_recovery_log_$format_time
# 新数据库的存储 路径
datadir=/devbdata/.data/mysql
#MySQL数据库用户名
MyUSER=root
#MySQL数据库密码
MyPASS=P@SSw0rdnqt123
#ip地址
MyHOST=localhost
#MySQL数据库端口号
PORT=3306
#恢复数据库名，一次只能恢复一个库，需要手动修改这个库名
# `tdata` `tdatabak`
DBNAME=XXX
# 表名 为all ，表示 当前库中的 所有表
tableName=all
if [ -d "$backdir/full" ];then
cd $backdir/full/Unzip/$DBNAME
echo "#####start alter ${recovery_tables} discard tablespace"
if [ "${tableName}" = "all" ]; then
recovery_tables=`find ./ -name "*\.frm"`
else
recovery_tables=${
tableName}
fi
for f in $recovery_tables
do
file_name=${
f##*/}
table=${
file_name%.*}
# 分区表 特殊处理、判断当前库 中 是否 含有分区表
if [ -f "${table}.par" ]; then
parName=`find ./ -name "${
table}#P#p*.ibd"`
if [ ! -f "${backdir}/table_sql_info.txt" ]; then
touch ${
backdir}/table_sql_info.txt
else
rm -rf ${
backdir}/table_sql_info.txt
touch ${
backdir}/table_sql_info.txt
fi
mysql -u $MyUSER -h $MyHOST -P $PORT -p$MyPASS $DBNAME -Bse "SHOW CREATE TABLE ${table}" >> ${
backdir}/table_sql_info.txt
sql=`cat ${
backdir}/table_sql_info.txt`
table_sql_temp=${
sql#${
table}}
table_sql=${
table_sql_temp%%ENGINE*}
for p in ${
parName}
do
parFileName=${
p##*#P#}
parTableName=${
parFileName%.*}
echo "this recovery table "
echo "#####create temporary table ${table}_${parTableName} ."
mysql -u $MyUSER -h $MyHOST -P $PORT -p$MyPASS $DBNAME -Bse "${table_sql/${table}/${table}_${parTableName}}" 1>>$out_log 2>&1
echo ">>>>finins create temporary table ${table}_${parTableName}."
echo "#####${table}_${parTableName} start discard tablespace"
mysql -u $MyUSER -h $MyHOST -P $PORT -p$MyPASS $DBNAME -Bse "ALTER TABLE ${table}_${parTableName} DISCARD TABLESPACE" 1>>$out_log 2>&1
echo ">>>>finish ${table%_*}_${parTableName} discard tablespace"
# .exp/.ibd/.cfg 都是解压后生成的 文件
echo "#####start copy $table ${parTableName} .exp/.ibd/.cfg file to $datadir"
cp ${
table}\#P\#${
parTableName}.ibd ${
datadir}/${
DBNAME}/${
table}_${
parTableName}.ibd
cp ${
table}\#P\#${
parTableName}.exp ${
datadir}/${
DBNAME}/${
table}_${
parTableName}.exp
cp ${
table}\#P\#${
parTableName}.cfg ${
datadir}/${
DBNAME}/${
table}_${
parTableName}.cfg
chown -R mysql:mysql ${
datadir}/${
DBNAME}
echo ">>>>finish copy $table ${parTableName} .exp/.ibd/.cfg file to $datadir"
echo "#####${table}_${parTableName} start import tablespace"
mysql -u $MyUSER -h $MyHOST -P $PORT -p$MyPASS $DBNAME -Bse "ALTER TABLE ${table}_${parTableName} IMPORT TABLESPACE" 1>>$out_log 2>&1
echo ">>>>finish ${table}_${parTableName} import tablespace"
echo "start exchange partition ${table} ${parTableName}"
mysql -u $MyUSER -h $MyHOST -P $PORT -p$MyPASS $DBNAME -Bse "ALTER TABLE ${table} exchange PARTITION ${parTableName} with table ${table}_${parTableName}" 1>>$out_log 2>&1
# 处理掺杂的 数据
mysql -u $MyUSER -h $MyHOST -P $PORT -p$MyPASS $DBNAME -Bse "INSERT INTO ${table} SELECT * FROM ${table}_${parTableName}" 1>>${
out_log} 2>&1
echo ">>>>finish exchange partition ${table} ${parTableName}"
echo "#####start remove ${table}_${parTableName} .exp file"
rm -rf $datadir/$DBNAME/${
table}_${
parTableName}*.exp
echo ">>>>finish remove ${table}_${parTableName} .exp file"
echo "####start remove ${table}_${parTableName} table"
mysql -u $MyUSER -h $MyHOST -P $PORT -p$MyPASS $DBNAME -Bse "DROP TABLE ${table}_${parTableName}" 1>>${
out_log} 2>&1
rm -rf $datadir/$DBNAME/${
table}_${
parTableName}.cfg
echo ">>>>finish remove ${table}_${parTableName} table"
done
else
echo ">>>>$table start discard tablespace"
mysql -u $MyUSER -h $MyHOST -P $PORT -p$MyPASS $DBNAME -Bse "ALTER TABLE $table DISCARD TABLESPACE" 1>>$out_log 2>&1
echo ">>>>finish $table discard tablespace"
echo "#####start copy $table .exp/.ibd file to $datadir"
cp $table.ibd $table.exp $table.cfg $datadir/$DBNAME
chown -R mysql:mysql $datadir/$DBNAME
echo "#####finish copy $table .exp/.ibd file to $datadir"
echo "$table start import tablespace"
mysql -u $MyUSER -h $MyHOST -P $PORT -p$MyPASS $DBNAME -Bse "ALTER TABLE $table IMPORT TABLESPACE" 1>>$out_log 2>&1
echo ">>>>finish $table import tablespace"
echo "#####start remove $table .exp file"
rm -rf $datadir/$DBNAME/$table*.exp
rm -rf $datadir/$DBNAME/$table*.cfg
echo ">>>>finish remove $table .exp file"
fi
done
fi
echo "#####recovery data success !!!!!"
===================== 恢复脚本 =====================
nohup sh +x /devbdata/mysql_backup/xbstream_recovery.sh &
tail -f nohup.out
注意：
1、数据存储引擎 类型为 MyISAM 的需要手动导入(手动从原来的数据库中 导出，然后在新库中 导入)
报错信息：cp: 无法获取"tdata.ibd" 的文件状态(stat): 没有那个文件或目录
然后去解压该库的目录下，去查看 ，是否有 MYD结尾的文件、如果有 、那就说明 显示这个表 需要手动 导入
（tdata 库名）
# ll -h /devbdata/mysql_backup/xbstream_no_month_tables/full/Unzip/tdata/*.MYD
2、视图的会报错、等库恢复后，需要手动去确认下视图是否正常导入
报错信息：cp: 无法获取"tdata.ibd" 的文件状态(stat): 没有那个文件或目录
###!!~ 特别注意
数据恢复成功后，需要手动给数据库中的每一张表 做元数据(索引) 重构
mysql -uroot -p'P@SSw0rdnqt123' -h 127.0.0.1
ALTER TABLE tdata.t ENGINE = INNODB; // 同一个数据库里，可能 有多种存储引擎，不同的表
*** ALTER TABLE 库名.表名 ENGINE = INNODB; // INNODB 表的存储引擎
*** ALTER TABLE 库名.表名 ENGINE = MyISAM; // MyISAM 表的存储引擎
查看恢复情况
select * from tdata.t limit 10;
如果要做主从，可以从 xbstream_log 问价里获取 需要 的 二进制文件 信息
做主从的时候需要注意，如果主库里有定时任务，记得在 从库中全局关闭 定时任务
