溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

MySQL數(shù)據(jù)的備份還原及數(shù)據(jù)修復

發(fā)布時間：2020-07-22 20:05:42 來源：網(wǎng)絡 閱讀：2172 作者：Darius_D 欄目：MySQL數(shù)據(jù)庫

在日常的生產(chǎn)環(huán)境中為什么要進行備份

??備份可以使生產(chǎn)中的數(shù)據(jù)進行有效的災難恢復：硬件故障、軟件故障、自然災害、誤操作測試等數(shù)據(jù)丟失場景。

備份注意要點

  1. 能容忍最多丟失多少數(shù)據(jù)
  2. 恢復數(shù)據(jù)需要在多長時間內(nèi)完成
  3. 需要恢復哪些數(shù)據(jù)

還原要點

  1. 做還原測試，用于測試備份的可用性
  2. 還原演練

備份類型

完全備份、不分備份

  1. 完全備份：整個數(shù)據(jù)集
  2. 部分備份：只備份數(shù)據(jù)子集，如部分庫或表

增量備份、差異備份

  1. 增量備份：僅備份最近一次完全備份或增量備份（如果存在增量）以來變化的數(shù)據(jù)，備份較快，還原復雜
  2. 差異備份：僅備份最近一次完全備份以來變化的數(shù)據(jù)，備份較慢，還原簡單

冷、溫、熱備份

  1. 冷備：讀寫操作均不可進行
  2. 溫備：讀操作可執(zhí)行；但寫操作不可執(zhí)行
  3. 熱備：讀寫操作均可執(zhí)行
    MyISAM：溫備，不支持熱備
    InnoDB：都支持

物理和邏輯備份

  1. 物理備份：直接復制數(shù)據(jù)文件進行備份，與存儲引擎有關(guān)，占用較多的空間，速度快
  2. 邏輯備份：從數(shù)據(jù)庫中“導出”數(shù)據(jù)另存而進行的備份，與存儲引擎無關(guān)，占用空間少，速度慢，可能丟失精度

備份時需要考慮的因素

  1. 溫備的持鎖多久
  2. 備份產(chǎn)生的負載
  3. 備份過程的時長
  4. 恢復過程的時長

備份什么

  1. 數(shù)據(jù)
  2. 二進制日志、InnoDB的事務日志
  3. 程序代碼（存儲過程、函數(shù)、觸發(fā)器、事件調(diào)度器）
  4. 服務器的配置文件

備份工具

  1. cp, tar等復制歸檔工具：物理備份工具，適用所有存儲引擎；只支持冷備；完全和部分備份
  2. LVM的快照：先加鎖，做快照后解鎖，幾乎熱備；借助文件系統(tǒng)工具進行備份
  3. mysqldump：邏輯備份工具，適用所有存儲引擎，溫備；支持完全或部分備份；對InnoDB存儲引擎支持熱備，結(jié)合binlog的增量備份
  4. xtrabackup：由Percona提供支持對InnoDB做熱備(物理備份)的工具，支持完全備份、增量備份
  5. MariaDB Backup： 從MariaDB 10.1.26開始集成，基于Percona
    XtraBackup 2.3.8實現(xiàn)
  6. mysqlbackup：熱備份， MySQL Enterprise Edition組件
  7. mysqlhotcopy：PERL 語言實現(xiàn)，幾乎冷備，僅適用于MyISAM存儲引擎，使用LOCK TABLES、 FLUSH TABLES和cp或scp來快速備份數(shù)據(jù)庫

實驗操作部分

物理備份：借助cp、tar執(zhí)行冷備份（適用于可以停止數(shù)據(jù)庫服務的業(yè)務）

1、停止服務器上的數(shù)據(jù)庫服務進程

[root@CentOS7 ~]#service mysqld stop
Stopping mysqld (via systemctl):                           [  OK  ]

2、考慮到空間資源問題，將備份的數(shù)據(jù)文件進行打包壓縮

