mysql备份还原

一、前言

数据对于一个公司来说有多重要这点不说大家也知道,数据还在,服务崩溃还可重建。为什么要备份?很简单,天灾人祸。比如硬件故障,软件故障,自然灾害,黑客攻击或自身误操作等因素。这时候备份,恢复数据就显得至关重要了。

二、基础概念

由于备份与恢复需要用到一些必要的知识,故在此先讲解下

2.1二进制日志

myql日志有多种,其中二进制日志文件对数据恢复有很大作用。那么什么是二进制日志?二进制日志记录了所有对数据库执行的更改操作,只有对数据能产生更改的操作才会记录下来。

日志格式:

blob.png

2.2备份类型

备份按照不同的划分方式可以划分为多种类型

如 :完全备份、增量备份、差异备份

完全备份:一个或多个数据库实例完全备份下来

增量备份:备份从完全备份或上次增量备份到现在改变了的数据部分

差异备份:备份从完全备份到现在改变了的数据

热备、温备、冷备

热备:在线备份,不需关闭mysql服务,业务不受影响

温备:在线备份,只支持读操作,

冷备:离线备份,需要关闭mysql服务

物理备份、逻辑备份

物理备份:直接复制数据文件进行备份

逻辑备份:从数据库中导出数据另存进行备份

2.3备份对象

数据、配置文件、二进制日志、存储过程、触发器

2.4备份方案

完全备份+增量备份+二进制日志

2.5数据恢复

有大概如下步骤

1、停止MySQL服务器;

2、记录服务器配置和文件权限;

3、将备份恢复到MySQL数据目录;此步骤依赖具体的备份工具;

4、改变配置和文件权限;

5、以限制方式启动MySQL服务器:比如通过网络访问;

[mysqld]

skip-networking 

socket=/tmp/mysql-recovery.sock

6、载入额外的逻辑备份;而检查和重放二进制日志;

7、检查已经还原的数据;

8、以完全访问模式重启服务器;

三、备份与恢复

3.1 mysqldump+二进制日志

备份

mysql> insert into classes(Class,NumOfStu) values('yue jiao','4');    #插入数据
Query OK, 1 row affected (0.03 sec)

mysql> show master logs;                        #查看二进制文件
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |     16274 |
| master-bin.000002 |       120 |
+-------------------+-----------+
2 rows in set (0.00 sec)

[root@vm ~]# mysqldump --databases test -E -R --triggers --master-data=2 > /tmp/test_backup.mysql    #-E表示事件,-R表示存储过程,--triggers表示触发器
[root@vm ~]# ls /tmp/test_backup.mysql -l
-rw-r--r--. 1 root root 8057 Nov 19 19:36 /tmp/test_backup.mysql
[root@vm ~]# cp /etc/my.cnf /tmp/my.cnf.backup                            #备份配置文件
 [root@vm ~]# ls /tmp/my.cnf.backup -l
-rw-r--r--. 1 root root 1171 Nov 19 19:37 /tmp/my.cnf.backup
[root@vm ~]# ls -l /data/mysql/binlogs/ 
total 24
-rw-rw----. 1 mysql mysql 16274 Nov 19 17:51 master-bin.000001
-rw-rw----. 1 mysql mysql   667 Nov 19 19:41 master-bin.000002
-rw-rw----. 1 mysql mysql    76 Nov 19 17:51 master-bin.index
[root@vm ~]# cp /data/mysql/binlogs/master-bin.* /tmp/                #备份二进制日志文件
[root@vm ~]# ls /tmp/master-bin.*
/tmp/master-bin.000001  /tmp/master-bin.000002  /tmp/master-bin.index

还原

[root@vm ~]# cp /tmp/my.cnf.backup /etc/my.cnf        #配置文件还原
skip-networking
[root@vm ~]# service mysqld start
Starting MySQL...... SUCCESS!


[root@vm ~]# mysqlbinlog --start-position=120 --database=test /tmp/master-bin.000002 >/tmp/new_log.sql    #指定从什么position开始查找

mysql> set session sql_log_bin=0;                #临时关闭二进制日志功能,反正把还原过程也写进日志文件
Query OK, 0 rows affected (0.04 sec)
mysql> source /tmp/test_backup.mysql            #执行完全备份的数据文件

