前言
看完本文,刪庫跑路!? 再也不可能發(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
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
配置是否啟用 binlog
。Mysql 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