[root@CentOS7 data]#tar -zcvf mysql.tar.gz mysql/*
mysql/aria_log.00000001
mysql/aria_log_control
mysql/hello/
mysql/hello/db.opt
mysql/hello/classes.frm
mysql/hello/classes.ibd
mysql/hello/coc.frm
mysql/hello/coc.ibd
mysql/hello/courses.frm
mysql/hello/courses.ibd
mysql/hello/scores.frm
....

3、備份二進制文件至備份主機

[root@CentOS7 data]#mkdir /data/bin
[root@CentOS7 data]#chown -R mysql.mysql /data/bin/
scp -p 可以保留文件的所屬權(quán)限
[root@CentOS7 data]#scp -p /data/bin/* 192.168.36.6:/data/bin/

4、將備份的數(shù)據(jù)壓縮文件scp至備份主機上

[root@CentOS7 data]#scp -p /data/mysql.tar.gz 192.168.36.6:/data/

5、備份配置文件

[root@CentOS7 data]#scp -p /etc/my.cnf 192.168.36.6:/etc/

6、啟動服務

[root@CentOS7 data]#service mysqld start
Starting mysqld (via systemctl):                           [  OK  ]

7、停止服務，刪除數(shù)據(jù)

[root@CentOS7 data]#service mysqld stop
Stopping mysqld (via systemctl):                           [  OK  ]
[root@CentOS7 data]#rm -rf /data/mysql/*

8、解壓備份的數(shù)據(jù)文件到/data/mysql目錄中

[root@CentOS7 data]#gzip -d mysql.tar.gz
[root@CentOS7 data]#tar -xvf mysql.tar -C ./

9、啟動服務測試,并進行數(shù)據(jù)檢查

[root@CentOS7 data]#service mysqld start
Starting mysqld (via systemctl):                           [  OK  ]

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hello              |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)

MariaDB [(none)]> use hello;
Database changed
MariaDB [hello]> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| classes         |
| coc             |
| courses         |
| scores          |
| students        |
| teachers        |
| toc             |
+-----------------+
7 rows in set (0.00 sec)

MariaDB [hello]> select *from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)

基于LVM的備份

1、請求鎖定所有表

mysql> FLUSH TABLES WITH READ LOCK;

2、記錄二進制日志文件及事件位置

mysql> FLUSH LOGS;
mysql> SHOW MASTER STATUS;
mysql -e 'SHOW MASTER STATUS' > /PATH/TO/SOMEFILE

3、創(chuàng)建快照

lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME

4.、釋放鎖

mysql> UNLOCK TABLES;
  1. 掛載快照卷，執(zhí)行數(shù)據(jù)備份
  2. 備份完成后，刪除快照卷
  3. 制定好策略，通過原卷備份二進制日志
    注意：此實驗前提是數(shù)據(jù)文件處于LVM邏輯卷中

基于mysqldump工具進行備份

mysqldump常見選項

