MySQL Study之--MySQL 备份

MySQL Study之--MySQL 备份

一、备份的目的

做灾难恢复：对损坏的数据进行恢复和还原
需求改变：因需求改变而需要把数据还原到改变以前
测试：测试新功能是否可用

二、备份需要考虑的问题

可以容忍丢失多长时间的数据；
恢复数据要在多长时间内完； 
恢复的时候是否需要持续提供服务；
恢复的对象，是整个库，多个表，还是单个库，单个表。

三、备份的类型

1、根据是否需要数据库离线

冷备（cold backup）：需要关mysql服务，读写请求均不允许状态下进行；
温备（warm backup）： 服务在线，但仅支持读请求，不允许写请求；
热备（hot backup）：备份的同时，业务不受影响。

注：

1、这种类型的备份，取决于业务的需求，而不是备份工具
2、MyISAM支持热备（mysqlhotcopy），InnoDB支持热备（ibbackup、xtrabackup）

2、根据要备份的数据集合的范围
完全备份：full backup，备份全部字符集。
增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据，不能单独使用，要借助完全备份，备份的频率取决于数据的更新频率。
差异备份：differential backup 上次完全备份以来改变了的数据。
建议的恢复策略：
完全+增量+二进制日志
完全+差异+二进制日志

3、根据备份数据或文件

物理备份：直接备份数据文件

优点：

备份和恢复操作都比较简单，能够跨mysql的版本，
恢复速度快，属于文件系统级别的

建议：

不要假设备份一定可用，要测试
mysql>check tables；检测表是否可用
逻辑备份: 备份表中的数据和代码

优点：

恢复简单、
备份的结果为ASCII文件，可以编辑
与存储引擎无关
可以通过网络备份和恢复

缺点：

备份或恢复都需要mysql服务器进程参与
备份结果占据更多的空间，
浮点数可能会丢失精度
还原之后，缩影需要重建

四：备份的对象

1、 数据；
2、配置文件；
3、代码：存储过程、存储函数、触发器
4、OS相关的配置文件
5、复制相关的配置
6、二进制日志

逻辑备份工具：

一、表的导出

     SELECT INTO…OUTFILE语句把表数据导出到一个文本文件中，并用LOAD DATA …INFILE语句恢复数据。但是这种方法只能导出或导入数据的内容，不包括表的结构，如果表的结构文件损坏，则必须先恢复原来的表的结构。
一、SELECT INTO…OUTFILE语法：
select * from Table into outfile '/路径/文件名'
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n'

fields子句：在FIELDS子句中有三个亚子句：TERMINATED BY、 [OPTIONALLY] ENCLOSED BY和ESCAPED BY。如果指定了FIELDS子句，则这三个亚子句中至少要指定一个。
（1）TERMINATED BY用来指定字段值之间的符号，例如，“TERMINATED BY ','”指定了逗号作为两个字段值之间的标志。
（2）ENCLOSED BY子句用来指定包裹文件中字符值的符号，例如，“ENCLOSED BY ' " '”表示文件中字符值放在双引号之间，若加上关键字OPTIONALLY表示所有的值都放在双引号之间。
（3）ESCAPED BY子句用来指定转义字符，例如，“ESCAPED BY '*'”将“*”指定为转义字符，取代“\”，如空格将表示为“*N”。
●   LINES子句：在LINES子句中使用TERMINATED BY指定一行结束的标志，如“LINES TERMINATED BY '?'”表示一行以“?”作为结束标志。

案例：

[root@rh64 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.25-73.1 Percona Server (GPL), Release 73.1, Revision 07b797f
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| prod               |
| test               |
+--------------------+
5 rows in set (0.04 sec)
mysql> use prod;
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> show tables;
+----------------+
| Tables_in_prod |
+----------------+
| t1             |
| t2             |
| t3             |
+----------------+
3 rows in set (0.00 sec)
mysql> insert into t2 select * from t1 order by id limit 100;
Query OK, 100 rows affected (0.33 sec)
Records: 100  Duplicates: 0  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)
mysql> select * from t2 into outfile '/tmp/t2_1.bak';
Query OK, 100 rows affected (0.00 sec)

[root@rh64 ~]# cat /tmp/t2_1.bak 
1       tom
1       tom
1       tom
1       tom
......

---默认字段之间是通过制表符（tab）进行分割的
mysql> select * from t2 into outfile '/tmp/t2_2.bak' fields terminated by ',' enclosed by '"';
Query OK, 100 rows affected (0.00 sec)
[root@rh64 ~]# cat /tmp/t2_2.bak 
"1","tom"
"1","tom"
"1","tom"
......

---发现第一列为数值字段，也被添加了“”

mysql> select * from t2 into outfile '/tmp/t2_3.bak' fields terminated by ',' optionally enclosed by '"';
Query OK, 100 rows affected (0.00 sec)
[root@rh64 ~]# cat /tmp/t2_3.bak 
1,"tom"
1,"tom"
1,"tom"
......

---发现第一列为数值字段，就没有添加了“”，只有字符型的字段有“”

二、表的导入

