Mysql备份所有数据库Shell脚本

【官方自带】使用命令行shell测试执行mysqldump，理解必备的参数，查看生成的sql备份文件是否符合需求

/usr/bin/mysqldump --opt -u用户 -p密码 -h主机地址  > /home/mysqlBackup/db_`date +%F`.sql

注解：
1、执行 /usr/bin/mysqldump；
2、--opt是quick，add-drop-table，add-locks，extended-insert，lock-tables几个参数的合称，一般都要使用，具体意思自行搜索；
3、-u数据库用户名 -p数据库用户密码 -h数据库地址 数据库名 > 导出的文件路径；
4、`date +%F`是shell中生成当前日期，格式如2018-12-11，所以成功导出时生成的文件名为 db_2018-12-11.sql；
5、下载生成的 sql文件 ，用文本编辑器打开检查，本地导入测试数据库，看是否有问题；

直接导出所有数据库

这个是直接使用mysqldump命令导出MySQL所有数据库的命令，包括MySQL本身的一些库，例如用户等信息也一并导出。
优点：备份完整，用户名和密码也一并备份
缺点：导入之后可能会改动原有的数据库用户信息(覆盖/重复/修改密码)，而且如果某一个库有改动更新，恢复起来相当麻烦。

#备份数据库
mysqldump -u用户名 -p密码 --lock-all-tables --all-databases > all.sql
#导入数据库
mysql -u用户名 -p密码 < all.sql

想要导出所有数据表，一般来说是需要root权限的，具体的信息可以在~/.my.cnf下找到。

分别导出数据库

有需求有脚本有内容，将网站数据库分别导出到各自的文件（尤其适合多业务多虚拟站点的用户使用）。
优点：不影响MySQL本身的信息，不会改动原有数据库的用户名和密码

缺点：导入之后需要手动建立用户名并赋予权限，需重新修改网站配置文件

#!/bin/bash
 
USER="用户名"
PASSWORD="密码"
#Host="主机"
 
databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
 
for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
       # gzip $OUTPUT/`date +%Y%m%d`.$db.sql
    fi
done


##有条件的可以自行根据时间来删除#删除.sql文件，以节约空间。

（网络上其他的脚本要么太过于复杂，要么就是很冗余，要么就是扯淡的抄来抄去，这个已经是作者本人实践比较精简的写法，欢迎高手来撩！！！）

记得vim完成之后给该脚本文件目录 chmod +x sqlAutoBak.sh 添加执行权限，否则会报错  Permission denied 。

想要导出所有数据表，一般来说是需要root权限的，具体的信息可以在~/.my.cnf下找到。

备份所有用户名密码

mysql -BNe "select concat('\'',user,'\'@\'',host,'\'') from mysql.user where user != 'root'" | \
while read uh; do mysql -BNe "show grants for $uh" | sed 's/$/;/; s/\\\\/\\/g'; done > grants.sql

设置linux定时任务执行该脚本

1、编辑定时任务列表

crontab -e

2、插入下面这一行，因为通常来说5点钟网站的访问量最低。

00 03 *   * * /bin/sh /home/renwujihua/sqlBak/sqlAutoBak.sh
#每天早上 3:00am 执行

3、查看任务是否创建成功

crontab -l

第二天检查自动生成的sql文件是否符合要求

如果生成的文件和解压出来查看没有问题，那么这个自动定时备份数据库的脚本就算是完成了。因为生成的文件多了会占用一定的空间，所以建议要定期（比如一个月）清理删除一下文件。

$、补充

若该数据库的用户没有分配锁表的权限，则备份会报错 when using LOCK TABLES 。那是因为mysqldump命令默认在导出时是要锁定表的（另外使用innodb引擎的数据库在占用或者使用了索引也会锁表的），所以解决方式有两个：

一个是给该用户开放锁表的权限；

另一个是在命令中加上  --skip-lock-tables 这个参数。即是：

$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME --skip-lock-tables | gzip > $BCK_DIR/db_$DATE.sql.gz

##这种备份的数据库，需要解压即可还原成sql格式.