目錄
- 一、資料庫備份的分類
- 1.1 資料備份的重要性
- 1.2 資料庫備份的分類-1
- 1.3 資料庫備份的分類-2
- 1.4 常見的備份方法
- 二、 MySQL完全備份與恢復
- 2.1 MySQL完全備份-1
- 2.2 MySQL完全備份-2
- 2.3 資料庫完全備份分類
- 2.4 MySQL物理冷備份及恢復
- 2.5 mysqldump備份資料庫-1
- 2.6 mysqldump備份資料庫-2
- 2.7 恢復表的操作
- 三、企業備份案例-完全備份
- 3.1 物理冷備份與恢復
- 3.2 mysqldump備份與恢復
- 四、MySQL增量備份與恢復
- 4.1 MySQL增量備份-1
- 4.2 MySQL增量備份-2
- 4.3 MySQL資料庫增量恢復-1
- 4.4 MySQL資料庫增量恢復-2
- 4.5 MySQL資料庫增量恢復-3
- 五、企業備份案例-增量備份
一、資料庫備份的分類
1.1 資料備份的重要性
■ 再生產環境中，資料的安全性至關重要
■ 任何資料的丟失都可能產生嚴重的后果
■ 造成資料丟失的原因
- 程式錯誤
- 人為操作錯誤
- 運算錯誤
- 磁盤故障
- 災難（如火災、地震）和盜竊
1.2 資料庫備份的分類-1
■ 從物理與邏輯的角度，備份可分為
- 物理備份：對資料庫作業系統的物理檔案（如資料檔案、日志檔案等）的備份
◆物理備份方法- 冷備份(脫機備份) :是在關閉資料庫的時候進行的
- 熱備份(聯機備份) :資料庫處于運行狀態，依賴于資料庫的日志檔案
- 溫備份:資料庫鎖定表格(不可寫入但可讀)的狀態下進行備份操作
- 邏輯備份:對資料庫邏輯組件(如: 表等資料庫物件)的
備份
1.3 資料庫備份的分類-2
■ 從資料庫的備份策略角度，備份可分為
- 完全備份：每次對資料進行完整的備份
- 差異備份：備份那些自從上次完全備份之后被修改過的檔案
- 增量備份：只有那些在上次完全備份或者增量備份后被修改的檔案才會被備份
1.4 常見的備份方法
■ 物理冷備
- 備份時資料庫處于關閉狀態，直接打包資料庫檔案
- 備份速度快，恢復時也是最簡單的
■ 專用備份工具mydump或mysqlhotcopy
- mysqldump常用的邏輯備份工具
- mysqlhotcopy僅擁有備份MyISAM和ARCHIVE表
■ 啟用二進制日志進行增量備份
- 進行增量備份，需要重繪_二進制日志
■ 第三方工具備份
- 免費的MySQl熱備份軟體Percona XtraBackup
二、 MySQL完全備份與恢復
2.1 MySQL完全備份-1
■ 完全備份是對整個資料庫的備份、資料庫結構和檔案結構的備份
■ 完全備份保存的是備份完成時刻的資料庫
■ 完全備份是增量備份的基礎
2.2 MySQL完全備份-2
■ 優點
- 安全性高
- 備份與恢復操作簡單方便
■ 缺點
- 資料存在大量的重復
- 占用大量的備份空間，空間利用率低
- 備份與恢復時間長
2.3 資料庫完全備份分類
■ 物理冷備份與恢復
- 關閉MySQL資料庫
- 使用tar命令直接打包資料庫檔案夾
- 直接替換現有MySQL目錄即可
■ mysqldump備份與恢復
- MySQL自帶的備份工具，可方便實作對MySQL的備份
- 可以將指定的庫、表匯出為SQL腳本
- 使用命令mysql匯入備份的資料
2.4 MySQL物理冷備份及恢復
■ 物理冷備份
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# mkdir /backup
[root@localhost ~]# tar zvcf /backup/mysql ll-$(date + %F).tar.gz /usrlocal/mysql/dat
■ 恢復資料庫
[root@localhost ~]# mkdir bak
[root@localhost ~]# mv /usr/local/mysqI/data/ /bak/
[root@localhost ~]# mkdir restore
[root@localhost ~]# tar zxf /backup/mysql all-2020-01-02.tar.gz -C restore/
[root@localhost ~]# mv restore/usr/local/mysql/data/ /usr/local/mysq/
[root@localhost ~]# systemctl start mysql
2.5 mysqldump備份資料庫-1
■ mysqldump命令對單個庫進行完全備份
musqldump -u 用戶名 -p【密碼】【選項】【資料庫名】 > 備份路徑/備份檔案名
■ 單褲備份的示例
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shuai |
| sys |
+--------------------+
5 rows in set (0.00 sec)
[root@localhost ~]# mysqldump -u root -p shuai > /opt/shuai.sql
#shuai.sql就是匯出的備份檔案
[root@localhost opt]# ls
mysql-5.7.20 mysql-boost-5.7.20.tar.gz shuai.sql
mysql_all_2020-08-19.tar.gz rh
2.6 mysqldump備份資料庫-2
■ mysqldump命令對多個庫進行完全備份
mysqldump -u 用戶名 -p 【密碼】【選項】 --databases 庫名1 【庫名2】.. > /備份路徑/備份檔案名
■ 多庫備份的示例
#顯示所有資料庫
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mei |
| mysql |
| performance_schema |
| shuai |
| sys |
+--------------------+
6 rows in set (0.01 sec)
#進行多庫備份
[root@localhost opt]# mysqldump -uroot -pabc123 --databases shuai mei > /opt/shuai.mei
mysqldump: [Warning] Using a password on the command line interface can be insecure.
■ 對所有庫進行完全備份
musqldump -u 用戶名 -p 【密碼】【選項】 -add-databases > /備份路徑/備份檔案名
■ 所有庫備份的示例
[root@localhost opt]# mysqldump -uroot -pabc123 --all-databases > /opt/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
2.7 恢復表的操作
■ 恢復表時同樣可以使用source或者mysql命令
■ source恢復表的操作與恢復庫的操作相同
■ 當備份檔案中包含表的備份，而不包括創建庫的陳述句時，必須指定庫名，且目標庫必須存在
mysql -u 用戶名 -p [密碼] <表備份腳本的路徑
mysql -u root -p mysql </backup/mysql-user.sql
■ 在生產環境中，可以使用shell腳本自動實作定時備份
三、企業備份案例-完全備份
3.1 物理冷備份與恢復
備份資料庫：
[root@localhost ~]# systemctl stop mysqld ###關閉資料庫####
[root@localhost ~]# mkdir /backup ###在根下新建個backup目錄
[root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/ #####/usr/local/mysql/data備份壓縮
[root@localhost ~]# cd /backup/ ###進入/backup/
[root@localhost backup]# ll ##查看備份
總用量 1328
-rw-r--r-- 1 root root 1359664 8月 12 22:39 mysql_all-2018-08-12.tar.gz
◆模擬故障
[root@localhost backup]# mkdir /bak ####在根下新建bak目錄
[root@localhost backup]# mv /usr/local/mysql/data/ /bak/ ####將目錄data資料移動到bak 里面去
[root@localhost backup]# cd /usr/local/mysql/ #####僅mysql目錄查看data
[root@localhost mysql]# ll ####查看后data目錄被移走 ，模擬故障成功
總用量 64
drwxr-xr-x 2 mysql mysql 4096 8月 4 19:19 bin
-rw-r--r-- 1 mysql mysql 17987 9月 13 2017 COPYING
-rw-r--r-- 1 mysql mysql 17987 9月 13 2017 COPYING-test
drwxr-xr-x 2 mysql mysql 55 8月 4 19:19 docs
drwxr-xr-x 3 mysql mysql 4096 8月 4 19:19 include
drwxr-xr-x 4 mysql mysql 191 8月 4 19:19 lib
drwxr-xr-x 4 mysql mysql 30 8月 4 19:19 man
drwxr-xr-x 10 mysql mysql 4096 8月 4 19:20 mysql-test
-rw-r--r-- 1 mysql mysql 2478 9月 13 2017 README
-rw-r--r-- 1 mysql mysql 2478 9月 13 2017 README-test
drwxr-xr-x 28 mysql mysql 4096 8月 4 19:20 share
drwxr-xr-x 2 mysql mysql 90 8月 4 19:20 support-files
drwxr-xr-x 3 mysql mysql 17 8月 4 19:19 usr
◆資料庫恢復
[root@localhost mysql]# mkdir /restore ####根目錄新建restore 目錄
[root@localhost mysql]# tar xzvf /backup/mysql_all-2018-08-12.tar.gz -C /restore/ ####將備份資料庫解壓到 /restore目錄下 不要無腦刷，時間節點不一樣
usr/local/mysql/data/
usr/local/mysql/data/ibdata1
usr/local/mysql/data/ib_logfile1
usr/local/mysql/data/ib_logfile0
，，，，，，省略，，，，，，，
[root@localhost mysql]# mv /restore/usr/local/mysql/data/ /usr/local/mysql/ ####將備份資料移動到到/usr/local/mysql中
[root@localhost mysql]# systemctl start mysqld ####啟動mysql資料庫
[root@localhost mysql]# systemctl status mysqld ####查看mysql資料庫啟動狀態 ，日志檔案顯示正常，
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 日 2018-08-12 22:48:20 CST; 7s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 2036 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 2016 ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 2039 (mysqld)
CGroup: /system.slice/mysqld.service
└─2039 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mys...
8月 12 22:48:20 localhost.localdomain mysqld[2036]: 2018-08-12T14:48:20.795875Z 0....
8月 12 22:48:20 localhost.localdomain mysqld[2036]: 2018-08-12T14:48:20.795889Z 0...;
8月 12 22:48:20 localhost.localdomain mysqld[2036]: 2018-08-12T14:48:20.795911Z 0....
8月 12 22:48:20 localhost.localdomain mysqld[2036]: 2018-08-12T14:48:20.812257Z 0...s
8月 12 22:48:20 localhost.localdomain mysqld[2036]: 2018-08-12T14:48:20.812564Z 0....
8月 12 22:48:20 localhost.localdomain mysqld[2036]: Version: '5.7.20' socket: '/...n
8月 12 22:48:20 localhost.localdomain mysqld[2036]: 2018-08-12T14:48:20.812584Z 0....
8月 12 22:48:20 localhost.localdomain mysqld[2036]: 2018-08-12T14:48:20.812591Z 0...s
8月 12 22:48:20 localhost.localdomain mysqld[2036]: 2018-08-12T14:48:20.826263Z 0...s
8月 12 22:48:20 localhost.localdomain systemd[1]: Started MySQL Server.
Hint: Some lines were ellipsized, use -l to show in full.
3.2 mysqldump備份與恢復
#######創建環境#####
mysql> create database auth; ####創建auth 這個資料庫
mysql> use auth; ####進入auth 這個資料庫
mysql> create table users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT '', PRIMARY KEY (user_name)); ###創建users表
Database changed
mysql> insert into users(user_name,user_passwd) values('zhangsan', password('123456')); ###向users表插入‘zhangsan’資訊
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into users values('lisi', password('123456')); ###向users表插入‘lisi’資訊
Query OK, 1 row affected, 1 warning (0.01 sec)
######列子####
[root@localhost /]# mysqldump -u root -p mysql user >mysql-user.sql ###備份資料庫mysql中的user表 ，檔案保存在當前操作目錄下面
Enter password: ###輸入密碼abc123
######列子#####
[root@localhost /]# mysqldump -u root -p --databases auth >auth.sql ####備份auth資料庫 檔案保存在當前操作目錄下面
Enter password: ####輸入密碼abc123
#####列子######
查看備份檔案
[root@localhost /]# grep -v "^--" auth.sql | grep -v "^/" | grep -v "^$"
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `auth` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `auth`;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`user_name` char(16) NOT NULL,
`user_passwd` char(48) DEFAULT '',
PRIMARY KEY (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES ('zhangsan','*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9');
UNLOCK TABLES;
#####恢復操作#####
mysql [選項] [庫名] [表名] < /備份路徑/備份檔案名
#####列子###恢復表
[root@localhost /]# mysql -u root -p ###進入資料庫###
Enter password: ###輸入密碼####
mysql> show databases; ###查看資料庫####
+--------------------+
| Database |
+--------------------+
| information_schema |
| auth |
| myadm |
| mysql |
| performance_schema |
| sys |
mysql> create database text ; ####新建資料庫text ,等下導表用###
Query OK, 1 row affected (0.00 sec)
mysql> exit ####退出#####
[root@localhost opt]# mysql -u root -p text < mysql-user.sql
Enter password:
[root@localhost /]# mysql -u root -p -e 'show tables from text' #######驗證輸出結果####
Enter password: ######輸入密碼
+----------------+
| Tables_in_text |
+----------------+
| user | ####導進去的表
+----------------+
######列子資料庫恢復#####
[root@localhost /]# mysql -u root -p -e 'drop database auth' ######模擬故障洗掉auth資料庫
Enter password:
[root@localhost /]# mysql -u root -p -e 'show databases' ######查看databases所有資料庫有沒有auth，發現沒有了
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| myadm |
| mysql |
| performance_schema |
| sys |
| text |
[root@localhost /]# mysql -u root -p < ./auth.sql ####匯入資料庫auth
Enter password: ####輸入密碼
[root@localhost /]# mysql -u root -p -e 'show databases' ######查看databases所有資料庫有沒有auth，發現有了
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| auth | #### 導進去的auth資料庫
| bdqn |
| myadm |
| mysql |
| performance_schema |
| sys |
| text |
+--------------------+
四、MySQL增量備份與恢復
4.1 MySQL增量備份-1
■ 使用mysqldump命令進行完全備份存在的問題
- 備份資料中有重復資料
- 備份時間與恢復時間過長
■ 是自上一次備份后增加/變化的檔案或者內容
■ 特點
- 沒有重復資料，備份量不大，時間短
- 恢復需要上次完全備份及完全備份之后所有的增量備份才能恢復，而且要對所有增量備份進行逐個反推恢復
4.2 MySQL增量備份-2
■ MySQL沒有提供直接的增量備份方法
■ 可以通過 MySQL提供的二進制日志間接實作增量備份
■ MySQL二進制日志對備份的意義
- 二進制日志保存了所有更新或者可能更新資料庫的操作
- 二進制日志在啟動MySQL服務器后開始記錄，并在檔案達到
max_binlog_size所設定的大小或者接收到flush logs命令后重新
創建新的日志檔案 - 只需定時執行flush logs方法重新創建新的日志，生成二進制文
件序列，并及時把這些日志保存到安全的地方就完成了一個時間
段的增量備份
4.3 MySQL資料庫增量恢復-1
■ 一般恢復
- 將所有備份的二進制日志內容全部恢復
■ 基于位置恢復
- 資料庫在某一時間點可能既有錯誤的操作也有正確的操作
- 可以基于精準的位置跳過錯誤的操作
■ 基于時間點恢復
- 跳過某個發生錯誤的時間點實作資料恢復
4.4 MySQL資料庫增量恢復-2
■ 增量恢復的方法
- 一般恢復
mysqlbinlog [--no-defaults] 增量備份檔案 | mysql -u 用戶名 -p
- 基于位置的恢復
恢復資料到指定位置
mysqlbinlog --stop-position=’操作 id’ 二進制日志 |mysql -u 用戶名 -p 密碼
從指定的位置開始恢復資料
mysqlbinlog --start-position=’操作 id’ 二進制日志 |mysql -u 用戶名 -p 密碼
4.5 MySQL資料庫增量恢復-3
■ 增量恢復的方法
- 基于時間點的恢復
從日志開頭截止到某個時間點的恢復
mysqlbinlog [--no-defaults] --stop-datetime=’年-月-日 小時:分鐘:秒’ 二進制日志 | mysql -u 用戶名 -p 密碼
從某個時間點到日志結尾的恢復
mysqlbinlog [--no-defaults] --start-datetime=’年-月-日 小時:分鐘:秒’ 二進制日志 | mysql -u 用戶名 -p 密碼
從某個時間點到某個時間點的恢復
mysqlbinlog [--no-defaults] --start-datetime=’年-月-日 小時:分鐘:秒’ --stop-datetime=’年-月-日小時:分鐘:秒’ 二進制日志 | mysql -u 用戶名 -p 密碼
五、企業備份案例-增量備份
1、一般恢復（丟什么資料，找什么資料）
[root@localhost opt]# mysql -u root -p
Enter password:
mysql> create database client;
Query OK, 1 row affected (0.01 sec)
mysql> use client;
mysql> create table user_info(身份證 char(20) not null,姓名 char(20) not null,姓別 char(4),用戶ID號 char(10) not null,資費 int(10));
mysql> insert into user_info values('000006','張三','男','016','10');
mysql> insert into user_info values('000007','李四','女','017','91');
mysql> insert into user_info values('000008','王五','女','018','23');
mysql> select * from user_info;
mysql> show master logs; ####查看當前資料庫binlog檔案
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1492 |
+------------------+-----------+
1 row in set (0.00 sec)
2、進行一次完全備份
mysql> flush tables with read lock; #####備份前需要將資料庫加讀鎖，防止資料在備份時寫入，
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
[root@localhost ~]# mkdir /mysql_bak ###創建/mysql_bak 目錄
[root@localhost opt]# mysqldump -u root -p client user_info >/mysql_bak/client_userinfo-$(date +%F).sql ####這邊括號中%F 的F必須是大寫的
Enter password:
[root@localhost ~]# ll /mysql_bak/
總用量 4
-rw-r--r-- 1 root root 2055 8月 15 05:55 client_userinfo-2018-08-15.sql
[root@localhost mysql_bak]# mysqladmin -u root -p flush-logs
[root@localhost ~]# ll /usr/local/mysql/data/
總用量 122928
-rw-r----- 1 mysql mysql 56 8月 9 16:16 auto.cnf
drwxr-x--- 2 mysql mysql 62 8月 15 06:04 client
-rw-r----- 1 mysql mysql 308 8月 15 06:03 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 8月 15 06:04 ibdata1
-rw-r----- 1 mysql mysql 50331648 8月 15 06:04 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 8月 9 16:16 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 8月 15 06:05 ibtmp1
drwxr-x--- 2 mysql mysql 4096 8月 9 16:16 mysql
-rw-r----- 1 mysql mysql 1539 8月 15 06:05 mysql-bin.000001
-rw-r----- 1 mysql mysql 154 8月 15 06:05 mysql-bin.000002
-rw-r----- 1 mysql mysql 78 8月 15 06:05 mysql-bin.index
drwxr-x--- 2 mysql mysql 8192 8月 9 16:16 performance_schema
drwxr-x--- 2 mysql mysql 8192 8月 9 16:16 sys
3、繼續錄入新的內容并進行增量備份
[root@localhost mysql_bak]# mysql -u root -p
Enter password:
mysql> unlock tables; #######解除表鎖，
Query OK, 0 rows affected (0.00 sec)
mysql> use client;
mysql> insert into user_info values('000009','趙六','男','019','37');
mysql> insert into user_info values('000010','孫七','男','020','36');
mysql> select * from user_info;
+-----------+--------+--------+-------------+--------+
| 身份證 | 姓名 | 姓別 | 用戶ID號 | 資費 |
+-----------+--------+--------+-------------+--------+
| 000006 | 張三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 趙六 | 男 | 019 | 37 |
| 000010 | 孫七 | 男 | 020 | 36 |
+-----------+--------+--------+-------------+--------+
5 rows in set (0.00 sec)
mysql> exit
Bye
[root@localhost mysql_bak]# mysqladmin -u root -p flush-logs
Enter password:
[root@localhost ~]# ll /usr/local/mysql/data
總用量 122932
-rw-r----- 1 mysql mysql 56 8月 9 16:16 auto.cnf
drwxr-x--- 2 mysql mysql 62 8月 15 06:04 client
-rw-r----- 1 mysql mysql 308 8月 15 06:03 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 8月 15 06:07 ibdata1
-rw-r----- 1 mysql mysql 50331648 8月 15 06:07 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 8月 9 16:16 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 8月 15 06:05 ibtmp1
drwxr-x--- 2 mysql mysql 4096 8月 9 16:16 mysql
-rw-r----- 1 mysql mysql 1539 8月 15 06:05 mysql-bin.000001
-rw-r----- 1 mysql mysql 797 8月 15 06:07 mysql-bin.000002
-rw-r----- 1 mysql mysql 154 8月 15 06:07 mysql-bin.000003
-rw-r----- 1 mysql mysql 117 8月 15 06:07 mysql-bin.index
drwxr-x--- 2 mysql mysql 8192 8月 9 16:16 performance_schema
drwxr-x--- 2 mysql mysql 8192 8月 9 16:16 sys
[root@localhost ~]# cp /usr/local/mysql/data/mysql-bin.000002 /mysql_bak/
4、模擬誤操作洗掉 user_info表
[root@localhost mysql_bak]# mysql -u root -p -e 'drop table client.user_info'
Enter password:
[root@localhost mysql_bak]# mysql -u root -p -e 'select * from client.user_info'
Enter password:
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
5、恢復操作
[root@localhost mysql_bak]# mysql -u root -p client < /mysql_bak/client_userinfo-2018-08-14.sql
Enter password:
[root@localhost mysql_bak]# mysql -u root -p -e'select * from client.user_info'
Enter password:
+-----------+--------+--------+-------------+--------+
| 身份證 | 姓名 | 姓別 | 用戶ID號 | 資費 |
+-----------+--------+--------+-------------+--------+
| 000006 | 張三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
+-----------+--------+--------+-------------+--------+
[root@localhost ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 |mysql -u root -p
Enter password:
[root@localhost ~]# mysql -u root -p -e'select * from client.user_info'
Enter password:
+-----------+--------+--------+-------------+--------+
| 身份證 | 姓名 | 姓別 | 用戶ID號 | 資費 |
+-----------+--------+--------+-------------+--------+
| 000006 | 張三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 趙六 | 男 | 019 | 37 |
| 000010 | 孫七 | 男 | 020 | 36 |
+-----------+--------+--------+-------------+--------+
######基于位置恢復#####
[root@localhost mysql_bak]# mysql -uroot -p -e 'drop table client.user_info' ### 洗掉表user_info
Enter password:
[root@localhost mysql_bak]# mysql -uroot -p -e 'select * from client.user_info'
Enter password:
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
[root@localhost mysql_bak]# mysql -u root -p client < /mysql_bak/client_userinfo-2018-08-14.sql
Enter password:
[root@localhost ~]# mysql -u root -p -e'select * from client.user_info'
Enter password:
+-----------+--------+--------+-------------+--------+
| 身份證 | 姓名 | 姓別 | 用戶ID號 | 資費 |
+-----------+--------+--------+-------------+--------+
| 000006 | 張三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
+-----------+--------+--------+-------------+--------+
[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000002 ####--base64-output=decode-rows 64解碼 ### -v 換行顯示
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180815 6:05:58 server id 1 end_log_pos 123 CRC32 0xba2781ac Start: binlog v 4, server v 5.7.20-log created 180815 6:05:58
# at 123
#180815 6:05:58 server id 1 end_log_pos 154 CRC32 0xa0182e52 Previous-GTIDs
# [empty]
# at 154
#180815 6:07:34 server id 1 end_log_pos 219 CRC32 0x3d67048b Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180815 6:07:34 server id 1 end_log_pos 293 CRC32 0xae858d22 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1534284454/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1437073414/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 293
#180815 6:07:34 server id 1 end_log_pos 359 CRC32 0x3ccdc62e Table_map: `client`.`user_info` mapped to number 221
# at 359
#180815 6:07:34 server id 1 end_log_pos 421 CRC32 0x55f72453 Write_rows: table id 221 flags: STMT_END_F
### INSERT INTO `client`.`user_info`
### SET
### @1='000009'
### @2='趙六'
### @3='男'
### @4='019'
### @5=37
# at 421
#180815 6:07:34 server id 1 end_log_pos 452 CRC32 0x45fd2af6 Xid = 50
COMMIT/*!*/;
# at 452
#180815 6:07:39 server id 1 end_log_pos 517 CRC32 0xa553503b Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 517
#180815 6:07:39 server id 1 end_log_pos 591 CRC32 0x02ad5ee9 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1534284459/*!*/;
BEGIN
/*!*/;
# at 591
#180815 6:07:39 server id 1 end_log_pos 657 CRC32 0x3f478909 Table_map: `client`.`user_info` mapped to number 221
# at 657
#180815 6:07:39 server id 1 end_log_pos 719 CRC32 0xb478e1c5 Write_rows: table id 221 flags: STMT_END_F
### INSERT INTO `client`.`user_info`
### SET
### @1='000010'
### @2='孫七'
### @3='男'
### @4='020'
### @5=36
# at 719
#180815 6:07:39 server id 1 end_log_pos 750 CRC32 0xe681426b Xid = 51
COMMIT/*!*/;
# at 750
#180815 6:07:59 server id 1 end_log_pos 797 CRC32 0xbc922301 Rotate to mysql-bin.000003 pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
#######
[root@localhost ~]# mysqlbinlog --no-defaults --stop-position='517' /mysql_bak/mysql-bin.000002 |mysql -u root -p ####操作的節點附近即可
[root@localhost ~]# mysql -u root -p -e'select * from client.user_info'
Enter password:
+-----------+--------+--------+-------------+--------+
| 身份證 | 姓名 | 姓別 | 用戶ID號 | 資費 |
+-----------+--------+--------+-------------+--------+
| 000006 | 張三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 趙六 | 男 | 019 | 37 |
+-----------+--------+--------+-------------+--------+
#######
[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info'
Enter password:
[root@localhost ~]# mysql -u root -p client < /mysql_bak/client_userinfo-2018-08-15.sql
Enter password:
[root@localhost ~]# mysqlbinlog --no-defaults --start-position='657' /mysql_bak/mysql-bin.000002 |mysql -u root -p ####操作的節點附近即可
Enter password:
[root@localhost ~]# mysql -u root -p -e'select * from client.user_info'
Enter password:
+-----------+--------+--------+-------------+--------
| 身份證 | 姓名 | 姓別 | 用戶ID號 | 資費 |
+-----------+--------+--------+-------------+--------+
| 000006 | 張三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000010 | 孫七 | 男 | 020 | 36 |
+-----------+--------+--------+-------------+--------+
#######總結##
基于位置恢復的操作，節點不要選擇太靠近操作的節點，否則容易恢復失敗，
######基于時間恢復##### 20200913 0:01:10
mysqlbinlog --no-defaults --stop-datetime='2018-8-15 6:07:39' /mysql_bak/mysql-bin.000002 |mysql -u root -p ##恢復趙六
mysqlbinlog --no-defaults --start-datetime='2018-8-15 6:07:39' /mysql_bak/mysql-bin.000002 |mysql -u root -p ##恢復孫七
mysqlbinlog --no-defaults --stop-datetime='2020-9-13 0:01:10' /mysql_bak/mysql-bin.000002 |mysql -u root -p ##恢復趙六
mysqlbinlog --no-defaults --start-datetime='2020-9-13 0:01:10' /mysql_bak/mysql-bin.000002 |mysql -u root -p ##恢復孫七
mysqlbinlog --no-defaults --start-datetime='2019-3-13 18:16:30' /mysql_bak/mysql-bin.000003 |mysql -u root -p
18:16:30
####列子###
[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info'
Enter password:
[root@localhost ~]# mysql -u root -p client < /mysql_bak/client_userinfo-2018-08-15.sql
Enter password:
[root@localhost ~]# mysqlbinlog --no-defaults --stop-datetime='2018-8-15 6:07:39' /mysql_bak/mysql-bin.000002 |mysql -u root -p ######時間戳不要弄錯了
Enter password:
[root@localhost ~]# mysql -u root -p -e'select * from client.user_info'
Enter password:
+-----------+--------+--------+-------------+--------+
| 身份證 | 姓名 | 姓別 | 用戶ID號 | 資費 |
+-----------+--------+--------+-------------+--------+
| 000006 | 張三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 趙六 | 男 | 019 | 37 |
+-----------+--------+--------+-------------+--------+
####列子###
[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info'
Enter password:
[root@localhost ~]# mysql -u root -p client < /mysql_bak/client_userinfo-2018-08-15.sql
Enter password:
[root@localhost ~]# mysqlbinlog --no-defaults --start-datetime='2018-8-15 15:33:48' /mysql_bak/mysql-bin.000002 |mysql -u root -p ######時間戳不要弄錯了，根據的你的日志檔案來##
Enter password:
[root@localhost ~]# mysql -u root -p -e'select * from client.user_info'
Enter password:
+-----------+--------+--------+-------------+--------+
| 身份證 | 姓名 | 姓別 | 用戶ID號 | 資費 |
+-----------+--------+--------+-------------+--------+
| 000006 | 張三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000010 | 孫七 | 男 | 020 | 36 |
+-----------+--------+--------+-------------+--------+
#####驗證表的鎖定是否產生二進制檔案####
mysqladmin -u root -p flush-logs
[root@localhost ~]# mysql -uroot -p -e 'show master logs'
Enter password:
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1539 |
| mysql-bin.000002 | 797 |
| mysql-bin.000003 | 9438 |
| mysql-bin.000004 | 154 |
+------------------+-----------+
mysql> use client;
mysql> insert into user_info values('000009','老八','男','025','23');
mysql> select * from user_info;
mysql>LOCK TABLES tbl_name READ;
mysql> insert into user_info values('000010','趙9','男','030','23');
[root@localhost ~]# mysqladmin -u root -p flush-logs
Enter password:
[root@localhost ~]# mysql -uroot -p -e 'show master logs'
Enter password:
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1539 |
| mysql-bin.000002 | 797 |
| mysql-bin.000003 | 9438 |
| mysql-bin.000004 | 499 |
| mysql-bin.000005 | 154 |
+------------------+-----------+
[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000004 ####--base64-output=decode-rows 64解碼 ### -v 換行顯示
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/34436.html
標籤：其他