远程mysql数据库自动备份与恢复

一、linux环境下使用crontab定时任务执行脚本

crontab命令未安装需要先安装

crond -e命令进行定时任务的编辑

crontab -l查看设置好的定时任务

任务格式如下(每天凌晨1点备份)：0 1 * * * /home/database_mysql_shell_4all.sh

database_mysql_shell_4all.sh脚本（4个数据库同时备份，保留最近5天的数据）：

#!/bin/bash
DATE=`date +%Y%m%d%H%M`                #every minute
DB_USERNAME=xx                       #database username
DB_PASSWORD=xx                    #database password
DATABASE_1=xxx              #database name
DATABASE_BACKUP_PATH_1=/home/mysqldata4all/firstDir          #backup path

DATABASE_2=yyy              #database name
DATABASE_BACKUP_PATH_2=/home/mysqldata4all/secondDir          #backup path

DATABASE_3=zzz              #database name
DATABASE_BACKUP_PATH_3=/home/mysqldata4all/thirdDir          #backup path

DATABASE_4=aaa              #database name
DATABASE_BACKUP_PATH_4=/home/mysqldata4all/forthDir          #backup path

# first one
#backup command

/usr/bin/mysqldump -u$DB_USERNAME -p$DB_PASSWORD -h 127.0.0.1 -R --opt $DATABASE_1 | gzip > ${DATABASE_BACKUP_PATH_1}\/${DATABASE_1}_${DATE}.sql.gz

#just backup the latest 5 days

find ${DATABASE_BACKUP_PATH_1} -mtime +5 -name "${DATABASE_1}_*.sql.gz" -exec rm -f {} \;

# second one
#backup command

/usr/bin/mysqldump -u$DB_USERNAME -p$DB_PASSWORD -h 127.0.0.2 -R --opt $DATABASE_2 | gzip > ${DATABASE_BACKUP_PATH_2}\/${DATABASE_2}_${DATE}.sql.gz

#just backup the latest 5 days

find ${DATABASE_BACKUP_PATH_2} -mtime +5 -name "${DATABASE_2}_*.sql.gz" -exec rm -f {} \;

# third one
#backup command

/usr/bin/mysqldump -u$DB_USERNAME -p$DB_PASSWORD -h 127.0.0.3 -R --opt $DATABASE_3 | gzip > ${DATABASE_BACKUP_PATH_3}\/${DATABASE_3}_${DATE}.sql.gz

#just backup the latest 5 days

find ${DATABASE_BACKUP_PATH_3} -mtime +5 -name "${DATABASE_31}_*.sql.gz" -exec rm -f {} \;

# forth one
#backup command

/usr/bin/mysqldump -u$DB_USERNAME -p$DB_PASSWORD -h 127.0.0.4 -R --opt $DATABASE_4 | gzip > ${DATABASE_BACKUP_PATH_4}\/${DATABASE_4}_${DATE}.sql.gz

#just backup the latest 5 days

find ${DATABASE_BACKUP_PATH_4} -mtime +5 -name "${DATABASE_4}_*.sql.gz" -exec rm -f {} \;

 二，还原

dump下来的sql文件中，批量插入语句示例：

INSERT INTO `aaa` VALUES (1,'a','b','c'),(2,'e','f','g');

以上语句可能很长，超过数据库默认max_allowed_packet的限制，或者包含大文件数据等，都可能在上传时报错（navicat的转出sql操作对每一条记录都会生成一条单独insert语句，不会出现这个问题）：[ERR]2006-MySQL server has gone away

解决方法：

1 方法1
可以编辑my.cnf来修改（windows下my.ini）,在[mysqld]段或者mysql的server配置段进行修改。 max_allowed_packet = 20M
如果找不到my.cnf可以通过
mysql --help | grep my.cnf
去寻找my.cnf文件。

1，找到 mysql 安装目录下的 my.ini 配置文件，加入以下代码：

max_allowed_packet=500M（或更大）

参数注释如下：

max_allowed_packet 是 mysql 允许最大的数据包，用来控制其通信缓冲区的最大长度。

最后重启下 mysql 服务：service mysqld restart

2，临时调整max_allowed_packet参数：

show VARIABLES like '%max_allowed_packet%';

set global max_allowed_packet = 500 * 1024 * 1024;

执行后再次恢复，成功 

三，还原方式：

1，navicat执行sql脚本还原

2，win+r，输入cmd，运行。（含数据，不含表机构）

dump的格式：mysqldump -h [ip地址] -P [端口号] -u [用户名] -p [数据库] [表名] --where="[条件]" > [导出sql文件位置]

例子：mysqldump -h 127.0.0.1 -P 3307 -u root -p test student --where="id=1" >D:\123.sql

mysqldump也可以按需要导出结果，条件在where中添加（where可以不要）。

问题：如果没有mysqldump命令，需要到c盘mysql安装目录的bin目录下执行cmd；如果失败，使用mysql -p -u user_name database_name < xxx.sql导入