mysql备份(增量备份)

数据库的备份方式:

  • 完全备份  full backup,备份全部字符集。
  • 增量备份 incremental backup 上次完全备份或增量备份以来改变了的数据,不能单独使用,要借助完全备份,备份的频率取决于数据的更新频率。
  • 差异备份 differential backup 上次完全备份以来改变了的数据。

  建议的恢复策略:
  完全+增量+二进制日志
  完全+差异+二进制日志

我们常用的备份方式:

备份整个数据库

$> mysqldump -u root -h host -p dbname > backdb.sql

 

备份数据库中的某个表

$> mysqldump -u root -h host -p dbname tbname1, tbname2 > backdb.sql

 

备份多个数据库

$> mysqldump -u root -h host -p --databases dbname1, dbname2 > backdb.sql

 

备份系统中所有数据库

$> mysqldump -u root -h host -p --all-databases > backdb.sql

 

1. 完全+增量+二进制日志

  在备份前需要献给表加锁

  --lock-all-tables 是对要备份的数据库的所有表施加读锁

  --lock-table 仅对单张表施加读锁,即使是备份整个数据库,它也是在我们备份某张表的时候才对该表施加读锁,因此适用于备份单张表

  首先需要做完全备份:

# sudo mysqldump -uroot --single-transaction --master-data=2 --databases test > /backup/testdb_`date +%F`.sql  # 生成了一个"testdb_2019-11-13.sql"的完全备份文件
  
--single-transaction: 基于此选项能实现热备InnoDB表;因此,不需要同时使用--lock-all-tables;
--master-data=2  记录备份那一时刻的二进制日志的位置,并且注释掉,1是不注释的
--databases hellodb 指定备份的数据库

 

  然后修改数据库表,找到修改后的日志位置:

mysql> insert into people (user_name,age) values('mm',18);

# cat /backup/testdb_2019-11-13.sql | less
找到备份起始位置:
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=960; # 960 是完全备份日志中的结束位置
查看当前日志位置:
mysql>  show master status;

 +------------------+----------+--------------+------------------+-------------------+
 | File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +------------------+----------+--------------+------------------+-------------------+
 | mysql-bin.000002 | 1228   |          |            |            |  # 1228 是插入一条数据后二进制日志当前位置
 +------------------+----------+--------------+------------------+-------------------+

 

  开始生成增量备份:

# sudo mysqlbinlog --start-position 960 --stop-position 1228 /var/log/mysql/mysql-bin.000002 > /backup/testdb_`date +%F_%H`.sql
# ll /backup # 可以看到有一个"testdb_2019-11-13_12.sql"的备份文件

   

再一次插入数据到数据库中
mysql> insert into people (user_name,age) values('gg',18); 关闭二进制日志 mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 1496   |          |            |            |  # 1496 是插入第二条数据后的二进制日志位置
+------------------+----------+--------------+------------------+-------------------+
# mysqlbinlog --start-position 1228 --stop-position 1496 /var/log/mysql/mysql-bin.000002 > /backup/testdb_`date +%F_%H`.sql
# ll /backup # 又多了一个"testdb_2019-11-13_13.sql"文件
  

  可以看到有三个文件,第一个是我们的完全备份文件,第2、3个就是增量备份文件

先让mysql离线:
mysql> set sql_log_bin=0; 关闭二进制日志 mysql> flush logs;      滚动下日志 

 

   模拟数据库损坏:

mysql> drop  database test;  # 删除test数据库

 

   开始恢复数据:

# mysql -uroot -p </backup/testdb_2019-11-13.sql    # 导入完全备份文件
# mysql -uroot -p </backup/testdb_2019-11-13_12.sql  # 导入第一次增量备份
# mysql -uroot -p </backup/testdb_2019-11-13_13.sql  # 导入第二次增量备份

查看是数据是否恢复:
mysql> select * from test.people;
  

  此时的数据库已经完全恢复了,我们新插入的数据也在数据库里。

mysql> set sql_log_bin=1;  # 确认没问题后开启bin日志

 

posted @ 2019-11-21 11:10  阿拉米苏  阅读(641)  评论(0编辑  收藏  举报