Tips

mysql清理分区

问题描述： mysql集群一个结点(appdb05)磁盘满，需要清理分区数据，同时保留部分客户数据。

解决方法： 先停止写入，再导出数据，再清空分区，再导入数据，确认无误后重新刷新写入。

待观察问题： slaver(appdb12)磁盘满，主从复制可能失效，数据可能不一致。

step 1 关掉相关专题刷新

@5.23

1
2
3
4
5
6
7
8

mysql -h 192.168.5.23 peony_t -uroot -p
mysql > update pe_t_subject set state = 0,update_time = now() where id in \
(4620,4849,4850,4852,4853,4854,4855,4858,4859,4860,4861,\
4862,4865,4866,4875,4876,4877,4879,4880,4881,4882,4883,\
4884,4885,4888,5034,5079,5081,5082,5083,5101,5102,5103,\
5104,5162);
mysql> update pe_t_subject set state = 0,\
update_time = now() where id in (3418,3419);

step 2 确保数据没有再写入

@5.5

1
2
3
4
5
6
7
8
9

mysql> use peony_m_63;
mysql> select count(*) from  pe_t_subject_page where \ userId=1526 AND publishDate<'2016-01-01';
+----------+
| count(*) |
+----------+
|    53226 |
+----------+
1 row in set (0.05 sec)
[root@i-cphylyv8 ~]# ll -rt /home/mysql3306/peony_m_63

step 3 备份数据

@5.5

1
2
3
4
5
6
7
8

mysqldump --host=192.168.5.5 --user=***--password=*** \
--no-create-info --where="publishDate<'2016-01-01' AND \
userId=1526" peony_m_63 pe_t_subject_page \
>1526.2016-01-01.sql
mysqldump --host=192.168.5.5 --user=*** --password=*** \
--no-create-info --where="publishDate<'2016-01-01' AND \
userId=496" peony_m_63 pe_t_subject_page \
>496.2016-01-01.sql

step 4 清空分区数据并导入保留的专题数据

@5.5 上清空分区数据并导入保留的专题数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

mysql> alter table pe_t_subject_page truncate partition \
p24_1,p24_2,p25_1,p25_2;
mysql> select count(*) from  pe_t_subject_page where \
userId=1526 AND publishDate<'2016-01-01';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.10 sec)
mysql> source 1526.2016-01-01.sql；
mysql> source 496.2016-01-01.sql；
mysql> select count(*) from  pe_t_subject_page where \
userId=1526 AND publishDate<'2016-01-01';
+----------+
| count(*) |
+----------+
|    53226 |
+----------+
1 row in set (0.04 sec)

step 5 重新开启专题刷新

@5.23

1
2
3
4
5
6
7
8

update pe_t_subject set state = 1,update_time = now() \
where id in
(4620,4849,4850,4852,4853,4854,4855,4858,4859,4860,\
4861,4862,4865,4866,4875,4876,4877,4879,4880,4881,\
4882,4883,4884,4885,4888,5034,5079,5081,5082,5083,\
5101,5102,5103,5104,5162);
update pe_t_subject set state = 1,update_time = now() \
where id in (3418,3419);