MySQL之备份与恢复

    MySQL备份类型：

1 根据备份时，服务器是否在线，分为：

1）热备——服务器在线，并且读写不受影响。

2）温备——服务器在线，但需要锁表，并且可读不可写。

3）冷备——服务器下线，读写中止。

2 按备份方式，分为：

物理备份——复制数据文件，特点是备份和恢复速度比较快。占用空间较大，适合于大数据备份。

逻辑备份——将数据导出到文件中，速度较慢，可能失去浮点数精度，适合于数据量较小的场景。

逻辑备份最大的优点是对于各种存储引擎都可以采用同样的方式来备份。而物理备份则不同，不同的存储引擎有不同的备份方法，因此，对于不同存储引擎混合的数据库，用逻辑备份会理简单一些。

    MySQL的备份工具:

1 mysqldump——逻辑备份工具，温备，对innoDB可以实现热备。

选项：

--master-data=0|1|2

0：不记录二进制文件及其位置

1：以change master to 的方式记录位置，可用于恢复后直接启动从服务器

2：以change master to 的方式记录位置，但默认为被注释

-x, --lock-all-tables：加上此参数，即备份之前锁定所有表

-F，--flush-logs：刷新二进制日志

--databases：备份指定库和表

-A, --all-databases：备份所有库和表

1）备份和恢复

导出指定库中的部分表：导出test库中的test01、test02两张表：

[root@localhost ~]# mysqldump -uroot -p test test01 test02 > /root/test_$(date +%F).sql

恢复：恢复表时，由于备份文件中没有建库的语句，必须指明库，如果库不存在，需要先建立数据库

[root@localhost ~]# mysql -uroot -p test < /root/test_2017-05-01.sql 

导出一个或多个数据库：导出test yewu xiaoshou数据库

[root@localhost ~]# mysqldump -uroot -p --databases test yewu xiaoshou > /root/test_yewu_xiaoshou_$(date +%F).sql

恢复：由于备份文件中已包含完整的库信息，因此还原时不需要指定库名

[root@localhost ~]# mysql -uroot -p < test_yewu_xiaoshou_2017-05-01.sql 

备份MySQL数据库中所有的库：

[root@localhost ~]# mysqldump -uroot -p --all-databases > alldatabase_$(date +%F).sql

恢复：

[root@localhost ~]# mysql -uroot -p < alldatabase_2017-05-01.sql 

2）生产环境实例

a 上午10：00备份数据库

[root@localhost ~]# mysqldump -uroot -p --lock-all-tables --flush-logs --master-data=2 --databases test > test_$(date +%F).sql

其中表test01的内容如下：

mysql> select * from test01;

+------+------+

| id   | name |

+------+------+

|    1 | zhan |

|    2 | liso |

|    3 | wang |

|    5 | kang |

+------+------+

b 向表中添加新的数据

mysql> insert into test01 values('18','dan');

Query OK, 1 row affected (2.56 sec)

mysql> select * from test01;

+------+------+

| id   | name |

+------+------+

|    1 | zhan |

|    2 | liso |

|    3 | wang |

|    5 | kang |

|   18 | dan  |

+------+------+

5 rows in set (0.00 sec)

c 下午2：00，数据库中的表被误删，开始恢复备份，首先恢复全备

[root@localhost ~]# mysql -uroot -p test < test_2017-05-01.sql 

Enter password: 

mysql> select * from test01;

+------+------+

| id   | name |

+------+------+

|    1 | zhan |

|    2 | liso |

|    3 | wang |

|    5 | kang |

+------+------+

4 rows in set (0.00 sec)

由以上结果可知，缺少id为18的行

d 使用mysqlbinlog命令恢复自mysqldump备份以来的binlog

查看mysqldump备份时binlog日志的位置

[root@localhost ~]# less test_2017-05-01.sql 

-- MySQL dump 10.13  Distrib 5.6.30, for Linux (i686)

--

-- Host: localhost    Database: test

-- ------------------------------------------------------

-- Server version       5.6.30-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--

-- Position to start replication or point-in-time recovery from

--

-- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000003', MASTER_LOG_POS=120;

--

-- Current Database: `test`

--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `test`;

--

-- Table structure for table `test01`

--

备份二进制日志

[root@localhost ~]#mysqlbinlog localhost-bin.000003 > /root/binlog.sql

删除二进制日志中的错误SQL语句

vim binlog.sql

...

DROP TABLE `test01`

DROP TABLE IF EXISTS `test01`

...

恢复：

mysql -uroot -p  test < backbinlog.sql

查看恢复后的表：

mysql> select * from test01;

+------+------+

| id   | name |

+------+------+

|    1 | zhan |

|    2 | liso |

|    3 | wang |

|    5 | kang |

|   18 | dan  |

+------+------+

5 rows in set (0.00 sec)

2 xtrabackup——是Percona公司参与开发的一款对InnoDB做数据备份的工具，备份方式为物理备份，而且支持热备。

Xtrabackup包含两个工具：即xtrabackup和innobackupex

• xtrabackup只能备份InnoDB和XtraDB两种数据引擎，而不能备份MyISAM引擎的数据表
  

• innobackupex是一个封装了xtrabackup的Perl脚本，支持同时备份InnoDB和MyISAM。

1）安装，需要安装好epel的yum源

[root@localhost ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.8/binary/redhat/6/i386/percona-xtrabackup-2.3.8-1.el6.i686.rpm

[root@localhost ~]# yum -y localinstall percona-xtrabackup-2.3.8-1.el6.i686.rpm 

2）备份和恢复

备份：

a 建立备份账户：

mysql> grant reload,lock tables,replication client on *.* to 'dbbak'@'localhost' identified by 'bk2016' ;

b 完全备份，socket可以不指定，除非和默认不一致

[root@localhost ~]# innobackupex --socket=/tmp/mysqld.sock --user=dbbak --password=bk2016 /root/

...

170501 23:20:35 Backup created in directory '/root/2017-05-01_23-20-25/'

MySQL binlog position: filename 'localhost-bin.000003', position '15651'

170501 23:20:35 [00] Writing backup-my.cnf

170501 23:20:35 [00]        ...done

170501 23:20:35 [00] Writing xtrabackup_info

170501 23:20:35 [00]        ...done

xtrabackup: Transaction log of lsn (2031876) to (2031876) was copied.

170501 23:20:35 completed OK!

[root@localhost ~]# cd 2017-05-01_23-20-25/            #查看备份后的目录

[root@localhost 2017-05-01_23-20-25]# ls

backup-my.cnf  ibdata1  mysql  performance_schema  test  xiaoshou  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile  yewu

备份目录说明：

xtrabackup_checkpoints——备份类型（如完全和增量）、备份状态、LSN（日志序列号）范围信息

xtrabackup_my.cnf——备份命令用到的配置选项信息

xtrabackup_binlog_info——MySQL服务器当前使用的二进制日志文件及到备份这一刻为止二进制日志事件的位置

c 预处理备份文件，准备一个完全备份，可增加内存选项，--use-memory=1G

[root@localhost ~]# innobackupex --apply-log /root/2017-05-01_23-20-25 

d 数据更改之后，通过二进制进行增量备份

[root@localhost 2017-05-01_23-20-25]# cat xtrabackup_binlog_info 

localhost-bin.00000315651

[root@localhost ~]# mysqlbinlog /usr/data/mysql/localhost-bin.000003 > /root/bin$(date +%F).sql

恢复：

a 停止服务

[root@localhost ~]# service mysqld stop

b 恢复全备，MySQL数据目录必须为空，否则会报错

[root@localhost ~]# innobackupex --copy-back --user=dbbak --password=bk2016 /root/2017-05-01_23-20-25/

[root@localhost mysql]# cd /usr/local/mysql/            #修改数据目录的属主属组

[root@localhost mysql]# chown -R mysql.mysql *

c 启动服务

[root@localhost mysql]# service mysqld start

d 通过二进制文件恢复增量备份

mysql> set sql_log_bin=0;                    #暂时关闭二进制日志

Query OK, 0 rows affected (0.03 sec)

mysql> source /root/bin2017-05-01.sql;

...

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> set sql_log_bin=1;                    #开启二进制日志

Query OK, 0 rows affected (0.00 sec)

e 恢复完成后，重新进行一次完全备份

    表的导入和导出

1 导出

方式 1：

[root@localhost ~]# mysql -uroot -p -h 127.0.0.1 -e "select * from test.test01" > /root/mysql.txt

Enter password: 

[root@localhost ~]# ls

2017-05-01_23-20-25  anaconda-ks.cfg  bin2017-05-01.sql  cmake-3.7.1  cmake-3.7.1.tar.gz  install.log  install.log.syslog  mysql-5.6.30  mysql-5.6.30.tar.gz  mysql.txt  percona-xtrabackup-2.3.8-1.el6.i686.rpm

[root@localhost ~]# cat mysql.txt 

idname

1zhan

2liso

3wang

5kang

18dan

方式 2：

mysql> select * from test01 into outfile '/tmp/c.txt';

Query OK, 5 rows affected (0.00 sec)

[root@localhost tmp]# cat /tmp/c.txt 

1zhan

2liso

3wang

5kang

18dan

方式 3：

[root@localhost tmp]# mysqldump -uroot -p -h 127.0.0.1 -T /tmp test test02

Enter password: 

[root@localhost tmp]# ls

a.txt  c.txt  mysqld.sock  percona-version-check  test02.sql  test02.txt  vmware-root  vmware-root-1821705925

[root@localhost tmp]# cat test02.txt

a

b

2 导入

mysql> select * from test03;                #查看导入数据之前的内容

+------+------+

| id   | name |

+------+------+

|   10 | zwj  |

|   10 | zwj  |

+------+------+

2 rows in set (0.00 sec)

mysql> load data infile '/tmp/a.txt' into table test.test03;        #导入数据

Query OK, 5 rows affected (0.13 sec)

Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from test.test03;

+------+------+

| id   | name |

+------+------+

|   10 | zwj  |

|   10 | zwj  |

|    1 | zhan |

|    2 | liso |

|    3 | wang |

|    5 | kang |

|   18 | dan  |

+------+------+

备注：

锁表：

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.03 sec)

释放锁表：

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)