Mysql备份和恢复

最新推荐文章于 2024-09-18 15:54:57 发布

ckth47350

最新推荐文章于 2024-09-18 15:54:57 发布

阅读量81

点赞数

文章标签：数据库

Mysql备份和恢复

一：mysqldump常见备份和恢复方式
二：完全恢复
三：不完全恢复
(1) 基于时间点恢复
(2) 基于位置恢复

一：mysqldump常见备份和恢复方式
逻辑备份工具：mysqldump
查看帮助信息：mysqldump --help

创建测试数据库
mysql> create database test;
mysql> create table emp(a int,b varchar(10));
insert into emp values(1,'z1');
insert into emp values(2,'z2');
insert into emp values(3,'z3');
mysql> create table dept(a int,b varchar(10));
insert into dept values(1,'a1');
insert into dept values(2,'a2');
insert into dept values(3,'a3');

---1 备份所有数据库
mysqldump -uroot -p --all-databases > D:\mysql\backup\all.sql
---2 备份数据库test
mysqldump -uroot -p test > D:\mysql\backup\test.sql
---3 备份数据库test下的表emp
mysqldump -uroot -p test emp > D:\mysql\backup\emp.sql
---4 备份备份数据库test下的表emp和dept
mysqldump -uroot -p test emp dept > D:\mysql\backup\emp_dept.sql
---5 备份数据库test下的所有表为逗号分割的文本，备份到D:\mysql\backup，需要提前设置好secure-file-priv参数；
mysqldump -uroot -p -T D:\mysql\backup test --fields-terminated-by ','

二：完全恢复

---16:52备份
mysqldump -uroot -p -l -F test > test.sql
其中-l参数表示给所有表加读锁，-F表示生成一个新的日志文件，此时，test中emp表的数据如下：
mysql> select * from emp order by a;
+------+------+
| a | b |
+------+------+
| 1 | z1 |
| 2 | z2 |
| 3 | z3 |
| 4 | z4 |
+------+------+
4 rows in set (0.02 sec)

---16:57点插入新数据：
insert into emp values(5,'z5');
insert into emp values(6,'z6');
mysql> select * from emp order by a;
+------+------+
| a | b |
+------+------+
| 1 | z1 |
| 2 | z2 |
| 3 | z3 |
| 4 | z4 |
| 5 | z5 |
| 6 | z6 |
+------+------+
6 rows in set (0.00 sec)

---17点，数据库突然故障，数据无法访问。需要恢复备份：
mysql> flush logs; ---手动切一下日志，将下面数据库恢复产生的SQL写入到下一个日志组里；
mysql -uroot -p test < test.sql
恢复后的数据如下：
---只恢复了备份时刻的数据
mysql> select * from emp order by a;
+------+------+
| a | b |
+------+------+
| 1 | z1 |
| 2 | z2 |
| 3 | z3 |
| 4 | z4 |
+------+------+
4 rows in set (0.00 sec)
---使用mysqlbinlog恢复自mysqldump备份以来的BINLOG
mysql> show master status;
---file mysql-bin.000005
D:\mysql\mysql-5.7.22-winx64\bin>mysqlbinlog D:\mysql\mysql-5.7.22-winx64\data\mysql-bin.000004|mysql -u root -p test
Enter password: ***
---查询完全恢复的数据如下：
mysql> use test
Database changed
mysql> select * from emp;
+------+------+
| a | b |
+------+------+
| 1 | z1 |
| 2 | z2 |
| 3 | z3 |
| 4 | z4 |
| 5 | z5 |
| 6 | z6 |
+------+------+
6 rows in set (0.00 sec)

三：不完全恢复
(1) 基于时间点恢复
(2) 基于位置恢复

(1) 基于时间点恢复
---不完全恢复
恢复到无操作之前的状态，然后跳过误操作语句，在恢复后面执行的语句，完成我们的恢复
---备份
---9:50
mysqldump -uroot -p -l -F test > test0621.sql
其中-l参数表示给所有表加读锁，-F表示生成一个新的日志文件，此时，test中dept表的数据如下：
mysql> use test
Database changed
mysql> select * from dept;
+------+------+
| a | b |
+------+------+
| 1 | a1 |
| 2 | a2 |
| 3 | a3 |
+------+------+
3 rows in set (0.00 sec)

---9:56
mysql>
insert into dept values(4,'a4');
insert into dept values(5,'a5');
insert into dept values(6,'a6');
mysql> select * from dept;
+------+------+
| a | b |
+------+------+
| 1 | a1 |
| 2 | a2 |
| 3 | a3 |
| 4 | a4 |
| 5 | a5 |
| 6 | a6 |
+------+------+
6 rows in set (0.00 sec)

---10:00
mysql> delete from dept;
Query OK, 6 rows affected (0.09 sec)

mysql> select * from dept;
Empty set (0.00 sec)

---10:03
mysql> create table t1(a int,b varchar(10));
insert into t1 values(1,'c1');
insert into t1 values(2,'z2');
insert into t1 values(3,'z3');
mysql> select * from t1;
+------+------+
| a | b |
+------+------+
| 1 | c1 |
| 2 | z2 |
| 3 | z3 |
+------+------+
3 rows in set (0.00 sec)

mysql> flush logs; ---手动切一下日志，将下面数据库恢复产生的SQL写入到下一个日志组里；
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 5599 |
| mysql-bin.000004 | 721 |
| mysql-bin.000005 | 2876 |
| mysql-bin.000006 | 201 |
| mysql-bin.000007 | 2237 |
| mysql-bin.000008 | 154 |
+------------------+-----------+
8 rows in set (0.00 sec)

