MySQL如何實(shí)現(xiàn)數(shù)據(jù)備份與恢復(fù)

下面講講關(guān)于MySQL如何實(shí)現(xiàn)數(shù)據(jù)備份與恢復(fù)，文字的奧妙在于貼近主題相關(guān)。所以，閑話就不談了，我們直接看下文吧，相信看完MySQL如何實(shí)現(xiàn)數(shù)據(jù)備份與恢復(fù)這篇文章你一定會(huì)有所受益。

常見(jiàn)的MySQL管工具
mysql  命令行   跨平臺(tái)  MySQL官方bundle包自帶

MySQL-Workbench  圖形  跨平臺(tái)  MySQL官方提供

MySQL-Front  圖形  Windows  開(kāi)源，輕量級(jí)客戶端軟件

phpMyAdmin  瀏覽器  跨平臺(tái)  開(kāi)源，需LAMP平臺(tái)

Navicat  圖形  Windows  專(zhuān)業(yè)、功能強(qiáng)大、商業(yè)版

PhpMyAdmin部署思路：
1.安裝httpd、mysql、php-mysql及相關(guān)包
2.啟動(dòng)httpd服務(wù)程序
3.解壓phpMyAdmin包，部署到網(wǎng)站目錄
4.配置config.inc.php,指定MySQL主機(jī)地址
5.創(chuàng)建授權(quán)用戶
6.瀏覽器訪問(wèn)、登錄使用

yum  -y  install  httpd  php  php-mysql
tar  -zxf  phpMyAdmin-2.11.11-all-languages.tar.gz  -C /var/www/html/
cd /var/www/html/
mv  phpMyAdmin-2.11.11-all-languages/  phpmyadmin
chown  -R  apache:apache  phpmyadmin/
cp  phpmyadmin/config.sample.inc.php  phpmysdmin/config.inc.php
vim  /var/www/html/phpmyadmin/config.inc.php
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['blowfish_secret'] = '123456';

mysql>create database  bbsdb;
mysql>grant all on bbsdb.* to admin@'localhost' identified by '654321';

systemclt start httpd
firefox http://localhost/phpmyadmin

MySQL數(shù)據(jù)備份與恢復(fù)
數(shù)據(jù)備份方式：
1.物理備份，冷備份：cp 、 tar 、 ....

