Mysql数据库异地备份
Mysql数据库异地备份
一、 简介
1.1 增量备份简介
增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。这种备份方式最显著的优点就是：没有重复的备份数据，因此备份的数据量不大，备份所需的时间很短。但增量备份的数据恢复是比较麻烦的。您必须具有上一次全备份和所有增量备份磁带（一旦丢失或损坏其中的一个增量，就会造成恢复的失败），并且它们必须沿着从全备份到依次增量备份的时间顺序逐个反推恢复，因此这就极大地延长了恢复时间。
假如我们有一个数据库，有20G的数据，每天会增加10M的数据，数据库每天都要全量备份一次，这样的话服务器的压力比较大，因此我们只需要备份增加的这部分数据，这样减少服务器的负担。
1.2 binlog简介
binlog日志由配置文件的log-bin选项负责启用，Mysql服务器将在数据根目录创建两个新文 件XXX-bin.001和xxx-bin.index，若配置选项没有给出文件名，Mysql将使用主机名称命名这两个文件，其中.index文件包含一份全体日志文件的清单。 Mysql会把用户对所有数据库的内容和结构的修改情况记入XXX-bin.n文件，而不会记录 SELECT和没有实际更新的UPDATE语句。
当MySQL数据库停止或重启时，服务器会把日志文件记入下一个日志文件，Mysql会在重启时生成一个新的binlog日志文件，文件序号递增，此外，如果日志文件超过max_binlog_size系统变量配置的上限时，也会生成新的日志文件。
二、 mysql的binlog操作
2.1开启
修改mysql的my.cnf(Linux)或者my.ini(Windows)文件
[mysqld]
#skip-grant-tables
lower_case_table_names = 1 #set case insensitivity
log-bin = logbin.log #log-bin若不显示制定存储目录,则默认存储在mysql的data目录下
log-bin-index = logindex
expire_logs_days = 7#日志保留天数为七天
server-id=1 #在设置log-bin的同时需要设置server-id,否则会报错
binlog_format=row #row基于行的存储,启动后会产生mysql-bin.*这样的文件,每启动一次就会增加一个
【存在的问题】在配置的过程中尝试使用自定义路径，但是重启mysql一直报错，未找到原因，所以咱配置默认的目录下。
【补充】log-bin若不显示指定存储目录，则默认存储在mysql的data目录下
binlog_format的几种格式：（STATEMENT，ROW和MIXED）
- STATEMENT：基于SQL语句的复制(statement-based replication, SBR)
- ROW：基于行的复制(row-based replication, RBR)
- MIXED：混合模式复制(mixed-based replication, MBR)
启动后会产生logbin.log.*这样的文件，每启动一次，就会增加一个或者多个。
2.2 查看binlog的开启情况
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%log_bin%';
+---------------------------------+-------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/logbin |
| log_bin_index | /var/lib/mysql/logindex.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------------------------------+
6 rows in set (0,01 sec)
mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| log_statements_unsafe_for_binlog | ON |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 104857600 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 1 |
+-----------------------------------------+----------------------+
20 ows in set (0,00 sec)
2.3 查看binlog中的内容
aaa@qq.com:/var/lib/mysql# mysqlbinlog logbin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @aaa@qq.com@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#171026 15:41:32 server id 1 end_log_pos 123 CRC32 0x8b1b5abe Start: binlog v 4, server v 5.7.20-0ubuntu0.16.04.1-log created 171026 15:41:32 at startup
ROLLBACK/*!*/;
BINLOG '
rJHxWQ8BAAAAdwAAAHsAAAAAAAQANS43LjIwLTB1YnVudHUwLjE2LjA0LjEtbG9nAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACskfFZEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
Ab5aG4s=
'/*!*/;
# at 123
#171026 15:41:32 server id 1 end_log_pos 154 CRC32 0xbfc1fab3 Previous-GTIDs
# [empty]
# at 154
#171026 15:41:39 server id 1 end_log_pos 177 CRC32 0x6d390fdc Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET aaa@qq.com_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
信息描述：
Position:位于文件中的位置，即第一行的（#at 4）和第二行的（log_pos 4），说明该事件记录从文件第4字节开始。
Timestamp:事件发生的时间戳，即第二行的（#070813 14:16:36）
Exec_time:事件的执行花费时间
Error_code:错误码
Type 事件类型：
Master ID:创建二进制事件的主机服务器ID
Master Pos:事件在原始二进制文件中的位置
Flags:标志信息
2.4 数据库的常用操作
• mysql> show master logs; #查看数据库所有日志文件。
• mysql> show binlog events \g; #查看当前使用的binlog文件信息。
• mysql> show binlog events in ‘mysql-bin.000016’; #查看指定的binlog文件信息。
• mysql> flush logs; #将内存中log日志写磁盘，保存在当前binlog文件中，并产生一个新的binlog日志文件。
• mysql> flush logs; reset master; #删除所有二进制日志，并重新（mysql-bin.000001）开始记录。
三、 脚本编写
3.1 全量备份脚本
运行source databak.sh，进行全量备份，其中bucket_water_db为数据库的名称，/opt/mysql_log_data为数据库的备份路径，’date+%Y%m%d’.sql为备份的数据库脚本的名称。后面的两行是定期删除备份内容的实现，实际中可以删除。
3.2 增量备份脚本
运行binlogbak.sh脚本，source binlogbak.sh。其中BakDir的位置设置为本地要存储的位置，BinDir的位置设置为数据库数据存放的文件（logbin文件的存放路径），LogFile为binlog.log存放的位置+文件名，BinFile为index文件的绝对路径。在修改时只修改这四个参数就可以。
四、 定时器开启
在终端输入命令：vi /etc/crontab
在最后一行添加
00 00 * * * /home/yyd/data_backup/binlogbak.sh >/dev/null 2>&1
保证每天的0点0分执行脚本，【00 00 * * *】分别对应分 时 周 月 年，【/home/yyd/data_backup/binlogbak.sh】为脚本存放的绝对路径。
输入以下命令使定时器生效。
crontab /etc/crontab
查看定时器是否生效
crontab –l
五、 数据库的还原
5.1 全量数据的恢复
方法1：进入数据库，通过source
- mysql> use backup_full;
- mysql> source /tmp/backup_full.sql;
- mysql> select * from backup_full.full;
- ±----------±---------+
- | c1 | c2 |
- ±----------±----------+
- | 1 | full1 |
- | 2 | full2 |
- | 3 | full3 |
- | 4 | full4 |
- | 5 | full5 |
- | 6 | full6 |
- | 7 | full7 |
- | 8 | full8 |
- | 9 | full9 |
- | 10 | full10 |
- ±----------±----------+
全量数据恢复成功。
5.2 直接还原数据文件
以此类推全部回复完成
5.3 借助position来还原
那么如何借助position来还原操作呢？
假设我们现在向 testdb 数据库下的表chinesetset中插入数据段(‘游戏’,‘男’,‘2017-4-3’)，然后再删除这一段数据，再借助mysqlbinlog恢复到删除之前的状态~
插入数据段：
可见插入新的数据段在位置戳2285到2564之间，现在我们删掉(‘游戏’,‘男’,‘2017-4-3’) ：
用mysqlbinlog恢复到删除数据段之前：
mysqlbinlog --start-position = P1 --stop-position=P2 E:/hostname.00000x >E:/filename.sql
mysql -u root -pdatabase_name <E:/filename.sql
P1,P2为E:/hostname.00000x中位置戳的开始点和结束点，该语句就是将P1到P2之间的操作转存为 E:/filename.sql ，注意此时要指明logbin的文件路径。实践走一波：
最后要说的是，mysqlbinlog还有有很多用法，比如 ：利用时间戳，利用xid等