数据备份策略：

–完全备份：备份所有数据

–增量备份：备份上次备份后，所有新产生的数据

–差异备份：备份完全备份后，所有新产生的数据

数据备份方式：

–物理备份

–逻辑备份

当访问量小的时候实行备份，完全+增量 完全+差异

备份的文件要有标识性，加上时间

1 物理备份：

（1） 将mysql50上的数据库备份至mysql51数据，保持数据库一致

模拟删除51虚拟机上的数据库文件

[root@mysql51 ~]# systemctl stop mysqld;

[root@mysql51 ~]# rm -rf /var/lib/mysql

备份50虚拟机上的数据库文件，并远程传送给51主机

[root@mysql50 ~]# cp -r /var/lib/mysql/ /root/mysqlall.bak 

[root@mysql50 ~]# scp -r /root/mysqlall.bak/  root@192.168.4.51:/root

在51主机上部署数据库文件，修改权限，再重启服务完成数据恢复

[root@mysql51 ~]# cp -r mysqlall.bak/ /var/lib/mysql

[root@mysql51 ~]# chown -R mysql.mysql /var/lib/mysql

[root@mysql51 ~]# systemctl restart mysqld;

1

2

3

4

5

6

7

8

9

10

物理备份局限性：

mysql版本要一致，操作系统要一致，存储引擎为innodb的数据库备份单张数据库表需要连带备份数据库日志

2 逻辑备份

备份：（由系统管理员来操作）

