在看文章之前需要提前安装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