[MySQL优化]为MySQL数据文件ibdata1瘦身
系统环境 发现问题 解决问题 1. 系统环境 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. 系统环境
Linux Ubuntu 13.04 64bit server
<code>~ uname -a 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 </code>
MySQL: 5.5.31-0ubuntu0.13.04.1
<code>~ mysql --version mysql Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (x86_64) using readline 6.2 </code>
硬盘：36G+4G+4G+36G
<code>~ df -h Filesystem 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/cgroup udev 4.1G 1.1G 3.0G 26% /dev tmpfs 824M 280K 823M 1% /run none 5.0M 0 5.0M 0% /run/lock none 4.1G 3.4G 729M 83% /run/shm none 100M 0 100M 0% /run/user /dev/vda1 228M 30M 187M 14% /boot 192.168.1.10:/home/amg/data 36G 13G 21G 39% /home/amg/data </code>
MySQL的ibdata1占用空间:20G
<code>~ cd /var/lib/mysql ~ ls -l drwxr-xr-x 2 mysql mysql 4096 Aug 2 19:38 CB drwxr-xr-x 2 mysql mysql 4096 Jun 24 23:08 conan drwxr-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_logfile1 drwxr-xr-x 2 mysql mysql 4096 Jun 26 09:03 Macro drwxr-xr-x 2 mysql root 4096 May 23 00:48 mysql -rwxr-xr-x 1 root root 6 May 23 00:48 mysql_upgrade_info drwxr-xr-x 2 mysql mysql 4096 May 23 00:48 performance_schema drwxr-xr-x 2 mysql mysql 4096 May 23 00:53 phpmyadmin drwxr-xr-x 2 mysql root 4096 May 23 00:48 test drwxr-xr-x 2 mysql mysql 4096 Jul 22 14:09 TF drwxr-xr-x 2 mysql mysql 4096 Jun 2 01:04 wordpress </code>
业务数据表
<code>mysql> show tables; +-----------------+ | Tables_in_CB | +-----------------+ | NSpremium | | cb_hft | | cb_hft_20130801 | | cb_hft_20130802 | +-----------------+ 4 rows in set (0.00 sec) </code>
2. 发现问题
ibdata1单个文件占用20G大小。
1. MySQL默认设置，没有按表空间分离数据，所有的表的数据都被放到ibdata1文件中。
2. 业务操作，每天会产生一张表cb_hft，晚上的时候对表进行重命名。
<code>RENAME TABLE cb_hft TO cb_hft_20130801; create table cb_hft like cb_hft_20130801; </code>
3. 每周会把数据导出，同时drop表。但drop后，ibdata1不会减少，随着数据的积累ibdata1越来越大，根空间已经不够用了。
3. 解决问题
1). 导出数据
现在数据库中，有两个数据表，cb_hft_20130801,cb_hft_20130802，分别导出到/run/shm, /dev
<code>~ 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 -h Filesystem 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/cgroup udev 4.1G 3.7G 368M 92% /dev tmpfs 824M 280K 823M 1% /run none 5.0M 0 5.0M 0% /run/lock none 4.1G 3.4G 729M 83% /run/shm none 100M 0 100M 0% /run/user /dev/vda1 228M 30M 187M 14% /boot 192.168.1.10:/home/amg/data 36G 13G 21G 39% /home/amg/data </code>
两张表分别占了，3.4G，3.7G。
登陆mysql删除CB数据库
<code>~ mysql -uroot -p ~ drop database CB </code>
导出其他数据库数据
<code>~ cd /run/shm ~ mysqldump -uroot -p -R -q --all-databases > others.sql </code>
2). 修改配置文件/etc/mysql/my.cnf
对每张表使用单独的数据文件存储innodb_file_per_table
停止mysql服务器
<code>~ sudo /etc/init.d/mysql stop #清空所有数据文件 ~ sudo rm -rf /var/lib/mysql/* </code>
修改配置文件
<code>~ sudo vi /etc/mysql/my.cnf [mysqld] innodb_file_per_table </code>
重新构建数据库实例
<code>~ /usr/bin/mysql_install_db ~ ls /var/lib/mysql mysql performance_schema test #启动MySQL ~ sudo /etc/init.d/mysql start </code>
3). 恢复其他数据库
<code>~ mysql show databases; +--------------------+ | Database | +--------------------+ | information_schema | | Macro | | TF | | conan | | dbwordpress | | mysql | | performance_schema | | phpmyadmin | | test | | wordpress | +--------------------+ 10 rows in set (0.01 sec) #查看ibdata1大小 ~ ls -l /var/lib/mysql drwx------ 2 mysql mysql 4096 Aug 2 21:33 CB drwx------ 2 mysql mysql 4096 Aug 2 21:23 conan drwx------ 2 mysql mysql 4096 Aug 2 21:23 dbwordpress -rw-rw---- 1 mysql mysql 18874368 Aug 2 21:34 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Aug 2 21:34 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Aug 2 21:34 ib_logfile1 drwx------ 2 mysql mysql 4096 Aug 2 21:23 Macro drwx------ 2 mysql root 4096 Aug 2 21:23 mysql drwx------ 2 mysql mysql 4096 Aug 2 21:19 performance_schema drwx------ 2 mysql mysql 4096 Aug 2 21:23 phpmyadmin drwx------ 2 mysql root 4096 Aug 2 21:19 test drwx------ 2 mysql mysql 4096 Aug 2 21:23 TF drwx------ 2 mysql mysql 4096 Aug 2 21:23 wordpress </code>
4). 重置root密码
<code>mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
mysql> FLUSH PRIVILEGES;
</code>5). 恢复CB数据库
<code>mysql> create database CB; Query OK, 1 row affected (0.00 sec) ~ mysql --database CB </code>
刚才设置的innodb_file_per_table参数已经起作用了，当我们再导出表drop后，对应的数据文件idb就会被删除，系统硬盘空间使用就会在正常值范围内。
查看表数据
<code>mysql> show tables; +-----------------+ | Tables_in_CB | +-----------------+ | cb_hft_20130801 | | cb_hft_20130802 | +-----------------+ 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) #删除表 ~ drop table cb_hft_20130801; #查看数据文件 ~ ls -l /var/lib/mysql/CB -rw-rw---- 1 mysql mysql 9928 Aug 2 22:09 cb_hft_20130802.frm -rw-rw---- 1 mysql mysql 7805599744 Aug 2 22:38 cb_hft_20130802.ibd -rw-rw---- 1 mysql mysql 61 Aug 2 21:30 db.opt -rw-rw---- 1 mysql mysql 9274 Aug 2 22:52 NSpremium.frm -rw-rw---- 1 mysql mysql 98304 Aug 2 22:53 NSpremium.ibd </code>
drop后，数据就一起被删除了。
经过对MySQL的调优，ibdata1已经被瘦身！数据库又可以继续正常的稳定的工作了。
http://blog.fens.me/mysql-ibdata1/
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1655
14
1413
52
1306
25
1252
29
1226
24
MySQL's Role: Databases in Web Applications
Apr 17, 2025 am 12:23 AM
The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.
How to start mysql by docker
Apr 15, 2025 pm 12:09 PM
The process of starting MySQL in Docker consists of the following steps: Pull the MySQL image to create and start the container, set the root user password, and map the port verification connection Create the database and the user grants all permissions to the database
Laravel Introduction Example
Apr 18, 2025 pm 12:45 PM
Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.
Solve database connection problem: a practical case of using minii/db library
Apr 18, 2025 am 07:09 AM
I encountered a tricky problem when developing a small application: the need to quickly integrate a lightweight database operation library. After trying multiple libraries, I found that they either have too much functionality or are not very compatible. Eventually, I found minii/db, a simplified version based on Yii2 that solved my problem perfectly.
Laravel framework installation method
Apr 18, 2025 pm 12:54 PM
Article summary: This article provides detailed step-by-step instructions to guide readers on how to easily install the Laravel framework. Laravel is a powerful PHP framework that speeds up the development process of web applications. This tutorial covers the installation process from system requirements to configuring databases and setting up routing. By following these steps, readers can quickly and efficiently lay a solid foundation for their Laravel project.
How to install mysql in centos7
Apr 14, 2025 pm 08:30 PM
The key to installing MySQL elegantly is to add the official MySQL repository. The specific steps are as follows: Download the MySQL official GPG key to prevent phishing attacks. Add MySQL repository file: rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm Update yum repository cache: yum update installation MySQL: yum install mysql-server startup MySQL service: systemctl start mysqld set up booting
MySQL and phpMyAdmin: Core Features and Functions
Apr 22, 2025 am 12:12 AM
MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.
MySQL vs. Other Programming Languages: A Comparison
Apr 19, 2025 am 12:22 AM
Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.
