日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

公告:魔扣目錄網(wǎng)為廣大站長提供免費(fèi)收錄網(wǎng)站服務(wù),提交前請(qǐng)做好本站友鏈:【 網(wǎng)站目錄:http://www.ylptlb.cn 】, 免友鏈快審服務(wù)(50元/站),

點(diǎn)擊這里在線咨詢客服
新站提交
  • 網(wǎng)站:51998
  • 待審:31
  • 小程序:12
  • 文章:1030137
  • 會(huì)員:747

前言

看完本文,刪庫跑路!? 再也不可能發(fā)生了!一定要養(yǎng)成風(fēng)險(xiǎn)操作備份數(shù)據(jù)的習(xí)慣,避免恢復(fù)數(shù)據(jù)浪費(fèi)時(shí)間。

本文內(nèi)容:

  • binlog 的作用
  • MySQLdump 和 mysqlbinlog 做數(shù)據(jù)備份和數(shù)據(jù)恢復(fù)。
  • XtraBackup 全量備份和增量備份

binlog

Mysql數(shù)據(jù)備份與恢復(fù)

binlog 作用及配置

Mysql 的 binlog (二進(jìn)制日志) 是 Server 層的,不管你的存儲(chǔ)引擎是什么都可以使用 binlog 。

binlog 記錄的是數(shù)據(jù)庫 DML 和 DDL 修改的數(shù)據(jù)內(nèi)容,也可以用于數(shù)據(jù)的備份與恢復(fù)。一般我們會(huì)用

binlog 也用于主從復(fù)制,從庫請(qǐng)求主庫的 binlog 寫入到自己的中繼日志,然后將中繼日志轉(zhuǎn)換為 sql ,然后將 sql 執(zhí)行在從庫執(zhí)行。

-- 查看是否開啟 binlog
SHOW VARIABLES LIKE '%log_bin%'

mysql> SHOW VARIABLES LIKE '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+

開啟二進(jìn)制日志配置

log_bin 配置是否啟用 binlogMysql 8.0 默認(rèn)開啟 binlog

log_bin_index 配置的是 binlog 日志文件的索引信息。這個(gè)配置最好配置了之后不要修改。

log_bin_basename 配置的是 binlog 日志的基礎(chǔ)路徑名稱。

server_id 這個(gè)也需要配置,在一個(gè)集群中這個(gè)數(shù)字不能重復(fù)。

sql_log_bin 配置當(dāng)前會(huì)話 DML 和 DDL 語句是否記錄。

[root@centos-7 mysql]# pwd
/var/lib/mysql
[root@centos-7 mysql]# ll | grep binlog
-rw-r-----. 1 mysql mysql    16162 11月 21 15:58 binlog.000013
-rw-r-----. 1 mysql mysql      179 11月 21 15:58 binlog.000014
-rw-r-----. 1 mysql mysql     3765 11月 22 14:42 binlog.000015
-rw-r-----. 1 mysql mysql     1700 11月 23 23:40 binlog.000016
-rw-r-----. 1 mysql mysql       64 11月 22 14:42 binlog.index
[root@centos-7 mysql]#

binlog 日志格式

-- 查看當(dāng)前 binlog 文件存儲(chǔ)什么數(shù)據(jù)
SHOW VARIABLES LIKE '%binlog_format%';

binlog 日志格式有以下三種

STATEMENT

記錄的是 sql 語句。

ROW

Mysql 8.0 默認(rèn)采用這個(gè)格式。記錄每行的修改。相較于 STATEMENT 它可能記錄的內(nèi)容會(huì)更多,但是主從復(fù)制時(shí)更安全。

比如全表更新 update test set a=1; STATEMENT 只會(huì)記錄這個(gè) sql ,而 ROW 會(huì)記錄所有數(shù)據(jù)的修改。

MIXED

當(dāng)需要時(shí),Mysql 將日志格式從 STATEMENT 切換為 ROW

比如說更新語句可能記錄為邏輯 sql (STATEMENT),而插入語句記錄為(ROW) 格式。

binlog 日志格式驗(yàn)證

創(chuàng)建一張表,插入 10 w 數(shù)據(jù)

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DROP PROCEDURE if EXISTS insertData;
CREATE PROCEDURE insertData ( )
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i < 10000 DO
        SET i = i + 1;
        INSERT INTO account ( username, age )VALUES( '測試', 12 );    
    END WHILE;
END;
CALL insertData ( );
  • binlog_format 在 ROW 模式下記錄的是每行數(shù)據(jù)的修改
mysql> SHOW BINLOG EVENTS IN 'binlog.000018' limit 10;
+---------------+-------+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos   | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+-------+----------------+-----------+-------------+--------------------------------------+
| binlog.000018 |     4 | Format_desc    |         1 |         125 | Server ver: 8.0.21, Binlog ver: 4    |
| binlog.000018 |   125 | Previous_gtids |         1 |         156 |                                      |
| binlog.000018 |   156 | Anonymous_Gtid |         1 |         236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000018 |   236 | Query          |         1 |         322 | BEGIN                                |
| binlog.000018 |   322 | Table_map      |         1 |         386 | table_id: 99 (ceshi2.account)        |
| binlog.000018 |   386 | Update_rows    |         1 |        8600 | table_id: 99                         |
| binlog.000018 |  8600 | Update_rows    |         1 |       16814 | table_id: 99                         |
| binlog.000018 | 16814 | Update_rows    |         1 |       25028 | table_id: 99                         |
| binlog.000018 | 25028 | Update_rows    |         1 |       33242 | table_id: 99                         |
| binlog.000018 | 33242 | Update_rows    |         1 |       41456 | table_id: 99                         |
+---------------+-------+----------------+-----------+-------------+--------------------------------------+
  • binlog_format 在 STATEMENT 模式下記錄的是 sql
flush logs;

update ceshi2.account set username='2';

mysql> SHOW BINLOG EVENTS IN 'binlog.000019' limit 10;
+---------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                   |
+---------------+-----+----------------+-----------+-------------+----------------------------------------+
| binlog.000019 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.21, Binlog ver: 4      |
| binlog.000019 | 125 | Previous_gtids |         1 |         156 |                                        |
| binlog.000019 | 156 | Anonymous_Gtid |         1 |         235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| binlog.000019 | 235 | Query          |         1 |         324 | BEGIN                                  |
| binlog.000019 | 324 | Query          |         1 |         446 | update ceshi2.account set username='2' |
| binlog.000019 | 446 | Xid            |         1 |         477 | COMMIT /* xid=300671 */                |
+---------------+-----+----------------+-----------+-------------+----------------------------------------+

binlog 操作

查看所有的 binlog

-- 查看鏈接的數(shù)據(jù)庫 binlog 文件信息
SHOW BINARY LOGS;
SHOW MASTER LOGS;

mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000013 |     16162 | No        |
| binlog.000014 |       179 | No        |
| binlog.000015 |      3765 | No        |
| binlog.000016 |      1700 | No        |
+---------------+-----------+-----------+

查看當(dāng)前正在寫入的 binlog

-- 查看當(dāng)前正在寫入的 binlog 文件信息
SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000016 |     1700 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

生成新的 binlog

-- 刷新產(chǎn)生新的日志文件
FLUSH LOGS;

-- 原來的日志文件是 binlog.000016
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000017 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

查看 binlog 中的操作

SHOW BINLOG EVENTS
   [IN 'log_name']
   [FROM pos]
   [LIMIT [offset,] row_count]

mysql> show binlog events limit 100,3;
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
| binlog.000013 | 9382 | Query          |         1 |        9539 | use `ceshi`; GRANT SELECT ON `ceshi`.`test2` TO 'db_dev'@'localhost' /* xid=1023 */ |
| binlog.000013 | 9539 | Anonymous_Gtid |         1 |        9616 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                |
| binlog.000013 | 9616 | Query          |         1 |        9711 | use `ceshi`; FLUSH PRIVILEGES                                                       |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------+

binlog 落盤時(shí)機(jī)

Mysql 中有很多 Buffer Pool (可以簡單理解為內(nèi)存),為了提高數(shù)據(jù)庫性能,一般提交事務(wù)之后,二進(jìn)制日志先寫入 Buffer Poll ,在寫入到二進(jìn)制文件中。

如果二進(jìn)制日志沒有落盤,那么二進(jìn)制日志有可能丟失,從庫進(jìn)行復(fù)制時(shí)會(huì)丟失數(shù)據(jù)。

