数据库及时备份可以帮助我们在数据库出现异常宕机时及时的使用备份数据进行恢复工作，将因为数据库宕机产生的影响降低到最小。上一篇针对使用xtrabackup工具进行物理备份和数据恢复做了一个详细讲解，本篇主要谈谈如何使用mysql自带的备份工具mysqldump进行逻辑备份和数据恢复。如果还围观看过上一篇文章的可以先行查询上一篇文章关于使用xtrabackup进行数据备份与恢复：Mysql备份与恢复(1)—物理备份。

前言
上一篇可能存在一个问题，最后一步恢复数据到/var/lib/mysql目录成功，但是最后重启数据库启动不起来，数据库日志一般存放路径为：/var/log/mysqld.log，经过定位日志可以查看到错误日志原因：

  • 2019-10-14T07:43:22.351861Z 0 [ERROR] InnoDB: The innodb_system data file ‘ibdata1’ must be writable

  • 2019-10-14T07:43:22.351885Z 0 [ERROR] InnoDB: The innodb_system data file ‘ibdata1’ must be writable

  • 2019-10-14T07:43:22.351892Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error

  • 2019-10-14T07:43:22.952508Z 0 [ERROR] Plugin ‘InnoDB’ init function returned error.

  • 2019-10-14T07:43:22.952596Z 0 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.

  • 2019-10-14T07:43:22.952606Z 0 [ERROR] Failed to initialize builtin plugins.

  • 2019-10-14T07:43:22.952613Z 0 [ERROR] Aborting

很明显是因为共享表文件ibdata1不可写，所以解决方案很简单：将/var/lib/mysql下的文件权限改成可写，使用命令：

  • chmod -R 777 /var/lib/mysql

然后重启mysql服务，可以发现mysql服务已经成功启动，而且备份数据也成功进行恢复了。
file

物理备份和逻辑备份的区别

逻辑备份

优点：

  • 备份成sql文件，恢复数据很简单。

  • 逻辑备份与存储引擎无关，所以可以通用备份。

  • 有助于避免数据损坏。

缺点：

  • 数据恢复速度较慢。

  • 需要数据库服务正常时安排cpu去进行还原操作。

物理备份

优点：

  • 容易跨平台，基于文件的物理备份。

  • 恢复数据很快，实际上就是将备份文件拷贝回原路径。

缺点：

  • 数据量大的话文件大。

  • 备份文件存储在磁盘本地，如果磁盘出问题容易造成备份文件丢失。

mysqldump逻辑备份

mysqldump是mysql自带的一个简单的备份工具，由于操作简单而且对不同的存储引擎通用，所以比较受欢迎。逻辑备份实际上是将数据库的数据被分成一个sql文件，然后恢复数据时只需要用图形化工具导入这个sql文件或者dos下使用source命令引入就可恢复数据。首先我们先看下mysqldump的基本语法：

  • mysqldump -uroot -proot -h127.0.0.1 -P3306 test test> test.sql

命令还有几个可传参数：

  • –all-databases：备份全部数据库。

  • -t：不导出创建表的语句。

  • -d：不导出数据。

  • -R：导出存储过程和函数。

  • –default-character-set：指定编码

  • –result-file：指定导出文件路径及文件名。

  • –skip-triggers：不导出触发器。

1. 备份前踩坑

如果系统中存在两个mysql版本，执行mysqldump命令就会报错，报错信息如下：

  • mysqldump: Couldn’t execute ‘SET OPTION SQL_QUOTE_SHOW_CREATE=1’: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘OPTION SQL_QUOTE_SHOW_CREATE=1’ at line 1 (1064)

首先使用mysqldump –version命令查看mysqldump当前版本，看是否和mysql版本一致：

  • mysqldump Ver 10.13 Distrib 5.5.29, for Win64 (x86)

再使用rpm -qa | grep -i mysql命令查看mysql版本

  • mysql-community-libs-5.7.27-1.el7.x86_64
  • mysql-community-server-5.7.27-1.el7.x86_64
  • mysql57-community-release-el7-8.noarch
  • mysql-community-common-5.7.27-1.el7.x86_64
  • mysql-community-libs-compat-5.7.27-1.el7.x86_64
  • perl-DBD-MySQL-4.023-6.el7.x86_64
  • mysql-community-client-5.7.27-1.el7.x86_64

可以看到mysql版本与mysqldump版本不一致造成的报错原因，使用whereis mysqld查看mysql安装路径：

  • /usr/local/mysql/bin/mysqld

