mysql增量,MySQL完全、增量的备份与恢复

数据库压缩时会使用压缩率较大的xz格式压缩。

rpm -q xz //检查xz软件包是否安装

yum -y install xz //安装xz软件包

对数据库文件夹(/usr/local/mysql/data/)进行打包操作

tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/

//备份数据库文件夹，并以年月日的形式命名

ls /opt/

mysql-2018-07-02.tar.xz

对数据库文件进行恢复操作

只需将数据库文件的压缩包，解压缩即可，即使整个/data/目录都丢失仍可找回

mkdir /abc

tar Jxf mysql-2018-07-03.tar.xz -C /abc/

完全逻辑备份

使用mysqldump工具备份

备份某个数据库

mysqldump -u root -p stady > /opt/stady.sql

//备份stady库到/opt目录下，-p之后可跟密码，也可不写。不写就在回车之后输入密码验证。 **备份文件后缀都为.sql,前面名称见名知意即可**

查看备份文件的内容

备份多个数据库

mysqldump -u root -p --databases mysql stady > /opt/mysql-stady.sql

备份所有数据库

mysqldump -u root -p --opt --all-databases > /opt//all.sql

备份数据库中的表

mysqldump -u root -p stady info > /opt/stady-info.sql //险些库名，再接表名

备份数据库的表结构

mysqldump -u root -p -d stady info > /opt/decribe-info.sql

// -d 库名+表名

数据恢复

登陆MySQL，使用source命令恢复

恢复”备份数据库中的表“步骤中的info表

[root@centos7-6 /]# mysql -u root -p

Enter password:

··· //省略部分内容

mysql> use stady; //切换到库

Database changed

mysql> drop table info; //删除info表

Query OK, 0 rows affected (0.02 sec)

mysql> show tables; //查看库中表

Empty set (0.00 sec) //库中为空

mysql> source /opt/stady-info.sql //恢复info表，格式是source+备份文件路径和文件名称

Query OK, 0 rows affected (0.02 sec)

··· //省略部分内容

mysql> show tables; //查看库中表

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

| Tables_in_stady |

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

| info | //info表已经成功恢复

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

1 row in set (0.00 sec)

使用mysql命令恢复表

[root@centos7-6 /]# mysql -u root -p

Enter password:

··· //省略部分内容

mysql> use stady; //切换到库

Database changed

mysql> drop table info; //删除info表

Query OK, 0 rows affected (0.02 sec)

mysql> show tables; //查看库中表

Empty set (0.00 sec) //库中为空

mysql> quit //退出数据库，切换到linux

Bye

[root@centos7-6 /]# mysql -u root -p stady < /opt/stady-info.sql //在linux环境中恢复

Enter password:

[root@centos7-6 /]# mysql -u root -p //进入数据库查看

Enter password:

··· //省略部分内容

mysql> use stady;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

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

| Tables_in_stady |

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

| info | //info表已恢复

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

1 row in set (0.00 sec)

恢复库和恢复表的方式相同，但在恢复数据表时要注意，如果备份文件中只备份了表文件，此时mysql中又没有库时，若直接恢复，会失败，必须先创建库，然后再进行恢复。

增量备份

在MySQL数据库中创建stady库，创建info表，并插入数据

mysql> use stady;

mysql> select * from info;

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

| name | score |

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

| zhangsan | 80.00 |

| lisi | 90.00 |

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

2 rows in set (0.00 sec)

开启二进制日志

vim /etc/my.cnf

log-bin=mysql-bin //在[mysqld]段插入该语句

systemctl restart mysqld.service //重启mysql服务,会在/data/目录下生成新的日志文件(空文件)

使用mysqldump备份stady库

mkdir /backup

mysqldump -u root -p stady > /backup/stady.sql

mysqladmin -u root -p flush-logs //刷新生成新的日志文件，存在01中，新存在的02为空文件

插入新数据，生成新的增量日志文件

mysql> insert into info (name,score) values ('chen',90);//进入数据库，向表中插入数据

mysqladmin -u root -p flush-logs //刷新生成新的增量文件，增量备份保存在02中

mysql> insert into info (name,score) values ('chen01',96);//再次进入数据库，向表中插入数据

mysql> select * from info;

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

| name | score |

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

| zhangsan | 80.00 |

| lisi | 90.00 |

