linux运维笔记：Mysql备份与恢复

1-备份简介

备份是数据安全的最后一道防线，对于任何数据丢失的场景，备份虽然不一定能恢复百分之百的数据(取决于备份周期)，但至少能将损失降到最低。衡量备份恢复有两个重要的指标：恢复点目标(RPO)和恢复时间目标(RTO)，前者重点关注能恢复到什么程度，而后者则重点关注恢复需要多长时间。

2-常用的备份方式

1-物理备份（数据文件的二进制副本）

2-全量备份

全量备份就是把数据库中所有的数据进行备份。

全量数据就是数据库中所有的数据（或某一个库的全部数据）。

mysqldump会取得一个时刻的一致性数据。

3-增量备份（刷新二进制日志）

增量数据就是指上一次全量备份数据之后到下一次全备之前数据库所更新的数据，对于mysqldump，binlog就是增量数据。

3-备份工具介绍

1、mysqldump: mysql原生自带很好用的逻辑备份工具

2、mysqlbinlog: 实现binlog备份的原生态命令

3、xtrabackup: precona公司开发的性能很高的物理备份工具

4-mysqldump备份基本流程

1.调用FTWRL(flush tables with read lock)，全局禁止读写

2.开启快照读，获取此时的快照(仅对innodb表起作用)

3.备份非innodb表数据(*.frm,*.myi,*.myd等)

4.非innodb表备份完毕后，释放FTWRL锁

5.逐一备份innodb表数据

6.备份完成。

5-mysqldump常用的备份参数

-A                #备份所有库

-B                #增加建库（create）及用库（use）的语句，可以接多个库名，同时备份多个库

-B   库1 库2

-d                 #仅备份表结构

-t                  #仅备份数据

--compact           #减少无用数据输出(调试)

-R                 #备份存储过程和函数数据

--triggers           #备份触发器数据

-F                 #刷新binlog日志

--master-data=1|2    #告诉备份时刻的binlog位置

-x, --lock-all-tables    #锁所有备份表

-l,  --lock-tables     #锁单表

6-mysqldump参数使用详解

①全库备份

mysqldump -uroot -p123456 -A >/backup/full.sql

②备份多个库

mysqldump -uroot -p123456 -B oldboy oldgirl >/backup/oldboy_oldgirl.sql

③分库备份

for name in `mysql -uroot -p123456 -e "show databases;"|sed 1d`

do

   mysqldump -uroot -p123456 -B $name >/backup/fen_$(date +%F).sql

done

④单表备份

mysqldump -uroot -p123456 oldboy test>/backup/oldboy_test.sql

⑤多表备份

mysqldump -uroot -p123456 oldboy 表1 表2 表3 … > /backup/oldboy_test.sql

⑥分库分表备份

#!/bin/sh

Myuser=root

Mypass=123456

Mycmd="mysql -u$Myuser -p$Mypass"

Mydump="mysqldump -u$Myuser -p$Mypass -x -F -R"

Dblist=`$Mycmd -e "show databases;"|sed '1,2d'|egrep -v "_schema|mysql"`

for database in $Dblist

do

  Tablist=`$Mycmd -e "show tables from $database;"|sed 1d`

  for table in $Tablist

  do

  mkdir -p /tmp/${database}

  $Mydump $database $table|gzip >/tmp/${database}/${table}_$(date +%F).sql.gz

  done

done

⑦压缩备份

mysqldump -uroot -p123456  -B --master-data=2 oldboy|gzip >/backup/oldboy.sql.gz

⑧innodb引擎备份命令

mysqldump -uroot -p123456 -A -B -R --triggers --master-data=2 --single-transaction|gzip >/backup/all.sql.gz

⑨适合多引擎混合备份

mysqldump -uroot -p123456 -A -B -R --triggers --master-data=2|gzip >/backup/alL_$(date +%F).sql.gz

7-mysqldump备份恢复实战案例。

1-前提条件

1）具备全量备份（mysqldump）。

2）除全量备份以外，还有全量备份之后产生的所有binlog增量日志。

2-环境准备

create database oldboy;

use oldboy;

create table `test` (

  `id` int(4) not null auto_increment,

  `name` char(20) not null,

  primary key (`id`)

) engine=innodb default charset=utf8;

insert into `test` values (1,'ning'),(2,'znix'),(3,'inca'),(4,'zuma'),(5,'kaka');

3-查看创建好的数据

mysql> select * from test;

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

| id | name |

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

|  1 | ning |

|  2 | znix |

|  3 | inca |

|  4 | zuma |

|  5 | kaka |

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

5 rows in set (0.00 sec)

4-模拟环境

[root@db01 ~]# mkdir /data/backup -p

[root@db01 ~]# date -s "2018/05/01"

Tue May  1 00:00:00 CST 2018

5-全备

[root@db01 ~]# mysqldump -uroot -p222222 -B --master-data=2 --single-transaction oldboy|gzip >/data/backup/all_$(date +%F_%T).sql.gz

[root@db01 ~]# ls /data/backup/

all_2018-05-01_00:03:12.sql.gz

