修復步驟還原
# ls
auto.cnf htest hua ib_buffer_pool ibdata1 ibtmp1 mysql performance_schema sys test
# rm ibdata1
# ls
auto.cnf htest hua ib_buffer_pool ibtmp1 mysql performance_schema sys test
2. 數據庫正常登錄。
mysql> select count(*) from htest.tables;
+----------+
| count(*) |
+----------+
| 287 |
+----------+
1 row in set (0.00 sec)
3. 查看mysqld進程pid。
# ps -ef|grep 23301
mysql 11849 10193 0 14:59 pts/0 00:00:05 /usr/local/mysql/bin/mysqld --defaults-
file=/data/mysql/db_order/conf/order.cnf --
basedir=/usr/local/mysql --datadir=/data/mysql/db_order/data
--plugin-dir=/usr/local/mysql/lib/plugin --log-
error=/data/mysql/db_order/elog/mysql.err --open-files-
limit=650 --pid-file=/data/mysql/db_order/mysql.pid --
socket=/data/mysql/db_order/mysql.sock --port=23301
4. 使用如下命令,查看結果。
$ ll /proc/11849/fd |egrep ibdata
lrwx------ 1 mysql mysql 64 Mar 23 15:33 11 ->
/data/mysql/db_order/data/ibdata1 (deleted)
-- 刪除一個文件時,并不是真正刪除,而是打一個標記,同樣在我們mysql數據庫中,delete一條記錄,實際的刪除操作也沒有發生。上面顯示的結果中,其中11就是我們需要恢復的文件。
5. 在恢復文件前,需要先執行flush tables with read lock,確保數據庫沒有寫入操作,以便我們完成恢復。
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
6. 把11號文件復制到原文件夾,注意文件權限,如果不是mysql:mysql,需要修改權限。
[mysql@mysqltest1 data]$ cd /proc/11849/fd
[mysql@mysqltest1 fd]$ cp 11 /data/mysql/db_order/data/ibdata1
[mysql@mysqltest1 fd]$ cd /data/mysql/db_order/data
[mysql@mysqltest1 data]$ ll
total 24612
-rw-r----- 1 mysql mysql 56 Jan 21 2019 auto.cnf
drwxr-x--- 2 mysql mysql 56 Mar 23 15:27 htest
drwxr-x--- 2 mysql mysql 58 Feb 24 00:56 hua
-rw-r--r-- 1 mysql mysql 299 Mar 23 14:57 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Mar 23 16:33 ibdata1
-rw-r--r-- 1 mysql mysql 12582912 Mar 23 15:28 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Jan 21 2019 mysql
drwxr-x--- 2 mysql mysql 8192 Jan 21 2019 performance_schema
drwxr-x--- 2 mysql mysql 8192 Jan 21 2019 sys
drwxr-x--- 2 mysql mysql 20 Jan 22 2019 test
7. 重啟MYSQL,修復完成。
[mysql@mysqltest1 bin]$ sh shutdown.sh
Enter password:
[mysql@mysqltest1 bin]$ sh startup.sh
[mysql@mysqltest1 bin]$ sh login.sh
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.7.25-log Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type help; or h for help. Type c to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| htest |
| hua |
| mysql |
| performance_schema |
| sys |
+--------------------+
9 rows in set (0.00 sec)
數據庫寫入操作確認
mysql> set global innodb_max_dirty_pages_pct=0;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------+
| mysql-bin.000024 | 11969 | | | 73b5b55c-1d8d-11e9-9aae-00155d607300:1-63 |
+------------------+----------+--------------+------------------+----------------------------------+
mysql> show engine innodb statusG;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2020-03-23 16:26:54 0x7f35e406c700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 44 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 8 srv_active, 0 srv_shutdown, 5216 srv_idle
srv_master_thread log flush and writes: 5224
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 11
OS WAIT ARRAY INFO: signal count 11
RW-shared spins 0, rounds 16, OS waits 8
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 16.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 42540
Purge done for trxs n:o < 42540 undo n:o < 0 state: running but idle
History list length 4 #確保后臺線程purge把undo log全部清刷掉
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421344771516240, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/os:, sync i/os:
Pending flushes (fsync) log: 0; buffer pool: 0
387 OS file reads, 133 OS file writes, 47 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges #確保合并插入緩存等于1
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 7172011
Log flushed up to 7172011
Pages flushed up to 7172011 #確保這里三個值保持一致,并且不再變化
Last checkpoint at 7172002
0 pending log flushes, 0 pending chkp writes
32 log i/os done, 0.00 log i/os/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 179916
Buffer pool size 8192
Free buffers 7798
Database pages 393
Old database pages 0
Modified db pages 0 #確保臟頁數量為0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 350, created 43, written 88
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 393, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=11849, Main thread ID=139869459859200, state: sleeping
Number of rows inserted 311, updated 2, deleted 0, read 651
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------#確保插入,更新,刪除為0
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.18 sec)
文章版權歸作者所有,未經允許請勿轉載,若此文章存在違規行為,您可以聯系管理員刪除。
轉載請注明本文地址:http://specialneedsforspecialkids.com/yun/129704.html
摘要:我把問題整理成文章,只是方便以后進行追溯我自己寫了一個腳本,每次項目完了就會執行一次備份。所以恢復工具基本告別。最后查到了的,確實有文件,但是無論恢復到什么版本,目錄確實還原了,但是都是怎么辦啊現在直接掛掉了 我把問題整理成文章,只是方便以后進行追溯我自己寫了一個db-backup腳本,每次項目完了就會執行一次備份。 后面我直接干掉了mysql目錄,想恢復13:28分的記錄備份,結果一...
摘要:我們先來看下對數據庫的完全備份。總結如果允許數據丟失,可以直接使用直接恢復,否則可以通過二進制文件進行恢復。現在看來其實也非常簡單了,找到開發服務器上的二進制日志文件就能恢復了,最后他又一個一個表開始建的,誰叫我當初又蠢又笨 開啟binlog show binary logs 檢查bin log是否開啟,Mac OS系統安裝完Mysql沒有my.cnf配置文件,需要自己新建,我的Mys...
閱讀 1346·2023-01-11 13:20
閱讀 1684·2023-01-11 13:20
閱讀 1132·2023-01-11 13:20
閱讀 1858·2023-01-11 13:20
閱讀 4100·2023-01-11 13:20
閱讀 2704·2023-01-11 13:20
閱讀 1385·2023-01-11 13:20
閱讀 3597·2023-01-11 13:20