在线自动收缩MySql的innoDB系统表空间，即10G以上ibdata文件的脚本

在线自动收缩MySql的innoDB系统表空间，即10G以上ibdata文件的脚本"/>

在线自动收缩MySql的innoDB系统表空间，即10G以上ibdata文件的脚本

数据无价，谨慎操作，风险自担。

经查询，已经超大的ibdata文件不会通过启用innodb_file_per_table选项和改变innodb_autoextend_increment配置而缩小，暂时也没有找到其它调整工具。因此写了以下脚本，在生产系统中成功缩小ibdata到10倍左右。引以为抛转，如有更好，可分享。

MySql参考手册中关于ibdata的介绍

ibdata文件
一组名称为ibdata1，ibdata2等的文件，构成InnoDB系统表空间。 这些文件包含有关InnoDB表（InnoDB数据字典）的元数据，以及一个或多个撤消日志，更改缓冲区和双写缓冲区的存储区域。 它们还可以包含部分或全部表数据（取决于创建每个表时每个表的文件模式是否有效）。 启用innodb_file_per_table选项后，新创建的表的数据和索引将存储在单独的.ibd文件中，而不是存储在系统表空间中。 ibdata文件的增长受innodb_autoextend_increment配置选项的影响。

脚本正文：

#!/bin/bash
#在线快速收缩超大ibdata共享表空间
#注意此脚本请在空白数据库服务器上执行
#软件平台：centos7.4 mysql5.7.22
#应用工具：xtrabackup2.4.12
#预安装好xtrabackup并测试通过
#预配置ssh免密码登陆并确认通过
#version 0.6
#by jyx 2019-12-09###修改需收缩ibdata的服务器ip及数据库等基础信息
#简称远程服务器
remote_host="xxx"
remote_host_ssh_port="xxx"
remote_host_username="xxx"
remote_db_username="xxx"
remote_db_password="xxx"
remote_db_port="xxx"###修改存储收缩后ibdata的服务器ip及数据库等基础信息
#简称本地服务器
local_host="xxx"
local_host_ssh_port="xxx"
local_host_username="xxx"
local_db_username="xxx"
local_db_password="xxx"
local_db_port="xxx"###修改数据库名称、mysql路径、文件名等
db_name="xxx"                                                       
mysql_path="/var/lib/mysql"
all_table="all_table"
all_table_discard_tablespace="all_table_discard_tablespace"
all_table_import_tablespace="all_table_import_tablespace"
backup_path="/home/tablespace"
table_path="/home/tablespace/table"###在本地服务器创建文件夹
#假如存在同名文件夹先删除
rm -rf ${backup_path}
mkdir -p ${table_path}###在远程服务器导出空表结构
#ssh远程服务器
ssh -p${remote_host_ssh_port} ${remote_host} &> /dev/null << eof
#在远程服务器创建文件夹
#假如存在同名文件夹先删除
rm -rf ${backup_path}
mkdir -p ${table_path}
#在远程服务器导出空的schema
mysqldump -u${remote_db_username} -p${remote_db_password} --no-data --set-gtid-purged=off --skip-lock-tables ${db_name} > ${backup_path}/${db_name}.sql
exit
eof###在远程服务器导出指定的schema的所有表名
#ssh远程服务器
ssh -p${remote_host_ssh_port} ${remote_host} &> /dev/null << eof
#在远程服务器导出指定的schema的所有表名
mysql -u${remote_db_username} -p${remote_db_password} -e "select concat(table_schema,'.',table_name) from information_schema.tables where table_schema in ('${db_name}')" > ${backup_path}/${all_table}.txt
exit
eof###在本地服务器指定拷贝远程服务器的文件
scp -r -P${remote_host_ssh_port} ${remote_host_username}@${remote_host}:${backup_path}/${db_name}.sql ${backup_path}
scp -r -P${remote_host_ssh_port} ${remote_host_username}@${remote_host}:${backup_path}/${all_table}.txt ${backup_path}###在本地停止从库复制,假如存在
mysql -u${local_db_username} -p${local_db_password} -e "
#重设所有从库信息
stop slave;
reset master;
reset slave all;
#退出数据库登陆
exit "###在本地强制删除schema下所有数据,假如存在
rm -rf ${mysql_path}/${db_name}/*###在本地服务器创建数据库并导入空表结构
mysql -u${local_db_username} -p${local_db_password} -e "
drop database if exists ${db_name};
create database ${db_name};
use ${db_name};
source ${backup_path}/${db_name}.sql;
exit "###在远程服务器流式备份表空间，传送本地服务器
#ssh远程服务器
ssh -p${remote_host_ssh_port} ${remote_host} &> /dev/null << eof
#在远程服务器用xtrabackup流式备份表空间，传送本地服务器
innobackupex --user=${remote_db_username} --password='${remote_db_password}' --tables-file=${backup_path}/${all_table}.txt  --stream=xbstream ./ | ssh -p${local_host_ssh_port} ${local_host} "xbstream -x -c ${table_path}"
exit
eof###在本地服务器准备流式备份表空间文件等
innobackupex --apply-log --export ${table_path}
chown -r mysql:mysql ${table_path}/${db_name}
sed '1d' ${backup_path}/${all_table}.txt | sed '/./{s/^/alter table &/;s/$/& discard tablespace;/}' > ${backup_path}/${all_table_discard_tablespace}.sql
sed '1d' ${backup_path}/${all_table}.txt | sed '/./{s/^/alter table &/;s/$/& import tablespace;/}' > ${backup_path}/${all_table_import_tablespace}.sql###在本地服务器丢弃表空间
mysql -u${local_db_username} -p${local_db_password} < ${backup_path}/${all_table_discard_tablespace}.sql###在本地服务器移动备份表空间到mysql的schema目录
mv ${table_path}/${db_name}/*.ibd ${mysql_path}/${db_name}
cp -p ${table_path}/${db_name}/*.cfg ${mysql_path}/${db_name}###在本地服务器导入表空间
mysql -u${local_db_username} -p${local_db_password} < ${backup_path}/${all_table_import_tablespace}.sql###在本地检查并分析导入的数据库
mysqlcheck -u${local_db_username} -p${local_db_password} --databases ${db_name} -a --auto-repair###在本地重启数据库
systemctl restart mysqld###重启数据库后，检查数据库及ibdata共享表空间，查看mysql错误日志，以作相应调整。

脚本结束