mysql備份的三種方法詳解。本站提示廣大學習愛好者：（mysql備份的三種方法詳解）文章只能為提供參考，不一定能成為您想要的結果。以下是mysql備份的三種方法詳解正文
1、備份的目標
做災害恢復：對破壞的數據停止恢復和復原
需求轉變：因需求轉變而須要把數據復原到轉變之前
測試：測試新功效能否可用
2、備份須要斟酌的成績
可以容忍喪失多長時光的數據；
恢單數據要在多長時光內完；
恢復的時刻能否須要連續供給辦事；
恢復的對象，是全部庫，多個表，照樣單個庫，單個表。
3、備份的類型
1、依據能否須要數據庫離線
冷備（cold backup）：須要關mysql辦事，讀寫要求均不許可狀況下停止；
溫備（warm backup）： 辦事在線，但僅支撐讀要求，不許可寫要求；
熱備（hot backup）：備份的同時，營業不受影響。
注：
1、這類類型的備份，取決於營業的需求，而不是備份對象
2、MyISAM不支撐熱備，InnoDB支撐熱備，然則須要專門的對象
2、依據要備份的數據聚集的規模
完整備份：full backup，備份全體字符集。
增量備份: incremental backup 前次完整備份或增量備份以來轉變了的數據，不克不及零丁應用，要借助完整備份，備份的頻率取決於數據的更新頻率。
差別備份：differential backup 前次完整備份以來轉變了的數據。
建議的恢復戰略：
完整+增量+二進制日記
完整+差別+二進制日記
3、依據備份數據或文件
物理備份：直接備份數據文件
長處：
備份和恢復操作都比擬簡略，可以或許跨mysql的版本，
恢復速度快，屬於文件體系級其余
建議：
不要假定備份必定可用，要測試
mysql>check tables；檢測表能否可用
邏輯備份: 備份表中的數據和代碼
長處：
恢復簡略、
備份的成果為ASCII文件，可以編纂
與存儲引擎有關
可以經由過程收集備份和恢復
缺陷：
備份或恢復都須要mysql辦事器過程介入
備份成果占領更多的空間，
浮點數能夠會喪失精度
復原以後，縮影須要重建
四：備份的對象
1、 數據；
2、設置裝備擺設文件；
3、代碼：存儲進程、存儲函數、觸發器
4、os相干的設置裝備擺設文件
5、復制相干的設置裝備擺設
6、二進制日記
5、備份和恢復的完成
1、應用select into outfile完成數據的備份與復原
1.1把須要備份的數據備份出來
mysql> use hellodb; //翻開hellodb庫
mysql> select * from students; 檢查students的屬性
mysql> select * from students where Age > 30 into outfile ‘/tmp/stud.txt' ; //將年紀年夜於三十的同窗的信息備份出來
留意：
備份的目次途徑必需讓以後運轉mysql辦事器的用戶mysql具有拜訪權限
備份完成以後須要把備份的文件從tmp目次復制走，要不就掉去備份的目標了
回到tmp目次下檢查適才備份的文件
[root@www ~]# cd /tmp
[root@www tmp]# cat stud.txt
3Xie Yanke53M216
4Ding Dian32M44
6Shi Qing46M5\N
13Tian Boguang33M2\N
25Sun Dasheng100M\N\N
[root@www tmp]#
你會發明是個文本文件。所以不克不及直接導入數據庫了。須要應用load data infile 恢復
回到mysql辦事器端，刪除年紀年夜於30的用戶，模仿數據被損壞
mysql> delete from students where Age > 30;
mysql> load data infile '/tmp/stud.txt' into table students;
2、應用mysqldump對象對數據停止備份和復原
mysqldump 經常使用來做溫備，所以我們起首須要對想備份的數據施加讀鎖，
2.1 施加讀鎖的方法：
1.直接在備份的時刻添加選項
--lock-all-tables 是對要備份的數據庫的一切表施加讀鎖
--lock-table 僅對單張表施加讀鎖，即便是備份全部數據庫，它也是在我們備份某張表的時刻才對該表施加讀鎖，是以實用於備份單張表
2、在辦事器端書寫敕令，
mysql> flush tables with read lock; 施加鎖，表現把位於內存上的表一切都同步到磁盤上去，然後施加讀鎖
mysql> flush tables with read lock;釋放讀鎖
但這關於InnoDB存儲引擎來說，固然你也可以或許要求道讀鎖，然則不代表它的一切數據都曾經同步到磁盤上，是以當面臨InnoDB的時刻，我們要應用mysql> show engine innodb status; 看看InnoDB一切的數據都曾經同步到磁盤上去了，才停止備份操作。
2.2備份的戰略：
完整備份+增量備份+二進制日記
演示備份的進程;
2.3 先給數據庫做完整備份：
[root@www ~]# mysqldump -uroot --single-transaction --master-data=2 --databases hellodb > /backup/hellodb_`date +%F`.sql
--single-transaction: 基於此選項能完成熱備InnoDB表；是以，不須要同時應用--lock-all-tables；
--master-data=2 記載備份那一時辰的二進制日記的地位，而且正文失落，1是不正文的
--databases hellodb 指定備份的數據庫
然後回到mysql辦事器端，
2.4回到mysql辦事器端更新數據
mysql> create table tb1(id int); 創立表
mysql> insert into tb1 values (1),(2),(3); 拔出數據，這裡只做演示，隨意拔出了幾個數據
2.5先檢查完整備份文件裡邊記載的地位：
[root@www backup]# cat hellodb_2013-09-08.sql | less
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=15684; 記載了二進制日記的地位
2.6 在回到辦事器端：
mysql> show master status; 顯示此時的二進制日記的地位
從備份文件裡邊記載的地位到我們此時的地位，即為增量的部門
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 15982 | | |
+------------------+----------+--------------+------------------+
2.7做增量備份
[root@www backup]# mysqlbinlog --start-position=15694 --stop-position=15982
/mydata/data/mysql-bin.000013 > /backup/hellodb_`date +$F_%H`.sql
2.8再回到辦事器
mysql> insert into tb1 values (4),(5); 在拔出一些數值
mysql> drop database hellodb; 刪除hellodb庫
2.9導出此次得二進制日記：
[root@www backup]# mysqlbinlog --start-position=15982 /mydata/data/mysql-bin.000013 檢查刪除操作時二進制日記的地位
[root@www backup]# mysqlbinlog --start-position=15982 --stop-position=16176 /mydata/data/mysql-bin.000013 > /tmp/hellodb.sql //導出二進制日記
2.10先讓mysql離線
回到辦事器端：
mysql> set sql_log_bin=0; 封閉二進制日記
mysql> flush logs; 轉動下日記
2.11模仿數據庫破壞
mysql> drop database hellodb;
2.12開端恢單數據：
[root@www ]# mysql < /backup/hellodb_2013-09-08.sql //導入完整備份文件
[root@www ]# mysql < /backup/hellodb_2013-09-08_05.sql //導入增量備份文件
[root@www ]# mysql< hellodb.sql //導入二進制文件
驗證完成，顯示成果為我們料想的那樣
注：
1、真正在臨盆情況中，我們應當導出的是全部mysql辦事器中的數據，而不是單個庫，是以應當應用--all-databases
2、在導出二進制日記的時刻，可以直接復制文件便可，然則要留意的是，備份之前轉動下日記。
3、應用lvm快照完成簡直熱備的數據備份與恢復
3.1戰略：
完整備份+二進制日記；
3.2預備：
注：事務日記必需跟數據文件在統一個LV上；
3.3創立lvm Lvm的創立這裡就不多說了，想懂得話點擊http://www.jb51.net/LINUXjishu/105937.html
3.4 修正mysql主設置裝備擺設文件寄存目次內的文件的權限與屬主屬組，並初始化mysql
[root@www ~]# mkdir /mydata/data //創立數據目次
[root@www ~]# chown mysql:mysql /mydata/data //改屬組屬主
[root@www ~]#
[root@www ~]# cd /usr/local/mysql/ //必需站在此目次下
[root@www mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data //初始化mysql
3.5修正設置裝備擺設文件：
vim /etc/my.cof
datadir=/mydata/data 添加數據目次
sync_binlog = 1 開啟此功效
3.6 啟動辦事
[root@www mysql]# service mysqld start
mysql> set session sql_log_bin=0; 封閉二進制日記
mysql> source /backup/all_db_2013-09-08.sql 讀取備份文件
3.7回到mysql辦事器：
mysql> FLUSH TABLES WITH READ LOCK; 要求讀鎖
注：不要加入，另起一個終端：
mysql> SHOW MASTER STATUS; 檢查二進制文件的地位
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> FLUSH LOGS; 建議轉動下日記。如許備份日記的時刻就會很便利了
3.8導出二進制文件，創立個目次零丁寄存
[root@www ~]# mkdir /backup/limian
[root@www ~]# mysql -e 'show master status;' > /backup/limian/binlog.txt
[root@www ~]#
3.9為數據地點的卷創立快照：
[root@www ~]# lvcreate -L 100M -s -p r -n mysql_snap /dev/myvg/mydata
回到辦事器端，釋放讀鎖
mysql> UNLOCK TABLES;
[root@www ~]# mount /dev/myvg/mysql_snap /mnt/data
[root@www data]# cp * /backup/limian/
[root@www data]#lvremove /dev/myvg/mylv_snap
3.10更新數據庫的數據，並刪除數據目次先的數據文件，模仿數據庫破壞
mysql> create table limiantb (id int,name CHAR(10));
mysql> insert into limiantb values (1,'tom');
[root@www data]# mysqlbinlog --start-position=187 mysql-bin.000003 > /backup/limian/binlog.sql
[root@www backup]# cd /mydata/data/
[root@www data]# rm -rf *
[root@www ~]# cp -a /backup/limian/* /mydata/data/
[root@www data]# chown mysql:mysql *
3.11測試
啟動辦事
[root@www data]# service mysqld start
[root@www data]# mysql 上岸測試
mysql> SHOW DATABASES;
mysql> SET sql_log_bin=0
mysql> source/backup/limian/binlog.sql; #二進制恢復
mysql> SHOW TABLES; #檢查恢復成果
mysql> SET sql_log_bin=1; #開啟二進制日記
注：此方法完成了接近於熱備的方法備份數據文件，並且數據文件放在lvm中可以依據數據的年夜小靈巧轉變lvm的年夜小，備份的方法也很簡略。
4、基於Xtrabackup做備份恢復
官方站點：www.percona.com
優勢：
1、疾速靠得住的停止完整備份
2、在備份的進程中不會影響到事務
3、支撐數據流、收集傳輸、緊縮，所以它可以有用的勤儉磁盤資本和收集帶寬。
4、可以主動備份校驗數據的可用性。
裝置Xtrabackup
[root@www ~]# rpm -ivh percona-xtrabackup-2.1.4-656.rhel6.i686.rpm
其最新版的軟件可從 http://www.percona.com/software/percona-xtrabackup/ 取得
留意：在備份數據庫的時刻，我們應當具有權限，但須要留意的是應當給備份數據庫時的用戶最小的權限，以包管平安性，
4.1條件：
應當肯定采取的是單表一個表空間，不然不支撐單表的備份與恢復。
在設置裝備擺設文件裡邊的mysqld段加上
innodb_file_per_table = 1
4.2備份戰略
完整備份+增量備份+二進制日記
4.3預備個目次用於寄存備份數據
[root@www ~]# makdir /innobackup
4.4做完整備份：
[root@www ~]# innobackupex --user=root --password=mypass /innobackup/
注：
1、只需在最初一行顯示 innobackupex: completed OK!，就解釋你的備份是准確的。
2、別的要留意的是每次備份以後，會主動在數據目次下創立一個以以後時光點定名的目次用於寄存備份的數據，那我們去看看都有甚麼
[root@www 2013-09-12_11-03-04]# ls
backup-my.cnf ibdata1 performance_schema xtrabackup_binary xtrabackup_checkpoints
hellodb mysql test xtrabackup_binlog_info xtrabackup_logfile
[root@www 2013-09-12_11-03-04]#
xtrabackup_checkpoints ：備份類型、備份狀況和LSN(日記序列號)規模信息；
xtrabackup_binlog_info ：mysql辦事器以後正在應用的二進制日記文件及至備份這一刻為止二進制日記事宜的地位。
xtrabackup_logfile ：非文本文件，xtrabackup本身的日記文件
xtrabackup_binlog_pos_innodb ：二進制日記文件及用於InnoDB或XtraDB表的二進制日記文件確當前position。
backup-my.cnf ：備份時數據文件中關於mysqld的設置裝備擺設
4.5回到mysql辦事器端對數據停止更新操作
mysql> use hellodb;
mysql> delete from students where StuID>=24;
4.6增量備份
innobackupex --user=root --password=mypass --incremental /innobackup/--incremental-basedir=/innobackup/2013-09-12_11-03-04/
--incremental 指定備份類型
--incremental-basedir= 指定此次增量備份是基於哪一次備份的，這裡是完整備份文件,如許可以把增量備份的數據歸並到完整備份中去
4.7第二次增量
先去修正數據
mysql> insert into students (Name,Age,Gender,ClassID,TeacherID) values ('tom',33,'M',2,4);
innobackupex --user=root --password=mypass --incremental /innobackup/ --incremental-basedir=/innobackup/2013-09-12_11-37-01/
這裡只需要把最初的目次改成第一次增量備份的數據目次便可
4.8最初一次對數據更改然則沒做增量備份
mysql> delete from coc where id=14;
4.9把二進制日記文件備份出來，(由於最初一次修正，沒做增量備份，要依附二進制日記做時光點恢復)
[root@www data]# cp mysql-bin.000003 /tmp/
4.10模仿數據庫瓦解
[root@www data]# service mysqld stop
[root@www data]# rm -rf *
恢復前預備
4.11對完整備份做數據同步
[root@www ~]# innobackupex --apply-log --redo-only /innobackup/2013-09-12_11-03-04/
4.12對第一次增量做數據同步
innobackupex --apply-log --redo-only /innobackup/2013-09-12_11-03-04/ --incremental-basedir=/innobackup/2013-09-12_11-37-01/
4.13對第二次增量做數據同步
innobackupex --apply-log --redo-only /innobackup/2013-09-12_11-03-04/ --incremental-basedir=/innobackup/2013-09-12_11-45-53/
--apply-log 的意義在於把備份時沒commit的事務撤消，曾經commit的但還在事務日記中的運用到數據庫
注：
關於xtrabackup來說，它是基於事務日記和數據文件備份的，備份的數據中能夠會包括還沒有提交的事務或曾經提交但還沒有同步至數據庫文件中的事務，還應當對其做預處置，把已提交的事務同步到數據文件，未提交的事務要回滾。是以其備份的數據庫，不克不及立刻拿來恢復。
預處置的進程：
起首對完整備份文件只把已提交的事務同步至數據文件，要留意的是有增量的時刻，不克不及對事務做數據回滾，否則你的增量備份就沒有用果了。
然後把第一次的增量備份歸並到完整備份文件內，
以此類推，把後幾回的增量都歸並到前一次歸並以後的文件中，如許的話，我們只需拿著完整備份+二進制日記，便可以做時光點恢復。
4.14數據恢復
[root@www ~]# service mysqld stop
[root@www data]# rm -rf * 模仿數據庫瓦解
[root@www ~]# innobackupex --copy-back /innobackup/2013-09-12_11-03-04/
--copy-back數據庫恢復，前面跟上備份目次的地位
4.15檢測：
[root@www ~]# cd /mydata/data/
[root@www data]# chown mysql:mysql *
[root@www data]#service mysqld start
檢測成果數據正常。
本文出自 “遺掉ぜ的ァ美妙~” 博客
