mysql8 mysqldump全量备份数据库，定时任务

环境：mysql 8 MGR集群

一、定时任务

 [root@dev2 ~]# crontab -e
 

#mysqlbackup
0 2 * * * /root/mysqlbackup.sh > /root/mysqlbackup.log 2>&1

二、全量导出数据库脚本

vi /root/mysqlbackup.sh

#!/bin/bash
# Database backup and Clean up backup files.

#设置PATH
PATH=$PATH:/opt/mysql-8.0.32/bin

#备份文件的存储路径
backupPath=/backup/mysql

#备份文件后缀时间，也可以写为time=”$(date +”%Y%m%d”)”
time=`date +%Y%m%d%H%M%S`

#mysql用户名
user=root

#mysql密码
password=password123

#需要备份的数据库名称
db_name=dbname

#备份数据库
mysqldump -h127.0.0.1 -u$user -p$password --databases $db_name --triggers --routines --events --single-transaction --set-gtid-purged=OFF --default-character-set=utf8mb4 | gzip --rsyncable > $backupPath/${db_name}_$time.sql.gz

#删除修改时间为7天前的文件
find $backupPath -name "${db_name}_*.sql.gz" -type f -mtime +6 -exec rm -rf {} \;

不压缩的

mysqldump  -h127.0.0.1 -u$user -p$password --databases $db_name --triggers --routines --events --single-transaction --set-gtid-purged=OFF --default-character-set=utf8mb4 > $backupPath/${db_name}_$time.sql

三、导出参数说明

4.5.4 mysqldump — A Database Backup Program

[root@dev1 bin]# ./mysqldump --help

--databases $db_name        这么写，sql里会有建库语句，不用--databases，直接写库名，sql里没有建库语句。

--triggers        包括触发器（默认包括）

--routines        包括函数和存储过程

--events        包括事件

--single-transaction        “以单个事务截止导出，不锁表”，看帮助吧

--set-gtid-purged=OFF        sql中不包括gtid，好还原。

--default-character-set=utf8mb4        默认字符集，

        导出的文件查看是乱码，可以选择使用Notpad++查看，或者用编辑器文件--打开，选择符集打开。

gzip --rsyncable 压缩rsync友好的存档

四、gzip、gunzip说明

不保留原文件
gzip test.sql
gunzip test.sql.gz

保留原文件
gzip -c test.sql > test.sql.gz
gunzip -c test.sql.gz > test.sql

五、导入数据库

（如果不新建库，理论上，导入是覆盖的。）

1、用命令直接导入sql

1、手动解压（保留原文件）

gunzip -c /backup/mysql/test_20231008020001.sql.gz > /backup/mysql/test_20231008020001.sql

mysql -h127.0.0.1 -P3306 -uroot -ppassword < /backup/mysql/test_20231008020001.sql

2、解压和导入一起（保留原文件）

gunzip -c /backup/mysql/test_20231008020001.sql.gz | mysql -h127.0.0.1 -P3306 -uroot -ppassword 

3、也可以手动先建数据库再还原

gunzip -c /backup/mysql/test_20231008020001.sql.gz | mysql -h127.0.0.1 -P3306 -uroot -ppassword -Dtest

不要使用-Dmysql，如果备份的sql里没有建库语句，容易误把脚本导入到mysql库。

2、连接mysql客户端，用source命令导入sql

mysql -h127.0.0.1 -uroot -ppassword
mysql>source /backup/mysql/test.sql

六、mysqldump其他操作

1、mysqldump迁移数据库

mysqldump -h127.0.0.1 -uroot -ppassword test --triggers --routines --events | mysql -h172.10.10.177 -uroot -ppassword test2

2、备份所有数据库

--all-databases

mysqldump -h127.0.0.1 -u$user -p$password --all-databases --triggers --routines --events --single-transaction --set-gtid-purged=OFF --default-character-set=utf8mb4 > $backupPath/${db_name}_$time.sql

3、导出数据库（只导出表结构）

  -d, --no-data                 No row information.

mysqldump -h127.0.0.1 -uroot -ppassword --databases test --no-data --triggers --routines --events > /backup/mysql/test-no-data.sql

4、导出数据库（只导出数据）

  -t, --no-create-info        Don't write table creation info.

mysqldump -h127.0.0.1 -uroot -ppassword --databases test --no-create-info --triggers --routines --events > /backup/mysql/test-no-create.sql

5、导出表

mysqldump -h127.0.0.1 -uroot -ppassword dbname --tables test1 test2 > /backup/mysql/test-tables.sql

6、导出表加条件

mysqldump -h127.0.0.1 -uroot -ppassword dbname--tables test1 --where="name like '啊%'" > /backup/mysql/test-table-where.sql

crontab 定时任务，写日志，环境变量