mysql備份恢複與遷移

邏輯備份mysqldump

本地備份連結方式
mysqldump -uroot -p123456 -S /tmp/mysql.sock
遠端備份的連結方式
mysqldump -uroot -p123456 -h xxxxxxx -P xxxxx           

基本備份參數

-A 全庫備份

例子：實作全庫備份

mkdir -p /data/backup
mysqldump -uroot -p123456 -A -S /tmp/mysql.sock > /data/backup/full.sql           

-B 備份 單個庫或者多個庫資料

mysqldump -uroot -p123456 -B order cms -S /tmp/mysql.sock > /data/backup/full.sql  
建庫+建表           

庫名 表名 ： 備份某個庫下的1張或者多上表

備份 world資料庫下的city和country表

mysqldump -uroot -p123456 world city country -S /tmp/mysql.sock > /data/backup/tab.sql           

mysqldump -uroot -p123456 world  -S /tmp/mysql.sock > /data/backup/tab.sql #隻建表 沒有建庫語句           

備份msyql所有庫和表 –A  -B

Mysqldump –uroot –p’oldboy123’ –A  –B –events|gzip > /opt/a.sql.gz

 

備份所有庫
mysqldump -uroot -poldboy -S /data/3306/mysql.sock -F -B -A|gzip > /server/backup/mysqlbak_$(date+%F).sql.gz
備份一個庫
mysqldump -uroot -poldboy -S /data/3306/mysql.sock -F -B oldboy|gzip > /server/backup/mysqlbak_$(date+%F).sql.gz           

mysqldump -uroot -p123456  -R -E  --triggers  --master-data=2 --single-transaction --set-gtid-purged=OFF  -B ichuntcms  > /data/backup/ichuntcms.sql
備份一個庫ichuntcms           

必須加的參數1）:

-R 在備份時，同時備份存儲過程和函數，如果沒有就會自動忽略

-E 在備份時，同時備份實踐EVENT 如果沒有就會自動忽略

--triggers 在備份時，同時備份觸發器，如果沒有就會自動忽略

必須加的參數2）:

--master-data=2

1. 記錄備份時的position
2. 自動鎖表
3. 配合--single-transaction 減少鎖的（innode引擎）

以注釋的形式,儲存備份開始時間點的binlog的狀态資訊

mysqldump -uroot -p  -A  -R --triggers --master-data=2   >/back/world.sql
[root@db01 ~]# grep 'CHANGE' /backup/world.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000035', MASTER_LOG_POS=194;

功能：
（1）在備份時，會自動記錄，二進制日志檔案名和位置号
0 預設值
1  以change master to指令形式，可以用作主從複制
2  以注釋的形式記錄，備份時刻的檔案名+postion号
（2） 自動鎖表
（3）如果配合--single-transaction，隻對非InnoDB表進行鎖表備份，InnoDB表進行“熱“”備，實際上是實作快照備份。           

--single-transaction

對于innode的表實作快照備份，不鎖表

innodb 存儲引擎開啟熱備(快照備份)功能       
master-data可以自動加鎖
（1）在不加--single-transaction ，啟動所有表的溫備份，所有表都鎖定
（1）加上--single-transaction ,對innodb進行快照備份,對非innodb表可以實作自動鎖表功能
例子6: 備份必加參數
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql           

--set-gtid-purged=auto

沒主從情況下： --set-gtid-purged=OFF

有主從情況下 --set-gtid-purged=ON

auto , on
off 
使用場景:
1. --set-gtid-purged=OFF,可以使用在日常備份參數中.
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
2. auto , on:在建構主從複制環境時需要的參數配置
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=ON >/data/backup/full.sql           

--max-allowed-paxket=128M

--表特别大的時候，可以設定

mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M >/data/backup/full.sql

 --max-allowed-packet=# 
The maximum packet length to send to or receive from server.           

生産場景：增加binlog日志檔案和對應位置點，鎖表

備份恢複案例

1,進行全備

mysqldump -uroot -p123456 -A -R --triggers -E --master-data=2 --single-transaction > /data/backup/full.sql           

master-data=2在備份檔案中增加了binlog檔案和pos位置，好處：恢複全備，恢複日志

vim /data/backup/full.sql

2，模拟全備之後的操作

3，模拟損壞

4，初始化資料

mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
/etc/init.d/mysqld start           

5,進行全備恢複

mysql登入
set sql_log_bin=0;#不記錄binlog
source /data/backup/full.sql
flush privileges;
           