??-A， --all-databases 備份所有數(shù)據(jù)庫，含create database
??-B , --databases db_name… 指定備份的數(shù)據(jù)庫，包括create database語句
??-E, --events：備份相關(guān)的所有event scheduler
??-R, --routines：備份所有存儲過程和自定義函數(shù)
??--triggers：備份表相關(guān)觸發(fā)器，默認啟用,用--skip-triggers，不備份觸發(fā)器
??--default-character-set=utf8 指定字符集
??--master-data[=#]： 此選項須啟用二進制日志
???? 1：所備份的數(shù)據(jù)之前加一條記錄為CHANGE MASTER TO語句，非注釋，不指定#，默認為1
???? 2：記錄為注釋的CHANGE MASTER TO語句此選項會自動關(guān)閉--lock-tables功能，自動打開-x | --lock-all-tables功能（除非開啟--single-transaction）
??-F, --flush-logs ：備份前滾動日志，鎖定表完成后，執(zhí)行flush logs命令,生成新的二進制日志文件，配合-A 或 -B 選項時，會導致刷新多次數(shù)據(jù)庫。 建議在同一時刻執(zhí)行轉(zhuǎn)儲和日志刷新，可通過和--single-transaction或-x，--master-data 一起使用實現(xiàn)，此時只刷新一次日志
??--compact 去掉注釋，適合調(diào)試，生產(chǎn)不使用
??-d, --no-data 只備份表結(jié)構(gòu)
??-t, --no-create-info 只備份數(shù)據(jù),不備份create table
??-n,--no-create-db 不備份create database，可被-A或-B覆蓋
??--flush-privileges 備份mysql或相關(guān)時需要使用
??-f, --force 忽略SQL錯誤，繼續(xù)執(zhí)行
??--hex-blob 使用十六進制符號轉(zhuǎn)儲二進制列，當有包括BINARY，VARBINARY，BLOB，BIT的數(shù)據(jù)類型的列時使用，避免亂碼
??-q, --quick 不緩存查詢，直接輸出，加快備份速度

mysqldump實驗部分

單個數(shù)據(jù)庫的備份

1、利用mysqldump生成備份文件

[root@CentOS7 data]#mysqldump hello >/data/backup/hello.sql

2、實現(xiàn)刪庫跑路的功能

[root@CentOS7 data]#mysql -e 'drop database hello'

3、創(chuàng)建一個數(shù)據(jù)庫，實現(xiàn)數(shù)據(jù)恢復

[root@CentOS7 data]#mysql -e 'create database hello'
[root@CentOS7 data]#mysql hello </data/backup/hello.sql

4、數(shù)據(jù)檢查

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hello              |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> use hello;
Database changed
MariaDB [hello]> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| classes         |
| coc             |
| courses         |
| scores          |
| students        |
| teachers        |
| toc             |
+-----------------+
7 rows in set (0.00 sec)
注：mysqldump hello命令可以羅列出數(shù)據(jù)庫hello的數(shù)據(jù)表的結(jié)構(gòu)以及字符集等數(shù)據(jù)，利用這些數(shù)據(jù)可完成數(shù)據(jù)的備份與恢復，缺陷是沒有創(chuàng)建數(shù)據(jù)庫的功能，還需要手動創(chuàng)建數(shù)據(jù)庫名稱。所以并不能確保創(chuàng)建的數(shù)據(jù)庫名稱與之前數(shù)據(jù)庫名稱的一致性
解決上述缺陷的辦法：使用 -B 參數(shù)（-B等價于--databases），可以備份出數(shù)據(jù)庫的定義，推薦使用
[root@CentOS7 data]#mysqldump -B hello >/data/backup/hello_bak.sql
[root@CentOS7 data]#grep '^CREATE DATABASE' /data/backup/hello_bak.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hello` /*!40100 DEFAULT CHARACTER SET latin1 */;
數(shù)據(jù)庫中表的備份

利用mysqldump可以實現(xiàn)數(shù)據(jù)庫中指定表的備份功能
1、備份hello庫中students表的數(shù)據(jù)

[root@CentOS7 data]#mysqldump hello students >/data/backup/students.sql

查看備份數(shù)據(jù)
[root@CentOS7 data]#cat /data/backup/students.sql
-- MySQL dump 10.16  Distrib 10.2.23-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: hello
-- ------------------------------------------------------
-- Server version   10.2.23-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 */;

--
-- Table structure for table `students`
--

DROP TABLE IF EXISTS `students`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `students` (
  `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Age` tinyint(3) unsigned NOT NULL,
  `Gender` enum('F','M') NOT NULL,
  `ClassID` tinyint(3) unsigned DEFAULT NULL,
  `TeacherID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `students`
--

LOCK TABLES `students` WRITE;
/*!40000 ALTER TABLE `students` DISABLE KEYS */;
INSERT INTO `students` VALUES (1,'Shi Zhongyu',22,'M',2,3),(2,'Shi Potian',22,'M',1,7),(3,'Xie Yanke',53,'M',2,16),(4,'Ding Dian',32,'M',4,4),(5,'Yu Yutong',26,'M',3,1),(6,'Shi Qing',46,'M',5,NULL),(7,'Xi Ren',19,'F',3,NULL),(8,'Lin Daiyu',17,'F',7,NULL),(9,'Ren Yingying',20,'F',6,NULL),(10,'Yue Lingshan',19,'F',3,NULL),(11,'Yuan Chengzhi',23,'M',6,NULL),(12,'Wen Qingqing',19,'F',1,NULL),(13,'Tian Boguang',33,'M',2,NULL),(14,'Lu Wushuang',17,'F',3,NULL),(15,'Duan Yu',19,'M',4,NULL),(16,'Xu Zhu',21,'M',1,NULL),(17,'Lin Chong',25,'M',4,NULL),(18,'Hua Rong',23,'M',7,NULL),(19,'Xue Baochai',18,'F',6,NULL),(20,'Diao Chan',19,'F',7,NULL),(21,'Huang Yueying',22,'F',6,NULL),(22,'Xiao Qiao',20,'F',1,NULL),(23,'Ma Chao',23,'M',4,NULL),(24,'Xu Xian',27,'M',NULL,NULL),(25,'Sun Dasheng',100,'M',NULL,NULL);
/*!40000 ALTER TABLE `students` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2019-05-06 15:22:32

2、刪除數(shù)據(jù)庫中的students表

[root@CentOS7 data]#mysql -e 'drop tables hello.students'

3、數(shù)據(jù)恢復

MariaDB [hello]> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| classes         |
| coc             |
| courses         |
| scores          |
| teachers        |
| toc             |
+-----------------+
6 rows in set (0.00 sec)

MariaDB [hello]> source /data/backup/students.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
....

MariaDB [hello]> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| classes         |
| coc             |
| courses         |
| scores          |
| students        |
| teachers        |
| toc             |
+-----------------+
7 rows in set (0.00 sec)
備份數(shù)據(jù)庫中所有數(shù)據(jù)

1、利用mysqldump -A功能進行備份數(shù)據(jù)庫所有數(shù)據(jù)

[root@CentOS7 data]#grep '^CREATE DATABASE' /data/backup/all.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hello` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTE

2、清空數(shù)據(jù)庫數(shù)據(jù)

[root@CentOS7 data]#rm -rf /data/mysql/*

3、重啟服務，還原數(shù)據(jù)

[root@CentOS7 data]#service mysqld restart
Restarting mysqld (via systemctl):                         [  OK  ]

[root@CentOS7 data]#mysql < /data/backup/all.sql

[root@CentOS7 data]#ll /data/mysql/
total 110620
-rw-rw---- 1 mysql mysql    16384 May  6 15:41 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 May  6 15:41 aria_log_control
drwx------ 2 mysql mysql      272 May  6 15:41 hello
-rw-rw---- 1 mysql mysql      860 May  6 15:41 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 May  6 15:41 ibdata1
-rw-rw---- 1 mysql mysql 50331648 May  6 15:41 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 May  6 15:41 ib_logfile1
drwx------ 2 mysql mysql     4096 May  6 15:41 mysql
drwx------ 2 mysql mysql       20 May  6 15:41 test

關(guān)于mysqldump的擴展

MyISAM備份選項：

??支持溫備；不支持熱備，所以必須先鎖定要備份的庫，而后啟動備份操作
??鎖定方法如下：
????-x,--lock-all-tables：加全局讀鎖，鎖定所有庫的所有表，同時加--singletransaction或--lock-tables選項會關(guān)閉此選項功能
????注意：數(shù)據(jù)量大時，可能會導致長時間無法并發(fā)訪問數(shù)據(jù)庫
????-l,--lock-tables：對于需要備份的每個數(shù)據(jù)庫，在啟動備份之前分別鎖定其所有表，默認為on,--skip-lock-tables選項可禁用,對備份MyISAM的多個庫,可能
會造成數(shù)據(jù)不一致
??注：以上選項對InnoDB表一樣生效，實現(xiàn)溫備，但不推薦使用

InnoDB備份選項：

??支持熱備，可用溫備但不建議用
??--single-transaction
????此選項Innodb中推薦使用，不適用MyISAM，此選項會開始備份前，先執(zhí)行START TRANSACTION指令開啟事務
????此選項通過在單個事務中轉(zhuǎn)儲所有表來創(chuàng)建一致的快照。 僅適用于存儲在支持多版本控制的存儲引擎中的表（目前只有InnoDB可以）; 轉(zhuǎn)儲不保證與其他存儲引擎保持一致。 在進行單事務轉(zhuǎn)儲時，要確保有效的轉(zhuǎn)儲文件（正確的表內(nèi)容和二進制日志位置），沒有其他連接應該使用以下語句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE
????此選項和--lock-tables（此選項隱含提交掛起的事務）選項是相互排斥
????備份大型表時，建議將--single-transaction選項和--quick結(jié)合一起使用

InnoDB建議備份策略
mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql
MyISAM建議備份策略
mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql
由于某公司指定時間進行數(shù)據(jù)的備份，然而備份完數(shù)據(jù)發(fā)生了修改，此時出現(xiàn)故障導致數(shù)據(jù)丟失，請嘗試恢復至最近數(shù)據(jù)

1、備份數(shù)據(jù)并壓縮

[root@CentOS7 data]# mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz

[root@CentOS7 data]#ll /data/all.sql.xz
-rw-r--r-- 1 root root 105104 May  6 16:42 /data/all.sql.xz

2、添加數(shù)據(jù)

MariaDB [hello]> insert students(name,age)values('Darius',23);
Query OK, 1 row affected (0.03 sec)

MariaDB [hello]> select *from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
....
|    26 | Darius        |  23 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)

3、 刪庫

[root@CentOS7 data]#rm -rf mysql/*

[root@CentOS7 data]#ll mysql/
total 0

4、重啟服務

[root@CentOS7 data]#service mysqld restart

5、因無需二進制日志改變，所以臨時關(guān)閉二進制日志

MariaDB [(none)]> set sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)

