Mysql备份还原总结

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

可能会报错

解决方法：

（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

5. 第三方工具

使用mysqlhotcopy工具快速备份

参考：https://www.cnblogs.com/kissdodog/p/4174421.html

问题记录

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

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

[mysqld] skip-name-resolve ... wait_timeout=2880000 interactive_timeout=2880000 max_allowed_packet=1500M

 

 

 

 

 

参考：

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

持续完善，待续...