Mysql备份还原总结

男娘i 2022-02-05 04:45 269阅读 0赞

Mysql的备份与还原，是很重要、很常见的操作，这里总结一下。linux系统中Mysql备份还原，主要有三种方式：目录复制、转储sql语句方式、shell脚本、压缩方式、binlog方式、第三方工具。

## 1. 目录复制 ##

即直接复制目录数据文件方式备份、还原也是直接复制。使用比较简单。但有一定要求，有优点也有缺点。

** 要求**：

1）源Mysql与目标Mysql版本最好完全一致，不然会出现一些异常错误情况，造成生成的表是无法访问操作的；

2）最好在源Mysql停止运行时进行复制；

3）复制目录为Mysql的datadir

**优点**：

1) 操作简单，只是文件复制而已；

2）执行备份与还原时只是文件复制，所以消耗系统CPU、内存相对比较低。

**缺点**：

1）复制文件后，可能会发生文件读写权限问题，需要设置好文件权限；

2）如果版本不同的Mysql，复制后，可能无法访问识别；

**命令    **

> **cp -rf /xxx/mysql/data/xxx/\* /xxx/data/xxx/**

## 2. 转储sql语句方式 ##

**    优点**：

1)  命令行mysqldump执行，相对运行速度较快；

2）

**缺点**：

1）在大数据量长时间执行时，消耗系统CPU、内存很高，造成系统无法访问，如果资源不足会崩溃。

**命令    **

> ** 备份**
> 
> mysqldump  -hxx.xx.xx.xx -uroot -pxxx \{<databasename>\} \{\[tablename\]\}> /xxx/backup/\{<backupname>\}.sql
> 
>  **还原**
> 
> mysql -hxx.xx.xx.xx \-uroot -pxxx \{ <databasename\}> <  /xxx/backup/\{<backupname>\}.sql > /xxx/backup/error\_bak.log

## 3. shell脚本 ##

在使用常规方法备份出现一些异常错误时，导致无法正常备份还原Mysql，可以根据实际情况，采用定制shell脚本方式。

**优点**：

1)  使用脚本定制，相对比较灵活，执行某些特定的备份、还原；

2）可以制作成定时任务，在指定时间执行；

**缺点**：

1）需要对linux操作命令及Mysql比较熟悉，编写命令需要一定经验调试运行。

**命令    **

**备份脚本示例 .test\_backup.sh**

#!/bin/sh
    
    cd /proj_shell/proj_backup/backup_testdb
    
    for name in `cat data.txt`
     do
       mysqldump  -h127.0.0.1 -uroot -proot testdb $name> result/$name.sql
       echo $name
       sleep 2s
     done

执行备份命令

\#  /**test\_backup.sh**

可能会报错![20190504225857797.png][]

解决方法：

（1）使用vi工具

vi **test\_backup.sh**

（2）利用如下命令查看文件格式

:set ff 或 :set fileformat

可以看到如下信息

fileformat=[dos][] 或 fileformat=unix

（3） 利用如下命令修改文件格式

:set ff=unix 或 :set fileformat=unix

:wq (存盘退出)

成功后，再次执行\#  /**test\_backup.sh，即可查看运行结果。**

> **还原**
> 
> mysql -hxx.xx.xx.xx \-uroot -pxxx \{ <databasename\}> <  /xxx/backup/\{<backupname>\}.sql > /xxx/backup/error\_bak.log

## 3. 压缩方式 ##

**命令    **

> ** 备份**
> 
> mysqldump -uroot -proot --lock-tables=false --quick --extended-insert --default-character-set=utf8 \{<backupname>\} | gzip > /xxx/backup/\{<backupname>\}.sql.gz
> 
>  **还原**
> 
> gunzip < /xxx/backup/\{<backupname>\}.sql.gz | mysql -h192.168.10.1 -uroot -p123456 \{ <databasename\} > /xxx/backup/error.log

## 4. binlog方式 ##

参考：[https://www.cnblogs.com/martinzhang/p/3454358.html][https_www.cnblogs.com_martinzhang_p_3454358.html]

## 5. 第三方工具 ##

**使用mysqlhotcopy工具快速备份**

> **参考：**[https://www.cnblogs.com/kissdodog/p/4174421.html][https_www.cnblogs.com_kissdodog_p_4174421.html]

## 问题记录 ##

*  问题：数据量大mysql还原数据库，可能中断报错“Lost connection to MySQL server during query”

解决：my.cnf配置增加如下：

<table> 
 <tbody> 
  <tr> 
   <td> <p>[mysqld]</p> <p>skip-name-resolve</p> <p>...</p> <p>wait_timeout=2880000<br> interactive_timeout=2880000<br> max_allowed_packet=1500M</p> </td> 
  </tr> 
 </tbody> 
</table>

参考：

mysql恢复过大文件自动断开的解决方案 [https://blog.51cto.com/douya/1937244][https_blog.51cto.com_douya_1937244]

持续完善，待续...

[20190504225857797.png]: /images/20220205/8630219aaf4a4deb9e0bd4e9620f80d7.png
[dos]: http://www.2cto.com/os/dos/
[https_www.cnblogs.com_martinzhang_p_3454358.html]: https://www.cnblogs.com/martinzhang/p/3454358.html
[https_www.cnblogs.com_kissdodog_p_4174421.html]: https://www.cnblogs.com/kissdodog/p/4174421.html
[https_blog.51cto.com_douya_1937244]: https://blog.51cto.com/douya/1937244