站長資訊網(wǎng)MySQL的備份還原(mysqldump)
MySQL的還原前提是要建立在，有完全備份和二進(jìn)制日志開啟的前提下，并且二進(jìn)制日志文件和完全備份存放在與數(shù)據(jù)庫文件不同的磁盤上，否則當(dāng)磁盤發(fā)生損壞數(shù)據(jù)將無法進(jìn)行恢復(fù)。
開啟二進(jìn)制日志
開啟二進(jìn)制日志需要將MySQL中的sql_log_bin和log_bin這兩個(gè)選項(xiàng)
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
二進(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
場景一、磁盤損壞恢復(fù)
生產(chǎn)中會發(fā)生存放數(shù)據(jù)的磁盤突然發(fā)生損壞而造成數(shù)據(jù)丟失的情況，此時(shí)就需要結(jié)合之前所作的完全備份以及二進(jìn)制日志進(jìn)行將數(shù)據(jù)完全恢復(fù)
場景模擬
一、對數(shù)據(jù)庫進(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ù)庫發(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ù)庫發(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)制日志。
由于每次啟動服務(wù)都會重新生成一個(gè)新的二進(jìn)制日志，所以先查看下二進(jìn)制日志的編號以免等下在使用二進(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ù)庫
[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ā)生過改變所以需要利用二進(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)制日志記錄
由于接下來的操作是恢復(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ù)庫，以及完全備份后增加的內(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 | +-------+----------+-----+--------+---------+-----------+
場景二、誤刪除的恢復(fù)
生產(chǎn)環(huán)境中通常會出現(xiàn)誤刪除的可能性，此時(shí)就需要用到完全備份，和部分修改后的二進(jìn)制日志來還原數(shù)據(jù)
場景模擬
一、對數(shù)據(jù)庫進(jìn)行完全備份
[root@localhost ~]# mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz
一段時(shí)間后數(shù)據(jù)庫發(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ù)庫hellodb.students表被刪除
恢復(fù)數(shù)據(jù)庫
一、停止MySQL服務(wù)
[root@localhost ~]# service mysqld stop Stopping mysqld (via systemctl): [ OK ]
二、將數(shù)據(jù)庫文件內(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)制日志，并從二級制日志中導(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) #但是誤操作后新加的記錄還沒有找回 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)
