MySQL資料庫備份

目錄

• 1.使用mysqldump實現邏輯備份
• 2.恢復邏輯備份
• 3.備份/恢復案例
• 4.實現自動化備份
• 5.表的導出和導入
• 6.資料庫遷移

# 1. 物理備份： 直接複製資料庫文件，適用於大型資料庫環境。但不能恢復到異構系統中如Windows。
# 2. 邏輯備份： 備份的是建表、建庫、插入等操作所執行SQL語句，適用於中小型資料庫，效率相對較低。
# 3. 導出表： 將表導入到文本文件中。 

1.使用mysqldump實現邏輯備份

# 語法：
# mysqldump -h 伺服器 -u用戶名 -p密碼 資料庫名 > 備份文件.sql

# 示例：
# 單庫備份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

# 多庫備份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

# 備份所有庫
mysqldump -uroot -p123 --all-databases > all.sql 

2.恢復邏輯備份

# 方法一：
[root@localhost backup]# mysql -uroot -p123 < /backup/all.sql

# 方法二：
mysql> use db1;
mysql> SET SQL_LOG_BIN=0;
mysql> source /root/db1.sql

# 註：如果備份/恢復單個庫時，可以修改sql文件
DROP database if exists school;
create database school;
use school; 

3.備份/恢復案例

# 資料庫備份/恢復實驗一：資料庫損壞
# 備份：
1. # mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql
2. # mysql -uroot -p123 -e 'flush logs' //截斷並產生新的binlog
3. 插入數據 //模擬伺服器正常運行
4. mysql> set sql_log_bin=0; //模擬伺服器損壞
mysql> drop database db;

# 恢復：
1. # mysqlbinlog 最後一個binlog > /backup/last_bin.log
2. mysql> set sql_log_bin=0; 
mysql> source /backup/2014-02-13_all.sql //恢復最近一次完全備份 
mysql> source /backup/last_bin.log //恢復最後個binlog文件


# 資料庫備份/恢復實驗二：如果有誤刪除
# 備份：
1. mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql
2. mysql -uroot -p123 -e 'flush logs' //截斷並產生新的binlog
3. 插入數據 //模擬伺服器正常運行
4. drop table db1.t1 //模擬誤刪除
5. 插入數據 //模擬伺服器正常運行

# 恢復：
1. # mysqlbinlog 最後一個binlog --stop-position=260 > /tmp/1.sql 
# mysqlbinlog 最後一個binlog --start-position=900 > /tmp/2.sql 
2. mysql> set sql_log_bin=0; 
mysql> source /backup/2014-02-13_all.sql //恢復最近一次完全備份
mysql> source /tmp/1.log //恢復最後個binlog文件
mysql> source /tmp/2.log //恢復最後個binlog文件

# 註意事項：
1. 完全恢復到一個乾凈的環境（例如新的資料庫或刪除原有的資料庫）
2. 恢復期間所有SQL語句不應該記錄到binlog中

4.實現自動化備份

# 備份計劃：
1. 什麼時間 2:00
2. 對哪些資料庫備份
3. 備份文件放的位置

# 備份腳本：
[root@localhost~]# vim /mysql_back.sql
# !/bin/bash
back_dir=/backup
back_file=`date +%F`_all.sql
user=root
pass=123

if [ ! -d /backup ];then
mkdir -p /backup

# 備份並截斷日誌
mysqldump -u${user} -p${pass} --events --all-databases > ${back_dir}/${back_file}
mysql -u${user} -p${pass} -e 'flush logs'

# 只保留最近一周的備份
cd $back_dir
find . -mtime +7 -exec rm -rf {} \;

# 手動測試：
[root@localhost ~] # chmod a+x /mysql_back.sql 
[root@localhost ~] # chattr +i /mysql_back.sql
[root@localhost ~] # /mysql_back.sql

# 配置cron：
[root@localhost ~] # crontab -l
2 * * * /mysql_back.sql

5.表的導出和導入

SELECT... INTO OUTFILE 導出文本文件
# 示例：
mysql> SELECT * FROM school.student1
INTO OUTFILE 'student1.txt'
FIELDS TERMINATED BY ',' //定義欄位分隔符
OPTIONALLY ENCLOSED BY '”' //定義字元串使用什麼符號括起來
LINES TERMINATED BY '\n' ; //定義換行符


# mysql 命令導出文本文件
# 示例:
# mysql -u root -p123 -e 'select * from student1.school' > /tmp/student1.txt
# mysql -u root -p123 --xml -e 'select * from student1.school' > /tmp/student1.xml
# mysql -u root -p123 --html -e 'select * from student1.school' > /tmp/student1.html

LOAD DATA INFILE 導入文本文件
mysql> DELETE FROM student1;
mysql> LOAD DATA INFILE '/tmp/student1.txt'
INTO TABLE school.student1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '”'
LINES TERMINATED BY '\n';

# 可能會報錯
mysql> select * from db1.emp into outfile 'C:\\db1.emp.txt' fields terminated by ',' lines terminated by '\r\n';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable


# 資料庫最關鍵的是數據，一旦資料庫許可權泄露，那麼通過上述語句就可以輕鬆將數據導出到文件中然後下載拿走，因而mysql對此作了限制，只能將文件導出到指定目錄
# 在配置文件中
[mysqld]
secure_file_priv='C:\\' # 只能將數據導出到C:\\下

# 重啟mysql
# 重新執行上述語句

6.資料庫遷移

# 務必保證在相同版本之間遷移
# mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目標IP -uroot -p456