mysql恢復與備份

1. 開啟bin-log

vim /etc/my.cnf
log_bin=/var/log/data/mysql-bin
binlog_format = row #日志格式
systemctl restart mysqld

常用命令：
# 是否啟用binlog日志
show variables like 'log_bin';
# 查看具體一個binlog文件的內容 （in 后面為binlog的文件名）
show binlog events in 'mysql-bin.000003';

2. 準備試驗數據

# 庫名：test,表名：user
1、建表
CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '用戶id',
  `username` varchar(30) NOT NULL DEFAULT '' COMMENT '用戶名',
  `password` varchar(128) NOT NULL DEFAULT '' COMMENT '密碼',
  `email` varchar(75) NOT NULL DEFAULT '' COMMENT '郵箱',
  `mobile` varchar(20) NOT NULL DEFAULT '' COMMENT '手機號碼',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `mobile` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶基本信息表';

2. 插入數據
mysql> select * from user;
+----+----------+----------+-------+----------+---------------------+---------------------+
| id | username | password | email | mobile   | create_time         | update_time         |
+----+----------+----------+-------+----------+---------------------+---------------------+
|  1 | one      | 123456   | rty   | 12345432 | 2020-12-19 11:55:40 | 2020-12-19 11:55:40 |
|  2 | two      | 67hff    | efs   | 239654   | 2020-12-19 11:55:58 | 2020-12-19 11:55:58 |
|  3 | three    | 5543     | uuj   | 69054    | 2021-01-12 15:57:20 | 2021-01-12 15:57:20 |
+----+----------+----------+-------+----------+---------------------+---------------------+

3.模擬業務場景(誤刪數據)
# 刪除數據
DELETE from user where username="three";
# 后續新的業務操作
insert into user(`username`,`password`,`email`,`mobile`)
values("four","wf55","ss7d","assef");
# 目前表記錄
mysql> select * from user;
+----+----------+----------+-------+----------+---------------------+---------------------+
| id | username | password | email | mobile   | create_time         | update_time         |
+----+----------+----------+-------+----------+---------------------+---------------------+
|  1 | one      | 123456   | rty   | 12345432 | 2020-12-19 11:55:40 | 2020-12-19 11:55:40 |
|  2 | two      | 67hff    | efs   | 239654   | 2020-12-19 11:55:58 | 2020-12-19 11:55:58 |
|  4 | four     | wf55     | ss7d  | assef    | 2021-01-12 17:50:45 | 2021-01-12 17:50:45 |
+----+----------+----------+-------+----------+---------------------+---------------------+

3. 恢復數據

3.1 更新日志文件,加只讀鎖

1. 查看最新的bin-log文件
[root@localhost data]# cd /var/log/data
[root@localhost data]# ls
mysql-bin.000001  mysql-bin.index

2.執行一次刷新日志索引操作，重新開始新的binlog日志記錄文件，mysql-bin.000001這個文件不會再有后續寫入了，便于定位為題
mysql> flush logs;
Query OK, 0 rows affected (0.09 sec)
# 新增了mysql-bin.000002
[root@localhost data]# ls
mysql-bin.000001  mysql-bin.000002  mysql-bin.index

3.在數據恢復前阻斷新的業務操作
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

#恢復后執行：
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

3.2 分析日志文件

1. 通過之前刪除的sql語句查找(過濾掉相關的位置信息，刪除庫里現有數據，進行全量恢復,當前我們只要恢復那條刪除的數據，不適用)
[root@localhost data]# mysqlbinlog mysql-bin.000001 | grep -C 6 --color  'DELETE from user where username="three"'
SET TIMESTAMP=1610445036/*!*/;
BEGIN
/*!*/;
# at 6290
#210112 17:50:36 server id 1  end_log_pos 6403 CRC32 0xc9e6d67f 	Query	thread_id=13	exec_time=0	error_code=0
SET TIMESTAMP=1610445036/*!*/;
DELETE from user where username="three"
/*!*/;
# at 6403
#210112 17:50:36 server id 1  end_log_pos 6434 CRC32 0x71dab87a 	Xid = 213
COMMIT/*!*/;
# at 6434
#210112 17:50:45 server id 1  end_log_pos 6513 CRC32 0x1b225b82 	Query	thread_id=13	exec_time=0	error_code=0

