1勉抓、MYSQL數(shù)據(jù)的備份方式有哪些?
? ? 答:備份方式有:物理備份候学、邏輯備份藕筋、冷熱備份;
? ? ? ? ? 物理備份:通過系統(tǒng)自帶命令進行復(fù)制cp或tar打包壓縮的方式備份梳码,也叫冷備份隐圾;
? ? ? ? ? 邏輯備份:通過mysql自帶服務(wù)或者額外安裝軟件進行備份伍掀;
? ? ? ? ? ? ? ? ? ? ? ? ? ? mysqldump 和 mysql
? ? ? ? ? ? 冷熱備份:在備份的過程中別人無法訪問的叫冷備份,備份過程中也能訪問的叫熱備份暇藏;
2蜜笤、備份策略有哪些?
? ? 答:備份策略有:完全備份盐碱、增量備份把兔、差異備份;
? ? ? ? ? 完全備份:備份所有數(shù)據(jù)瓮顽;
? ? ? ? ? ?
? ? ? ? ? 增量備份:備份上次備份后县好,所有新產(chǎn)生的數(shù)據(jù);
? ? ? ? ? ? ? ? ? ? ? ? ? ? 如:周一備份之后暖混,周二產(chǎn)生了新數(shù)據(jù)缕贡,再次備都份時只需要備份新增的周二的數(shù)據(jù)即可;
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 以上一次備份作為新數(shù)據(jù)備份的參考點拣播,備份時間短晾咪,節(jié)約內(nèi)存;
? ?
? ? ? ? ? ? 差異備份:備份完全備份后诫尽,所有新產(chǎn)生的數(shù)據(jù)禀酱;
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 如:差異備份時,主要以完全備份作為參考點牧嫉,每次備份都拿新數(shù)據(jù)與完全備份時的數(shù)據(jù)對比剂跟;
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 周一完全備份,周二新增數(shù)據(jù)進行備份酣藻,差異備份會把周二的數(shù)據(jù)與周一完全備份的數(shù)據(jù)全部備份? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 一遍曹洽,周三,周四依然如此辽剧;
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 以完全備份為參考點送淆,備份所需時間長,耗內(nèi)存怕轿,安全偷崩;
各種備份方式的操作流程與優(yōu)缺點:
1、物理備份的具體操作流程與優(yōu)缺點撞羽;
? ? 第一步阐斜、新建一個目錄,將mysql的所有數(shù)據(jù)拷貝到新建的目錄中诀紊;
? ? ? ? ? ? ? ? ? 也可以用tar命令谒出;
? ? ? ? ? ? ? ? ? mkdir? /mysql.backups? ? ;? cp? -r? /var/lib/mysql? ? /mysql.backups
? ? 第二步、scp 遠程拷貝給所需要的主機;
? ? ? ? ? ? ? ? ? scp? -r? ? /mysql.backups? root@客戶IP地址:/目錄
? ? 第三步笤喳、客戶機驗證目錄是否拷貝成功为居;
? ? ? ? ? ? ? ? ? ls? 查看scp拷貝過來的目錄;
? ? 第四步杀狡、清空/var/lib/mysql/目錄下的其他文件蒙畴,將備份文件拷貝到這個目錄下并授權(quán);
? ? ? ? ? ? ? ? ? rm? -rf? /var/lib/mysql/
? ? ? ? ? ? ? ? ? cp? ? -r? ? /備份目錄/*? ? /var/lib/mysql/
? ? ? ? ? ? ? ? ? 因為呜象,是用root拷貝的忍抽,所以目錄中所有的文件都屬于root用戶,mysql用戶無權(quán)讀榷鸠项;
? ? ? ? ? ? ? ? ? 所以,要將這個目錄的所有者和所屬組變更為mysql子姜;
? ? ? ? ? ? ? ? ? chown? -R? ? mysql:mysql? ? ? /var/lib/mysql/
? ? 第五步祟绊、重起mysql服務(wù),驗證備份結(jié)果哥捕;
? ? ? ? ? ? ? ? ? systemctl? restart? mysql?
? ? ? ? ? ? ? ? ? mysql? -u? toot? -p密碼
優(yōu)點:簡單便捷
缺點:隨著時間的積累數(shù)據(jù)越越來越大赖瞒,備份起來費時費力纯续,并且存在文件格式與系統(tǒng)不兼容的問題;
所以,物理備份只適合數(shù)據(jù)量小拓瞪,系統(tǒng)統(tǒng)一時使用劫笙;
2井辆、邏輯備份的具體操作流程與優(yōu)缺點借跪;
? ? 邏輯備份之完全備份與恢復(fù)——mysqldump
? ? 備份命令格式:】#? ? mysqldump? -uroot? -p密碼? 庫名? ? >? 路徑/xxx.sql
? ? 恢復(fù)命令格式:】#? ? mysql? -uroot? -p密碼? 庫名? <? ? 路徑/xxx.sql
? ? 備份所有數(shù)據(jù)、備份數(shù)據(jù)庫愧薛、備份表的具體操作:
* mysqldump? -uroot? -p密碼? all? (或-databases晨炕、或-A)? >? 路徑/xxx.sql?
* 如何備份數(shù)據(jù)庫所有數(shù)據(jù)?
* 如何備份數(shù)據(jù)庫中的單個庫毫炉?
* mysqldump? -uroot? -p密碼? 庫名? >? 路徑/xxx.sql?
* 如何同時備份數(shù)據(jù)庫中的多個庫瓮栗?
* mysqldump? -uroot? -p密碼? -B? 庫名1? 庫名2? 庫n...? >? 路徑/xxx.sql?
* 如何備份數(shù)據(jù)庫中的表?
* mysqldump? ? -uroot? ? -p密碼? ? 庫名? 表名1? 表名2? ? >? 路徑/xxx.sql
? ? 恢復(fù)所有數(shù)據(jù)瞄勾、備份數(shù)據(jù)庫费奸、備份表的具體操作:
* 如何恢復(fù)所有數(shù)據(jù)?
* mysql? -uroot? -p密碼? ? <? ? 路徑/xxx.sq? ( 恢復(fù)所有數(shù)據(jù)可以不需要加庫名)
* 如何恢復(fù)單個書庫的數(shù)據(jù)进陡?
* mysql? -uroot? -p密碼? 庫名? <? ? 路徑/xxx.sq
* 如何恢復(fù)多個數(shù)據(jù)庫的數(shù)據(jù)愿阐?
* mysql? -uroot? -p密碼? ? <? ? 路徑/xxx.sq? ( 恢復(fù)多個數(shù)據(jù)庫的數(shù)據(jù)也不需要加庫名)
* 如何恢復(fù)數(shù)據(jù)庫中表的數(shù)據(jù)?
* mysql? -uroot? -p密碼? 庫名? <? ? 路徑/xxx.sq
注意事項:? ? ? ? ?
1. mysqldump備份數(shù)據(jù)四濒,本質(zhì)上備份的是你在數(shù)據(jù)庫中書寫的命令换况;
2. 在恢復(fù)表數(shù)據(jù)時需要查看裝表的庫是存在,如果不存在需要先創(chuàng)建在恢復(fù)盗蟆;
3. 在恢復(fù)多個數(shù)據(jù)庫和所有庫的時候戈二,可以不需要加庫名;
4. 無論是恢復(fù)數(shù)據(jù)還是備份數(shù)據(jù)都要驗證用戶權(quán)限喳资;? ? ?
mysqldump完全備份的優(yōu)缺點:
? ? ? 優(yōu)點:1.mysqldump備份數(shù)據(jù)觉吭,本質(zhì)上備份的是你在數(shù)據(jù)庫中書寫的命令,所以備份的通用性非常好仆邓,mysql命令通用鲜滩;
? ? ? ? ? ? ? ? 2.方便快捷,
? ? ? 缺點:1.備份時會把數(shù)據(jù)庫鎖住導(dǎo)致備份期間無法寫入和讀取數(shù)據(jù)节值,影響客戶體驗徙硅;
? ? ? ? ? ? ? ?
? ? ? ? ? ? ? ? 2.不能時時備份,當備份完之后搞疗,隨即寫入的數(shù)據(jù)沒有備份嗓蘑,如果刪除無法恢復(fù),只能恢復(fù)之前的備份匿乃;
3桩皿、時時備份的具體操作流程與優(yōu)缺點;
1幢炸、時時備份的工作原理:
? ? ? ? ? 第一泄隔、時時備份采取的時運用二進制的方式備份你在數(shù)據(jù)庫中所操作的所有除查看命令外的,會讓數(shù)據(jù)庫變動的命令宛徊;
? ? ? ? ? 比如:insert? into? 佛嬉、? drop? 、? update? 闸天、? delete? 等等
? ? ? ? ? ? ? ? ? ? select 巷燥、 show? 、? desc? 查看命令不備份号枕;
? ? ? ? ? 第二缰揪、每次產(chǎn)生一個新的二進制日志文件,系統(tǒng)都會自動生成一個索引文件葱淳,用于記錄日志已有的文件名钝腺;
? ? ? ? ? 第三、需要在/etc/my.cnf文件中的 [ mysql ] 字符下面添加:
? ? ? ? ? ? ? ? ? ? log_bin[=dir/name]? ? ? ? ? ? ?
? ? ? ? ? ? ? ? ? ? ## [=dir/name]譯為指定日志文件存放的目錄位置赞厕,不設(shè)定的情況默認存放在/var/lib/mysql/艳狐;
? ? ? ? ? ? ? ? ? ? server_id=數(shù)字? ? ? ? ? ? ? ? ? ? ? ?
? ? ? ? ? ? ? ? ? ? ##譯為服務(wù)器id,0-255隨便填寫一個數(shù)字即可皿桑;
? ? ? ? ? ? ? ? ? ? max_binlog_size=數(shù)字M? ? ? ?
? ? ? ? ? ? ? ? ? ? ##譯為日志文件的內(nèi)存大小為多少M毫目,默認1G蔬啡,不設(shè)定的情況下為默認值;
? ? ? ? ? ? ? ? ?
2镀虐、如何手動添加日志文件箱蟆?
? ? ? ? ? ? ? 第一、重新啟動mysql服務(wù)刮便,生產(chǎn)環(huán)境下禁用空猜,因為在啟動過程中可能會有客戶訪問;
? ? ? ? ? ? ? 第二恨旱、在mysql環(huán)境中執(zhí)行刷新命令:? mysql? >? flush? logs;
? ? ? ? ? ? 第三辈毯、運用mysqldump備份數(shù)據(jù)時添加--flush-logs選項;
? ? ? ? ? ? ? ? ? ? ? ? mysqldump? -uroot? -p密碼? --flush-logs? 庫名? >? 路徑/xxx.sql
? ? ? ? ? ? ? 第四搜贤、linux系統(tǒng)命令行中添加-e選項谆沃;
? ? ? ? ? ? ? ? ? ? ? ? mysql? -uroot? -p密碼? -e? "flush? logs"
? ? ? ? ? ? ? ? ? ? ? ? 這個方法可以不用進入數(shù)據(jù)庫,在系統(tǒng)命令行可以查看到mysql數(shù)據(jù)庫中的內(nèi)容仪芒;
3管毙、如何刪除無用的日志文件?
? ? ? ? ? ? ? 第一桌硫、刪除早于指定版本之前的binlog日志夭咬;
? ? ? ? ? ? ? ? ? ? ? ? 命令格式:purge? master? logs? to? "binlog文件名";
? ? ? ? ? ? ? 第二铆隘、刪除所有binlog日志卓舵,重建新日志;
? ? ? ? ? ? ? ? ? ? ? ? 命令格式:reset? master ;
? ? ? ? ? ? ? 注:刪除日志文件的同時膀钠,索引文件也會自動刪除掏湾,并同步日志文件;
? ? ? ? ? ? ? ? ? ? 在linux命令行用mysql的指令:
? ? ? ? ? ? ? ? ? ? mysql? -uroot? -p密碼? -e? ? "mysql的指令"
? ? ? ? ? ? ? ? ? ? 在mysql命令行用linux的指令:
? ? ? ? ? ? ? ? ? ? system? linux的指令
4肿嘲、如何自定義mysql數(shù)據(jù)庫中的binlog日志文件名和目錄融击?
? ? ? ? ? 第一、不能把目錄創(chuàng)建在/root目錄下雳窟,因為/root目錄是管理員root的家目錄尊浪,所有者是root,如果把權(quán)限賦予給其他用戶封救,會非常危險拇涤,所以不能把目錄創(chuàng)建在/root目錄下;
? ? ? ? ? 第二誉结、在根目錄下創(chuàng)建一個目錄鹅士,并歸屬與mysql用戶;
? ? ? ? ? ? ? ? ? ? mkdir? ? /mylog? ? ;? chown? mysql:mysql? ? /mylog
? ? ? ? ? 第三惩坑、修改mysql的配置文件/etc/my.conf掉盅;
? ? ? ? ? ? ? ? ? ? vim? ? /etc/my.conf
? ? ? ? ? ? ? ? ? ? [mysql]
? ? ? ? ? ? ? ? ? ? server_id=50? ? ? ? ? ? ? ? ? ? ##1-255之間的數(shù)字均可也拜;
? ? ? ? ? ? ? ? ? ? log_bin=/mylog/bac? ? ? ##目錄均可自定義
? ? ? ? ? 第四、驗證目錄是否修改成功趾痘;
? ? ? ? ? ? ? ? ? a慢哈、在linux命令行查看日志文件是否有生成;
? ? ? ? ? ? ? ? ? b扼脐、在mysql命令行查看:? show? master? status;
5、如何運用binlog日志文件恢復(fù)所有的數(shù)據(jù)奋刽?
? ? ? ? ? 日志文件恢復(fù)數(shù)據(jù)的原理是:通過記錄保存mysql執(zhí)行過的命令瓦侮,然后再通過mysqlbinlog命令執(zhí)行一遍;
? ? ? ? ? 第一佣谐、將binlog日志文件拷貝到需要數(shù)據(jù)恢復(fù)的主機肚吏;
? ? ? ? ? 第二、運用 mysqbinlog 命令恢復(fù)數(shù)據(jù)狭魂;
? ? ? ? ? ? ? ? ? ? mysqlbinlog? ? 日志文件路徑? |? mysql? -uroot? -p密碼
? ? ? ? ? 第三步罚攀、驗證數(shù)據(jù)是否有恢復(fù);
? ? ? ? ? ? ? ? ? ? show? databases;? ? ? select? *? from? 庫名.表名;
? ? ? ? ? 注意事項:
? ? ? ?
6雌澄、如何修改日志格式斋泄?
? ? ? ? 第一、查看當前日志格式,"binlog_format"日志變量镐牺;
? ? ? ? ? ? ? ? ? ? mysql >? show? variables? like? "binlog_format";
? ? ? ? ? 第二炫掐、必須要了解日志的三種格式;
? ? ? ? ? ? ? ? A睬涧、statement? 這種格式每一條修改數(shù)據(jù)的SQL命令都會記錄再binlog日志中募胃;
? ? ? ? ? ? ? ? B、row 不記錄SQL語句上下文相關(guān)信息僅保存哪條記錄備修改畦浓;
? ? ? ? ? ? ? ? C痹束、mixed 以上兩種格式的混合使用;
? ? ? ? ? 第三讶请、修改日志的具體步驟祷嘶;
? ? ? ? ? ? ? ? ? 在 /etc/my.cnf 配置文件 [mysql] 字符下添加 binlog_format="日志格式" 即可;
? ? ? ? ? ? ? ? ? 然后夺溢,重新啟動mysql抹蚀,并用 mysqlbinlog? 日志路徑文件? |? grep? -i? mysql命令;
? ? ? ? ? ? ? ? ? 例如:? mysql? >? mysqlbinlog? ? ? /mylog/bac.000001? |? grep? insert
7企垦、如何運用binlog日志恢復(fù)指定的部分日志环壤?
? ? ? ? ? 第一、要明白什么是“偏移量”的概念钞诡?
? ? ? ? ? ? ? ? ? 偏移量——指的就是字符A到字符B的距離
? ? ? ? ? ? ? ? ? 在mysql命令行輸入的每一條改變數(shù)據(jù)庫和表格的數(shù)據(jù)郑现,偏移量都會改變——Position下的數(shù)字會變化湃崩;
? ? ? ? ? ? ? ? ? 偏移量可以用于恢復(fù)指定的數(shù)據(jù);
? ? ? ? ? 第二接箫、恢復(fù)指定部分數(shù)據(jù)的命令格式:
? ? ? ? ? mysqlbinlog? ? 日志文件路徑? ? ##先查看出偏移量的起始與結(jié)束位置攒读,時間也是用mysqlbinlog命令查詢;
? ? ? ? ? ]#? mysqlbinlog? --start-position=偏移量? --stop-position=偏移量? ? 日志文件路徑? ? |? ? mysql? -uroot? -p密碼
? ? ? ? ? ]#? mysqlbinlog? --start-datetime="yyyy-mm-dd hh:mm:ss"? ? --stop-datetime="yyyy-mm-dd hh:mm:ss"
? ? ? ? ? ? ? ? 日志文件路徑? ? |? ? mysql? -uroot? -p密碼
? ? ? ? ? 注:在生產(chǎn)環(huán)境中通常是drop刪除在最后辛友,所以可以快速定位偏移量薄扁;
以上.......
<END>
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
祝:開心!
羅貴
2019-05-17于深圳