6、恢復數(shù)據(jù)庫，先恢復完全備份

解壓
[root@CentOS7 data]#xz -d all.sql.xz
恢復完全備份數(shù)據(jù)
[root@CentOS7 data]#mysql <all.sql
[root@CentOS7 data]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 36
Server version: 10.2.23-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hello              |
| information_schema |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use hello
Database changed
MariaDB [hello]> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| classes         |
| coc             |
| courses         |
| scores          |
| students        |
| teachers        |
| toc             |
+-----------------+
7 rows in set (0.00 sec)

>> 此時備份之后的數(shù)據(jù)還沒有進行恢復

7、查看完全備份時二進制文件的位置

[root@CentOS7 data]#vim all.sql
....
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=4882960;
....

8、查看完全備份時二進制日志的位置，從此位置后的日志導出到inc.sql

[root@CentOS7 data]# mysqlbinlog --start-position=4882960 /data/bin/mysql-bin.000005 > /data/inc.sql

9、導入數(shù)據(jù)

MariaDB [mysql]> source /data/inc.sql;

開啟二進制日志
MariaDB [(none)]> set sql_log_bin=on;
Query OK, 0 rows affected (0.00 sec)

10、成功恢復數(shù)據(jù)

MariaDB [hello]> select *from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
....
|    26 | Darius        |  23 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)

