1.简介
**mysqldump **是 MySQL 数据库管理系统的命令行实用程序,用于创建数据库的逻辑备份。它能够导出数据库的结构 如表结构、视图、触发器等 以及表中的数据为 SQL 格式的文本文件。
2.基本用途
- 全库备份:可以备份整个 MySQL 服务器上的所有数据库。
- 单数据库备份:仅备份指定的单一数据库。
- 表备份:备份特定数据库中的某几张表。
3.命令格式
1
2
| mysqldump [选项] [数据库名 [表名...]]
|
3.1常用选项
| 序号 |
表名 |
描述 |
| 选项 |
描述 |
-u 用户名, –user 用户名 |
| -p[密码], –password[ 密码] |
提供连接MySQL服务器的密码。如果仅使用 -p,将会提示输入密码。为安全起见,建议不在命令行直接暴露密码。 |
-h 主机名, –host 主机名 |
| -P 端口, –port 端口 |
如果MySQL服务不是运行在默认的3306端口,可以用此选项指定端口号。 |
-A, –all-databases |
| -B, –databases |
允许指定多个数据库进行备份,后面直接列出数据库名。 |
数据库名 [表名…] |
| > 备份文件路径 |
输出重定向符号,用于指定备份文件的保存路径和名称。 |
–single-transaction |
| –lock-tables false |
防止在备份时锁定表,通常与 –single-transaction 一起使用,尤其适用于InnoDB表。 |
–routines |
| –triggers |
包含数据库中的触发器到备份中。 |
–events |
| –skip-add-drop-table |
备份时不包含 DROP TABLE 语句,这样在恢复时不会删除已存在的表。 |
–quick |
| — |
— |
— |
| — |
— |
— |
| — |
— |
— |
| — |
— |
— |
| — |
— |
— |
| — |
— |
— |
| — |
— |
— |
| — |
— |
— |
3.2常用命令
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| **导出命令大全**
mysqldump -u[用户名] -h[ip] -p[密码] -P[端口号] 数据库名 表名 >导出的文件名.sql
mysqldump -uroot -h127.0.0.1 -proot -P3306 education users>d:/user.sql
mysqldump -uroot -proot --all-databases>all.sql
mysqldump -uroot -proot --databases test --tables t1 t2>two.sql
mysqldump -uroot -h127.0.0.1 -proot -P3306 -d education users>d:/user.sql
mysqldump -uroot -h127.0.0.1 -proot -P3306 -t education users>d:/user.sql
导出:mysqldump -uroot -proot --no-data --databases test>s1.sql
导入:mysql -uroot -proot -h127.0.0.1 -P3306 test<s1.sql
**导入命令大全**
格式:mysql -h[ip] -P[(大写)端口] -u[用户名] -p[密码] [数据库名] < d:XX.sql(路径)
mysql -uroot -proot -h127.0.0.1 -P3306 education<d:/database.sql
mysql -uroot -proot -h127.0.0.1 -P3306 <d:/all_database.sql
mysql> use test;
mysql> source /home/test/database.sql
|
4.备份脚本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
| #!/bin/bash
NAME MySQL Database Backup Script
DATE $(date %Y-%m-%d-%H-%M)
LOG_FILE /var/log/mysql_backup.log
source /etc/mysql_backup.conf 2>/dev/null
: ${MYSQL_USER: mysql_user_not_set}
: ${MYSQL_PASSWORD: mysql_passwd_not_set}
: ${MYSQL_HOST: localhost}
: ${MYSQL_PORT: 3306}
: ${MYSQL_CHARSET: utf8mb4}
: ${BACKUP_PATH: /backups/mysql}
: ${MAX_BACKUPS: 10}
: ${EMAIL_TO: you example.com}
: ${EMAIL_FROM: backup_script example.com}
mkdir -p $BACKUP_PATH
DATE_TIME $(date %Y%m%d_%H%M%S)
mysqldump --opt --all-databases --single-transaction --flush-logs --master-data 2 \
-h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD \
| gzip > $BACKUP_PATH/mysql_backup_$DATE_TIME.sql.gz
if [ $? -eq 0 ]; then
echo $(date) - 数据库备份成功 >> $LOG_FILE
find $BACKUP_PATH -name *.sql.gz -type f | sort | head -n -$MAX_BACKUPS | xargs -r rm -f
echo $(date) - 已清理过期备份文件 >> $LOG_FILE
{
echo 主题: MySQL 数据库备份通知
echo From: $EMAIL_FROM
echo To: $EMAIL_TO
echo
echo 数据库备份已完成,备份文件:mysql_backup_$DATE_TIME.sql.gz
echo 备份路径:$BACKUP_PATH
} | mail -s $(echo -e MySQL Backup on $DATE ) $EMAIL_TO
else
echo $(date) - 数据库备份失败,请检查错误日志 >> $LOG_FILE
{
echo 主题: MySQL 数据库备份失败通知
echo From: $EMAIL_FROM
echo To: $EMAIL_TO
echo
echo 数据库备份失败,请立即检查。
} | mail -s $(echo -e MySQL Backup Failure on $DATE ) $EMAIL_TO
fi
|
说明:
- 环境变量: 使用环境变量读取敏感信息,可以提高脚本的灵活性和安全性。这里假设存在一个
/etc/mysql_backup.conf配置文件来集中管理这些变量,以避免硬编码。
- 日志记录: 添加了日志文件,用于记录备份操作的详细信息,方便追踪和审计。
- 备份压缩: 使用
gzip命令对备份文件进行压缩,减少存储空间占用。
- 自动清理: 使用
find命令自动清理超过保留数量的旧备份文件,提高了脚本的自动化程度。
- 邮件通知: 添加了成功和失败的邮件通知功能,可以及时提醒管理员备份状态,
mail命令用于发送邮件,需确保系统已正确配置邮件发送工具。
5.定时执行备份脚本
编辑Crontab配置:首先,打开终端并以您的用户身份运行crontab命令来编辑Cron配置。如果您想要以root用户身份执行备份,可以使用sudo crontab -e,否则,只需输入crontab -e。
添加定时任务:在打开的Crontab文件中,移动光标到文件末尾,然后添加以下行来设定定时任务。假设您的备份脚本名为mysql_backup.sh,且该脚本位于/usr/local/bin/目录下:
1
| 0 2 * * * /usr/local/bin/mysql_backup.sh
|
这行的意思是:每天的凌晨2点0分执行/usr/local/bin/mysql_backup.sh脚本。Cron任务的时间格式为分钟 小时 日 月 星期,从左到右依次对应。
保存并退出:编辑完成后,保存文件并退出编辑器。大多数情况下,您可以通过按下Ctrl X,然后按Y键确认保存,最后按回车键退出。
测试与验证:您可以通过查看Cron的日志 通常是/var/log/syslog或/var/log/cron.log,具体取决于您的系统配置 来确认任务是否按时执行,或者等待下一个执行时间点查看是否收到了邮件通知 如果您的脚本配置了邮件通知功能 。
