Petua pemulihan data MySQL menggunakan fail ibd?

Pengenalan

Sektor buruk cakera, bekalan elektrik terputus dan kemalangan lain bukanlah perkara biasa, tetapi menghadapinya sudah cukup untuk membuatkan anda "mendebarkan"! Apakah yang perlu saya lakukan jika data hilang akibat kerosakan pangkalan data dan Binlog tidak lagi tersedia? Untuk memulihkan data tanpa kehilangan dalam masa yang singkat untuk memastikan kestabilan perniagaan, selain menggunakan binlog, kami juga mengamalkan kemahiran pemulihan baharu!

Ingat apa yang kami tulis sebelum ini, "Hanya satu helah untuk membuat R&D di luar kawalan jatuh cinta dengan anda"? Seperti yang dinyatakan sebelum ini, dua kaedah pemulihan pangkalan data yang paling kami gunakan setiap hari ialah:

Kedua-dua kaedah di atas boleh mencapai pengembalian masa nyata, tetapi adakah anda fikir mempunyai dua kemahiran ini sudah memadai?

Tidak….!

Dalam seni bina dalam talian yang rumit ini, sebenarnya terdapat banyak sebab yang tidak diketahui yang tidak dapat kita jangkakan. Contohnya:

Disk yang telah rosak akibat kerja keras akan membangunkan sektor buruk, menyebabkan kerosakan pangkalan data. Dan ia berlaku merosakkan fail ibdata dan fail binlog. Jadi jika anda masih memikirkan penyelesaian sandaran berjadual + pemulihan binlog, adakah anda boleh menggunakan sandaran titik tetap untuk memulihkan? Selepas pertimbangan yang teliti, sebagai kakitangan operasi dan penyelenggaraan, kami tidak akan sekali-kali melaksanakan rollback lossy sebagai pilihan terakhir, kerana ia akan memberi impak yang besar kepada perniagaan, tetapi apa lagi yang boleh kami lakukan? Seterusnya kami akan melancarkan langkah besar! ! !

Mula-mula semak persekitaran pangkalan data untuk melihat sama ada ruang jadual bebas didayakan Jika ia telah didayakan, tahniah, terdapat peluang besar untuk semua data boleh dipulihkan. Kami boleh bergantung pada fail frm dan ibd dalam setiap direktori pangkalan data untuk mencapai pemulihan data Secara umumnya, jika InnoDB digunakan tetapi ruang jadual bebas tidak didayakan, semua maklumat jadual pangkalan data dan metadata ini akan kekal untuk masa yang lama jika dijalankan, fail ibdata akan menjadi lebih besar dan lebih besar, dan prestasi pangkalan data akan berkurangan. InnoDB menyediakan parameter untuk membolehkan ruang jadual bebas, yang membolehkan data disimpan secara bebas Dengan cara ini, fail ibdata hanya digunakan untuk menyimpan beberapa maklumat indeks berkaitan enjin, dan data sebenar ditulis kepada fail frm dan ibd bebas.

Baiklah, dengan fail frm dan ibd, kita boleh mula mencuba pemulihan data Prosesnya lebih mendebarkan dan menarik daripada pemulihan binlog! Mula-mula, mari kita lihat arahan tentang ibd dan frm:

Fail

.frm: Menyimpan metadata setiap jadual, termasuk definisi struktur jadual, dsb. Fail ini tiada kaitan dengan enjin pangkalan data.

Fail

.ibd: Fail yang dijana oleh enjin InnoDB apabila ruang jadual bebas didayakan (innodb_file_per_table = 1 dalam my.ini) untuk menyimpan data dan indeks jadual.

Kita semua tahu bahawa untuk pangkalan data InnoDB, jika anda tidak menyalin keseluruhan direktori data, tetapi hanya menyalin direktori pangkalan data yang ditentukan ke contoh baharu, pangkalan data tidak akan dikenali. Jadi bagaimana untuk memulihkan pangkalan data berdasarkan dua fail ini?

Idea pemulihan:

Oleh kerana beberapa maklumat indeks tentang enjin disimpan dalam fail ibdata, fail ibdata rosak, menyebabkan indeks nama jadual hilang dan tidak dapat dihidupkan. Kemudian kita boleh menamakan semula keseluruhan direktori data lama dahulu dan menyandarkannya, kemudian memulakan semula pangkalan data untuk menjana fail ibdata baharu, kemudian mencipta semula pangkalan data asal dan jadual yang sepadan, dan akhirnya menukar nombor ID ruang jadual sandaran kepada nombor ID ruang jadual baharu (fail ibdata mengandungi ID indeks ruang jadual unik untuk setiap jadual, yang ditambah dengan bilangan jadual baharu yang dibuat), supaya pangkalan data asal boleh dipulihkan.
Contohnya:
Nama perpustakaan: test_restore
Struktur jadual: db_struc.sql
Fail jadual: G_RESTORE.ibd, G_RESTORE.frm

1. Cipta perpustakaan baharu dan import struktur jadual

#mysql -uroot –p**** -e “buat pangkalan data test_restore”

#mysql -uroot –p**** test_restore 2 Lihat dan ubah suai id jadual dalam pustaka test_restore dalam contoh baharu.

#vim -b /data/database/mysql/test_restore/G_RESTORE.ibd

Buka terus sebagai aksara bercelaru dan tukarkannya kepada perenambelasan untuk tontonan. Laksanakan :%!xxd dalam Vi untuk menukar kepada perenambelasan. Hasilnya ialah:

Seperti yang ditunjukkan dalam gambar. Id jadual G_RESTORE dalam pangkalan data mysql ialah 00fe.

Ubah suai fail G_RESTORE.ibd yang disandarkan. Operasi adalah sama seperti di atas, tetapi ambil perhatian bahawa anda perlu membuat sandaran terlebih dahulu.

#cp G_RESTORE.ibd{,_back}

#vim -v G_RESTORE.ibd

Tukar 011b kepada 00fe. Notis. Selepas pengubahsuaian selesai, anda perlu melaksanakannya dalam vim dahulu: %!xxd -r

Wq sekali lagi untuk menyimpan dan keluar dari fail. Jika tidak, hasil yang disimpan ialah paparan heksadesimal.

Simpan keputusan seperti berikut:

Ganti fail G_RESTORE.ibd yang diubah suai dengan fail G_RESTORE.ibd dalam pangkalan data baharu.

Penjelasan tentang id jadual ibdata:

参考官方文档解释，每个表空间分配了4个字节存储了表空间id信息,最后偏移量地址为38。还有一组预留的表空间id，同样是4个字节，最后偏移量地址为42。

3. 验证并还原mysql数据

关闭mysql。修改my.conf。
innodb_force_recovery=6 innodb_purge_threads=0

启动数据库。如果不修改。数据库会认为G_RESTORE已被损坏。

Select 一下，即可查看到还原结果，但此时插入数据会报错，应尽快将数据dump出来 ,导回原来的实例中。

导出数据，再导入数据，恢复完毕！

#mysqldump -uroot –p****** test_restore > test_restore.sql

