MySQL | 冷备 | 热备 mysqldump + select ...into outfile

不念不忘少年蓝@ 2021-11-01 04:28 668阅读 0赞

# 一、冷备及恢复 #

## 1、什么是冷备 ##

*  冷备就是在**数据库处于关闭的状态下的备份**
 *  优点是：  
    保证数据库的完整性  
    备份过程简单并且恢复速度快
 *  缺点：  
    关闭数据库，意味着相关的业务无法正常进行，用户无法访问你的网站
 *  一般冷备用于不是很重要、非核心业务上面

## 2 、冷备的备份 ##

先停掉mysql服务，再将整个mysql数据目录备份到远程主机或本地磁盘上，最后重启服务即可

关闭mysql服务
    # /usr/local/mysql/bin/mysqladmin -uroot -p shutdown
    
    备份数据文件
    # cp -rp /data/mysql/	本地目录
    或者
    # scp -rp /data/mysql/ root@远程主机IP：目录
    
    重启服务
    # /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

## 3、冷备的恢复 ##

关闭mysql服务后，将备份的数据目录替换原有的数据目录，再启动mysql服务

# 二、热备及恢复 #

## 1、什么是热备？ ##

热备是数据库处于**运行状态下的备份**，不影响现有业务的进行

热备又分为裸文件备份和逻辑备份

裸文件备份：基于底层数据文件的copy datafile  
逻辑备份：备份SQL语句，恢复时执行备份SQL，实现数据库数据的重现

## 2、mysqldump的备份与恢复 ##

a）mysqldump备份原理

先从buffer中找到需要备份的数据进行备份，如果buffer中没有，就去磁盘中的数据文件中查找并调回到buffer里面再备份，最后形成一个可编辑的备份文件

b）几个核心参数

*  –single-transaction ： 保证InnoDB备份数据时的一致性；当发起事务时，读取一个数据的快照，直到备份结束，都不会读取到本事务开始之后提交的任何数据（相当重要）
 *  \-A ： 备份所有数据库
 *  –dump-slave ：用于在从库端备份数据，在搭建新的从库时使用
 *  –database（-B）：用于备份多个数据库
 *  –quick(-q)：不会读取缓存中的数据  
    c）备份及恢复
 *  全库

#备份所有库
    mysqldump --single-transaction -uroot -p -A > /data/mysql/backup/all_2019-09-08.sql
    
    #恢复
    mysql -uroot -p < /data/mysql/backup/all_2019-09-08.sql

*  单个库

#备份linux库
    mysqldump --single-transaction -uroot -p linux > /data/mysql/backup/linux_2019-09-08.sql
    
    #恢复
    mysql -uroot -p linux < /data/mysql/backup/linux_2019-09-08.sql 
    #注：如果linux库存在，则可以直接恢复，如果linux库不存在，则需要在恢复前，先去数据库建立一个linux库

*  单个表

#备份库linux下的表lxn
    mysqldump --single-transaction -uroot -p linux lxn > /data/mysql/backup/lxn_2019-09-08.sql
    
    #恢复
    mysql -uroot -p linux < /data/mysql/backup/lxn_2019-09-08.sql
    #注：恢复单个表直接写库就行

## 3、mysqldump备份的原理缺陷 ##

mysqldump是先从buffer中找到想要备份的内容，如果buffer中没有，就去访问磁盘中的数据文件，再把数据掉回到内存，再形成数据文件。  
问题出现在把磁盘中的数据掉回到内存，这个过程可能把内存里的热数据给冲掉，从而导致数据库的性能下降！

## 4、select … into outfile ##

a）简单描述

*  逻辑备份方法
 *  恢复速度非常快
 *  只能备份表中的数据，不能包含表中的结构
 *  备份完成之后，表被drop掉了，就无法实现恢复操作

b）备份一个表lxn

select * from lxn into outfile '/tmp/lxn.sql';

![在这里插入图片描述][201909082030166.png]  
![在这里插入图片描述][20190908205134436.png]  
如果出现以下报错：  
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statementERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement  
![在这里插入图片描述][20190908203236911.png]  
解决方案：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70]  
secure\_file\_priv 为 NULL表示secure\_file\_priv 没有设置

vim /etc/my.cnf
    #加入以下信息
    secure_file_priv = /tmp

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 1]  
然后重启mysql服务即可  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 2]

*  注：如果再次将备份文件以同样的命名导入到/tmp下，则会报错文件已存在  
    ![在这里插入图片描述][20190908205741500.png]

c）恢复一个表lxn

把表中的数据删除  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 3]  
恢复

LOAD DATA INFILE '/tmp/lxn.sql' INTO TABLE lxn;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 4]

[201909082030166.png]: /images/20211101/3f0f8d5a31284063b238f5e0c2e80d94.png
[20190908205134436.png]: /images/20211101/32fa0d5a66304cfaad9df7190420ee1a.png
[20190908203236911.png]: /images/20211101/7899ab8607994a1b8598aae9ad9ace09.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70]: /images/20211101/7ba42a074dbd4999ac306642bdb49cbf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 1]: /images/20211101/c8563b46b3f34930bce9de63323f9e7b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 2]: /images/20211101/14e9a9539dcc45cc92abadcbe46fad58.png
[20190908205741500.png]: /images/20211101/3eaba1cf4219494585cce0a64ddbc5a6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 3]: /images/20211101/44ba5887be38499aa95e29ff715025f2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI5NzMwMw_size_16_color_FFFFFF_t_70 4]: /images/20211101/8ad644ed09ec4a629be3e526e60aec75.png