溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
備份類型：熱備份、溫備份和冷備份
熱備份：讀、寫不受影響；
溫備份：僅可以執行讀操作；
冷備份：離線備份，讀、寫操作均中止；
備份方式：物理備份和邏輯備份：
物理備份：復制數據文件，速度快，保存數據庫的所有數據結構，如果數據跟數據庫關聯性強，
導致物理備份可移植性能力不強；
邏輯備份：將數據導出至文本文件中，速度慢、丟失浮點數精度；方便使用文本處理工具
直接對其處理，可移植能力強；缺點：浮點數據可能會丟失精度；備份出的數據更占用
存儲空間；壓縮后可大大節省空間；不適合對大數據庫做完全備份。
備份策略：完全備份、增量備份和差異備份：
完全備份：備份全部數據；
增量備份：僅備份上次完全備份或者增量備份以后變化的數據；
差異備份：僅備份上次完全備份以后變化的數據；（差異備份比增量備份所需空間大）
數據庫需要備份的內容：數據、配置文件、二進制日志、事務日志
MySQL備份工具：
mysqldump：邏輯備份工具、MyISAM(溫備份)、InnoDB(熱備份)
mysqldump:(備份時需加以下選項)
--databases DB1,DB2,... #備份那幾個數據庫
--all-databases #備份所有數據庫
MyISAM:溫備份（需加選項如下）
--lock-all-tables #備份時鎖定所有表
--lock-tables #備份一張表時只鎖定該備份的表
InnoDB:熱備份（需加選項如下）
--single-transaction #執行熱備份時需加該項表示是執行熱備份
--flush-logs #備份時先滾動下日志文件
--events #備份事件
--routines #備份存儲事件和函數
--triggers #備份觸發器
--master-data={0|1|2} 備份后的文件記錄了日志位置（一般設置為2）
mysqlhotcopy：物理備份工具、溫備份
文件系統工具：
cp:冷備工具
lv:邏輯卷的快照功能，幾乎熱備：
mysql> FLUSH TABLES; 刷新表
mysql>LOCK tables 鎖表
創建快照：釋放鎖，而后復制數據
第三方備份工具：
ibbackup：商業工具
xtrabackup：開源工具
一、使用mysqldump命令備份（邏輯備份工具）
mysqldump命令將數據庫中的數據備份成一個文本文件。表的結構和表中的數據將存儲在生成的文本文件中。
mysqldump命令的工作原理很簡單。它先查出需要備份的表的結構，再在文本文件中生成一個CREATE語句。然后，將表中的所有記錄轉換成一條INSERT語句。然后通過這些語句，就能夠創建表并插入數據。
1、備份一個數據庫，備份前需先刷新表，并鎖表，否則表修改造成備份的不一致性
mysqldump基本語法：
mysqldump -u username -p dbname table1 table2 ...-> BackupName.sql
其中：
dbname參數表示數據庫的名稱；
table1和table2參數表示需要備份的表的名稱，為空則整個數據庫備份；
BackupName.sql參數表設計備份文件的名稱，文件名前面可以加上一個絕對路徑。通常將數據庫被分成一個后綴名為sql的文件；
選項：
--master-data={0|1|2}
0：不記錄二進制日志文件及其位置；
1：以CHANGE MASTER TO的方式記錄位置，可用于恢復后直接啟動從服務器；
2：以CHANGE MASTER TO的方式記錄位置，但默認為被注釋掉；
--lock-all-tables：鎖定所有表
--flush-logs：執行日志flush刷新
如果指定庫中的表類型均為InnoDB,可使用--single-transaction啟動熱備；
實例：使用root用戶備份test數據庫步驟：
第一步先刷新并鎖表：
mysql> FLUSH TABLES WITH READ LOCK; #刷新表，并以讀鎖表
Query OK, 0 rows affected (0.00 sec)
再通過mysqldump備份表：
[root@lamp ~]# mysqldump -uroot -p test > /root/stu.sql #備份test數據庫到/root目錄下
Enter password:
[root@lamp ~]# ls /root
anaconda-ks.cfg Music Public Templates stu.sql
備份完后再解鎖表：
mysql> UNLOCK TABLES; #解鎖表
Query OK, 0 rows affected (0.00 sec)
文件的開頭會記錄MySQL的版本、備份的主機名和數據庫名。
文件中以“--”開頭的都是SQL語言的注釋，以"/*!40101"等形式開頭的是與MySQL有關的注釋。40101是MySQL數據庫的版本號，如果MySQL的版本比1.11高，則/*!40101和*/之間的內容就被當做SQL命令來執行，如果比4.1.1低就會被當做注釋。
*************************************************************
****實例：模擬所有庫都壞掉，實現完全備份和及時點還原。
先完全備份所有數據庫：
第一步先刷新并鎖表：
mysql> FLUSH TABLES WITH READ LOCK; #刷新表，并以讀鎖表（此步驟也可以結合mysqldump一起使用，不用單獨操作）--flush-logs --lock-all-tables含義一樣。
Query OK, 0 rows affected (0.00 sec)
[root@lamp ~]# mysqldump -uroot -p --master-data=2 --flush-logs --all-databases --lock-all-tables > /root/alldatabases.sql #--master-data=2以CHANGE MASTER TO的方式記錄位置，但默認為被注釋掉，--flush-logs刷新日志文件，--all-databases備份所有庫，--lock-all-tables鎖定所有表，備份至/root目錄下。（完全備份所有數據庫）
Enter password:
[root@lamp ~]# less /root/alldatabases.sql #查看完全備份中當前的二進制日志為000020
-- MySQL dump 10.13 Distrib 5.5.28, for Linux (x86_64)
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.5.28-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000020', MASTER_LOG_POS=107;
-- Current Database: `hellodb`
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARAC
................
mysql> PURGE BINARY LOGS TO 'mysql-bin.000020'; #模擬刪除當前二進制之前的日志信息。(再刪除前最好把日志文件保存至其他地方，以備還原至之前狀態需要)
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW BINARY LOGS; #查看所有日志信息，只剩下000020
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000020 | 107 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> USE studb;
Database changed
mysql> SELECT * FROM tutors; #查詢表tutors的信息
+-----+------------------+--------+-----+
| TID | Tname | Gender | Age |
+-----+------------------+--------+-----+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | HuangRong | F | 46 |
| 4 | HuYidao | M | 65 |
| 5 | XiaoLongnv | F | 28 |
| 6 | HuFei | M | 45 |
| 7 | GuoXiang | F | 32 |
+-----+------------------+--------+-----+
mysql> DELETE FROM tutors WHERE Age>80;#刪除表tutors年齡大于80的行
Query OK, 1 rows affected (0.01 sec)
mysql> SELECT * FROM tutors;#查詢表tutors的信息
+-----+------------------+--------+-----+
| TID | Tname | Gender | Age |
+-----+------------------+--------+-----+
| 2 | HuangYaoshi | M | 63 |
| 3 | HuangRong | F | 46 |
| 4 | HuYidao | M | 65 |
| 5 | XiaoLongnv | F | 28 |
| 6 | HuFei | M | 45 |
| 7 | GuoXiang | F | 32 |
+-----+------------------+--------+-----+
mysql> \q
[root@lamp ~]# mysql -uroot -p #登錄mysql
Enter password:
mysql> FLUSH LOGS; #刷新滾動日志
Query OK, 0 rows affected (0.01 sec)
mysql> \q
[root@lamp ~]# cd /mydata/data/#進入到mysql數據文件夾
[root@lamp data]# ls
hellodb ib_logfile0 lamp.err mydb mysql-bin.000020 mysql-bin.index stu
ibdata1 ib_logfile1 lamp.pid mysql mysql-bin.000021 performance_schema test
[root@lamp data]# mysqlbinlog mysql-bin.000020 > /root/mon-incremental.sql #把mysql的二進制日志文件導出成sql格式數據，（并命名為第一次增量備份）
[root@lamp data]# mysql -uroot -p #登錄mysql
Enter password:
mysql> USE studb;
Database changed
mysql> INSERT INTO tutors (Tname) Values ('stu123');#往表tutors插入一行數據
Query OK, 1 rows affected (0.01 sec)
mysql> \q
[root@lamp data]# ls
hellodb ib_logfile0 lamp.err mydb mysql-bin.000020 mysql-bin.index
ibdata1 ib_logfile1 lamp.pid mysql mysql-bin.000021 performance_schema
[root@lamp data]# cp mysql-bin.000021 /root #復制最后一次操作的二進制日志文件至數據目錄以外的目錄/root
[root@lamp data]# rm -rf ./* #刪除數據庫的數據目錄中的所有數據（模擬數據庫的所有庫數據丟失）
[root@lamp data]# ls #查看當前目錄，數據已經被清空
[root@lamp data]# service mysqld stop #由于數據文件都被清空，故此時mysql無法停止，也無法啟動
MySQL server PID file could not be found! [FAILED]
[root@lamp data]# killall mysqld #此時只能強制終止mysqld進程
[root@lamp ~]# cd /usr/local/mysql/ #進入mysql的安裝目錄
[root@lamp mysql]# ls
bin data include lib mysql-test scripts sql-bench
COPYING docs INSTALL-BINARY man README share support-files
[root@lamp mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/ #對mysql數據庫進行初始化設置，并指定相關的選項，--datadir指定數據庫的數據存放目錄。
Installing MySQL system tables...
OK
Filling help tables...
OK
...........
cd . ; ./bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl
Please report any problems with the ./bin/mysqlbug script!
[root@lamp mysql]# service mysqld start #數據庫初始化成功后重新啟動數據庫，因為已經初始化，所以數據庫的密碼為空.
Starting MySQL... [ OK ]
[root@lamp mysql]# mysql < /root/alldatabases.sql #先恢復完全備份的內容
[root@lamp mysql]# mysql < /root/mon-incremental.sql #恢復第一次增量備份
[root@lamp mysql]# mysqlbinlog /root/mysql-bin.000021 > /root/temp.sql #把故障前的最后一次二進制日志導出成sql格式的數據
[root@lamp mysql]# mysql < /root/temp.sql #把二進制日志還原至數據庫
至此就手動進行了一次mysql所有庫的及時點備份還原恢復,但是此還原數據庫的方法會導致產生很多mysql的二進制日志文件。
正確的恢復方法是：
恢復數據前，臨時關閉mysql記錄二進制日志功能，待恢復后再開啟記錄二進制日志的功能；
mysql> SET sql_log_bin=0; #臨時關閉記錄二進制日志功能
Query OK, 0 rows affected (0.00 sec)
mysql> \. /root/alldatabases.sql #恢復完全備份的內容（\.表示讀取導入后面目錄的文件內容，
也可以替換為SOURCE命令）
mysql> \. /root/mon-incremental.sql #恢復第一次增量備份
mysql> SET sql_log_bin=1; #數據庫恢復完后再打開記錄二進制日志功能
************************************************************************
2、備份多個數據庫
語法：mysqldump -u username -p --all-databases > /root/back.sql ：備份所有庫
mysqldump -u username -p --databases dbname2 dbname2 > Backup.sql
加上了--databases選項，然后后面跟多個數據庫
mysqldump -u root -p --databases test mysql > /root/backup.sql
3、備份所有數據庫
mysqldump命令備份所有數據庫的語法如下：
mysqldump -u username -p -all-databases > /root/BackupName.sql
示例：
mysqldump -u -root -p -all-databases > /root/all.sql
二、直接復制整個數據庫目錄
MySQL有一種非常簡單的備份方法，就是將MySQL中的數據庫文件直接復制出來。這是最簡單，速度最快的方法。
不過在此之前，要先將服務器停止，這樣才可以保證在復制期間數據庫的數據不會發生變化。如果在復制數據庫的過程中還有數據寫入，就會造成數據不一致。這種情況在開發環境可以，但是在生產環境中很難允許備份服務器。
注意：這種方法不適用于InnoDB存儲引擎的表，而對于MyISAM存儲引擎的表很方便。同時，還原時MySQL的版本最好相同。
三、使用mysqlhotcopy工具快速備份（物理備份工具）
一看名字就知道是熱備份。因此,mysqlhotcopy支持不停止MySQL服務器備份。而且，mysqlhotcopy的備份方式比mysqldump快。mysqlhotcopy是一個perl腳本，主要在Linux系統下使用。其使用LOCK TABLES、FLUSH TABLES和cp來進行快速備份。
原理：先將需要備份的數據庫加上一個讀鎖，然后用FLUSH TABLES將內存中的數據寫回到硬盤上的數據庫，最后，把需要備份的數據庫文件復制到目標目錄。
命令格式如下：
[root@localhost ~]#
dbname：數據庫名稱；
backupDir：備份到哪個文件夾下；
常用選項：
--help：查看mysqlhotcopy幫助；
--allowold：如果備份目錄下存在相同的備份文件，將舊的備份文件加上_old；
--keepold：如果備份目錄下存在相同的備份文件，不刪除舊的備份文件，而是將舊的文件更名；
--flushlog：本次輩分之后，將對數據庫的更新記錄到日志中；
--noindices：只備份數據文件，不備份索引文件；
--user=用戶名：用來指定用戶名，可以用-u代替；
--password=密碼：用來指定密碼，可以用-p代替。使用-p時，密碼與-p之間沒有空格；
--port=端口號：用來指定訪問端口，可以用-P代替；
--socket=socket文件：用來指定socket文件，可以用-S代替；
mysqlhotcopy并非mysql自帶，需要安裝Perl的數據庫接口包；下載地址為:http://dev.mysql.com/downloads/dbi.html
目前，該工具也僅僅能夠備份MyISAM類型的表。
1、還原使用mysqldump命令備份的數據庫的語法如下：
mysql -u root -p [dbname] < backup.sql
示例：
mysql -u root -p < C:\backup.sql
2、還原直接復制目錄的備份
通過這種方式還原時，必須保證兩個MySQL數據庫的版本號是相同的。MyISAM類型的表有效，對于InnoDB類型的表不可用，InnoDB表的表空間不能直接復制。
自動備份腳本：
要求：
1，每天4點備份mysql數據；
2，為節省空間，刪除超過3個月的所有備份數據；
3，刪除超過7天的備份數據，保留3個月里的 10號 20號 30號的備份數據；
#創建shell文件
vim backup_mysql.sh
#!/bin/bash
mysqldump -uroot -p --master-data=2 --flush-logs --all-databases --lock-all-tables >
/root/dbdata/mysqlbak/`date +%Y%m%d`.sql
find /root/dbdata/mysqlbak/ -mtime +7 -name '*[1-9].sql' -exec rm -rf {} \;
find /root/dbdata/mysqlbak/ -mtime +90 -name '*.sql' -exec rm -rf {} \;
#創建定時任務
crontab –e
0 4 * * * /data/dbdata/backup_mysql.sh
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
