MySQL workbench数据库定时备份 mysql定时备份到另一台mysql

文章目录

• 说明
• 一、mysql数据备份

• 1.mysqldump命令备份数据

• 1.1全量备份
• 1.2 增量备份

• 增量备份的shell脚本
• 二、远程备份文件

• 1.创建备份文件脚本
• 2.备份文件脚本内容

• 三、Linux定时任务
• 四、采过的坑

• 1.expect: 未找到命令
• 2.mysqldump: 未找到命令
• 3.mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect
• 4.用ssh远程创建目录报错

---

说明

关于数据备份也是一件不让人省心的事，公司内部已有数据备份和系统备份的功能，为了进一步确保数据安全，除了本地备份外增加远程备份，经过查找和整理，具体内容如下：

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、mysql数据备份

1.mysqldump命令备份数据

线上的数据库定时做全量备份和增量备份。
增量备份的优点是没有重复数据，备份量不大，时间短。但缺点也很明显，需要建立在上次完全备份及完全备份之后所有的增量才能恢复。

MySQL没有提供直接的增量备份方法，但是可以通过mysql二进制日志间接实现增量备份。二进制日志对备份的意义如下：

• 二进制日志保存了所有更新或者可能更新数据的操作
• 二进制日志在启动MySQL服务器后开始记录，并在文件达到所设大小或者收到flush logs 命令后重新创建新的日志文件
• 只需定时执行flush logs 方法重新创建新的日志，生成二进制文件序列，并及时把这些文件保存到一个安全的地方，即完成了一个时间段的增量备份。

1.1全量备份

mysqldump --lock-all-tables --flush-logs --master-data=2 -u root -ppassword mysql > mysql_`date +%Y-%m-%d_%H:%M:%S`.sql

• 参数 --lock-all-tables 对于InnoDB将替换为 --single-transaction。
  该选项在导出数据之前提交一个 BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 InnoDB 和 BDB。本选项和 --lock-tables 选项是互斥的，因为 LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用 --quick 选项。
• 参数 --flush-logs，结束当前日志，生成并使用新日志文件
• 参数 --master-data=2，该选项将会在输出SQL中记录下完全备份后新日志文件的名称，用于日后恢复时参考，例如输出的备份SQL文件中含有：CHANGE MASTER TO MASTER_LOG_FILE='MySQL-bin.000002', MASTER_LOG_POS=27;
• 参数 mysql，该处的mysql表示数据库mysql，如果想要将所有的数据库备份，可以换成参数 --all-databases
• 参数 --databases 指定多个数据库
• 参数 --quick或-q，该选项在导出大表时很有用，它强制 MySQLdump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。
• 参数 --ignore-table，忽略某个数据表，如 --ignore-table mysql.user 忽略数据库mysql里的user表
• 更多mysqldump 参数，请参考官网

创建备份脚本文件

vi /app/mysql_full_bak.sh

全量备份脚本shell

#!/bin/bash
#保存备份个数，备份31天数据
number=31
#备份保存路径
backup_dir=/root/mysqlbackup
#日期
dd=`date +%Y-%m-%d_%H:%M:%S`
#备份工具
tool=mysqldump
#ip地址
ip=127.0.0.1
#端口号
port=3306
#用户名
username=root
#密码
password=password
#将要备份的数据库
database_name=database

#如果文件夹不存在则创建
if [ ! -d $backup_dir ]; 
then     
    mkdir -p $backup_dir; 
fi

$tool -h$ip -P$port -u $username -p$password $database_name > $backup_dir/$database_name-$dd.sql

#写创建备份日志
echo "create $backup_dir/$database_name-$dd.dupm" >> $backup_dir/log.txt

#找出需要删除的备份
delfile=`ls -l -crt  $backup_dir/*.sql | awk '{print $9 }' | head -1`

#判断现在的备份数量是否大于$number
count=`ls -l -crt  $backup_dir/*.sql | awk '{print $9 }' | wc -l`

if [ $count -gt $number ]
then
  #删除最早生成的备份，只保留number数量的备份
  rm $delfile
  #写删除文件日志
  echo "delete $delfile" >> $backup_dir/log.txt
fi

#上传到远程服务器储存
expect -c"
spawn scp -r $backup_dir/$database_name-$dd.sql root@127.0.0.1:/filebak/datafile/
expect {
    \"yes/no\" {set \"yes\r\"; exp_continue;}
    \"password\" {set timeout 3600; send \"hostpassword\r\";}
    #\"yes/no\" {set \"yes\r\"; exp_continue;}
}
expect eof"

1.2 增量备份

