1、mysqldump数据库增量备份前提:
1>要开启mysql log-bin日志功能,若没有开启则,修改配置文件/etc/my.cnf,添加如下内容:
[mysqld]
datadir=/var/lib/mysql/data
log-bin=mysql-bin
server-id=1
重启mysql
2>查看数据库是否开启了二进制日志打印功能:
mysql> show variables like 'log_%';
+---------------------------------+---------------------+
| Variable_name | Value |
+---------------------------------+---------------------+
| log_bin | ON |
......
log_bin为ON 则表示该功能已经开启
3>存在一个完全备份,生产环境一般凌晨某个时刻进行全备
例如:mysqldump -uroot -p –default-character-set=gbk –single-transaction -F -B school |gzip > /backup/school_$(date +%F).sql.gz
InnoDB 表在备份时,通常启用选项 –single-transaction 来保证备份的一致性
2、mysqldump全量备份+mysqlbinlog二进制日志增量备份过程:
模拟00:00进行全量备份,误删数据库,恢复
1>准备数据库和表并向表中插入数据
mysql> create database school default character set utf8; //创建school数据库
Query OK, 1 row affected (0.00 sec)
mysql> use school //切换至school数据库
Database changed
mysql> create table student(
-> id int(10) not null comment '学号',
-> name varchar(16) not null comment '姓名',
-> sex varchar(16) not null comment '性别',
-> age tinyint(2) not null default '0' comment '学生年龄',
-> dept varchar(16) default 'null' comment '学生所在系别',
-> primary key (id))
-> ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
-> COLLATE=utf8_general_ci; //创建student表
Query OK, 0 rows affected (0.03 sec)
mysql> insert into student values //向表中插入数据
-> (1,'张三','男',24,'计算机'),(2,'李四','女',27,'英语'),
-> (3,'王五','男',22,'电子商务'),(4,'赵六','女',20,'物流管理');
Query OK, 4 rows affected, 4 warnings (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 4
mysql> select *