mysqldump全量备份+mysqlbinlog二进制日志增量备份
1、mysqldump数据库增量备份条件:
1>要敞开mysql log-bin日志功用,若没有敞开则,修正配置文件/etc/my.cnf,增加如下内容:
[mysqld]
datadir=/var/lib/mysql/data
log-bin=mysql-bin
server-id=1
重启mysql
2>检查数据库是否敞开了二进制日志打印功用:
1
2
3
4
5
6
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>预备数据库和表并向表中刺进数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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 * from student; //检查student表中的一切字段的值
+----+--------+-----+-----+--------------+
| id | name | sex | age | dept |
+----+--------+-----+-----+--------------+
| 1 | 张三 | 男 | 24 | 计算机 |
| 2 | 李四 | 女 | 27 | 英语 |
| 3 | 王五 | 男 | 22 | 电子商务 |
| 4 | 赵六 | 女 | 20 | 物流办理 |
+----+--------+-----+-----+--------------+
4 rows in set (0.00 sec)
2>模仿清晨00:00全备
1
2
[root@centos6 ~]# mkdir /backup //创立备份文件夹
[root@centos6 ~]# date -s "20200225"
[root@centos6 ~]# mysqldump -uroot -p111111 --default-character-set=utf8 --single-transaction -F -B school -e | gzip > /backup/mysql_backup_date +%F
.sql.gz
3>全备之后再向表中写入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
[root@centos6 ~]# mysql -uroot -p111111
......
mysql> use school
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> insert into student values
-> (5,'tom','男',29,'化学'),(6,'jack','女',19,'法语'),
-> (7,'mimi','女',21,'化装'),(8,'kaka','女',20,'酒店办理');
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from student; | ||||
---|---|---|---|---|
id | name | sex | age | dept |
1 | 张三 | 男 | 24 | 计算机 |
2 | 李四 | 女 | 27 | 英语 |
3 | 王五 | 男 | 22 | 电子商务 |
4 | 赵六 | 女 | 20 | 物流办理 |
5 | tom | 男 | 29 | 化学 |
6 | jack | 女 | 19 | 法语 |
7 | mimi | 女 | 21 | 化装 |
8 | kaka | 女 | 20 | 酒店办理 |
8 rows in set (0.00 sec)
4>模仿用户损坏数据
1
2
mysql> drop database school;
Query OK, 1 row affected (0.01 sec)
5>检查全备后的一切binlog
1
2
3
4
5
6
7
8
9
10
11
12
13
14
[root@centos6 ~]# ll -nat /var/lib/mysql/data/
总用量 21276
-rw-rw----. 1 27 27 5242880 2月 25 00:00 ib_logfile0
-rw-rw----. 1 27 27 10485760 2月 25 00:00 ibdata1
-rw-rw----. 1 27 27 416 2月 25 00:00 mysql-bin.000004
drwxr-xr-x. 4 27 27 4096 2月 25 00:00 .
-rw-rw----. 1 27 27 76 2月 25 00:00 mysql-bin.index
-rw-rw----. 1 27 27 1581 2月 25 00:00 mysql-bin.000003
drwx------. 2 27 27 4096 1月 12 18:34 school
drwxr-xr-x. 3 27 27 4096 1月 12 18:31 ..
-rw-rw----. 1 27 27 5242880 1月 12 18:31 ib_logfile1
-rw-rw----. 1 27 27 765307 1月 12 18:31 mysql-bin.000002
-rw-rw----. 1 27 27 19734 1月 12 18:31 mysql-bin.000001
drwx------. 2 27 27 4096 1月 12 18:31 mysql
6>当即改写;再次检查binlog:依据时刻点及前一个binlog能够知道发现问题时刻前binlog日志
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@centos6 ~]# mysqladmin -uroot -p111111 flush-logs
[root@centos6 ~]# ll -nat /var/lib/mysql/data/
总用量 21276
drwxr-xr-x. 3 27 27 4096 2月 25 00:05 .
-rw-rw----. 1 27 27 95 2月 25 00:05 mysql-bin.index
-rw-rw----. 1 27 27 106 2月 25 00:05 mysql-bin.000005 //确定该binlog
-rw-rw----. 1 27 27 544 2月 25 00:05 mysql-bin.000004
-rw-rw----. 1 27 27 5242880 2月 25 00:03 ib_logfile0
-rw-rw----. 1 27 27 10485760 2月 25 00:03 ibdata1
-rw-rw----. 1 27 27 1581 2月 25 00:00 mysql-bin.000003
drwxr-xr-x. 3 27 27 4096 1月 12 18:31 ..
-rw-rw----. 1 27 27 5242880 1月 12 18:31 ib_logfile1
-rw-rw----. 1 27 27 765307 1月 12 18:31 mysql-bin.000002
-rw-rw----. 1 27 27 19734 1月 12 18:31 mysql-bin.000001
drwx------. 2 27 27 4096 1月 12 18:31 mysql
7>备份出binlog至/backup目录
1
[root@centos6 ~]# cp /var/lib/mysql/data/mysql-bin.* /backup/
8>康复school数据库:
(1)康复全量备份
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[root@centos6 data]# cd /backup/
[root@centos6 backup]# zcat mysql_backup_2020-02-25.sql.gz >mysql_backup_2020-02-25.sql
[root@centos6 backup]# mysql -uroot -p111111 -e "create database school;"
[root@centos6 backup]# mysql -uroot -p111111 school < /backup/mysql_backup_2020-02-25.sql
[root@centos6 backup]# mysql -uroot -p111111
......
mysql> use school
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> select * from student; | ||||
---|---|---|---|---|
id | name | sex | age | dept |
1 | 张三 | 男 | 24 | 计算机 |
2 | 李四 | 女 | 27 | 英语 |
3 | 王五 | 男 | 22 | 电子商务 |
4 | 赵六 | 女 | 20 | 物流办理 |
4 rows in set (0.00 sec) //增量康复成功
mysql> quit
(2)康复增量备份
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
[root@centos6 backup]# cd /var/lib/mysql/data/
[root@centos6 data]# mysqlbinlog -d school mysql-bin.000004 > school-bin.sql
[root@centos6 data]# mysqlbinlog -d school mysql-bin.000005 >> school-bin.sql
[root@centos6 data]# vim school-bin.sql //翻开school-bin.sql删去drop sql句子
[root@centos6 data]# mysql -uroot -p111111 school < school-bin.sql
ERROR 1007 (HY000) at line 65: Can't create database 'school'; database exists //由于school现已康复至全量备份,所以此处提示school数据库现已存在,不过没关系,对存在的数据掩盖
[root@centos6 data]# mysql -uroot -p111111
......
mysql> use school
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> select * from student; | ||||
---|---|---|---|---|
id | name | sex | age | dept |
1 | 张三 | 男 | 24 | 计算机 |
2 | 李四 | 女 | 27 | 英语 |
3 | 王五 | 男 | 22 | 电子商务 |
4 | 赵六 | 女 | 20 | 物流办理 |
5 | tom | 男 | 29 | 化学 |
6 | jack | 女 | 19 | 法语 |
7 | mimi | 女 | 21 | 化装 |
8 | kaka | 女 | 20 | 酒店办理 |
8 rows in set (0.00 sec) //增量备份康复
mysql> quit
9>总结
mysqlbinlog增量康复办法
根据时刻点康复
(1)指定开端时刻到完毕时刻 myslbinlog mysqlbin.000005 --start-datetime='2020-02-25 01:10:46' --stop datetime='2020-02-25 03:10:46' -r time.sql
(2)指定开端时刻到文件完毕 myslbinlog mysqlbin.000005 --start-datetime='2020-02-25 01:10:46' -d esen -r time.sql
(3)从文件最初到指定完毕时刻 myslbinlog mysqlbin.000005 --stop-datetime='2020-02-25 03:10:46' -d esen -r time.sql
根据方位点的增量康复
(1)指定开端方位到完毕方位 myslbinlog mysqlbin.000005 --start-position=510 --stop-position=1312 -r pos.sql
(2)指定开端方位到文件完毕 myslbinlog mysqlbin.000005 --start-position=510 -r pos.sql
(3)从文件开端方位到指定完毕方位 myslbinlog mysqlbin.000005 --stop-position=1312 -r pos.sql
或许
在增量备份的时分,能够将二进制文件转化成.sql句子,然后在删去“drop”句子,修正.sql,再导入即可(上述康复选用该办法)
3、完成自动化备份(数据库小)
1>备份方案:
(1)什么时刻:2:00
(2)对那些数据备份
(3)备份的文件放的方位
2>备份脚本:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[root@centos6 ~]# vim /mysql_back.sql
!/bin/bash
back_dir=/backup
back_file=date +%F
_all.sql
user=root
pass=111111
if [ ! -d /backup ];then
mkdir -p /backup
fi
备份并切断日志
mysqldump -u${user} -p${pass} --lock-all-tables --routines --events --triggers -
-master-data=2 --flush-logs --all-databases > /$back_dir/$back_file
只保存最近一周的备份
cd $back_dir
find . -mtime +7 -exec rm -rf {} ;
3>测验:
1
2
3
4
[root@centos6 ~]# chmod +x /mysql_back.sql
[root@centos6 ~]# /mysql_back.sql
[root@centos6 ~]# ls /backup
2020-02-25_all.sql
4、仿制数据文件
仿制数据文件办法,能够运用cp或tar
(1)中止服务
1
[root@centos6 ~]# service mysqld stop
(2)备份数据文件
1
2
[root@centos6 ~]# mkdir /backup
[root@centos6 ~]# tar cvf /backup/all.date +%F
.tar.gz /var/lib/mysql/*
(3)将备份文件拷贝到方针服务器
1
scp /backup/all.date +%F
.tar.gz 192.168.129.142:/tmp/
(4)发动服务,如有需要则康复数据库数据
原文地址https://www.cnblogs.com/zyybky/p/12364583.html
发表评论