mysql> source /tmp/new_log.sql                  #执行增量还原

mysql> select * from classes where Class='mo jiao';
+---------+---------+----------+
| ClassID | Class   | NumOfStu |
+---------+---------+----------+
|      10 | mo jiao |        3 |
+---------+---------+----------+
1 row in set (0.02 sec)

mysql> set sql_log_bin=1;                    #打开二进制文件功能
Query OK, 0 rows affected (0.00 sec)

[root@vm ~]# sed -in /skip-networking/d /etc/my.cnf    #此操作使用户能远程访问数据库

[root@vm ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL...... SUCCESS!

3.2 lvm+二进制日志

这种备份要求mysql的data目录放在逻辑卷组中。

本实验中mysql的data目录放在逻辑卷组lv1中

mysql> show master logs;                #查看二进制文件及position
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |     16274 |
| master-bin.000002 |       690 |
| master-bin.000003 |       143 |
| master-bin.000004 |       143 |
| master-bin.000005 |       143 |
| master-bin.000006 |       143 |
| master-bin.000007 |       417 |
| master-bin.000008 |       143 |
| master-bin.000009 |       143 |
| master-bin.000010 |      2857 |
| master-bin.000011 |     10293 |
+-------------------+-----------+
11 rows in set (0.03 sec)

mysql> flush tables with read lock;        #全库锁表
Query OK, 0 rows affected (0.06 sec)

mysql> set sql_log_bin=0;            #关闭二进制日志功能
Query OK, 0 rows affected (0.01 sec)



[root@vm ~]# rm -rf /mydata/test/    #模拟破坏数据库操作

[root@vm ~]# lvcreate -s  -n backup -L 3G /dev/mapper/vg-lv1     #建立快照
  Logical volume "backup" created


[root@vm ~]# cp -r /data/mysql/binlogs /tmp/    #备份二进制日志文件


mysql> set sql_log_bin=1;                    #打开二进制日志功能
Query OK, 0 rows affected (0.18 sec)

mysql> unlock tables;                        #解锁
Query OK, 0 rows affected (0.00 sec)

mysql> insert into classes(Class,NumOfStu) values('yue jiao','4');    #插入数据
Query OK, 1 row affected (0.09 sec)

还原

[root@vm ~]# service mysqld stop        #停止数据库服务
Shutting down MySQL.. SUCCESS!

 [root@vm ~]# mount /dev/mapper/vg-backup /mnt/backup/        #挂载快照

[root@vm ~]# cp -r /mnt/backup/test /mydata/                #还原数据文件
[root@vm ~]# chown mysql.mysql /mydata/test –R            #更改文件属性
[root@vm ~]# mysqlbinlog --start-position=10293--database=test /tmp/master-bin.000011 >/tmp/new_log.sql     #提取从数据库备份那时的位置到最后的位置的二进制日志信息

[root@vm ~]# service mysqld start
Starting MySQL..... SUCCESS!

mysql> set sql_log_bin=0;            #关闭二进制日志功能
Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/new_log.sql        #增量还原

3.3 Xtrabackup 备份

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

在备份的同时,innobackupex还会在备份目录中创建如下文件:

(1)xtrabackup_checkpoints —— 备份类型(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN(日志序列号)范围信息;

每个InnoDB页(通常为16k大小)都会包含一个日志序列号,即LSN。LSN是整个数据库系统的系统版本号,每个页面相关的LSN能够表明此页面最近是如何发生改变的。

(2)xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。

(3)xtrabackup_binlog_pos_innodb —— 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。

(4)xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件;

(5)backup-my.cnf —— 备份命令用到的配置选项信息;

在使用innobackupex进行备份时,还可以使用–no-timestamp选项来阻止命令自动创建一个以时间命名的目录;如此一来,innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。

从http://www.percona.com/software/percona-xtrabackup/ 下载

语法

innobackupex [options]...

创建最小权限用户用于备份

mysql> grant reload,lock tables,replication client on *.* to 'xtrabackup'@'localhost' identified by 'xtrabackup';
Query OK, 0 rows affected (0.23 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)

安装xtrabackup

[root@vm ~]# yum install -y percona-xtrabackup-2.2.3-4982.el6.x86_64.rpm

完全备份一个数据库

[root@vm ~]# innobackupex --user=xtrabackup --password=xtrabackup /tmp/backup/
MariaDB [test]>  insert into classes (Class,NumOfstu) values ('mou pai','50');        #插入数据
Query OK, 1 row affected (0.00 sec)
[root@vm backup]# innobackupex --incremental /tmp/backup/ --incremental-basedir=/tmp/backup/2015-11-27_06-11-01/    #增量备份一个数据库
MariaDB [test]>  insert into classes (Class,NumOfstu) values ('huo pai','51');        #继续插入数据
Query OK, 1 row affected (0.00 sec)
[root@vm backup]# innobackupex --incremental /tmp/backup/ --incremental-basedir=/tmp/backup/2015-11-27_06-14-53/    #第二次增量备份一个数据库

备份还原

[root@vm backup]# innobackupex --apply-log --redo-only /tmp/backup/2015-11-27_06-11-01/    #整理备份文件
[root@vm backup]# innobackupex --apply-log --redo-only /tmp/backup/2015-11-27_06-11-01/ --incremental-dir=/tmp/backup/2015-11-27_06-14-53/    #整理第一个备份文件到完全备份中
[root@vm backup]# innobackupex --apply-log --redo-only /tmp/backup/2015-11-27_06-11-01/ --incremental-dir=/tmp/backup/2015-11-27_06-16-19/    #整理第二个备份文件到完全备份中
[root@vm backup]# rm -rf /mydata/*
[root@vm backup]# innobackupex --copy-back --user=xtrabackup --password=xtrabackup /tmp/backup/2015-11-27_06-11-01/    #还原数据库
[root@vm backup]# chown mysql.mysl -R /mydata
[root@vm backup]# service mysqld start

blob.png

四、总结

备份和还原十分重要,这点就不啰嗦了,备份的时候注意不要让用户对数据库进行写操作,还原的时候需要注意关闭日志文件功能,反正还原的时候还原日志也写进二进制日志中。还需禁止数据库远程登录而进行读写操作。可以通过防火墙或者配置文件来实现。


原创文章,作者:成吉思汗,如若转载,请注明出处:http://www.178linux.com/9534

(0)
上一篇 2015-11-28 14:06
下一篇 2015-12-03 11:22

相关推荐

  • Bash基础命令操作

    Bash基础命令操作

    2018-03-18
  • grub详解

    #GRUB详解 grub基础概念 前面的开机过程我们知道:按照BIOS定义的硬件设备启动顺序,第一启动设备中的MBR去读取boot loader。boot loader功能很强大,要重新自检硬件设备,开始有一个菜单供用户选择系统或者内核版本,还要加载内核将内核解压到RAM中并执行,最后将控制权移交给内核。屈屈446个字节怎么让它完成那么多功能。所以Linux…

    Linux干货 2016-11-25
  • linux 入门基础 (二)

    主要内容包含有 文件查找、压缩和正则表达式,以及包是管理和安装。

    2017-09-10
  • session sticky + session cluster 实战

    前言 在做负载均衡集群的时候,如果后端是应用服务器,我们就有一个不得不考虑的一个问题:会话绑定。为了追踪会话,我们常见的有三种方式:(1)session sticky:会话粘性,常见有2种方式: source_ip:采用源地址绑定方式 nginx:ip_hash,ip地址哈希 haproxy:source lvs:sh,源地址哈希 cookie:基于cook…

    Linux干货 2017-02-13
  • 浅谈正则表达式

    1.       什么是正则表达式 基本说来,正则表达式是一种用来描述一定数量文本的模式。Regex代表Regular Express。本文将用<<regex>>来表示一段具体的正则表达式。 一段文本就是最基本的模式,简单的匹配相同的文本。   2. &n…

    Linux干货 2016-08-12
  • centos6.9实现网卡bonding

    centos6.9实现网卡bonding 为什么要用bonding  将多块网卡配置同一IP地址实际中是不可能的。通过bonding,虚拟一块网卡对外提供连接,物理网卡被修改为相同的MAC地址,这样可以实现多块网卡绑定同一IP地址,对外提供服务。用于网络负载均衡和网络冗余。 工作模式  bonding的模式:0-6,即:7种模式,这里我们…

    2017-08-20

评论列表(1条)

  • stanley
    stanley 2015-11-30 22:15

    眼前一亮,好久没见这么标准的文章和写标签意识的人了