參數(shù) sync_binlog 配置寫入 Buffer Poll 多少次的時(shí)候調(diào)用系統(tǒng)調(diào)用 fsync 將內(nèi)存中的二進(jìn)制日志數(shù)據(jù)落盤。

  • sync_binlog=1 表示提交事務(wù)的時(shí)候同步將二進(jìn)制日志數(shù)據(jù)落盤。配合 innodb_flush_log_at_trx_commit=1(控制 redo log 落盤) 數(shù)據(jù)安全。
  • sync_binlog=N 表示提交了 N 個(gè)二進(jìn)制數(shù)據(jù)時(shí)才將日志數(shù)據(jù)落盤。也有人將其設(shè)置為 2,提高并發(fā)性,系統(tǒng)崩潰時(shí)可能丟失二進(jìn)制日志數(shù)據(jù)。
  • sync_binlog=0 表示由操作系統(tǒng) IO 調(diào)度來決定日志什么時(shí)候落盤。一般沒人采用這個(gè)。

Mysql 備份和恢復(fù)

為了避免意外情況發(fā)生導(dǎo)致數(shù)據(jù)丟失,數(shù)據(jù)庫需要定時(shí)全備和增量備份。以便于可以將數(shù)據(jù)庫恢復(fù)到任意時(shí)間點(diǎn)的數(shù)據(jù)。

根據(jù)備份方法的不同可以劃分為:

  • 熱備(Hot Backup)
  • 冷備 (Clod Backup)

熱備是在數(shù)據(jù)庫正在運(yùn)行時(shí)直接備份,對(duì)業(yè)務(wù)的影響不大。

冷備需要停止 Mysql 進(jìn)行備份,速度較快。可以在從庫進(jìn)行冷備。

根據(jù)備份后的文件內(nèi)容可以劃分為:

  • 邏輯備份,數(shù)據(jù)庫執(zhí)行的 sql 內(nèi)容
  • 文件備份,備份數(shù)據(jù)庫的物理文件

一般我們會(huì)定時(shí)對(duì)數(shù)據(jù)執(zhí)行備份腳本,然后將備份的內(nèi)容壓縮發(fā)送到存儲(chǔ)文件的服務(wù)器,比如 OSS 。

備份與恢復(fù)使用到程序

  • mysqldump,對(duì)數(shù)據(jù)庫進(jìn)行不停機(jī)執(zhí)行邏輯備份及恢復(fù)
  • mysqlbinlog,操作 binlog 日志,使數(shù)據(jù)恢復(fù)到某個(gè)時(shí)間點(diǎn)的數(shù)據(jù)
  • xtrabackup,percona 開源工具,對(duì)數(shù)據(jù)庫不停機(jī)進(jìn)行文件備份

mysqldump 使用

備份某些數(shù)據(jù)庫

mysqldump --master-data --single-transaction --databases ceshi2 ceshi -h10.211.55.8 -uroot -pMysql@12345678  > backup.sql

備份所有數(shù)據(jù)庫

mysqldump --master-data --single-transaction --all-databases -h10.211.55.8 -uroot -pMysql@12345678  > backup.sql

參數(shù)說明

  • --single-transaction 用于全是 InnoDB 表的備份。備份開始執(zhí)行前 START TRANSACTION 會(huì)開啟事務(wù),由于 MVCC 的特性這種備份不會(huì)影響數(shù)據(jù)庫讀寫,而且還保證了備份期間數(shù)據(jù)的一致性

  • --master-data 為 1 時(shí)記錄 CHANGE MASTER 語句,可以在從庫中使用備份的文件,比如新增加一個(gè)從庫,就可以在從庫上執(zhí)行這個(gè)備份的數(shù)據(jù)。為 2 時(shí) 會(huì)注釋 CHANGE MASTER 。

  • --lock-tables 鎖住單個(gè)數(shù)據(jù)庫中所有表,只允許讀取數(shù)據(jù)。為了保證備份時(shí)數(shù)據(jù)的一致性。因?yàn)橹荒苕i住單個(gè)數(shù)據(jù)庫,如果有多個(gè)數(shù)據(jù)庫就不能保證數(shù)據(jù)的一致性了。當(dāng)數(shù)據(jù)庫采用的存儲(chǔ)引擎既有 InnoDB 和 MyISAM 時(shí)需要使用這個(gè)屬性

  • --lock-all-tables 鎖住備份所有數(shù)據(jù)庫的表,能保證多個(gè)數(shù)據(jù)庫數(shù)據(jù)的一致性。

  • --databases 可以指定備份哪些數(shù)據(jù)庫實(shí)例

  • --all-databases 備份連接中所有的數(shù)據(jù)庫實(shí)例。

  • --evnets 備份事件調(diào)度器

  • --routines 備份存儲(chǔ)過程和存儲(chǔ)函數(shù)

  • --triggers 備份觸發(fā)器

  • --flush-logs 導(dǎo)出之前刷新日志,因?yàn)橛械臄?shù)據(jù)在內(nèi)存中,可能還沒有寫入到二進(jìn)制日志中