2. 查找name=three相關的日志信息(找到了一條插入和刪除記錄)
[root@localhost data]# mysqlbinlog mysql-bin.000001 | grep -C 6 --color  'three'
# at 5971
# at 6003
#210112 15:57:20 server id 1  end_log_pos 6003 CRC32 0x50942aba 	Intvar
SET INSERT_ID=3/*!*/;
#210112 15:57:20 server id 1  end_log_pos 6180 CRC32 0xba7fdfb9 	Query	thread_id=9	exec_time=0	error_code=0
SET TIMESTAMP=1610438240/*!*/;
INSERT INTO `user` (`username`, `password`, `email`, `mobile`) VALUES ('three', '5543', 'uuj', '69054')
/*!*/;
# at 6180
#210112 15:57:20 server id 1  end_log_pos 6211 CRC32 0xb6a36777 	Xid = 141
COMMIT/*!*/;
# at 6211
#210112 17:50:36 server id 1  end_log_pos 6290 CRC32 0x2931601b 	Query	thread_id=13	exec_time=0	error_code=0
SET TIMESTAMP=1610445036/*!*/;
BEGIN
/*!*/;
# at 6290
#210112 17:50:36 server id 1  end_log_pos 6403 CRC32 0xc9e6d67f 	Query	thread_id=13	exec_time=0	error_code=0
SET TIMESTAMP=1610445036/*!*/;
DELETE from user where username="three"
/*!*/;
# at 6403
#210112 17:50:36 server id 1  end_log_pos 6434 CRC32 0x71dab87a 	Xid = 213
COMMIT/*!*/;
# at 6434
#210112 17:50:45 server id 1  end_log_pos 6513 CRC32 0x1b225b82 	Query	thread_id=13	exec_time=0	error_code=0

3.binlog events查找
如下圖：
插入username="three"
這條數據的日志區間是日志區間是Pos[6003] --> End_log_pos[6180];
按事務區間是：Pos[5892] --> End_log_pos[6211]；

3.3 mysqlbinlog還原

1. 通過插入記錄的pos進行還原
注：單條數據恢復的話直接執行下sql語句就好(注意主鍵id)
命令：
[root@localhost data]# mysqlbinlog --start-position=5892 --stop-position=6211 --database=test mysql-bin.000001 | mysql -uroot -pqqcqqc -v test
查看：
mysql> select * from test.user;
+----+----------+----------+-------+----------+---------------------+---------------------+
| id | username | password | email | mobile   | create_time         | update_time         |
+----+----------+----------+-------+----------+---------------------+---------------------+
|  1 | one      | 123456   | rty   | 12345432 | 2020-12-19 11:55:40 | 2020-12-19 11:55:40 |
|  2 | two      | 67hff    | efs   | 239654   | 2020-12-19 11:55:58 | 2020-12-19 11:55:58 |
|  3 | three    | 5543     | uuj   | 69054    | 2021-01-12 15:57:20 | 2021-01-12 15:57:20 |
|  4 | four     | wf55     | ss7d  | assef    | 2021-01-12 17:50:45 | 2021-01-12 17:50:45 |
+----+----------+----------+-------+----------+---------------------+---------------------+
4 rows in set (0.00 sec)

3.4.刪除user表后進行還原(通過pos點)

1. 清理數據
drop table test.user;

mysql> select * from test.user;
ERROR 1146 (42S02): Table 'test.user' doesn't exist

2.查看日志文件
[root@localhost data]# ls
mysql-bin.000001  mysql-bin.000002  mysql-bin.index
刪除user表的日志存在了mysql-bin.000002

[root@localhost data]# mysqlbinlog mysql-bin.000002 | grep -C 6 --color  'DROP TABLE `user`'
#210112 15:57:20 server id 1  end_log_pos 959 CRC32 0xc38f7ca5 	Xid = 307
COMMIT/*!*/;
# at 959
#210112 19:04:23 server id 1  end_log_pos 1076 CRC32 0x682f1347 	Query	thread_id=13	exec_time=0	error_code=0
SET TIMESTAMP=1610449463/*!*/;
SET @@session.pseudo_thread_id=13/*!*/;
DROP TABLE `user` /* generated by server */
/*!*/;
# at 1076
#210112 19:05:59 server id 1  end_log_pos 1198 CRC32 0xdf4c1492 	Query	thread_id=5	exec_time=0	error_code=0
SET TIMESTAMP=1610449559/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
DROP TABLE `yilou_log` /* generated by server */

# show binlog events in 'mysql-bin.000002';

3.還原數據
# 還原mysql-bin.000001所有數據
mysqlbinlog --start-position=4 --stop-position=6791 --database=test mysql-bin.000001 | mysql -uroot -pqqcqqc -v test
mysql> select * from test.user;
+----+----------+----------+-------+----------+---------------------+---------------------+
| id | username | password | email | mobile   | create_time         | update_time         |
+----+----------+----------+-------+----------+---------------------+---------------------+
|  1 | one      | 123456   | rty   | 12345432 | 2020-12-19 11:55:40 | 2020-12-19 11:55:40 |
|  2 | two      | 67hff    | efs   | 239654   | 2020-12-19 11:55:58 | 2020-12-19 11:55:58 |
|  4 | four     | wf55     | ss7d  | assef    | 2021-01-12 17:50:45 | 2021-01-12 17:50:45 |
+----+----------+----------+-------+----------+---------------------+---------------------+
3 rows in set (0.00 sec)


