mysql异地备份_mysql数据库异地备份的俩种思路与实践

一、直连数据库备份

1、使用navicat进行定时备份

这种备份方法详见http://tech.huweishen.com/gongju/1182.html，该方法在windows和centos中都可以实现，直接连接数据库进行定时备份。

2、在centos下使用脚本文件进行备份

以下脚本测试可以实现备份，参考http://www.jb51.net/article/84514.htm实现

!/bin/sh

BACKUP_BIN=/usr/bin/mysqlbinlog

LOCAL_BACKUP_DIR=/opt/mysql_log_data/mysqllog

BACKUP_LOG=/opt/mysql_log_data/baklog

REMOTE_HOST=...

REMOTE_PORT=端口号

REMOTE_USER=用户名

REMOTE_PASS=用户密码

FIRST_BINLOG=logbin.000001

time to wait before reconnecting after failure

SLEEP_SECONDS=10

create local_backup_dir if necessary

mkdir -p ${LOCAL_BACKUP_DIR}

cd ${LOCAL_BACKUP_DIR}

运行while循环，连接断开后等待指定时间，重新连接

while :

do

if [ ls -A "${LOCAL_BACKUP_DIR}" |wc -l -eq 0 ];then

LAST_FILE=${FIRST_BINLOG}

else

LAST_FILE=ls -l ${LOCAL_BACKUP_DIR} | grep -v backuplog |tail -n 1 |awk '{print $9}'

fi

${BACKUP_BIN} --raw --read-from-remote-server --stop-never --host=${REMOTE_HOST} --port=${REMOTE_PORT} --user=${REMOTE_USER} --password=${REMOTE_PASS} ${LAST_FILE}

echo "date +"%Y/%m/%d %H:%M:%S" mysqlbinlog停止，返回代码：$?" | tee -a ${BACKUP_LOG}

echo "${SLEEP_SECONDS}秒后再次连接并继续备份" | tee -a ${BACKUP_LOG}

sleep ${SLEEP_SECONDS}

done

3、缺点：

个人认为这种方法最显著的缺点就是：数据库直接暴露在公网环境中，实际的生产环境是不允许的。

二、间接拷贝备份文件(详细介绍这种思路)

1、开启mysql的binlog日志文件

修改mysql的my.cnf(Linux)或者my.ini(Windows)文件，添加如下内容

[mysqld]

skip-grant-tables

lower_case_table_names = 1 #大小写不敏感

log-bin = logbin.log #log-bin若不显示制定存储目录,则默认存储在mysql的data目录下

log-bin-index = logindex

expire_logs_days = 7#日志保留天数为七天

server-id=1 #在设置log-bin的同时需要设置server-id,否则会报错

binlog_format=row #row基于行的存储,启动后会产生mysql-bin.*这样的文件,每启动一次就会增加一个

重启mysql--systemctl restart mysqld

进入mysql中查看日志文件是否开启-show variables like '%log_bin%'；

弹出以下内容：

+---------------------------------+-------------------------------+

| Variable_name | Value |

+---------------------------------+-------------------------------+

| log_bin | ON |

| log_bin_basename | /var/lib/mysql/logbin |

| log_bin_index | /var/lib/mysql/logindex.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| sql_log_bin | ON |

+---------------------------------+-------------------------------+

log_bin_basename和log_bin_index属性是可以修改，上述是默认位置

修改只需在/etc/my.cnf中log-bin = logbin.log 和log-bin-index = logindex加入绝对路径即可；

2、做一次全量备份

mysqldump -h IP地址 -P端口号 -u用户 -p密码 --databases 数据库名称> /opt/mysql_log_data/date +%Y%m%d.sql

3、定时做增量备份

!/bin/bash

export LANG=en_US.UTF-8

BakDir=本地路劲

BinDir=/var/lib/mysql

LogFile=logbin文件的存放路径，具体看你设置为多少

BinFile=logindex.logindex路径

mysqladmin -u用户名-p密码 flush-logs

这个是用于产生新的mysql-bin.00000*文件,必须使用mysqladmin

Counter=wc -l $BinFile |awk '{print $1}'

NextNum=0

这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的。

for file in cat $BinFile

do

base=basename $file

#basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./

NextNum=expr $NextNum + 1

if [ $NextNum -eq $Counter ]

then

echo $base skip! >> $LogFile

else

dest=$BakDir/$base

if(test -e $dest)

#test -e用于检测目标文件是否存在，存在就写exist!到$LogFile去。

then

echo $base exist! >> $LogFile

else

cp $BinDir/$base $BakDir

echo $base copying >> $LogFile

fi

fi

done

echo date +"%Y年%m月%d日 %H:%M:%S" Bakup succussful! >> $LogFile

开启定时器vim /etc/crontab

加入脚本文件的执行

00 00 * * * 脚本文件路径 >/dev/null 2>&1

定时器的设置详见https://www.cnblogs.com/wt645631686/p/6868672.html

开启定时器crontab /etc/crontab

查看定时器 crontab -l

4、安装expect ---yum -install expect

5、写scp拷贝脚本(需要首先安装ssh服务，并配置端口)

!/usr/bin/expect

set timeout 100

set password "密码"

spawn scp -r -P 端口root@IP地址:目标文件夹/. 本地文件夹

expect "终端提示要输入密码的前面内容"

send "$password\n"

interact

6、写定时执行scp拷贝脚本(因为source sh执行出错)

!/bin/sh

cd scp存放的绝对路径

./脚本名称

加入定时器

完成以上6步就实现了远程数据库的备份，这种基于ssh的备份是相对安全的

欢迎批评指正