MySQL备份恢复（一）

[root@mysql mysql]# mysqldump -uroot -proot --single-transaction -F test > /tmp/db_test0803.sql
参数说明：
-u：用户名
-p：密码
--single-transaction：生成一致性视图，主要用与InnoDB引擎下
-F：备份完成后，生成新的binlog
复制

1.查看test库下当前的表
root@db 22:08:  [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a              |
| b              |
| cg             |
| cg1            |
| child          |
| person         |
| t              |
| t1             |
| t2             |
| t3             |
| tab_no_index   |
| test           |
| test1          |
| test2          |
| vc             |
+----------------+
15 rows in set (0.00 sec)


2.查看当前表中的数据
root@db 22:11:  [test]> select * from a;
+----+------+
| id | name |
+----+------+
|  1 | x    |
+----+------+
1 row in set (0.00 sec)


root@db 22:11:  [test]> select * from b;
+------+------+
| id   | name |
+------+------+
|    1 | x    |
|    2 | x    |
+------+------+
2 rows in set (0.00 sec)


3.修改数据
root@db 22:11:  [test]> insert into a values (2,'b'),(3,'c'),(4,'d');
root@db 22:11:  [test]> update b set id=3 where id=1;
root@db 22:14:  [test]> delete from b where id=2;
复制

drop database test
复制

1.创建test库
root@db 22:14:  [test]> drop database test;  
2.导入备份数据
[root@mysql mysql]# mysql -uroot -proot  test < /tmp/db_test0803.sql 
3.检查恢复情况
[root@mysql mysql]# mysql -uroot -proot test
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 72
Server version: 5.7.26-log MySQL Community Server (GPL)


Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


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


root@db 22:20:  [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a              |
| b              |
| cg             |
| cg1            |
| child          |
| person         |
| t              |
| t1             |
| t2             |
| t3             |
| tab_no_index   |
| test           |
| test1          |
| test2          |
| vc             |
+----------------+
15 rows in set (0.00 sec)


root@db 22:20:  [test]> select * from a;
+----+------+
| id | name |
+----+------+
|  1 | x    |
+----+------+
1 row in set (0.00 sec)


root@db 22:20:  [test]> select * from b;
+------+------+
| id   | name |
+------+------+
|    1 | x    |
|    2 | x    |
+------+------+
2 rows in set (0.00 sec)
--可以看到前面对表a、b的操作还没恢复。
ps:生产数据库密码请尽量复杂
复制

1.查看当前binlog
root@db 22:23:  [test]> show master status;
+-----------------+----------+--------------+------------------+-----------------------------------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                             |
+-----------------+----------+--------------+------------------+-----------------------------------------------+
| mybinlog.000048 | 20400970 |              |                  | 62bd8118-967d-11e9-acf1-08002778eef2:1-210142 |
+-----------------+----------+--------------+------------------+-----------------------------------------------+
1 row in set (0.00 sec)


2.查看binlog对应事件，确定起始和结束的pos号
root@db 22:25:  [test]> show binlog events in 'mybinlog.000047';
+-----------------+------+----------------+-----------+-------------+------------------------------------------------+
| Log_name        | Pos  | Event_type     | Server_id | End_log_pos | Info                                           |
+-----------------+------+----------------+-----------+-------------+------------------------------------------------+
| mybinlog.000047 |    4 | Format_desc    |   3306101 |         123 | Server ver: 5.7.26-log, Binlog ver: 4          |
| mybinlog.000047 |  123 | Previous_gtids |   3306101 |         194 | 62bd8118-967d-11e9-acf1-08002778eef2:27-210142 |
| mybinlog.000047 |  194 | Anonymous_Gtid |   3306101 |         259 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'           |
| mybinlog.000047 |  259 | Query          |   3306101 |         331 | BEGIN                                          |
| mybinlog.000047 |  331 | Table_map      |   3306101 |         378 | table_id: 217 (test.a)                         |
| mybinlog.000047 |  378 | Write_rows     |   3306101 |         434 | table_id: 217 flags: STMT_END_F                |
| mybinlog.000047 |  434 | Xid            |   3306101 |         465 | COMMIT * xid=1337 */                          |
| mybinlog.000047 |  465 | Anonymous_Gtid |   3306101 |         530 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'           |
| mybinlog.000047 |  530 | Query          |   3306101 |         602 | BEGIN                                          |
| mybinlog.000047 |  602 | Table_map      |   3306101 |         649 | table_id: 218 (test.b)                         |
| mybinlog.000047 |  649 | Update_rows    |   3306101 |         699 | table_id: 218 flags: STMT_END_F                |
| mybinlog.000047 |  699 | Query          |   3306101 |         772 | COMMIT                                         |
| mybinlog.000047 |  772 | Anonymous_Gtid |   3306101 |         837 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'           |
| mybinlog.000047 |  837 | Query          |   3306101 |         909 | BEGIN                                          |
| mybinlog.000047 |  909 | Table_map      |   3306101 |         956 | table_id: 218 (test.b)                         |
| mybinlog.000047 |  956 | Delete_rows    |   3306101 |         998 | table_id: 218 flags: STMT_END_F                |
| mybinlog.000047 |  998 | Query          |   3306101 |        1071 | COMMIT                                         |
| mybinlog.000047 | 1071 | Rotate         |   3306101 |        1117 | mybinlog.000048;pos=4                          |
+-----------------+------+----------------+-----------+-------------+------------------------------------------------+
18 rows in set (0.00 sec)
--确定操作起始pos为259，结束pos为1071
3.恢复数据
[root@mysql mysql]# mysqlbinlog --start-position=259 --stop-position=1071 data/mysql/mybinlog.000047 |


4.检查恢复情况
root@db 23:27:  [test]> select * from a;
+----+------+
| id | name |
+----+------+
|  1 | x    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+
4 rows in set (0.00 sec)


root@db 23:28:  [test]> select * from b;
+------+------+
| id   | name |
+------+------+
|    3 | x    |
+------+------+
1 row in set (0.00 sec)
复制