使用mysqldump导出SQL格式的备份
默认情况下mysqldump导出的是标准的SQL语法，因此你可以使用标准输出写入文件。
shell> mysqldump [arguments] > file_name
若需要导出所有数据，如下：
shell> mysqldump --all-databases > dump.sql
若仅导出特定数据库，请在命令行中命名它们，然后使用--databases选项。--databases后的所有名称都将视为数据库名称，若省略该选项则默认取第一个字符串作为数据库的名称。
shell> mysqldump --databases db1 db2 db3 > dump.sql
当使用--all-databases或--databases选项时，在具体的数据库导出之前会添加CREATE DATABASE与USE语句。这样可以确保导出的备份在下次被加载之前，若发现库没有创建可以实施创建，不会因此影响数据导入。如果你需要在导入时删除之前的库，可以在导出时添加选项--add-drop-database。这样做可以在声明CREATE DATABASE 之前添加语句DROP DATABASE。
导出单个数据库：
shell> mysqldump --databases test > dump.sql
也可以省略--databases标签：
shell> mysqldump test > dump.sql
他们两者的区别是省略--databases标签在导出的数据库脚本中，不会自动添加CREATE DATABASE与USE语句。
省略--databases这意味着你在执行导入时：
- 需要指定数据库。
- 你可以将数据导入到与导出指定数据库名称不同的数据库中。
- 如果你指定的数据库不存在，你需要手动创建。
- 因为输出将不包含CREATE DATABASE语句，所以
--add-drop-database选项不起作用。如果使用它，它不会生成DROP DATABASE语句。
如果仅想导出指定数据库中的指定数据表，可以这样做：
shell> mysqldump test t1 t3 t7 > dump.sql
用mysqldump以分隔文本格式转储数据
若你使用mysqldump --tab={dir-name}时，它会使用{dir-name}作为输出目录。并且每张表将会有两份文件，比如数据库表名称是t1，则对应的输出文件为t1.sql与t1.txt。*.txt存放表的数据，一行一条数据。
以下命令将db1数据库的内容转储到/ tmp数据库中的文件：
shell> mysqldump --tab=/tmp db1
*.txt是由mysql的运行的用户持有，因为mysql的用户调用SELECT ... INTO OUTFILE语句写入该文件，所以你需要确保执行该方法的用户具备FILE权限。另外，如果对应的*.txt文件已存在时，导出操作将会报错。
Tips：
FILE权限是一个比较危险的权限，SQL注入之后利用FILE权限可以完成提权，所以处于安全考虑不对外开放该权限。
选项--tab最好仅应用于本地服务器。
要使用不同的格式写入数据文件，mysqldump支持以下选项：
--fields-terminated-by=str
# 分割列的符号 (default: tab).
--fields-enclosed-by=char
# 包围列的字符 (default: no character).
--fields-optionally-enclosed-by=char
# 包围非数据的列字符 (default: no character).
--fields-escaped-by=char
# 用于转义的特殊字符 (default: no escaping).
--lines-terminated-by=str
# 终止符 (default: newline).
在实际使用时，你可以指定Hex也可以使用字符。
--fields-enclosed-by='"'
# 上下是同一个意思
--fields-enclosed-by=0x22
制作数据库备份
3行代码备份一个数据库。
# 不使用 --databases 是避免CREATE 语句导致导入到db2会失败
shell> mysqldump db1 > dump.sql
shell> mysqladmin create db2
shell> mysql db2 < dump.sql
导出存储过程、计划事件、触发器
- --events : 导出计划事件
- --routines : 导出存储过程
- --triggers : 导出触发器
--triggers默认是开启的，其它两个是默认关闭的。如果需要导出，则需要显示的指定。如果明确无需导出，可以设置：--skip-events，--skip-routines，--skip-triggers。
定时备份
有了上面的概念对导出导入有了基本认识，接下去借助crontab即可完成定时备份的效果。
不了解crontab可以参考文档：Configuring Cron Tasks。
1. 创建并授权备份用户
CREATE USER 'backup'@'%' IDENTIFIED BY '你的密码'
GRANT SELECT,LOCK TABLES ON *.* TO 'backup'@'%' IDENTIFIED BY '你的密码';
2. 编写备份脚本
#!/bin/bash
#定义有备份的数据库名 定义远程数据库
dbname="你要备份的数据库名" dbhost="数据库HOST"
#定义远程数据库端口
dbport=3306
#定义备份数据库时使用的用户名和密码
dbuser="你的mysql用户名" dbpasswd="你的密码"
#数据库备份的路径
backuppath=/home/mysql/${dbhost}/
#数据库备份日志文件存储的路径
logfile=/home/mysql/${dbhost}/logs/bak.log
#以当前的时间作为备份的数据库命名。
dumpfile=${dbname}-$(date +%Y%m%d%H%M)
#这个函数用来备份数据库
back_db() {
#将备份的时间、数据库名存入日志
echo "------"$(date +%Y-%m-%d%t%A%t%T)" Beginning database "${dbname}" backup--------" >>${logfile}
#备份数据库，如果有错误信息也记入日志。 默认utf8编码
/usr/bin/mysqldump -u${dbuser} -p${dbpasswd} -h ${dbhost} -P ${dbport} --default-character-set=utf8 --databases ${dbname} >${backuppath}${dumpfile}.sql 2>> ${logfile}
#开始压缩数据文件
echo $(date +%Y-%m-%d%t%A%t%T)" Beginning zip ${backuppath}${dumpfile}.sql" >>${logfile}
#将备份数据库文件库压成ZIP文件，并删除先前的SQL文件。如果有错误信息也记入日志。
tar zcvf ${dumpfile}.tar.gz ${dumpfile}.sql && rm ${dumpfile}.sql 2>> ${logfile}
#将压缩后的文件名存入日志。
echo "backup file name:"${dumpfile}".tar.gz" >>${logfile}
echo -e "-------"$(date +%Y-%m-%d%t%A%t%T)" Ending database "${dbname}" backup-------\n" >>${logfile}
}
#发送邮件
#cat ${logfile} | mutt -s "Blog数据库备份" -a ${dumpfile}.tar.gz 8chf@163.com }
rm_oldfile() {
#查找出当前目录下7天前生成的文件，并将之删除
find ${backuppath} -type f -mtime +7 -exec rm {} \;
}
#切换到数据库备份的目录。如果不做这个操作，压缩文件时有可能会错误
cd ${backuppath}
#运行备份数据函数
back_db
#运行删除文件函数
rm_oldfile
3. 设置备份目录
-
220.*.*.*- 约定以mysql服务器host为文件夹，每个host下备份只有该host下的备份。
-
logs目录存放备份过程的日志。
-
shell目录下则是备份脚本。
4. 启用计划任务
若服务器没有安装crontab服务，则使用yum -y install crontab安装即可。
shell > crontab -e
编辑文件并保存。
15 14 * * * /home/mysql/shell/bak-220.*.*.*.sh > /dev/null
