mysql備份執行個體方案

標籤：大型資料庫   資料庫檔案   databases   windows   sql語句   

線上備份如何保障備份的資料的一致性，使用邏輯卷快照功能來實現，先鎖幾秒

 

 

把blog日誌重新導向到一個檔案裡，再用source匯入到資料庫裡，因為每操作一步都會產生新的blog日誌

物理備份：

直接複製資料庫檔案，適用於大型資料庫環境，一般結構lvm snapshot的方式實現，缺點是不能恢複到異構系統中如Windows中

邏輯備份

備份的是建表，建庫，插入等操作所執行的sql語句，適用於中小型資料庫，效率比較低

 

Mysqldump實現邏輯備份

文法

#mysqldump -h 伺服器 -u使用者名稱  -p密碼 資料庫名 > 備份檔案.sql

 

資料庫名的選項

--all -databases   所有庫

School   資料庫名

School stu_info t1 school資料庫的表stu_info t1

--databases bbs test mysql 多個資料庫

 

執行個體：

單庫備份：

#mysqldump -uroot -p123 bbs >bbs.sql

#mysqldump -uroot -p123 bbs table1 table 2 > bbs table1 table2.sql

 

多庫備份

#mysqldump -uroot -p123 -databases bbs test mysql > bbs_test_mysql,sql

 

備份所有庫

#mysqldump -uroot -p123 --all-databases > all.sql

 

#mysqldump -uroot -p123 --all-databses --lock-all-tables > /tmp/all2.sql

 

備份/恢複案例：

情況1資料庫損壞了，等於是機器都壞了

操作步驟：

備份

1#mysqldump -uroot -p123 --all-databases --lock-all-tables>/backup/`date+%F`_all.sql

2mysql -uroot -p123 -e ‘flush logs’   //截斷併產生新的binlog

3插入資料   //類比伺服器正常運行

4mysql>set sql_log_bin=0;

Mysql>drop database bbs   //類比伺服器損壞

 

恢複：（乾淨的環境，新機器）

1mysql>set sql_log_bin=0;

 Mysql>source /backup/2014-02-13 all.sql            //恢複最近一次完全備份

2mysqlbinlog 最後一個binlog |mysql -uroot -p 123     //恢複最後一個binlog檔案

 

 

情況2如果有誤刪除

備份

1#mysqldump -uroot -p123 --all-databases --lock-all-tables>/backup/`date+%F`_all.sql

2mysql -uroot -p123 -e ‘flush logs’   //截斷併產生新的binlog

3插入資料   //類比伺服器正常運行

4Mysql>drop table db1.t1  //類比誤刪除

 

恢複：（乾淨的環境，新機器）

1mysql>set sql_log_bin=0;

 Mysql>source /backup/2014-02-13 all.sql            //恢複最近一次完全備份

2mysqlbinlog 最後一個binlog  --stop-position=260|mysql -uroot -p 123  

mysqlbinlog 最後一個binlog  --start-position=500|mysql -uroot -p 123    這種情況下就需要用到blog字元截取來恢複

 

 

備份/恢複注意事項：

1）完全備份到一個乾淨的環境（例如新的資料庫或刪除原有的資料庫）

2）恢複期間所有sql語句不應該記錄到binlog中

3）單庫恢複前需要手動建立資料庫

實現自動化備份

1）寫指令碼

Vim /mysql_back.sql

#!/bin/Bash

#The mysql_back

# Author: luliechu （E-mail: [email protected]）

Back_dir=/backup

Back_file=`date+%F`_all.sql

User=root

Pass=123

If[! -d /backup];

Then

 Mkdir -p /backup

Fi

 

#備份並切斷日誌

Mysqldump -u${user} -p${pass} -- events --all-databases --lock-all-tables>${back_dir}/${back_file}

Mysql  -u${user}  -p${pass} -e ‘flush’ logs

 

#只保留最近一周的備份

Cd $back_dir

Find -mtime +7 -exec rm -rf {}\;

 

 

2）測試

Chmod a+x /mysql_back.sql //給指令碼執行許可權

Chattr +i /mysql_back.sql   //為了安全給這指令檔上鎖，禁止任何人做改動

/mysql_back.sql    //執行指令碼

3）配置定時任務到生產環境中

Crontab  -l

0 2 * * * /mysql_back.sql   //每天淩晨2點執行指令碼

匯出表

將表匯入到文字檔中

表的匯出和匯入

Select ....into outfile 匯出文字檔

執行個體：

Mysql>select * from school.student1

Into outfile ‘student1.txt’

Fields terminated by `,`   //定義欄位分割符

Optinon

Ally enclosed by `’’` 定義字串使用什麼符號括起來

Lines terminated by ‘\n’  //定義分行符號

 

 

資料庫遷移

注意要盡量是相同版本之間遷移

 

Mysqldump -h 遷移源ip -uroot -p123 --databases bbs |mysql -h 目標ip  -uroot

 -p456

//遷移源為本機，不用申請

總結：mysql支援多種不同的備份方式，包括物理備份，邏輯備份等方式，通過在生產環境中一般都是採用物理備份和邏輯備份相結合的方式來實現

 

本文出自 “快樂學習” 部落格，請務必保留此出處http://983865387.blog.51cto.com/9838888/1875829

mysql備份執行個體方案