mysql异地备份原理_Mysql数据库异地备份-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_29128423/article/details/113588457

本文介绍了MySQL数据库异地备份的重要性，详细解析了增量备份的概念和binlog的工作原理。通过开启binlog、查看binlog状态及内容，阐述了如何利用binlog进行数据库的全量和增量备份，并提供了相关脚本示例。同时，文章还讨论了数据库的恢复方法，包括全量恢复和基于position的恢复策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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 |

+-----------------------------------------+----------------------+

20ows in set (0,00 sec)

2.3 查看binlog中的内容

root@yyd-Lenovo:/var/lib/mysql# mysqlbinlog logbin.000001

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@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 COMPLETION_TYPE=@OLD_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文件的绝对路径。在修改时只修改这四个参数就可以。