MySQL の InnoDB 拡張と ibdata1 ファイルスリム化ソリューションの完全な分析_MySQL

mysql innodb 拡張
テーブルスペースにデータファイルを追加するには、まず MySQL データベースを閉じ、my.cnf ファイルを編集し、innodb ibdata ファイルの実際の状況が my.cnf の設定と一致しているかどうかを確認します。ここでの状況:
1.my.cnfの設定

リーリー

現在のデータベースが ibdata1 または ibdata2 を使用しているが、ibdata2 が 10G を超えない場合は、my.cnf 構成を次のように直接変更します。

リーリー

2. 最後の ibdata が自動的に展開される場合、最後の ibdata が占有するスペースが my.cnf の構成スペースよりも大きくなる可能性があります。例:

リーリー

リーリー

このとき、ibdata2 15360Mのサイズを正確に計算する必要があります。変更:

リーリー

mysqlを再起動します。

注:
1. 拡張する前に、ディスク容量が十分であるかどうかに注意してください。 2. 再起動後、新しい ibdata が生成されるかどうかに注意してください。
詳しい手順:
最後のファイルがキーワード autoextend で記述されている場合、my.cnf を編集するときに、最後のファイルのサイズをチェックし、1024 * 1024 バイト (= 1 MB) の倍数に近づける必要があります (たとえば、/ autoextend の ibdata/ibdata1 は 18.5M ですが、古い my.ini では 10M になっており、20M に変更する必要があります。報告されています)。また、そのサイズを innodb_data_file_path で明示的に指定します。その後、別のデータ ファイルを追加できます。 innodb_data_file_path 内の最後のファイルのみを自動拡張として指定できることに注意してください。
例: 最初に自動拡張データ ファイル ibdata1 が 1 つだけあり、このファイルが 988 MB に近いとします。次に、別の自動拡張データ ファイルを追加した後の考えられる例を示します。

リーリー

ibdata1 痩身

0. ibdata1に格納されるもの

innodb_file_per_table を有効にすると、テーブルは独自のテーブルスペースに保存されますが、共有テーブルスペースには引き続き他の InnoDB 内部データが保存されます。 (1) InnoDBテーブルのメタデータであるデータディクショナリ

(2) バッファを変更する
(3) ダブルライトバッファ
(4) アンドゥログ

これらの一部は、過度の増大を避けるために Percona サーバーで構成できます。たとえば、innodb_ibuf_max_size を使用して最大変更バッファを設定したり、二重書き込みバッファを別のファイルに保存するように innodb_doublewrite_file を設定したりできます。

MySQL バージョン 5.6 では、外部 UNDO テーブルスペースを作成することもできるため、ibdata1 に保存するのではなく、独自のファイルに配置することができます。

1. ibdata1 が急速に成長する原因は何ですか?

MySQL に問題がある場合、通常、最初に実行する必要があるコマンドは次のとおりです:

リーリー

これにより、いくつかの貴重な情報が表示されます。 TRANSACTION セクションから始めると、次のことがわかります:

リーリー

これが最も一般的な理由で、14 日前に作成されたかなり古いトランザクションです。この状態はアクティブです。これは、InnoDB がデータのスナップショットを作成したことを意味するため、トランザクションが開始されるまでデータベースの一貫したビューを確保するために、古いページを UNDO ログに保持する必要があります。データベースに大量の書き込みがある場合は、大量の取り消しページが保存されていることを意味します。

長時間実行されているトランザクションが見つからない場合は、INNODB STATUS の他の変数を監視して、保留中のクリーンアップ操作を確認することもできます。この場合、クリーンアップ スレッド (または古いバージョンのメイン スレッド) がこれらのレコードを受信するのと同じくらい速く元に戻す処理を処理できないため、問題がよく発生します。

2. ibdata1 に何が保存されているかを確認するにはどうすればよいですか?

残念ながら、MySQL は ibdata1 共有テーブルスペースに何が保存されているかに関する情報を提供しませんが、2 つのツールが役立ちます。 1 つ目は、Mark Callahan によって作成され、この脆弱性レポートで公開された innochecksum の修正バージョンです。

使い方はとても簡単です:

リーリー

合計 20608 件中、19272 件の元に戻すログ ページがあります。これはテーブルスペースの 93% を占めます。

テーブルスペースの内容を確認する 2 番目の方法は、Jeremy Cole による InnoDB Ruby ツールです。これは、InnoDB の内部構造を検査するためのより高度なツールです。たとえば、space-summary パラメータを使用して、各ページとそのデータ型のリストを取得できます。標準の Unix ツールを使用して、Undo ログ ページの数をカウントできます:

リーリー

この特定のケースでは、innochedcksum の方が高速で使いやすいですが、InnoDB のデータ分散と内部構造について詳しく学ぶには、Jeremy のツールを使用することをお勧めします。

さて、問題はわかりました。

3. ibdata1 瘦身方案
其中的一些在 Percona 服务器上可以被配置来避免增长过大的。例如你可以通过 innodb_ibuf_max_size 设置最大变更缓冲区，或设置 innodb_doublewrite_file 来将双写缓冲区存储到一个分离的文件。

MySQL 5.6 版中你也可以创建外部的撤销表空间，所以它们可以放到自己的文件来替代存储到 ibdata1。

通常不能移除 InnoDB 的数据文件。为了减小数据文件的大小，你必须使用 mysqldump 来转储(dump)所有的数据表，再重新建立一个新的数据库，并将数据导入新的数据库中。具体步骤如下：
（1）备份数据库
mysqldump -uroot -p123456 --default-character-set=utf8 --opt --extended-insert=true --triggers -R --hex-blob --single-transaction --no-autocommit  test > db_name.sql  <br> （2）停止数据库

service mysqld stop 

（3）删除相关文件

ibdata1 
ib_logfile* 
mysql-bin.index 

（4）手动删除除Mysql之外所有数据库文件夹，然后启动数据库 

service mysqld start 

（5）还原数据

 /usr/local/mysql/bin/mysql -uroot -phigkoo < /data/bkup/mysqldump.sql 

主要是使用Mysqldump时的一些参数，建议在使用前看一个说明再操作。另外备份前可以先查看一下当前数据库里哪些表占用空间大，把一些不必要的给truncate table掉。这样省些空间和时间