MySQL如何實現備份與恢復

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

實驗環境：RHEL5.8 ,SElinux關閉，MySQL是tar包初始化安裝版本5.5.28

一.測試環境準備

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

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

建立一目錄用于存放二進制日志

1. mkdir /mybinlog

2. chown mysql:mysql /mybinlog

修改my.cnf

1. vim /etc/my.cnf

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

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

啟動mysqld

1. service mysqld start

1.3 創建一個測試庫與測試表

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 創建用于存放備份的目錄

1. mkdir /myback

2. chown -R mysql:mysql /myback

二,用mysqldump實現備份

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

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 存儲過程與函數

6. --triggers 觸發器

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

8. --flush-logs 日志滾動一次

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

備份二進制日志

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

2.2 模擬數據庫意外損壞，測試完整恢復

1. rm -rf /data/mydata/*

2. rm -rf

3. /mybinlog/*

初始化mysql并啟動mysql

1. cd /usr/local/mysql

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

3. rm -rf /mybinlog/* ##因為我們不是全新初始化的，可能會有報錯的二進制日志，我們不需要

4. service mysqld start ##啟動時會重新生成新的二進制日志的

恢復到備份狀態,備份前先關閉對恢復過程的二進制日志記錄，因為記錄恢復語句是毫無意義的

1. mysql> set global sql_log_bin=0;

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

3. 打開記錄并查看恢復狀況

4. mysql> set global sql_log_bin=1;

5. mysql> show databases;

打開二進制記錄并查看恢復狀況

1. mysql> set global sql_log_bin=1;

2. mysql> show databases;

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

2.3.1 新增數據

1. mysql> use laoguang;

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

3. mysql> drop table linux;

4. mysql> show master status; ##查看當前所在二進制日志中的位置

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

6. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

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

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

我們先恢復完整數據，再恢復完整備份后到刪除之前的數據，對應二進制日志就是完整備份后的二進制日志位置到刪除表之前的位置

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

1. mysql > set global sql_log_bin=0;

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

2.3.3 查看刪除表時的記錄位置

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 由上圖可知刪除是在8893時做的，將二進制文件中完整備份到刪除表之前的記錄導出

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

2. --start-position 指定從哪開始導出二進制日志

3. --stop-position 指定到哪結束

4. --start-datetime 從哪個時間開始格式如"2005-12-25 11:25:56"

5. --stop-datetime 到哪個時間結束

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

將這段二進制記錄應用到mysql的庫中

1. mysql < /tmp/change.sql

進入數據庫查看數據有沒有恢復

1. mysql> select * from linux;

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