1. 检查log_bin是否开启
   进入mysql命令行，执行 show variables like ‘%log_bin%’

mysql> show variables like ‘%log_bin%’;
±--------------------------------±------+
| Variable_name | Value |
±--------------------------------±------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
±--------------------------------±------+
6 rows in set (0.03 sec)
如上所示，log_bin 未开启；如果log_bin开启，则跳过第2步，直接进入第3步。

或者用navicat也可以查询

2. 开启 log_bin，并重启mysql
   编辑 mysql 的配置文件 vim /etc/my.cnf，在 mysqld 下面添加下面2条配置

[mysqld]

log-bin=/data/mysql/mysql-bin

server-id=1

Tip1: 一定要加 server-id，否则会报错。至于server-id的值，随便设就可以。

Tip2: log-bin 中间可以-减号相连，也可以下划线_相连。同理server-id也一样。

重启mysql

yum安装用service mysqld restart tar.gz安装用systemctl restart mysqld

再次在mysql命令行中执行 show variables like ‘%log_bin%’

mysql> show variables like ‘%log_bin%’;
 ±--------------------------------±----------------------------+
 | Variable_name | Value |
 ±--------------------------------±----------------------------+
 | log_bin | ON |
 | log_bin_basename | /data/mysql/mysql-bin |
 | log_bin_index | /data/mysql/mysql-bin.index |
 | log_bin_trust_function_creators | OFF |
 | log_bin_use_v1_row_events | OFF |
 | sql_log_bin | ON |
 ±--------------------------------±----------------------------+
 6 rows in set (0.08 sec)

3. 备份
   进入mysql命令行，执行 show master status;

mysql> show master status;

当前正在记录日志的文件名是 mysql-bin.000001

查询当前mysql库里的emp表

select * from emp

插入一条新的记录

insert into emp(no, name, sex, birthday) values('A03','小强', '男', '1990-08-05');

执行命令mysqladmin -uroot -p密码 flush-logs，生成并使用新的日志文件

再次查看当前使用的日志文件，已经变为 mysql-bin.000002 了。

mysql-bin.000001 则记录着刚才执行的 insert 语句的日志。

到这里，其实已经完成了增量备份。

恢复增量备份

假如误删数据库记录

delete from emp where id=3;
select * from emp;

从备份的日志文件mysql-bin.000001中恢复数据

[root@localhost ~]# mysqlbinlog --no-defaults /data/mysql/mysql-bin.000001 | mysql -uroot -p mysql
 Enter password:
 ERROR 1032 (HY000) at line 36: Can’t find record in ‘emp’

如果你也遇到这个问题的话，不妨修改/etc/my.cnf配置试试。
我在server_id那一行下添加了 slave_skip_errors=1032 ，然后就执行成功了，不再报错。

增量备份的shell脚本

#!/bin/bash

# 增量备份时复制mysql-bin.00000*的目标目录，提前手动创建这个目录
bakdir=/root/mysqlbin
#如果文件夹不存在则创建
if [ ! -d $bakdir ];
then
    mkdir -p $bakdir;
fi
# 日志目录
logdir=/root/mysqllog
#如果文件夹不存在则创建
if [ ! -d $logdir ];
then
    mkdir -p $logdir;
fi
# 日志文件
logfile=$logdir/bak.log
# mysql的数据目录
bindir=/data/mysql
# mysql的index文件路径，放在数据目录下的
binfile=/data/mysql/mysql-bin.index
# 这个是用于产生新的mysql-bin.00000*文件,如果配置环境变量可以直接执行mysqladmin，否则用绝对路径
/app/mysql/bin/mysqladmin -uroot -ppassword flush-logs
#统计有mysql-bin.index中有多少个文件
count=`wc -l $binfile | awk '{print $1}'`
#最新文件索引
num=0
# 这个for循环用于比对$count,$num这两个值来确定文件是不是存在或最新的
for file in `cat $binfile`
do
  base=`basename $file`
  num=`expr $num + 1`
  if [ $num -eq $count ]
  then
      echo $base skip! >> $logfile
  else
      dest=$bakdir/$base
      #test -e用于检测目标文件是否存在，存在就写exist!到$logfile去
      if(test -e $dest)
      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"` $num bak success! >> $logfile

说明：循环遍历中为什么加要basename，加了之后可以直接去掉取文件前面的目录得到文件名

二、远程备份文件

1.创建备份文件脚本

vi /app/filebak.sh

2.备份文件脚本内容

