溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
下文內容主要給大家帶來mysql備份方式、策略、恢復等簡析，這里所講到的知識，與書籍略有不同，都是億速云專業技術人員在與用戶接觸過程中，總結出來的，具有一定的經驗分享價值，希望給廣大讀者帶來幫助。
rmysql的備份：
誤操作、mysql崩潰、******、軟件故障、硬件故障、升級數據庫，測試等都會造成mysql數據的損壞，這時如果有備份的話還好，沒備份的話就尷尬了。
備份類型：
完全備份：備份整個數據庫
部分備份：只備份數據庫中的幾張表或庫
增量備份：相對于上一次完全備份或增量備份，只備份變化的數據集 （可以是二進制日志）
差異備份：相對于上一次完全備份，僅備份變化的數據集
備份方式：
物理備份：直接復制數據文件進行備份：速度快
缺點：當在備份的過程中有用戶通過應用程序訪問更新數據，這樣就無法備份當時的數據，如果數據庫表在文件系統備份過程中被修改，進入備份的表文件主語不一致的狀態，而對以后的恢復表將失去意義，備份過的數據集還原時得跟備份之前數據庫用到的存儲引擎一致。
邏輯備份：從數據庫中“導出”數據另存而進行的備份，速度比較慢
缺點：備份的速度比較慢。如果是數據量很多的時候。就很耗時間。如果數據庫云服務器處在提供給用戶服務狀態，在這段長時間操作過程中，意味著要鎖定表(一般是讀鎖定，只能讀不能寫入數據)。那么服務就會影響的，備份過的數據集還原時無需擔心存儲引擎的問題。
備份策略:
冷備份： 備份過程中讀寫操作均不可執行，好處是能保證數據的完整性，不會出現事務未提交的請求，壞處是需要mysql停止工作
熱備份： 備份過程中讀寫操作均可執行，好處是不需要停止mysql
溫備份： 備份過程中讀操作可執行，寫操作不可執行。
備份時需要考慮的問題：
備份時鎖表需要多久、備份需要多長、備份時產生多大的負載、恢復時需要多長時間
備份的方案：
對于數據集是完全備份加增量備份，還是完全備份加差異備份
備份的方式：物理備份還是邏輯備份
備份時的策略：選擇冷備份、溫備份、還是熱備份
備份的工具：
mysqldump：mysql官方自己提供的工具，是一款邏輯備份工具，適用于所有存儲引擎，對MyISAM引擎支持溫備，不支持熱備，支持完全備份，部分備份，對InnoDB存儲引擎是支持熱備的
cp，tar等復制歸檔工具：物理備份工具，對所有引擎都支持備份
lvm2的快照：幾乎熱備，需要記住與文件系統管理工具，cp、mv等
mysqlhotcopy：冷備工具
xtarbackup：percona提供的快速備份工具，可以熱備、溫備
一、mysqldump實現數據備份和還原
mysqldump是客戶端命令，通過mysql協議來連接msql數據庫實現備份， 是采用SQL級別的備份機制，它將數據表導成 SQL 腳本文件
mysqldump [option] [db_name [tbl_name..]] ]# mysqldump [options] db_name [tbl_name ...] //備份單個表，還原時需要手動創建數據庫 ]# mysqldump [options] --databases db_name . //備份指定數據庫，還原無需創建數據庫 ]# mysqldump [options] --all-databases //備份所有數據庫，還原無需創建數據庫 [options] --event -E：備份指定數據相關的所有事件 --default-character-set=charset：指定導出數據時采用何種字符集，如果數據表不是采用默認的 latin1 字符集的話，那么導出時必須指定該選項，否則再次導入數據后將產生亂碼問題 -R，--routines：備份指定數據庫相關的所有存儲過程和存儲函數 --triggers：備份表相關的觸發器 --skip-triggers：跳過備份觸發器 --lock-all-tables：鎖定所有庫的所有表 --lock-tables：鎖定指定數據庫的所有表 --no-create-info，-t：只導出數據，而不添加 CREATE TABLE 語句 --no-data，-d：不導出任何數據，只導出數據庫表結構。 --single-transaction：該選項在導出數據之前提交一個 BEGIN SQL語句，BEGIN 不會阻塞任何應用程序且能保證導出時數據庫的一致性狀態。它只適用于事務表，例如 InnoDB 和 BDB。 注意：本選項和 --lock-tables 選項是互斥的，因為 LOCK TABLES 會使任何掛起的事務隱含提交 --master-data[=value]：記錄二進制日志和文件的位置 0：不啟用 1、記錄為CHANGE MASTER TO語句，此語句不被注釋 2、記錄為注釋的CHANGE MASTER TO語句 --flush-logs: 對二進制日志進行日志滾動
示例：完全備份數據庫，然后恢復
]# mysqldump -uadmin -padmin --databases hellodb -R --triggers --master-data=2 >/backup/mysqlback.sql ]# service mariadb stop Redirecting to /bin/systemctl stop mariadb.service ]# rm -rf /var/lib/mysql/* //刪除mysql數據 ]# service mariadb start Redirecting to /bin/systemctl start mariadb.service ]# mysql </backup/mysqlback.sql //直接導入還原 MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | //直接還原回來 | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) 還原時還有一種方式： MariaDB [(none)]> source /backup/mysqlback.sql //兩種還原方式都是mysql客戶端提供的
備份完后，在下次備份之前數據庫崩潰需要還原，那么中間產生數據需要通過二進制日志根據時間點還原。
二進制日志： 記錄導致數據改變或潛在導致數據改變的SQL語句。
]# mysqldump -uroot --databases hellodb -R --triggers --master-data=2 >/backup/mysqlbackv2.sql
MariaDB [hellodb]> insert into students (Name,Age) values ("Hello",20);
Query OK, 1 row affected (0.01 sec)
備份完后又做了寫操作，這時數據發生了改變，需要通過二進制日志進行重放
]# less /backup/mysqlbackv2.sql
-- Host: localhost Database: hellodb
-- ------------------------------------------------------
-- Server version 5.5.44-MariaDB-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='master.000003', MASTER_LOG_POS=7925; //顯示備份時二進制的文件及二進制事件開始的位置
]# mysqlbinlog --start-position=245 /root/master.000003 >/backup/mysqlbin.sql
//把記錄的之后事件的二進制日志導成sql語句
]# mysql </backup/mysqlbackv2.sql //先完全備份恢復
]# mysql </backup/mysqlbin.sql //在根據二進制日志恢復
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb | //數據庫沒問題
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students;
| 26 | Hello | 20 | F | NULL | NULL | //新添加的數據也恢復了
+-------+---------------+-----+--------+---------+-----------+二、lvm2實現數據的備份與恢復
創建lvm邏輯卷，把mysql數據存放目錄掛載到邏輯卷上
]# pvcreate /dev/sdb1 Physical volume "/dev/sdb1" successfully created ]# vgcreate myvg /dev/sdb1 Volume group "myvg" successfully created ]# lvcreate -L 1G -n mysql_lvm /dev/m mapper/ mcelog mem midi mqueue/ ]# lvcreate -L 1G -n mysql_lvm /dev/m mapper/ mcelog mem midi mqueue/ ]# lvcreate -L 1G -n mysql_lvm /dev/myvg Logical volume "mysql_lvm" created. ]# mke2fs -t ext4 /dev/myvg/mysql_lvm mke2fs 1.42.9 (28-Dec-2013) Filesystem label= OS type: Linux Block size=4096 (log=2) Fragment size=4096 (log=2) Stride=0 blocks, Stripe width=0 blocks 65536 inodes, 262144 blocks 13107 blocks (5.00%) reserved for the super user First data block=0 Maximum filesystem blocks=268435456 8 block groups 32768 blocks per group, 32768 fragments per group 8192 inodes per group Superblock backups stored on blocks: 32768, 98304, 163840, 229376 Allocating group tables: done Writing inode tables: done Creating journal (8192 blocks): done Writing superblocks and filesystem accounting information: done ]# mkdir /data/mysql ]# mount /dev/myvg/mysql_lvm /data/mysql/ ]# chown -R mysql.mysql /data/mysql/ ]# service mariadb start //啟動mysql報錯 Redirecting to /bin/systemctl start mariadb.service Job for mariadb.service failed. See 'systemctl status mariadb.service' and 'journalctl -xn' for details. [root@localhost /]# tail -f /var/log/mariadb/ tail: error reading ‘/var/log/mariadb/’: Is a directory tail: /var/log/mariadb/: cannot follow end of this type of file; giving up on this name tail: no files remaining [root@localhost /]# tail -f /var/log/mariadb/mariadb.log 160609 18:14:55 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended 160609 18:18:21 mysqld_safe Starting mysqld daemon with databases from /data/mysql 160609 18:18:21 [Note] /usr/libexec/mysqld (mysqld 5.5.44-MariaDB-log) starting as process 5585 ... 160609 18:18:21 [Warning] Can't create test file /data/mysql/localhost.lower-test 160609 18:18:21 [ERROR] mysqld: File './master-bin.index' not found (Errcode: 13) 發生這個原因可能是/data/mysql這個目錄的問題，但我上面已經設置好了還報錯 ]# getenforce //selinux的問題，關閉就行 Enforcing ]# setenforce 0 ]# service mariadb start Redirecting to /bin/systemctl start mariadb.service
備份數據庫：
MariaDB [hellodb]> flush tables with read lock; //先鎖表 MariaDB [hellodb]> flsuh logs; //滾動二進制日志 ]# mysql -e "show master status;" >/root/mysqlbin.date+ "%F" //記錄二進制日志文件及位置 ]# lvcreate -L 500M -s -p r -n mysql_lvm_snap /dev/myvg/mysql_lvm //快照 Logical volume "mysql_lvm_snap" created. ]# mount /dev/myvg/mysql_lvm_snap /backup/ ]# cp -r * /data/mysqlback/ //數據備份成功
恢復數據：
]# cp -r /data/mysqlback/* /data/mysql/ ]# chown -R mysql.mysql ./* ]# service mariadb start MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | //數據沒問題 | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.01 sec) 然后根據二進制日志恢復完全備份之后丟失的數據就行
三、xtrabackup實現數據備份和恢復
Xtrabackup是由percona提供的mysql數據庫備份工具，據官方介紹，這也是世界上惟一一款開源的能夠對innodb和xtradb數據庫進行熱備的工具。特點：
(1)備份過程快速、可靠；
(2)備份過程不會打斷正在執行的事務；
(3)能夠基于壓縮等功能節約磁盤空間和流量；
(4)自動實現備份檢驗；
(5)還原速度快；
]# ls percona-xtrabackup-2.3.2-1.el7.x86_64.rpm ]# yum -y install *.rpm
xtrabackup完全備份：
]# innobackupex --user=root /backup/ //如果數據庫有密碼則加上--password 160609 19:08:43 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". ................. 160609 19:08:55 Executing UNLOCK TABLES 160609 19:08:55 All tables unlocked 160609 19:08:55 Backup created in directory '/data/mysql//2016-06-09_19-08-43' MySQL binlog position: filename 'master-bin.000005', position '245' 160609 19:08:55 [00] Writing backup-my.cnf 160609 19:08:55 [00] ...done 160609 19:08:56 [00] Writing xtrabackup_info 160609 19:08:56 [00] ...done xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied. 160609 19:08:56 completed OK!
數據還原：
還原準備： 在備份完成后，數據尚且不能用于恢復操作，因為備份的數據中可能會包含尚未提交的事務或已經提交但尚未同步至數據文件中的事務。因此，此時數據文件仍處理不一致狀態。“準備”的主要作用正是通過回滾未提交的事務及同步已經提交的事務至數據文件也使得數據文件處于一致性狀態。 ]# innobackupex --apply-log /backup/2016-06-09_19-16-30/ InnoDB: Setting log file ./ib_logfile101 size to 48 MB InnoDB: Setting log file ./ib_logfile1 size to 48 MB InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 //事務合并時innobackupex會把事務日志文件設置為48M，事務日志默認為5M，這樣會導致mariadb啟動不了 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1598486 160609 19:17:40 completed OK! 刪除數據集，恢復 ]# innobackupex --copy-back /backup/2016-06-09_19-16-30/ 160609 19:22:27 [01] ...done 160609 19:22:28 [01] Copying ./performance_schema/threads.frm to /data/mysql/performance_schema/threads.frm 160609 19:22:28 [01] ...done 160609 19:22:28 [01] Copying ./xtrabackup_info to /data/mysql/xtrabackup_info 160609 19:22:28 [01] ...done 160609 19:22:28 completed OK! ]# chown -R mysql.mysql ./* ]# service mariadb start MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
xtrabackup進行增量備份:
插入數據：
MariaDB [hellodb]> insert into students (Name,Age) values ("Hello",21);
Query OK, 1 row affected (0.01 sec)
]# innobackupex --incremental /backup --incremental-basedir=/backup/2016-06-09_19-16-30/
//basedir后面跟最近一次完全備份的目錄，如果上次備份的增量備份，則指向上一次增量備份注意：恢復時mysql是不能啟動的，增量備份僅能應用于InnoDB或XtraDB表，對于MyISAM表而言，執行增量備份時其實進行的是完全備份。
增量備份的整合事務時和完全備份有一些不同，需要注意：
1、需要在每個備份上（增量備份和完全備份），將已提交的事務進行“重放”，“重放”之后，所有的備份數據都合并到完全備份上
2、合并完后在基于所有的備份上將有些還未提交的事務進行“回滾”
為什么不能把增量備份上未提交的事務進行“回滾”？
因為這次增量備份上事務未提交就備份完了，在下次增量備份上事務就可能已經提交了，所有不能進行事務回滾，只有把所有的備份的事務都“重放”完后，在基于所有備份上把所有的未完成事務進行“回滾”。
]# ]# innobackupex --apply-log --redo-only /backup/2016-06-09_19-55-38 //先完全備份事務整合 ]# ]# innobackupex --apply-log --redo-only /backup/2016-06-09_19-55-38 --incremental=dir=/backup/2016-06-09_19-57-43/ //第一次增量備份，如果有多個，只把incremental后面的目錄改成第二次增量備份 ]# rm -rf /data/mysql/* ]# innobackupex --copy-back /backup/2016-06-09_19-55-38/ ]# service mariadb start MariaDB [hellodb]> select * from students; | 26 | Hello | 21 | F | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 26 rows in set (0.01 sec)
每個備份目錄下都有些xtarbackup文件：
]# cd /backup/2016-06-09_19-55-38/ [root@localhost 2016-06-09_19-55-38]# ls xtrabackup_binlog_info ：mysql服務器當前正在使用的二進制日志文件及至備份這一刻為止二 進制日志事件的位置 xtrabackup_checkpoints ：備份類型（如完全或增量）、備份狀態（如是否已經為prepared狀態）和LSN(日志序列號)范圍信息，增量備份就是通過查看LSN發生改變的內容去備份修改的數據 xtrabackup_logfile ：xtrabackup備份是日志 xtrabackup_binlog_pos_innodb ：二進制日志文件及用于InnoDB或XtraDB表的二進制日志文件的當前position。xtrabackup_info ：xtrabackup備份數據庫的各種信息
對于以上關于mysql備份方式、策略、恢復等簡析，如果大家還有更多需要了解的可以持續關注我們億速云的行業推新，如需獲取專業解答，可在官網聯系售前售后的，希望該文章可給大家帶來一定的知識更新。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
