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.
- System Environment
- Problems Found
- 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/