# 還原mysql-bin.000002文件 pos點在959之前的所有數據
mysqlbinlog --start-position=4 --stop-position=959 --database=test mysql-bin.000002 | mysql -uroot -pqqcqqc -v test

最終結果：
mysql> select * from test.user;
+----+----------+----------+-------+----------+---------------------+---------------------+
| id | username | password | email | mobile   | create_time         | update_time         |
+----+----------+----------+-------+----------+---------------------+---------------------+
|  1 | one      | 123456   | rty   | 12345432 | 2020-12-19 11:55:40 | 2020-12-19 11:55:40 |
|  2 | two      | 67hff    | efs   | 239654   | 2020-12-19 11:55:58 | 2020-12-19 11:55:58 |
|  3 | three    | 5543     | uuj   | 69054    | 2021-01-12 15:57:20 | 2021-01-12 15:57:20 |
|  4 | four     | wf55     | ss7d  | assef    | 2021-01-12 17:50:45 | 2021-01-12 17:50:45 |
+----+----------+----------+-------+----------+---------------------+---------------------+
4 rows in set (0.00 sec)

3.5.刪除user表后進行還原(通過時間點)

現有數據：
mysql> mysql> select * from test.user;
+----+----------+----------+-------+----------+---------------------+---------------------+
| id | username | password | email | mobile   | create_time         | update_time         |
+----+----------+----------+-------+----------+---------------------+---------------------+
|  1 | one      | 123456   | rty   | 12345432 | 2020-12-19 11:55:40 | 2020-12-19 11:55:40 |
|  2 | two      | 67hff    | efs   | 239654   | 2020-12-19 11:55:58 | 2020-12-19 11:55:58 |
|  3 | three    | 5543     | uuj   | 69054    | 2021-01-12 15:57:20 | 2021-01-12 15:57:20 |
|  4 | four     | wf55     | ss7d  | assef    | 2021-01-12 17:50:45 | 2021-01-12 17:50:45 |
+----+----------+----------+-------+----------+---------------------+---------------------+
4 rows in set (0.00 sec)

# 只恢復2020年的數據
mysql> drop table test.user;
Query OK, 0 rows affected (0.07 sec)

mysql> select * from test.user;
ERROR 1146 (42S02): Table 'test.user' doesn't exist

mysqlbinlog --start-datetime="2019-12-31 00-00-00" --stop-datetime="2020-12-31 00-00-00" --database=test mysql-bin.000001 | mysql -uroot -pqqcqqc -v test

# 查看結果
mysql> select * from test.user;
+----+----------+----------+-------+----------+---------------------+---------------------+
| id | username | password | email | mobile   | create_time         | update_time         |
+----+----------+----------+-------+----------+---------------------+---------------------+
|  1 | one      | 123456   | rty   | 12345432 | 2020-12-19 11:55:40 | 2020-12-19 11:55:40 |
|  2 | two      | 67hff    | efs   | 239654   | 2020-12-19 11:55:58 | 2020-12-19 11:55:58 |
+----+----------+----------+-------+----------+---------------------+---------------------+
2 rows in set (0.00 sec)

4. mysqlbinlog,mysqldump

還原數據mysqlbinlog：
--stop-position=1437                   結束pos點
--start-datetime="2019-12-31 00-00-00" 起始時間點
--stop-datetime="2020-12-31 00-00-00"  結束時間點
--database=zyyshop                     指定只恢復test數據庫(一臺主機上往往有多個數據庫，只限本地log日志)
# 數據恢復本質上就是找到binlog中的sql日志，執行

備份數據mysqldump：
mysqldump -h 127.0.0.1 -uroot -pqqcqqc test > /tmp/test.sql
導入數據：
use test;
source /tmp/test.sql

參考：
https://dev.mysql.com/doc/refman/8.0/en/
http://www.rzrgm.cn/martinzhang/p/3454358.html
https://blog.csdn.net/wwwdc1012/article/details/88373440

posted @ 2021-01-13 14:08 朝朝哥閱讀(136) 評論(0) 收藏舉報

刷新頁面返回頂部

俄洛伊

mysql恢復與備份

1. 開啟bin-log

2. 準備試驗數據

3. 恢復數據

3.1 更新日志文件,加只讀鎖

3.2 分析日志文件

3.3 mysqlbinlog還原

3.4.刪除user表后進行還原(通過pos點)

3.5.刪除user表后進行還原(通過時間點)

4. mysqlbinlog,mysqldump

公告