然后将同处于bin目录下的mysqldump拷贝到/usr/bin/目录下覆盖旧版本的mysqldump即可解决版本不一致的问题：

  • cp /usr/local/mysql/bin/mysqldump /usr/bin/mysqldump

2. 数据备份

刚才说过了命令，我们使用mysqldump是可以进行多种不同方案的备份的。接下来我们简单看几种备案方案：

(1) 全库备份：使用全库备份会将所有数据库中的数据全部备份到同一个sql文件中。

  • mysqldump -uroot -proot -h49.235.28.88 -P3306 –all-databases> /usr/local/all.sql

(2) 备份单个数据库：将lxy数据库中的数据备份到sql文件lxy.sql中。

  • mysqldump -uroot -proot -h49.235.28.88 -P3306 lxy> /usr/local/lxy.sql

(3) 备份lxy数据库中的users表到user_test.sql文件。

  • mysqldump -uroot -proot -h49.235.28.88 -P3306 lxy users> /usr/local/user_test.sql

(4) 只备份lxy数据库的表结构，不备份数据到lxy1.sql文件中。

  • mysqldump -uroot -proot -h49.235.28.88 -P3306 lxy -d> /usr/local/lxy1.sql

(5) 备份lxy数据库的表结构和存储过程，不备份数据到lxy2.sql中。

  • mysqldump -uroot -proot -h49.235.28.88 -P3306 lxy -d -R> /usr/local/lxy2.sql

(6) 不备份表结构，只备份数据到lxy3.sql。

  • mysqldump -uroot -proot -h49.235.28.88 -P3306 lxy -t> /usr/local/lxy3.sql

数据恢复

表级恢复

(1) 登录mysql，选择对应的数据库，然后设置编码。

(2) 使用source命令导入备份数据。

  • source /usr/local/all.sql

单库恢复

(1) 登录Mysql，删除要恢复的旧数据库。

(2) 使用source命令导入备份数据。

全库恢复

(1) 退出mysql，使用mysql命令恢复数据。

  • mysql < /usr/local/all.sql -p

然后在控制台输入密码，密码不会显示别以为没有输入成功。

mysqldump全量备份 + mysqlbinlog增量备份

刚才已经将mysql全量备份及数据恢复的方式讲完了，可以发现mysqldump的使用方式很简单，但是使用mysqldump进行数据备份会存在一个问题：恢复数据时会丢失掉从备份点开始的更新数据，所以我们需要结合mysqlbinlog进行二进制日志增量备份，首先修改/etc/my.cnf文件，在[mysqld]下启用二进制日志：

  • log_bin=mysql-bin

开启二进制日志，数据库的任何更新操作都会记录到二进制日志中。接下来看下mysqldump全量备份 + mysqlbinlog增量备份如何进行操作：

首先进行全量备份,添加–flush-logs参数生成新的二进制日志文件：

  • mysqldump –single-transaction –flush-logs –master-data=2 > /usr/local/all1.sql

所以说实际上备份有两个文件：mysqldump全量备份生成的/usr/local/all1.sql以及二进制日志文件/var/lib/mysql/mysql-bin.000003文件。所以恢复数据时，我们需要分成两部分去进行恢复数据：

先恢复全量备份的数据：

  • mysql < /usr/local/all1.sql -p

然后输入密码确认恢复数据。

然后恢复增量备份：

  • mysqlbinlog /var/lib/mysql/mysql-bin.000003 | mysql -p

这样就可以将数据恢复到恢复点时的数据。而且mysqlbinlog可以通过时间间隔来精确地恢复某个时刻的数据，我们看看bonlog几个可传参数：

  • –start-datetime：从某个时间点开始恢复数据

  • –stop-datetime：恢复截止到某个时间内的数据

  • –start-position：开始恢复数据的位置信息，位置信息可以从日志文件的log_pos中获取。

  • –end-position：截止恢复数据的位置信息，位置信息可以从日志文件的log_pos中获取。

比如我们需要恢复2019-01-01到2019-09-09的数据，我们在恢复增量日志时就可以这么书写mysqlbinlog命令：

  • mysqlbinlog –start-datetime=”2019-01-01 00:00:00″ –stop-datetime=”2019-09-09 23:59:59″ /var/lib/mysql/mysql-bin.000003 | mysql -p

欢迎关注公众号：程序猿周先森。

file

欢迎关注公众号：程序猿周先森。文章原创于微信公众号，本平台不定时更新。

版权声明：本文为niyueling原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/niyueling/p/12171791.html