#mysql -uroot –p****** test_restore 
<p>说明：变更了新的space id后的.ibd表文件，启动数据库后只能认出数据，但不能写入，这是因为原ibdata文件不仅保存了space id索引，还同时保存了一些其它的元数据。为了使元数据补全，所以采取导出、再导入的操作。</p>
<p>以上举例为单个库表的恢复过程，看到这里大家一定会产生另一个疑问吧？线上的场景不可能是只有一个表的，数据库表很多的情况下，这样一个个表的修改，速度无疑是太慢了。那么存在大量表的情况下如何恢复呢？思路是，取得备份的ibd文件的id值，按id值顺序来建表，中间跨度随便建表语句来凑够数（每个表空间索引id由创建新表的数量依次递增）。实现方式如下：</p>
<p><span style="color: #339966;"><strong>1. 获取备份数据库ibd文件的space id号，并排序。</strong></span></p>
<pre class="brush:php;toolbar:false">for ibd in `find test_restore/ -name “*.ibd”` ; do  echo -e “${ibd///// }   /c” ;hexdump -C ${ibd} |head -n 3 |tail -n 1|awk ‘{print  strtonum(“0x”$6$7)}’ ;done | sort -n  -k 3 | column -t > /tmp/

生成的ibd.txt文件，格式如下：(库名–表名–SpaceId)

2. 新建表，查看当前表空间id（假设space id为10）

#mysql -uroot –p****** -e”create table test.tt(a bool)”

#hexdump -C mysql/test/tt.ibd |head -n 3 |tail -n 1|awk ‘{print  strtonum(“0x”$6$7)}’

3. 先创建所有库，准备所有表结构，写脚本，依据space id号自动创建新表

准备好数据库表结构，可以从备份文件里取出来（我们备份方式是把结构和数据分开备份的），或者从其他有相同表结构的服务器上备份再拷贝过来。

参考备份语句：

mysqldump -uroot –p****** -d ${db} –T /data/backup/${db}/

创建原有的数据库：

mysql -uroot –p****** -e “create database ${db}”

恢复表id创建表脚本：

#!/bin/bash
#因为前面假设为10，所以从11开始创建
oid=11

#打开前面生成的ibd.txt文件，按行读取”库名–表名–SpaceId”
cat /tmp/ibd.txt | while read db tb id ;do

#假如我们需要恢复catetory表，他的id为415，基于id是创表自增的原则，即415-11=404，
#我们还需要循环创建404个表后，才真正导入catetory表结构。
for ((oid;oid<id do mysql table test.t bool echo ok done let oid="oid+1">
<p><span style="color: #339966;"><strong>4. 检查表空间id 和备份的是否一致</strong></span></p>
<pre class="brush:php;toolbar:false">for ibd in `find test_restore/ -name “*.ibd”` ; do  echo -e “${ibd///// }   /c” ;hexdump -C ${ibd} |head -n 3 |tail -n 1|awk ‘{print  strtonum(“0x”$6$7)}’ ;done | sort -n  -k 3 | column -t > /tmp/ibd2.txt

确认一致后，拷贝备份的.ibd文件到新数据库实例目录下，修改my.cnf

innodb_force_recovery=6

innodb_purge_threads=0

启动数据库。后续步骤如同单表恢复，直接导出恢复到原来实例中即可。

当然，这种方式是在数据库出现极端情况下，不得不采取的一种方式，线上最重要的还是做好主从同步和定时备份，从而规避此类风险。

关于InnoDB引擎独立表空间说明：

使用过MySQL的同学，刚开始接触最多的莫过于MyISAM表引擎了，这种引擎的数据库会分别创建三个文件：表结构、表索引、表数据空间。我们可以将某个数据库目录直接迁移到其他数据库也可以正常工作。然而当你使用InnoDB的时候，一切都变了。

InnoDB默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中：ibdata1，这样就感觉不爽，增删数据库的时候，ibdata1文件不会自动收缩，单个数据库的备份也将成为问题。通常只能将数据使用mysqldump导出，然后再导入解决这个问题。

但是可以通过修改MySQL配置文件[mysqld]部分中innodb_file_per_table的参数来开启独立表空间模式，每个数据库的每个表都会生成一个数据空间。

优点：

1．每个表都有自已独立的表空间。

2．每个表的数据和索引都会存在自已的表空间中。

3．可以实现单表在不同的数据库中移动。

4．空间可以回收（除drop table操作处，表空不能自已回收）

a) Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。

b) 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。

c) 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

Kelemahan:

Meja tunggal terlalu besar, seperti lebih daripada 100 G.

Kesimpulan:

Ruang meja kongsi mempunyai sedikit kelebihan dalam operasi Sisipan. Lain-lain tidak berprestasi sebaik ruang meja bebas. Apabila mendayakan ruang jadual bebas, sila laraskan dengan sewajarnya: innodb_open_files.

Kaedah konfigurasi:
1.innodb_file_per_table tetapan Cara mendayakannya:

Tetapkan di bawah [mysqld] dalam my.cnf

innodb_file_per_table=1
2. Semak sama ada ia didayakan:
mysql> tunjukkan pembolehubah seperti ‘%per_table%’;

3 Tutup ruang meja eksklusif
innodb_file_per_table=0 mematikan ruang jadual bebas

mysql> tunjukkan pembolehubah seperti ‘%per_table%’;

Atas ialah kandungan terperinci Petua pemulihan data MySQL menggunakan fail ibd?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!