mysql增量备份及恢复_MYSQL增量备份与恢复

vim /etc/my.cnf

在[mysqld]下添加

max_binlog_size = 1024000 //二进制日志最大1M

要进行mysql的增量备份，首先要开启二进制日志功能

方法一：

在/etc/my.cnf文件中的[mysqld]下，添加

log-bin = mysql-bin

方法二：

使用mysqld --log-bin = 文件存放路径/文件前缀，然后重新启动mysql服务。

mysql增量恢复的方法：

一般恢复：

mysqlbinlog [--no-defaults] 增量备份文件 | mysql -uroot -p密码

基于时间点的恢复：

从日志开头截止到某个时间点的恢复：

mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -uroot -p密码

从某个时间点到日志结尾的恢复：

mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -uroot -p密码

从某个时间点到某个时间的恢复：

mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日 小时:分钟:秒 二进制日志 | mysql -uroot -p密码

基于位置的恢复：

mysqlbinlog --stop-position='操作id' 二进制日志 | mysql -uroot -p密码

mysqlbinlog --start-position='操作id' 二进制日志 | mysql -uroot -p密码

[root@localhost ~]# vim /opt/mysql_bak_zengbei.sh //shell脚本增量备份

#!/bin/bash

#MYSQL数据库增量备份脚本。

#设置登录变量

MY_USER="root"

MY_PASS="111111"

MY_HOST="localhost"

MY_CONN="-u$MY_USER -p$MY_PASS -h$MY_HOST"

#定义备份路径、工具、二进制日志前缀、二进制日志存放路径

BF_TIME="$(date +%Y%m%d)"

BF_DIR="/mysql_bak/zengbei/$BF_TIME"

CMD="/usr/bin/mysqladmin"

QZ="mysql-bin"

LOG_DIR="/var/lib/mysql"

#拷贝二进制日志

[ -d $BF_DIR ] || mkdir-p $BF_DIR

$CMD $MY_CONN flush-logs

/bin/cp -p $(ls $LOG_DIR/$QZ.* | awk -v RS="" '{print $(NF-2)}') $BF_DIR

案例：

[root@localhost ~]# mysql -uroot -p111111

创建数据库

MariaDB [(none)]> create database client;

使用数据库

MariaDB [(none)]> use client

创建数据表

MariaDB [auth]> create table user_info (身份证 int(20),姓名 char(20),性别 char(2),用户ID号 int(110),资费 int(10));

插入数据到数据表

MariaDB [auth]> insert into user_info values ('000000001','孙空武','男','011','100');

MariaDB [auth]> insert into user_info values ('000000002','蓝凌','女','012','98');

MariaDB [auth]> insert into user_info values ('000000003','姜文','女','013','12');

MariaDB [auth]> insert into user_info values ('000000004','关园','男','014','38');

MariaDB [auth]> insert into user_info values ('000000005','罗中昆','男','015','39');

先进行一次完全备份

[root@localhost ~]# mkdir /mysql_bak

[root@localhost ~]# mysqldump -uroot -p111111 client user_info > /mysql_bak/client_user_info-$(date +%F).sql

[root@localhost ~]# mysqldump -uroot -p111111 client > /mysql_bak/client-$(date +%F).sql

先进行一次日志回滚(生成新的日志)

[root@localhost ~]# ls /var/lib/mysql/

aria_log.00000001 client ib_logfile1 mysql.sock

aria_log_control ibdata1 mysql performance_schema

auth ib_logfile0 mysql-bin.000001 mysql-bin.index test

[root@localhost ~]# cd /var/lib/mysql

[root@localhost mysql]# mysqladmin -uroot -p111111 flush-logs

[root@localhost mysql]# ls

aria_log.00000001 client ib_logfile1 mysql-bin.000002 performance_schema

aria_log_control ibdata1 mysql mysql-bin.index test

auth ib_logfile0 mysql-bin.000001 mysql.sock

继续录入新的数据

[root@localhost ~]# mysql -uroot -p111111

MariaDB [(none)]> use client

MariaDB [client]> insert into user_info values('6','蓝精灵','男','16','78');

MariaDB [client]> insert into user_info values('7','红头发','女','17','23');

MariaDB [client]>select * from user_info;

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

| 身份证   | 姓名      | 性别   | 用户ID号 | 资费   |

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

|     1        | 孙空武  | 男       |     11        | 100    |

|     2        | 蓝凌      | 女      |     12        | 98      |

|     3        | 姜文      | 女      |     13        | 12      |

|     4        | 关园      | 男      |     14        | 38      |

|     5        | 罗中昆   | 男     |     15         | 39     |

|     6        | 蓝精灵   | 男     |     16         | 78     |

|     7        | 红头发   | 女     |     17         | 23     |

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

ctrl + D退出

进行增量备份

[root@localhost mysql]# mysqladmin -uroot -p111111 flush-logs

[root@localhost mysql]# ls

aria_log.00000001 client ib_logfile1 mysql-bin.000002 mysql.sock

aria_log_control ibdata1 mysql mysql-bin.000003 performance_schema

auth ib_logfile0 mysql-bin.000001 mysql-bin.index test

[root@localhost mysql]# mysqlbinlog --no-defaults mysql-bin.000002

内容省略。。。。。。

[root@localhost mysql]# cp -p mysql-bin.000002 /mysql_bak/

模拟误操作删除user_info表

[root@localhost mysql]# mysql -uroot -p111111 -e'drop table client.user_info;'

[root@localhost mysql]# mysql -uroot -p111111 -e'select * from client.user_info;'

ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

恢复完全备份