#!/bin/sh
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/
#导入数据所在路径
importpath=/home/ruoyi/uploadPath
#附件上传路径
path=/home/ruoyi/uploadPath/upload
#说明:减一天因隔天备份前一天的文件
#年
year=`date +'%Y' -d'-1 day'`
#月
month=`date +'%m' -d'-1 day'`
#日
day=`date +'%d' -d'-1 day'`
#day=`date +'%d'`
#echo $path/$year/$month/$day/
#原文件路径
path=$path/$year/$month/$day
echo $path
#备份文件路径
bak_dir=/filebak/appfile/upload/$year/$month
#上传到远程服务器储存
if [ -d $path ];
then
    expect -c"
#    spawn ssh root@127.0.0.1 \"mkdir -p \" $bak_dir
#    expect {
#        \"password\" {set timeout 3600; send \"hostpassword\r\";}
#    }
    spawn scp -r $path root@127.0.0.1:$bak_dir
    expect {
        \"yes/no\" {set \"yes\r\"; exp_continue;}
        \"password\" {set timeout 3600; send \"hostpassword\r\";}
    }
    expect eof"
    if [ $? -eq 0 ]; then
        echo "====文件备份成功!===="
    else
        echo "====文件备份失败!===="
        exit 1
    fi
fi

---

#保留31天的备份
#find $bindir -mtime +31 -name “mysql-bin.0*” -exec rm -rf {} ;

三、Linux定时任务

执行命令 crontab -e，添加如下配置
#备份数据，每天凌晨1点备份数据
0 1 * * * . /etc/profile;/bin/sh /app/mysql_full_bak.sh #备份文件，每天凌晨3点备份前一天文件
0 3 * * * . /etc/profile;/bin/sh /app/filebak.sh 查看定时任务命令crontab -l crontab执行时间计算:在线工具

当定时任务没有达到预期效果时，到/var/spool/mail/root文件中查看报错信息
时间长日志文件也会过大，为避免文件过大可以在定时任务后面增加 >/dev/null 2>&1,具体如下：
0 1 * * * . /etc/profile;/bin/sh /app/mysql_full_bak.sh >/dev/null 2>&1

四、采过的坑

1.expect: 未找到命令

执行下面的命令安装下即可

yum install -y expect

如果没有服务器没有联网，本地下载上传到服务器安装安装
安装expect时有个tcl依赖，先安装依赖
expect下载地址：https://mirrors.ustc.edu.cn/centos/7/os/x86_64/Packages/expect-5.45-14.el7_1.x86_64.rpm tcl下载地址：https://mirrors.ustc.edu.cn/centos/7/os/x86_64/Packages/tcl-8.5.13-8.el7.x86_64.rpm 安装命令：

[root@localhost app]# rpm -ivh tcl-8.5.13-8.el7.x86_64.rpm 
准备中...                          ################################# [100%]
        软件包 tcl-1:8.5.13-8.el7.x86_64 已经安装
[root@localhost app]# rpm -ivh expect-5.45-14.el7_1.x86_64.rpm 
准备中...                          ################################# [100%]
正在升级/安装...
   1:expect-5.45-14.el7_1             ################################# [100%]

2.mysqldump: 未找到命令

linux命令行可以正确执行，放到定时任务就会报错，问题原因是环境变量问题引起的，就是在脚本中增加下面内容，两个都放也没有问题
#指定环境变量
source /etc/profilesource /root/.bash_profile

profile和.bash_profile是mysql配置的环境变量
export PATH=$PATH:/app/mysql/bin

3.mysqldump: Got error: 2002: Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2) when trying to connect

用yum安装的mysql不会出现这种情况
tar.gz文件安装的mysqldump则会报错，如下：

mysqldump -u root -ppassword --quick --events mysql --flush-logs --delete-master-logs --single-transactionmysql > /root/mysqlbackup/mysql_`date +%Y-%m-%d_%H:%M:%S`.sql

后面增加参数 -hip -pport即可解决问题

mysqldump -h127.0.0.1 -p3306 -u root -ppassword --quick --events mysql --flush-logs --delete-master-logs --single-transactionmysql > /root/mysqlbackup/mysql_`date +%Y-%m-%d_%H:%M:%S`.sql

4.用ssh远程创建目录报错

spawn ssh root@127.0.0.1 "mkdir -p " $bak_dir 执行上面命令会报错，内容如下：
invalid command name “ssh”
while executing
“ssh root@127.0.0.1 mkdir”
expect: invalid option – ‘p’
usage: expect [-div] [-c cmds] [[-f] cmdfile] [args]
报错原因是双引号需要转义
spawn ssh root@127.0.0.1 \"mkdir -p \" $bak_dir