当前位置：Gxlcms > 数据库问题 > 学会4种备份MySQL数据库（基本备份方面没问题了）

学会4种备份MySQL数据库（基本备份方面没问题了）

时间：2021-07-01 10:21:17 帮助过：21人阅读

向数据库施加读锁

  1. <code class="language-sql hljs">mysql> <span class="hljs-operator"><span class="hljs-keyword">FLUSH <span class="hljs-keyword">TABLES <span class="hljs-keyword">WITH <span class="hljs-keyword">READ <span class="hljs-keyword">LOCK;    #向所有表施加读锁<br>Query OK, 0 rows affected (0.00 sec)<br></span></span></span></span></span></span></code>

备份数据文件

  1. <code class="hljs coffeescript">[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># mkdir /backup   #创建文件夹存放备份数据库文件<br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># cp -a /var/lib/mysql/* /backup     #保留权限的拷贝源数据文件<br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># ls /backup   #查看目录下的文件<br>employees  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.sock  test<br></span></span></span></span></span></span></code>

模拟数据丢失并恢复

  1. <code class="language-sql hljs">[root@node1 ~]# rm -rf /var/lib/mysql<span class="hljs-comment">/*    #删除数据库的所有文件<br>[root@node1 ~]# service mysqld restart   #重启MySQL, 如果是编译安装的应该不能启动, 如果rpm安装则会重新初始化数据库<br><br><br>mysql> SHOW DATABASES;    #因为我们是rpm安装的, 连接到MySQL进行查看, 发现数据丢失了！<br>+--------------------+<br>| Database           |<br>+--------------------+<br>| information_schema |<br>| mysql              |<br>| test               |<br>+--------------------+<br>3 rows in set (0.00 sec)<br><br>[root@node1 ~]# rm -rf /var/lib/mysql<span class="hljs-comment">/*    #这一步可以不做<br>[root@node1 ~]# cp -a /backup<span class="hljs-comment">/* /var/lib/mysql/    #将备份的数据文件拷贝回去<br>[root@node1 ~]# service mysqld restart  #重启MySQL<br><br><br>#重新连接数据并查看<br><br>mysql> SHOW DATABASES;    #数据库已恢复<br>+--------------------+<br>| Database           |<br>+--------------------+<br>| information_schema |<br>| employees          |<br>| mysql              |<br>| test               |<br>+--------------------+<br>4 rows in set (0.00 sec)<br><br>mysql> USE employees;      <br><br>mysql> SELECT COUNT(*) FROM employees;    #表的行数没有变化<br>+----------+<br>| COUNT(*) |<br>+----------+<br>|   300024 |<br>+----------+<br>1 row in set (0.06 sec)<br><br><br>##完成<br></span></span></span></code>

使用mysqldump+复制BINARY LOG备份

我们这里使用的是使用yum安装的mysql-5.1的版本, 使用的数据集为从网络上找到的一个员工数据库

我们通过mysqldump进行一次完全备份, 再修改表中的数据, 然后再通过binary log进行恢复 二进制日志需要在mysql配置文件中添加 log_bin=on 开启

mysqldump命令介绍

mysqldump是一个客户端的逻辑备份工具, 可以生成一个重现创建原始数据库和表的SQL语句, 可以支持所有的存储引擎, 对于InnoDB支持热备

官方文档介绍

  1. <code class="hljs livecodeserver"><span class="hljs-comment">#基本语法格式<br><br><span class="hljs-built_in">shell> mysqldump [options] db_name [tbl_name ...]    恢复需要手动CRATE DATABASES<br><span class="hljs-built_in">shell> mysqldump [options] <span class="hljs-comment">--databases db_name ...   恢复不需要手动创建数据库<br><span class="hljs-built_in">shell> mysqldump [options] <span class="hljs-comment">--all-databases           恢复不需要手动创建数据库<br><br><br>其他选项:<br>     -E, <span class="hljs-comment">--events: 备份事件调度器<br>     -R, <span class="hljs-comment">--routines: 备份存储过程和存储函数<br>     <span class="hljs-comment">--triggers: 备份表的触发器; --skip-triggers <br>     <span class="hljs-comment">--master-date[=value]  <br>         <span class="hljs-number">1: 记录为CHANGE MASTER TO 语句、语句不被注释<br>         <span class="hljs-number">2: 记录为注释的CHANGE MASTER TO语句<br>         基于二进制还原只能全库还原<br><br>     <span class="hljs-comment">--flush-logs: 日志滚动<br>         锁定表完成后执行日志滚动<br></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

查看数据库的信息

  1. <code class="language-sql hljs">mysql> <span class="hljs-operator"><span class="hljs-keyword">SHOW <span class="hljs-keyword">DATABASES;    #查看当前的数据库, 我们的数据库为employees<br>+<span class="hljs-comment">--------------------+<br>| Database           |<br>+<span class="hljs-comment">--------------------+<br>| information_schema |<br>| employees          |<br>| mysql              |<br>| test               |<br>+<span class="hljs-comment">--------------------+<br>4 rows in <span class="hljs-operator"><span class="hljs-keyword">set (<span class="hljs-number">0.00 sec)<br><br>mysql> <span class="hljs-keyword">USE employees;<br>Database changed<br>mysql> <span class="hljs-operator"><span class="hljs-keyword">SHOW <span class="hljs-keyword">TABLES;         #查看当前库中的表<br>+<span class="hljs-comment">---------------------+<br>| Tables_in_employees |<br>+<span class="hljs-comment">---------------------+<br>| departments         |<br>| dept_emp            |<br>| dept_manager        |<br>| employees           |<br>| salaries            |<br>| titles              |<br>+<span class="hljs-comment">---------------------+<br>6 rows in <span class="hljs-operator"><span class="hljs-keyword">set (<span class="hljs-number">0.00 sec)<br><br>mysql> <span class="hljs-keyword">SELECT <span class="hljs-keyword">COUNT(*) <span class="hljs-keyword">FROM employees;   #由于篇幅原因, 我们这里只看一下employees的行数为300024<br>+<span class="hljs-comment">----------+<br>| COUNT(*) |<br>+<span class="hljs-comment">----------+<br>|   300024 |<br>+<span class="hljs-comment">----------+<br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set (<span class="hljs-number">0.05 sec)<br><br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

使用mysqldump备份数据库

  1. <code class="language-sql hljs">[root@node1 ~]# mysql -uroot -p -e ‘<span class="hljs-operator"><span class="hljs-keyword">SHOW <span class="hljs-keyword">MASTER <span class="hljs-keyword">STATUS<span class="hljs-string">‘   #查看当前二进制文件的状态, 并记录下position的数字<br>+------------------+----------+--------------+------------------+<br>| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |<br>+------------------+----------+--------------+------------------+<br>| mysql-bin.000003 |      106 |              |                  |<br>+------------------+----------+--------------+------------------+<br><br>[root@node1 ~]# mysqldump --all-databases --lock-all-tables  > backup.sql   #备份数据库到backup.sql文件中<br><br>mysql> CREATE DATABASE TEST1;   #创建一个数据库<br>Query OK, 1 row affected (0.00 sec)<br><br>mysql> SHOW MASTER STATUS;   #记下现在的position<br>+------------------+----------+--------------+------------------+<br>| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |<br>+------------------+----------+--------------+------------------+<br>| mysql-bin.000003 |      191 |              |                  |<br>+------------------+----------+--------------+------------------+<br>1 row in set (0.00 sec)<br><br>[root@node1 ~]# cp /var/lib/mysql/mysql-bin.000003 /root  #备份二进制文件<br>[root@node1 ~]# service mysqld stop   #停止MySQL<br>[root@node1 ~]# rm -rf /var/lib/mysql/*   #删除所有的数据文件<br>[root@node1 ~]# service mysqld start    #启动MySQL, 如果是编译安装的应该不能启动(需重新初始化), 如果rpm安装则会重新初始化数据库<br><br><br>mysql> SHOW DATABASES;   #查看数据库, 数据丢失!<br>+--------------------+<br>| Database           |<br>+--------------------+<br>| information_schema |<br>| mysql              |<br>| test               |<br>+--------------------+<br>3 rows in set (0.00 sec)<br><br>mysql> SET sql_log_bin=OFF;   #暂时先将二进制日志关闭  <br>Query OK, 0 rows affected (0.00 sec)<br><br><br>mysql> source backup.sql  #恢复数据，所需时间根据数据库时间大小而定<br><br>mysql> SET sql_log_bin=ON; 开启二进制日志<br><br>mysql> SHOW DATABASES;   #数据库恢复, 但是缺少TEST1<br>+--------------------+<br>| Database           |<br>+--------------------+<br>| information_schema |<br>| employees          |<br>| mysql              |<br>| test               |<br>+--------------------+<br>4 rows in set (0.00 sec)<br><br>[root@node1 ~]# mysqlbinlog --start-position=106 --stop-position=191 mysql-bin.000003 | mysql employees #通过二进制日志增量恢复数据<br><br>mysql> SHOW DATABASES;    #现在TEST1出现了！<br>+--------------------+<br>| Database           |<br>+--------------------+<br>| information_schema |<br>| TEST1              |<br>| employees          |<br>| mysql              |<br>| test               |<br>+--------------------+<br>5 rows in set (0.00 sec)<br><br><br><br>#完成<br></span></span></span></span></span></code>

使用lvm2快照备份数据

做实验之前我们先回顾一下lvm2-snapshot的知识

LVM快照简单来说就是将所快照源分区一个时间点所有文件的元数据进行保存，如果源文件没有改变，那么访问快照卷的相应文件则直接指向源分区的源文件，如果源文件发生改变，则快照卷中与之对应的文件不会发生改变。快照卷主要用于辅助备份文件。 这里只简单介绍，点击查看详细介绍

部署lvm环境

  1. <code class="hljs livescript">添加硬盘; 这里我们直接实现SCSI硬盘的热插拔, 首先在虚拟机中添加一块硬盘, 不重启<br><br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># ls /dev/sd*   #只有以下几块硬盘, 但是我们不重启可以让系统识别新添加的硬盘<br>/dev/sda  /dev/sda1  /dev/sda2<br><br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># echo ‘- - -‘ > /sys/class/scsi_host/host0/scan <br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># echo ‘- - -‘ > /sys/class/scsi_host/host1/scan <br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># echo ‘- - -‘ > /sys/class/scsi_host/host2/scan <br><br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># ls /dev/sd*    #看！sdb识别出来了<br>/dev/sda  /dev/sda1  /dev/sda2  /dev/sdb<br><br><br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># fdisk /dev/sdb   #分区<br>Device contains neither a valid DOS partition table, nor Sun, SGI <span class="hljs-keyword">or OSF disklabel<br>Building a <span class="hljs-keyword">new DOS disklabel <span class="hljs-keyword">with disk identifier <span class="hljs-number">0xd353d192.<br>Changes will remain <span class="hljs-keyword">in memory only, <span class="hljs-keyword">until you decide <span class="hljs-keyword">to write them.<br>After <span class="hljs-literal">that, <span class="hljs-keyword">of course, the previous content won<span class="hljs-string">‘t be recoverable.<br><br>Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)<br><br>WARNING: DOS-compatible mode is deprecated. It‘s strongly recommended <span class="hljs-keyword">to<br>         <span class="hljs-keyword">switch <span class="hljs-literal">off the mode (command <span class="hljs-string">‘c‘) <span class="hljs-keyword">and change display units <span class="hljs-keyword">to<br>         sectors (command <span class="hljs-string">‘u‘).<br><br>Command (m <span class="hljs-keyword">for help): n<br>Command action<br>   e   extended<br>   p   primary partition (<span class="hljs-number">1-<span class="hljs-number">4)<br>p<br>Partition number (<span class="hljs-number">1-<span class="hljs-number">4): <span class="hljs-number">1<br>First cylinder (<span class="hljs-number">1-<span class="hljs-number">2610, <span class="hljs-keyword">default <span class="hljs-number">1): <br>Using <span class="hljs-keyword">default value <span class="hljs-number">1<br>Last cylinder, +cylinders <span class="hljs-keyword">or +size{K,M,G} (<span class="hljs-number">1-<span class="hljs-number">2610, <span class="hljs-keyword">default <span class="hljs-number">2610): +<span class="hljs-number">15G<br><br>Command (m <span class="hljs-keyword">for help): t<br>Selected partition <span class="hljs-number">1<br>Hex code (type L <span class="hljs-keyword">to list codes): <span class="hljs-number">8e<br>Changed system type <span class="hljs-keyword">of partition <span class="hljs-number">1 <span class="hljs-keyword">to <span class="hljs-number">8e (Linux LVM)<br><br>Command (m <span class="hljs-keyword">for help): w<br>The partition table has been altered!<br><br>Calling ioctl() <span class="hljs-keyword">to re-read partition table.<br>Syncing disks.<br>You have <span class="hljs-keyword">new mail <span class="hljs-keyword">in /<span class="hljs-keyword">var/spool/mail/root<br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># partx -a /dev/sdb<br><span class="hljs-attribute">BLKPG: Device <span class="hljs-keyword">or resource busy<br>error adding partition <span class="hljs-number">1<br><br><span class="hljs-comment">##创建逻辑卷<br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># pvcreate /dev/sdb1<br>  Physical volume <span class="hljs-string">"/dev/sdb1" successfully created<br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># vgcreate myvg /dev/sdb1 <br>  Volume group <span class="hljs-string">"myvg" successfully created<br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># lvcreate -n mydata -L 5G myvg <br>  Logical volume <span class="hljs-string">"mydata" created.<br><br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># mkfs.ext4 /dev/mapper/myvg-mydata   #格式化<br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># mkdir /lvm_data<br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># mount /dev/mapper/myvg-mydata /lvm_data  #挂载到/lvm_data<br><br><br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># vim /etc/my.cnf    #修改mysql配置文件的datadir如下<br><br>datadir=/lvm_data<br><br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># service mysqld restart  #重启MySQL<br><br><span class="hljs-comment">####重新导入employees数据库########略过####<br><br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

查看数据库的信息

  1. <code class="language-sql hljs">mysql> <span class="hljs-operator"><span class="hljs-keyword">SHOW <span class="hljs-keyword">DATABASES;    #查看当前的数据库, 我们的数据库为employees<br>+<span class="hljs-comment">--------------------+<br>| Database           |<br>+<span class="hljs-comment">--------------------+<br>| information_schema |<br>| employees          |<br>| mysql              |<br>| test               |<br>+<span class="hljs-comment">--------------------+<br>4 rows in <span class="hljs-operator"><span class="hljs-keyword">set (<span class="hljs-number">0.00 sec)<br><br>mysql> <span class="hljs-keyword">USE employees;<br>Database changed<br>mysql> <span class="hljs-operator"><span class="hljs-keyword">SHOW <span class="hljs-keyword">TABLES;         #查看当前库中的表<br>+<span class="hljs-comment">---------------------+<br>| Tables_in_employees |<br>+<span class="hljs-comment">---------------------+<br>| departments         |<br>| dept_emp            |<br>| dept_manager        |<br>| employees           |<br>| salaries            |<br>| titles              |<br>+<span class="hljs-comment">---------------------+<br>6 rows in <span class="hljs-operator"><span class="hljs-keyword">set (<span class="hljs-number">0.00 sec)<br><br>mysql> <span class="hljs-keyword">SELECT <span class="hljs-keyword">COUNT(*) <span class="hljs-keyword">FROM employees;   #由于篇幅原因, 我们这里只看一下employees的行数为300024<br>+<span class="hljs-comment">----------+<br>| COUNT(*) |<br>+<span class="hljs-comment">----------+<br>|   300024 |<br>+<span class="hljs-comment">----------+<br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set (<span class="hljs-number">0.05 sec)<br><br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

创建快照卷并备份

  1. <code class="hljs perl">mysql> FLUSH TABLES WITH READ LOCK;     <span class="hljs-comment">#锁定所有表<br>Query OK, <span class="hljs-number">0 rows affected (<span class="hljs-number">0.<span class="hljs-number">00 sec)<br><br>[root<span class="hljs-variable">@node1 lvm_data]<span class="hljs-comment"># lvcreate -L 1G -n mydata-snap -p r -s /dev/mapper/myvg-mydata   #创建快照卷<br>  Logical volume <span class="hljs-string">"mydata-snap" created.<br><br>mysql> UNLOCK TABLES;  <span class="hljs-comment">#解锁所有表<br>Query OK, <span class="hljs-number">0 rows affected (<span class="hljs-number">0.<span class="hljs-number">00 sec)<br><br>[root<span class="hljs-variable">@node1 lvm_data]<span class="hljs-comment"># mkdir /lvm_snap  #创建文件夹<br>[root<span class="hljs-variable">@node1 lvm_data]<span class="hljs-comment"># mount /dev/myvg/mydata-snap /lvm_snap/  #挂载snap<br>mount: block device /dev/mapper/myvg-mydata--snap is <span class="hljs-keyword">write-protected, mounting <span class="hljs-keyword">read-only<br><br>[root<span class="hljs-variable">@node1 lvm_data]<span class="hljs-comment"># cd /lvm_snap/<br>[root<span class="hljs-variable">@node1 lvm_snap]<span class="hljs-comment"># ls<br>employees  ibdata1  ib_logfile<span class="hljs-number">0  ib_logfile1  mysql  mysql-bin.<span class="hljs-number">000001  mysql-bin.<span class="hljs-number">000002  mysql-bin.<span class="hljs-number">000003  mysql-bin.<span class="hljs-keyword">index  test<br>[root<span class="hljs-variable">@node1 lvm_snap]<span class="hljs-comment"># tar cf /tmp/mysqlback.tar *  #打包文件到/tmp/mysqlback.tar<br><br>[root<span class="hljs-variable">@node1 ~]<span class="hljs-comment"># umount /lvm_snap/  #卸载snap<br>[root<span class="hljs-variable">@node1 ~]<span class="hljs-comment"># lvremove myvg mydata-snap  #删除snap<br><br><br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

恢复数据

  1. <code class="language-sql hljs">[root@node1 lvm_snap]# rm -rf /lvm_data<span class="hljs-comment">/*<br>[root@node1 ~]# service mysqld start    #启动MySQL, 如果是编译安装的应该不能启动(需重新初始化), 如果rpm安装则会重新初始化数据库<br><br><br>mysql> SHOW DATABASES;   #查看数据库, 数据丢失!<br>+--------------------+<br>| Database           |<br>+--------------------+<br>| information_schema |<br>| mysql              |<br>| test               |<br>+--------------------+<br>3 rows in set (0.00 sec)<br><br>[root@node1 ~]# cd /lvm_data/<br>[root@node1 lvm_data]# rm -rf * #删除所有文件<br>[root@node1 lvm_data]# tar xf /tmp/mysqlback.tar     #解压备份数据库到此文件夹 <br>[root@node1 lvm_data]# ls  #查看当前的文件<br>employees  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.index  test<br><br>mysql> SHOW DATABASES;  #数据恢复了<br>+--------------------+<br>| Database           |<br>+--------------------+<br>| information_schema |<br>| employees          |<br>| mysql              |<br>| test               |<br>+--------------------+<br>4 rows in set (0.00 sec)<br><br><br>##完成<br></span></code>

使用Xtrabackup备份

为了更好地演示, 我们这次使用mariadb-5.5的版本, 使用xtrabackup使用InnoDB能够发挥其最大功效, 并且InnoDB的每一张表必须使用单独的表空间, 我们需要在配置文件中添加 innodb_file_per_table = ON 来开启

下载安装xtrabackup

  1. <code class="hljs cpp">我们这里通过wget percona官方的rpm包进行安装<br>[root@node1 ~]<span class="hljs-preprocessor"># wget https:<span class="hljs-comment">//www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.4/binary/redhat/6/x86_64/percona-xtrabackup-2.3.4-1.el6.x86_64.rpm   <br>[root@node1 ~]<span class="hljs-preprocessor"># yum localinstall percona-xtrabackup-<span class="hljs-number">2.3<span class="hljs-number">.4-<span class="hljs-number">1.el6.x86_64.rpm   #需要EPEL源<br></span></span></span></span></span></span></code>

xtrabackup介绍

Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：

  1. 备份过程快速、可靠；

  2. 备份过程不会打断正在执行的事务；

  3. 能够基于压缩等功能节约磁盘空间和流量；

  4. 自动实现备份检验；

  5. 还原速度快；

摘自马哥的文档

xtrabackup实现完全备份

我们这里使用xtrabackup的前端配置工具innobackupex来实现对数据库的完全备份

使用innobackupex备份时, 会调用xtrabackup备份所有的InnoDB表, 复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件, 同时还会备份触发器和数据库配置文件信息相关的文件, 这些文件会被保存至一个以时间命名的目录.

备份过程

  1. <code class="hljs coffeescript">[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># mkdir /extrabackup  #创建备份目录<br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># innobackupex --user=root /extrabackup/ #备份数据<br><span class="hljs-comment">######<span class="hljs-comment">######<span class="hljs-comment">######<span class="hljs-comment">#提示complete表示成功*********************<br><br>[root<span class="hljs-property">@node1 ~]<span class="hljs-comment"># ls /extrabackup/  #看到备份目录<br><span class="hljs-number">2016-<span class="hljs-number">04-<span class="hljs-number">27_07-<span class="hljs-number">30-<span class="hljs-number">48 <br><br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

一般情况, 备份完成后, 数据不能用于恢复操作, 因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此, 此时的数据文件仍不一致, 所以我们需要”准备”一个完全备份

  1. <code class="hljs cpp">[root@node1 ~]<span class="hljs-preprocessor"># innobackupex --apply-log /extrabackup/<span class="hljs-number">2016-<span class="hljs-number">04-<span class="hljs-number">27_07-<span class="hljs-number">30-<span class="hljs-number">48/  #指定备份文件的目录<br><br><span class="hljs-preprocessor">#一般情况下下面三行结尾代表成功*****************<br>InnoDB: Starting shutdown...<br>InnoDB: Shutdown completed; <span class="hljs-built_in">log sequence number <span class="hljs-number">369661462<br><span class="hljs-number">160427 <span class="hljs-number">07:<span class="hljs-number">40:<span class="hljs-number">11 completed OK!<br><br>[root@node1 ~]<span class="hljs-preprocessor"># cd /extrabackup/<span class="hljs-number">2016-<span class="hljs-number">04-<span class="hljs-number">27_07-<span class="hljs-number">30-<span class="hljs-number">48/<br>[root@node1 <span class="hljs-number">2016-<span class="hljs-number">04-<span class="hljs-number">27_07-<span class="hljs-number">30-<span class="hljs-number">48]<span class="hljs-preprocessor"># ls -hl  #查看备份文件<br>total <span class="hljs-number">31M<br>-rw-r----- <span class="hljs-number">1 root root  <span class="hljs-number">386 Apr <span class="hljs-number">27 <span class="hljs-number">07:<span class="hljs-number">30 backup-my.cnf<br>drwx------ <span class="hljs-number">2 root root <span class="hljs-number">4.0K Apr <span class="hljs-number">27 <span class="hljs-number">07:<span class="hljs-number">30 employees<br>-rw-r----- <span class="hljs-number">1 root root  <span class="hljs-number">18M Apr <span class="hljs-number">27 <span class="hljs-number">07:<span class="hljs-number">40 ibdata1<br>-rw-r--r-- <span class="hljs-number">1 root root <span class="hljs-number">5.0M Apr <span class="hljs-number">27 <span class="hljs-number">07:<span class="hljs-number">40 ib_logfile0<br>-rw-r--r-- <span class="hljs-number">1 root root <span class="hljs-number">5.0M Apr <span class="hljs-number">27 <span class="hljs-number">07:<span class="hljs-number">40 ib_logfile1<br>drwx------ <span class="hljs-number">2 root root <span class="hljs-number">4.0K Apr <span class="hljs-number">27 <span class="hljs-number">07:<span class="hljs-number">30 mysql<br>drwx------ <span class="hljs-number">2 root root <span class="hljs-number">4.0K Apr <span class="hljs-number">27 <span class="hljs-number">07:<span class="hljs-number">30 performance_schema<br>drwx------ <span class="hljs-number">2 root root <span class="hljs-number">4.0K Apr <span class="hljs-number">27 <span class="hljs-number">07:<span class="hljs-number">30 test<br>-rw-r----- <span class="hljs-number">1 root root   <span class="hljs-number">27 Apr <span class="hljs-number">27 <span class="hljs-number">07:<span class="hljs-number">30 xtrabackup_binlog_info<br>-rw-r--r-- <span class="hljs-number">1 root root   <span class="hljs-number">29 Apr <span class="hljs-number">27 <span class="hljs-number">07:<span class="hljs-number">40 xtrabackup_binlog_pos_innodb<br>-rw-r----- <span class="hljs-number">1 root root  <span class="hljs-number">117 Apr <span class="hljs-number">27 <span class="hljs-number">07:<span class="hljs-number">40 xtrabackup_checkpoints<br>-rw-r----- <span class="hljs-number">1 root root  <span class="hljs-number">470 Apr <span class="hljs-number">27 <span class="hljs-number">07:<span class="hljs-number">30 xtrabackup_info<br>-rw-r----- <span class="hljs-number">1 root root <span class="hljs-number">2.0M Apr <span class="hljs-number">27 <span class="hljs-number">07:<span class="hljs-number">40 xtrabackup_logfile<br><br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

恢复数据

  1. <code class="hljs gherkin">[root<span class="hljs-comment">@node1 ~]# rm -rf /data/*   #删除数据文件<br><br><span class="hljs-keyword">*<span class="hljs-keyword">*<span class="hljs-keyword">*不用启动数据库也可以还原<span class="hljs-keyword">*<span class="hljs-keyword">*<span class="hljs-keyword">*<span class="hljs-keyword">*<span class="hljs-keyword">*<span class="hljs-keyword">*<span class="hljs-keyword">*<span class="hljs-keyword">*<span class="hljs-keyword">*<span class="hljs-keyword">*<span class="hljs-keyword">*<span class="hljs-keyword">*<span class="hljs-keyword">*<br><br>[root<span class="hljs-comment">@node1 ~]# innobackupex --copy-back /extrabackup/2016-04-27_07-30-48/   #恢复数据, 记清使用方法<br><br><span class="hljs-comment">#########我们这里是编译安装的mariadb所以需要做一些操作##########<br>[root<span class="hljs-comment">@node1 data]# killall mysqld<br><br>[root<span class="hljs-comment">@node1 ~]# chown -R mysql:mysql ./* <br>[root<span class="hljs-comment">@node1 ~]# ll /data/      #数据恢复<br>total 28704<br>-rw-rw---- 1 mysql mysql    16384 Apr 27 07:43 aria_log.00000001<br>-rw-rw---- 1 mysql mysql       52 Apr 27 07:43 aria_log_control<br>-rw-rw---- 1 mysql mysql 18874368 Apr 27 07:43 ibdata1<br>-rw-rw---- 1 mysql mysql  5242880 Apr 27 07:43 ib_logfile0<br>-rw-rw---- 1 mysql mysql  5242880 Apr 27 07:43 ib_logfile1<br>-rw-rw---- 1 mysql mysql      264 Apr 27 07:43 mysql-bin.000001<br>-rw-rw---- 1 mysql mysql       19 Apr 27 07:43 mysql-bin.index<br>-rw-r----- 1 mysql mysql     2166 Apr 27 07:43 node1.anyisalin.com.err<br><br><br>[root<span class="hljs-comment">@node1 data]# service mysqld restart<br>MySQL server PID file could not be found!                  [FAILED]<br>Starting MySQL..                                           [  OK  ]<br><br>MariaDB [(none)]> SHOW DATABASES;  <span class="hljs-comment">#查看数据库, 已经恢复<br>+--------------------+<br>|<span class="hljs-string"> Database           |<br>+--------------------+<br>|<span class="hljs-string"> information_schema |<br>|<span class="hljs-string"> employees          |<br>|<span class="hljs-string"> mysql              |<br>|<span class="hljs-string"> performance_schema |<br>|<span class="hljs-string"> test               |<br>+--------------------+<br>5 rows in set (0.00 sec<br><br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

增量备份

  1. <code class="hljs applescript"><span class="hljs-comment">#########创建连两个数据库以供测试#####################<br>MariaDB [(none)]> CREATE DATABASE TEST1;<br>Query OK, <span class="hljs-number">1 row affected (<span class="hljs-number">0.00 sec)<br><br>MariaDB [(none)]> CREATE DATABASE TEST2;<br>Query OK, <span class="hljs-number">1 row affected (<span class="hljs-number">0.00 sec)<br><br>[root@node1 ~]<span class="hljs-comment"># innobackupex --incremental /extrabackup/ --incremental-basedir=/extrabackup/2016-04-27_07-30-48/ <br><br>[root@node1 ~]<span class="hljs-comment"># ls /extrabackup/2016-04-27_07-57-22/ #查看备份文件<br>total <span class="hljs-number">96<br>-rw-r<span class="hljs-comment">----- 1 root root   386 Apr 27 07:57 backup-my.cnf<br>drwx<span class="hljs-comment">------ 2 root root  4096 Apr 27 07:57 employees<br>-rw-r<span class="hljs-comment">----- 1 root root 49152 Apr 27 07:57 ibdata1.delta<br>-rw-r<span class="hljs-comment">----- 1 root root    44 Apr 27 07:57 ibdata1.meta<br>drwx<span class="hljs-comment">------ 2 root root  4096 Apr 27 07:57 mysql<br>drwx<span class="hljs-comment">------ 2 root root  4096 Apr 27 07:57 performance_schema<br>drwx<span class="hljs-comment">------ 2 root root  4096 Apr 27 07:57 test<br>drwx<span class="hljs-comment">------ 2 root root  4096 Apr 27 07:57 TEST1<br>drwx<span class="hljs-comment">------ 2 root root  4096 Apr 27 07:57 TEST2<br>-rw-r<span class="hljs-comment">----- 1 root root    21 Apr 27 07:57 xtrabackup_binlog_info<br>-rw-r<span class="hljs-comment">----- 1 root root   123 Apr 27 07:57 xtrabackup_checkpoints<br>-rw-r<span class="hljs-comment">----- 1 root root   530 Apr 27 07:57 xtrabackup_info<br>-rw-r<span class="hljs-comment">----- 1 root root  2560 Apr 27 07:57 xtrabackup_logfile<br><br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

BASEDIR指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/extrabackup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外，在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向上一次的增量备份所在的目录。

需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

整理增量备份

  1. <code class="hljs cpp">[root@node1 ~]<span class="hljs-preprocessor"># innobackupex --apply-log --redo-only /extrabackup/<span class="hljs-number">2016-<span class="hljs-number">04-<span class="hljs-number">27_07-<span class="hljs-number">30-<span class="hljs-number">48/<br>[root@node1 ~]<span class="hljs-preprocessor"># innobackupex --apply-log --redo-only /extrabackup/<span class="hljs-number">2016-<span class="hljs-number">04-<span class="hljs-number">27_07-<span class="hljs-number">30-<span class="hljs-number">48/ --incremental-dir=/extrabackup/<span class="hljs-number">2016-<span class="hljs-number">04-<span class="hljs-number">27_07-<span class="hljs-number">5<br><span class="hljs-number">7-<span class="hljs-number">22/<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

恢复数据

  1. <code class="hljs haskell">[root@node1 ~]# rm -rf /<span class="hljs-typedef"><span class="hljs-keyword">data/*   #删除数据<br><br>[root@node1 ~]# innobackupex <span class="hljs-comment">--copy-back /extrabackup/2016-04-27_07-30-48/     #整理增量备份之后可以直接通过全量备份还原<br><br>[root@node1 ~]# chown -<span class="hljs-type">R mysql.mysql /<span class="hljs-typedef"><span class="hljs-keyword">data/<br>[root@node1 ~]# ls /<span class="hljs-typedef"><span class="hljs-keyword">data/ -l<br><span class="hljs-title">total <span class="hljs-number">28732<br>-rw-rw<span class="hljs-comment">---- 1 mysql mysql     8192 Apr 27 08:05 aria_log.00000001<br>-rw-rw<span class="hljs-comment">---- 1 mysql mysql       52 Apr 27 08:05 aria_log_control<br><span class="hljs-title">drwx<span class="hljs-comment">------ 2 mysql mysql     4096 Apr 27 08:05 employees<br>-rw-r<span class="hljs-comment">----- 1 mysql mysql 18874368 Apr 27 08:05 ibdata1<br>-rw-r<span class="hljs-comment">----- 1 mysql mysql  5242880 Apr 27 08:05 ib_logfile0<br>-rw-r<span class="hljs-comment">----- 1 mysql mysql  5242880 Apr 27 08:05 ib_logfile1<br><span class="hljs-title">drwx<span class="hljs-comment">------ 2 mysql mysql     4096 Apr 27 08:05 mysql<br>-rw-rw<span class="hljs-comment">---- 1 mysql mysql      245 Apr 27 08:05 mysql-bin.000001<br>-rw-rw<span class="hljs-comment">---- 1 mysql mysql       19 Apr 27 08:05 mysql-bin.index<br>-rw-r<span class="hljs-comment">----- 1 mysql mysql     1812 Apr 27 08:05 node1.anyisalin.com.err<br>-rw-rw<span class="hljs-comment">---- 1 mysql mysql        5 Apr 27 08:05 node1.anyisalin.com.pid<br><span class="hljs-title">drwx<span class="hljs-comment">------ 2 mysql mysql     4096 Apr 27 08:05 performance_schema<br><span class="hljs-title">drwx<span class="hljs-comment">------ 2 mysql mysql     4096 Apr 27 08:05 test<br><span class="hljs-title">drwx<span class="hljs-comment">------ 2 mysql mysql     4096 Apr 27 08:05 TEST1<br><span class="hljs-title">drwx<span class="hljs-comment">------ 2 mysql mysql     4096 Apr 27 08:05 TEST2<br>-rw-r<span class="hljs-comment">----- 1 mysql mysql       29 Apr 27 08:05 xtrabackup_binlog_pos_innodb<br>-rw-r<span class="hljs-comment">----- 1 mysql mysql      530 Apr 27 08:05 xtrabackup_info<br><br><span class="hljs-type">MariaDB [(none)]> <span class="hljs-type">SHOW <span class="hljs-type">DATABASES;  #数据还原<br>+<span class="hljs-comment">--------------------+<br>| <span class="hljs-type">Database           |<br>+<span class="hljs-comment">--------------------+<br>| information_schema |<br>| <span class="hljs-type">TEST1              |<br>| <span class="hljs-type">TEST2              |<br>| employees          |<br>| mysql              |<br>| performance_schema |<br>| test               |<br>+<span class="hljs-comment">--------------------+<br><span class="hljs-number">7 rows <span class="hljs-keyword">in set (<span class="hljs-number">0.00 sec)<br><br><span class="hljs-preprocessor">#关于xtrabackup还有很多强大的功能没有叙述、有兴趣可以去看官方文档<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

总结

备份方法备份速度恢复速度便捷性功能一般用于
cp快快一般、灵活性低很弱少量数据备份
mysqldump慢慢一般、可无视存储引擎的差异一般中小型数据量的备份
lvm2快照快快一般、支持几乎热备、速度快一般中小型数据量的备份
xtrabackup较快较快实现innodb热备、对存储引擎有要求强大较大规模的备份

其实我们还可以通过Master-Slave Replication 进行备份。

转自：http://www.cnblogs.com/liangshaoye/p/5464794.html

学会4种备份MySQL数据库（基本备份方面没问题了）

标签：pad   for   enter   事务   完成后   editor   restart   设计   文件系统管理   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！