mysql按照備份恢復(fù)方式分為邏輯備份和物理備份。邏輯備份是備份sql語句,在恢復(fù)的時候執(zhí)行備份的sql語句實現(xiàn)數(shù)據(jù)庫數(shù)據(jù)的重現(xiàn),物理備份就是備份數(shù)據(jù)文件了,比較形象點(diǎn)就是cp下數(shù)據(jù)文件,但真正備份的時候自然不是的cp這么簡單。
這2種備份各有優(yōu)劣,一般來說,物理備份恢復(fù)速度比較快,占用空間比較大,邏輯備份速度比較慢,占用空間比較小
下面介紹以下3種常用的備案方法
一、mysqldump工具備份
mysqldump由于是mysql自帶的備份工具,所以也是最常用的mysql數(shù)據(jù)庫的備份工具。支持基于InnoDB的熱備份。但由于是邏輯備份,所以速度不是很快,適合備份數(shù)據(jù)量比較小的場景。
mysqldump完全備份+二進(jìn)制日志 —>實現(xiàn)時間點(diǎn)恢復(fù)
溫備:
在使用MyISAM引擎中,只能使用溫備份,這時候要防止數(shù)據(jù)的寫入,所以先加上讀鎖
這時候可以進(jìn)入數(shù)據(jù)庫手動加讀鎖。這樣比較麻煩,在mysqldump工具中直接有一個加鎖的選項
mysqldump --databases mydatabase --lock-all-tables --flush-logs> /tmp/backup-`date +%F-%H-%M`.sql
如果是針對某張表備份,只要在數(shù)據(jù)庫名稱后面加上表名稱就行了
這里注意,要實現(xiàn)時間點(diǎn)的恢復(fù),加上--flush-logs選項,在使用備份文件恢復(fù)后,然后再基于二進(jìn)制日志進(jìn)行時間點(diǎn)的恢復(fù)
時間點(diǎn)的恢復(fù)方法
mysqlbinlog mysql-bin.000000x > /tmp/PointTime.sql
然后用mysql命令導(dǎo)入這個sql腳本就行了
熱備:如果使用的是InnoDB引擎,就不必進(jìn)行對數(shù)據(jù)庫加鎖的操作,加一個選項既可以進(jìn)行熱備份:--single-transaction
mysqldump --databases mydb --single-transaction --flush-logs --master-data=2 > /tmp/backup-`date +%F-%H-%M`.sql
注意點(diǎn)
恢復(fù)的時刻關(guān)閉二進(jìn)制日志
mysql>set sql_log_bin=0;
因為這是基于邏輯備份方式,在恢復(fù)日志時會執(zhí)行sql語句插入數(shù)據(jù),而恢復(fù)時候插入數(shù)據(jù)的日志沒有意義。
二、基于LVM快照備份
在物理備份中 ,有基于文件系統(tǒng)的物理備份(LVM的快照),也可以直接用tar之類的命令打包。但這些只能進(jìn)行冷備份
不同的存儲引擎能備份的級別也不一樣,MyISAM能備份到表級別,而InnoDB不開啟每表一文件的話就只能備份整個數(shù)據(jù)庫。
下面就介紹下使用LVM的快照功能進(jìn)行備份
為了安全 首先在數(shù)據(jù)庫上施加讀鎖
mysql>FLUSH TABLES WITH READ LOCK;
刷新一下二進(jìn)制日志,便于做時間點(diǎn)恢復(fù)
mysql>FLUSH LOGS;
然后創(chuàng)建快照卷
lvcreate –L 1G –s –n data-snap –p –r /dev/myvg/mydata
最后進(jìn)入數(shù)據(jù)庫釋放讀鎖
UNLOCK TABLES;
掛載快照卷進(jìn)行備份
mount –r /dev/myvg/data-snap /mnt/snap
然后對/mnt/snap下的文件進(jìn)行打包備份
還原的時候,關(guān)閉mysqld,然后備份二進(jìn)制日志后將原來備份的文件還原進(jìn)去,然后通過二進(jìn)制日志還原到出錯的時間點(diǎn)(通過二進(jìn)制還原時間點(diǎn)的時候不要忘了暫時關(guān)閉二進(jìn)制日志)
三、使用percona提供的xtrabackup(推薦)
支持InnoDB的物理熱備份,支持完全備份,增量備份,而且速度非常快,而且支持InnoDB引擎的數(shù)據(jù)在不同數(shù)據(jù)庫遷移
為了讓xtrabackup支持更多的功能擴(kuò)展,配置InnoDB每表一個文件的功能
在my.cnf的mysqld中加入此項: innodb_file_per_table=1
此項不啟用將不支持備份單獨(dú)的表
但如果之前沒有啟用這個選項,要實現(xiàn)單表一文件的話,可以用mysqldump導(dǎo)出數(shù)據(jù),然后啟用該選項,恢復(fù)回去后就是單表一文件了
首先下載xtrabackup,下載地址:http://www.percona.com/software/percona-xtrabackup,可以直接下載rpm包安裝即可。
xtrabackup有完全備份,增量備份和部分備份(前面開啟innodb每表一文件,就是為了此功能)
1.完全備份整個數(shù)據(jù)庫
innobackupex --user=root --password=123456 /tmp/backup
此時會在/tmp/backup目錄下生成以時間為名的文件夾,里面是備份文件
在這里,備份的數(shù)據(jù)還不能直接用來還原,因為備份數(shù)據(jù)中會含有尚未提交的事務(wù)或者未同步到數(shù)據(jù)文件中的事物。這里需要用prepare回滾事物使數(shù)據(jù)文件處于一致性。
innobackupex --apply-log /tmp/backup/dir
處理完成后才能用來還原數(shù)據(jù),用此命令還原
innobackupex --copy-back /tmp/backup/dir
要實現(xiàn)時間點(diǎn)還原,還是需要使用二進(jìn)制日志
2.增量備份
增量備份支持Innodb,對于MyISAM只能完全備份
innobackupex –incremental /tmp/backup/incremental --incremental-basedir=/tmp/backup/dir
在進(jìn)行一次增量備份--incremental-basedir要指向上一次增量備份的目錄
如果要進(jìn)行還原,先進(jìn)行prepare處理
這里處理的方式,將備份合并
innobackupex --apply-log --redo-only /tmp/backup/dir
innobackupex --apply-log --redo-only /tmp/backup/dir --incremental-dir=/tmp/backup/incremental
最后使用完全備份的那個備份還原
至于差異備份,只要每次將basedir指向完全備份文件夾就行了
最后再廢話一句:要實現(xiàn)時間點(diǎn)還原,是需要使用二進(jìn)制日志的,所以備份好二進(jìn)制日志至關(guān)重要。除非在恢復(fù)時間點(diǎn)和上一次備份時間點(diǎn)這段時間的數(shù)據(jù)對你來說無所謂。