mysql -uroot -p test < test0621.sql
mysql> select * from dept;
+------+------+
| a | b |
+------+------+
| 1 | a1 |
| 2 | a2 |
| 3 | a3 |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from t1;
+------+------+
| a | b |
+------+------+
| 1 | c1 |
| 2 | z2 |
| 3 | z3 |
+------+------+
3 rows in set (0.00 sec)

如果上午10点发送误操作，可以用以下语句用备份和BINLOG将数据恢复到故障前：
mysqlbinlog --stop-datetime="2018-06-21 09:57:10" D:\mysql\mysql-5.7.22-winx64\data\mysql-bin.000007| mysql -u root -p test
跳过故障时的时间点，继续执行后面的BINLOG，完成恢复。
mysqlbinlog --start-datetime="2018-06-21 10:02:00" D:\mysql\mysql-5.7.22-winx64\data\mysql-bin.000007| mysql -u root -p test
Enter password: ***
ERROR 1050 (42S01) at line 26: Table 't1' already exists

mysql> select * from dept;
+------+------+
| a | b |
+------+------+
| 1 | a1 |
| 2 | a2 |
| 3 | a3 |
| 4 | a4 |
| 5 | a5 |
| 6 | a6 |
+------+------+
6 rows in set (0.00 sec)

(2) 基于位置恢复
和基于时间点的恢复类似，但是更精确，因为同一个时间点可能有很多条SQL语句同时执行。恢复的操作步骤如下。
mysql> create table t2(a int,b varchar(10));
insert into t2 values(1,'c1');
insert into t2 values(2,'z2');
insert into t2 values(3,'z3');
mysql> select * from t2;
+------+------+
| a | b |
+------+------+
| 1 | c1 |
| 2 | z2 |
| 3 | z3 |
+------+------+
3 rows in set (0.00 sec)

---10:32
mysqldump -uroot -p -l -F test > test0621a.sql

---10:44
insert into t2 values(4,'a4');
insert into t2 values(5,'a5');
insert into t2 values(6,'a6');

mysql> select * from t2;
+------+------+
| a | b |
+------+------+
| 1 | c1 |
| 2 | z2 |
| 3 | z3 |
| 4 | a4 |
| 5 | a5 |
| 6 | a6 |
+------+------+
6 rows in set (0.00 sec)

---10:47
mysql> delete from t2;
Query OK, 6 rows affected (0.13 sec)

mysql> select * from t2;
Empty set (0.00 sec)

---10:50
insert into t1 values(4,'c4');
insert into t1 values(5,'c5');
insert into t1 values(6,'c6');

mysql> flush logs; ---手动切一下日志，将下面数据库恢复产生的SQL写入到下一个日志组里；
Query OK, 0 rows affected (0.21 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 5599 |
| mysql-bin.000004 | 721 |
| mysql-bin.000005 | 2876 |
| mysql-bin.000006 | 201 |
| mysql-bin.000007 | 2237 |
| mysql-bin.000008 | 4131 |
| mysql-bin.000009 | 2054 |
| mysql-bin.000010 | 154 |
+------------------+-----------+
10 rows in set (0.00 sec)

mysql -uroot -p test < test0621a.sql
mysql> use test
Database changed

mysql> select * from t2;
+------+------+
| a | b |
+------+------+
| 1 | c1 |
| 2 | z2 |
| 3 | z3 |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from t1;
+------+------+
| a | b |
+------+------+
| 1 | c1 |
| 2 | z2 |
| 3 | z3 |
+------+------+
3 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000009';
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000009 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.22-log, Binlog ver: 4 |
......
| mysql-bin.000009 | 1068 | Table_map | 1 | 1116 | table_id: 114 (test.t2) |
| mysql-bin.000009 | 1116 | Delete_rows | 1 | 1199 | table_id: 114 flags: STMT_END_F |
......

mysqlbinlog --start-datetime="2018-06-21 10:01:00" --stop-datetime="2018-06-21 10:55:00" D:\mysql\mysql-5.7.22-winx64\data\mysql-bin.000009 > sql_resore.sql
---sql_resore.sql
# at 1116
#180621 10:47:56 server id 1 end_log_pos 1199 CRC32 0xa6883df9 Delete_rows: table id 114 flags: STMT_END_F

BINLOG '
3BErWxMBAAAAMAAAAFwEAAAAAHIAAAAAAAEABHRlc3QAAnQyAAIDDwIeAAOaVcAI
3BErWyABAAAAUwAAAK8EAAAAAHIAAAAAAAEAAgAC//wBAAAAAmMx/AIAAAACejL8AwAAAAJ6M/wE
AAAAAmE0/AUAAAACYTX8BgAAAAJhNvk9iKY=
'/*!*/;
# at 1199
#180621 10:47:56 server id 1 end_log_pos 1230 CRC32 0x9e871466 Xid = 264
COMMIT/*!*/;

找出误删除语句前后位置，分别是1116到1199；

恢复
mysqlbinlog --stop-position="1116" D:\mysql\mysql-5.7.22-winx64\data\mysql-bin.000009 |mysql -u root -p test
mysqlbinlog --start-position="1199" D:\mysql\mysql-5.7.22-winx64\data\mysql-bin.000009 |mysql -u root -p test

mysql> use test
Database changed
mysql> select * from t1;
+------+------+
| a | b |
+------+------+
| 1 | c1 |
| 2 | z2 |
| 3 | z3 |
| 4 | c4 |
| 5 | c5 |
| 6 | c6 |
+------+------+
6 rows in set (0.00 sec)

mysql> select * from t2;
+------+------+
| a | b |
+------+------+
| 1 | c1 |
| 2 | z2 |
| 3 | z3 |
| 4 | a4 |
| 5 | a5 |
| 6 | a6 |
+------+------+
6 rows in set (0.00 sec)

------参考《深入浅出mysql》

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-2156430/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-2156430/