mysql数据库备份

痛定思痛。 2022-03-30 04:07 290阅读 0赞

#### 1.技术应用背景 ####

公司新添加2台服务器，出于高可用、宕机容错多因素考虑，把当前的单节点服务或单服务器集群服务变更成多服务器集群，非运维的开发记录自己的摸索过程。

#### 2.1 canal技术 ####

先调研了canal技术，发现并不是mysql数据库备份！

###### mysql主从复制实现： ######

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21zdDEwMTA_size_16_color_FFFFFF_t_70]  
实现过程：

> ① master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，可以通过show binlog events进行查看）；  
> ② slave将master的binary log events拷贝到它的中继日志(relay log)；  
> ③ slave重做中继日志中的事件，将改变反映它自己的数据。

###### canal的工作原理： ######

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21zdDEwMTA_size_16_color_FFFFFF_t_70 1]  
实现过程：

> ① canal模拟mysql slave的交互协议，伪装自己为mysql slave，向mysql master发送dump协议  
> ② mysql master收到dump请求，开始推送binary log给slave(也就是canal)  
> ③ canal解析binary log对象(原始为byte流)

到这，就可以发现canal的功能是伪装成mysql slave,获取到日志文件，通过某些逻辑，实现把数据备份到redis、kafka等。

#### 2.2 mysql主从备份 ####

[点我！—>官方文档][Link 1]

###### 1.主服务器配置 ######

1.  修改配置文件

> ① 必须在主服务器上启用 二进制日志记录，因为二进制日志是将主服务器复制到其从服务器的基础。如果主服务器未启用log-bin 选项，则无法进行复制。  
> ②必须使用唯一的服务器ID配置复制组中的每个服务器。此ID用于标识组内的各个服务器，并且必须是介于1和（2 ^32）-1 之间的正整数。

关闭MySQL服务器并编辑my.cnf或 my.ini文件。在\[mysqld\]配置文件的 部分中，添加log-bin和 server-id选项。如果这些选项已经存在，但已被注释掉，需要取消注释选项并根据需要更改它们。  
例:

log-bin=mysql-bin
    server-id=1

进行更改后，重新启动服务器。

> **注意**：  
> ① 如果省略server-id（或将其明确设置为默认值0），则主服务器拒绝来自从服务器的任何连接。  
> ② 为了在使用InnoDB的事务复制中获得最大的持久性和一致性，在主服务器my.cnf文件中使用 innodb\_flush\_log\_at\_trx\_commit=1 和sync\_binlog=1  
> ③ 确保skip-networking未在复制主服务器上启用该选项。如果已禁用网络，则从属设备无法与主服务器通信，并且复制失败。

1.  创建复制账户

虽然不必专门为复制创建帐户，但建议创建一个仅具有复制权限的单独帐户，以最大程度地降低对其他帐户的危害。

mysql> CREATE USER 'username'@'登录主机' IDENTIFIED BY 'password';
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'username'@'登录主机';

1.  获取当前日志坐标

要在从服务器配置正确的位置启动复制过程，您需要在其二进制日志中记录主服务器的当前坐标。

> ① 执行 FLUSH TABLES WITH READ LOCK 语句来刷新所有表和阻止写语句(阻止InnoDB表的提交操作)  
> mysql> FLUSH TABLES WITH READ LOCK;  
> ② 使用该 SHOW MASTER STATUS语句确定当前二进制日志文件的名称和位置：  
> mysql > SHOW MASTER STATUS;  
> ±-----------------±---------±-------------±-----------------+  
> | File | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB |  
> ±-----------------±---------±-------------±-----------------+  
> | mysql-bin.000003 | 73 | test | manual,mysql |  
> ±-----------------±---------±-------------±-----------------+

File列显示日志文件的名称，Position列显示二进制日志文件的位置。在此示例中，二进制日志文件是mysql-bin.000003，位置为73。记录这些值。在以后设置slave时需要它们，表示从服务器应从主服务器开始处理更新的复制坐标。  
如果主服务器之前一直在运行而未启用二进制日志记录，则 SHOW MASTER STATUS 显示的日志文件名和位置值为空。在指定从属日志文件和位置时需要使用的值是空字符串（’ '）和4。

1.  创建数据快照

如果主数据库包含现有数据，则必须将此数据复制到每个从站。

shell> mysqldump --all-databases --master-data > dbdump.db

> **注意：**  
> 如果不使用 --master-data，则需要手动锁定单独会话中的所有表。

1.  释放读锁

mysql> UNLOCK TABLES;

###### 2.从服务器配置 ######

1.  修改配置文件

每个复制从站必须具有唯一的服务器ID。如果未设置从属服务器ID，或者当前值与主服务器配置的值冲突，则关闭从属服务器并编辑配置文件的\[mysqld\]部分，指定唯一的服务器ID。  
例:

server-id=2

> ① 如果设置多个从站，则每个从站具有的server-id必须与主站和其他任何从站的唯一值不同。如果省略server-id（或将其设置为默认值0），则从站拒绝连接到主站。  
> ② 不必在复制的从站上启用二进制日志记录。如果在从站上启用二进制日志记录，则可以使用从站的二进制日志进行数据备份和崩溃恢复，还可以将从站用作更复杂的复制拓扑的一部分。如，此从站充当其他从站的主站。

进行更改后，重新启动服务器。

1.  设置主站配置

要将从站设置为与主站通信以进行复制，使用必要的连接信息配置从站。

mysql> CHANGE MASTER TO
        ->     MASTER_HOST='master_host_name',
        ->     MASTER_PORT=master_port_num,
        ->     MASTER_USER='replication_user_name',
        ->     MASTER_PASSWORD='replication_password',
        ->     MASTER_LOG_FILE='recorded_log_file_name',
        ->     MASTER_LOG_POS=recorded_log_position;

1.  从站数据导入

> ① 使用–skip-slave-start 选项启动slave，这样复制就不会启动。  
> ② 导入主站已存在的数据(可以使用工具，mysqldump等)

1.  启动从站

mysql> START SLAVE;

查看启动状态：

mysql > SHOW SLAVE STATUS;

Slave\_IO\_Running 值yes,则表示 I/O线程被启动并成功地连接到主服务器上。 Slave\_SQL\_Running值yes,则表示SQL线程是否被启动。否则，启动失败！[解决示例][Link 2] [其他方案][Link 3]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21zdDEwMTA_size_16_color_FFFFFF_t_70]: /images/20220330/28e6cee3b25441bda7209f95ec16b97a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21zdDEwMTA_size_16_color_FFFFFF_t_70 1]: /images/20220330/af22e9bf3da34ed1b38e35f87420eedc.png
[Link 1]: https://dev.mysql.com/doc/refman/5.7/en/replication-howto.html
[Link 2]: https://www.cnblogs.com/byfboke/articles/9120672.html
[Link 3]: http://www.baidu.com