MySQL8 定时备份数据库

由于一次惨痛的教训(其实就是刚刚建好这个博客,就发现数据库被人搞了,要我支付比特币),再加上某友人的劝说我抛弃了原本的 MariaDB,投入了 MySQL 的怀抱,现在我又回到 mariadb 的怀抱了,真香。但这样还是改不了数据库可能被人入侵的问题,这才不可能是因为我数据库密码太简单了,于是加强了一波数据密码之后,又找了一个数据库备份脚本,这回稳得!

mysqldump 数据库备份工具

#mysqldump 命令备份数据

在 MySQL 中提供了命令行导出数据库数据以及文件的一种方便的工具 mysqldump,我们可以通过命令行直接实现数据库内容的导出 dump,首先我们简单了解一下 mysqldump 命令用法:

#MySQLdump 常用
mysqldump -u root -p --databases db1 db2 > xxx.sql

#mysqldump 常用操作示例

1.备份全部数据库的数据和结构

mysqldump -uroot -p123456 -A > /data/mysqlDump/mydb.sql

2.备份全部数据库的结构(加 -d 参数)

mysqldump -uroot -p123456 -A -d > /data/mysqlDump/mydb.sql

3.备份全部数据库的数据(加 -t 参数)

mysqldump -uroot -p123456 -A -t > /data/mysqlDump/mydb.sql

4.备份单个数据库的数据和结构,数据库名 mydb

mysqldump -uroot-p123456 mydb > /data/mysqlDump/mydb.sql

5.备份单个数据库的结构

mysqldump -uroot -p123456 mydb -d > /data/mysqlDump/mydb.sql

6.备份单个数据库的数据

mysqldump -uroot -p123456 mydb -t > /data/mysqlDump/mydb.sql

7.备份多个表的数据和结构(数据,结构的单独备份方法与上同)

mysqldump -uroot -p123456 mydb t1 t2 > /data/mysqlDump/mydb.sql

8.一次备份多个数据库

mysqldump -uroot -p123456 --databases db1 db2 > /data/mysqlDump/mydb.sql

mysqldump 还原 mysql 备份内容

有两种方式还原,第一种是在 MySQL 命令行中,第二种是使用 SHELL 行完成还原

1.在系统命令行中,输入如下实现还原

mysql -uroot -p123456 < /data/mysqlDump/mydb.sql

2.在登录进入 mysql 系统中,通过 source 指令找到对应系统中的文件进行还原

mysql> source /data/mysqlDump/mydb.sql

mysqldump 几个参数

mysqldump -uroot –proot --quick --no-create-info --extended-insert --default-character-set=utf-8 test>test.sql

参数说明:
--quick:用于转储大的表,强制 mysqldump 从服务器一次一行的检索数据而不是检索所有行,并输出当前 cache 到内存中
--no-create-info:不要创建 create table 语句
--extended-insert:使用包括几个 values 列表的多行 insert 语法,这样文件更小,IO 也小,导入数据时会非常快
--default-character-set=utf-8:按照原有字符集导出数据,这样导出的文件中,所有中文都是可见的,不会保存成乱码

备份 MySQL

#创建备份文件

mkdir /data/mysqlbackup
cd /data/mysqlbackup

#编写备份脚本代码

vim dbbackup.sh

#!/bin/sh
#-h后面改为自己的ip
#-u后面改为自己的数据库账号
#-p后面改为自己的数据库密码,有字符需要加""
#demand_database改为你想要备份的数据库名称
echo "开始备份数据库";
#导出所有数据库 username 替换为自己mysql登陆名,password123登陆密码
## mysqldump -h106.14.XX.XXX -uusername -p"password123" --all-databases > /data/mysqlbackup/databaseName`date +%Y-%m-%d_%H%M%S`.sql;

#导出指定数据库并压缩
## mysqldump -h106.14.XX.XXX -uusername -p"password123"  demand_database| gzip > /data/mysqlbackup/databaseName`date +%Y-%m-%d_%H%M%S`.sql.gz;

#最近转投docker怀抱,本地不安装mysql时,采用docker的mysql备份,备份语句修改为:
docker exec -it mysql mysqldump -h106.14.XX.XXX -uusername -p"password123"  demand_database| gzip > /data/mysqlbackup/databaseName`date +%Y-%m-%d_%H%M%S`.sql.gz;
# mysql 替换为对应的容器名
#删除3天前的备份文件

backupdir=/data/mysqlbackup
db_name=databaseName

find $backupdir -name $db_name"*.sql.gz" -type f -mtime +3 -exec rm -rf {} \;


echo "备份完成";

脚本代码可以根据自己的需求修改

#更改备份脚本权限

chmod +x dbbackup.sh

#用 crontab 定时执行备份脚本代码

crontab -e

输入上面的命令后会进入 vim 编辑的一个文件,在上面写 cron 表达式 + 脚本地址就行了

测试 每 1 分钟执行一次:

*/1 * * * *  /data/mysqlbackup/dbbackup.sh

我设定在每天两点更新,添加如下代码:

00 2 * * * /data/mysqlbackup/dbbackup.sh

#错误情况

1.任务执行失败了,可以通过以下命令查看任务日志:

tail -f /var/log/cron

2.mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

  1. 密码有字符是否加了双引号"password";

  2. 添加自身 ip 地址:-h192.168.1.233

参考文章