Mariadb的数据恢复严重依赖与bin-log日志,所以为了防止磁盘故障导致数据文件和bin-log文件一起丢失,所以最好将bin-log日志存放
mysqldump备份介绍
mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本,其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等,适用于备份数据量不大的数据库。
优点:备份简单,恢复容易。
备份缺点:schema和数据存储在一起,巨大的SQL语句、单个巨大的备份文件(备份的库和表都在一个文件中)。
mysqldump: 是一个mysql客户端命令,通过mysql协议连接至mysqld,实现数据库备份
命令的语法格式:
mysqldump [OPTIONS] database [tables]:备份单个库,或库指定的一个或多个表
mysqldump[OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]:备份一个或多个库
mysqldump[OPTIONS] --all-databases [OPTIONS]:备份所有库
常用选项:
-A,--all-databases #备份所有数据库
InnoDB:
--single-transaction:启动一个大的单一事务实现备份
-B,--databases db_name1 db_name2 ...:备份指定的数据库
-C,--compress:压缩传输;
-x, --lock-all-tables:锁定所有表
-l, --lock-tables:锁定备份的表
--flush-logs,-F:锁定表之后执行flush logs命令;
其它选项:
-E,--events:备份指定库的事件调度器;
-R,--routines:备份存储过程和存储函数;
--triggers:备份触发器
--master-data[=#]:
1:记录CHANGE MASTER TO语句;此语句未被注释;
2:记录为注释语句;
特别说明:Mariadb的数据恢复严重依赖与bin-log日志,所以为了防止磁盘故障导致数据文件和bin-log文件一起丢失,所以最好将bin-log日志存放到共享存储中。
设置方法:修改Mariadb配置文件,将日志文件存放位置指向本地挂载网络存储的路径,然后重启Mariadb服务即可。
[root@MariaDB ~]# vim /etc/my.cnf
log-bin="/backup/bin-log/mysql-bin.x"
验证:重启服务之后/backup/bin-log/目录下就有了Mysql的二进制日志文件和日志索引文件。
[root@MariaDB ~]# ll /backup/bin-log/mysql-bin.*
-rw-rw---- 1 mysql mysql 245 Jun 16 00:00/backup/bin-log/mysql-bin.000001
-rw-rw---- 1 mysql mysql 33 Jun 16 00:00/backup/bin-log/mysql-bin.index
单个库实现备份恢复
数据库中有hellodb表一个,需要做对hellodb表的备份,以实现数据库故障或者发生误删除操作时可以及时恢复数据库。
备份方案为:每周日完全备份一次数据库,周一到周六增量备份数据库
备份过程如下
第一天备份:完全备份hellodb数据库,并且在完全备份的时候锁定表和滚动二进制日志
[root@MariaDB~]# mysqldump -B hellodb -u root -p --lock-all-tables --flush-logs --master-data=2 > /backup/hellodb-`date+%F`.sql
Enterpassword:
[root@MariaDB~]# ll /backup/
total8
-rw-r--r--1 root root 7950 Jun 16 11:59 hellodb-2015-06-16.sql
由于在做完全备份的时候滚动了二进制日志,所以在做一些关于数据库更改的操作都会记录到新的二进制日志中,从查看中得知后续日志会记录到mysql-bin.000002中。
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 288 |
| mysql-bin.000002 | 245 |
+------------------+-----------+
2 rows in set (0.00 sec)
备份完成之后的一天时间里,创建了一张表,并且向表中插入了一些数据
MariaDB[(none)]> use hellodb;
MariaDB[hellodb]> create table tb1 (id int);
MariaDB[hellodb]> insert into tb1 values (1),(2),(3);
MariaDB[hellodb]> select * from tb1;
+------+
|id |
+------+
| 1 |
| 2 |
| 3 |
+------+
第一天增量备份:当天的所有对数据库进行更改的语句都会记录到二进制日志文件中,只需要滚动二进制日志,把二进制日志进行备份即可,二进制滚动之后,第二天的所有对数据库进行更改的语句,都会记录到新的二进制日志文件中。
MariaDB[hellodb]> flush logs;
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 288 |
| mysql-bin.000002 | 577 |
| mysql-bin.000003 | 245 |
+------------------+-----------+
3 rows in set (0.00 sec)
将二进制日志文件转换为sql文件
[root@MariaDB ~]# mysqlbinlog/backup/bin-log/mysql-bin.000002 > /backup/1.sql
第二天操作:继续向tb1中插入数据
MariaDB[hellodb]> insert into tb1 values (21),(22),(23);
MariaDB[hellodb]> select * from tb1;
+------+
|id |
+------+
| 1 |
| 2 |
| 3 |
| 21 |
| 22 |
| 23 |
+------+
故障模拟
hellodb数据库遭到误删除:
MariaDB[hellodb]> DROP database hellodb;
恢复前的准备
查看二进制日志是记录到了第三个日志
MariaDB[(none)]> show master logs;
+------------------+-----------+
|Log_name | File_size |
+------------------+-----------+
|mysql-bin.000001 | 288 |
|mysql-bin.000002 | 577 |
|mysql-bin.000003 | 533 | #当前日志的记录位置
+------------------+-----------+
3rows in set (0.00 sec)
MariaDB[(none)]> show binlog events in 'mysql-bin.000003'\G;
***************************5. row ***************************
Log_name: mysql-bin.000005
Pos: 446
Event_type: Query
Server_id: 1
End_log_pos:533
Info: DROP database hellodb #记录的删除语句
5rows in set (0.00 sec)
ERROR:No query specified
已抢48187个
抢已抢3473个
抢已抢1345个
抢已抢1450个
抢已抢2013个
抢已抢2640个
抢