备份操作 ： mysqldump -uroot -p123456 库名 > 路径/文件 (完全备份）

恢复操作 ： mysql -uroot -p123456 库名 < 路径/文件 （完全恢复）

备份时库名表示方式：

–all-databases 或 -A ---->所有库

数据库名 ---->单个库

数据库名 表名 ---->单张表

-B 数据库1 数据库2 ---->多个库

注意事项： 无论是备份还是恢复，都要验证用户权限

当单个库被删除后，如果要用备份数据恢复这个库，需要先创建这个库，因为备份的时候只会备份这个库下的所有表的记录

（1）备份恢复一个库

[root@mysql50 ~]# mysqldump -uroot -p123456 db4 > /mydata/db4.sql    //备份

mysql> drop database db4;

mysql> create database db4;    //创建一个数据库名

[root@mysql50 ~]# mysql -uroot -p123456 db4 < /mydata/db4_ps1.sql    //恢复

1

2

3

4

（2）备份恢复一个库下的表

[root@mysql50 ~]# mysqldump -uroot -p123456 db4 passwd1 > /mydata/db4_ps1.sql

mysql> drop table passwd1;

[root@mysql50 ~]# mysql -uroot -p123456 db4 < /mydata/db4_ps1.sql

1

2

3

（3）备份恢复几个库

[root@mysql50 ~]# mysqldump -uroot -p123456 -B db4 db3 > /mydata/db3_4.sql

mysql> drop database db4;

mysql> drop database db3;

[root@mysql50 ~]# mysql -uroot -p123456 < /mydata/db3_4.sql   

1

2

3

4

完全备份的两个缺点：

–完全备份后，对于新增加的数据无法进行恢复

–完全备份或恢复时，会对数据库表加上写锁，影响用户的访问

3 binlog日志

启用binlog日志实现数据实时增量备份和恢复

binlog日志又叫二进制日志

记录所有除查询以外的所有操作

配置mysql主从同步的必备条件

（1）修改主配置文件开启binlog日志

[root@mysql50 ~]# vim /etc/my.cnf

[mysqld]

log_bin=/logdir/log （自定义日志文件路径，如果不修改指定，默认路径为mysql根目录下以当前时间为名字的文件）

server_id=50   （注意指定id不能重复，值随意 0-255之间）

binlog_format=mixed  (开启模式mixed 记录每条修改的数据以及每条除select以外的写sql命令）

max_binlog_size=200m（当日志文件达到200M后新建一个日志文件，默认为1G）

1

2

3

4

5

6

7

（2 ） 日志文件的存储格式：

三种日志记录格式：

statement :记录每条sql命令

row :记录哪条记录被修改

mixed：上两种格式的总和

查看日志文件的默认存储格式

mysql> show variables like "binlog_format";  

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

| Variable_name | Value |

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

| binlog_format | ROW   |

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

1 row in set (0.01 sec)

1

2

3

4

5

6

7

（3）查看日志文件默认最大存储量

mysql> show variables like "max_binlog_size";

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

| Variable_name   | Value      |

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

| max_binlog_size | 1073741824 |

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

1

2

3

4

5

6

（4）查看主日志文件状态（当前偏移量）

mysql> show master status;

mysql> show master status;

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

| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| log.000008 |    27328 |              |                  |                   |

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

1 row in set (0.00 sec)

1

2

3

4

5

6

7

8

（5）查看二进制日志文件

[root@mysql50 ~]# mysqlbinlog /logdir/log.000001

1

（6）删除指定日志之前的日志文件

mysql> purge master logs to "log.000007";

1

（7）删除所有日志文件，重置，生成log.000001

mysql> reset master;

1

（8）手动生成日志文件的种方式

1 重启Mysqld服务

2 执行sql操作： mysql > flush logs

3 mysqldump   --flush-logs   在实行完全备份时生成新的日志文件

4 mysql -uroot -p123456 -e 'flush logs'

1

2

3

4

（9）分析binlog日志（日志以偏移量和时间来记录对数据库的操作）

[root@mysql50 ~]# mysqlbinlog /logdir/log.000008

…………

# at 26989                    //起始偏移量

#181121 19:15:11 server id 50  end_log_pos 27120 CRC32 0xfafa5a5c Query thread_id=15 exec_time=0 error_code=0

SET TIMESTAMP=1542798911/*!*/;

INSERT INTO `yg` VALUES (3,'kenji'),(4,'natasha'),(888,'kenji')      //记录的SQL语句

/*!*/;

# at 27120                   // 执行sql语句提交后的偏移量

#181121 19:15:11 server id 50  end_log_pos 27151 CRC32 0x6593fcc8 Xid = 610

COMMIT/*!*/;

…………

1

2

3

4

5

6

7

8

9

10

11

（10）使用mysqlbinlog 工具

格式 ： mysqlbinlog [选项] binlog日志文件名

常用选项 ：

--start-datetime="yyyy-mm-dd hh:mm:ss"

--stop-datetime="yyyy-mm-dd hh:mm:ss"

--start-position=数字

--stop-position=数字

1

2

3

4

（11）案例：

启用binlog日志

创建db1库tb1表，插入三条记录

删除t1表中刚插入的三条记录

使用mysqlbinlog恢复删除的三条记录

1 修改配置文件，开启binlog，重启mysqld服务

[root@mysql50 ~]# vim /etc/my.cnf

[mysqld]

log_bin=/logdir/log

server_id=50

max_binlog_size=200m

binlog_format=mixed

[root@mysql50 ~]# systemctl restart mysqld

2 创建数据库表tb1，插入3条数据

mysql> create database db1;

mysql> use db1;

mysql> create table tb1(

    -> id int,

    -> name varchar(12),

    -> age int);

mysql> insert into tb1 values (1,"tom",22),(2,"kenji",24),(3,"harry",26);

mysql> select * from tb1;

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

| id   | name  | age  |

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

|    1 | tom   |   22 |

|    2 | kenji |   24 |

|    3 | harry |   26 |

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

3 rows in set (0.00 sec)

3 查看日志记录

[root@mysql50 ~]# mysqlbinlog /logdir/log.000009 | grep insert

insert into tb1 values (1,"tom",22),(2,"kenji",24),(3,"harry",26)

4 查看详细日志记录

[root@mysql50 ~]# mysqlbinlog /logdir/log.000009 

# at 795

#181121 21:03:49 server id 50  end_log_pos 932 CRC32 0xc9dd7cec Query thread_id=3 exec_time=0 error_code=0

SET TIMESTAMP=1542805429/*!*/;

insert into tb1 values (1,"tom",22),(2,"kenji",24),(3,"harry",26)

/*!*/;

# at 932

#181121 21:03:49 server id 50  end_log_pos 963 CRC32 0x05b7ce5e Xid = 16

COMMIT/*!*/;

5 删除数据库表的三条记录

mysql> delete from tb1 where id=1;

mysql> delete from tb1 where id=2;

mysql> delete from tb1 where id=3;

ysql> select * from tb1;

Empty set (0.00 sec)

6 利用binlog日志恢复数据库表

root@mysql50 ~]# mysqlbinlog --start-position=795 --stop-position=932 /logdir/log.000009  | mysql -uroot -p123456

7 再次查看数据库表内的记录 （数据已恢复）

mysql> select * from tb1;

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

| id   | name  | age  |

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

|    1 | tom   |   22 |

|    2 | kenji |   24 |

|    3 | harry |   26 |

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

3 rows in set (0.00 sec)

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

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

4 innobackupex工具实现增量热备份

（1）常用的mysql备份工具

物理备份缺点

**跨平台性差

**备份时间长，冗余备份，浪费存储空间

mysqldump备份缺点

**效率低，备份和还原数度慢

** 备份过程中，数据插入和更新操作会被挂起

（2）XtraBackupex工具

一款强大的在线热备份工具

**备份过程不锁库表，适合生产环境

**由专业组织percona提供

主要含有两个组件

** xtrabackup：C程序，支持innodb和xtradb存储引擎

** innobackupex: 以perl脚本封装xtrabackup,还支持myisam存储引擎

（3）安装XtraBackup软件包

[root@mysql50 ~]# yum -y install perl-DBD-MySQL perl-Digest-MD5   //安装依赖包

[root@mysql50 ~]#rpm –ivh libev-4.15-1.el6.rf.x86_64.rpm         //安装依赖包

[root@mysql50 ~]# rpm -qpi percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm  

1

2

3

（4）innobackupex完整备份、增量备份操作

–host 主机名

–port 3306

–user 用户名

–password 密码

–databases=“库名”

–databases=“库1 库2”

–databases=“库.表”

–no-timestamp 不用日期命名备份文件存储的子目录，使用备份的数据库名做备份目录名

–no-timestmap 不使用日期命名备份目录名

5 做一个完整备份

默认情况下，备份文件存储的子目录会用日期命名，

innobackupex作为客户端工具，以mysql协议连入mysqld，将数据备份到/backup文件夹：

（1）完整备份数据库

[root@mysql50 ~]#  innobackupex --user=root --password=123456 /b --no-timestamp

确认备份文件

[root@mysql50 ~]# ls /b

backup-my.cnf  db44            mysql               xtrabackup_binlog_info

db1            db5             performance_schema  xtrabackup_checkpoints

db2            ib_buffer_pool  personinfo          xtrabackup_info

db3            ibdata1         sys                 xtrabackup_logfile

查看本次完整本次备份信息

[root@mysql50 ~]# cat /b/xtrabackup_checkpoints 

backup_type = full-backuped

from_lsn = 0

to_lsn = 4966947             //本次完全备份的结束日志序列号，也是下次增量备份开始的日志序列号

last_lsn = 4966956

compact = 0

recover_binlog_info = 0

准备恢复数据

[root@mysql50 ~]#  innobackupex  --apply-log /b

模拟数据库丢失

[root@mysql50 ~]# systemctl stop mysqld

[root@mysql50 ~]# rm -rf /var/lib/mysql

[root@mysql50 ~]# mkdir /var/lib/mysql

恢复数据

[root@mysql50 ~]#  innobackupex  --copy-back /b

修改权限,启动服务，查看结果

[root@mysql50 ~]# chown -R mysql.mysql /var/lib/mysql

[root@mysql50 ~]# systemctl restart mysqld

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

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

（2）在数据库新增加数据

mysql> create database db;

mysql> create table nsd(id int);

mysql> insert into nsd values(1);

mysql> insert into nsd values(2);

mysql> insert into nsd values(3);

mysql> select * from nsd;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

+------+

1

2

3

4

5

6

7

8

9

10

11

12

13

（3）第一次增量备份：

[root@mysql50 ~]# innobackupex --user=root --password=123456 --incremental /node --incremental-basedir=/b --no-timestamp

1

（4）第二次增加数据

mysql> insert into nsd values(4);

mysql> insert into nsd values(4);

mysql> insert into nsd values(4);

mysql> insert into nsd values(4);

mysql> select * from nsd;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|    4 |

|    4 |

|    4 |

+------+

7 rows in set (0.00 sec)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

（5）第二次增量备份

[root@mysql50 ~]# innobackupex --user=root --password=123456 --incremental /node1 --incremental-basedir=/node --no-timestamp

1

2

（6） 模拟数据库丢失

[root@mysql50 ~]# systemctl stop mysqld

[root@mysql50 ~]# rm -rf /var/lib/mysql

1

2

（7） 准备恢复数据

查看日志序列号

[root@mysql50 ~]# cat /b/xtrabackup_checkpoints 

backup_type = full-backuped

from_lsn = 0

to_lsn = 4966947

last_lsn = 4966956

compact = 0

recover_binlog_info = 0

[root@mysql50 ~]# cat /node/xtrabackup_checkpoints 

backup_type = incremental

from_lsn = 4966947

to_lsn = 4973748

last_lsn = 4973757

compact = 0

recover_binlog_info = 0

[root@mysql50 ~]# cat /node1/xtrabackup_checkpoints 

backup_type = incremental

from_lsn = 4973748

to_lsn = 4978822

last_lsn = 4978831

compact = 0

recover_binlog_info = 0

[root@mysql50 ~]# innobackupex --apply-log --redo-only /b

[root@mysql50 ~]# innobackupex --apply-log --redo-only /b --incremental-dir=/node

[root@mysql50 ~]# innobackupex --apply-log --redo-only /b --incremental-dir=/node1

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

（8）拷贝文件

[root@mysql50 ~]# innobackupex --copy-back /b

1

（9）修改权限，启动mysql服务，查看结果

 [root@mysql50 ~]# chown -R mysql.mysql /var/lib/mysql

 [root@mysql50 ~]# systemctl restart mysqld

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

1

2

3

7 在完全备份中恢复单个表：

复制整个db4数据库

[root@mysql50 ~]# innobackupex --user=root --password=123456 --databases="bd1" /a --no-timestamp

[root@mysql50 ~]# ls /a

backup-my.cnf  ib_buffer_pool  xtrabackup_binlog_info  xtrabackup_info

db1            ibdata1         xtrabackup_checkpoints  xtrabackup_logfile

删除db4库中的pass表以及表里的数据记录

mysql> drop table db1.pass;

导出表信息

[root@mysql50 ~]# innobackupex --apply-log --export /a

创建pass表（此时表中没有数据记录，注意创建时字段要和之前表的字段一致）

mysql> create table pass( id int);

Query OK, 0 rows affected (0.25 sec)

[root@mysql50 ~]# ls /var/lib/mysql/db1/

db.opt  pass.frm  pass.ibd  stu.frm  stu.ibd

删除pass表空间 .ibd文件

mysql> alter table pass discard tablespace;

Query OK, 0 rows affected (0.13 sec)

[root@mysql50 ~]# ls /var/lib/mysql/db1/

db.opt  pass.frm  stu.frm  stu.ibd                           //少了文件pass.ibd

拷贝表信息文件

[root@mysql50 ~]# cp /a/db1/pass.{exp,cfg,ibd} /var/lib/mysql/db1

修改文件权限

[root@mysql50 ~]# chown -R mysql.mysql /var/lib/mysql/db1/pass.*

导入pass表空间 （数据恢复完成）

mysql> alter table pass import tablespace;

mysql> select * from pass;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

+------+

3 rows in set (0.00 sec)

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

29

30

31

32

33

34

35

36

37

38

39

40

41

---------------------