1. 首頁 > 生活 > >

mysql數據庫三種備份方式 mysql數據備份通常有哪幾種方式

小知識



文章插圖
mysql數據庫三種備份方式 mysql數據備份通常有哪幾種方式

文章插圖
一 背景
在我們運維數據庫的過程中.為了保證數據的安全性及PTIR的恢復.都會對數據庫進行備份.mysql中我們一般使用的備份方式就兩種.一種是邏輯備份.另一種是物理備份.接下來我們一起了解一下兩種備份方式的實現及原理.
二 mysql邏輯備份
mysql中我們經常使用的邏輯備份方式是mysql官方的mysqldump.當然也有開源的mydumper 但是不建議使用.為了數據的安全性.接下來我們了解一下mysqldump的使用及原理
2.1 mysqldump介紹:
基于SQL（create database ,create table , insert into）語句的備份 。
使用場景： 100G以內，比較常用的就是邏輯備份 。
針對InnoDB表可以實現非鎖定備份 。原理上是通過MVCC中的快照技術進行備份 。
針對非InnoDB表，是啟用了鎖表備份，FTWRL（global read lock） 。
優點： 自帶工具，不需要單獨安裝；文本形式存儲，便于查看處理；壓縮比較高，節省空間 。
缺點： 備份時間較長 。恢復時間更長（4-6倍） 。
2.2 mysqldump的執行原理
首先看一下我們執行mysqldump發生了什么2021-07-20T02:48:26.478282Z57 [email protected] onusing Socket2021-07-20T02:48:26.478440Z57 Query/*!40100 SET @@SQL_MODE='' */2021-07-20T02:48:26.478531Z57 Query/*!40103 SET TIME_ZONE='+00:00' */2021-07-20T02:48:26.478601Z57 Query/*!80000 SET SESSION information_schema_stats_expiry=0 */2021-07-20T02:48:26.478654Z57 QuerySET SESSION NET_READ_TIMEOUT= 86400, SESSION NET_WRITE_TIMEOUT= 864002021-07-20T02:48:26.478719Z57 QueryFLUSH /*!40101 LOCAL */ TABLES2021-07-20T02:48:26.480218Z57 QueryFLUSH TABLES WITH READ LOCK2021-07-20T02:48:26.480292Z57 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2021-07-20T02:48:26.480341Z57 QuerySTART TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */2021-07-20T02:48:26.480422Z57 QuerySHOW VARIABLES LIKE 'gtid\_mode'2021-07-20T02:48:26.482852Z57 QuerySHOW MASTER STATUS2021-07-20T02:48:26.482920Z57 QueryUNLOCK TABLES可以看到mysqldump首先執行了FTWL全局只讀鎖.然后獲取了數據庫的一致性快照.開始備份2.3 mysqldump使用方法介紹
大家可以通過命令查看幫助:如下
[[email protected] ~]# mysqldump --help基本連接參數:
-u備份用戶名-p用戶密碼-Ssocket位置-hIP地址-Pmysql端口下面重點參數介紹一下.
–tab 將表數據和表結構分別導出.表數據為csv格式
mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock --single-transaction--tab="/tmp" world將world數據庫下的所有表結構及表數據備份到/tmp目錄下且表數據為csv格式查看:[[email protected] tmp]# ll總用量 4760-rw-r--r-- 1 rootroot1637 7月20 13:51 city.sql-rw-r----- 1 mysql mysql 143565 7月20 13:51 city.txt-rw-r--r-- 1 rootroot1666 7月20 13:51 countrylanguage.sql-rw-r----- 1 mysql mysql18234 7月20 13:51 countrylanguage.txt-rw-r--r-- 1 rootroot2036 7月20 13:51 country.sql-rw-r----- 1 mysql mysql31755 7月20 13:51 country.txt-rw-r--r-- 1 rootroot1763 7月20 13:51 employees.sql-rw-r----- 1 mysql mysql335 7月20 13:51 employees.txt其中sql為表結構語句.txt為表數據–no-data, -d 只導出表結構.不導出表數據
[[email protected] tmp]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock --single-transaction--no-data world >world.sql[[email protected] tmp]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock --single-transaction-d world >world.sql只導出world庫下所有表的表結構只導出表數據.不導出表結構:
[[email protected] ~]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock -t world city --single-transaction>all.sql–master-data=http://www.mnbkw.com/jxjc/187783/2 備份時自動生成，當前的binlog位置信息.自動開啟鎖表備份功能，如果開了–single-transaction，可以減少global read lock.
–single-transaction 功能：InnoDB “熱備” 。對于InnoDB表不鎖表，開啟一致性快照備份.使用這個參數會在備份時調整隔離級別為RR同時開啟快照備份
2021-07-20T02:48:26.478282Z57 [email protected] onusing Socket2021-07-20T02:48:26.478440Z57 Query/*!40100 SET @@SQL_MODE='' */2021-07-20T02:48:26.478531Z57 Query/*!40103 SET TIME_ZONE='+00:00' */2021-07-20T02:48:26.478601Z57 Query/*!80000 SET SESSION information_schema_stats_expiry=0 */2021-07-20T02:48:26.478654Z57 QuerySET SESSION NET_READ_TIMEOUT= 86400, SESSION NET_WRITE_TIMEOUT= 864002021-07-20T02:48:26.478719Z57 QueryFLUSH /*!40101 LOCAL */ TABLES2021-07-20T02:48:26.480218Z57 QueryFLUSH TABLES WITH READ LOCK2021-07-20T02:48:26.480292Z57 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2021-07-20T02:48:26.480341Z57 QuerySTART TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */–flush-logs 在備份開始時刷新一個新的二進制日志文件
特殊對象備份-R -E –triggers 備份存儲過程,函數.事件
–max-allowed-packet=128M 默認是24M.最大可以設置1G
–where 導出指定條件的數據
[[email protected] ~]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock scsales_prd T_HM_ROOMNO_VIEW_LOG_bak1 --where="pkid<10010">all.sql單庫備份:
[[email protected] ~]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock world --single-transaction>all.sql單表備份:
[[email protected] ~]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock world city --single-transaction>all.sql【mysql數據庫三種備份方式 mysql數據備份通常有哪幾種方式】最后提供一個生產經常使用的mysqldump完整版
mysqldump -uroot -p123 -A --master-data=http://www.mnbkw.com/jxjc/187783/2 --single-transaction -R -E --triggers --max-allowed-packet=128M --flush-logs>/opt/full.sql以上就是對mysql的邏輯備份方式mysqldump的總結.
三 物理備份方式介紹
說到物理備份方式.在MySQL8.0之前我們可以選擇的物理備份工具都是PXB 從MySQL8017版本之后我們多了一種選擇.那就是MySQL自帶的clone plguin
3.1 PXB介紹
PXB是percona開源出來的一個物理備份工具.可以實現innodb的熱備.實現原理是利用了mysql的crash recovery原理.
首先介紹xtrabackup的實現原理:
1.執行備份命令之后pxb先獲取到當前的lsn.然后開始拷貝redo文件.同時開啟一個后臺線程監控redo的實時變化同步到拷貝目錄.2.開始先拷貝innodb表數據.然后執行FLUSH TABLES WITH READ LOCK 和LOCK TABLES FOR BACKUP拷貝非innodb表數據.當這些拷貝完成之后.再拷貝其他數據文件3.然后執行LOCK BINLOG FOR BACKUP開始獲取二進制日志的位置點及Exec_Gtid_Set4.此時xtrabackup完成對redo的拷貝并釋放二進制日志的鎖使用xtrabackup備份數據庫:
[email protected] ~]# xtrabackup --backup --user=root --password=123 --target-dir=/data/backups/xtrabackup: recognized server arguments: --datadir=/data/3306/data --server-id=7 --log_bin=/data/3306/binlog/mysql-bin --open_files_limit=65535 --innodb_buffer_pool_size=4096M --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=32M --innodb_log_file_size=128M --innodb_log_files_in_group=3 --innodb_flush_log_at_trx_commit=1 使用xtrabackup進行增量備份
增量備份的原理:
xtrabackup會對比全備下的LSN號碼.然后進行增量拷貝
查看全備目錄下記錄的lsn.后邊我們查看增量的LSN
[[email protected] ~]# cat /data/backups/xtrabackup_checkpointsbackup_type = full-backupedfrom_lsn = 0to_lsn = 2628274last_lsn = 2628283compact = 0recover_binlog_info = 0模擬增量數據mysql> create database increment;Query OK, 1 row affected (0.00 sec)mysql> use increment;Database changedmysql> create table increment(id int,name varchar(30));Query OK, 0 rows affected (0.01 sec)mysql> insert into increment values(1,'lzm');Query OK, 1 row affected (0.01 sec)mysql> 開始增量備份:
[[email protected] ~]# xtrabackup --backup --user=root --password=123 --target-dir=/data/backups/inc1 \> --incremental-basedir=/data/backups查看增量目錄下的LSN號碼對比全量目錄下的LSN號碼
[[email protected] ~]# cat /data/backups/inc1/xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 2628274to_lsn = 2633017last_lsn = 2633026compact = 0recover_binlog_info = 0[[email protected] ~]# 此時增量備份也已經完成.接下來我們進行數據恢復
首先對全備備份目錄操作:
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/然后應用增量日志
xtrabackup --prepare --apply-log-only --target-dir=/data/backups --incremental-dir=/data/backups/inc1這里注意.如果增量不止一個的話.后邊的增量和全備合并的時候不需要加--apply-log-only停止現在的數據庫并把數據目錄清除:
pkill mysqldrm -rf /data/3306/data/*將全備目錄下的文件復制到數據目錄:
xtrabackup --copy-back --target-dir=/data/backups/修改權限chown -R mysql:mysql /data/ 啟動mysql/etc/init.d/mysqld start連接進入數據庫查看數據是否恢復
mysql> show databases;+--------------------+| Database|+--------------------+| information_schema || inc1|| increment|| mysql|| performance_schema || sys|+--------------------+6 rows in set (0.00 sec)mysql> use increment;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+---------------------+| Tables_in_increment |+---------------------+| increment|+---------------------+1 row in set (0.00 sec)mysql> select * from increment;+------+------+| id| name |+------+------+|1 | lzm|+------+------+1 row in set (0.00 sec)可以看到我們的增量數據也已經恢復了
關于8017之后加入的clone plguin的備份方式請查看我之前的文章MySQL 8.0新特性之clone plugin
四 總結:
這篇文章為大家分享了MySQL的邏輯備份及物理備份方式.以及詳細的使用方法!