欧美一区二区三区老妇人-欧美做爰猛烈大尺度电-99久久夜色精品国产亚洲a-亚洲福利视频一区二区

MySQL誤刪庫恢復實戰(zhàn)

創(chuàng)建測試庫、表

創(chuàng)新互聯(lián)專注于和龍網(wǎng)站建設服務及定制，我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗。 熱誠為您提供和龍營銷型網(wǎng)站建設，和龍網(wǎng)站制作、和龍網(wǎng)頁設計、和龍網(wǎng)站官網(wǎng)定制、重慶小程序開發(fā)服務，打造和龍網(wǎng)絡公司原創(chuàng)品牌,更為您提供和龍網(wǎng)站排名全網(wǎng)營銷落地服務。

create database test;
use test;
create table  leo (id int,name varchar(10));

插入數(shù)據(jù)

insert into leo values (1,"liufeng");
insert into leo values (2,"zhangsan");
insert into leo values (3,"liufeng");
insert into leo values (4,"zhangsan");
MySQL> select * from leo;
+------+----------+
| id   | name     |
+------+----------+
|    1 | liufeng  |
|    2 | zhangsan |
|    3 | liufeng  |
|    4 | zhangsan |
+------+----------+
.......

全備數(shù)據(jù)庫

#!/bin/sh
bak_path=/home/mysql/backup
file_name=bak_$(date +%F)
if  [ ! -e  $bak_path  ];then
 mkdir  -p  $bak_path
fi
 mysqldump -uroot -proot -S /home/mysql/3306/data/mysql.sock -A --single-transaction --master-data=2  >  $bak_path/${file_name}.sql
md5sum $bak_path/${file_name}.sql > $bak_path/${file_name}.flag
find $BakPath  -name "*.sql.gz"  -mtime +7|xargs rm -f

確認備份狀態(tài)

[root@leo home]# cd mysql/backup/
[root@leo backup]# ll
total 792
-rw-r--r-- 1 root root     72 Jun 28 22:18 bak_2019-06-28.flag
-rw-r--r-- 1 root root 804496 Jun 28 22:18 bak_2019-06-28.sql
[root@leo backup]# more bak_2019-06-28.flag 
db297e95d491ae3b85ed2b5d2496e527  /home/mysql/backup/bak_2019-06-28.sql
[root@leo backup]#  cd ..
[root@leo mysql]# 
[root@leo mysql]# md5sum -c /home/mysql/backup/bak_2019-06-28.flag
/home/mysql/backup/bak_2019-06-28.sql: OK

插入數(shù)據(jù)

mysql> insert into leo values (5,"liufeng");
Query OK, 1 row affected (0.01 sec)

mysql> insert into leo values (6,"zhangsan");
Query OK, 1 row affected (0.01 sec)

mysql> select * from leo;
+------+----------+
| id   | name     |
+------+----------+
|    1 | liufeng  |
|    2 | zhangsan |
|    3 | liufeng  |
|    4 | zhangsan |
|    5 | liufeng  |
|    6 | zhangsan |
+------+----------+

刪除數(shù)據(jù)庫

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| leo                |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> drop database test;
Query OK, 0 rows affected (0.01 sec)
mysql>  drop database test;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

備份bin—log

[root@leo backup]# cd /home/mysql/3306/data/
[root@leo data]# ll
total 123740
-rw-r-----. 1 mysql mysql       56 Jun 18 00:59 auto.cnf
-rw-r-----  1 mysql mysql      456 Jun 28 17:41 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jun 29 02:45 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jun 29 02:45 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jun 18 00:59 ib_logfile1
-rw-r-----  1 mysql mysql 12582912 Jun 29 02:42 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Jun 25 23:09 mysql
-rw-r-----  1 mysql mysql      177 Jun 25 22:54 mysql-bin.000003
-rw-r-----  1 mysql mysql   815245 Jun 25 23:17 mysql-bin.000004
-rw-r-----  1 mysql mysql      642 Jun 28 17:41 mysql-bin.000005
-rw-r-----  1 mysql mysql     5909 Jun 29 02:45 mysql-bin.000006
-rw-r-----  1 mysql mysql       76 Jun 28 19:36 mysql-bin.index
srwxrwxrwx  1 mysql mysql        0 Jun 28 19:36 mysql.sock
-rw-------  1 mysql mysql        5 Jun 28 19:36 mysql.sock.lock
drwxr-x---. 2 mysql mysql     4096 Jun 18 00:59 performance_schema
-rw-r-----  1 mysql mysql        5 Jun 28 19:36 resourcepool-0559.pid
drwxr-x---. 2 mysql mysql    12288 Jun 18 00:59 sys
[root@leo data]# cp -a mysql-bin.* /home/mysql/backup/
[root@leo data]# ll /home/mysql/backup/
total 1612
-rw-r--r-- 1 root  root      72 Jun 29 02:42 bak_2019-06-29.flag
-rw-r--r-- 1 root  root  804515 Jun 29 02:42 bak_2019-06-29.sql
-rw-r----- 1 mysql mysql    177 Jun 25 22:54 mysql-bin.000003
-rw-r----- 1 mysql mysql 815245 Jun 25 23:17 mysql-bin.000004
-rw-r----- 1 mysql mysql    642 Jun 28 17:41 mysql-bin.000005
-rw-r----- 1 mysql mysql   5909 Jun 29 02:45 mysql-bin.000006
-rw-r----- 1 mysql mysql     76 Jun 28 19:36 mysql-bin.index

