本文转自: https://www.jianshu.com/p/b77dfd6d998b
手动备份
- 备份一个数据库
mysqldump -h hostname -u username -p mypasswd databasename > /path to backup/bakname.sql
备份并压缩
mysqldump -h hostname -u username -p mypasswd databasename | gzip > /path to backup/bakname.sql.gz
- 备份多个数据库
mysqldump -h hostname -u username -p mypasswd databases databasename1 databasename2 databasename3 > /path to backup/bakname.sql
- 备份数据库一些表
mysqldump -h hostname -u username -p mypasswd databasename table1 table2 table3 > /path to backup/bakname.sql
- 仅备份数据库结构
mysqldump -no-data -databases databasename1 databasename2 databasename3 > /path to backup/bakname.sql
- 备份所有数据库
mysqldump -all-databases > /path to backup/bakname.sql
还原数据库
- 还原无压缩数据库
mysql -h hostname -u username -p mypasswd databasename < /path to backup/bakname.sql
- 还原压缩数据库
gunzip < /path to backup/bakname.sql.gz | mysql -h hostname -u username -p mypasswd databasename
迁移到新服务器
mysqldump -h hostname -u username -p mypasswd databasename | mysql -h new_hostname -C databasename
脚本定时备份
- 创建备份脚本
mysql_backup.sh
#!/bin/sh
# This is a mysql datbase backup shell script.
# set mysql info
hostname="localhost"
user="root"
password="my password"
# set database info
database="bak database name"
bakpath="path to backup"
date=$(date +%Y%m%d_%H%M%S)
# backup
mkdir -p $bakpath
mysqldump -h $hostname -u $user -p $password $database | gzip \
> $bakpath/$database_$date_sql.gz
- 创建定时任务
crontab: crontab 是linux系统下的一个任务调度器
crontab定时服务 启动|结束|状态
service crond start | status | stop
# 查看config文件,可以看到定时规则
$ cat /etc/cron
# 添加备份定时任务
$ crontab -e
添加定时计划,例如:每天2点执行
0 2 * * * /path to sh/mysql_backup.sh
保存退出
# 查看当前用户定时任务
$ crontab -l
#查看定时计划日志
$ tail -f /var/log/cron
- 定时跟踪