使用shell脚本定期备份MySQL数据库

一、开启log-bin
使用mysqldump命令备份数据库。
需要开启log_bin。配置文件/etc/my.cnf

[mysqld]
log_bin=mysql-bin

重启mysql服务

二、shell脚本

#!/bin/sh
# Database info
DB_USER="root"
DB_PASS="password"
DB_HOST="localhost"
# Database array
DB_NAME=("DB_1" "DB_2")
# Others vars
BIN_DIR="/opt/mysql/bin"      #the mysql bin path
BCK_DIR="/home/mysql-backups"  #the backup file directory
DATE=`date +%F`
# create file
mkdir $BCK_DIR/$DATE
# TODO
# /usr/bin/mysqldump --opt -ubatsing -pbatsingpw -hlocalhost timepusher > /mnt/mysqlBackup/db_`date +%F`.sql
for var in ${DB_NAME[@]};
do
  $BIN_DIR/mysqldump --opt --single-transaction --master-data=2 -u$DB_USER -p$DB_PASS -h$DB_HOST --databases $DB_NAME > $BCK_DIR/$DATE/db_$var.sql
done