頻道欄目
首頁 > 系統 > Linux > 正文

MySQL的備份還原(mysqldump)-Masuri-51CTO博客

2019-05-07 18:17:34           
收藏   我要投稿

MySQL的備份還原(mysqldump)

MySQL的還原前提是要建立在,有完全備份和二進制日志開啟的前提下,并且二進制日志文件和完全備份存放在與數據庫文件不同的磁盤上,否則當磁盤發生損壞數據將無法進行恢復。

開啟二進制日志

開啟二進制日志需要將MySQL中的sql_log_bin和log_bin這兩個選項系統中默認開啟sql_log_bin選項所以此處無需修改

MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.01 sec)

2.開啟log_bin注意:不要和數據庫存放在統一磁盤內,不要和數據庫存放在統一磁盤內,不要和數據庫存放在統一磁盤內重要的事情說三遍。

[[email protected] ~]# mkdir /data/bin
[[email protected] ~]# chown -R mysql.mysql /data/bin  #將目錄的屬主和屬組都改為mysql

二進制日志目錄創建完畢后,修改配置文件,添加log-bin選項,并指定路徑,此處要注意mysql-bin是二進制日志的抬頭。

[[email protected] ~]# vim /etc/mysql/my.cnf
log-bin=/data/bin/mysql-bin            #mysql-bin為二進制日志的文件名的抬頭

以上配置完成后重啟mysql服務

[[email protected] ~]# service mysqld restart
Restarting mysqld (via systemctl):                         [  OK  ]

此時二進制日志目錄下已經多出了兩個二進制日志文件

[[email protected] ~]# ll /data/bin/
total 12
-rw-rw---- 1 mysql mysql 351 May  6 16:51 mysql-bin.000001
-rw-rw---- 1 mysql mysql  54 May  6 16:51 mysql-bin.index

場景一、磁盤損壞恢復

生產中會發生存放數據的磁盤突然發生損壞而造成數據丟失的情況,此時就需要結合之前所作的完全備份以及二進制日志進行將數據完全恢復

場景模擬

一、對數據庫進行備份

[[email protected] ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

[[email protected] ~]# mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz

二、備份完畢后數據庫發生小部分變化

MariaDB [(none)]> INSERT hellodb.students(stuid,name,gender,age) VALUE(26,'linchong','M',30) ;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> INSERT hellodb.students(stuid,name,gender,age) VALUE(27,'Lujunyi','M',30);
Query OK, 1 row affected (0.00 sec)

三、數據庫發生破壞,數據丟失

[[email protected] ~]# rm -rf /data/mysql/*

數據恢復

一、將mysqld服務停止

[[email protected] ~]# service mysqld stop
Stopping mysqld (via systemctl):                           [  OK  ]

二、查看下二進制日志。

由于每次啟動服務都會重新生成一個新的二進制日志,所以先查看下二進制日志的編號以免等下在使用二進制日志還原數據時還原了不必要的數據

[[email protected]alhost ~]# ll /data/bin/
total 504
-rw-rw---- 1 mysql mysql    351 May  6 16:51 mysql-bin.000001
-rw-rw---- 1 mysql mysql   9388 May  6 17:01 mysql-bin.000002
-rw-rw---- 1 mysql mysql     81 May  6 17:03 mysql-bin.index

三、重啟MySQL服務,初始化數據庫

[[email protected] ~]# service mysqld start
Starting mysqld (via systemctl):                           [  OK  ]
[[email protected] ~]# ll /data/mysql/
total 122924
-rw-rw---- 1 mysql mysql    16384 May  6 17:01 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 May  6 17:01 aria_log_control
drwx------ 2 mysql mysql      272 May  6 16:54 hellodb
-rw-rw---- 1 mysql mysql     1298 May  6 17:01 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 May  6 17:03 ibdata1
-rw-rw---- 1 mysql mysql 50331648 May  6 17:03 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Apr 29 12:49 ib_logfile1
-rw-rw---- 1 mysql mysql 12582912 May  6 17:03 ibtmp1
-rw-rw---- 1 mysql mysql        6 May  6 17:03 localhost.localdomain.pid
-rw-rw---- 1 mysql mysql        0 Apr 29 12:57 multi-master.info
drwx------ 2 mysql root      4096 Apr 29 12:49 mysql
-rw-rw---- 1 mysql mysql      351 Apr 29 14:06 mysql-bin.000001
-rw-rw---- 1 mysql mysql      351 May  6 16:50 mysql-bin.000002
-rw-rw---- 1 mysql mysql       38 May  6 16:26 mysql-bin.index
-rw-rw---- 1 mysql mysql        0 May  6 16:50 mysql-bin.state
srwxrwxrwx 1 mysql mysql        0 May  6 17:03 mysql.sock
drwx------ 2 mysql mysql       20 Apr 29 12:49 performance_schema
drwx------ 2 mysql root         6 Apr 29 12:49 test

四、將完全備份解壓

[[email protected] ~]# unxz /data/all.sql.xz 

五、由于完全備份后數據又發生過改變所以需要利用二進制日志進行還原在利用二進制還原前,先查看下完全備份時二進制日志所在的位置。

[[email protected] ~]# vim /data/all.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=8946;

六、將完全備份后產生二進制日志的數據導出

[[email protected] ~]# mysqlbinlog --start-position=8946 /data/bin/mysql-bin.000002 > /data/inc.log

七、進入MySQL,停止二進制日志記錄

由于接下來的操作是恢復數據,所以此處不需要讓二進制日志記錄數據。

MariaDB [(none)]> SET sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)