基于 xtrabackup 工具完全備份及還原

簡介

??percona提供的mysql數(shù)據(jù)庫備份工具，惟一開源的能夠?qū)nnodb和xtradb數(shù)據(jù)庫進行熱備的工具

特點

  1. 備份還原過程快速、可靠
  2. 備份過程不會打斷正在執(zhí)行的事務
  3. 能夠基于壓縮等功能節(jié)約磁盤空間和流量
  4. 自動實現(xiàn)備份檢驗
  5. 開源，免費

關(guān)于xtrabackup

??xtrabackup 是用來備份 InnoDB 表的，不能備份非 InnoDB 表，和 MySQL Server 沒有交互

??innobackupex 腳本用來備份非 InnoDB 表，同時會調(diào)用 xtrabackup 命令來備份 InnoDB 表，還會和 MySQL Server 發(fā)送命令進行交互，如加全局讀鎖（FTWRL）、獲取位點（SHOW SLAVE STATUS）等。即innobackupex是在xtrabackup 之上做了一層封裝實現(xiàn)的

xtrabackup備份過程

MySQL數(shù)據(jù)的備份還原及數(shù)據(jù)修復

xtrabackup的新版變化

xtrabackup版本升級到2.4后，相比之前的2.1有了比較大的變化：innobackupex 功能全部集成到 xtrabackup 里面，只有一個 binary程序，另外為了兼容考慮，innobackupex作為 xtrabackup 的軟鏈接，即xtrabackup現(xiàn)在支持非Innodb表備份，并且 Innobackupex 在下一版本中移除，建議通過xtrabackup替換innobackupex

