CentOS自动化运维-备份MySQL数据库

在看文章之前需要提前安装MySQL数据库，没有安装的看我前面这篇文章，在Linux中使用docker安装和配置MySQL数据库-CSDN博客

1.进入MySQL容器

docker exec -it mysql8 /bin/bash

 

2.创建test数据库

（1）登录数据库

mysql -uroot -p'testhh@mysql8'

这里的密码根据你自己的来。

（2）创建test数据库

create database test charset=utf8mb4;

3.创建测试表，插入几条测试数据

（1）进入test数据库

use test;

（2）创建测试表user

CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL,
  `sex` VARCHAR(255) NULL,
  `phone` VARCHAR(255) NULL,
  PRIMARY KEY (`id`)
);

 （3）插入几条数据

INSERT INTO `user` VALUES (1, 'zhangsan', '1', '123456');
INSERT INTO `user` VALUES (2, 'lisi', '1', '123456');
INSERT INTO `user` VALUES (3, 'wangwu', '1', '123467989');
INSERT INTO `user` VALUES (4, 'chuanchuan', '2', '123456789');

（4）退出数据库

exit

 （5）退出容器

exit

4.编写运维脚本

（1）进入数据库备份目录

cd /data/mysql8/data/backups/

 （3）创建cmysql.sh脚本

vi cmysql.sh

输入以下内容：

#!/bin/bash
# List of databases to back up
DBLIST="mysql test"

# Backup directory
BACKUPDIR=/mydata/backups/data/mysql

# The time to save
NUMDAYS=30

# Back up the Linux commands and MySQL database configuration to be used
FINDCMD="find"
MYSQLCMD="mysql"
MyUSER="root"		# USERNAME
MyPASS="testhh@mysql8"	# PASSWORD
MyHOST="localhost"	# Hostname
DUMPCMD="mysqldump -u$MyUSER -h $MyHOST -p$MyPASS --lock-tables --databases"
GZIPCMD="gzip"

# Date of backup
BACKUPDATE=`date + % Y% m% d_% H% M`

function USAGE() {
cat << EOF
usage: $0 options

This script backs up a list of MySQL databases.

OPTIONS:
 -h Show this message
 -a Backup all databases
 -l Databases to backup (space seperated)
 -n Number of days to keep backups
EOF
}

while getopts "hal:n:" opt;
do
  case $opt in
    a)
      DBLIST=""
      ;;
    h)
      USAGE
      exit 1
      ;;
    l)
      DBLIST="$OPTARG"
      ;;
    n)
      NUMDAYS="$OPTARG"
      ;;
    \?)
      USAGE
      exit
      ;;
     :)
      echo "Option -$OPTARG requires an argument.">&2
      exit 1
        ;;
  esac
done

function ERROR() {
  echo && echo "[error] $@ "
  exit 1
}

function NOTICE() {
  echo && echo "[notice] $@ "
}

function RUNCMD() {
  echo $@
  eval $@
}

# Use the backup data for robustness judgment
if [ ! -n "$DBLIST" ]; then
  DBLIST=`$MYSQLCMD -N -s -e "show databases" | grep -viE '(information_schema | performance_schema|mysql|test)'`

  if [ ! -n "$DBLIST" ]; then
    ERROR "Invalid database list"
  fi
fi

if [ ! -n "$BACKUPDIR" ]; then
  ERROR "Invalid backup directory"
fi

if [[ ! $NUMDAYS=~^[0-9]+$ ]]; then
  ERROR "Invalid number of days: $NUMDAYS"
elif [ "$NUMDAYS" -eq "0" ]; then
  ERROR "Number of days must be greater than zero"
fi

# Mask word
umask 077

# $BACKUPDIR is the directory variable for the backup defined earlier
RUNCMD mkdir -p -v $BACKUPDIR

if [ ! -d $BACKUPDIR ]; then
  ERROR "Invalid directory: $BACKUPDIR"
fi

NOTICE "Dumping MySQL databases..."
RC=0

for database in $DBLIST; do
  NOTICE "Dumping $database..."
  RUNCMD "$DUMPCMD $database|$GZIPCMD >$BACKUPDIR/${database}_$BACKUPDATE.sq.gz"

  RC=$?
  if [ $RC -gt 0 ];then
    continue;
  fi
done

if [ $RC -gt 0 ]; then
        ERROR "MySQLDump failed!"
else
        NOTICE "Removing dumps older than $NUMDAYS days..."
        RUNCMD "$FINDCMD $BACKUPDIR -name \"*.sql.gz\" -type f -mtime +$NUMDAYS -print0|xargs -0 rm -fv"

        NOTICE "Listing backup directory contents..."
        RUNCMD ls -la $BACKUPDIR

        NOTICE "MySQLDump is complete!"
fi

（4）保存退出

:wq

5.运行

（1）进入mysql8容器

docker exec -it mysql8 /bin/bash

（2） 进入挂在宿主机的备份目录

cd /mydata/backups/data/mysql
ls

（3）运行脚本 

bash ./cmysql.sh

成功

6.查看备份文档 

ls -l

7.重要说明！！ 

运行cmysql.sh脚本的时候需要在mysql8容器内部/mydata/backups/data/mysql/运行。

编辑cmysql.sh脚本的时候需要在宿主机/data/mysql8/data/backups目录下编辑。

因为宿主机的/data/mysql8/data/backups目录和mysql8容器的/mydata/backups/data/mysql/目录是共享的。

建议开两个终端，一个在容器内部，一个在外部。

另外从宿主机进入docker数据库mysql8容器命令，mysql8是容器的name。

docker exec -it mysql8 /bin/bash