set sql_log_bin=1;           

6，找日志起點和終點

show binlog events in "mysql-bin.000045";

實戰案例：

vim ./ichuntcms.sql

找到gtid位置導出binlog sql檔案

mysqlbinlog --skip-gtids  --include-gtids='efbb4db6-2148-11ed-8b49-000c29281610:2' /data/binlog/3306/mysql-bin.000001 > /tmp/bin.sql           

7,恢複binlog日志

set sql_log_bin = 0;
source /data/backup/bin.sql;

set sql_log_bin = 1;           

擴充：

[root@localhost backup]# sed -e '/./{H;$!d;}'   -e 'x;/CREATE TABLE `user_info`/!d;q' ./ichuntcms.sql

DROP TABLE IF EXISTS `user_info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_info` (
  `userId` int(11) NOT NULL COMMENT '使用者Id',
  `name` varchar(32) NOT NULL DEFAULT '' COMMENT '中文名',
  `engName` varchar(32) NOT NULL COMMENT '英文名',
  `email` varchar(64) NOT NULL DEFAULT '' COMMENT '使用者郵箱',
  `code_sn` varchar(10) NOT NULL DEFAULT '' COMMENT '員工編碼',
  `workNumber` varchar(11) NOT NULL DEFAULT '' COMMENT '工号',
  `mobile` varchar(32) NOT NULL DEFAULT '' COMMENT '手機号碼',
  `gender` tinyint(4) NOT NULL DEFAULT '0' COMMENT '性别: 1 男性，2女性',
  `idCard` varchar(32) NOT NULL DEFAULT '' COMMENT '身份證号',
  `tel` varchar(16) NOT NULL DEFAULT '' COMMENT '座機号',
  `workAddr` varchar(128) NOT NULL DEFAULT '' COMMENT '辦公地點',
  `fax` varchar(32) NOT NULL DEFAULT '' COMMENT '傳真号碼',
  `degree` varchar(16) NOT NULL DEFAULT '' COMMENT '最高學曆',
  `schoole` varchar(32) NOT NULL DEFAULT '' COMMENT '畢業院校',
  `qq` varchar(20) NOT NULL DEFAULT '' COMMENT 'QQ号',
  `wechat` varchar(64) NOT NULL DEFAULT '' COMMENT '微信号',
  `dingtalk` varchar(64) NOT NULL DEFAULT '' COMMENT '釘釘',
  `header` varchar(128) NOT NULL DEFAULT '' COMMENT '頭像',
  `country` varchar(20) NOT NULL DEFAULT '' COMMENT '國家',
  `province` varchar(20) NOT NULL DEFAULT '' COMMENT '省份',
  `city` varchar(20) NOT NULL DEFAULT '' COMMENT '城市',
  `address` varchar(128) NOT NULL DEFAULT '' COMMENT '家庭位址',
  `abo` varchar(4) NOT NULL DEFAULT '' COMMENT '血型',
  `jobLevel` varchar(32) NOT NULL DEFAULT '' COMMENT '職級',
  `department_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '部門ID',
  `department_name` varchar(128) NOT NULL DEFAULT '' COMMENT '部門名稱',
  `position_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '職位ID',
  `position_name` varchar(128) NOT NULL DEFAULT '' COMMENT '職位',
  `birthday` varchar(10) NOT NULL DEFAULT '' COMMENT '生日',
  `superior` varchar(64) NOT NULL DEFAULT '' COMMENT '直接上級',
  `unemployDate` varchar(10) NOT NULL DEFAULT '' COMMENT '離職日期',
  `entryDate` varchar(10) NOT NULL DEFAULT '' COMMENT '入職日期',
  `personSite` varchar(128) NOT NULL DEFAULT '' COMMENT '個人首頁',
  `resume` varchar(60) NOT NULL DEFAULT '' COMMENT '履歷檔案',
  `introduction` varchar(512) NOT NULL DEFAULT '' COMMENT '個人說明',
  `emergencyPeople` varchar(30) NOT NULL DEFAULT '' COMMENT '緊急聯系人',
  `emergencyPhone` varchar(18) NOT NULL DEFAULT '' COMMENT '緊急聯系電話',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '狀态，0-正常，4-離職',
  `ctime` datetime NOT NULL COMMENT '建立時間',
  `mtime` datetime NOT NULL COMMENT '修改時間',
  `wechat_unionid` char(64) NOT NULL DEFAULT '' COMMENT '使用者微信登陸的unionid',
  PRIMARY KEY (`userId`),
  KEY `email` (`email`),
  KEY `engName` (`engName`),
  KEY `mobile` (`mobile`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='使用者資訊表';
/*!40101 SET character_set_client = @saved_cs_client */;



[root@localhost backup]# grep -i 'INSERT INTO `users`' ./ichuntcms.sql
INSERT INTO `users` VALUES (1,'周三','[email protected]','123456','qwert','2022-09-12 04:27:36','2022-09-29 04:27:39');

           

1、獲得表結構
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  full.sql>createtable.sql

2、獲得INSERT INTO 語句，用于資料的恢複

# grep -i 'INSERT INTO `city`'  full.sqll >data.sql &

3.擷取單庫的備份

# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql           

（1）每天全備
（2）binlog日志是完整
（3）模拟白天的資料變化
（4）模拟下午兩點誤删除資料庫

需求： 利用全備+binlog回複資料庫誤删除之前。
故障模拟及恢複：
1. 模拟周一23:00的全備
mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
2. 模拟白天的資料變化
Master [(none)]>create database day1 charset utf8;
Master [(none)]>use day1
Master [day1]>create table t1(id int);
Master [day1]>insert into t1 values(1),(2),(3);
Master [day1]>commit;
Master [world]>update city set countrycode='CHN';
Master [world]>commit;
模拟磁盤損壞：
[root@db01 data]# \rm -rf /data/mysql/data/*
3. 恢複故障
[root@db01 data]# pkill mysqld
[root@db01 data]# \rm -rf /data/mysql/data/*
4. 恢複思路
1.檢查備份可用性
2.從備份中擷取二進制日志位置
3.根據日志位置截取需要的二進制日志
4.初始化資料庫,并啟動
5.恢複全備
6.恢複二進制日志           

例子：
mysqldump -uroot -p123 -A  -R  --triggers --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
mysqldump -uroot -p123 -A  -R  --triggers --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F-%T).sql.gz

mysqldump備份的恢複方式（在生産中恢複要謹慎，恢複會删除重複的表）
set sql_log_bin=0;
source /backup/full_2018-06-28.sql

注意：
1、mysqldump在備份和恢複時都需要mysql執行個體啟動為前提。
2、一般資料量級100G以内，大約15-45分鐘可以恢複，資料量級很大很大的時候（PB、EB）
3、mysqldump是覆寫形式恢複的方法。

一般我們認為，在同資料量級，實體備份要比邏輯備份速度快.
邏輯備份的優勢:
1、可讀性強
2、壓縮比很高           

故障模拟演練


準備資料

create database backup;
use backup
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
rm -rf /backup/*


周二 23：00全備
mysqldump -uroot -p123 -A  -R  --triggers --set-gtid-purged=OFF --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

模拟周二 23：00到周三 10點之間資料變化
use backup
insert into t1 values(11),(22),(33);
commit;
create table t2 (id int);
insert into t2 values(11),(22),(33);

模拟故障,删除表(隻是模拟，不代表生産操作)
drop database backup;


恢複過程
準備臨時資料庫（多執行個體3307）
systemctl start mysqld3307
準備備份
（1）準備全備：
cd /backup
gunzip full_2018-10-17.sql.gz 
（2）截取二進制日志
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000036', MASTER_LOG_POS=793;
mysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:6-7' /data/binlog/mysql-bin.000036 >/backup/bin.sql
恢複備份到臨時庫
mysql -S /data/3307/mysql.sock
set sql_log_bin=0;
source /backup/full_2018-10-17.sql
source /backup/bin.sql

将故障表導出并恢複到生産
mysqldump   -S /data/3307/mysql.sock backup t1 >/backup/t1.sql
mysql -uroot -p123 
set sql_log_bin=0
use backup 
source /backup/t1.sql;           

備份時優化參數:

(1) max_allowed_packet   最大的資料包大小

mysqldump -uroot -p123 -A  -R  --triggers --set-gtid-purged=OFF --master-data=2 max_allowed_packet=128M  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

(2) 增加key_buffer_size    (臨時表有關)
(3) 分庫分表并發備份       (作業)
(4) 架構分離,分别備份      (架構拆分,分布式備份)           

MySQL實體備份工具-xtrabackup(XBK、Xbackup)

安裝依賴包：

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev           

下載下傳軟體并安裝

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm           

備份指令介紹:

xtrabackup
innobackupex    ******


           

備份方式——實體備份

（1）對于非Innodb表（比如 myisam）是，鎖表cp資料檔案，屬于一種溫備份。
（2）對于Innodb的表（支援事務的），不鎖表，拷貝資料頁，最終以資料檔案的方式儲存下來，把一部分redo和undo一并備走，屬于熱備方式。           

xbk 在innodb表備份恢複的流程

0、xbk備份執行的瞬間,立即觸發ckpt,已送出的資料髒頁,從記憶體刷寫到磁盤,并記錄此時的LSN号
  1、備份時，拷貝磁盤資料頁，并且記錄備份過程中産生的redo和undo一起拷貝走,也就是checkpoint LSN之後的日志
  2、在恢複之前，模拟Innodb“自動故障恢複”的過程，将redo（前滾）與undo（復原）進行應用
  3、恢複過程是cp 備份到原來資料目錄下           

innobackupex使用

全備

[root@db01 backup]# innobackupex --user=root --password=123  /data/backup           

注意：備份工具依賴于/etc/my.cnf

如果說配置檔案沒有在/etc/,可以如下操作

innobackupex --defaults-file=xxxxx --user=root --password=123  /data/backup           

自主定制備份路徑名

[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /data/backup/full           

備份集中多出來的檔案：

-rw-r----- 1 root root       24 Jun 29 09:59 xtrabackup_binlog_info
-rw-r----- 1 root root      119 Jun 29 09:59 xtrabackup_checkpoints
-rw-r----- 1 root root      489 Jun 29 09:59 xtrabackup_info
-rw-r----- 1 root root     2560 Jun 29 09:59 xtrabackup_logfile

xtrabackup_binlog_info ：（備份時刻的binlog位置）
[root@db01 full]# cat xtrabackup_binlog_info 
mysql-bin.000003    536749
79de40d3-5ff3-11e9-804a-000c2928f5dd:1-7
記錄的是備份時刻，binlog的檔案名字和當時的結束的position，可以用來作為截取binlog時的起點。

xtrabackup_checkpoints ：
backup_type = full-backuped
from_lsn = 0            上次所到達的LSN号(對于全備就是從0開始,對于增量有别的顯示方法)
to_lsn = 160683027      備份開始時間(ckpt)點資料頁的LSN    
last_lsn = 160683036    備份結束後，redo日志最終的LSN
compact = 0
recover_binlog_info = 0
（1）備份時刻，立即将已經commit過的，記憶體中的資料頁重新整理到磁盤(CKPT).開始備份資料，資料檔案的LSN會停留在to_lsn位置。
（2）備份時刻有可能會有其他的資料寫入，已備走的資料檔案就不會再發生變化了。
（3）在備份過程中，備份軟體會一直監控着redo的undo，如果一旦有變化會将日志也一并備走，并記錄LSN到last_lsn。
從to_lsn  ----》last_lsn 就是，備份過程中産生的資料變化.           

全備的恢複

準備備份（Prepared）

将redo進行重做，已送出的寫到資料檔案，未送出的使用undo復原掉。模拟了CSR的過程
[root@db01 ~]# innobackupex --apply-log  /backup/full           

恢複備份

前提：
1、被恢複的目錄是空
2、被恢複的資料庫的執行個體是關閉
systemctl stop mysqld           

建立新目錄

[root@db01 backup]# mkdir /data/mysql1           

資料授權

chown -R mysql.mysql /data/mysql1           

恢複備份

[root@db01 full]# cp -a /backup/full/* /data/mysql1/           

啟動資料庫

vim /etc/my.cnf
datadir=/data/mysql1
[root@db01 mysql1]# chown -R mysql.mysql /data/mysql1
systemctl start mysqld           

innobackupex 增量備份(incremental)

（1）增量備份的方式，是基于上一次備份進行增量。
（2）增量備份無法單獨恢複。必須基于全備進行恢複。
（3）所有增量必須要按順序合并到全備中。           

增量備份指令

（1）删掉原來備份

略.

（2）全備（周日）

[root@db01 backup]# innobackupex --user=root --password --no-timestamp /backup/full >&/tmp/xbk_full.log

或者 innobackupex --user=root --password --no-timestamp /backup/full_$(date +%F) >&/tmp/xbk_full.log

（3）模拟周一資料變化

db01 [(none)]>create database cs charset utf8;

db01 [(none)]>use cs

db01 [cs]>create table t1 (id int);

db01 [cs]>insert into t1 values(1),(2),(3);

db01 [cs]>commit;

（4）第一次增量備份（周一）

innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1 &>/tmp/inc1.log

--incremental-basedir=原始全備目錄 當天增量目錄

（5）模拟周二資料

db01 [cs]>create table t2 (id int);

db01 [cs]>insert into t2 values(1),(2),(3);

db01 [cs]>commit;

（6）周二增量

innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1 /backup/inc2 &>/tmp/inc2.log

--incremental-basedir=上一天的增量備份目錄 當天增量目錄

（7）模拟周三資料變化

db01 [cs]>create table t3 (id int);

db01 [cs]>insert into t3 values(1),(2),(3);

db01 [cs]>commit;

db01 [cs]>drop database cs;

檢查下備份檔案的lsn号

模拟删除資料庫

pkill mysqld

\rm -rf /data/mysql/data/*

恢複到周三誤drop之前的資料狀态

恢複思路：
1.  挂出維護頁，停止當天的自動備份腳本
2.  檢查備份：周日full+周一inc1+周二inc2，周三的完整二進制日志
3. 進行備份整理（細節），截取關鍵的二進制日志（從備份——誤删除之前）
4. 測試庫進行備份恢複及日志恢複
5. 應用進行測試無誤，開啟業務
6. 此次工作的總結           

恢複過程

1. 檢查備份
1afe8136-601d-11e9-9022-000c2928f5dd:7-9
2. 備份整理（apply-log）+合并備份（full+inc1+inc2）
(1) 全備的整理
[root@db01 one]# innobackupex --apply-log --redo-only /data/backup/full
(2) 合并inc1到full中
[root@db01 one]# innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1 /data/backup/full
(3) 合并inc2到full中
           

最後一次增量恢複不需要 --redo-only參數，上面過程都需要添加--redo-only           

[root@db01 one]# innobackupex --apply-log  --incremental-dir=/data/backup/inc2 /data/backup/full
(4) 最後一次整理全備
[root@db01 backup]#  innobackupex --apply-log  /data/backup/full
3. 截取周二 23:00 到drop 之前的 binlog 
[root@db01 inc2]# mysqlbinlog --skip-gtids --include-gtids='1afe8136-601d-11e9-9022-000c2928f5dd:7-9' /data/binlog/mysql-bin.000009 >/data/backup/binlog.sql
4. 進行恢複
[root@db01 backup]# mkdir /data/mysql/data2 -p
[root@db01 full]# cp -a * /data/mysql/data2
[root@db01 backup]# chown -R mysql.  /data/*
[root@db01 backup]# systemctl stop mysqld
vim /etc/my.cnf
datadir=/data/mysql/data2
systemctl start mysqld
Master [(none)]>set sql_log_bin=0;
Master [(none)]>source /data/backup/binlog.sql           

上述3中截取binlog日志

-----找到最後一次增量備份的檔案

mysqlbinlog --skip-gitds --include-gtids='xxxx:5' /data/binlog/mysql-bin.000050 >data/bak/bin.sql

登入mysql用戶端

source /data/bak/bin.sql           

擴充：

innobackupex --user=root --password=123 --defaults-file=/etc/my.cnf  -no-timestamp --stream=tar --use-memory=256M --parallel=8 /data/mysql_backup | gzip | ssh   [email protected]  " cat -> /data/mysql_backup.tgz "

--stream=tar   #啟用壓縮方式備份
--use-memory=256M  #單獨占用一塊記憶體
--parallel=8 #開啟并發備份 一般設定為cpu核數的一半           

備份如何配合binlog找到起點和終點

mysqldump

起點：找到全備腳本，找到change master to

終點：通過看events 或者 檔案内容 找到故障點的位置

xtrabackup：

起點：找到增強備份，備份路徑下xtrabackup_binlog_info

終點：通過看events 或者 檔案内容 找到故障點的位置

備份集中單獨恢複表

思考:在之前的項目案例中,如果誤删除的表隻有10M,而備份有500G,該如何快速恢複誤删除表?
提示：
drop table city;
create table city like city_bak;
alter table city discard tablespace;
cp /backup/full/world/city.ibd  /application/mysql/data/world/
chown -R mysql.mysql  /application/mysql/data/world/city.ibd 
alter table city import  tablespace;           

從mysqldump 全備中擷取庫和表的備份

1、獲得表結構
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  full.sql>createtable.sql

2、獲得INSERT INTO 語句，用于資料的恢複

# grep -i 'INSERT INTO `city`'  full.sqll >data.sql &

3.擷取單庫的備份

# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql