备份MySQL数据库的各种方法介绍

不知道大家之前对类似备份MySQL数据库的各种方法的文章有无了解，今天我在这里给大家再简单的讲讲。感兴趣的话就一起来看看正文部分吧，相信看完备份MySQL数据库的各种方法你一定会有所收获的。 

查看数据库的信息

mysql> SHOW DATABASES;    #查看当前的数据库, 我们的数据库为employees
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> USE employees;
Database changed
mysql> SHOW TABLES;         #查看当前库中的表
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM employees;   #由于篇幅原因, 我们这里只看一下employees的行数为300024
+----------+
| COUNT(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.05 sec)

向数据库施加读锁

mysql> FLUSH TABLES WITH READ LOCK;    #向所有表施加读锁
Query OK, 0 rows affected (0.00 sec)

备份数据文件

[root@node1 ~]# mkdir /backup   #创建文件夹存放备份数据库文件
[root@node1 ~]# cp -a /var/lib/mysql/* /backup     #保留权限的拷贝源数据文件
[root@node1 ~]# ls /backup   #查看目录下的文件
employees  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.sock  test

模拟数据丢失并恢复

[root@node1 ~]# rm -rf /var/lib/mysql/*    #删除数据库的所有文件
[root@node1 ~]# service mysqld restart   #重启MySQL, 如果是编译安装的应该不能启动, 如果rpm安装则会重新初始化数据库

mysql> SHOW DATABASES;    #因为我们是rpm安装的, 连接到MySQL进行查看, 发现数据丢失了！
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

[root@node1 ~]# rm -rf /var/lib/mysql/*    #这一步可以不做
[root@node1 ~]# cp -a /backup/* /var/lib/mysql/    #将备份的数据文件拷贝回去
[root@node1 ~]# service mysqld restart  #重启MySQL

#重新连接数据并查看

mysql> SHOW DATABASES;    #数据库已恢复
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> USE employees;      

mysql> SELECT COUNT(*) FROM employees;    #表的行数没有变化
+----------+
| COUNT(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.06 sec)

##完成

使用mysqldump+复制BINARY LOG备份

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

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

mysqldump命令介绍

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

官方文档介绍

#基本语法格式

shell> mysqldump [options] db_name [tbl_name ...]    恢复需要手动CRATE DATABASES
shell> mysqldump [options] --databases db_name ...   恢复不需要手动创建数据库
shell> mysqldump [options] --all-databases           恢复不需要手动创建数据库

其他选项:
     -E, --events: 备份事件调度器
     -R, --routines: 备份存储过程和存储函数
     --triggers: 备份表的触发器; --skip-triggers 
     --master-date[=value]  
         1: 记录为CHANGE MASTER TO 语句、语句不被注释
         2: 记录为注释的CHANGE MASTER TO语句
         基于二进制还原只能全库还原

     --flush-logs: 日志滚动
         锁定表完成后执行日志滚动

查看数据库的信息

mysql> SHOW DATABASES;    #查看当前的数据库, 我们的数据库为employees
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> USE employees;
Database changed
mysql> SHOW TABLES;         #查看当前库中的表
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM employees;   #由于篇幅原因, 我们这里只看一下employees的行数为300024
+----------+
| COUNT(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.05 sec)

使用mysqldump备份数据库

[root@node1 ~]# mysql -e 'SHOW MASTER STATUS'   #查看当前二进制文件的状态, 并记录下position的数字
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      106 |              |                  |
+------------------+----------+--------------+------------------+

[root@node1 ~]# mysqldump --all-databases --lock-all-tables  > backup.sql   #备份数据库到backup.sql文件中

mysql> CREATE DATABASE TEST1;   #创建一个数据库
Query OK, 1 row affected (0.00 sec)

mysql> SHOW MASTER STATUS;   #记下现在的position
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      191 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

[root@node1 ~]# cp /var/lib/mysql/mysql-bin.000003 /root  #备份二进制文件
[root@node1 ~]# service mysqld stop   #停止MySQL
[root@node1 ~]# rm -rf /var/lib/mysql/*   #删除所有的数据文件
[root@node1 ~]# service mysqld start    #启动MySQL, 如果是编译安装的应该不能启动(需重新初始化), 如果rpm安装则会重新初始化数据库


