MySQL的備份還原(mysqldump)

MySQL的還原前提是要建立在，有完全備份和二進(jìn)制日志開(kāi)啟的前提下，并且二進(jìn)制日志文件和完全備份存放在與數(shù)據(jù)庫(kù)文件不同的磁盤(pán)上，否則當(dāng)磁盤(pán)發(fā)生損壞數(shù)據(jù)將無(wú)法進(jìn)行恢復(fù)。

開(kāi)啟二進(jìn)制日志

開(kāi)啟二進(jìn)制日志需要將MySQL中的sql_log_bin和log_bin這兩個(gè)選項(xiàng)系統(tǒng)中默認(rèn)開(kāi)啟sql_log_bin選項(xiàng)所以此處無(wú)需修改

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

2.開(kāi)啟log_bin注意：不要和數(shù)據(jù)庫(kù)存放在統(tǒng)一磁盤(pán)內(nèi)，不要和數(shù)據(jù)庫(kù)存放在統(tǒng)一磁盤(pán)內(nèi)，不要和數(shù)據(jù)庫(kù)存放在統(tǒng)一磁盤(pán)內(nèi)重要的事情說(shuō)三遍。

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

二進(jìn)制日志目錄創(chuàng)建完畢后，修改配置文件，添加log-bin選項(xiàng)，并指定路徑，此處要注意mysql-bin是二進(jìn)制日志的抬頭。

[root@localhost ~]# vim /etc/mysql/my.cnf log-bin=/data/bin/mysql-bin            #mysql-bin為二進(jìn)制日志的文件名的抬頭

以上配置完成后重啟mysql服務(wù)

[root@localhost ~]# service mysqld restart Restarting mysqld (via systemctl):                         [  OK  ]

此時(shí)二進(jìn)制日志目錄下已經(jīng)多出了兩個(gè)二進(jìn)制日志文件

[root@localhost ~]# 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

場(chǎng)景一、磁盤(pán)損壞恢復(fù)

生產(chǎn)中會(huì)發(fā)生存放數(shù)據(jù)的磁盤(pán)突然發(fā)生損壞而造成數(shù)據(jù)丟失的情況，此時(shí)就需要結(jié)合之前所作的完全備份以及二進(jìn)制日志進(jìn)行將數(shù)據(jù)完全恢復(fù)

場(chǎng)景模擬

一、對(duì)數(shù)據(jù)庫(kù)進(jìn)行備份

[root@localhost ~]# mysql -e "SHOW DATABASES;" +--------------------+ | Database           | +--------------------+ | hellodb            | | information_schema | | mysql              | | performance_schema | | test               | +--------------------+  [root@localhost ~]# mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz

二、備份完畢后數(shù)據(jù)庫(kù)發(fā)生小部分變化

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)

三、數(shù)據(jù)庫(kù)發(fā)生破壞，數(shù)據(jù)丟失

[root@localhost ~]# rm -rf /data/mysql/*

數(shù)據(jù)恢復(fù)

一、將mysqld服務(wù)停止

[root@localhost ~]# service mysqld stop Stopping mysqld (via systemctl):                           [  OK  ]

二、查看下二進(jìn)制日志。

由于每次啟動(dòng)服務(wù)都會(huì)重新生成一個(gè)新的二進(jìn)制日志，所以先查看下二進(jìn)制日志的編號(hào)以免等下在使用二進(jìn)制日志還原數(shù)據(jù)時(shí)還原了不必要的數(shù)據(jù)

[root@localhost ~]# 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服務(wù)，初始化數(shù)據(jù)庫(kù)

[root@localhost ~]# service mysqld start Starting mysqld (via systemctl):                           [  OK  ] [root@localhost ~]# 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

四、將完全備份解壓

[root@localhost ~]# unxz /data/all.sql.xz 

五、由于完全備份后數(shù)據(jù)又發(fā)生過(guò)改變所以需要利用二進(jìn)制日志進(jìn)行還原在利用二進(jìn)制還原前，先查看下完全備份時(shí)二進(jìn)制日志所在的位置。

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

六、將完全備份后產(chǎn)生二進(jìn)制日志的數(shù)據(jù)導(dǎo)出

[root@localhost ~]# mysqlbinlog --start-position=8946 /data/bin/mysql-bin.000002 > /data/inc.log

七、進(jìn)入MySQL,停止二進(jìn)制日志記錄

由于接下來(lái)的操作是恢復(fù)數(shù)據(jù)，所以此處不需要讓二進(jìn)制日志記錄數(shù)據(jù)。

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

八、導(dǎo)入之前做的完全備份

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

九、導(dǎo)入完全備份后產(chǎn)生的數(shù)據(jù)

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

十、驗(yàn)證

查看數(shù)據(jù)庫(kù)，以及完全備份后增加的內(nèi)容是否存在

[root@localhost ~]# 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 | +-------+----------+-----+--------+---------+-----------+

---

場(chǎng)景二、誤刪除的恢復(fù)

生產(chǎn)環(huán)境中通常會(huì)出現(xiàn)誤刪除的可能性，此時(shí)就需要用到完全備份，和部分修改后的二進(jìn)制日志來(lái)還原數(shù)據(jù)

場(chǎng)景模擬

一、對(duì)數(shù)據(jù)庫(kù)進(jìn)行完全備份

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

一段時(shí)間后數(shù)據(jù)庫(kù)發(fā)生了誤刪除操作,然后又進(jìn)行了部分其他的曾的操作

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)

此時(shí)發(fā)現(xiàn)了數(shù)據(jù)庫(kù)hellodb.students表被刪除

恢復(fù)數(shù)據(jù)庫(kù)

一、停止MySQL服務(wù)

[root@localhost ~]# service mysqld stop Stopping mysqld (via systemctl):                           [  OK  ]

二、將數(shù)據(jù)庫(kù)文件內(nèi)的文件清空

[root@localhost ~]# rm -rf /data/mysql/*

三、將完全備份解壓

[root@localhost ~]# unxz /data/all.sql.xz 

四、查看完全備份內(nèi)，二進(jìn)制日志的記錄點(diǎn)

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

五、查看二進(jìn)制日志，并從二級(jí)制日志中導(dǎo)出數(shù)據(jù)

[root@localhost ~]# 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 [root@localhost ~]# mysqlbinlog --start-position=494073 /data/bin/mysql-bin.000003 > /data/inc.sql

六、從導(dǎo)出的數(shù)據(jù)中找到那條誤刪除的數(shù)據(jù)將其刪除或注釋

[root@localhost ~]# vim /data/inc.sql #DROP TABLE `hellodb`.`students` /* generated by server */

七、重啟MySQL服務(wù)

[root@localhost mysql]# service mysqld start Starting mysqld (via systemctl):                           [  OK  ]

八、將二進(jìn)制日志關(guān)閉

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

九、導(dǎo)入完全備份

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) #此時(shí)被刪除的students表已經(jīng)找回 MariaDB [test]> SHOW TABLES FROM hellodb; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes           | | coc               | | courses           | | scores            | | students          | | teachers          | | toc               | +-------------------+ 7 rows in set (0.00 sec) #但是誤操作后新加的記錄還沒(méi)有找回 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)

十、導(dǎo)入二進(jìn)制日志所生成的修改后的文件

MariaDB [test]> SOURCE /data/inc.sql  #導(dǎo)入后再次查看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)