一次生产环境mysql迁移操作（二）mysql空间释放（碎片整理）

我不是女神ヾ 2023-07-02 11:23 2阅读 0赞

[一次生产环境mysql迁移操作（一）数据归档][mysql]  
[一次生产环境mysql迁移操作（二）mysql空间释放（碎片整理）][mysql_mysql]  
上文中增加了定时归档，现在一些大表磁盘空间一直不释放，导致数据库文件越来越大。现在介绍下数据导入导出方案。

## 方案选择 ##

暂时有两种方案选择：  
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.service

#### 2、执行清理脚本 ####

由于这些大表都已经分区，所以清理就只需要保留当天的分区，以前的分区可以都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.sql

#### 4、使用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*
    
    #查看空间是否释放
    df

#### 6、配置/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 mariadb

mysql -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;
    ...
    ...
    ...

[mysql]: https://blog.csdn.net/lp19861126/article/details/104103358
[mysql_mysql]: https://blog.csdn.net/lp19861126/article/details/104107453