bitsCN.com
为MySQL数据文件ibdata1瘦身
MySQL在运行一段时间后，ibdata1的文件会增长大小，就算删除了表的数据，ibdata1的体积也不会减小。由于硬盘空间有限，这样一直膨胀下去磁盘空间接近崩溃。今天在导出数据的时候就发现了，磁盘竟然满了，明明预留了1个月的用量，1周就占满了,下面就要给ibdata1做个瘦身。
1. 系统环境
Linux Ubuntu 13.04 64bit server
~ 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
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
硬盘：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/dataMySQL的ibdata1占用空间:20G~ 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业务数据表mysql> show tables;+-----------------+| Tables_in_CB |+-----------------+| NSpremium || cb_hft || cb_hft_20130801 || cb_hft_20130802 |+-----------------+4 rows in set (0.00 sec)
2. 发现问题
ibdata1单个文件占用20G大小。
1. MySQL默认设置，没有按表空间分离数据，所有的表的数据都被放到ibdata1文件中。
2. 业务操作，每天会产生一张表cb_hft，晚上的时候对表进行重命名。
RENAME TABLE cb_hft TO cb_hft_20130801;
create table cb_hft like cb_hft_20130801;
3. 每周会把数据导出，同时drop表。但drop后，ibdata1不会减少，随着数据的积累ibdata1越来越大，根空间已经不够用了。
3. 解决问题
1). 导出数据
现在数据库中，有两个数据表，cb_hft_20130801,cb_hft_20130802，分别导出到/run/shm, /dev
~ 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两张表分别占了，3.4G，3.7G。登陆mysql删除CB数据库~ mysql -uroot -p~ drop database CB导出其他数据库数据~ cd /run/shm~ mysqldump -uroot -p -R -q --all-databases > others.sql
2). 修改配置文件/etc/mysql/my.cnf
对每张表使用单独的数据文件存储innodb_file_per_table
停止mysql服务器~ sudo /etc/init.d/mysql stop#清空所有数据文件~ sudo rm -rf /var/lib/mysql/*修改配置文件~ sudo vi /etc/mysql/my.cnf[mysqld]innodb_file_per_table重新构建数据库实例~ /usr/bin/mysql_install_db~ ls /var/lib/mysqlmysql performance_schema test#启动MySQL~ sudo /etc/init.d/mysql start
3). 恢复其他数据库
~ 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)#查看ibdata1大小~ ls -l /var/lib/mysqldrwx------ 2 mysql mysql 4096 Aug 2 21:33 CBdrwx------ 2 mysql mysql 4096 Aug 2 21:23 conandrwx------ 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_logfile1drwx------ 2 mysql mysql 4096 Aug 2 21:23 Macrodrwx------ 2 mysql root 4096 Aug 2 21:23 mysqldrwx------ 2 mysql mysql 4096 Aug 2 21:19 performance_schemadrwx------ 2 mysql mysql 4096 Aug 2 21:23 phpmyadmindrwx------ 2 mysql root 4096 Aug 2 21:19 testdrwx------ 2 mysql mysql 4096 Aug 2 21:23 TFdrwx------ 2 mysql mysql 4096 Aug 2 21:23 wordpress
4). 恢复CB数据库
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#查看ibdata1大小：还是出初始值没有增长~ ls -l /var/lib/mysqldrwx------ 2 mysql mysql 4096 Aug 2 21:33 CBdrwx------ 2 mysql mysql 4096 Aug 2 21:23 conandrwx------ 2 mysql mysql 4096 Aug 2 21:23 dbwordpress-rw-rw---- 1 mysql mysql 18874368 Aug 2 22:01 ibdata1-rw-rw---- 1 mysql mysql 5242880 Aug 2 22:01 ib_logfile0-rw-rw---- 1 mysql mysql 5242880 Aug 2 22:01 ib_logfile1drwx------ 2 mysql mysql 4096 Aug 2 21:23 Macrodrwx------ 2 mysql root 4096 Aug 2 21:23 mysqldrwx------ 2 mysql mysql 4096 Aug 2 21:19 performance_schemadrwx------ 2 mysql mysql 4096 Aug 2 21:23 phpmyadmindrwx------ 2 mysql root 4096 Aug 2 21:19 testdrwx------ 2 mysql mysql 4096 Aug 2 21:23 TFdrwx------ 2 mysql mysql 4096 Aug 2 21:23 wordpress#查看CB库目录：所有的数据都保存在自己单独的数据文件~ ls -l /var/lib/mysql/CB-rw-rw---- 1 mysql mysql 9928 Aug 2 21:33 cb_hft_20130801.frm-rw-rw---- 1 mysql mysql 7159676928 Aug 2 22:08 cb_hft_20130801.ibd-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
刚才设置的innodb_file_per_table参数已经起作用了，当我们再导出表drop后，对应的数据文件idb就会被删除，系统硬盘空间使用就会在正常值范围内。
查看表数据
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
drop后，数据就一起被删除了。
经过对MySQL的调优，ibdata1已经被瘦身！数据库又可以继续正常的稳定的工作了。
bitsCN.com
MySQL：世界で最も人気のあるデータベースの紹介Apr 12, 2025 am 12:18 AMMySQLはオープンソースのリレーショナルデータベース管理システムであり、主にデータを迅速かつ確実に保存および取得するために使用されます。その実用的な原則には、クライアントリクエスト、クエリ解像度、クエリの実行、返品結果が含まれます。使用法の例には、テーブルの作成、データの挿入とクエリ、および参加操作などの高度な機能が含まれます。一般的なエラーには、SQL構文、データ型、およびアクセス許可、および最適化の提案には、インデックスの使用、最適化されたクエリ、およびテーブルの分割が含まれます。
MySQLの重要性：データストレージと管理Apr 12, 2025 am 12:18 AMMySQLは、データストレージ、管理、クエリ、セキュリティに適したオープンソースのリレーショナルデータベース管理システムです。 1.さまざまなオペレーティングシステムをサポートし、Webアプリケーションやその他のフィールドで広く使用されています。 2。クライアントサーバーアーキテクチャとさまざまなストレージエンジンを通じて、MySQLはデータを効率的に処理します。 3.基本的な使用には、データベースとテーブルの作成、挿入、クエリ、データの更新が含まれます。 4.高度な使用には、複雑なクエリとストアドプロシージャが含まれます。 5.一般的なエラーは、説明ステートメントを介してデバッグできます。 6.パフォーマンスの最適化には、インデックスの合理的な使用と最適化されたクエリステートメントが含まれます。
なぜMySQLを使用するのですか？利点と利点Apr 12, 2025 am 12:17 AMMySQLは、そのパフォーマンス、信頼性、使いやすさ、コミュニティサポートに選択されています。 1.MYSQLは、複数のデータ型と高度なクエリ操作をサポートし、効率的なデータストレージおよび検索機能を提供します。 2.クライアントサーバーアーキテクチャと複数のストレージエンジンを採用して、トランザクションとクエリの最適化をサポートします。 3.使いやすく、さまざまなオペレーティングシステムとプログラミング言語をサポートしています。 4.強力なコミュニティサポートを提供し、豊富なリソースとソリューションを提供します。
InnoDBロックメカニズム（共有ロック、排他的ロック、意図ロック、レコードロック、ギャップロック、次のキーロック）を説明します。Apr 12, 2025 am 12:16 AMINNODBのロックメカニズムには、共有ロック、排他的ロック、意図ロック、レコードロック、ギャップロック、次のキーロックが含まれます。 1.共有ロックにより、トランザクションは他のトランザクションが読み取らないようにデータを読み取ることができます。 2.排他的ロックは、他のトランザクションがデータの読み取りと変更を防ぎます。 3.意図ロックは、ロック効率を最適化します。 4。ロックロックインデックスのレコードを記録します。 5。ギャップロックロックインデックス記録ギャップ。 6.次のキーロックは、データの一貫性を確保するためのレコードロックとギャップロックの組み合わせです。
貧弱なMySQLクエリパフォーマンスの一般的な原因は何ですか？Apr 12, 2025 am 12:11 AMMySQLクエリのパフォーマンスが低いことの主な理由には、インデックスの使用、クエリオプティマイザーによる誤った実行計画の選択、不合理なテーブルデザイン、過剰なデータボリューム、ロック競争などがあります。 1.インデックスがゆっくりとクエリを引き起こし、インデックスを追加するとパフォーマンスが大幅に向上する可能性があります。 2。説明コマンドを使用してクエリ計画を分析し、オプティマイザーエラーを見つけます。 3.テーブル構造の再構築と結合条件を最適化すると、テーブルの設計上の問題が改善されます。 4.データボリュームが大きい場合、パーティション化とテーブル分割戦略が採用されます。 5.高い並行性環境では、トランザクションの最適化とロック戦略は、ロック競争を減らすことができます。
複数の単一列インデックスに対して複合インデックスをいつ使用する必要がありますか？Apr 11, 2025 am 12:06 AMデータベースの最適化では、クエリ要件に従ってインデックス作成戦略を選択する必要があります。1。クエリに複数の列が含まれ、条件の順序が固定されている場合、複合インデックスを使用します。 2。クエリに複数の列が含まれているが、条件の順序が修正されていない場合、複数の単一列インデックスを使用します。複合インデックスは、マルチコラムクエリの最適化に適していますが、単一列インデックスは単一列クエリに適しています。
MySQLでスロークエリを識別して最適化する方法は？ （スロークエリログ、Performance_schema）Apr 10, 2025 am 09:36 AMMySQLスロークエリを最適化するには、slowquerylogとperformance_schemaを使用する必要があります。1。LowerQueryLogを有効にし、しきい値を設定して、スロークエリを記録します。 2。performance_schemaを使用してクエリの実行の詳細を分析し、パフォーマンスのボトルネックを見つけて最適化します。
MySQLおよびSQL：開発者にとって不可欠なスキルApr 10, 2025 am 09:30 AMMySQLとSQLは、開発者にとって不可欠なスキルです。 1.MYSQLはオープンソースのリレーショナルデータベース管理システムであり、SQLはデータベースの管理と操作に使用される標準言語です。 2.MYSQLは、効率的なデータストレージと検索機能を介して複数のストレージエンジンをサポートし、SQLは簡単なステートメントを通じて複雑なデータ操作を完了します。 3.使用の例には、条件によるフィルタリングやソートなどの基本的なクエリと高度なクエリが含まれます。 4.一般的なエラーには、SQLステートメントをチェックして説明コマンドを使用することで最適化できる構文エラーとパフォーマンスの問題が含まれます。 5.パフォーマンス最適化手法には、インデックスの使用、フルテーブルスキャンの回避、参加操作の最適化、コードの読み取り可能性の向上が含まれます。
ホットAIツール
Undresser.AI Undress
リアルなヌード写真を作成する AI 搭載アプリ
AI Clothes Remover
写真から衣服を削除するオンライン AI ツール。
Undress AI Tool
脱衣画像を無料で
Clothoff.io
AI衣類リムーバー
AI Hentai Generator
AIヘンタイを無料で生成します。
人気の記事
ホットツール
ドリームウィーバー CS6
ビジュアル Web 開発ツール
SecLists
SecLists は、セキュリティ テスターの究極の相棒です。これは、セキュリティ評価中に頻繁に使用されるさまざまな種類のリストを 1 か所にまとめたものです。 SecLists は、セキュリティ テスターが必要とする可能性のあるすべてのリストを便利に提供することで、セキュリティ テストをより効率的かつ生産的にするのに役立ちます。リストの種類には、ユーザー名、パスワード、URL、ファジング ペイロード、機密データ パターン、Web シェルなどが含まれます。テスターはこのリポジトリを新しいテスト マシンにプルするだけで、必要なあらゆる種類のリストにアクセスできるようになります。
PhpStorm Mac バージョン
最新(2018.2.1)のプロフェッショナル向けPHP統合開発ツール
ZendStudio 13.5.1 Mac
強力な PHP 統合開発環境
SublimeText3 Linux 新バージョン
SublimeText3 Linux 最新バージョン