[root@localhost mysql]# mysql -uroot -p111111 client < /mysql_bak/client-20191014-1414.sql

[root@localhost mysql]# mysql -uroot -p111111 -e'select * from client.user_info;'

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

| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |

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

| 1 | 孙空武 | 男 | 11 | 100 |

| 2 | 蓝凌 | 女 | 12 | 98 |

| 3 | 姜文 | 女 | 13 | 12 |

| 4 | 关园 | 男 | 14 | 38 |

| 5 | 罗中昆 | 男 | 15 | 39 |

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

恢复增量备份

[root@localhost mysql]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -uroot -p111111

[root@localhost mysql]# mysql -uroot -p111111 -e'select * from client.user_info;'

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

| 身份证   | 姓名      | 性别   | 用户ID号 | 资费   |

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

|     1        | 孙空武  | 男       |     11        | 100    |

|     2        | 蓝凌      | 女      |     12        | 98      |

|     3        | 姜文      | 女      |     13        | 12      |

|     4        | 关园      | 男      |     14        | 38      |

|     5        | 罗中昆   | 男     |     15         | 39     |

|     6        | 蓝精灵   | 男     |     16         | 78     |

|     7        | 红头发   | 女     |     17         | 23     |

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

[root@localhost ~]# mysql -uroot -p111111 -e'drop table client.user_info;'

[root@localhost ~]# mysql -uroot -p111111 -e'select * from client.user_info;'

ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

[root@localhost ~]# mysql -uroot -p111111 client < /mysql_bak/client-20191014-1414.sql

[root@localhost ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002

基于时间点的恢复：

--stop-datetime

仅恢复到2019-10-14  14:22:53之前的数据，不再恢复“红头发”的信息

[root@localhost ~]# mysqlbinlog --no-defaults --stop-datetime='2019-10-14   14:22:53' /mysql_bak/mysql-bin.000002 | mysql -uroot -p111111

[root@localhost ~]# mysql -uroot -p111111 -e'select * from client.user_info'

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

| 身份证   | 姓名      | 性别   | 用户ID号 | 资费   |

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

|     1        | 孙空武  | 男       |     11        | 100    |

|     2        | 蓝凌      | 女      |     12        | 98      |

|     3        | 姜文      | 女      |     13        | 12      |

|     4        | 关园      | 男      |     14        | 38      |

|     5        | 罗中昆   | 男     |     15         | 39     |

|     6        | 蓝精灵   | 男     |     16         | 78     |

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

仅恢复“红头发”，跳过“蓝精灵”的信息

--start-datetime

[root@localhost ~]# mysql -uroot -p111111 -e'drop table client.user_info;'

[root@localhost ~]# mysql -uroot -p111111 -e'select * from client.user_info;'

ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

[root@localhost ~]# mysql -uroot -p111111 client < /mysql_bak/client-20191014-1414.sql

[root@localhost ~]# mysqlbinlog --no-defaults --start-datetime='2019-10-14 14:22:53' /mysql_bak/mysql-bin.000002 | mysql -uroot -p111111

[root@localhost ~]# mysql -uroot -p111111 -e'select * from client.user_info;'

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

| 身份证   | 姓名      | 性别   | 用户ID号 | 资费   |

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

|     1        | 孙空武  | 男       |     11        | 100    |

|     2        | 蓝凌      | 女      |     12        | 98      |

|     3        | 姜文      | 女      |     13        | 12      |

|     4        | 关园      | 男      |     14        | 38      |

|     5        | 罗中昆   | 男     |     15         | 39     |

|     7        | 红头发   | 女     |     17         | 23     |

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

基于位置的恢复：

--stop-possion

[root@localhost ~]# mysql -uroot -p111111 -e'drop table client.user_info;'

[root@localhost ~]# mysql -uroot -p111111 -e'select * from client.user_info;'

ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

[root@localhost ~]# mysql -uroot -p111111 client < /mysql_bak/client-20191014-1414.sql

[root@localhost ~]# mysqlbinlog --no-defaults --stop-position='468' /mysql_bak/mysql-bin.000002 | mysql -uroot -p111111

[root@localhost ~]# mysql -uroot -p111111 -e'select * from client.user_info'

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

| 身份证   | 姓名      | 性别   | 用户ID号 | 资费   |

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

|     1        | 孙空武  | 男       |     11        | 100    |

|     2        | 蓝凌      | 女      |     12        | 98      |

|     3        | 姜文      | 女      |     13        | 12      |

|     4        | 关园      | 男      |     14        | 38      |

|     5        | 罗中昆   | 男     |     15         | 39     |

|     6        | 蓝精灵   | 男     |     16         | 78     |

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

--start-possion

[root@localhost ~]# mysql -uroot -p111111 -e'drop table client.user_info;'

[root@localhost ~]# mysql -uroot -p111111 -e'select * from client.user_info;'

ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

[root@localhost ~]# mysql -uroot -p111111 client < /mysql_bak/client-20191014-1414.sql

[root@localhost ~]# mysqlbinlog --no-defaults --start-position='538' /mysql_bak/mysql-bin.000002 | mysql -uroot -p111111

[root@localhost ~]# mysql -uroot -p111111 -e'select * from client.user_info'

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

| 身份证   | 姓名      | 性别   | 用户ID号 | 资费   |

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

|     1        | 孙空武  | 男       |     11        | 100    |

|     2        | 蓝凌      | 女      |     12        | 98      |

|     3        | 姜文      | 女      |     13        | 12      |

|     4        | 关园      | 男      |     14        | 38      |

|     5        | 罗中昆   | 男     |     15         | 39     |

|     7        | 红头发   | 女     |     17         | 23     |

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