[MySQL optimization] slimming MySQL Data File ibdata1

Source: Internet
Author: User
System Environment troubleshooting 1. System Environment LinuxUbuntu13.0464bitserver ~ Uname-aLinuxd23.8.0-21-generic #32-rjtusmptuemay1422: 16: 46UTC2013x86_64x86_64x86_64GNULinux ~ CatetcissueUbuntu13.04nlMySQL: 5.5.31-0ubuntu0.

System Environment troubleshooting 1. System Environment Linux Ubuntu 13.04 64bit server ~ Uname-aLinux 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/issueUbuntu 13.04 \ n \ l MySQL: 5.5.31-0ubuntu0.

  1. System Environment
  2. Problems Found
  3. Solve the problem
1. System Environment

Linux Ubuntu 13.04 64bit server

~ uname -aLinux 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/issueUbuntu 13.04 \n \l

MySQL: 5.5.31-0ubuntu0. 13.04.1

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

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

~ df -hFilesystem                    Size  Used Avail Use% Mounted on/dev/mapper/server3--vg-root   36G   31G  3.2G  91% /none                          4.0K     0  4.0K   0% /sys/fs/cgroupudev                          4.1G  1.1G  3.0G  26% /devtmpfs                         824M  280K  823M   1% /runnone                          5.0M     0  5.0M   0% /run/locknone                          4.1G  3.4G  729M  83% /run/shmnone                          100M     0  100M   0% /run/user/dev/vda1                     228M   30M  187M  14% /boot192.168.1.10:/home/amg/data    36G   13G   21G  39% /home/amg/data

MySQL ibdata1 space: 20 GB

~ cd /var/lib/mysql~ ls -ldrwxr-xr-x 2 mysql mysql        4096 Aug  2 19:38 CBdrwxr-xr-x 2 mysql mysql        4096 Jun 24 23:08 conandrwxr-xr-x 2 mysql mysql        4096 Jun  2 00:52 dbwordpress-rwxr-xr-x 1 root  root            0 May 23 00:48 debian-5.5.flag-rwxr-xr-x 1 mysql mysql 20101201920 Aug  2 20:08 ibdata1-rwxr-xr-x 1 mysql mysql     5242880 Aug  2 20:08 ib_logfile0-rwxr-xr-x 1 mysql mysql     5242880 Aug  2 19:38 ib_logfile1drwxr-xr-x 2 mysql mysql        4096 Jun 26 09:03 Macrodrwxr-xr-x 2 mysql root         4096 May 23 00:48 mysql-rwxr-xr-x 1 root  root            6 May 23 00:48 mysql_upgrade_infodrwxr-xr-x 2 mysql mysql        4096 May 23 00:48 performance_schemadrwxr-xr-x 2 mysql mysql        4096 May 23 00:53 phpmyadmindrwxr-xr-x 2 mysql root         4096 May 23 00:48 testdrwxr-xr-x 2 mysql mysql        4096 Jul 22 14:09 TFdrwxr-xr-x 2 mysql mysql        4096 Jun  2 01:04 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;

3. Data is exported every week and the table is dropped at the same time. However, after the drop operation, ibdata1 will not decrease. As the data accumulation increases, ibdata1 will not be enough to use the root space.

3. Solve the Problem

1). Export data
Currently, the database has two data tables: cb_hft_20130801 and cb_hft_20130802, which are exported to/run/shm and/dev respectively.

~ cd /dev~ mysqldump -uroot -p CB cb_hft_20130802 > export_cb_hft_20130802.sql~ cd /run/shm~ mysqldump -uroot -p CB cb_hft_20130801 > export_cb_hft_20130801.sql~ df -hFilesystem                    Size  Used Avail Use% Mounted on/dev/mapper/server3--vg-root   36G   31G  3.2G  91% /none                          4.0K     0  4.0K   0% /sys/fs/cgroupudev                          4.1G  3.7G  368M  92% /devtmpfs                         824M  280K  823M   1% /runnone                          5.0M     0  5.0M   0% /run/locknone                          4.1G  3.4G  729M  83% /run/shmnone                          100M     0  100M   0% /run/user/dev/vda1                     228M   30M  187M  14% /boot192.168.1.10:/home/amg/data    36G   13G   21G  39% /home/amg/data

The two tables account for 3.4 GB and 3.7 GB respectively.

Log onto mysql to delete the CB database

~ mysql -uroot -p~ drop database CB

Export other database data

~ cd /run/shm~ mysqldump -uroot -p -R -q --all-databases > others.sql

2) modify the configuration file/etc/mysql/my. cnf
Use a separate data file storage innodb_file_per_table for each table

Stop a mysql Server

~ Sudo/etc/init. d/mysql stop # Clear all data files ~ Sudo rm-rf/var/lib/mysql /*

