MySQLどのようにテーブルスペースを復元しますか

MySQLどのようにテーブルスペースを復元しますか？

これは新しい情報ではありませんが、あまり取り上げていませんので、必要な人のためにここで取り上げます。

ibdファイルを紛失すると、データが失われます。 ですから、利用可能なもののコピーがある場合、または他のデータベースから同期している場合でも、それをインポートできます。 どのように/どのようにあなたはテーブルスペースを失いますか？

これはテーブルスペースを回復する簡単な例です。



mysql> Create database demo;

mysql> use demo;

mysql> CREATE TABLE `demotable` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `dts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;

今、私たちはいくつかのデータを保存します...


mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.10 sec)

mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM demotable;
+----+---------------------+
| id | dts |
+----+---------------------+
| 1 | 2019-07-12 23:31:34 |
| 2 | 2019-07-12 23:31:35 |
+----+---------------------+
2 rows in set (0.00 sec)

[OK]を今すぐそれを破ることができます..


# systemctl stop mysqld
# cd /var/lib/mysql/demo/
# ls -ltr
total 80
-rw-r-----. 1 mysql mysql 114688 Jul 12 23:31 demotable.ibd
# mv demotable.ibd /tmp/

# systemctl start mysqld
# mysql demo

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| demotable |
+----------------+
1 row in set (0.00 sec)

mysql> desc demotable;
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| dts | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
2 rows in set (0.01 sec)

mysql> INSERT INTO demotable (id) VALUES (NULL);
ERROR 1812 (HY000): Tablespace is missing for table `demo`.`demotable`.

壊れて失われた表領域...これで回復できます。


demo]# cp /tmp/demotable.ibd .

mysql> ALTER TABLE demotable DISCARD TABLESPACE;

demo]# cp /tmp/demotable.ibd .
demo]# ls -ltr
total 112
-rw-r-----. 1 root root 114688 Jul 12 23:50 demotable.ibd
demo]# chown mysql:mysql demotable.ibd
demo]# mysql demo
mysql> ALTER TABLE demotable IMPORT TABLESPACE;
ERROR 1034 (HY000): Incorrect key file for table 'demotable'; try to repair it

mysql> REPAIR TABLE demotable;
+----------------+--------+----------+---------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+--------+----------+---------------------------------------------------------+
| demo.demotable | repair | note | The storage engine for the table doesn't support repair |
+----------------+--------+----------+---------------------------------------------------------+

今、私たちはまた別のエラーを得ました..これは通常tmpdirに利用可能なスペースに結び付けられていて、修復はとにかく.ibdのためにうまくいきません。


mysql> select @@tmpdir;
+----------+
| @@tmpdir |
+----------+
| /tmp |
+----------+

# vi /etc/my.cnf
tmpdir=/var/lib/mysql-files/

# systemctl restart mysqld
# mysql demo

たとえば、mysql-filesディレクトリを使用しました。
今すぐもう一度試すことができます。


mysql> ALTER TABLE demotable IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.61 sec)

mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.11 sec)

mysql> SELECT * FROM demotable;
+----+---------------------+
| id | dts |
+----+---------------------+
| 1 | 2019-07-12 23:31:34 |
| 2 | 2019-07-12 23:31:35 |
| 3 | 2019-07-12 23:56:08 |
+----+---------------------+

うまくいきました。
テーブルが1つしかない場合は、これですべて簡単で簡単です。 しかし、100年代はどうでしょうか。

それを自動化し、もちろん、あなたのinformation_schemaを使って助けてください。

テスト用にさらにコピーをいくつか作成します。


mysql> create table demotable1 like demotable;
Query OK, 0 rows affected (0.51 sec)

mysql> create table demotable2 like demotable;
Query OK, 0 rows affected (1.04 sec)

mysql> create table demotable3 like demotable;
Query OK, 0 rows affected (0.74 sec)

mysql> create table demotable4 like demotable;
Query OK, 0 rows affected (2.21 sec)

それらをすべて壊してください。


demo]# mv *.ibd /tmp/

これで、information_schema.tablesテーブルを使って、必要なすべてのコマンドを作成できます。


# vi build_discard.sql
# cat build_discard.sql
SELECT CONCAT(" ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," DISCARD TABLESPACE; ") as CMD FROM information_schema.TABLES WHERE TABLE_SCHEMA='demo';

# vi build_import.sql
# cat build_import.sql
SELECT CONCAT(" ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," IMPORT TABLESPACE; ") as CMD FROM information_schema.TABLES WHERE TABLE_SCHEMA='demo';


# mysql -N < build_import.sql > import_tablespace.sql
# mysql -N < build_discard.sql | mysql demo

demo]# cp /tmp/*.ibd .
demo]# chown mysql:mysql *.ibd
# systemctl restart mysqld
# mysql demo < import_tablespace.sql
# mysql demo

mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO demotable1 (id) VALUES (NULL);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO demotable2 (id) VALUES (NULL);
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO demotable3 (id) VALUES (NULL);
^[[AQuery OK, 1 row affected (0.37 sec)

mysql> INSERT INTO demotable4 (id) VALUES (NULL);
Query OK, 1 row affected (0.12 sec)

そしてそれはうまくいった。

MySQL Binlogs ::回復するには

だから私は最近出てきたこのような状況の後、私はこれについての投稿をしていないことに気づいた。

シナリオは次のとおりです。バックアップは真夜中に行われ、データベースごとにMySQLダンプを使用しました。 それから翌日の午前10時にデータベースがクラッシュしました。 電話をかける前に一連のイベントが発生しましたが、MyISAMテーブルとIBDファイルがテーブルスペースにないデータベースのバージョンに到達しました。

そのため、オプション1、バックアップから復元すると深夜になり、何時間ものデータを失うことになります。 オプション2、私たちは何千ものibdファイルを再インポートしてすべてを保存します。 それから、オプション3、バックアップからの復元、そして最近の変更にbinlogを適用しました。

それをもっと面白くするために、彼らは私が言われたibdファイルの全てを持っていませんでした、そして私はいくらかの欠けているのを見ました。 それでそれがどのように可能であったかについてよくわからないが、選択肢2は無効な選択肢になった。 彼らは、もちろん、可能な限り少ないデータ損失を望んでいたので、私たちはオプション3を使いました。

これを安全に行うために、ポート3307でMySQLの別のインスタンスを起動しました。これにより、トラフィックがポート3306インスタンスのMyISAMデータへの読み取りアクセスを行っている間も、安全に作業できます。

すべてのバックアップダンプファイルを圧縮解除して3307インスタンスにインポートすると、binlogファイルに集中することができました。

最初は、この概念は実際よりもはるかに危険に思えます。 それは実際にはかなり単純明快です。

だから最初にあなたはあなたの後のデータを見つけなければなりません。 binlogファイルを見直すことで、どのファイルが関連しているのかをすぐに知ることができます。 私の場合、どういうわけか彼らはbinlogをリセットすることができたので117ファイルはその中に2つの日付範囲を持っていました。

最初にbinlogを確認するために、次のコマンドはデータを人間が読める形式で出力します。

mysqlbinlog --defaults-file=/root/.my.cnf --base64-output=DECODE-ROWS --verbose mysql-bin.000117 > review_mysql-bin.000117.sql

*注意...上記のコマンドを実行するときは注意してください。 binlogと同じ場所に直接ファイルをダンプしていることに注意してください。 ファイル名が有効であることを確認してください。 このmysql-bin.000117.sqlは、このmysql-bin.000117 .sqlとは異なります。 あなたは2番目のオプションと.sqlの前のスペースであなたのbinlogを失うでしょう。

適用できるようにデータを保存します。 私はいくつかのbinlogを持っていたので、私はファイルを作成し、とにかく時間範囲を再確認したいと思いました。


mysqlbinlog --defaults-file=/root/.my.cnf --start-datetime="2019-07-09 00:00:00" --stop-datetime="2019-07-10 00:00:00" mysql-bin.000117 > binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf mysql-bin.000118 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf mysql-bin.000119 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf --start-datetime="2019-07-10 00:00:00" --stop-datetime="2019-07-10 10:00:00" mysql-bin.000117 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf --stop-datetime="2019-07-10 10:00:00" mysql-bin.000120 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf --stop-datetime="2019-07-10 10:00:00" mysql-bin.000121 >> binlog_restore.sql

mysql --socket=/var/lib/mysql_restore/mysql.sock -e "source /var/lib/mysql/binlog_restore.sql"


今、私は与えられた時間範囲についてそれらのbinlogからのすべてのデータを適用しました。 クライアントはすべてのデータをダブルチェックし、すべて元に戻すことができてとても嬉しかったです。

この状況にはいくつかの異なる選択肢がありましたが、これはたまたまクライアントと一緒にトレーニングすることでした。

検証されたすべてが復元されたバージョンで大丈夫だったとすれば、両方のデータベースを停止し、データディレクトリを移動し（datadirのデフォルトをそのままにしておきたい）、ディレクトリを安全にしてMySQLを起動します。 復元されたインスタンスはポート3306で起動しました。