mysqlbinlog 使用

mysqlbinlog 可以解析 binlog 生成 sql語句。

# 在本地生成 sql
mysqlbinlog --disable-log-bin /Users/zhangpanqin/Desktop/binlog.000019 > test.sql
mysqlbinlog --disable-log-bin /Users/zhangpanqin/Desktop/binlog.000019 > test.sql

# 根據(jù)日志的位置
mysqlbinlog binlog.000019 --disable-log-bin --start-position 775 > 775.sql
mysqlbinlog binlog.000019 --disable-log-bin --start-position 477 --stop-position 556 > 477-556.sql

# 根據(jù)時(shí)間
mysqlbinlog binlog.000019 --start-date='2017-12-19 10:10:00' --stop-date='2017-12-19 18:52:00' > aa.sql

# 鏈接遠(yuǎn)程使用
mysqlbinlog --disable-log-bin --read-from-remote-server  --host=10.211.55.8 --user=root --password=Mysql@12345678 binlog.000019 binlog.000020> remote_test.sql
  • --start-position 指定從哪個(gè)位置開始
  • --stop-position 指定從哪個(gè)位置開始
  • --start-datetime 指定開始時(shí)間
  • --stop-datetime 指定結(jié)束時(shí)間
  • --disable-log-bin 生成的 sql 語句中,添加 SET SQL_LOG_BIN=0 ,執(zhí)行轉(zhuǎn)換的 sql 時(shí),不會(huì)生成二進(jìn)制日志
  • --read-from-remote-server 從遠(yuǎn)程服務(wù)器讀取

數(shù)據(jù)恢復(fù)

一般我們會(huì)使用 mysqldump 進(jìn)行一個(gè)全量備份,在這個(gè)全量備份的基礎(chǔ)上,從 binlog 提取后續(xù) sql 進(jìn)行數(shù)據(jù)恢復(fù)。

模擬一個(gè)場景

1、比如我們?cè)谀硞€(gè) 2020-11-28 16:30:00 進(jìn)行了全量備份。

2、2020-11-28 16:35:00 刪除了 account 表中全部數(shù)據(jù)

3、刪除之后不知道,又插入了兩條數(shù)據(jù)

INSERT INTO `ceshi2`.`account`(`id`, `username`, `age`) VALUES (11111111, '刪除全庫之后插入', 11);
INSERT INTO `ceshi2`.`account`(`id`, `username`, `age`) VALUES (11111112, 'asdfasd', 12);

恢復(fù)數(shù)據(jù)的時(shí)候,為避免恢復(fù)操作寫入到二進(jìn)制日志中去,需要暫時(shí)關(guān)閉二進(jìn)制日志,恢復(fù)會(huì)話期間不寫入二進(jìn)制日志

SET SQL_LOG_BIN=0;
SHOW VARIABLES LIKE '%sql_log_bin%';

現(xiàn)在開始對(duì)數(shù)據(jù)庫進(jìn)行數(shù)據(jù)恢復(fù)

  • 開始恢復(fù)之前先 flush logs 刷新新的二進(jìn)制日志
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000020 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
  • 設(shè)置當(dāng)前會(huì)話不記錄二進(jìn)制日志,并恢復(fù)全備數(shù)據(jù)
(echo "SET SQL_LOG_BIN=0;";cat /Users/zhangpanqin/Desktop/backup.sql) | mysql -u root -h 10.211.55.8 -pMysql@12345678 -f
  • 查看 backup.sql 記錄的是什么時(shí)候備份的數(shù)據(jù)
/*CHANGE MASTER TO MASTER_LOG_FILE='binlog.000019', MASTER_LOG_POS=477;*/
  • 使用 mysqlbinlog 導(dǎo)出 binlog 從位置 477 開始的 sql
-- 笨的方法就是,查看刪除的 sql 語句
SHOW BINLOG EVENTS IN 'binlog.000019' FROM 477 LIMIT 0,10;

