【MySQL主从实践】主从备份

怼烎@ 2023-06-10 10:23 13阅读 0赞

### 文章目录 ###

*  1. 环境信息
 *  2. 环境准备
 *   *  2.1. 数据准备
     *   *  2.1.1. 主服务器生成数据快照
         *  2.1.2. 传输数据至从服务器
         *  2.1.3. 从服务器加载数据
 *  3. 配置主服务器
 *   *  3.1. 分配单独账号
     *  3.2. mysql.cnf 配置
     *  3.3. 重启 MySQL 服务
     *  3.4. 查看状态（可省略）
 *  4. 配置从服务器
 *   *  4.1. mysql.cnf 配置
     *  4.2. 重启 MySQL 服务
     *  4.3. 从服务器 “认主”
     *   *  4.3.1. 获取主服务器信息
         *  4.3.2. 停掉 slave 进程
         *  4.3.4. 配置主服务器信息
         *  4.3.5. 开启从服务器进程
         *  4.3.6. 查看从服务器状态
 *  5. 测试
 *   *  5.1. 更新一条数据
     *  5.2. 删库重建
 *  6. 踩坑记录
 *   *  6.1. 服务器端口
     *  6.2. 防火墙
     *  6.3. MySQL 账号
     *  6.4. MySQL 远程连接
     *  6.5. 从服务器 认主 配置
     *  6.6. 网络问题
 *  参考

MySQL主从实践系列文章：

*  [【MySQL主从实践】主从备份][MySQL]
 *  【MySQL主从实践】双机热备
 *  【MySQL主从实践】一主多从
 *  【MySQL主从实践】主从分级架构

# 1. 环境信息 #

**主服务器**

*  阿里云ECS 4 core 16 G
 *  MySQL 5.7

**从服务器**

*  阿里云ECS 2 core 4 G
 *  MySQL 5.7

# 2. 环境准备 #

在主、从服务器均搭建 MySQL 服务器，建议版本一致（要求从服务器 MySQL 版本不低于主服务器）。

参见：[【Linux】初入Ubuntu的Java开发者安装软件手记][Linux_Ubuntu_Java] MySQL 搭建部分

## 2.1. 数据准备 ##

如果不是新创建的数据库（即主服务器已有数据），那么我们需要先将数据手动备份一份至从服务器。

（PS：在进行备份实验的时候，好像可以定位备份的起始点，不知道可否通过这个参数来实现历史数据的备份，尚未实验）

### 2.1.1. 主服务器生成数据快照 ###

通过 mysqldump 将数据生成 .sql 文件。

mysqldump -u userName -p databaseName>target.sql

更多命令参见：[mysql数据库从一台服务器迁移到另一台服务器上][mysql]

### 2.1.2. 传输数据至从服务器 ###

采用 scp 命令在服务器之间进行数据传输

**本地文件拷贝到远程**

scp 文件名 –用户名@计算机IP或者计算机名称:远程路径

**拷贝远程文件到本地**

scp –用户名@计算机IP或者计算机名称:文件名 本地路径

更多命令参见：[两台Linux系统之间传输文件的几种方法][Linux]

### 2.1.3. 从服务器加载数据 ###

a. 登录到 MySQL 命令行界面

shell 脚本：

mysql -u username -p password

b. 创建数据库（和主服务器数据库实例同名）

sql 脚本：

create database databaseName;

c. 选择数据库

不选择数据库，执行 source 命令会报错。原因在于从 mysqldump 命令获取到的 sql 脚本，缺少此步。

use databaseName;

d. 加载数据

采用 source 命令进行加载数据，其实就是直接执行 sql 脚本。

sql 脚本：

source target.sql

其中， target.sql 需要使用全路径

# 3. 配置主服务器 #

## 3.1. 分配单独账号 ##

直接创建用户，并赋值权限即可。

GRANT REPLICATION SLAVE ON *.* to 'slaveUsername'@'%' identified by 'slavePassword';
    FLUSH PRIVILEGES;

## 3.2. mysql.cnf 配置 ##

进行 MySQL 配置文件配置，主要开启 log-bin 功能、允许从服务器连接功能。

mysql.cnf

[mysqld]
    # 启用二进制日志。此日志仅记录事务SQL，即存在数据更改
    log-bin=mysql-bin  
    # 定义 服务器唯一ID。 整个 MySQL 主从架构中唯一
    server-id=1

更多参数可查看 MySQL 官方文档：[MySQL 5.7参考手册][MySQL 5.7]

## 3.3. 重启 MySQL 服务 ##

执行 MySQL 重启命令即可。

shell 脚本：

/etc/init.d/mysql restart

## 3.4. 查看状态（可省略） ##

# 4. 配置从服务器 #

## 4.1. mysql.cnf 配置 ##

# 配置 MySQL 服务 ID， MySQL 主从架构中唯一即可
    server-id=2
    # 备份的数据库实例名称，可配置多个，采用英文半角逗号分隔
    replicate-do-db=test
    skip-slave-start=true

## 4.2. 重启 MySQL 服务 ##

shell 脚本：

/etc/init.d/mysql restart

## 4.3. 从服务器 “认主” ##

### 4.3.1. 获取主服务器信息 ###

到 **主服务器** 获取 bin-log 信息

此步获取数据，**认主** 需要使用

### 4.3.2. 停掉 slave 进程 ###

停掉 从服务器中 slave 进程，如果没有则可跳过此步。

sql 脚本：

stop slave;

