站長資訊網MySQL的備份還原(mysqldump)
MySQL的還原前提是要建立在，有完全備份和二進制日志開啟的前提下，并且二進制日志文件和完全備份存放在與數據庫文件不同的磁盤上，否則當磁盤發生損壞數據將無法進行恢復。
開啟二進制日志
開啟二進制日志需要將MySQL中的sql_log_bin和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
[root@localhost ~]# mkdir /data/bin [root@localhost ~]# chown -R mysql.mysql /data/bin #將目錄的屬主和屬組都改為mysql
二進制日志目錄創建完畢后，修改配置文件，添加log-bin選項，并指定路徑，此處要注意mysql-bin是二進制日志的抬頭。
[root@localhost ~]# vim /etc/mysql/my.cnf log-bin=/data/bin/mysql-bin #mysql-bin為二進制日志的文件名的抬頭
以上配置完成后重啟mysql服務
[root@localhost ~]# service mysqld restart Restarting mysqld (via systemctl): [ OK ]
此時二進制日志目錄下已經多出了兩個二進制日志文件
[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
場景一、磁盤損壞恢復
生產中會發生存放數據的磁盤突然發生損壞而造成數據丟失的情況，此時就需要結合之前所作的完全備份以及二進制日志進行將數據完全恢復
場景模擬
一、對數據庫進行備份
[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
二、備份完畢后數據庫發生小部分變化
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)
三、數據庫發生破壞，數據丟失
[root@localhost ~]# rm -rf /data/mysql/*
數據恢復
一、將mysqld服務停止
[root@localhost ~]# service mysqld stop Stopping mysqld (via systemctl): [ OK ]
二、查看下二進制日志。
由于每次啟動服務都會重新生成一個新的二進制日志，所以先查看下二進制日志的編號以免等下在使用二進制日志還原數據時還原了不必要的數據
[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服務，初始化數據庫
[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
五、由于完全備份后數據又發生過改變所以需要利用二進制日志進行還原在利用二進制還原前，先查看下完全備份時二進制日志所在的位置。
[root@localhost ~]# vim /data/all.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=8946;
六、將完全備份后產生二進制日志的數據導出
[root@localhost ~]# 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
十、驗證
查看數據庫，以及完全備份后增加的內容是否存在
[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 | +-------+----------+-----+--------+---------+-----------+
場景二、誤刪除的恢復
生產環境中通常會出現誤刪除的可能性，此時就需要用到完全備份，和部分修改后的二進制日志來還原數據
場景模擬
一、對數據庫進行完全備份
[root@localhost ~]# 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服務
[root@localhost ~]# service mysqld stop Stopping mysqld (via systemctl): [ OK ]
二、將數據庫文件內的文件清空
[root@localhost ~]# rm -rf /data/mysql/*
三、將完全備份解壓
[root@localhost ~]# unxz /data/all.sql.xz
四、查看完全備份內，二進制日志的記錄點
[root@localhost ~]# vim /data/all.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=494073;
五、查看二進制日志，并從二級制日志中導出數據
[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
六、從導出的數據中找到那條誤刪除的數據將其刪除或注釋
[root@localhost ~]# vim /data/inc.sql #DROP TABLE `hellodb`.`students` /* generated by server */
七、重啟MySQL服務
[root@localhost 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)