八、導入之前做的完全備份

MariaDB [(none)]> source /root/all.sql

九、導入完全備份后產生的數據

MariaDB [(none)]> source /data/inc.log 

十、驗證

查看數據庫,以及完全備份后增加的內容是否存在

[[email protected] ~]# mysql -e 'SHOW DATABASES;SELECT * FROM hellodb.students where stuid>25;'
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
+-------+----------+-----+--------+---------+-----------+
| StuID | Name     | Age | Gender | ClassID | TeacherID |
+-------+----------+-----+--------+---------+-----------+
|    26 | linchong |  30 | M      |    NULL |      NULL |
|    27 | Lujunyi  |  30 | M      |    NULL |      NULL |
+-------+----------+-----+--------+---------+-----------+

場景二、誤刪除的恢復

生產環境中通常會出現誤刪除的可能性,此時就需要用到完全備份,和部分修改后的二進制日志來還原數據

場景模擬

一、對數據庫進行完全備份

[[email protected] ~]# mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz

一段時間后數據庫發生了誤刪除操作,然后又進行了部分其他的曾的操作

MariaDB [(none)]> DROP TABLE hellodb.students;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> INSERT hellodb.teachers VALUE (5,'Jiang Jieshi',50,'M');
Query OK, 1 row affected (0.00 sec)

此時發現了數據庫hellodb.students表被刪除

恢復數據庫

一、停止MySQL服務

[[email protected] ~]# service mysqld stop
Stopping mysqld (via systemctl):                           [  OK  ]

二、將數據庫文件內的文件清空

[[email protected] ~]# rm -rf /data/mysql/*

三、將完全備份解壓

[[email protected] ~]# unxz /data/all.sql.xz 

四、查看完全備份內,二進制日志的記錄點

[[email protected] ~]# vim /data/all.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=494073;

五、查看二進制日志,并從二級制日志中導出數據

[[email protected] ~]# ll /data/bin/
total 508
-rw-rw---- 1 mysql mysql    351 May  6 16:51 mysql-bin.000001
-rw-rw---- 1 mysql mysql   9388 May  6 17:01 mysql-bin.000002
-rw-rw---- 1 mysql mysql 494449 May  6 17:52 mysql-bin.000003
-rw-rw---- 1 mysql mysql     81 May  6 17:03 mysql-bin.index
-rw-rw---- 1 mysql mysql      8 May  6 17:52 mysql-bin.state
[[email protected] ~]# mysqlbinlog --start-position=494073 /data/bin/mysql-bin.000003 > /data/inc.sql

六、從導出的數據中找到那條誤刪除的數據將其刪除或注釋

[[email protected] ~]# vim /data/inc.sql
#DROP TABLE `hellodb`.`students` /* generated by server */

七、重啟MySQL服務

[[email protected] mysql]# service mysqld start
Starting mysqld (via systemctl):                           [  OK  ]

八、將二進制日志關閉

MariaDB [(none)]> SET sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)

九、導入完全備份

MariaDB [(none)]> SOURCE /data/all.sql

MariaDB [test]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
#此時被刪除的students表已經找回
MariaDB [test]> SHOW TABLES FROM hellodb;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)
#但是誤操作后新加的記錄還沒有找回
MariaDB [test]> SELECT * FROM hellodb.teachers ;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

十、導入二進制日志所生成的修改后的文件

MariaDB [test]> SOURCE /data/inc.sql

#導入后再次查看students表在
MariaDB [test]> SHOW TABLES FROM hellodb;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)
#新添加的記錄也有了
MariaDB [test]> SELECT * FROM hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Jiang Jieshi  |  50 | M      |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
相關TAG標簽
上一篇:linux netstat 總結-奮進的K-51CTO博客
下一篇:linux中數據庫的管理-虛擬機中centos的安裝-51CTO博客
相關文章
圖文推薦
文章
推薦
熱門新聞

關于我們 | 聯系我們 | 廣告服務 | 投資合作 | 版權申明 | 在線幫助 | 網站地圖 | 作品發布 | Vip技術培訓 | 舉報中心

版權所有: 紅黑聯盟--致力于做實用的IT技術學習網站

重庆快乐十分开奖记录