### 4.3.4. 配置主服务器信息 ###

在 从服务器 MySQL 命令行进行配置 主服务器 信息。

sql 脚本：

change master to 
    master_host='master ip',
    master_user='slaveUsername',
    master_password='slavePassword',
    master_log_file='bin-log name', 
    master_log_pos=positon;

**参数说明**

master\_host 主服务器 IP  
master\_user 主服务器中申请进行数据备份的用户名称  
master\_password 用户密码  
master\_log\_file “4.3.1. 获取主服务器信息” 中获取到的 bin-log 文件名称  
master\_log\_pos “4.3.1. 获取主服务器信息” 中获取到的 Position 值

以本实验为例：

change master to 
    master_host='127.0.0.1',
    master_user='slave',
    master_password='123456',
    master_log_file='mysql-bin.000003', 
    master_log_pos=970;

### 4.3.5. 开启从服务器进程 ###

MySQL 命令行执行：

sql 脚本：

start slave;

不报错即可，警告可忽略。目前尚未遇见报错，也不知道会报什么错、报错如何解决。 hhh ~ \_ ~

### 4.3.6. 查看从服务器状态 ###

查看从服务器进程状态

sql 脚本：

mysql> show slave status\G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: // 屏蔽敏感信息
                      Master_User: // 屏蔽敏感信息
                      Master_Port: // 屏蔽敏感信息
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000005
              Read_Master_Log_Pos: 39537230
                   Relay_Log_File: iZbp107uqxtgptoycocm3wZ-relay-bin.000012
                    Relay_Log_Pos: 39537443
            Relay_Master_Log_File: mysql-bin.000005
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: // 屏蔽敏感信息
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 39537230
                  Relay_Log_Space: 39537715
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: // 屏蔽敏感信息
                 Master_Info_File: // 屏蔽敏感信息
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)

上文中以屏蔽敏感信息，实际情况按照自己服务器信息即可。

重点看两个指标，下列两个指标均为 Yes 即为配置成功。

Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

**如果没有成功，参见下边踩坑记录**

# 5. 测试 #

在主服务器进行数据变更，查看从服务器是否完成数据更新。

## 5.1. 更新一条数据 ##

右边为 master ， 左边为 slave。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5zYW53YQ_size_16_color_FFFFFF_t_70]

## 5.2. 删库重建 ##

我进行了删除主服务器的数据库实例，然后重新创建同名数据库实例，插入新数据。

**测试结果**

主服务器删除数据库实例后，从服务器随之进行了删除对应数据库实例。  
主服务器重新创建数据库实例并进行数据插入操作，从数据库实例同样进行了数据库实例创建及数据更新。

结果表示，主数据库实例删除并不会对于 MySQL 主从备份产生影响，即 bin-log 文件存在。

MySQL 数据库对于主从备份应该是单独处理，并不与数据库实例生命周期挂钩。

# 6. 踩坑记录 #

## 6.1. 服务器端口 ##

服务器端口需要打开，以 3306 为例，在服务器配置安全组解决

## 6.2. 防火墙 ##

服务器端口打开后，需要在防火墙进行开启。以 3306 为例。

如果服务器尚未安装防火墙，先进行安装。

apt 方式安装：

sudo apt-get install firewalld

shell 脚本

#查看防火墙中端口使用情况：
    sudo firewall-cmd --query-port=3306/tcp
    #防火墙中添加端口
    sudo firewall-cmd --permanent --zone=public --add-port=3306/tcp
    #关闭防火墙
    sudo systemctl stop firewalld.service
    #开启防火墙
    sudo systemctl start firewalld.service

## 6.3. MySQL 账号 ##

确认主服务器 MySQL 数据库配置的用户名、密码是否能正常进行连接

## 6.4. MySQL 远程连接 ##

确认 主服务器 是否允许 远程访问，可通过配置 /etc/mysql/mysql.conf.d/mysqld.cnf 文件

bind-address            = 0.0.0.0

## 6.5. 从服务器 认主 配置 ##

确认 从服务器 **认主** 配置中，IP、用户名、密码、bin-log 文件名是否正确

## 6.6. 网络问题 ##

主、从服务器相互 ping

# 参考 #

*  [MySQL 5.7参考手册][MySQL 5.7]
 *  [MySQL 主从复制，双机热备][MySQL 1]
 *  [MySQL主从备份配置][MySQL 2]
 *  [mysql数据库从一台服务器迁移到另一台服务器上][mysql]
 *  [mysql主从同步从库上Slave\_IO\_Running: Connecting问题][mysql_Slave_IO_Running_ Connecting]
 *  [两台Linux系统之间传输文件的几种方法][Linux]

欢迎留言评论，如有错误欢迎指正，谢谢。

[MySQL]: https://blog.csdn.net/chensanwa/article/details/102762933
[Linux_Ubuntu_Java]: https://blog.csdn.net/chensanwa/article/details/83144546
[mysql]: https://blog.csdn.net/LJFPHP/article/details/78691875
[Linux]: https://blog.csdn.net/gatieme/article/details/51673229
[MySQL 5.7]: http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5zYW53YQ_size_16_color_FFFFFF_t_70]: /images/20230531/cee4d71713064d9488c0b27816da4af8.png
[MySQL 1]: https://juejin.im/post/5ca2f93cf265da307261fae1
[MySQL 2]: https://www.jianshu.com/p/1eed312e83bf
[mysql_Slave_IO_Running_ Connecting]: https://blog.51cto.com/8999a/1966548