mysql> load data infile '/tmp/t2_1.bak' into table t2;
Query OK, 100 rows affected (0.06 sec)
Records: 100  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)
mysql> load data infile '/tmp/t2_2.bak' into table t2 fields terminated by ',' enclosed by '"';
Query OK, 100 rows affected (0.07 sec)
Records: 100  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)

 案例：通过备份脚本备份

1、建立备份目录

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

[root@rh64 ~]# chown -R mysql:mysql /data 
[root@rh64 ~]# chmod -R 777 /data    

2、授权用户
[root@rh64 ~]# /usr/bin/mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.25-73.1 Percona Server (GPL), Release 73.1, Revision 07b797f
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant all on prod.* to 'mysql'@'192.168.8.248' identified by 'oracle';
Query OK, 0 rows affected (0.00 sec)

---用户没有权限执行outfile，必须授予file权限
mysql> select * from t2 into outfile '/tmp/t2.txt';
ERROR 1045 (28000): Access denied for user 'root'@'192.168.8.248' (using password: YES)
mysql> select * from t2;
+------+------+
| id   | name |
+------+------+
|    1 | tom  |
|    1 | tom  |
|    1 | tom  |
|    1 | tom  |
|    1 | tom  |
|    1 | tom  |
|    1 | tom  |
授予file权限：
mysql> grant file on *.* to 'root'@'192.168.8.248' identified by 'oracle';
Query OK, 0 rows affected (0.00 sec)
备份脚本：
backup Script:    
#!/bin/bash
#FileName:select_into_bak.sh
#Desc:Use select into outfile to backup db or tables
#Created By:fedoracle
#Date:2015/10/15


DB=prod
USER=mysql
PASSWD=oracle
HOST=192.168.8.248
BAK_DIR=/data/mysql/backup/$DB
DATE=`date "+%Y-%m-%d %H-%M-%S"`


[ -d "$BAK_DIR" ] || /bin/mkdir -p $BAK_DIR && /bin/chown mysql:mysql $BAK_DIR
/usr/bin/mysql -h$HOST -u$USER -p$PASSWD -e "show tables from $DB" | grep -v "Tables_in" > $BAK_DIR/tab.txt
for table in `cat $BAK_DIR/tab.txt`
do
   /usr/bin/mysql -h$HOST -u$USER -p$PASSWD -e "select * from $DB.$table into outfile '"$BAK_DIR/$table".txt'  character set utf8;"
done

cd $BAK_DIR
/bin/tar -czf "$DB-$DATE".tar.gz *.txt
/bin/rm -f *.txt
exit 0

在执行备份时，写出文件到备份目录下，需关闭系统selinux：

[root@rh64 ~]# su - mysql
[mysql@rh64 ~]$ cd /data/mysql/backup/

[mysql@rh64 backup]$ sh bak.sh
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
ERROR 1 (HY000) at line 1: Can't create/write to file '/data/mysql/backup/prod/t1.txt' (Errcode: 13 - Permission denied)
Warning: Using a password on the command line interface can be insecure.
ERROR 1 (HY000) at line 1: Can't create/write to file '/data/mysql/backup/prod/t2.txt' (Errcode: 13 - Permission denied)
Warning: Using a password on the command line interface can be insecure.
ERROR 1 (HY000) at line 1: Can't create/write to file '/data/mysql/backup/prod/t3.txt' (Errcode: 13 - Permission denied)

查看关闭selinux：
[mysql@rh64 backup]$ cat /etc/sysconfig/selinux 
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=enforcing
# SELINUXTYPE= can take one of these two values:
#     targeted - Targeted processes are protected,
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted 

[mysql@rh64 backup]$ su -
Password: 
[root@rh64 ~]# vi /etc/sysconfig/selinux 
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled 
# SELINUXTYPE= can take one of these two values:
#     targeted - Targeted processes are protected,
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted
~
"/etc/sysconfig/selinux" 13L, 457C written
关闭selinux：
[root@rh64 ~]# setenforce 0
[root@rh64 ~]# getenforce 
Permissive
3、执行备份
[mysql@rh64 backup]$ sh bak.sh
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
[mysql@rh64 backup]$ 
4、验证备份
[mysql@rh64 prod]$ ls -l
total 356
-rwxrwxrwx. 1 mysql mysql    127 Oct 15 12:20 prod-2015-10-15 12-20-48.tar.gz
-rwxrwxrwx. 1 mysql mysql    129 Oct 15 12:24 prod-2015-10-15 12-24-36.tar.gz
-rwxrwxrwx. 1 mysql mysql   1094 Oct 15 12:25 prod-2015-10-15 12-25-47.tar.gz
-rw-rw-r--. 1 mysql mysql 344064 Oct 15 12:25 t1.txt
-rw-rw-r--. 1 mysql mysql    600 Oct 15 12:25 t2.txt
-rw-rw-r--. 1 mysql mysql      0 Oct 15 12:25 t3.txt
-rw-rw-r--. 1 mysql mysql      9 Oct 15 12:25 tab.txt
[mysql@rh64 prod]$ head t1.txt
1       tom
2       jerry
3       rose
1       tom
2       jerry
3       rose
1       tom
2       jerry
3       rose
1       tom
[mysql@rh64 prod]$