恢复云数据库MySQL的备份文件到自建数据库

  • A+
所属分类:MySQL
高性能企业级服务器首台5折

云数据库MySQL版使用开源软件Percona Xtrabackup对数据库进行备份,所以您可以使用该软件将云数据库MySQL的备份文件恢复到自建数据库中,本文将介绍详细的操作步骤。

关于云数据库MySQL版如何备份数据,请参见备份RDS数据

前提条件

  • 本地MySQL数据库安装在64位的Linux系统中,且与云数据库MySQL版的版本相同。

    说明:由于软件限制,目前只支持将云数据库MySQL的备份文件恢复到安装在Linux系统中的自建MySQL数据库中。但在Linux下恢复的数据文件,可以在Windows和Linux操作系统中正常使用。

  • 操作系统中已安装数据恢复工具Percona XtraBackup 2.2.9或以上版本。若未安装,请从Percona XtraBackup官网下载,安装指导请参见官方文档
  • 操作系统中已安装数据备份文件解压工具rds_backup_extract.sh。若未安装,请点击下载

操作步骤

  1. 获取数据备份文件下载地址,具体操作步骤如下。
    1. 登录RDS管理控制台
    2. 选择目标实例所在地域。
    3. 单击目标实例的ID,进入基本信息页面。
    4. 在左侧导航栏中,选择备份恢复,进入备份恢复页面。
    5. 选择数据备份标签页。
    6. 选择查询的时间范围,然后单击查询
    7. 在数据备份列表中,找到要下载的数据备份,并单击其对应的下载,如下图所示。下载数据备份
    8. 实例备份文件下载窗口,单击复制外网地址,获取数据备份文件外网下载地址,如下图所示。复制外网下载地址
  2. 下载数据备份文件,具体操作步骤如下。
    1. 登录云服务器ECS
    2. 执行如下命令,下载数据备份文件。
      1. 1
        <span class="pln">wget </span><span class="pun">-</span><span class="pln">c </span><span class="str"><span class="hljs-string">'&lt;数据备份文件外网下载地址&gt;'</span></span> <span class="pun">-</span><span class="pln">O </span><span class="pun">&lt;自定义文件名&gt;<span class="hljs-selector-class">.</span></span><span class="pln"><span class="hljs-selector-class">tar</span></span><span class="pun"><span class="hljs-selector-class">.</span></span><span class="pln"><span class="hljs-selector-class">gz</span></span>

      参数说明:

      • -c:启用断点续传模式。
      • -O:将下载的结果保存为指定的文件(建议使用URL中包含的文件名)。

        说明:若提示显示100%进度,则表示文件下载完成。

  3. 将下载的数据备份恢复到本地MySQL数据库中,具体操作步骤如下。
    1. 执行如下命令,解压已下载的数据备份文件。

      说明:本文以自定义路径

      1
      /home/mysql/data

      为例,您可以根据实际情况将其替换成实际路径。

      1. 1
        <span class="pln">bash rds_backup_extract</span><span class="pun"><span class="hljs-selector-class">.</span></span><span class="pln"><span class="hljs-selector-class">sh</span> </span><span class="pun">-</span><span class="pln">f </span><span class="pun">&lt;数据备份文件名&gt;<span class="hljs-selector-class">.</span></span><span class="pln"><span class="hljs-selector-class">tar</span></span><span class="pun"><span class="hljs-selector-class">.</span></span><span class="pln"><span class="hljs-selector-class">gz</span> </span><span class="pun">-</span><span class="pln">C </span><span class="pun">/</span><span class="pln">home</span><span class="pun">/</span><span class="pln">mysql</span><span class="pun">/</span><span class="pln">data</span>

      参数说明:

      • -f:指定要解压的备份集文件。
      • -C:指定文件要解压到的目录。可选参数,若不指定就解压到当前目录。
    2. 执行如下命令,查询解压后生成的文件。
      1. 1
        <span class="pln"><span class="hljs-title">ls</span> </span><span class="pun">-</span><span class="pln">l </span><span class="pun">/</span><span class="pln">home</span><span class="pun">/</span><span class="pln">mysql</span><span class="pun">/</span><span class="pln"><span class="hljs-class"><span class="hljs-keyword">data</span></span></span>

      命令执行成功后,系统会返回如下结果,其中蓝色字体为生成备份文件时RDS实例所包含的数据库。

      查看解压文件

    3. 执行如下命令,恢复解压好的备份文件。
      1. 1
        <span class="pln">innobackupex </span><span class="pun">--</span><span class="pln">defaults</span><span class="pun">-</span><span class="pln"><span class="hljs-keyword">file</span></span><span class="pun">=</span><span class="str">/home/</span><span class="pln">mysql</span><span class="pun">/</span><span class="pln"><span class="hljs-keyword">data</span></span><span class="pun">/</span><span class="pln">backup</span><span class="pun">-</span><span class="kwd">my</span><span class="pun">.</span><span class="pln">cnf </span><span class="pun">--</span><span class="pln">apply</span><span class="pun">-</span><span class="pln"><span class="hljs-built_in">log</span> </span><span class="pun">/</span><span class="pln">home</span><span class="pun">/</span><span class="pln">mysql</span><span class="pun">/</span><span class="pln"><span class="hljs-keyword">data</span></span>

      若系统返回如下类似结果,则说明备份文件已成功恢复到本地数据库。

      恢复成功

  4. 为避免版本问题,需修改backup-my.cnf参数,具体操作步骤如下。
    1. 执行如下命令,以文本方式编辑backup-my.cnf文件。
      1. 1
        <span class="pln">vi </span><span class="pun"><span class="hljs-regexp">/</span></span><span class="pln"><span class="hljs-regexp">home</span></span><span class="pun"><span class="hljs-regexp">/</span></span><span class="pln"><span class="hljs-regexp">my</span>sql</span><span class="pun"><span class="hljs-regexp">/</span></span><span class="pln"><span class="hljs-regexp">data</span></span><span class="pun"><span class="hljs-regexp">/</span></span><span class="pln">backup</span><span class="pun">-</span><span class="kwd">my</span><span class="pun">.</span><span class="pln">cnf</span>
    2. 执行如下命令,注释掉如下参数。
      1. 1
        <span class="com"><span class="hljs-comment">#innodb_fast_checksum</span></span>
      2. 1
        <span class="com"><span class="hljs-comment">#innodb_page_size</span></span>
      3. 1
        <span class="com"><span class="hljs-comment">#innodb_log_block_size</span></span>
    3. Esc键,然后输入
      1
      :wq

      进行保存并关闭编辑器。

  5. 执行如下命令,修改文件属主,并确定文件所属为MySQL用户。
    1. 1
      <span class="pln"> chown </span><span class="pun">-</span><span class="pln">R mysql</span><span class="pun">:</span><span class="pln">mysql </span><span class="pun">/</span><span class="pln">home</span><span class="pun">/</span><span class="pln">mysql</span><span class="pun">/</span><span class="pln">data</span>
  6. 执行如下命令,启动MySQL进程。
    1. 1
      <span class="pln"> mysqld_safe </span><span class="pun"><span class="hljs-attribute">--</span></span><span class="pln"><span class="hljs-attribute">defaults</span></span><span class="pun"><span class="hljs-attribute">-</span></span><span class="pln"><span class="hljs-attribute">file</span></span><span class="pun">=</span><span class="str">/home/</span><span class="pln">mysql</span><span class="pun">/</span><span class="pln">data</span><span class="pun">/</span><span class="pln">backup</span><span class="pun">-</span><span class="kwd">my</span><span class="pun">.</span><span class="pln">cnf </span><span class="pun"><span class="hljs-attribute">--</span></span><span class="pln"><span class="hljs-attribute">user</span></span><span class="pun">=</span><span class="pln">mysql </span><span class="pun"><span class="hljs-attribute">--</span></span><span class="pln"><span class="hljs-attribute">datadir</span></span><span class="pun">=</span><span class="str">/home/</span><span class="pln">mysql</span><span class="pun">/</span><span class="pln">data </span><span class="pun">&amp;</span>
  7. 执行如下命令,登录MySQL数据库以验证进程启动成功。
    1. 1
      <span class="pln"><span class="hljs-attribute"> mysql </span></span><span class="pun"><span class="hljs-attribute">-</span></span><span class="pln"><span class="hljs-attribute">uroot</span></span>

    若系统返回如下结果,进程启动成功,则说明已成功执行参数注释和修改文件属主。

    启动成功

  8. 恢复完成后,表mysql.user中不包含在RDS实例中创建的用户,需要新建。在新建用户前,执行如下命令。
    1. 1
       <span class="kwd"><span class="hljs-keyword">delete</span></span> <span class="kwd"><span class="hljs-keyword">from</span></span><span class="pln"> mysql</span><span class="pun">.</span><span class="pln">db </span><span class="kwd"><span class="hljs-keyword">where</span></span><span class="pln"> <span class="hljs-keyword">user</span></span><span class="pun">&lt;&gt;’</span><span class="pln">root</span><span class="pun">’</span> <span class="kwd"><span class="hljs-keyword">and</span></span><span class="pln"> <span class="hljs-keyword">char_length</span></span><span class="pun">(</span><span class="pln"><span class="hljs-keyword">user</span></span><span class="pun">)&gt;</span><span class="lit"><span class="hljs-number">0</span></span><span class="pun">;</span><span class="kwd"><span class="hljs-keyword">delete</span></span> <span class="kwd"><span class="hljs-keyword">from</span></span><span class="pln"> mysql</span><span class="pun">.</span><span class="pln">tables_priv </span><span class="kwd"><span class="hljs-keyword">where</span></span><span class="pln"> <span class="hljs-keyword">user</span></span><span class="pun">&lt;&gt;’</span><span class="pln">root</span><span class="pun">’</span> <span class="kwd"><span class="hljs-keyword">and</span></span><span class="pln"> <span class="hljs-keyword">char_length</span></span><span class="pun">(</span><span class="pln"><span class="hljs-keyword">user</span></span><span class="pun">)&gt;</span><span class="lit"><span class="hljs-number">0</span></span><span class="pun">;</span><span class="pln"><span class="hljs-keyword">flush</span> <span class="hljs-keyword">privileges</span></span><span class="pun">;</span>

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: