2019年7月13日星期六

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)


好的,現在讓我們打破它..


# 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


OK只使用了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 |
+----+---------------------+


好的工作。
現在,如果您只有一張桌子,這一切都很簡單。 但是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轉儲。 然後在第二天上午十點數據庫崩潰。 在我被調用之前發生了一系列事件,但他們把它帶到了MyISAM表的數據庫版本和表空間中缺少的IBD文件。

所以選項1,從備份恢復會讓我們到午夜,我們會丟失數小時的數據。 選項2,我們重新導入1000的ibd文件並保留所有內容。 然後我們有選項3,從備份恢復,然後應用binlogs進行最近的更改。

為了使它更有趣,他們沒有我被告知的所有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不同。 您將使用第二個選項和.sql之前的空格來丟失binlog。

現在保存數據,以便可以應用它。 由於我有幾個binlogs,我創建了一個文件,我想要仔細檢查時間範圍。


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中的所有數據應用於給定的時間範圍。 客戶端仔細檢查了所有數據,並非常高興能夠全部恢復。

對於這種情況存在幾種不同的選擇,這恰好與客戶一起鍛煉。

一旦驗證的all在恢復的版本上沒問題,它就是一個簡單的停止兩個數據庫,移動數據目錄(想要保持datadir默認完整),chown目錄只是為了安全並啟動MySQL。 現在,已恢復的實例已在端口3306上啟動。