docker容器mysql开启binlog和定时备份

「爱情、让人受尽委屈。」 2022-12-24 14:57 215阅读 0赞

> ## 为了保证mysql数据库的数据完整性，除了数据库的数据挂到宿主机做持久化外，还需要做binlog和定时备份数据 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjUzMzg1Ng_size_16_color_FFFFFF_t_70][]

### 1， 添加mysql的文件映射到docker-stack 文件中 ###

#指定开启binlog的配置文件
          - /home/test/binlog/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf
          #指定备份数据库的定时任务执行shell文件
          - /home/test/binlog/mysql-dump.sh:/home/app/mysql-dump.sh
          #指定定时备份存放文件
          - /home/test/binlog/mysql_dump_file:/home/app/sql_backup

### mysqld.cnf的配置文件如下： 指定了开启binlog和存放binlog文件的路径（该路径需要挂到宿主机） ###

# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
    #
    # This program is free software; you can redistribute it and/or modify
    # it under the terms of the GNU General Public License, version 2.0,
    # as published by the Free Software Foundation.
    #
    # This program is also distributed with certain software (including
    # but not limited to OpenSSL) that is licensed under separate terms,
    # as designated in a particular file or component or in included license
    # documentation.  The authors of MySQL hereby grant you an additional
    # permission to link the program and your derivative works with the
    # separately licensed software that they have included with MySQL.
    #
    # This program is distributed in the hope that it will be useful,
    # but WITHOUT ANY WARRANTY; without even the implied warranty of
    # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    # GNU General Public License, version 2.0, for more details.
    #
    # You should have received a copy of the GNU General Public License
    # along with this program; if not, write to the Free Software
    # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA
    
    #
    # The MySQL  Server configuration file.
    #
    # For explanations see
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
    
    [mysqld]
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    datadir         = /var/lib/mysql
    #log-error      = /var/log/mysql/error.log
    # By default we only accept connections from localhost
    #bind-address   = 127.0.0.1
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    #配置binlog的日志路径
    log-bin=/var/lib/mysql/mysql-bin
    #配置serverid
    server-id=123456

### mysql-dump.sh内容如下：定时备份mysql数据库到/home/app/sql\_backup。 ###

#!/bin/bash
    # 设置mysql的登录用户名和密码(根据实际情况填写)
    mysql_user="root"
    mysql_password="123456"
    mysql_host="localhost"
    mysql_port="3306"
    mysql_charset="utf8mb4"
    #数据库名称
    mysql_backup_database="test"
    
    # 备份文件存放地址(根据实际情况填写)
    backup_location=/home/app/sql_backup
    
    # 是否删除过期数据
    expire_backup_delete="ON"
    #保存多少天的数据
    expire_days=15
    backup_time=`date +%Y%m%d%H%M`
    backup_dir=$backup_location
    welcome_msg="Welcome to use MySQL backup tools!"
    
    # 判断mysql实例是否正常运行
    mysql_ps=`ps -ef |grep mysql |wc -l`
    mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port|wc -l`
    if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
            echo "ERROR:MySQL is not running! backup stop!"
            exit
    else
            echo $welcome_msg
    fi
    
    # 备份指定数据库中数据(此处假设数据库是mysql_backup_test)
    mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -B $mysql_backup_database> $backup_dir/mysql_backup_test-$backup_time.sql
    flag=`echo $?`
    if [ $flag == "0" ];then
            echo "database mysql_backup_test success backup to $backup_dir/mysql_backup_test-$backup_time.sql.gz"
    else
            echo "database mysql_backup_test backup fail!"
    fi
    
    # 删除过期数据
    if [ "$expire_backup_delete" == "ON" -a  "$backup_location" != "" ];then
            `find $backup_location/ -type f -mtime +$expire_days | xargs rm -rf`
            echo "Expired backup data delete complete!"
    fi

### 2，启动项目后进入mysql容器内:  ###

> 1.  执行命令更新linux库     ： apt-get update
> 2.  安装定时任务工具： apt-get install -y cron vim
> 3.  通过命令编辑任务： crontab -e
> 4.  添加该任务命令： 59 23 \* \* \* /bin/sh /home/app/mysql-dump.sh
> 5.  启动crontab ：service cron start

## 到此，docker容器mysql开启binlog和定时备份处理完成 ##

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjUzMzg1Ng_size_16_color_FFFFFF_t_70]: /images/20221120/79442006d86641d6a86e2f5770a0e1eb.png