[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/
ホットAIツール
Undresser.AI Undress
リアルなヌード写真を作成する AI 搭載アプリ
AI Clothes Remover
写真から衣服を削除するオンライン AI ツール。
Undress AI Tool
脱衣画像を無料で
Clothoff.io
AI衣類リムーバー
Video Face Swap
完全無料の AI 顔交換ツールを使用して、あらゆるビデオの顔を簡単に交換できます。
人気の記事
ホットツール
メモ帳++7.3.1
使いやすく無料のコードエディター
SublimeText3 中国語版
中国語版、とても使いやすい
ゼンドスタジオ 13.0.1
強力な PHP 統合開発環境
ドリームウィーバー CS6
ビジュアル Web 開発ツール
SublimeText3 Mac版
神レベルのコード編集ソフト（SublimeText3）
ホットトピック
7698
15
1640
14
1393
52
1287
25
1229
29
phpmyadminを開く方法
Apr 10, 2025 pm 10:51 PM
次の手順でphpmyadminを開くことができます。1。ウェブサイトコントロールパネルにログインします。 2。phpmyadminアイコンを見つけてクリックします。 3。MySQL資格情報を入力します。 4.「ログイン」をクリックします。
MySQL：世界で最も人気のあるデータベースの紹介
Apr 12, 2025 am 12:18 AM
MySQLはオープンソースのリレーショナルデータベース管理システムであり、主にデータを迅速かつ確実に保存および取得するために使用されます。その実用的な原則には、クライアントリクエスト、クエリ解像度、クエリの実行、返品結果が含まれます。使用法の例には、テーブルの作成、データの挿入とクエリ、および参加操作などの高度な機能が含まれます。一般的なエラーには、SQL構文、データ型、およびアクセス許可、および最適化の提案には、インデックスの使用、最適化されたクエリ、およびテーブルの分割が含まれます。
MySQLの場所：データベースとプログラミング
Apr 13, 2025 am 12:18 AM
データベースとプログラミングにおけるMySQLの位置は非常に重要です。これは、さまざまなアプリケーションシナリオで広く使用されているオープンソースのリレーショナルデータベース管理システムです。 1）MySQLは、効率的なデータストレージ、組織、および検索機能を提供し、Web、モバイル、およびエンタープライズレベルのシステムをサポートします。 2）クライアントサーバーアーキテクチャを使用し、複数のストレージエンジンとインデックスの最適化をサポートします。 3）基本的な使用には、テーブルの作成とデータの挿入が含まれ、高度な使用法にはマルチテーブル結合と複雑なクエリが含まれます。 4）SQL構文エラーやパフォーマンスの問題などのよくある質問は、説明コマンドとスロークエリログを介してデバッグできます。 5）パフォーマンス最適化方法には、インデックスの合理的な使用、最適化されたクエリ、およびキャッシュの使用が含まれます。ベストプラクティスには、トランザクションと準備された星の使用が含まれます
なぜMySQLを使用するのですか？利点と利点
Apr 12, 2025 am 12:17 AM
MySQLは、そのパフォーマンス、信頼性、使いやすさ、コミュニティサポートに選択されています。 1.MYSQLは、複数のデータ型と高度なクエリ操作をサポートし、効率的なデータストレージおよび検索機能を提供します。 2.クライアントサーバーアーキテクチャと複数のストレージエンジンを採用して、トランザクションとクエリの最適化をサポートします。 3.使いやすく、さまざまなオペレーティングシステムとプログラミング言語をサポートしています。 4.強力なコミュニティサポートを提供し、豊富なリソースとソリューションを提供します。
Apacheのデータベースに接続する方法
Apr 13, 2025 pm 01:03 PM
Apacheはデータベースに接続するには、次の手順が必要です。データベースドライバーをインストールします。 web.xmlファイルを構成して、接続プールを作成します。 JDBCデータソースを作成し、接続設定を指定します。 JDBC APIを使用して、接続の取得、ステートメントの作成、バインディングパラメーター、クエリまたは更新の実行、結果の処理など、Javaコードのデータベースにアクセスします。
DockerによるMySQLを開始する方法
Apr 15, 2025 pm 12:09 PM
DockerでMySQLを起動するプロセスは、次の手順で構成されています。MySQLイメージをプルしてコンテナを作成および起動し、ルートユーザーパスワードを設定し、ポート検証接続をマップしてデータベースを作成し、ユーザーはすべての権限をデータベースに付与します。
MySQLの役割：Webアプリケーションのデータベース
Apr 17, 2025 am 12:23 AM
WebアプリケーションにおけるMySQLの主な役割は、データを保存および管理することです。 1.MYSQLは、ユーザー情報、製品カタログ、トランザクションレコード、その他のデータを効率的に処理します。 2。SQLクエリを介して、開発者はデータベースから情報を抽出して動的なコンテンツを生成できます。 3.MYSQLは、クライアントサーバーモデルに基づいて機能し、許容可能なクエリ速度を確保します。
Centosはmysqlをインストールします
Apr 14, 2025 pm 08:09 PM
CentOSにMySQLをインストールするには、次の手順が含まれます。適切なMySQL Yumソースの追加。 yumを実行して、mysql-serverコマンドをインストールして、mysqlサーバーをインストールします。ルートユーザーパスワードの設定など、MySQL_SECURE_INSTALLATIONコマンドを使用して、セキュリティ設定を作成します。必要に応じてMySQL構成ファイルをカスタマイズします。 MySQLパラメーターを調整し、パフォーマンスのためにデータベースを最適化します。