mysql> SHOW BINLOG EVENTS IN 'binlog.000019' FROM 477 LIMIT 0,10;
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                    |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------+
| binlog.000019 |  477 | Anonymous_Gtid |         1 |         556 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                    |
| binlog.000019 |  556 | Query          |         1 |         642 | BEGIN                                                                                                                   |
| binlog.000019 |  642 | Query          |         1 |         744 | use `ceshi2`; DELETE FROM `account`                                                                                     |
| binlog.000019 |  744 | Xid            |         1 |         775 | COMMIT /* xid=300922 */                                                                                                 |
| binlog.000019 |  775 | Anonymous_Gtid |         1 |         854 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                    |
| binlog.000019 |  854 | Query          |         1 |         940 | BEGIN                                                                                                                   |
| binlog.000019 |  940 | Query          |         1 |        1126 | use `ceshi2`; INSERT INTO `ceshi2`.`account`(`id`, `username`, `age`) VALUES (11111111, '刪除全庫之后插入', 11)         |
| binlog.000019 | 1126 | Xid            |         1 |        1157 | COMMIT /* xid=301033 */                                                                                                 |
| binlog.000019 | 1157 | Anonymous_Gtid |         1 |        1236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                    |
| binlog.000019 | 1236 | Query          |         1 |        1322 | BEGIN                                                                                                                   |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------+
 -- 導(dǎo)出 477-556 之間的 sql 
 mysqlbinlog binlog.000019 --start-position 477 --stop-position 556 > 477-556.sql
  -- 導(dǎo)出 從 775 開始的 sql 
 mysqlbinlog binlog.000019 --start-position 775> 775.sql

這里比較好的做法就是直接使用工具直接解析 sql

binlog2sql

https://github.com/danfengcao/binlog2sql
  • 執(zhí)行剩下的 sql
(echo "SET SQL_LOG_BIN=0;";cat /Users/zhangpanqin/Desktop/477-556.sql) | mysql -u root -h 10.211.55.8 -pMysql@12345678 -f
(echo "SET SQL_LOG_BIN=0;";cat /Users/zhangpanqin/Desktop/775.sql) | mysql -u root -h 10.211.55.8 -pMysql@12345678 -f
  • 查看 binlog 日志,沒有添加二進(jìn)制日志到數(shù)據(jù)庫中,不影響從庫
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000020 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

XtraBackup 使用

XtraBackup 只能備份InnoDB和XtraDB 兩種數(shù)據(jù)表。

安裝

系統(tǒng)環(huán)境:Centos 7 x86_64

數(shù)據(jù)庫:Mysql 8.0.21

由以上環(huán)境決定了 xtrabackup 需要安裝 8.0.14 版本。

https://www.percona.com/doc/percona-xtrabackup/8.0/installation/yum_repo.html
wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.14/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpm

yum localinstall percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpm

# 驗(yàn)證版本
xtrabackup --version

