一次生产环境mysql迁移操作（二）mysql空间释放（碎片整理）
一次生产环境mysql迁移操作（二）mysql空间释放（碎片整理）
一笠风雨任生平
发布于 2020-02-18 12:53:19
发布于 2020-02-18 12:53:19
上文中增加了定时归档，现在一些大表磁盘空间一直不释放，导致数据库文件越来越大。现在介绍下数据导入导出方案。
方案选择
暂时有两种方案选择： 1、停机数个小时，使用mysqldump进行备份（大表只最近导出10天的数据）和恢复，并将共享表空间设置为独立表空间 2、mysql使用OPTIMIZE 来进行碎片整理 2、搭建主从集群，记录备份时间点，然后将数据重新导入到从库，最后将从库切换为主库。
经过对比1、2、3，方案一操作简单，比较灵活可以先导入近一天数据，以后的数据慢慢导入，方案二需要锁表，且时间太长，而我们数据库版本是5.5.47且innodb-file-per-table参数没设置，所以不适合该方案，方案三操作比较麻烦，时间也需要很久，经过评估停机时间可以缩短为1小时，最后选择方案1。
实施
前置步骤
1、从我们归档的数据库中导出近期半个月数据。
mkdir -p /mnt/backup/mysql/
cd /mnt/backup/mysql/
mysqldump -uroot -p1 -t --skip-add-locks --single-transaction --quick --databases xxx --tables table1 --where 'clock >1543161600 and clock <=1543939200' >table1_1126-1204.sql
mysqldump -uroot -p1 -t --skip-add-locks --single-transaction --quick --databases xxx --tables table2 --where 'clock >1543161600 and clock <=1543939200' >table2_1126-1204.sql
...
...
...2、从生产数据库中导出近一天的数据
比如说我们准备12月10升级，这里我们可以提前到处那六张大表的数据，该步骤可以不用停服务，并且不影响数据库正常运行。
mkdir -p /mnt/backup/mysql/
cd /mnt/backup/mysql/
mysqldump -uroot -p1 -t --skip-add-locks --single-transaction --quick --databases xxx --tables table1 --where 'clock >1543939200 and clock <=1544371200' >table1_1205-1209.sql
mysqldump -uroot -p1 -t --skip-add-locks --single-transaction --quick --databases xxx--table2 --where 'clock >1543939200 and clock <=1544371200' >table2_1205-1209.sql
...
...
...操作步骤
1、停止相关服务
systemctl stop httpd
systemctl stop supervisord
systemctl stop xxx-server.service2、执行清理脚本
由于这些大表都已经分区，所以清理就只需要保留当天的分区，以前的分区可以都drop掉，由于不同的库清理脚本不一样，所以脚本我这里就不列出来了
3、导出其余表数据
使用mysqldump导出主数据库中除了那6个大表以外的其他所有数据库的数据
mkdir -p /mnt/backup/mysql/
cd /mnt/backup/mysql/
mysqldump -R -uroot -p1 --databases db1 db2alert db3 guacamole --ignore-table=db1.table1 --ignore-table=db1.table2 > mysqldump.sql备份mysql数据库中的用户和数据库信息
mysqldump -t -uroot -p1 --databases mysql> mysql_info_data.sql4、使用mysqldump 分别导出 6个大表清理后的数据
cd /mnt/backup/mysql/
mysqldump -uroot -p1 --databases xxx --tables table1 --where 'clock >1544371200' >table1.sql
mysqldump -uroot -p1 --databases xxx --tables table2 --where 'clock >1544371200' >table2.sql
...
...
...5、删除所有数据库
mysql -uroot -p1
drop database db1;
drop database db2;
drop database db3;删除数据/var/lib/mysql下日志文件
systemctl stop mariadb
rm -rf /var/lib/mysql/ib*
#查看空间是否释放
df6、配置/etc/my.cnf
修改/etc/my.cnf，这里需要将共享表空间设置为独立表空间。 备注：mysql 5.6.7版本后会默认开启独立表空间innodb-file-per-table=on 当前服务现在使用的版本是5.5.47，所以需要在my.cnf中进行配置，而openstack用的是5.6.22所以不用设置。 添加下面一句：
[mysqld]
innodb-file-per-table=1
systemctl start mariadbmysql -uroot -p1
#查看设置是否生效，如果是on则说明已生效
show variables like '%per_table%';7、分批导入数据库
cd /mnt/backup/mysql
mysql -uroot -p1
source mysqldump.sql;
use db1
source table1.sql;
source table2.sql;
...
...
...8、验证数据
mysql -uroot -p1
use db1
show table status like 'table1%';9、重启服务并验证
systemctl start httpd
systemctl start supervisord
systemctl start xxx-server.service可以说现在服务运行正常了，现在就是慢慢导入历史数据了
后置步骤
导入历史数据
cd /mnt/backup/mysql
mysql -uroot -p1
use db1
source table1_1205-1209.sql;
source table2_1205-1209.sql;
...
...
...本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。