恢復步驟
1、停止數(shù)據(jù)庫對外訪問，防止數(shù)據(jù)庫因update導致數(shù)據(jù)破壞
2、由于備份語句中加入--master-data=2，可以記錄全備后的binlog對應恢復點

[root@leo data]# cd /home/mysql/backup/
[root@leo backup]# ll
total 1612
-rw-r--r-- 1 root  root      72 Jun 29 02:42 bak_2019-06-29.flag
-rw-r--r-- 1 root  root  804515 Jun 29 02:42 bak_2019-06-29.sql
-rw-r----- 1 mysql mysql    177 Jun 25 22:54 mysql-bin.000003
-rw-r----- 1 mysql mysql 815245 Jun 25 23:17 mysql-bin.000004
-rw-r----- 1 mysql mysql    642 Jun 28 17:41 mysql-bin.000005
-rw-r----- 1 mysql mysql   5909 Jun 29 02:45 mysql-bin.000006
-rw-r----- 1 mysql mysql     76 Jun 28 19:36 mysql-bin.index
[root@leo backup]# sed -n '22p' bak_2019-06-29.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=5181;

3、將binlog轉(zhuǎn)換SQL

[root@leo backup]# mysqlbinlog -d test mysql-bin.000006  --start-position=5181 -r test.sql
[root@leo backup]# cat test.sql

4、實際環(huán)境中后續(xù)還有 mysql-bin.000007 .... 需要陸續(xù)轉(zhuǎn)換為SQL
5、刪除test.sql中drop語句

[root@leo backup]# grep  -w drop test.sql 
drop database test
[root@leo backup]# sed -i '/drop database test/d' test.sql 
[root@leo backup]# grep  -w drop test.sql

6、全備恢復數(shù)據(jù)庫

[root@leo backup]# mysql -u root -p -S /home/mysql/3306/data/mysql.sock < bak_2019-06-29.sql 
Enter password: 
[root@leo backup]# mysql -u root -p -S /home/mysql/3306/data/mysql.sock -e "select * from test.leo";
Enter password: 
+------+----------+
| id   | name     |
+------+----------+
|    1 | liufeng  |
|    2 | zhangsan |
|    3 | liufeng  |
|    4 | zhangsan |
+------+----------+

7、恢復binlog

[root@leo backup]# mysql -u root -p -S /home/mysql/3306/data/mysql.sock < test.sql
Enter password: 
[root@leo backup]# mysql -u root -p -S /home/mysql/3306/data/mysql.sock -e "select * from test.leo";
Enter password: 
+------+----------+
| id   | name     |
+------+----------+
|    1 | liufeng  |
|    2 | zhangsan |
|    3 | liufeng  |
|    4 | zhangsan |
|    5 | liufeng  |
|    6 | zhangsan |
+------+----------+

8、校驗數(shù)據(jù)，恢復數(shù)據(jù)庫對外訪問

網(wǎng)站欄目：MySQL誤刪庫恢復實戰(zhàn)
轉(zhuǎn)載注明：http://www.chinadenli.net/article42/ieodec.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供外貿(mào)建站、企業(yè)建站、面包屑導航、關鍵詞優(yōu)化、電子商務、網(wǎng)站制作

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

h5響應式網(wǎng)站建設