命令講解

  • --backup 備份操作,備份數(shù)據(jù)到 --target-dir 指定的目錄。
  • --prepare 恢復(fù)數(shù)據(jù)執(zhí)行的階段。
  • --use-memory 指定備份時(shí)占用的內(nèi)存,--use-memory=4G。
  • --copy-back 將準(zhǔn)備好的數(shù)據(jù)文件復(fù)制到 mysql datadir 目錄。
  • ``--Apply-log-only` 阻止回滾未完成的事務(wù)

全量備份

創(chuàng)建備份使用的用戶

CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'Mysql@12345678';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'xtrabackup'@'localhost';
GRANT SELECT ON performance_schema.log_status TO 'xtrabackup'@'localhost'; 
FLUSH PRIVILEGES;

xtrabackup 全量備份

xtrabackup  --host=localhost --user=xtrabackup --password=Mysql@12345678 --backup  --target-dir=/opt/test22/backup

全量數(shù)據(jù)恢復(fù)

# 先停止數(shù)據(jù)庫
systemctl stop mysqld

# 恢復(fù)數(shù)據(jù)執(zhí)行的準(zhǔn)備
xtrabackup  --host=localhost --user=xtrabackup --password=Mysql@12345678 --prepare  --target-dir=/opt/test22/backup

# 備份數(shù)據(jù)庫文件,并刪除數(shù)據(jù)庫數(shù)據(jù)目錄下的文件
cp -r /var/lib/mysql{,"$(date '+%Y-%m-%d %H:%M:%S')"_bak} && rm -fr /var/lib/mysql/*
# 恢復(fù)數(shù)據(jù)
xtrabackup  --host=localhost --user=xtrabackup --password=Mysql@12345678 --copy-back --target-dir=/opt/test22/backup
# 查看 /var/lib/mysql 目錄下所有文件的所屬人,需要改成 mysqld 運(yùn)行的用戶
chown -R mysql:mysql /var/lib/mysql
# 啟動(dòng) mysql 數(shù)據(jù)庫
systemctl start mysqld

增量備份

在全量備份的基礎(chǔ)上,增量備份。

增量備份

# 創(chuàng)建全量備份在那個(gè)目錄下
mkdir -p /opt/xtrabackup_mysql/full_data_dir
# 全量基礎(chǔ)之后的增量數(shù)據(jù)一次
mkdir -p /opt/xtrabackup_mysql/increment_data_dir
# 在上一次增量備份的基礎(chǔ)上在增量備份一次
mkdir -p /opt/xtrabackup_mysql/increment_data_dir_2

# 全量備份
xtrabackup --defaults-file=/etc/my.cnf  --host=localhost --user=xtrabackup --password=Mysql@12345678 --backup --parallel=3  --target-dir=/opt/xtrabackup_mysql/full_data_dir

# 全量備份之后,操作數(shù)據(jù)。

# 做增量備份
xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=xtrabackup --password=Mysql@12345678 --backup --parallel=3  --target-dir=/opt/xtrabackup_mysql/increment_data_dir --incremental-basedir=/opt/xtrabackup_mysql/full_data_dir

# 操作了數(shù)據(jù)之后,在上一次增量備份基礎(chǔ)上做第二次增量備份
xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=xtrabackup --password=Mysql@12345678 --backup --parallel=3  --target-dir=/opt/xtrabackup_mysql/increment_data_dir_2 --incremental-basedir=/opt/xtrabackup_mysql/increment_data_dir

增量備份數(shù)據(jù)恢復(fù)

# 停止數(shù)據(jù)庫
systemctl stop mysqld

# 準(zhǔn)備全備份日志數(shù)據(jù)
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/opt/xtrabackup_mysql/full_data_dir

# 合并第一次增量備份數(shù)據(jù)到全量中,注意路徑別寫錯(cuò)了
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/opt/xtrabackup_mysql/full_data_dir  --incremental-dir=/opt/xtrabackup_mysql/increment_data_dir

# 合并第二次增量備份數(shù)據(jù)到全量中,注意路徑。最后一次不需要添加 --apply-log-only
xtrabackup --defaults-file=/etc/my.cnf --prepare  --target-dir=/opt/xtrabackup_mysql/full_data_dir  --incremental-dir=/opt/xtrabackup_mysql/increment_data_dir_2


# 將原來數(shù)據(jù)庫備份
cp -r /var/lib/mysql{,"$(date '+%Y-%m-%d %H:%M:%S')"_bak} && rm -fr /var/lib/mysql/*

-- 拷回?cái)?shù)據(jù)
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/opt/xtrabackup_mysql/full_data_dir
# 修改mysql 數(shù)據(jù)文件的權(quán)限為 mysql
chown -R mysql:mysql /var/lib/mysql
# 啟動(dòng)數(shù)據(jù)庫
systemctl start mysqld

本文由 張攀欽的博客 http://www.mflyyou.cn/ 創(chuàng)作。 可自由轉(zhuǎn)載、引用,但需署名作者且注明文章出處。

如轉(zhuǎn)載至微信公眾號(hào),請(qǐng)?jiān)谖哪┨砑幼髡吖娞?hào)二維碼。微信公眾號(hào)名稱:Mflyyou

分享到:
標(biāo)簽:數(shù)據(jù)備份 Mysql
用戶無頭像

網(wǎng)友整理

注冊(cè)時(shí)間:

網(wǎng)站:5 個(gè)   小程序:0 個(gè)  文章:12 篇

  • 51998

    網(wǎng)站

  • 12

    小程序

  • 1030137

    文章

  • 747

    會(huì)員

趕快注冊(cè)賬號(hào),推廣您的網(wǎng)站吧!
最新入駐小程序

數(shù)獨(dú)大挑戰(zhàn)2018-06-03

數(shù)獨(dú)一種數(shù)學(xué)游戲,玩家需要根據(jù)9

答題星2018-06-03

您可以通過答題星輕松地創(chuàng)建試卷

全階人生考試2018-06-03

各種考試題,題庫,初中,高中,大學(xué)四六

運(yùn)動(dòng)步數(shù)有氧達(dá)人2018-06-03

記錄運(yùn)動(dòng)步數(shù),積累氧氣值。還可偷

每日養(yǎng)生app2018-06-03

每日養(yǎng)生,天天健康

體育訓(xùn)練成績?cè)u(píng)定2018-06-03

通用課目體育訓(xùn)練成績?cè)u(píng)定