MySQL資料庫備份和恢復
目錄- MySQL資料庫備份和恢復
- 備份恢復概述
- 為什么要備份
- 備份注意要點
- 還原要點
- 備份型別：
- 備份時需要考慮的因素
- 備份什么
- 備份工具
- 冷備份 cp tar
- mysqldump備份工具
- 模擬資料庫崩潰，最大限度還原資料
- mysqldump的分庫備份
- mysqldump的MyISAM存盤引擎相關的備份選項：
- mysqldump的InnoDB存盤引擎相關的備份選項：
- 生產環境實戰備份策略
- 將誤洗掉了的某個表進行還原
- 備份恢復概述
備份恢復概述
為什么要備份
災難恢復：硬體故障、軟體故障、自然災害、黑客攻擊、誤操作測驗等資料丟失場景
備份注意要點
能容忍最多丟失多少資料
恢復資料需要在多長時間內完成
需要恢復哪些資料
還原要點
做還原測驗，用于測驗備份的可用性
還原演練
備份型別：
* 完全備份，部分備份
完全備份：整個資料集
部分備份：只備份資料子集，如部分庫或表
* 完全備份、增量備份、差異備份
增量備份：僅備份最近一次完全備份或增量備份（如果存在增量）以來變化的資料，備份較快，還原復雜
差異備份：僅備份最近一次完全備份以來變化的資料，備份較慢，還原簡單
* 注意：二進制日志檔案不應該與資料檔案放在同一磁盤
* 冷、溫、熱備份
冷備：讀、寫操作均不可進行
溫備：讀操作可執行；但寫操作不可執行
熱備：讀、寫操作均可執行
MyISAM：溫備，不支持熱備
InnoDB：都支持
* 物理和邏輯備份
物理備份：直接復制資料檔案進行備份，與存盤引擎有關，占用較多的空間，速度快
邏輯備份：從資料庫中“匯出”資料另存而進行的備份，與存盤引擎無關，占用空間少，速度慢，可能丟失精度
備份時需要考慮的因素
溫備的持鎖多久
備份產生的負載
備份程序的時長
恢復程序的時長
備份什么
資料
二進制日志、InnoDB的事務日志
程式代碼（存盤程序、函式、觸發器、事件調度器）
服務器的組態檔
備份工具
cp, tar等復制歸檔工具：物理備份工具，適用所有存盤引擎；只支持冷備；完全和部分備份
LVM的快照：先加鎖，做快照后解鎖，幾乎熱備；借助檔案系統工具進行備份
mysqldump：邏輯備份工具，適用所有存盤引擎，溫備；支持完全或部分備份；對InnoDB存盤引擎支持熱備，結合binlog的增量備份
xtrabackup：由Percona提供支持對InnoDB做熱備(物理備份)的工具，支持完全備份、增量備份
MariaDB Backup： 從MariaDB 10.1.26開始集成，基于Percona XtraBackup 2.3.8實作
mysqlbackup：熱備份， MySQL Enterprise Edition組件
mysqlhotcopy：PERL 語言實作，幾乎冷備，僅適用于MyISAM存盤引擎，使用LOCK TABLES、
FLUSH TABLES和cp或scp來快速備份資料庫
冷備份 cp tar
- 確定資料庫是否關閉，沒關閉，關閉掉，（適合于可以停止訪問的公司型別）
[root@centos7 ~]#ss -ntl # 確定3306埠關閉
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:6000 *:*
LISTEN 0 5 192.168.122.1:53 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 127.0.0.1:631 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 127.0.0.1:6010 *:*
LISTEN 0 128 127.0.0.1:6011 *:*
LISTEN 0 128 127.0.0.1:6012 *:*
LISTEN 0 128 :::111 :::*
LISTEN 0 128 :::6000 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 128 ::1:631 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 128 ::1:6010 :::*
LISTEN 0 128 ::1:6011 :::*
LISTEN 0 128 ::1:6012 :::*
# 沒關閉使用這條命令關閉
[root@centos7 ~]#systemctl stop mariadb
MariaDB [(none)]> show databases; # 備份前建立一個資料庫或表用以測驗是否可以還原成功
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
- 打包并壓縮mysql目錄
[root@centos7 data]#tar -zcvf mysql-$(date +%Y%m%d-%H%M).tar.gz /data/mysql/*
[root@centos7 data]#ll # 打包成帶時間格式
total 1324
drwxr-xr-x 4 mysql root 336 Nov 29 20:04 mysql
-rw-r--r-- 1 root root 1355106 Nov 29 20:10 mysql-20191130-1144.tar.gz
- 發送到另一臺服務器(最好服務器上的資料庫版本和以前的相同)
[root@centos7 data]#scp mysql-20191130-1144.tar.gz 192.168.39.57:/data/
The authenticity of host '192.168.39.57 (192.168.39.57)' can't be established.
ECDSA key fingerprint is SHA256:vYJfaHhadE2ci7V5WRkZJ6iDUkQFzoZPmny56D9qKfI.
ECDSA key fingerprint is MD5:22:72:17:9a:a8:93:1a:02:d8:09:17:f4:85:fe:b3:f5.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.39.57' (ECDSA) to the list of known hosts.
[email protected]'s password:
mysql-20191130-1144.tar.gz 100% 1323KB 100.2MB/s 00:00
[root@centos7 data]#ls # 查看一下
mysql-20191130-1144.tar.gz
[root@centos7 ~]# mkdir /backup
[root@centos7 ~]# tar zxvf /data/mysql-20191130-1227.tar.gz -C /backup
[root@centos7 backup]# tree # 查看一下檔案是否拷貝過來了
.
└── data
└── mysql
├── aria_log.00000001
├── aria_log_control
├── db1
│ └── db.opt
├── ib_buffer_pool
├── ibdata1
├── ib_logfile0
├── ib_logfile1
├── multi-master.info
├── mysql
│ ├── columns_priv.frm
│ ├── columns_priv.MYD
│ ├── columns_priv.MYI
│ ├── column_stats.frm
│ ├── column_stats.MYD
│ ├── column_stats.MYI
│ ├── db.frm
│ ├── db.MYD
│ ├── db.MYI
│ ├── db.opt
│ ├── event.frm
│ ├── event.MYD
[root@centos7 mysql]# ll # 查看一下MySQL的目錄
total 110668
-rw-rw---- 1 987 981 16384 Nov 30 11:44 aria_log.00000001
-rw-rw---- 1 987 981 52 Nov 30 11:44 aria_log_control
drwx------ 2 987 981 20 Nov 30 11:43 db1
-rw-rw---- 1 987 981 942 Nov 30 11:44 ib_buffer_pool
-rw-rw---- 1 987 981 12582912 Nov 30 11:44 ibdata1
-rw-rw---- 1 987 981 50331648 Nov 30 11:44 ib_logfile0
-rw-rw---- 1 987 981 50331648 Nov 19 16:41 ib_logfile1
-rw-rw---- 1 987 981 0 Nov 19 16:57 multi-master.info
drwx------ 2 987 981 4096 Nov 19 16:41 mysql
-rw-rw---- 1 987 981 29310 Nov 19 16:41 mysql-bin.000001
-rw-rw---- 1 987 981 1685 Nov 30 11:35 mysql-bin.000002
-rw-rw---- 1 987 981 492 Nov 30 11:44 mysql-bin.000003
-rw-rw---- 1 987 981 57 Nov 30 11:41 mysql-bin.index
-rw-rw---- 1 987 981 7 Nov 30 11:44 mysql-bin.state
drwx------ 2 987 981 20 Nov 19 16:41 performance_schema
[root@centos7 mysql]# mv * /var/lib/mysql/ # 移動到原有目錄下
[root@centos7 mysql]# systemctl start mariadb.service # 啟動服務
[root@centos7 mysql]# mysql # 登錄資料庫
MariaDB [(none)]> show databases; # 之前創建的資料庫還在代表還原成功
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysqldump備份工具
- 邏輯備份工具：
mysqldump, mydumper, phpMyAdmin
Schema和資料存盤在一起、巨大的SQL陳述句、單個巨大的備份檔案
mysqldump:是MySQL的客戶端命令，通過mysql協議連接至mysql服務器進行備份
- 命令格式:
mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] –B DB1 [DB2 DB3...]
mysqldump [OPTIONS] –A [OPTIONS]
-
mysqldump參考：
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html -
mysqldump 常見通用選項：
-A, --all-databases #
-B, --databases db_name… #指定備份的資料庫，包括create database陳述句
-E, --events：#備份相關的所有event scheduler
-R, --routines：#備份所有存盤程序和自定義函式
--triggers：#備份表相關觸發器，默認啟用,用--skip-triggers，不備份觸發器
--default-character-set=utf8 #指定字符集
--master-data[=#]： #此選項須啟用二進制日志
#1：所備份的資料之前加一條記錄為CHANGE MASTER TO陳述句，非注釋，不指定#，默認為1
#2：記錄為注釋的CHANGE MASTER TO陳述句
#此選項會自動關閉--lock-tables功能，自動打開-x | --lock-all-tables功能（除非開啟-- single-transaction）
-F, --flush-logs #備份前滾動日志，鎖定表完成后，執行flush logs命令,生成新的二進制日志文 件，配合-A 或 -B
選項時，會導致重繪多次資料庫，建議在同一時刻執行轉儲和日志重繪，可通過和-- single-transaction或-x，--master-data 一起使用實作，此時只重繪一次二進制日志
--compact #去掉注釋，適合除錯，生產不使用
-d, --no-data #只備份表結構
-t, --no-create-info #只備份資料,不備份create table
-n,--no-create-db #不備份create database，可被-A或-B覆寫
--flush-privileges #備份mysql或相關時需要使用
-f, --force #忽略SQL錯誤，繼續執行
--hex-blob #使用十六進制符號轉儲二進制列，當有包括BINARY， VARBINARY，BLOB，BIT的數 據型別的列時使用，避免亂碼
-q, --quick #不快取查詢，直接輸出，加快備份速度
- mysqldump命令用法
[root@centos7 ~]# mysqldump --help # 有三種格式
mysqldump Ver 10.14 Distrib 5.5.60-MariaDB, for Linux (x86_64)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
- 第一種用法(對某一個資料庫或者某個資料庫的特定表做列印，之后在匯入到檔案里,只是列印到螢屏里所以要匯出)
[root@centos7 ~]# mysqldump -uroo hellodb students # 備份hellodb資料庫的students表 （要指定用戶和密碼，沒設定密碼不用指定）不加表名就備份資料庫，
-- MySQL dump 10.14 Distrib 5.5.60-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: hellodb
-- ------------------------------------------------------
-- Server version 5.5.60-MariaDB
/*!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 */;
mysqldump: Got error: 1044: "Access denied for user ''@'localhost' to database 'hellodb'" when selecting the database
- 洗掉庫做實驗
[root@centos7 ~]# mysqldump hellodb > /data/hellodb.sql # 先匯出資料庫
MariaDB [(none)]> drop database hellodb; # 洗掉庫
Query OK, 7 rows affected (0.00 sec)
MariaDB [(none)]> show databases; # 查看已經洗掉掉了
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> create database hello; # 創建一個資料庫名字不一樣都可以
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use hello # 進入創建的資料庫
Database changed
MariaDB [hello]> source /data/hellodb.sql # 把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)
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)
這種方法可以恢復，但是資料庫的格式和字符集都是默認的，這種方法不建議用，
- 第二種方法（推薦使用方法）
-B 挑選指定的資料庫做備份
[root@centos7 ~]# mysqldump -B hellodb mysql > /data/hellodb_mysql.sql # 匯出生成sql腳本，最好加上時間格式，
[root@centos7 ~]# vim /data/hellodb_mysql.sql # 查看一下這個檔案
# 這個sql腳本里面有這一行是用來創建庫和定義庫的格式的，加上-B才會有這一行，
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `hellodb`;
MariaDB [(none)]> show create database hellodb; # 查看hellodb資料庫的字符集和格式
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| hellodb | CREATE DATABASE `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
- 發送到遠程主機做測驗
[root@centos7 ~]# scp /data/hellodb_mysql.sql 192.168.39.27:/root
[email protected]'s password:
hellodb_mysql.sql 100% 509KB 85.6MB/s 00:00
- 匯入sql腳本
MariaDB [(none)]> show databases; # 遠程主機的資料庫
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> source /root/hellodb_mysql.sql # 匯入sql腳本
MariaDB [mysql]> show databases; # 查看資料庫生成
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [mysql]> show create database hellodb; # 查看hellodb字符集，是和原本一摸一樣的，
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| hellodb | CREATE DATABASE `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
- 第三種方法 （所有的資料庫做備份） （完全備份）
[root@centos7 ~]# mysqldump -A |gzip > /data/All.sql.gz # 備份連帶壓縮一起執行
[root@centos7 ~]# ll /data/
total 140
-rw-r--r-- 1 root root 140945 Nov 30 16:00 All.sql.gz
# 還原下面會做現在就解釋一下，
模擬資料庫崩潰，最大限度還原資料
[root@centos7 ~]# mysqldump -A --master-data=https://www.cnblogs.com/www233ii/p/2 |gzip > /data/all_'date +%F'.sql.gz
mysqldump: Error: Binlogging on server not active
# 上面不成功是因為二進制日志沒有啟用
- 啟用二進制日志
# 事先創建好二進制日志存放的路徑
[root@centos7 ~]# chown -R mysql:mysql /data/mysql/ # 創建完路徑記得更改權限
[root@centos7 ~]# vim /etc/my.cnf # 修改組態檔
[mysqld]
log-bin=/data/mysql/bin_log # 指定二進制日志存放路徑（最好和資料庫是分開的） 最后的是指定日志的前綴，
[root@centos7 ~]# systemctl restart mariadb.service # 重啟服務
- 完全備份資料庫
[root@centos7 ~]# mysqldump -A --master-data=https://www.cnblogs.com/www233ii/p/2 |gzip > /data/all_`ate +%F`.sql.gz
[root@centos7 ~]# ll /data/
total 140
-rw-r--r-- 1 root root 141043 Nov 30 16:29 all_2019-11-30.sql.gz
- 模擬添加一條記錄，創建一個賬號，
MariaDB [(none)]> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> insert students (name,age,gender)value('a',20,'M');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> 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 |
| 26 | a | 20 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)
MariaDB [hellodb]> grant all on hellodb.* to test@'192.168.39.%' identified by 'centos'; # 創建一個賬號
Query OK, 0 rows affected (0.00 sec)
- 假設資料庫崩潰了（刪之前確定二進制日志獨立出來了）
[root@centos7 ~]# rm -rf /var/lib/mysql/* # 洗掉資料庫
[root@centos7 ~]# mysql # 登錄不上了
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
- 查看資料庫和二進制檔案
[root@centos7 ~]# ll /var/lib/mysql/ # 資料庫沒有了
total 0
[root@centos7 ~]# ll /data/mysql/ # 二進制檔案獨立出來了
total 12
-rw-rw---- 1 mysql mysql 264 Nov 30 16:26 bin_log.000001
-rw-rw---- 1 mysql mysql 637 Nov 30 16:35 bin_log.000002
-rw-rw---- 1 mysql mysql 54 Nov 30 16:26 bin_log.index
- 重新生成資料庫
[root@centos7 ~]# systemctl restart mariadb.service # 重啟服務就可以
# 新版本在重新啟用服務的時候不會重新啟用資料庫
[root@centos7 ~]# mysql_install_db --user=mysql # 使用這條命令在新版上面生成資料庫
[root@centos7 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB 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)]>
[root@centos7 ~]# ll /var/lib/mysql/ # 查看庫是否生成
total 37852
-rw-rw---- 1 mysql mysql 16384 Nov 30 16:42 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Nov 30 16:42 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Nov 30 16:42 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Nov 30 16:42 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Nov 30 16:42 ib_logfile1
drwx------ 2 mysql mysql 4096 Nov 30 16:42 mysql
srwxrwxrwx 1 mysql mysql 0 Nov 30 16:42 mysql.sock
drwx------ 2 mysql mysql 4096 Nov 30 16:42 performance_schema
drwx------ 2 mysql mysql 6 Nov 30 16:42 test
- 新的資料庫里之前的哪些庫和表是沒有的
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
- 解壓縮完全備份包
[root@centos7 data]# ls
all_2019-11-30.sql.gz mysql
[root@centos7 data]# gzip -d all_2019-11-30.sql.gz
[root@centos7 data]# ls
all_2019-11-30.sql mysql
- 臨時停用二進制日志（不停用的話會有生成一份一模一樣的二進制所以最好關閉）
MariaDB [(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
- 還原資料庫的完全備份，當前會話使用，（但是在完全備份到資料庫崩潰這段時間的資料這樣是還原不了的）
MariaDB [(none)]> source /data/all_2019-11-30.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)
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 [test]> show databases; # 完全備份之前建立的庫是在的
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| hello |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
MariaDB [test]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> 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)
- 查看sql腳本來確定丟失的二進制資料位置
[root@centos7 data]# grep '^-- CHANGE MASTER TO' /data/all_2019-11-30.sql # 查找以這個開頭的行
-- CHANGE MASTER TO MASTER_LOG_FILE='bin_log.000002', MASTER_LOG_POS=245; # 最后的數字確定丟失起點位置
- 以二進制日志恢復丟失資料
[root@centos7 mysql]# ll
total 1064
-rw-rw---- 1 mysql mysql 264 Nov 30 16:26 bin_log.000001
-rw-rw---- 1 mysql mysql 656 Nov 30 16:42 bin_log.000002 # 要這個檔案245之后的
-rw-rw---- 1 mysql mysql 30373 Nov 30 16:42 bin_log.000003 # 和這個檔案全部內容
-rw-rw---- 1 mysql mysql 1038814 Nov 30 16:42 bin_log.000004
-rw-rw---- 1 mysql mysql 245 Nov 30 16:42 bin_log.000005
-rw-rw---- 1 mysql mysql 135 Nov 30 16:42 bin_log.index
[root@centos7 mysql]# mysqlbinlog bin_log.000002 --start-position=245 > inc.sql
[root@centos7 mysql]# mysqlbinlog bin_log.000003 >> inc.sql
# 順序不要錯了
[root@centos7 mysql]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.60-MariaDB 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)]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> source /data/mysql/inc.sql # 匯入二進制腳本
MariaDB [hellodb]> 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 |
| 26 | a | 20 | M | NULL | NULL | # 添加的a記錄回來了
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)
MariaDB [hellodb]> select user,host,password from mysql.user;
+------+---------------------+-------------------------------------------+
| user | host | password |
+------+---------------------+-------------------------------------------+
| root | localhost | |
| root | centos7.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | centos7.localdomain | |
| test | 192.168.39.% | *128977E278358FF80A246B5046F51043A2B1FCED | # 添加的賬戶也還原回來了
+------+---------------------+-------------------------------------------+
7 rows in set (0.00 sec)
- 最后在啟用二進制日志
MariaDB [hellodb]> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
還原的時候，資料庫必須是不能訪問的狀態，資料庫的二進制日志就是備份，
mysqldump的分庫備份
- 先查看庫名
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| hello |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
- 挑出資料庫備份(用grep)
[root@centos7 ~]# mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'
db1
hello
hellodb
mysql
test
- 使用while回圈來備份
[root@centos7 ~]# mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'|while read db;do mysqldump -B $db|gzip > /data/$db.sql.gz;done
[root@centos7 ~]# ll /data/
total 156
-rw-r--r-- 1 root root 516 Nov 30 17:31 db1.sql.gz
-rw-r--r-- 1 root root 1898 Nov 30 17:31 hellodb.sql.gz
-rw-r--r-- 1 root root 1892 Nov 30 17:31 hello.sql.gz
-rw-r--r-- 1 root root 139603 Nov 30 17:31 mysql.sql.gz
-rw-r--r-- 1 root root 516 Nov 30 17:31 test.sql.gz
- 使用for回圈做備份
[root@centos7 ~]# for db in `mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'`;do mysqldump -B $db|gzip > /data/$db.sql.gz;done
[root@centos7 ~]# ll -t /data/
total 156
-rw-r--r-- 1 root root 516 Nov 30 17:34 test.sql.gz
-rw-r--r-- 1 root root 139603 Nov 30 17:34 mysql.sql.gz
-rw-r--r-- 1 root root 1898 Nov 30 17:34 hellodb.sql.gz
-rw-r--r-- 1 root root 1892 Nov 30 17:34 hello.sql.gz
-rw-r--r-- 1 root root 516 Nov 30 17:34 db1.sql.gz
- 使用sed來備份資料
[root@centos7 ~]# mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$' | sed -rn 's#(.*)#mysqldump -B \1 |gzip > /data/\1.sql.gz#p'|bash
[root@centos7 ~]# ll -t /data/
total 156
-rw-r--r-- 1 root root 517 Nov 30 17:37 test.sql.gz
-rw-r--r-- 1 root root 139603 Nov 30 17:37 mysql.sql.gz
-rw-r--r-- 1 root root 1898 Nov 30 17:37 hellodb.sql.gz
-rw-r--r-- 1 root root 1892 Nov 30 17:37 hello.sql.gz
-rw-r--r-- 1 root root 516 Nov 30 17:37 db1.sql.gz
- 用sed替換grep分庫備份
[root@centos7 ~]# mysql -uroot -e 'show databases'|sed -rn '/^(Database|information_schema|performance_schema)$/!s#(.*)#mysqldump -B \1 |gzip > /data/\1.sql.gz#p' |bash
[root@centos7 ~]# ll -t /data/
total 156
-rw-r--r-- 1 root root 516 Nov 30 17:43 test.sql.gz
-rw-r--r-- 1 root root 139600 Nov 30 17:43 mysql.sql.gz
-rw-r--r-- 1 root root 1898 Nov 30 17:43 hellodb.sql.gz
-rw-r--r-- 1 root root 1892 Nov 30 17:43 hello.sql.gz
-rw-r--r-- 1 root root 516 Nov 30 17:43 db1.sql.gz
分庫備份可以撰寫成腳本，配合計劃任務使用，（最好備份的包加上時間格式要不容易覆寫掉）
mysqldump的MyISAM存盤引擎相關的備份選項：
MyISAM不支持事務,只能支持溫備；不支持熱備，所以必須先鎖定要備份的庫，而后啟動備份操作
-x,--lock-all-tables #加全域讀鎖，鎖定所有庫的所有表，同時加--single-transaction或--
lock-tables選項會關閉此選項功能
#注意：資料量大時，可能會導致長時間無法并發訪問資料庫
-l,--lock-tables #對于需要備份的每個資料庫，在啟動備份之前分別鎖定其所有表，默認為on,--
skip-lock-tables選項可禁用,對備份MyISAM的多個庫,可能會造成資料不一致
#注：以上選項對InnoDB表一樣生效，實作溫備，但不推薦使用
mysqldump的InnoDB存盤引擎相關的備份選項：
InnoDB 存盤引擎支持事務,可以利用事務的相應的隔離級別,實作熱備，也可以實作溫備但不建議用
--single-transaction
#此選項Innodb中推薦使用，不適用MyISAM，此選項會開始備份前，先執行START TRANSACTION指令開啟
事務
#此選項通過在單個事務中轉儲所有表來創建一致的快照， 僅適用于存盤在支持多版本控制的存盤引擎中的表
（目前只有InnoDB可以）; 轉儲不保證與其他存盤引擎保持一致， 在進行單事務轉儲時，要確保有效的轉儲
檔案（正確的表內容和二進制日志位置），沒有其他連接應該使用以下陳述句：ALTER TABLE，DROP
TABLE，RENAME TABLE，TRUNCATE TABLE,此選項和--lock-tables（此選項隱含提交掛起的事務）選
項是相互排斥,備份大型表時，建議將--single-transaction選項和--quick結合一起使用
生產環境實戰備份策略
- InnoDB建議備份策略
mysqldump –uroot -p –A –F –E –R --single-transaction --master-data=https://www.cnblogs.com/www233ii/p/1 --flushprivileges
--triggers --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql
- MyISAM建議備份策略
mysqldump –uroot -p –A –F –E –R –x --master-data=https://www.cnblogs.com/www233ii/p/1 --flush-privileges --
triggers --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql
范例：完全備份和還原
#開啟二進制日志
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log-bin
#備份
[root@centos8 ~]#mysqldump -uroot -p -A -F --single-transaction --master-data=https://www.cnblogs.com/www233ii/p/2
|gzip > /backup/all-`date +%F`.sql.gz
#還原
[root@centos8 backup]#dnf install mariadb-server
[root@centos8 backup]#gzip -d all-2019-11-27.sql.gz
[root@centos8 ~]#mysql
MariaDB [(none)]> set sql_log_bin=off;
MariaDB [(none)]> source /backup/all-2019-11-27.sql
MariaDB [(none)]> set sql_log_bin=on;
范例：mysqldump 和二進制日志結合實作增量備份
[root@centos8 ~]#mysqldump -uroot -p -A -F --single-transaction --master-data=https://www.cnblogs.com/www233ii/p/2
|gzip > /backup/all-`date +%F`.sql.gz
#觀察備份檔案中的二進制檔案和位置，將之后的二進制日志進行復制備份
[root@centos8 ~]#cp /var/lib/mysql/mariadb-bin.000003 /backup
[root@centos8 ~]#mysqlbinlog --start-position=389 /backup/mariadb-bin.000003 >
/backup/inc.sql
將誤洗掉了的某個表進行還原
- 先完全備份一份
[root@centos7 ~]# mysqldump -A -F --single-transaction --master-data=https://www.cnblogs.com/www233ii/p/2 |gzip > /backup/allbackup_`date +%F_%T`.sql.gz
[root@centos7 ~]# ll /backup/
total 140
-rw-r--r-- 1 root root 141141 Nov 30 18:03 allbackup_2019-11-30_18:03:08.sql.gz
- 在完全備份之后最一些記錄變化
MariaDB [db1]> create table test ( id int unsigned auto_increment primary key,name varchar(10) not null,mobile char(11) not null ); # 創建一張表
Query OK, 0 rows affected (0.00 sec)
MariaDB [db1]> show tables; # 查看庫里的所有表
+---------------+
| Tables_in_db1 |
+---------------+
| test |
+---------------+
1 row in set (0.00 sec)
MariaDB [db1]> insert test (name) values('rose'); # 添加記錄
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [db1]> insert test (name) values('jack'); # 添加記錄
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [db1]> select * from test; # 查看添加的記錄
+----+------+--------+
| id | name | mobile |
+----+------+--------+
| 1 | rose | |
| 2 | jack | |
+----+------+--------+
2 rows in set (0.01 sec)
- 誤洗掉表
MariaDB [db1]> drop table test;
Query OK, 0 rows affected (0.01 sec)
MariaDB [db1]> show tables;
Empty set (0.00 sec)
- 解壓縮
[root@centos7 ~]# cd /backup/
[root@centos7 backup]# ll
total 140
-rw-r--r-- 1 root root 141141 Nov 30 18:03 allbackup_2019-11-30_18:03:08.sql.gz
[root@centos7 backup]# gzip -d allbackup_2019-11-30_18\:03\:08.sql.gz
[root@centos7 backup]# ll
total 520
-rw-r--r-- 1 root root 528632 Nov 30 18:03 allbackup_2019-11-30_18:03:08.sql
- 找到上次完全備份的結束位置
[root@centos7 backup]# grep '\-\- CHANGE MASTER TO' /backup/allbackup_2019-11-30_18\:03\:08.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='bin_log.000006', MASTER_LOG_POS=245;
[root@centos7 backup]#
- 匯出245之后的所有二進制日志（增量備份）
[root@centos7 backup]# mysqlbinlog --start-position=245 /data/mysql/bin_log.000006 > /backup/inc.sql
[root@centos7 backup]# ll /backup/
total 524
-rw-r--r-- 1 root root 528632 Nov 30 18:03 allbackup_2019-11-30_18:03:08.sql
-rw-r--r-- 1 root root 2791 Nov 30 18:20 inc.sql
- 查看執行過的drop命令的時間和位置
[root@centos7 backup]# mysqlbinlog --start-position=245 /data/mysql/bin_log.000006|grep -i drop
DROP TABLE `test` /* generated by server */
[root@centos7 backup]# mysqlbinlog --start-position=245 /data/mysql/bin_log.000006|grep -C3 -i drop
# at 856 # 這個位置
#191130 18:07:32 server id 1 end_log_pos 961 Query thread_id=35 exec_time=error_code=0
SET TIMESTAMP=1575108452/*!*/;
DROP TABLE `test` /* generated by server */
/*!*/;
DELIMITER ;
# End of log file
- 然后打開匯出來的檔案刪掉或注釋掉誤操作
[root@centos7 backup]# vim /backup/inc.sql
# at 856
#191130 18:07:32 server id 1 end_log_pos 961 Query thread_id=35 exec_time=0 error_code=0
SET TIMESTAMP=1575108452/*!*/;
#DROP TABLE `test` /* generated by server */ # 這行注釋掉其他的可以不用管
/*!*/;
DELIMITER ;
# End of log file
- 洗掉資料庫（可以在一個新的主機上做這個實驗也可以）
[root@centos7 backup]# rm -rf /var/lib/mysql/* # 洗掉資料庫
[root@centos7 backup]# systemctl restart mariadb.service # 重啟服務生成資料庫
[root@centos7 backup]# ll /var/lib/mysql/
total 37852
-rw-rw---- 1 mysql mysql 16384 Nov 30 18:33 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Nov 30 18:33 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Nov 30 18:33 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Nov 30 18:33 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Nov 30 18:33 ib_logfile1
drwx------ 2 mysql mysql 4096 Nov 30 18:33 mysql
srwxrwxrwx 1 mysql mysql 0 Nov 30 18:33 mysql.sock
drwx------ 2 mysql mysql 4096 Nov 30 18:33 performance_schema
drwx------ 2 mysql mysql 6 Nov 30 18:33 test
- 登錄資料庫關閉二進制日志
[root@centos7 backup]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB 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)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
- 還原完全備份
MariaDB [(none)]> source /backup/allbackup_2019-11-30_18:03:08.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)
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 [test]> show databases; # 查看資料庫是否還原
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| hello |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
- 再還原增量備份(注意是修改過誤操作的那個檔案)
MariaDB [test]> source /backup/inc.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)
Database changed
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)
Charset changed
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)
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, 1 row affected, 1 warning (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)
Query OK, 0 rows affected (0.01 sec)
Query OK, 1 row affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR at line 65 in file: '/backup/inc.sql': No query specified # 這里沒有執行就是我注釋掉的drop命令（報錯不用管）
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 [db1]> set sql_log_bin=1; # 確保資料還原完畢再打開
Query OK, 0 rows affected (0.00 sec)
- 查看資料是否還原
MariaDB [db1]> show tables; # 表沒有被刪掉
+---------------+
| Tables_in_db1 |
+---------------+
| test |
+---------------+
1 row in set (0.00 sec)
MariaDB [db1]> select * from test; # 添加的記錄也在
+----+------+--------+
| id | name | mobile |
+----+------+--------+
| 1 | rose | |
| 2 | jack | |
+----+------+--------+
2 rows in set (0.00 sec)
做以上實驗確保你的二進制日志獨立于資料庫之外，轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/99373.html
標籤：MySQL
下一篇：MySql事務的簡單使用