mysql> SHOW DATABASES;   #查看数据库, 数据丢失!
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> SET sql_log_bin=OFF;   #暂时先将二进制日志关闭  
Query OK, 0 rows affected (0.00 sec)


mysql> source backup.sql  #恢复数据，所需时间根据数据库时间大小而定

mysql> SET sql_log_bin=ON; 开启二进制日志

mysql> SHOW DATABASES;   #数据库恢复, 但是缺少TEST1
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

[root@node1 ~]# mysqlbinlog --start-position=106 --stop-position=191 mysql-bin.000003 | mysql employees #通过二进制日志增量恢复数据

mysql> SHOW DATABASES;    #现在TEST1出现了！
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TEST1              |
| employees          |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)

#完成

使用lvm2快照备份数据

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

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

部署lvm环境

添加硬盘; 这里我们直接实现SCSI硬盘的热插拔, 首先在虚拟机中添加一块硬盘, 不重启

[root@node1 ~]# ls /dev/sd*   #只有以下几块硬盘, 但是我们不重启可以让系统识别新添加的硬盘
/dev/sda  /dev/sda1  /dev/sda2

[root@node1 ~]# echo '- - -' > /sys/class/scsi_host/host0/scan 
[root@node1 ~]# echo '- - -' > /sys/class/scsi_host/host1/scan 
[root@node1 ~]# echo '- - -' > /sys/class/scsi_host/host2/scan 

[root@node1 ~]# ls /dev/sd*    #看！sdb识别出来了
/dev/sda  /dev/sda1  /dev/sda2  /dev/sdb

[root@node1 ~]# fdisk /dev/sdb   #分区
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xd353d192.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-2610, default 1): 
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-2610, default 2610): +15G

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
You have new mail in /var/spool/mail/root
[root@node1 ~]# partx -a /dev/sdb
BLKPG: Device or resource busy
error adding partition 1

##创建逻辑卷
[root@node1 ~]# pvcreate /dev/sdb1
  Physical volume "/dev/sdb1" successfully created
[root@node1 ~]# vgcreate myvg /dev/sdb1 
  Volume group "myvg" successfully created
[root@node1 ~]# lvcreate -n mydata -L 5G myvg 
  Logical volume "mydata" created.

[root@node1 ~]# mkfs.ext4 /dev/mapper/myvg-mydata   #格式化
[root@node1 ~]# mkdir /lvm_data
[root@node1 ~]# mount /dev/mapper/myvg-mydata /lvm_data  #挂载到/lvm_data


[root@node1 ~]# vim /etc/my.cnf    #修改mysql配置文件的datadir如下

datadir=/lvm_data

[root@node1 ~]# service mysqld restart  #重启MySQL

####重新导入employees数据库########略过####

查看数据库的信息

mysql> SHOW DATABASES;    #查看当前的数据库, 我们的数据库为employees
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> USE employees;
Database changed
mysql> SHOW TABLES;         #查看当前库中的表
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM employees;   #由于篇幅原因, 我们这里只看一下employees的行数为300024
+----------+
| COUNT(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.05 sec)

创建快照卷并备份

mysql> FLUSH TABLES WITH READ LOCK;     #锁定所有表
Query OK, 0 rows affected (0.00 sec)

[root@node1 lvm_data]# lvcreate -L 1G -n mydata-snap -p r -s /dev/mapper/myvg-mydata   #创建快照卷
  Logical volume "mydata-snap" created.

mysql> UNLOCK TABLES;  #解锁所有表
Query OK, 0 rows affected (0.00 sec)

[root@node1 lvm_data]# mkdir /lvm_snap  #创建文件夹
[root@node1 lvm_data]# mount /dev/myvg/mydata-snap /lvm_snap/  #挂载snap
mount: block device /dev/mapper/myvg-mydata--snap is write-protected, mounting read-only

[root@node1 lvm_data]# cd /lvm_snap/
[root@node1 lvm_snap]# ls
employees  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.index  test
[root@node1 lvm_snap]# tar cf /tmp/mysqlback.tar *  #打包文件到/tmp/mysqlback.tar

