数据库的备份和还原

备份的基本概念

备份的类型

根据支持对数据操作的不同分为
热备份：读写不受影响
温备份：仅可以执行读操作
冷备份：离线备份，读写操作均中止。

根据备份的方式的不同分为物理备份和逻辑备份
物理备份：复制数据文件
逻辑备份：将数据导出至文本文件。

根据备份的数据集是否包括全部内容，分为完全备份和差量备份

完全备份：备份全部数据
增量备份：仅备份上次完全备份或增量备份以后变化的数据
差异备份：仅备份上次完全备份之后变化的数据。

备份内容：数据、配置文件、二进制日志、事务日志

存储引擎	MyISM	Innodb
热备份		xtrabackup、mysqldump

物理备份：速度快
逻辑备份：速度慢、丢失浮点数精度、方便使用文本处理工具对其处理、可移植能力强。

备份工具

mysql自带的备份工具

• mysqldump:逻辑备份工具，MyISM存储引擎可以实现温备份，Innodb存储引擎使用热备份。
• mysqlhostcopy：物理备份工具，温备份

文件系统备份工具：

• cp：冷备份

• lv：逻辑卷的快照功能，几乎热备。

  1. mysql> flush tables;
  2. mysql> lock tables;
  3. 创建快照、释放锁、而后复制数据。

商业备份工具：

• ibbackup、xtrabackup

mysqldump

格式：

mysqldump [OPTIONS] database [tables]

选项：

选项	含义
–master-data={0|1|2}	0:不记录二进制日志文件及路径；1：以change master to的方式记录位置，可用于恢复后直接启动从服务器；2：以change master to的方式记录位置，但默认被注释
–lock-tables	锁定指定的表
–lock-all-tables	锁定所有的表
–flush-logs	执行日志flush，生成新的二进制日志。
–single-transaction	如果数据库中的存储引擎是Innodb，可以使用该参数启动热备。
–all-databases	备份所有的库，备份的数据库不只一个恢复数据的时候不需要指定数据库。会自动创建。
–databases DB_name,DB_name	备份指定的数据库 备份的数据库不只一个，恢复的时候不需要指定数据库
–events	备份事件
–routines	备份存储过程
–triggers	备份触发器

–master-data 参数使用的结果

首先不使用该参数备份，第二条语句使用该参数备份

[root@miner-k ~]# mysqldump -uroot -p studb > studb-`date +%Y-%m-%d-%H-%M-%S`.sql
Enter password: 


[root@miner-k ~]# mysqldump -uroot -p --master-data=2 studb > studb-`date +%Y-%m-%d-%H-%M-%S`.sql
Enter password: 


[root@miner-k ~]# ls studb-2018-07-03-04-5
studb-2018-07-03-04-56-57.sql  studb-2018-07-03-04-57-43.sql 

执行之后会生成两个sql文件，打开第二条语句执行的结果，会看到有记录二进制日志和二进制日志的位置。

[root@miner-k ~]# vim studb-2018-07-03-04-57-43.sql
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000018', MASTER_LOG_POS=8768;

--
-- Table structure for table `courses`

数据备份和恢复实例

数据备份

1. 锁表

   mysql> flush tables with read lock;
   Query OK, 0 rows affected (0.00 sec)
   

2. 保存mysql中的students库

   [root@miner-k ~]# mysqldump -uroot -p students > stu.sql
   Enter password: 
   

3. 解锁

   mysql> unlock tables;
   Query OK, 0 rows affected (0.00 sec)

数据恢复

使用mysqldump导出的数据，如果没有创建数据库是无法直接导入的，导入数据时，需要导入指定的数据库。

[root@miner-k ~]# mysql < stu.sql 
ERROR 1046 (3D000) at line 22: No database selected

• 创建数据库

  mysql> create database studb;
  Query OK, 1 row affected (0.00 sec)

• 导入数据库，在此处没有指定-u root -p密码，本地设置了~/.my.cnf

  [root@miner-k ~]# mysql studb< stu.sql

备份所有数据库，并自动锁定所有表

[root@miner-k ~]# mysqldump -uroot -p --lock-all-tables --flush-logs --all-databases --master-data=2 > all.sql
Enter password: 

通过备份策略创建的数据恢复数据库数据

备份策略：每周完全备份+每日增量备份

保存全量备份

[root@miner-k ~]# mysqldump -uroot -p --lock-all-tables --flush-logs --all-databases --master-data=2 > alldatabases.sql
Enter password: 

修改数据库

mysql> select * from student;
+----+--------+-----+--------+-----+------+
| id | name   | cid | gender | age | cid2 |
+----+--------+-----+--------+-----+------+
|  2 | tom    |   2 | M      |  19 | NULL |
|  3 | jack   |   3 | M      |  20 |    4 |
|  4 | lucy   |   2 | F      |  25 |    4 |
|  5 | neccy  |   3 | F      |  30 | NULL |
|  6 | mary   |   4 | F      |  21 |    5 |
|  7 | kaka   |   5 | M      |  21 |    3 |
|  8 | suke   |   3 | M      |  20 |    5 |
|  9 | suke   |   4 | M      |  21 |    3 |
| 10 | beita  |   3 | M      |  24 |    1 |
| 11 | wukong |   5 | M      |  25 |    2 |
| 12 | wujing |   1 | M      |  28 | NULL |
| 13 | wuneng |   4 | F      |  30 | NULL |
| 14 | tita   |   3 | NULL   |  32 |    1 |
| 15 | tomm   |   1 | F      |  30 | NULL |
+----+--------+-----+--------+-----+------+
14 rows in set (0.00 sec)