| chen | 90.00 |

| chen01 | 96.00 |

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

4 rows in set (0.00 sec)

mysqladmin -u root -p flush-logs //再次刷新，增量备份保存在03中

增量恢复

模拟数据丢失，删除新添加的两条记录

[root@centos7-6 data]# mysql -u root -p

Enter password:

mysql> use stady;

Database changed

mysql> delete from info where name='chen';

Query OK, 1 row affected (0.00 sec)

mysql> delete from info where name='chen01';

Query OK, 1 row affected (0.00 sec)

mysql> select * from info;

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

| name | score |

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

| zhangsan | 80.00 |

| lisi | 90.00 |

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

2 rows in set (0.00 sec)

使用mysqlbinlog命令，恢复数据

[root@centos7-6 data]# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p

Enter password: //输入密码确认恢复数据

[root@centos7-6 data]# mysql -u root -p

Enter password: //输入密码登陆系统

mysql> use stady;

Database changed

mysql> select * from info;

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

| name | score |

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

| zhangsan | 80.00 |

| lisi | 90.00 |

| chen | 90.00 |//增量备份的02数据成功恢复

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

3 rows in set (0.00 sec)

使用mysqlbinlog查看增量备份文件的内容

mysqlbinlog --no-defaults --base64-output-decode-rows -v /usr/local/mysql/data/mysql-bin.000002

//--base64-output=decode-rows使用64位编码机制解码，按行读取 -v 显示出来

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

··· //省略部分内容

# at 345

#180703 21:12:11 server id 1 end_log_pos 389 CRC32 0xb0bc2cb1 Write_rows: table id 118 flags: STMT_END_F

### INSERT INTO `stady`.`info`

### SET

### @1='chen'

### @2=90.00

···//省略部分内容

在开启二进制日志之后就会在/data/目录下生成空的mysql-bin.000001文件，在使用该命令mysqladmin -u root -p flush-logs 后会将增量备份写入mysql-bin.000001，同时生成一个新的mysql-bin.000002空文件，等待下次刷新写入，同时再创建mysql-bin.000003……

增量断点恢复

模拟环境

模拟插入数据出现误操作，在插入两条数据的同时，删除了一条正确的数据。

[root@centos7-6 data]# mysql -u root -p

Enter password:

mysql> use stady;

Database changed

mysql> insert into info (name,score) values ('test01',88);

Query OK, 1 row affected (0.00 sec)

mysql> delete from info where name='chen';

Query OK, 1 row affected (0.00 sec)

mysql> insert into info (name,score) values ('test02',88);

Query OK, 1 row affected (0.01 sec)

mysql> select * from info;

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

| name | score |

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

| zhangsan | 80.00 |

| lisi | 90.00 |

| test01 | 88.00 |

| test02 | 88.00 |

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

4 rows in set (0.00 sec) //插入了两条test01和test02，但是chen被操作删除

mysql> quit //退出数据库

Bye

[root@centos7-6 data]# mysqladmin -u root -p flush-logs //刷新生成新的增量文件，保存在04中

Enter password:

增量时间节点恢复

解码导出增量备份文件，便于查看和做恢复

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000004 > /backup/info.txt

cd /backup

记录错误操作的开始时间，以及正确操作的开始时间，恢复时跳过该时段的操作。

根据时间点恢复被误操作的数据

mysql> drop table info; //删除被误操作的表

mysql -u root -p stady < /backup/stady.sql //恢复完全备份的stady库

mysqlbinlog --no-defaults --stop-datetime='2018-07-03 21:57:08' /usr/local/mysql/data/mysql-bin.000004 | mysql -u root -p //恢复误操作前的内容

mysqlbinlog --no-defaults --start-datetime='2018-07-03 21:57:14' /usr/local/mysql/data/mysql-bin.000004 | mysql -u root -p //恢复误操作之后的内容

根据位置标记号恢复数据

mysql> drop table info; //删除被误操作的表

mysql -u root -p stady < /backup/stady.sql //恢复完全备份的stady库

mysqlbinlog --no-defaults --stop-position='1408' /usr/local/mysql/data/mysql-bin.000005 | mysql -u root -p

mysqlbinlog --no-defaults --start-position='1674' /usr/local/mysql/data/mysql-bin.000005 | mysql -u root -p

数据完成恢复