mysql資料備份與操作

標籤：插入   版本   備份   copy   cron   案例   chm   data   位置   

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

一、使用mysqldump實現邏輯備份

#文法：# mysqldump -h 伺服器 -u使用者名稱 -p密碼 資料庫名 > 備份檔案.sql#樣本：#單庫備份mysqldump -uroot -p123 db1 > db1.sqlmysqldump -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

二、恢複邏輯備份

#方法一：[[email protected] 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;

三、備份/恢複案例

#Database Backup/恢複實驗一：資料庫損壞備份：1. # mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql2. # mysql -uroot -p123 -e ‘flush logs‘ //截斷併產生新的binlog3. 插入資料 //類比伺服器正常運行4. mysql> set sql_log_bin=0; //類比伺服器損壞mysql> drop database db;恢複：1. # mysqlbinlog 最後一個binlog > /backup/last_bin.log2. mysql> set sql_log_bin=0; mysql> source /backup/2014-02-13_all.sql //恢複最近一次完全備份 mysql> source /backup/last_bin.log //恢複最後個binlog檔案#Database Backup/恢複實驗二：如果有誤刪除備份：1. mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql2. mysql -uroot -p123 -e ‘flush logs‘ //截斷併產生新的binlog3. 插入資料 //類比伺服器正常運行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中

View Code

四、實現自動化備份

備份計劃：
1. 什麼時間 2:00
2. 對哪些Database Backup
3. 備份檔案放的位置

備份指令碼：
[[email protected] ~]# 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
fi

# 備份並截斷日誌
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 {} \;

手動測試：
[[email protected] ~]# chmod a+x /mysql_back.sql
[[email protected] ~]# chattr +i /mysql_back.sql
[[email protected] ~]# /mysql_back.sql

配置cron：
[[email protected] ~]# crontab -l
2 * * * /mysql_back.sql

備份計劃：1. 什麼時間 2:002. 對哪些Database Backup3. 備份檔案放的位置備份指令碼：[[email protected] ~]# vim /mysql_back.sql#!/bin/bashback_dir=/backupback_file=`date +%F`_all.sqluser=rootpass=123if [ ! -d /backup ];thenmkdir -p /backupfi# 備份並截斷日誌mysqldump -u${user} -p${pass} --events --all-databases > ${back_dir}/${back_file}mysql -u${user} -p${pass} -e ‘flush logs‘# 只保留最近一周的備份cd $back_dirfind . -mtime +7 -exec rm -rf {} \;手動測試：[[email protected] ~]# chmod a+x /mysql_back.sql [[email protected] ~]# chattr +i /mysql_back.sql[[email protected] ~]# /mysql_back.sql配置cron：[[email protected] ~]# crontab -l0 2 * * * /mysql_back.sql

五、表的匯出和匯入

SELECT... INTO OUTFILE 匯出文字檔樣本：mysql> SELECT * FROM school.student1INTO 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.htmlLOAD DATA INFILE 匯入文字檔mysql> DELETE FROM student1;mysql> LOAD DATA INFILE ‘/tmp/student1.txt‘INTO TABLE school.student1FIELDS TERMINATED BY ‘,‘OPTIONALLY ENCLOSED BY ‘”‘LINES TERMINATED BY ‘\n‘;

View Code

六、資料庫遷移

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

 

mysql資料備份與操作