MySQL Study之--MySQL 备份

冷不防 2022-08-09 06:25 190阅读 0赞

**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\]$