mysql> delete from student where age >20;
Query OK, 11 rows affected (0.03 sec)

mysql> select * from student;
+----+------+-----+--------+-----+------+
| id | name | cid | gender | age | cid2 |
+----+------+-----+--------+-----+------+
|  2 | tom  |   2 | M      |  19 | NULL |
|  3 | jack |   3 | M      |  20 |    4 |
|  8 | suke |   3 | M      |  20 |    5 |
+----+------+-----+--------+-----+------+
3 rows in set (0.01 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000021 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

拷贝二进制日志作为当天的增量备份

[root@miner-k ~]# cp /mydata/data/mysql-bin.000020 /root/

将二进制日志转化为可读的sql语句

[root@miner-k ~]# mysqlbinlog mysql-bin.000020 > mon-increment.sql

[root@miner-k ~]# cp /mydata/data/mysql-bin.000021 /root/

好了现在，搞点破坏，删除所有的数据。

[root@miner-k ~]# cd /mydata/data/
[root@miner-k data]# rm -rf *

关闭数据库

[root@miner-k data]# netstat -anlp | grep :3306
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      2342/mysqld         
[root@miner-k data]# killall mysqld
[root@miner-k data]# netstat -anlp | grep :3306

初始化数据库

[root@miner-k mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
Installing MySQL system tables...

启动mysqld服务器

[root@miner-k mysql]# service mysqld start
Starting MySQL.Logging to '/mydata/data/miner-k.com.err'.
. SUCCESS! 

将数据库恢复

[root@miner-k ~]# mysql < alldatabases.sql 
[root@miner-k ~]# mysql < mon-increment.sql 
[root@miner-k ~]# mysqlbinlog mysql-bin.00002
mysql-bin.000020  mysql-bin.000021  
[root@miner-k ~]# mysqlbinlog mysql-bin.000021 > tmp.sql
[root@miner-k ~]# mysql < tmp.sql 

mysqlhostcopy

导入mysql提供的测试数据

MySQL官网的指导链接
employee 数据 github仓库地址

[root@miner-k ~]# git clone https://github.com/datacharmer/test_db.git /mydata/employee/

[root@miner-k ~]# cd /mydata/employee/
[root@miner-k employee]# mysql < employees.sql

select语句备份

特点：
创建的时候比mysqldump速度要快
只是用与单张表的数据恢复。
不会记录mysql的二进制日志。

mysql> select * from student;
+----+---------+-----+--------+-----+------+
| id | name    | cid | gender | age | cid2 |
+----+---------+-----+--------+-----+------+
|  3 | jack    |   3 | M      |  20 |    4 |
|  8 | suke    |   3 | M      |  20 |    5 |
| 16 | jim     |   1 | M      |  21 | NULL |
| 17 | tomm    |   2 | M      |  25 | NULL |
| 18 | jessise |   2 | M      |  32 | NULL |
+----+---------+-----+--------+-----+------+
5 rows in set (0.00 sec)

mysql> select * into outfile '/mydata/back/stu.txt' from student;
Query OK, 5 rows affected (0.00 sec)

注意：输入时指定的文件，属组、属主必须是mysqld的启动用户，此处设置的是mysql否则写入数据失败。

[root@miner-k ~]# cat /mydata/back/stu.txt 
3   jack    3   M   20  4
8   suke    3   M   20  5
16  jim 1   M   21  \N
17  tomm    2   M   25  \N
18  jessise 2   M   32  \N

数据恢复

mysql> create table stu1 like student;
Query OK, 0 rows affected (0.09 sec)

mysql> load data infile '/mydata/back/stu.txt' into table stu1;
Query OK, 5 rows affected (0.03 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from stu1;
+----+---------+-----+--------+-----+------+
| id | name    | cid | gender | age | cid2 |
+----+---------+-----+--------+-----+------+
|  3 | jack    |   3 | M      |  20 |    4 |
|  8 | suke    |   3 | M      |  20 |    5 |
| 16 | jim     |   1 | M      |  21 | NULL |
| 17 | tomm    |   2 | M      |  25 | NULL |
| 18 | jessise |   2 | M      |  32 | NULL |
+----+---------+-----+--------+-----+------+
5 rows in set (0.00 sec)

错误提示

mysql> select * into outfile '/mydata/stu.txt' from student;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

出现问题的原因：secure-file-priv的变量指向为空，需要修改，该变量是只读变量，需要修改配置文件。

解决办法：

• 修改配置文件/etc/my.cnf

  secure_file_priv=/mydata
  

• 重新启动mysqld服务

总结

mysqldump ：

• –databases DB1,DB2,….
• –alldatabases

使用这两个参数，创建出来的逻辑备份包含了数据库的创建不需要手动创建数据库再导入数据。

MyISM：温备份

--lock-all-tables
--lock-tables

InndDB:热备份

--single-transaction
--flush-logs
--events
--routines
--triggers
--master-data={0|1|2}

逻辑备份
浮点数据丢失精度
备份出来的数据更占用存储空间，压缩后可能大大节省空间
不适用对大数据做完全备份

对Innodb如果做温备份，
使用命令flush tables with read lock;对数据库中的数据进行加锁操作，如果遇到有大的事务要进行很长时间的等待，

如果使用flush tables with read lock;这个命令对应数据进行完毕了加锁操作，但是可能后台的还会对数据进行写操作，InnoDB存储引擎支持事务，可能事务提交，但是事务日志没有写入存储数据文件，mysql> show engine innodb status;查看innodb的事务的存储情况。