MySQL 数据库如何定时自动备份?

嘉兴小伙子 2023-9-18  服务器   598

之前我的网站出了一次事故,就是帖子被大量灌水,总共有5000多条(用的是MySQL数据库),虽然第一时间就发现了,并且进入 phpMyAdmin 利用SQL语句全部进行了删除清理,但毕竟还是给数据库造成了一定的破坏。我在想,要是那会有数据库的备份就好了,可以直接恢复。请问 MySQL 数据库的定时备份怎么做?

4 个回答
  • 小何同志 2023-9-18
    2

    有多种方法可以定时备份 MySQL 数据库,以下列举几种常见的方法:

    1. 使用 crontab 定时任务:在 Linux 系统中可以使用 crontab 来设置定时任务,例如每天凌晨 2 点备份 MySQL 数据库。可以编写脚本来实现备份操作,然后将脚本添加到定时任务中。

    2. 使用 MySQL 自带的 mysqldump 工具:mysqldump 是 MySQL 自带的备份工具,可以使用它来备份数据库。可以编写脚本来实现备份操作,然后将脚本添加到定时任务中。

    3. 使用第三方备份工具:还有一些第三方备份工具可以用来备份 MySQL 数据库,例如 Percona XtraBackup、Mariabackup 等。这些工具可以通过脚本或命令来实现备份操作,然后将脚本添加到定时任务中。

    以下是使用 crontab 定时任务配合 mysqldump 命令来定时备份 MySQL 数据库:

    1. 编写备份脚本

    使用 mysqldump 命令备份 MySQL 数据库,可以将备份脚本命名为 backup_mysql.sh,并保存到指定目录下,例如 /home/mysql_backup/backup_mysql.sh。备份脚本内容如下:

    #!/bin/bash

    # 备份目录

    BACKUP_DIR=/home/mysql_backup

    # 数据库用户名

    DB_USER=root

    # 数据库密码

    DB_PASSWD=123456

    # 数据库名称

    DB_NAME=my_db

    # 备份文件名

    BACKUP_FILE=mysql_$(date +%Y%m%d%H%M%S).sql

    # 创建备份目录

    mkdir -p $BACKUP_DIR

    # 备份数据库

    mysqldump -u $DB_USER -p$DB_PASSWD $DB_NAME > $BACKUP_DIR/$BACKUP_FILE

    # 压缩备份文件

    gzip $BACKUP_DIR/$BACKUP_FILE

    2. 添加定时任务

    使用 crontab 命令添加定时任务,例如每天凌晨 2 点备份 MySQL 数据库。在终端中执行以下命令:

    crontab -e

    在打开的文件中添加以下内容:

    0 2 * * * /bin/bash /home/mysql_backup/backup_mysql.sh

    这表示每天凌晨 2 点执行 /home/mysql_backup/backup_mysql.sh 脚本。

    3. 保存并退出

    保存并退出 crontab 文件即可。

    以上就是使用 crontab 定时任务备份 MySQL 数据库的具体操作步骤。需要根据实际情况修改备份脚本中的参数,例如数据库用户名、密码、数据库名、备份目录等。

    需要注意的是,备份 MySQL 数据库时应该选择合适的备份策略,包括全量备份、增量备份、差异备份等,以及备份的存储位置、备份文件的命名规则等。同时,备份的频率也要根据业务需求和数据量来确定。

    2 回复引用 引用
  • 野溜小子 2023-9-18
    3

    全量备份脚本dbback.sh:
    #!/bin/bash
    ##备份数据库脚
    #MySQL User Information
    HOST=127.0.0.1
    USERNAME=root
    PASSWORD=*****
    #Date Format
    DATE=`date +%Y%m%d`
    #Back directory
    DAYS=20
    #备份文件存放位置
    BACKUP_DIR=/data/db-backup/
    #MySQL directory
    MYSQL_DIR=/usr/bin/
    #Go to the backup directory
    if [ -d ${BACKUP_DIR} ];then
    cd ${BACKUP_DIR}
    else
    mkdir -p ${BACKUP_DIR}
    fi
    #The first instance of the backup
    while read dbname
    do
    if [ -f ${dbname}_${DATE}.gz ]; then
    echo "MySQL Database ${dbname}_${DATE}.gz already exists."
    else
    ${MYSQL_DIR}/mysqldump -u${USERNAME} -p${PASSWORD} -h${HOST} --default-character-set=utf8 --master-data=2 --single-transaction  ${dbname} | /bin/gzip > ${BACKUP_DIR}/${dbname}_${DATE}.gz
    fi
    done < /opt/ly-traffic/db-backup/dbs.txt

    ##删除旧备份文件
    find  ${BACKUP_DIR} -name "*.gz" -type f -mtime +22|tee -a ${BACKUP_DIR}/del.log|xargs rm -r 2>>${BACKUP_DIR}/err.lo


    exit 0

    数据备份可用行测试:
    gzid -d  文件名
    mysql -uroot -p -h127.0.0.1  数据库 < 文件名

    1 回复引用 引用
  • 孤城浪人 2023-9-18
    4
    对于自建的数据库,没有使用云数据库的时候,就需要自己保证数据的可用性,需要及时对数据进行备份。

    这里讲一下如何通过脚本,每天定时对数据库进行全量备份,并对数据文件保存14天(当然天数可以自己随意设置,根据服务器容量大小和具体要求设置)。

    1、创建一个备份脚本文件,例如 backup_mysql.sh,并将以下代码复制到文件中:
    #!/bin/bash

    # 设置 MySQL 登录信息
    MYSQL_USER="your_mysql_username"
    MYSQL_PASSWORD="your_mysql_password"
    MYSQL_DATABASE="your_database_name"
    BACKUP_DIR="/path/to/backup/directory"
    DATE=$(date +%Y-%m-%d)
    TIME=$(date +%H-%M-%S)

    # 创建备份目录和文件名
    mkdir -p $BACKUP_DIR/$DATE
    FILENAME=$BACKUP_DIR/$DATE/$MYSQL_DATABASE-$TIME.sql.gz

    # 使用 mysqldump 命令备份数据库
    mysqldump --user=$MYSQL_USER --password=$MYSQL_PASSWORD --databases $MYSQL_DATABASE | gzip > $FILENAME

    # 输出备份完成信息
    echo "Backup completed on $(date +%Y-%m-%d %H:%M:%S) for database $MYSQL_DATABASE to $FILENAME."

    # 删除超过14天的备份文件
    find $BACKUP_DIR/* -mtime +14 -exec rm {} \;

    2、为脚本文件添加可执行权限:
    chmod +x backup_mysql.sh

    3、将脚本文件添加到 crontab 中,以每天凌晨 2 点执行备份任务:
    crontab -e

    4、在打开的编辑器中,新增以下行:
    0 2 * * * /path/to/backup_mysql.sh >/dev/null 2>&1

    保存并退出编辑器。现在,脚本将在每天凌晨 2 点执行,并输出一条备份计划信息。

    上面的shell脚本做了以下事情:
    1.获取当前日期和时间。
    2.使用 mkdir 命令创建备份目录和文件名。
    3.使用 mysqldump 命令备份数据库,并将结果压缩成 gzip 格式。
    4.输出一条备份完成信息。
    5.使用 find 命令查找超过14天的备份文件,并将其删除。

    自己做备份时,记得修改mysql的登录账号、密码,数据库,备份文件夹等信息。也可以修改备份文件保存时间。是不是很简单啊?
    1 回复引用 引用
  • 棠大大 2023-9-18
    5
    MySQL的全量备份与增量备份

    全量备份:可以使用mysqldump直接备份整个库或者是备份其中某一个库或者一个库中的某个表。

    备份所有数据库:
    [root@my ~]# mysqldump -uroot -p123456 --all-databases >/opt/all.sql
    mysqldump: [Warning] Using a password on the command line interface can be insecure.

    备份单个数据库的所有表:
    [root@my ~]# mysqldump -uroot -p123456 --databases test>/opt/test.sql
    mysqldump: [Warning] Using a password on the command line interface can be insecure.

    备份数据库中的单个表的结构:
    [root@my ~]# mysqldump -uroot -p123456 -d test userinfo >/opt/test_userinfo_jiegou.sql
    mysqldump: [Warning] Using a password on the command line interface can be insecure.

    备份数据库中的单个表的内容:
    [root@my ~]# mysqldump -uroot -p123456 test userinfo>/opt/test_userinfo_neirong.sql
    mysqldump: [Warning] Using a password on the command line interface can be insecure.

    增量备份:增量备份是针对于数据库的bin-log日志进行备份的,需要开始数据库的bin-log日志。增量备份是在全量的基础上进行操作的。增量备份主要是靠mysql记录的bin-log日志。(可以把二进制日志保存成每天的一个文件)

    1.开启二进制日志文件;
    [root@my ~]# echo -e "log-bin = /usr/local/mysql/logs/mysql_bin \nserver_id = 1\n max_binlog_size = 100M " >>/etc/my.cnf 

    根据position值位置进行恢复;
    [root@my ~]# mysqlbinlog --start-position=1 --stop-position=795 /usr/local/mysql/logs/mysql_bin.000001 |mysql -uroot -p123456

    根据时间点位置进行恢复;-d指定数据库,选项-h指定主机
    [root@my ~]#mysqlbinlog --start-date='2019-05-30 14:49:25' --stop-date='2019-05-30 14:52:39' -d linux -h127.0.0.1 /usr/local/mysql/logs/mysql-bin.000001 |mysql -uroot -p123456

    将二进制日志文件中所有的数据记录全部恢复;
    [root@my ~]# mysqlbinlog /usr/local/mysql/logs/mysql_bin.000001 |mysql -uroot -p123123

    脚本:
    全量备份:
    [root@calldb1 ~]# cat /shell/fs_sql_bak.sh 
    #!/bin/bash
    ##beifen sql

    Bakdir=/data/sql-bak
    Time=$(date +"%F_%T")
    [ -f /usr/bin/mysqldump ] && echo "ok" || exit
    /usr/bin/mysqldump -uroot -p123456 -R --all-databases > $Bakdir/$Time"_34.sql"
    #echo $Bakdir/$Time"_34.sql"

    /usr/bin/mysqldump dbname -uroot -p123456 > $Bakdir/$Time"-dbname.sql"

    增量备份:
    [root@calldb2 shell]# cat zeng_sql.sh 
    #!/bin/bash
    ## zeng liang bak sql
    Logdir=/usr/local/mysql/logs
    Time=$(date +%F)
    mkdir $Logdir/"log-"$Time
    find $Logdir -type f -mmin 1 -exec cp {} $Logdir/"log-"$Time/ \;

    if [ -f $Logdir/"log-"$Time/mysql-bin.* ];then
      echo "mysql is zeng backup success on time-$(date +%F)" >>$Logdir/"log-"$Time/mysql_zeng_backup.log
    else
      echo "mysql is zeng backup fail on time-$(date +%F)" >>$Logdir/"log-"$Time/mysql_zeng_backup.log
    fi
    mysqladmin -uroot -p123456 flush-logs >/dev/null

    生产环境:
    建议一周或者三天进行一次全量备份,一天一次增量备份。
    1 回复引用 引用
    • 探知网
      6
        立即登录 立即注册
返回
发新帖