在线自动收缩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错误日志,以作相应调整。

脚本结束

  • 2
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值