Mysql增量备份与恢复

案例：

关闭防火墙：

systemctl stop firewalld

iptables -F

setenforce 0

安装mariadb mariadb-server

yum -y install mariadb

yum -y install mariadb-server

vim /etc/my.cnf

[mysqld]

server-id=1

log-bin=mysql-bin

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

default-storage-engine = INNODB

character-set-server = utf8

collation-server = utf8_general_ci

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/run/mariadb/mariadb.pid

# include all files from the config directory

!includedir /etc/my.cnf.d

添加数据库、表，录入数据

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

mysql> create database client;

mysql> use client;

mysql> create table user_info(身份证 char(20) not null,姓名 char(20) not null,性别 char(4),用户ID号 char(10) not null,资费 int(10));

mysql> insert into user_info values('000000006','张三','男','016','10');

mysql> insert into user_info values('000000007','李四','女','017','91');

mysql> insert into user_info values('000000008','王五','女','018','23');

mysql> select * from user_info;

先进行一次完全备份

[root@localhost ~]# mkdir /mysql_bak

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

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

[root@localhost ~]# ls /mysql_bak/

client-2018-12-14.sql client_userinfo-2018-12-14.sql

进行一次日志回滚（生成新的二进制日志）

[root@localhost ~]# ls /usr/local/mysql/data

client ibdata1 ib_logfile0 ib_logfile1 localhost.localdomain.err mysql mysql-bin.000001 mysql-bin.index mysql.sock test

[root@localhost ~]# mysqladmin -uroot -p123456 flush-logs

[root@localhost ~]# ls /usr/local/mysql/data

client ibdata1 ib_logfile0 ib_logfile1 localhost.localdomain.err mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql.sock test

继续录入新的数据

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

mysql> use client;

mysql> insert into user_info values('000000009','赵六','男','019','37');

mysql> insert into user_info values('0000000010','孙七','男','020','36');

mysql> select * from user_info;

进行增量备份

[root@localhost ~]# mysqladmin -uroot -p123456 flush-logs

[root@localhost ~]# ls /usr/local/mysql/data

client ib_logfile0 localhost.localdomain.err mysql-bin.000001 mysql-bin.000003 mysql.sock ibdata1 ib_logfile1 mysql mysql-bin.000002 mysql-bin.index test

[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000002 //查看新操作的日志记录

[root@localhost ~]# cp -p /usr/local/mysql/data/mysql-bin.000002 /mysql_bak/

模拟误操作删除user_info表

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