6-开启二进制日志

修改配置文件开启二进制日志

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

[mysqld]

.............

log-bin=mysql-bin

binlog_format=mixed

查看log_bin是否开启

mysql> show variables like '%log_bin%';

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

| Variable_name                   | Value |

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

| log_bin                         | ON    |

| log_bin_trust_function_creators | OFF   |

| sql_log_bin                     | ON    |

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

3 rows in set (0.00 sec)

查看binlog设置

mysql> show variables like '%binlog%';

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

| Variable_name                           | Value                |

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

| binlog_cache_size                       | 32768                |

| binlog_direct_non_transactional_updates | OFF                  |

| binlog_format                           | MIXED                |

| binlog_stmt_cache_size                  | 32768                |

| innodb_locks_unsafe_for_binlog          | OFF                  |

| max_binlog_cache_size                   | 18446744073709547520 |

| max_binlog_size                         | 1073741824           |

| max_binlog_stmt_cache_size              | 18446744073709547520 |

| sync_binlog                             | 0                    |

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

9 rows in set (0.00 sec)

mysql> show binary logs;

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

| Log_name         | File_size |

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

| mysql-bin.000001 |    705360 |

| mysql-bin.000002 |       150 |

| mysql-bin.000003 |       107 |

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

3 rows in set (0.00 sec)

刷新binlog日志

mysql> flush logs;

Query OK, 0 rows affected (0.05 sec)

7-模拟增量。

[root@db01 ~]# mysql -uroot -p222222 -e "use oldboy;insert into test values(6,'haha');"

[root@db01 ~]# mysql -uroot -p222222 -e "use oldboy;insert into test values(7,'hehe');"

[root@db01 ~]# mysql -uroot -p222222 -e "select * from oldboy.test;"

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

| id | name |

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

|  1 | ning |

|  2 | znix |

|  3 | inca |

|  4 | zuma |

|  5 | kaka |

|  6 | haha |

|  7 | hehe |

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

8-模拟误删数据

date -s "2018/05/01 11:40"

mysql -uroot -p222222 -e "drop database oldboy;show databases;"

出现问题10分钟后，发现问题，删除了数据库了。

[root@db01 ~]# ls /application/mysql/data/

db01.err     mysql             oldboy

db01.pid     mysql-bin.000001  performance_schema

ibdata1      mysql-bin.000002  test

ib_logfile0  mysql-bin.000003  wordpress

ib_logfile1  mysql-bin.index

9-开启iptables防火墙屏蔽所有应用程序的写入。

[root@db01 ~]# /etc/init.d/iptables start

[root@db01 ~]# iptables -I INPUT -p tcp --dport 3306 ! -s 172.16.1.51 -j DROP

#非172.16.1.51禁止访问数据库3306端口。

10-复制二进制日志文件。

[root@db01 ~]# cp -a /application/mysql/data/mysql-bin.* /data/backup/

[root@db01 ~]# ls /data/backup/

all_2018-05-01_00:03:12.sql.gz  mysql-bin.000003

mysql-bin.000001                mysql-bin.index

mysql-bin.000002

11-截取日志。

[root@db01 ~]# gzip -d /data/backup/all_2018-05-01_00:03:12.sql.gz

[root@db01 ~]# ls /data/backup/

all_2018-05-01_00:03:12.sql  mysql-bin.000003

mysql-bin.000001             mysql-bin.index

mysql-bin.000002

#提取出binlog位置点

[root@db01 ~]# sed -n '22p' /data/backup/all_2018-05-01_00:03:12.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=704951;

#截取binlog位置点

[root@db01 ~]# mysqlbinlog -d oldboy --start-position=704951 /data/backup/mysql-bin.000001 -r /data/backup/bin.sql

[root@db01 ~]# ls /data/backup/

all_2018-05-01_00:03:12.sql  mysql-bin.000002

bin.sql                      mysql-bin.000003

mysql-bin.000001             mysql-bin.index

12-全备恢复。

[root@db01 ~]#

mysql -uroot -p222222 </data/backup/all_2018-05-01_00:03:12.sql

mysql> use oldboy;

Database changed

mysql> select * from test;

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

| id | name |

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

|  1 | ning |

|  2 | znix |

|  3 | inca |

|  4 | zuma |

|  5 | kaka |

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

5 rows in set (0.00 sec)

13-增量恢复。

[root@db01 ~]# mysql -uroot -p222222 </data/backup/bin.sql

mysql> select * from test;

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

| id | name |

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

|  1 | ning |

|  2 | znix |

|  3 | inca |

|  4 | zuma |

|  5 | kaka |

|  6 | haha |

|  7 | hehe |

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

7 rows in set (0.00 sec)

14-恢复完毕。

调整iptables允许用户访问：

[root@db01 ~]# /etc/init.d/iptables stop

多个binlog问题：

mysqlbinlog -d oldboy --start-position=704951 /data/backup/mysql-bin.000001 /data/backup/mysql-bin.000002 /data/backup/mysql-bin.000003 ...... -r /data/backup/bin.sql