例：物理備份與恢復(fù)
備份操作格式:
cp  -rp  /var/lib/mysql/數(shù)據(jù)庫(kù)  備份目錄/文件名
tar -zcvf xxx.tar.gz  /var/lib/mysql/數(shù)據(jù)庫(kù)/*

恢復(fù)操作格式:
cp -rp  備份目錄/文件名  /var/lib/mysql/（默認(rèn)存儲(chǔ)路徑）
tar -zxvf  xxx.tar.gz  -C  /var/lib/mysql/數(shù)據(jù)庫(kù)名/

2.邏輯備份，mysqldump 、mysql
原理：執(zhí)行備份時(shí)，根據(jù)已有的庫(kù)和表生成對(duì)應(yīng)SQL命令，把生成的SQL命令存儲(chǔ)到指定的備份文件里。

備份策略：
完全備份：備份所有數(shù)據(jù)（一臺(tái)云服務(wù)器，一個(gè)庫(kù)，一張表）
增量備份：備份自上一次備份（包含完全備份、差異備份、增量備份）之后有變化的數(shù)據(jù)。
差異備份：備份自上一次完全備份之后有變化的數(shù)據(jù)。

例：邏輯備份及恢復(fù)
完全備份操作格式：
mysqldump -u用戶名 -p密碼  源庫(kù)名  >  路徑/xxx.sql

完全備份的恢復(fù)操作格式：
mysql  -u用戶名  -p密碼  目標(biāo)庫(kù)名    <  路徑/xxx.sql

庫(kù)名的表示方式：
--all-databases      所有庫(kù)
庫(kù)名                 指定的單個(gè)庫(kù)
庫(kù)名.表名            指定庫(kù)的指定表
-B 庫(kù)名1  庫(kù)名2 ..   備份多個(gè)庫(kù)

注意事項(xiàng)：
無(wú)論備份還是恢復(fù)，都要驗(yàn)證用戶及權(quán)限。

例：
將所有的庫(kù)備份為mysql-all.sql文件
mysqldump  -u root  -p 123456  --all-databases > alldb.sql

將userdb庫(kù)備份為userdb.sql文件
mysqldump  -u root  -p 123456  userdb  > userdb.sql

將備份文件userdb.sql恢復(fù)到userdb3庫(kù)
mysql>create  databases  userdb3;
mysql -u roo -p 123456 userdb3 < userdb.sql

實(shí)時(shí)增量備份
binlog日志
類(lèi)型：二進(jìn)制日志，用途：記錄所有更改數(shù)據(jù)的操作，
配置：
log_bin[=dir/name]
server_id=數(shù)字
max_binlog_size=數(shù)字m

啟用binlog日志
采用binlog日志的好處
1.記錄除查詢之外的所有SQL命令。
2.用于數(shù)據(jù)恢復(fù)。
3.配置mysql主從同步的必要條件。

例：
vim  /etc/my.cnf
[mysqld]
....
log_bin  //啟用binlog日志
server_id=100   //指定id值,id號(hào)不能重復(fù)（1-255）范圍。

systemctl restart mysqld

binlog相關(guān)文件
默認(rèn)日志文件名：
主機(jī)名-bin.index   //記錄已有的binlog日志文件名
主機(jī)名-bin.000001  //第1個(gè)二進(jìn)制日志（達(dá)到500M存去下一條紀(jì)錄）
主機(jī)名-bin.000002  //第2份二進(jìn)制日志
....

手動(dòng)生成新的日志文件
1.重啟mysql服務(wù)
2.執(zhí)行SQL操作 mysql > flush logs;（flush logs，切換到下一個(gè)binlog日志文件）
3.mysqldump  --flush-logs
4.mysql -uroot -p密碼 -e  'flush  logs'

清理binlog日志
刪除早于指定版本的binlog日志：
purge  master  logs  to  'binlog文件名';

刪除所有binlog日志，重建新日志：
reset  master;

例：
mysql>purge  master  logs to 'mysql-bin.000003';

mysql>reset master;

分析binlog日志
查看日志當(dāng)前記錄格式：
mysql>show variables like 'binlog_format';

修改日志記錄格式：
vim  /etc/my.cnf
[mysqld]
....
binlog_format="mixed"  設(shè)置日志文件的記錄格式

systemctl restart  mysqld

三種記錄格式：
1.statement: 每一條修改數(shù)據(jù)的sql命令都會(huì)記錄在binlog日志中。
2.row: 不記錄sql語(yǔ)句上下文相關(guān)信息，僅保存哪條記錄被修改。
3.mixed: 是以上兩種格式的混合使用。

binlog日志文件記錄sql命令的方式：
1.時(shí)間點(diǎn)
2.pos點(diǎn)（偏移量）

查看有哪些bilog日志：
mysql>show master logs;

使用mysqlbinlog工具
格式：mysqlbinlog  [選項(xiàng)]  binlog日志文件名

常用選項(xiàng)：
1.時(shí)間點(diǎn)：
--start-datetime="yyyy-mm-dd  hh:mm:ss"
--stop-datetime="yyyy-mm-dd hh:mm:ss"
2.pos點(diǎn)：
--start-position=數(shù)字
--stop-position=數(shù)字

例：
查看從2017年1月2日15:30開(kāi)始的更改操作
mysqlbinlob  --start-datetime="2017-01-01 15:30"  /var/lib/mysql-bin.000001
....

at 318

解釋?zhuān)?br/>server id 1        ： 數(shù)據(jù)庫(kù)主機(jī)的服務(wù)號(hào)；
end_log_pos 796    ： sql結(jié)束時(shí)的pos節(jié)點(diǎn)
thread_id=11       ： 線程號(hào)

binlog恢復(fù)數(shù)據(jù)
方式一：
基本思路：
1.使用mysqlbinlog提取歷史SQL操作
2.通過(guò)管道交給mysql命令執(zhí)行

例：
恢復(fù)第1份binlog日志的部分信息
mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/mysql-bin.000001

mysqlbinlog  --start-position=296  --stop-position=1073  /var/lib/mysql-bin.000001  |mysql -uroot  -p123456

方式二：
命令格式：
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
參數(shù)解釋?zhuān)?br/>IN 'log_name'    ：指定要查詢的binlog文件名(不指定就是第一個(gè)binlog文件)
FROM pos         ：指定從哪個(gè)pos起始點(diǎn)開(kāi)始查起(不指定就是從整個(gè)文件首個(gè)pos點(diǎn)開(kāi)始算)
LIMIT [offset,]  ：偏移量(不指定就是0)
row_count        ：查詢總條數(shù)(不指定就是所有行)

例：
mysql>show binlog events in 'mysql-bin.000002'\G;

MySQL備份工具
物理備份缺點(diǎn)：
1.跨平臺(tái)性差
2.備份時(shí)間長(zhǎng)、冗余備份、浪費(fèi)存儲(chǔ)空間

mysqldump備份缺點(diǎn)：
1.效率較低，備份和還原速度慢。
2.備份過(guò)程中，數(shù)據(jù)插入和更新操作會(huì)被掛起。

XtraBackup備份工具：
1.備份過(guò)程中不鎖庫(kù)表，適合生產(chǎn)環(huán)境。
2.由專(zhuān)業(yè)組織Percona提供（改進(jìn)MySQL分支）。
主要含兩個(gè)組件：
1.xtrabackup：C程序，支持InnoDB/XtraDB
2.innobackupex：以Perl腳本封裝xtrabackup,還支持MyISAM

支持事務(wù)和事務(wù)回滾，要求存儲(chǔ)引擎為innodb
事務(wù)日志文件：
ibdata
LSN  日志序列號(hào)
ib_logfile0   //SQL命令
ib_loggile1
....

安裝XtraBackup
yum  -y  install  perl-Digest-MD5.x86_64  rsync perl-DBD-MySQL
rpm  -ivh  libev-4.15-1.el6.rf.x86_64.rpm
rpm  -ivh  percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
rpm  -ql   percona-xtrabackup-24
/usr/bin/innobackupex  //備份innodb、xtrdb、myisam引擎的表
/usr/bin/xbcloud
....
/usr/bin/xtrabackup  //備份innodb、xtrdb引擎的表
....

innobackupex基本選項(xiàng)
--host           //主機(jī)名   
--user           //用戶名
--port           //端口號(hào)
--password       //密碼
--databases      //數(shù)據(jù)庫(kù)名（單個(gè)庫(kù)：databases="庫(kù)名"，多個(gè)庫(kù)：databases="庫(kù)1 庫(kù)2"，單個(gè)表：databases="庫(kù).表"）
--no-timestamp   //不用日期命名備份文件存儲(chǔ)的子目錄名
--redo-only      //日志回滾合并（最后一次增量備份日志回滾不需要此選項(xiàng)）
--apply-log      //準(zhǔn)備還原（回滾日志）
--copy-back      //恢復(fù)數(shù)據(jù)
--incremental 目錄名  //增量備份
--incremental-basedir=目錄名   //增量備份時(shí)，指定上一次備份數(shù)據(jù)存儲(chǔ)的目錄名 
--incremental-dir=目錄名        //準(zhǔn)備恢復(fù)數(shù)據(jù)時(shí)，指定增量備份數(shù)據(jù)存儲(chǔ)的目錄名
--export         //導(dǎo)出表信息
import           //導(dǎo)出表空間

XtraBackup完全備份
格式：innobackupex  -user 用戶名  --password  密碼  --databases="系統(tǒng)庫(kù)列表和存儲(chǔ)數(shù)據(jù)庫(kù)"  備份目錄名  --no-timestamp

例：將所有庫(kù)完全備份到 /backup
innobackupex  --user root  --password 123456 /backup  --no-timestamp  

XtraBackup完全恢復(fù)
完全恢復(fù)時(shí)要求空的庫(kù)目錄
rm -rf /var/lib/mysql
mkdir  /var/lib/mysql
chown  -R  mysql:mysql  /var/lib/mysql
格式：innobackupex  -user  用戶名  --password  密碼  --databases="系統(tǒng)庫(kù)列表和存儲(chǔ)數(shù)據(jù)庫(kù)"   --copy-back  備份目錄名

例:恢復(fù)所有數(shù)據(jù)
innobackupex  --apply-log  /backup 
innobackupex   --copy-back  /backup 

XtraBackup增量備份
必須先有一次完全備份
格式：innobackupex  --user 用戶名  --password 密碼  databases="系統(tǒng)庫(kù)列表和存儲(chǔ)數(shù)據(jù)庫(kù)"  --incremental  目錄名 --incremental-basedir="完全備份目錄名"  -no-timestamp

例：完全備份到/allbak、第一次增量備份到/new1、第二次增量備份到/new2
cp  -rp  /var/lib/mysql/mysql  /root/mysql.bak  //備份授權(quán)庫(kù)
innobackupex  --user root  --password 123456 --databases="gamedb"  /fullbak  --no-timestamp  //完全備份
innobackupex  --user root  --password 123456 --databases="gamedb"  --incremental /new1  --incremental-basedir="/fullbak" --no-timestamp //第一次增量備份
innobackupex  --user root  --password 123456 --databases="gamedb"  --incremental /new2  --incremental-basedir="/new1"  --no-timestamp  //第二次增量備份

XtraBackup增量恢復(fù)
rm -rf /var/lib/mysql
mkdir  /var/lib/mysql
chown  -R  mysql:mysql  /var/lib/mysql
格式：
1.innobackupex  --user  用戶名  --password  密碼  --databases="系統(tǒng)庫(kù)列表和存儲(chǔ)數(shù)據(jù)庫(kù)"  --apply-log  --redo-only 完全備份目錄名  
2.innobackupex  --user  用戶名  --password  密碼  --databases="系統(tǒng)庫(kù)列表和存儲(chǔ)數(shù)據(jù)庫(kù)"  --apply-log  --redo-only 完全備份目錄名  --incremental-dir=增量備份目錄名
3.innobackupex  --user  用戶名  --psssword  密碼  --databases="系統(tǒng)庫(kù)列表和存儲(chǔ)數(shù)據(jù)庫(kù)"  --copy-back  完全備份目錄名

例：恢復(fù)第一次增量備份到/new1、第二次增量備份到/new2的數(shù)據(jù)
rm -rf /var/lib/mysql
mkdir  /var/lib/mysql
innobackupex  --user root  --password 123456  --databases="gamedb"  --apply-log  --redo-only  /fullbak  //恢復(fù)完全備份
innobackupex  --user root  --password 123456  --databases="gamedb"  --apply-log  --redo-only  /fullbak  --incremental-dir="/new1"  //恢復(fù)增量
innobackupex  --user root  --password 123456  --databases="gamedb"  --apply-log  /fullbak  --incremental-dir="new2"  //恢復(fù)增量
innobackupex  --user root  --password 123456  --databases="gamedb"  --copy-back  /fullbak  //拷貝文件
cp -r /root/mysql.bak  /var/lib/mysql/mysql
chown  -R  mysql:mysql  /var/lib/mysql

恢復(fù)完全備份文件中的單個(gè)表
格式：innobackupex  innobackupex  --user  用戶名  --password  密碼  --databases="系統(tǒng)庫(kù)列表和存儲(chǔ)數(shù)據(jù)庫(kù)"  --apply-log  --export 完全備份目錄名

例：完全備份數(shù)據(jù)庫(kù)到/allbak目錄
innobackupex  --user root  --password 123456  --databases="gamedb"  /allbak --no-timestamp //完全備份
mysql>drop  table gamedb.a;
innobackupex  --user root  --password 123456  --databases="gamedb"  --apply-log  --export  /allbak  //導(dǎo)出表信息
mysql>create  table gamedb.a(id int);  //創(chuàng)建表
mysql>alter   table gamedb.a  discard tablespace;  //刪除表空間
cp  /allbak/gamedb/a.{ibd,cfg,exp}  /var/lib/mysql/gamedb  //拷貝表信息文件
chown  mysql:mysql  /var/lib/mysql/gamedb/a.*   //修改所有者
mysql>alter  table  gamedb.a  import  tablespace;  //導(dǎo)入表空間
對(duì)于以上MySQL如何實(shí)現(xiàn)數(shù)據(jù)備份與恢復(fù)相關(guān)內(nèi)容，大家還有什么不明白的地方嗎？或者想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。