自动备份多个MySQL或MariaDB数据库

介绍

在本文中，我们将逐步介绍如何使用自定义bash脚本并设置cron作业来备份位于同一台计算机上的多个MySQL或MariaDB数据库。

数据库备份

我们假设在Vultr实例上有三个MySQL数据库，分别为db-vultr-site，db-vultr-blog，db-vultr-app （不用担心创建这些数据库，您可以替换它们的名称为您所用，它们将在下面的步骤2中的脚本中使用）。

1. 登录到您的MySQL或MariaDB数据库，并在下面的查询中运行以创建数据库用户db_user_backups来处理备份

   GRANT LOCK TABLES, SELECT, SHOW VIEW, REPLICATION CLIENT ON *.* TO 'db_user_backups'@'%' IDENTIFIED BY '{COMPLEX-PASSWORD}';
   

   还要在下面运行以确保将MySQL配置为正确还原存储过程

   SET GLOBAL log_bin_trust_function_creators = 1;
   

2. 设置必要的目录结构和所需文件

   # create backup directory with environment and log file
   sudo mkdir /backups && cd /backups
   sudo touch .env db-backup.sh db-backup.log
   sudo chmod -R 775 /backups
   sudo chmod -R g+s /backups
   sudo chmod +x db-backup.sh
   
   # add mysql backup user credentials into environment file
   echo "export MYSQL_USER=db_user_backups" > /backups/.env
   echo "export MYSQL_PASS={COMPLEX-PASSWORD}" >> /backups/.env
   

3. 打开db-backup.sh nano /backups/db-backup.sh并将其下面的代码粘贴到其中，然后保存文件（Ctrl + X-> Y->按下Enter）。

   DB_NAMES=( 'db-vultr-site' 'db-vultr-blog' 'db-vultr-app' ) #replace with your own database name(s)
   BKUP_NAMES=()
   BKUP_DIR="/backups"
   
   # get total number of directories
   total_dbs=${#DB_NAMES[@]}
   
   # create backup file names
   for (( i=0; i<${total_dbs}; i++ )); do
       BKUP_NAMES[$i]="`date +%Y%m%d%H%M`-backup-$${DB_NAMES[$i]}.sql.gz"
   done
   
   # get backup users credentials
   source $BKUP_DIR/.env
   
   # create backups
   for (( i=0; i<${total_dbs}; i++ )); do
       # NOTE: --routines flag makes sure stored procedures are also backed up
       mysqldump --routines -u ${MYSQL_USER} -p${MYSQL_PASS} | gzip > ${BKUP_DIR}/${BKUP_NAMES[$i]}
   done
   

   上面的代码正在遍历具有您要备份的数据库名称的数组，并执行此操作。

Cronjob设置

设置一个cronjob以在每个运行备份脚本的午夜运行，并将结果/输出保存到备份日志。

1. 打开crontab

   crontab -e
   

2. 在crontab中添加以下条目

   0 0 * * * /usr/bin/env bash /backups/db-backup.sh &>> /backups/db-backup.log
   

   注意：在测试时，您可以将cronjob设置为每1分钟运行一次，如下所示

   * * * * * /usr/bin/env bash /backups/db-backup.sh &>> /backups/db-backup.log
   

   -或-每5分钟（用所需的分钟数替换5）

   */5 * * * * /usr/bin/env bash /backups/db-backup.sh &>> /backups/db-backup.log
   

下一步是什么

您可能不希望将备份放在运行数据库的同一台服务器上，而是希望在不同地理位置的服务器上。有多种方法可以执行此操作，从使用SFTP到使用由众多可用的云存储提供商提供的自定义工具。一种很好的替代方法是Rsync，如此处所述-vultr.com/docs/setup-file-mirroring-using-rsync-in-debian-ubuntu

由Lami Adabonyan撰写