Modify configuration file

~ sudo vi /etc/mysql/my.cnf[mysqld]innodb_file_per_table

Rebuilding database instances

~ /Usr/bin/mysql_install_db ~ Ls/var/lib/mysqlmysql performance_schema test # Start MySQL ~ Sudo/etc/init. d/mysql start

3). Restore other databases

~ Mysql </run/shm/others. SQL ~ Mysql-umysql-p mysql> show databases; + -------------------- + | Database | + -------------------- + | information_schema | Macro | TF | conan | dbwordpress | mysql | performance_schema | phpmyadmin | test | wordpress | + -------------------- + 10 rows in set (0.01 sec) # view ibdata1 size ~ Ls-l/var/lib/mysqldrwx ------ 2 mysql 4096 Aug 2 CBdrwx ------ 2 mysql 4096 Aug 2 conandrwx ------ 2 mysql 4096 Aug 2 dbwordpress-rw ---- 1 mysql 18874368 Aug 2 ibdata1-rw-rw ---- 1 mysql 5242880 Aug 2 ib_logfile0-rw-rw ---- 1 mysql 5242880 Aug 2 ib_logfile1drwx ------ 2 mysql 4096 Aug 2 Macrodrwx ------ 2 mysql root 4096 Aug 2 mysqldrwx ------ 2 mysql 4096 Aug 2 performance_schemadrwx ------ 2 mysql 4096 Aug 2 phpmyadmindrwx ------ 2 mysql root 4096 Aug 2 testdrwx ------ 2 mysql 4096 Aug 2 TFdrwx ------ 2 mysql 4096 Aug 2 wordpress

4) reset the root password

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');mysql> FLUSH PRIVILEGES;

5). Restore the CB database

Mysql> create database CB; Query OK, 1 row affected (0.00 sec )~ Mysql -- database CB </run/shm/export_cb_hft_20130801. SQL ~ Mysql -- database CB </dev/export_cb_hft_20130802. SQL ~ Mysql -- database CB </dev/export_NSpremium. SQL # Check whether the size of ibdata1 has not increased ~ Ls-l/var/lib/mysqldrwx ------ 2 mysql 4096 Aug 2 CBdrwx ------ 2 mysql 4096 Aug 2 conandrwx ------ 2 mysql 4096 Aug 2 dbwordpress-rw ---- 1 mysql 18874368 Aug 2 ibdata1-rw-rw ---- 1 mysql 5242880 Aug 2 ib_logfile0-rw-rw ---- 1 mysql 5242880 Aug 2 ib_logfile1drwx ------ 2 mysql 4096 Aug 2 Macrodrwx ------ 2 mysql root 4096 Aug 2 mysqldrwx ------ 2 mysql 4096 Aug 2 performance_schemadrwx ------ 2 mysql 4096 Aug 2 phpmyadmindrwx ------ 2 mysql root 4096 Aug 2 testdrwx ------ 2 mysql 4096 Aug 2 TFdrwx ------ 2 mysql 4096 Aug 2 wordpress # view the CB library directory: all data is stored in a separate data file ~ Ls-l/var/lib/mysql/CB-rw ---- 1 mysql 9928 Aug 2 cb_hft_20130801.frm-rw-rw ---- 1 mysql 7159676928 Aug 2 cb_hft_20130801.ibd-rw-rw ---- 1 mysql 9928 Aug 2 cb_hft_20130802.frm-rw-rw ---- 1 mysql 7805599744 Aug 2 cb_hft_20130802.ibd-rw-rw ---- 1 mysql 61 Aug 2 db. opt

The innodb_file_per_table parameter just set has taken effect. After we export the table drop, the corresponding data file idb will be deleted, and the system hard disk space will be used within the normal range.

View table data

Mysql> show tables; + --------------- + | Tables_in_CB | + ----------------- + | cb_hft_20130801 | bytes | + ----------------- + 2 rows in set (0.00 sec) mysql> select count (1) from cb_hft_20130801; + ---------- + | count (1) | + ---------- + | 21063172 | + ---------- + 1 row in set (1 min 1.46 sec) # delete a table ~ Drop table cb_hft_20130801; # View data files ~ Ls-l/var/lib/mysql/CB-rw ---- 1 mysql 9928 Aug 2 cb_hft_20130802.frm-rw-rw ---- 1 mysql 7805599744 Aug 2 cb_hft_20130802.ibd-rw-rw ---- 1 mysql 61 Aug 2 db. opt-rw ---- 1 mysql 9274 Aug 2 NSpremium. frm-rw ---- 1 mysql 98304 Aug 2 NSpremium. ibd

After drop, the data is deleted.

After tuning MySQL, ibdata1 has been reduced! The database can continue to work normally and stably.

Http://blog.fens.me/mysql-ibdata1/

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.