Slimming MySQL Data File ibdata1

Welcome to the Linux community forum and interact with 2 million technical staff. After MySQL runs for a period of time, the ibdata1 file will increase in size. Even if the table data is deleted, the size of ibdata1 will not be reduced.

Welcome to the Linux community forum and interact with 2 million technical staff> after MySQL runs for a period of time, ibdata1 files will increase in size, even if the table data is deleted, the size of ibdata1 will not be reduced.

Welcome to the Linux community forum and interact with 2 million technicians>

After MySQL runs for a period of time, the size of ibdata1 files will increase. Even if the table data is deleted, the size of ibdata1 will not decrease. Due to limited hard disk space, the disk space continues to expand and is close to crashing. Today, when exporting data, I found that the disk is full. I have reserved the usage for one month and it will be full in one week. I will try to slim down ibdata1 below.

1. System Environment

Linux Ubuntu 13.04 64bit server

~ Uname-

Linux d2 3.8.0-21-generic # 32-Ubuntu SMP Tue May 14 22:16:46 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux

~ Cat/etc/issue

Ubuntu 13.04 \ n \ l

MySQL: 5.5.31-0ubuntu0. 13.04.1

~ Mysql -- version

Mysql Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (x86_64) using readline 6.2

Hard Disk: 36G + 4G + 4G + 36G

~ Df-h

Filesystem Size Used Avail Use % Mounted on

/Dev/mapper/server3 -- vg-root 36g 31G 3.2G 91%/

None 4.0 K 0 4.0 K 0%/sys/fs/cgroup

Udev 4.1G 1.1G 3.0G 26%/dev

Tmpfs 824 M 280 K 823 M 1%/run

None 5.0 M 0 5.0 M 0%/run/lock

None 4.1G 3.4G 729 M 83%/run/shm

None 100 M 0 100 M 0%/run/user

/Dev/vda1 228 M 30 M 187 M 14%/boot

192.168.1.10:/home/amg/data 36G 13G 21G 39%/home/amg/data

MySQL ibdata1 space: 20 GB

~ Cd/var/lib/mysql

~ Ls-l

Drwxr-xr-x 2 mysql 4096 Aug 2 CB

Drwxr-xr-x 2 mysql 4096 Jun 24 conan

Drwxr-xr-x 2 mysql 4096 Jun 2 dbwordpress

-Rwxr-xr-x 1 root 0 May 23 debian-5.5.flag

-Rwxr-xr-x 1 mysql 20101201920 Aug 2 ibdata1

-Rwxr-xr-x 1 mysql 5242880 Aug 2 ib_logfile0

-Rwxr-xr-x 1 mysql 5242880 Aug 2 ib_logfile1

Drwxr-xr-x 2 mysql 4096 Jun 26 Macro

Drwxr-xr-x 2 mysql root 4096 May 23 mysql

-Rwxr-xr-x 1 root 6 May 23 00:48 mysql_upgrade_info

Drwxr-xr-x 2 mysql 4096 May 23 performance_schema

Drwxr-xr-x 2 mysql 4096 May 23 phpmyadmin

Drwxr-xr-x 2 mysql root 4096 May 23 test

Drwxr-xr-x 2 mysql 4096 Jul 22 TF

Drwxr-xr-x 2 mysql 4096 Jun 2 wordpress

Business data table

Mysql> show tables;

+ ----------------- +

| Tables_in_CB |

+ ----------------- +

| NSpremium |

| Cb_hft |

| Cb_hft_20130801 |

| Cb_hft_20130802 |

+ ----------------- +

4 rows in set (0.00 sec)

2. Problems Found

The size of a single ibdata1 file is 20 GB.

1. MySQL is set by default. Data is not separated by tablespace. All table data is stored in the ibdata1 file.

2. for business operations, a cb_hft table is generated every day and renamed at night.

Rename table cb_hft TO cb_hft_20130801;

Create table cb_hft like cb_hft_20130801;

[1] [2] [3]