xtrabackup安裝

xtrabackup工具在EPEL中，需要通過EPEL源進行下載
[root@CentOS7 ~]#yum install percona-xtrabackup

通過官網(wǎng)下載最新版本
https://www.percona.com/downloads/XtraBackup/LATEST/
貼心的人兒，給各位奉上阿里云的EPEL源
[epel]
name=epel
baseurl=http://mirrors.aliyun.com/epel/7/x86_64
gpgcheck=0

xtrabackup用法

?? 備份：innobackupex [option] BACKUP-ROOT-DIR
?? 選項說明：https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html
?? ?? --user：該選項表示備份賬號
?? ?? --password：該選項表示備份的密碼
?? ?? --host：該選項表示備份數(shù)據(jù)庫的地址
?? ?? --databases：該選項接受的參數(shù)為數(shù)據(jù)庫名，如果要指定多個數(shù)據(jù)庫，彼此間需要以空格隔開；如："xtra_test dba_test"，同時，在指定某數(shù)據(jù)庫時，也可以只指定其中的某張表。如："mydatabase.mytable"。該選項對innodb引擎表無效，還是會備份所有innodb表
?? ?? --defaults-file：該選項指定從哪個文件讀取MySQL配置，必須放在命令行第一個選項位置
?? ?? --incremental：該選項表示創(chuàng)建一個增量備份，需要指定--incremental-basedir
?? ?? --incremental-basedir：該選項指定為前一次全備份或增量備份的目錄，與--incremental同時使用
?? ?? --incremental-dir：該選項表示還原時增量備份的目錄
?? ?? --include=name：指定表名，格式：databasename.tablename
?? Prepare：innobackupex --apply-log [option] BACKUP-DIR
?? 選項說明：
?? --apply-log：一般情況下,在備份完成后，數(shù)據(jù)尚且不能用于恢復操作，因為備
份的數(shù)據(jù)中可能會包含尚未提交的事務或已經(jīng)提交但尚未同步至數(shù)據(jù)文件中的事務。因此，此時數(shù)據(jù)文件仍處理不一致狀態(tài)。此選項作用是通過回滾未提交的事務及同步已經(jīng)提交的事務至數(shù)據(jù)文件使數(shù)據(jù)文件處于一致性狀態(tài)
?? --use-memory：和--apply-log選項一起使用，當prepare 備份時，做crash recovery分配的內(nèi)存大小，單位字節(jié)，也可1MB,1M,1G,1GB等，推薦1G
?? --export：表示開啟可導出單獨的表之后再導入其他Mysql中
?? --redo-only：此選項在prepare base full backup，往其中合并增量備份時候使用，但不包括對最后一個增量備份的合并
?? 還原：innobackupex --copy-back [選項] BACKUP-DIR
?? innobackupex --move-back [選項] [--defaults-group=GROUP-NAME] BACKUP-DIR
?? 選項說明：
?? --copy-back：做數(shù)據(jù)恢復時將備份數(shù)據(jù)文件拷貝到MySQL服務器的datadir
?? --move-back：這個選項與--copy-back相似，唯一的區(qū)別是它不拷貝文件，而是移動文件到目的地。這個選項移除backup文件，用時候必須小心。使用場景：沒有足夠的磁盤空間同事保留數(shù)據(jù)文件和Backup副本
?? 還原注意事項：
?? ?? 1.datadir 目錄必須為空。除非指定innobackupex --force-non-emptydirectorires選項指定，否則--copy-backup選項不會覆蓋
?? ?? 2.在restore之前,必須shutdown MySQL實例，不能將一個運行中的實例restore到datadir目錄中
?? ?? 3.由于文件屬性會被保留，大部分情況下需要在啟動實例之前將文件的屬主改為mysql，這些文件將屬于創(chuàng)建備份的用戶
?? ?? ?? chown -R mysql:mysql /data/mysql
?? ?? ?? 以上需要在用戶調(diào)用innobackupex之前完成
?? ?? ?? --force-non-empty-directories：指定該參數(shù)時候，使得innobackupex --copy-back或--move-back選項轉(zhuǎn)移文件到非空目錄，已存在的文件不會被覆蓋。如果--copy-back和--move-back文件需要從備份目錄拷貝一個在datadir已經(jīng)存在的文件，會報錯失敗

