MySQL如何實(shí)現(xiàn)備份與恢復(fù)

小編給大家分享一下MySQL如何實(shí)現(xiàn)備份與恢復(fù)，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

實(shí)驗(yàn)環(huán)境：RHEL5.8 ,SElinux關(guān)閉，MySQL是tar包初始化安裝版本5.5.28

一.測試環(huán)境準(zhǔn)備

1.1 mysql的安裝就不說了，見 http://www.linuxidc.com/Linux/2012-04/58990.htm

1.2 編緝/etc/my.cnf把二進(jìn)制日志存放目錄改到其它非數(shù)據(jù)目錄,innodb每表一文件

建立一目錄用于存放二進(jìn)制日志

1. mkdir /mybinlog

2. chown mysql:mysql /mybinlog

修改my.cnf

1. vim /etc/my.cnf

2. log-bin=/mybinlog/mysql-bin ##二進(jìn)制日志目錄及文件名前綴

3. innodb_file_per_table = 1 ##啟用InnoDB表每表一文件，默認(rèn)所有庫使用一個(gè)表空間

啟動(dòng)mysqld

1. service mysqld start

1.3 創(chuàng)建一個(gè)測試庫與測試表

1. mysql> create database laoguang;

2. mysql> use laoguang;

3. mysql> create table linux (id tinyint auto_increment primary key,name char(10));

4. mysql> insert into linux (name) values ('apache'),('nginx'),('php');

1.4 創(chuàng)建用于存放備份的目錄

1. mkdir /myback

2. chown -R mysql:mysql /myback

二,用mysqldump實(shí)現(xiàn)備份

2.1 mysqldump用來溫備，所以我們得為所有庫加讀鎖,并且滾動(dòng)一下二進(jìn)制日志，并記錄當(dāng)前二進(jìn)制文件位置

1. mysqldump --all-databases --lock-all-tables --routines --triggers --master-data=2 \

2. --flush-logs > /myback/2012-12-3.19-23.full.sql

3. --all-tables 備份所有庫

4. --lock-all-tables 為所有表加讀鎖

5. --routinge 存儲過程與函數(shù)

6. --triggers 觸發(fā)器

7. --master-data=2 在備份文件中記錄當(dāng)前二進(jìn)制日志的位置，并且為注釋的，1是不注釋掉在主從復(fù)制中才有意義

8. --flush-logs 日志滾動(dòng)一次

查看有沒有備份成功，有沒有啟用新二進(jìn)制的日志，查看備份的文件中有沒有記錄完整備份后二進(jìn)制的位置

備份二進(jìn)制日志

1. cp /mybinlog/mysql-bin.000001 /myback/2012-12-3.19-23.full.00001

2.2 模擬數(shù)據(jù)庫意外損壞，測試完整恢復(fù)

1. rm -rf /data/mydata/*

2. rm -rf

3. /mybinlog/*

初始化mysql并啟動(dòng)mysql

1. cd /usr/local/mysql

2. ./scripts/mysql_install_db --user=mysql--datadir=/data/mydata

3. rm -rf /mybinlog/* ##因?yàn)槲覀儾皇侨鲁跏蓟?，可能?huì)有報(bào)錯(cuò)的二進(jìn)制日志，我們不需要

4. service mysqld start ##啟動(dòng)時(shí)會(huì)重新生成新的二進(jìn)制日志的

恢復(fù)到備份狀態(tài),備份前先關(guān)閉對恢復(fù)過程的二進(jìn)制日志記錄，因?yàn)橛涗浕謴?fù)語句是毫無意義的

1. mysql> set global sql_log_bin=0;

2. mysql < /myback/2012-12-3.19-23.full.sql ##如果有賬號密碼記的-u -h哦

3. 打開記錄并查看恢復(fù)狀況

4. mysql> set global sql_log_bin=1;

5. mysql> show databases;

打開二進(jìn)制記錄并查看恢復(fù)狀況

1. mysql> set global sql_log_bin=1;

2. mysql> show databases;

2.3 模擬一種場景，我往linux表中新添加了數(shù)據(jù)，然后不小心將這個(gè)表刪了，我們要恢復(fù)到刪除之前的狀態(tài)，并且新加的數(shù)據(jù)還存在。

2.3.1 新增數(shù)據(jù)

1. mysql> use laoguang;

2. mysql> insert into linux (name) values ('haddop'), ('mysql');

3. mysql> drop table linux;

4. mysql> show master status; ##查看當(dāng)前所在二進(jìn)制日志中的位置

5. +------------------+----------+--------------+------------------+

6. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

7. +------------------+----------+--------------+------------------+

8. | mysql-bin.000001 | 9005 | | |

9. +------------------+----------+--------------+------------------+

我們先恢復(fù)完整數(shù)據(jù)，再恢復(fù)完整備份后到刪除之前的數(shù)據(jù)，對應(yīng)二進(jìn)制日志就是完整備份后的二進(jìn)制日志位置到刪除表之前的位置

2.3.2 先恢復(fù)完整備份，同樣恢復(fù)過程不要記錄日志

1. mysql > set global sql_log_bin=0;

2. mysql < /myback/2012-12-3.19-23.full.sql

2.3.3 查看刪除表時(shí)的記錄位置

1. mysqlbinlog /mybinlog/mysql-bin.000001

2. # at 8893

3. #121202 14:14:07 server id 1 end_log_pos 9005 Query thread_id=5exec_time=0error_code=0

4. SET TIMESTAMP=1354428847/*!*/;

5. DROP TABLE `linux` /* generated by server */

6. /*!*/;

7. DELIMITER ;

8. # End of log file

2.3.4 由上圖可知?jiǎng)h除是在8893時(shí)做的，將二進(jìn)制文件中完整備份到刪除表之前的記錄導(dǎo)出

1. mysqlbinlog --stop-position=8893 /mybinlog/mysql-bin.000001 > /tmp/change.sql

2. --start-position 指定從哪開始導(dǎo)出二進(jìn)制日志

3. --stop-position 指定到哪結(jié)束

4. --start-datetime 從哪個(gè)時(shí)間開始格式如"2005-12-25 11:25:56"

5. --stop-datetime 到哪個(gè)時(shí)間結(jié)束

由于這個(gè)二進(jìn)制日志是我們完整恢復(fù)后才啟用的，所以我們直接從頭開始即可，如果你的二進(jìn)制日志很多，請查看完整備份中記錄的備份時(shí)的位置，從那開始到刪除之前即可

將這段二進(jìn)制記錄應(yīng)用到mysql的庫中

1. mysql < /tmp/change.sql

進(jìn)入數(shù)據(jù)庫查看數(shù)據(jù)有沒有恢復(fù)

1. mysql> select * from linux;

看完了這篇文章，相信你對“MySQL如何實(shí)現(xiàn)備份與恢復(fù)”有了一定的了解，如果想了解更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！