【Mysql】Docker下Mysql8数据备份与恢复

[TOC]

【Mysql】Docker下Mysql8数据备份与恢复

1 创建Mysql容器

• 格式

  登录后复制

  docker run -d
  --name=容器名称 
  -p 宿主端口号:3306 
  -e MYSQL_ROOT_PASSWORD=mysql密码 
  -e MYSQL_PASSWORD=mysql密码
  -e TZ=Asia/Shanghai
  -v 宿主目录-数据:/var/lib/mysql 
  -v 宿主目录-备份数据:/backup 
  --restart always 
  mysql:版本号
  

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.

• 示例

  登录后复制

  docker run -d --name=mysql-8 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_PASSWORD=123456 -e TZ=Asia/Shanghai -v /workspace/mysql-8:/var/lib/mysql -v /workspace/mysql-8-backup:/backup --restart always mysql:8.0
  

  • 1.

2 连接Mysql

• Navicat

  

  • 创建SQL

    登录后复制

    -- 删除数据库
    DROP DATABASE IF EXISTS `test`;
    -- 创建数据库
    CREATE DATABASE IF NOT EXISTS `test`;
    -- 进入数据库
    use `test`;
    -- 创建表
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student` (
      `id` bigint NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `age` int DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    -- 插入数据
    INSERT INTO `student` (`id`, `name`,`age`) VALUES (1, '小红', '20');
    INSERT INTO `student` (`id`, `name`,`age`) VALUES (2, '小明', '21');

    • 1.
    • 2.
    • 3.
    • 4.
    • 5.
    • 6.
    • 7.
    • 8.
    • 9.
    • 10.
    • 11.
    • 12.
    • 13.
    • 14.
    • 15.
    • 16.
    • 17.

3 Binlog检查

3.1 检查是否开启

登录后复制

SHOW VARIABLES like 'log_bin';

• 1.

• ON : 开启

• OFF : 关闭

3.2 mysql 5.7

• 找到 my.cnf

  登录后复制

  find / -name my.cnf

  • 1.
  • docker 位置： /etc/my.cnf

• 开启

  登录后复制

  [mysqld]
  log_bin = mysql-bin
  server_id = 1   # 设置MySQL实例的唯一ID，如果有多个MySQL实例，每个实例的ID必须不同

  • 1.
  • 2.
  • 3.

3.3 mysql8

• 默认开启

4 备份数据库

4.1 容器里执行备份

• 进入容器

  登录后复制

  docker exec -ti mysql-8 /bin/bash

  • 1.

• 在容器内创建备份目录

  前面创建容器时已创建，没有则执行下面

  登录后复制

  mkdir /backup

  • 1.

• 全量备份SQL

  登录后复制

  mysqldump --single-transaction --flush-logs --source-data=2 --routines --opt -uroot -p123456 test > /backup/test_20240713.sql

  • 1.

4.2 宿主机执行备份

• 格式

  登录后复制

  docker exec 容器名 bash -c '命令1 && 命令2'

  • 1.

• 示例

  登录后复制

  docker exec mysql-8 bash -c 'mysqldump --single-transaction --flush-logs --source-data=2 --routines --opt -uroot -p123456 test > /backup/test_20240713.sql'

  • 1.

4.3 参数说明

• mysqldump: 这是 mysqldump 命令的执行路径或者别名，用于备份 MySQL 数据库。
• --single-transaction: 在导出数据时使用单个事务，这样可以确保导出的数据是一个一致性状态的快照，避免了在备份过程中数据变化引起的问题。
• --flush-logs: 在备份完成后刷新日志文件，这样可以确保备份包含了最新的日志记录。
• --source-data=2: 指定数据来源的格式，这里是指定为默认格式 2，表示在备份过程中，将主服务器的二进制日志位置信息也备份到文件中。这个参数比较重要，用于在恢复数据时找到位置信息。
  • Mysql 8 : --source-data=2
  • Mysql 5.7 : --master-data=2
• --routines: 备份存储过程和函数。
• --opt: 表示使用优化过的备份方式，以提高备份速度和减少备份文件的大小。
• -uroot: 使用 root 用户连接到 MySQL 数据库。
• -p: 提示输入密码。
• test: 要备份的数据库名称，这里假设要备份的数据库名为 test。
• > backup.sql: 将导出的 SQL 数据写入到 backup.sql 文件中。

5 定时备份

使用crontab定时任务来实现定时备份功能

• 新建个shell脚本mysql-backup.sh

  登录后复制

#!/bin/bash
docker exec mysql-8 sh -c ‘mysqldump --single-transaction --flush-logs --source-data=2 --routines --opt -uroot -pn2wqeGrlv7 test > /backup/test_$(date +%Y%m%d_%H%M%S).sql’

登录后复制

* 进入crontab

• 1.
• 2.
• 3.

crontab -e

登录后复制

* 添加到定时任务

* 每1分钟生效

  ```
  # mysql备份-每1分钟
  */1 * * * * /workspace/cron-jobs/mysql-backup.sh >> /workspace/cron-jobs/mysql-backup.log 2>&1
  ```

* 每天凌晨3点生效

  ```
  mysql备份-每天凌晨3点
  0 3 * * * /workspace/cron-jobs/mysql-backup.sh >> /workspace/cron-jobs/mysql-backup.log 2>&1
  ```

* 保存即时生效

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.

按 shift + :

输入 wq

登录后复制

# 6 恢复数据

* 容器内

格式：`mysql -uroot -p密码 数据库 < 备份数据库SQL文件路径`

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

mysql -uroot -p123456 test < /backup/test_20240714_123905.sql

登录后复制

* 宿主机

格式：`docker exec -i 容器名 mysql -uroot -p密码 数据库 <  备份数据库SQL文件路径`

• 1.
• 2.
• 3.
• 4.
• 5.

docker exec -i mysql-8 mysql -uroot -p123456 test < /workspace/mysql-8-backup/test_20240714_123905.sql

登录后复制

# 7 注意事项

## 7.1 定时备份，出现`the input device is not a TTY`

* 原因

​		"the input device is not a TTY" 错误通常出现在尝试从非交互式的上下文(比如 cron 任务)中运行需要交互的命令时。在你的情况下，可能是你的 Shell 脚本 `mysql-backup.sh` 中包含了需要交互的部分，但是在 cron 任务中无法提供所需的交互。

* 解决方案：

  将脚本中 `docker exec -it` 中去掉 `-it` 即可，表示在容器外部执行命令，而不需要进入容器内开启交互终端。

## 7.2 提示：mysqldump: [Warning] Using a password on the command line interface can be insecure.

* 原因

命令行不能出现密码 `-p123456`

* 临时解决方案

 设置环境密码，然后命令行不在输入密码

 * 设置环境密码 

   ```
   export MYSQL_PWD='123456'
   ```

 * 去掉密码命令行 `-p密码`

   ```
   docker exec mysql-8 bash -c 'mysqldump --single-transaction --flush-logs --source-data=2 --routines --opt -uroot test > /backup/test_20240713.sql'
   ```

​		`export MYSQL_PWD='123456'` 是临时性的设置，适合用于测试、临时任务或者当前会话中的需要。如果你需要更持久的设置，考虑		使用 Dockerfile、Docker Compose 文件或者其他更长久的配置方式来设置环境变量

* 长期解决方案

1. 创建容器时候加入`-e MYSQL_PASSWORD=密码`

   ```
   docker run --name=mysql-8 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_PASSWORD=123456 --restart always -v /workspace/mysql-8:/var/lib/mysql -v /workspace/mysql-8-backup:/backup -d mysql:8.0
   ```

2. 去掉密码命令行 `-p密码`

   ```
   docker exec mysql-8 bash -c 'mysqldump --single-transaction --flush-logs --source-data=2 --routines --opt -uroot test > /backup/test_20240713.sql'
   ```

   

​

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.
• 33.
• 34.
• 35.
• 36.
• 37.
• 38.
• 39.
• 40.
• 41.
• 42.
• 43.
• 44.
• 45.
• 46.
• 47.
• 48.
• 49.
• 50.
• 51.
• 52.
• 53.
• 54.
• 55.
• 56.
• 57.