備份生成的相關(guān)文件

使用innobackupex備份時，其會調(diào)用xtrabackup備份所有的InnoDB表，復制所有關(guān)于表結(jié)構(gòu)定義的相關(guān)文件(.frm)、以及MyISAM、 MERGE、 CSV和ARCHIVE表的相關(guān)文件，同時還會備份觸發(fā)器和數(shù)據(jù)庫配置信息相關(guān)的文件。這些文件會被保存至一個以時間命名的目錄中,在備份時，innobackupex還會在備份目錄中創(chuàng)建如下文件：
?? (1)xtrabackup_info：innobackupex工具執(zhí)行時的相關(guān)信息，包括版本，備份選項，備份時長，備份LSN(log sequence number日志序列號)，BINLOG的位置
?? (2)xtrabackup_checkpoints：備份類型（如完全或增量）、備份狀態(tài)（如是否已經(jīng)為prepared狀態(tài)）和LSN范圍信息,每個InnoDB頁(通常為16k大小)都會包含一個日志序列號LSN。 LSN是整個數(shù)據(jù)庫系統(tǒng)的系統(tǒng)版本號，每個頁面相關(guān)的LSN能夠表明此頁面最近是如何發(fā)生改變的
?? (3)xtrabackup_binlog_info：MySQL服務器當前正在使用的二進制日志文件及至備份這一刻為止二進制日志事件的位置，可利用實現(xiàn)基于binlog的恢復
?? (4)backup-my.cnf：備份命令用到的配置選項信息
?? (5)xtrabackup_logfile：備份生成的日志文件

使用舊版本 xtrabackup 工具進行備份及還原

1、在原主機

innobackupex --user=root /backup
scp -r /backup/2018-02-23_11-55-57/ 目標主機:/data/

2、在目標主機

innobackupex --apply-log /data/2019-05-06_20-34-35/

systemctl stop mariadb