[root@node1 ~]# umount /lvm_snap/  #卸载snap
[root@node1 ~]# lvremove myvg mydata-snap  #删除snap

恢复数据

[root@node1 lvm_snap]# rm -rf /lvm_data/*
[root@node1 ~]# service mysqld start    #启动MySQL, 如果是编译安装的应该不能启动(需重新初始化), 如果rpm安装则会重新初始化数据库

mysql> SHOW DATABASES;   #查看数据库, 数据丢失!
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

[root@node1 ~]# cd /lvm_data/
[root@node1 lvm_data]# rm -rf * #删除所有文件
[root@node1 lvm_data]# tar xf /tmp/mysqlback.tar     #解压备份数据库到此文件夹 
[root@node1 lvm_data]# ls  #查看当前的文件
employees  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.index  test

mysql> SHOW DATABASES;  #数据恢复了
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

##完成

使用Xtrabackup备份

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

下载安装xtrabackup

我们这里通过wget percona官方的rpm包进行安装
[root@node1 ~]# wget https://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   
[root@node1 ~]# yum localinstall percona-xtrabackup-2.3.4-1.el6.x86_64.rpm   #需要EPEL源

xtrabackup介绍

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

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

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

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

4. 自动实现备份检验；

5. 还原速度快；

摘自马哥的文档

xtrabackup实现完全备份

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

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

备份过程

[root@node1 ~]# mkdir /extrabackup  #创建备份目录
[root@node1 ~]# innobackupex --user=root /extrabackup/ #备份数据
###################提示complete表示成功*********************

[root@node1 ~]# ls /extrabackup/  #看到备份目录
2016-04-27_07-30-48 

[root@node1 ~]# innobackupex --apply-log /extrabackup/2016-04-27_07-30-48/  #指定备份文件的目录

#一般情况下下面三行结尾代表成功*****************
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 369661462
160427 07:40:11 completed OK!

[root@node1 ~]# cd /extrabackup/2016-04-27_07-30-48/
[root@node1 2016-04-27_07-30-48]# ls -hl  #查看备份文件
total 31M
-rw-r----- 1 root root  386 Apr 27 07:30 backup-my.cnf
drwx------ 2 root root 4.0K Apr 27 07:30 employees
-rw-r----- 1 root root  18M Apr 27 07:40 ibdata1
-rw-r--r-- 1 root root 5.0M Apr 27 07:40 ib_logfile0
-rw-r--r-- 1 root root 5.0M Apr 27 07:40 ib_logfile1
drwx------ 2 root root 4.0K Apr 27 07:30 mysql
drwx------ 2 root root 4.0K Apr 27 07:30 performance_schema
drwx------ 2 root root 4.0K Apr 27 07:30 test
-rw-r----- 1 root root   27 Apr 27 07:30 xtrabackup_binlog_info
-rw-r--r-- 1 root root   29 Apr 27 07:40 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root  117 Apr 27 07:40 xtrabackup_checkpoints
-rw-r----- 1 root root  470 Apr 27 07:30 xtrabackup_info
-rw-r----- 1 root root 2.0M Apr 27 07:40 xtrabackup_logfile

恢复数据

[root@node1 ~]# rm -rf /data/*   #删除数据文件

***不用启动数据库也可以还原*************

[root@node1 ~]# innobackupex --copy-back /extrabackup/2016-04-27_07-30-48/   #恢复数据, 记清使用方法

#########我们这里是编译安装的mariadb所以需要做一些操作##########
[root@node1 data]# killall mysqld

[root@node1 ~]# chown -R mysql:mysql ./* 
[root@node1 ~]# ll /data/      #数据恢复
total 28704
-rw-rw---- 1 mysql mysql    16384 Apr 27 07:43 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Apr 27 07:43 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Apr 27 07:43 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Apr 27 07:43 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Apr 27 07:43 ib_logfile1
-rw-rw---- 1 mysql mysql      264 Apr 27 07:43 mysql-bin.000001
-rw-rw---- 1 mysql mysql       19 Apr 27 07:43 mysql-bin.index
-rw-r----- 1 mysql mysql     2166 Apr 27 07:43 node1.anyisalin.com.err

[root@node1 data]# service mysqld restart
MySQL server PID file could not be found!                  [FAILED]
Starting MySQL..                                           [  OK  ]

MariaDB [(none)]> SHOW DATABASES;  #查看数据库, 已经恢复
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec

增量备份

#########创建连两个数据库以供测试#####################
MariaDB [(none)]> CREATE DATABASE TEST1;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> CREATE DATABASE TEST2;
Query OK, 1 row affected (0.00 sec)

[root@node1 ~]# innobackupex --incremental /extrabackup/ --incremental-basedir=/extrabackup/2016-04-27_07-30-48/ 

[root@node1 ~]# ls /extrabackup/2016-04-27_07-57-22/ #查看备份文件
total 96
-rw-r----- 1 root root   386 Apr 27 07:57 backup-my.cnf
drwx------ 2 root root  4096 Apr 27 07:57 employees
-rw-r----- 1 root root 49152 Apr 27 07:57 ibdata1.delta
-rw-r----- 1 root root    44 Apr 27 07:57 ibdata1.meta
drwx------ 2 root root  4096 Apr 27 07:57 mysql
drwx------ 2 root root  4096 Apr 27 07:57 performance_schema
drwx------ 2 root root  4096 Apr 27 07:57 test
drwx------ 2 root root  4096 Apr 27 07:57 TEST1
drwx------ 2 root root  4096 Apr 27 07:57 TEST2
-rw-r----- 1 root root    21 Apr 27 07:57 xtrabackup_binlog_info
-rw-r----- 1 root root   123 Apr 27 07:57 xtrabackup_checkpoints
-rw-r----- 1 root root   530 Apr 27 07:57 xtrabackup_info
-rw-r----- 1 root root  2560 Apr 27 07:57 xtrabackup_logfile

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

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

整理增量备份

[root@node1 ~]# innobackupex --apply-log --redo-only /extrabackup/2016-04-27_07-30-48/
[root@node1 ~]# innobackupex --apply-log --redo-only /extrabackup/2016-04-27_07-30-48/ --incremental-dir=/extrabackup/2016-04-27_07-5
7-22/

恢复数据

[root@node1 ~]# rm -rf /data/*   #删除数据

[root@node1 ~]# innobackupex --copy-back /extrabackup/2016-04-27_07-30-48/     #整理增量备份之后可以直接通过全量备份还原

[root@node1 ~]# chown -R mysql.mysql /data/
[root@node1 ~]# ls /data/ -l
total 28732
-rw-rw---- 1 mysql mysql     8192 Apr 27 08:05 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Apr 27 08:05 aria_log_control
drwx------ 2 mysql mysql     4096 Apr 27 08:05 employees
-rw-r----- 1 mysql mysql 18874368 Apr 27 08:05 ibdata1
-rw-r----- 1 mysql mysql  5242880 Apr 27 08:05 ib_logfile0
-rw-r----- 1 mysql mysql  5242880 Apr 27 08:05 ib_logfile1
drwx------ 2 mysql mysql     4096 Apr 27 08:05 mysql
-rw-rw---- 1 mysql mysql      245 Apr 27 08:05 mysql-bin.000001
-rw-rw---- 1 mysql mysql       19 Apr 27 08:05 mysql-bin.index
-rw-r----- 1 mysql mysql     1812 Apr 27 08:05 node1.anyisalin.com.err
-rw-rw---- 1 mysql mysql        5 Apr 27 08:05 node1.anyisalin.com.pid
drwx------ 2 mysql mysql     4096 Apr 27 08:05 performance_schema
drwx------ 2 mysql mysql     4096 Apr 27 08:05 test
drwx------ 2 mysql mysql     4096 Apr 27 08:05 TEST1
drwx------ 2 mysql mysql     4096 Apr 27 08:05 TEST2
-rw-r----- 1 mysql mysql       29 Apr 27 08:05 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql      530 Apr 27 08:05 xtrabackup_info

MariaDB [(none)]> SHOW DATABASES;  #数据还原
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TEST1              |
| TEST2              |
| employees          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)

#关于xtrabackup还有很多强大的功能没有叙述、有兴趣可以去看官方文档

总结

看完备份MySQL数据库的各种方法介绍这篇文章，大家觉得怎么样？如果想要了解更多相关，可以继续关注我们的行业资讯板块。