mysql数据备份与恢复

备份方式

冷备（cold backup）：停机状态下备份，服务不可用
温备（warm backup）： 服务在线，但仅支持读请求，不允许写请求
热备（hot backup）：备份的同时，业务不受影响
物理备份：直接复制数据文件

备份工具

mysqldump
Percona提供的xtrabackup

mysqldump 命令

# mysqldump --help
# --master-data 0(不记录position) 1(记录position位置) 2(记录position位置并注释该条)
# routines 存储过程
# triggers 触发器
# events 事件
# single-transaction  
# --ignore-table=icoding_admin.ad_user_role --ignore-table=icoding_admin.ad_user
# 基于innodb引擎
mysqldump -uroot -p123456 -h127.0.0.1 --master-data=2 --routines --triggers --events --single-transaction --databases ikang_db --ignore-table=ikang_db.ad_user_role > mydb.sql

场景

模拟一下恢复全量数据、增量数据。步骤如下：

1. 开启数据库binlog，设置为row 模式
2. mysqldump 导出全量数据
3. 增加一写新的数据
4. 删除数据库
5. 从全量数据导入备份前数据
6. 从binlog 中导入增量数据

1. 开启binglog

vim /etc/my.cnf

binlog_format=row
log_bin=ikang_binlog
#sql_log_bin=ON
max_binlog_size=500m
                 

2. 导出数据

 mysqldump -uroot -p --master-data=2  --databases ikang_db > /root/backup/back.sql

3. 插入数据

mysql> insert into ad_role values(13, 'VIPIP');
Query OK, 1 row affected (0.00 sec)

mysql> select * from ad_role;
+----+-----------+
| id | role_name |
+----+-----------+
|  1 | vip1      |
|  2 | vip2      |
|  3 | vip3      |
|  5 | III       |
| 10 | vippp     |
| 11 | vippp     |
| 12 | vippp     |
| 13 | VIPIP     |
+----+-----------+
8 rows in set (0.00 sec)

4. 删除数据库

mysql> drop database ikang_db;
Query OK, 3 rows affected (0.07 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

5.导入全量数据

mysql> source /root/backup/back.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ikang_db           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use ikang_db;
Database changed
mysql> select * from ad_role;
+----+-----------+
| id | role_name |
+----+-----------+
|  1 | vip1      |
|  2 | vip2      |
|  3 | vip3      |
|  5 | III       |
| 10 | vippp     |
| 11 | vippp     |
| 12 | vippp     |
+----+-----------+
7 rows in set (0.01 sec)

全量数据恢复完成，缺少第13 记录，从binlog 恢复

6. 增量数据恢复

• 查看ikang_db.000008 文件
  
• 文件内容查看

 mysqlbinlog --base64-output=decode-rows -vv ikang_binlog.000008; 

通过上面命令，看到binlog 记录了增量insert、drop 操作

• sql视图查看 binlog

mysql> show binlog events in 'ikang_binlog.000008';
+---------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name            | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+---------------------+-----+----------------+-----------+-------------+---------------------------------------+
| ikang_binlog.000008 |   4 | Format_desc    |       123 |         123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| ikang_binlog.000008 | 123 | Previous_gtids |       123 |         154 |                                       |
| ikang_binlog.000008 | 154 | Anonymous_Gtid |       123 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| ikang_binlog.000008 | 219 | Query          |       123 |         295 | BEGIN                                 |
| ikang_binlog.000008 | 295 | Table_map      |       123 |         352 | table_id: 124 (ikang_db.ad_role)      |
| ikang_binlog.000008 | 352 | Write_rows     |       123 |         398 | table_id: 124 flags: STMT_END_F       |
| ikang_binlog.000008 | 398 | Xid            |       123 |         429 | COMMIT /* xid=192 */                  |
| ikang_binlog.000008 | 429 | Anonymous_Gtid |       123 |         494 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| ikang_binlog.000008 | 494 | Query          |       123 |         598 | drop database ikang_db                |
+---------------------+-----+----------------+-----------+-------------+---------------------------------------+
9 rows in set (0.00 sec)

上面试图，看到恢复的数据截止位置是： 494 drop 之前，起始位置，在全量备份中查看

• 导出增量数据

mysqlbinlog --start-position=154 --stop-position=494  ikang_binlog.000008 > add_data.sql

• 导入恢复增量数据

mysql> 
mysql> select * from ad_role;
+----+-----------+
| id | role_name |
+----+-----------+
|  1 | vip1      |
|  2 | vip2      |
|  3 | vip3      |
|  5 | III       |
| 10 | vippp     |
| 11 | vippp     |
| 12 | vippp     |
| 13 | VIPIP     |
+----+-----------+
8 rows in set (0.00 sec)

增量第13条数据恢复，至此被删除的数据全部恢复。