rm -rf /var/lib/mysql/*

innobackupex --copy-back /data/2019-05-06_20-34-35/

chown -R mysql.mysql /var/lib/mysql/

systemctl start mariadb

使用新版本 xtrabackup 工具進行備份及還原

1、在原主機做完全備份到/backups

xtrabackup --backup --target-dir=/backup/
scp -r /backup/* 目標主機:/backup

2、在目標主機上

??1）預準備：確保數(shù)據(jù)一致，提交完成的事務，回滾未完成的事務 

xtrabackup --prepare --target-dir=/backup/

??2）復制到數(shù)據(jù)庫目錄
???? 注意：數(shù)據(jù)庫目錄必須為空，MySQL服務不能啟動

xtrabackup --copy-back --target-dir=/backup/

??3）還原屬性

chown -R mysql:mysql /var/lib/mysql

??4）啟動服務

systemctl start mariadb

舊版xtrabackup完全，增量備份及還原

??1、在原主機

innobackupex /backup

mkdir /backup/inc{1,2}

??修改數(shù)據(jù)庫內(nèi)容

innobackupex --incremental /backup/inc1 --incrementalbasedir=/backups/2019-05-06_20-34-35（完全備份生成的路徑）

??再次修改數(shù)據(jù)庫內(nèi)容

innobackupex --incremental /backup/inc2 --incrementalbasedir=/backup/inc1/2019-05-06_20-34-35 （上次增量備份生成的路徑）

scp -r /backup/* 目標主機:/data/

??2、在目標主機

不啟動mariadb

rm -rf /var/lib/mysql/*

innobackupex --apply-log --redo-only /data/2019-05-06_20-34-35/

innobackupex --apply-log --redo-only /data/2019-05-06_20-34-35/ --incremental-dir=/data/inc1/2018-02-23_14-26-17

innobackupex --apply-log /data/2019-05-06_20-34-35/ --incrementaldir=/data/inc2/2018-02-23_14-28-29/

innobackupex --copy-back /data/2019-05-06_20-34-35/

chown -R mysql.mysql /var/lib/mysql/

systemctl start mariadb

新版xtrabackup完全，增量備份及還原

??1. 備份過程

??1）完全備份：

xtrabackup --backup --target-dir=/backup/base

??2）第一次修改數(shù)據(jù)
??3）第一次增量備份

xtrabackup --backup --target-dir=/backup/inc1 --incrementalbasedir=/backup/base

??4）第二次修改數(shù)據(jù)
??5）第二次增量

xtrabackup --backup --target-dir=/backup/inc2 --incrementalbasedir=/backup/inc1

??6）scp到目標主機

scp -r /backup/* 目標主機:/backup/

??備份過程生成三個備份目錄

/backup/{base，inc1，inc2}

??2. 還原過程

??1）預準備完成備份，此選項--apply-log-only 阻止回滾未完成的事務

xtrabackup --prepare --apply-log-only --target-dir=/backup/base

??2）合并第1次增量備份到完全備份

xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1

??3）合并第2次增量備份到完全備份：最后一次還原不需要加選項--apply-log-only

xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2

??4）復制到數(shù)據(jù)庫目錄，注意數(shù)據(jù)庫目錄必須為空，MySQL服務不能啟動

xtrabackup --copy-back --target-dir=/backup/base

??5）還原屬性

chown -R mysql:mysql /var/lib/mysql

??6）啟動服務

systemctl start mariadb

xtrabackup 單表導入導出

1、單表備份

innobackupex --include='hellodb.students' /backups

2、備份表結(jié)構(gòu)

mysql -e 'show create table hellodb.students' > student.sql

3、刪除表

mysql -e 'drop table hellodb.students'

4、

innobackupex --apply-log --export /backups/2019-05-06_20-34-35/

5、創(chuàng)建表

mysql>CREATE TABLE `students` (

`StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,

`Name` varchar(50) NOT NULL,

`Age` tinyint(3) unsigned NOT NULL,

`Gender` enum('F','M') NOT NULL,

`ClassID` tinyint(3) unsigned DEFAULT NULL,

`TeacherID` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8

6、刪除表空間

alter table students discard tablespace;

7、復制

cp /backups/2019-05-06_20-34-35/hellodb/students.{cfg,exp,ibd} /var/lib/mysql/hellodb/

8、添加屬組權(quán)限

chown -R mysql.mysql /var/lib/mysql/hellodb/

9 、

mysql>alter table students import tablespace;
向AI問一下細節(jié)

免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手