Mysql 数据库备份与恢复

YY-LIN

　　今天有点时间，自己总结下mysql备份的几种方法以备查阅！
　　生产环境中数据无疑是一个公司的命脉，作为一个运维工程师无法保证企业数据的安全，那么是一个不合格的工程师。我们如何保证数据不丢失、或者丢失后快速恢复呢？下面介绍mysql数据库备份和恢复方法。
　　目录大纲
　　一、Mysql 基础知识
　　二、CP/tar 物理备份还原
　　三、Mysqldump逻辑备份
　　四、使用lvm2快照备份
　　五、使用Xtrabackup备份
　　一、MYSQL基础知识总结
　　
　　1、为什么需要备份数据？
　　生成环境中我们的数据库可能会遭遇各种各样的不测导致数据丢失，大概有几种情况（硬件故障、软件故障、自然灾害、******、误操作（占比最大））所以，为了在数据丢失后能恢复数据，我们就必须定期备份数据，备份数据的策略要根据不同的企业场景进行定制，我们可以根据这几个因素取定制如何自己企业的备份策略（如：数据库的大小、能容忍的数据丢失度、能容忍的恢复时间、需要恢复的数据）
　　2、数据备份分类
　　根据数据库的完整度分： 完全备份 和部分备份（增量备份和差异备份）
　　完全备份：备份整个数据库集，
　　部分备份：备份部分数据库集（如，指备份某个数据库，或者某个表）
　　增量备份：备份自上一次备份（增量或完全）以来变化的数据；特定：节约空间、还原较麻烦。
　　差异备份：备份自上一次完全备份以来变化的数据；特定：浪费空间，还原比增量还原简单。
　　根据备份时数据库的状态分： 热备份、温备份、冷备份
　　热备份：指数据库备份时，数据库读写不受影响
　　温备份：指数据库备份时，数据库只读，不能写操作
　　冷备份：指数据库备份时，数据库不能进行读写操作，即数据库要下线。
　　数据库的不同的存储引擎支持不同的备份方式
　　MyISAM ：支持 温备、冷备
　　InnoDB ：支持 热备 、温备、冷备
　　根据备份的手段分：物理备份和逻辑备份
　　物理备份：通过tar,cp等命令直接打包复制数据库的数据文件达到备份的效果。
　　逻辑备份：通过特定的工具从数据库中导出数据或SQL语句并另存备份（会丢失数据的精度）
　　3、备份需要考虑的问题
　　（1）我们需要备份什么数据？
　　数据、二进制日志，innodb事务日志、代码（存储过程、函数库、触发器、调度器）、配置文件
　　（2）备份工具如何选择
　　mysqldump：逻辑备份工具，适用于所有的存储引擎，支持温备份、完全备份和部分备份；innodb支持热备份。
　　cp\tar:物理备份工具，适用于所有存储引擎，冷备、完全备份、部分备份
　　lvm2 snapshot:几乎热备，借助文件系统管理工具进行备份
　　xtrabackup:一款非常强大的innodb/xtraDB热备份工具，支持完全备份、增量备份，由perconat提供
　　4、设计合适的备份策略
　　针对不同的企业场景，制定不同的备份策略对数据库进行备份，备份策略一般为三种：
　　直接cp\tar 复制数据库文件 (适合数据库较小，直接复制)
　　mysqldump+复制binlogs  （数据量一般，先mysqldump对数据完备，然后定期备份日志文）
　　lvm2快照+复制binlogs （数据量一般，不过分影响业务运行，使用LVM2快照完备后，增量备份日志）
　　xtrabackup （数据量大100G以上，不过分影响业务运行使用xtrabackup进行完全备份后，定期进行增                           量或差异备份）
　　二、CP/tar 物理备份还原
　　
　　yum安装mysql,默认数据库路径/var/lib/msyql,如下

　　查看当前数据库，我们的数据库为yema ;

　　查看yema数据库中所有表，ym_001、 ym_002

　　向数据库施加读锁
　　mysql> FLUSH TABLES WITH READ LOCK;
　　Query OK, 0 rows affected (0.00 sec)
　　开始备份数据文件
　　[root@localhost ~]# mkdir /backup       ###创建备份数据库目录
　　[root@localhost ~]# cp -a /var/lib/mysql/* /backup   ###保留权限的拷贝源数据文件
　　[root@localhost ~]# ls /backup
　　ibdata1      ib_logfile1  mysql.sock           test
　　ib_logfile0  mysql        nginx-1.12.0.tar.gz  yema
　　[root@localhost ~]#
　　模拟数据丢失并恢复
　　[root@localhost ~]# rm -rf /var/lib/mysql/*
　　[root@localhost ~]# ls /var/lib/mysql/
　　[root@localhost ~]#
　　[root@localhost ~]# service mysqld restart
　　Stopping mysqld:                                           [  OK  ]
　　Initializing MySQL database:  Installing MySQL system tables...
　　OK         重启mysql，如果是编译安装的应该不能启动，如果是rpm安装的则重新初始化数据库
　　Filling help tables...
　　OK
　　^^^^^^^^
　　Please report any problems with the /usr/bin/mysqlbug script!
　　[  OK  ]
　　Starting mysqld:                                           [  OK  ]
　　You have new mail in /var/spool/mail/root
　　重启完成后连接数据库，可以看到数据库初始化完成，yema数据库不存在了！
　　[root@localhost ~]#
　　mysql> show databases;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| mysql              |
　　| test               |
　　+--------------------+
　　3 rows in set (0.01 sec)
　　mysql>
　　将备份的数据还原回去，重启mysql，并重新连接mysql,发现数据库已恢复！！！
　　[root@localhost ~]# cp -a /backup/* /var/lib/mysql/
　　mysql> show databases;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| mysql              |
　　| test               |
　　| yema               |
　　+--------------------+
　　4 rows in set (0.00 sec)
　　mysql> show tables;
　　+----------------+
　　| Tables_in_yema |
　　+----------------+
　　| ym_001         |
　　| ym_002         |
　　+----------------+
　　到此，用cp的方式备份、还原数据库就完成了。
　　三、Mysqldump逻辑备份+二进制还原
　　
　　通过mysqldump进行一次完全备份，再修改数据，然后通过二进制日志增量备份，再恢复，需要在mysql配置文件中开启log_bin=on。mysqldump 是一个客户端的逻辑备份工具，可以生成一个重现创建原始数据库和表的sql语句，可以支持所有的存储引擎，对于innodb 支持热备。
　　基本语法格式：
　　mysqldump [options] db_name [tb_name]  恢复时需要手动CREATE DATABASES
　　mysqldump [options] --databases db_name 恢复时不需要手动CREATE DATABASES
　　mysqldump [options] --all-databases  恢复时不需要手动创建数据库
　　其他选项：-E ,--events 备份事件调度器
　　-R，--routines 备份存储过程和存储函数
　　--triggers,备份表的触发器；--skip-triggers
　　--master-data=[value]
　　1:记录为CHANGE MASTER TO语句、语句不被注释
　　2：记录为注释的CHANGE MASTER TO语句，基于二进制还原只能全库还原
　　--flush-logs:日志滚动 ，锁定表完成后执行日志滚动
　　查看数据库信息
　　mysql> show databases;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| mysql              |
　　| test
　　yema              |
　　+--------------------+
　　3 rows in set (0.00 sec)
　　mysql> use yema;
　　Reading table information for completion of table and column names
　　You can turn off this feature to get a quicker startup with -A
　　Database changed
　　mysql> show tables;
　　+----------------+
　　| Tables_in_yema |
　　+----------------+
　　| ym_001         |
　　| ym_002         |
　　+----------------+
　　2 rows in set (0.00 sec)
　　使用mysqldump备份数据库
　　#查看当前二进制文件的状态，并记录position的数字
　　mysql> show master status;
　　+------------------+----------+--------------+------------------+
　　| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
　　+------------------+----------+--------------+------------------+
　　| mysql-bin.000003 |      787 |              |                  |
　　+------------------+----------+--------------+------------------+
　　1 row in set (0.00 sec)
　　[root@localhost mysql]# mysqldump --all-databases --lock-all-tables >/backup/backup.sql
　　#备份数据库到backup.sql 文件中
　　#创建一个数据库haha，记录现在的position 值。
　　mysql> create database haha;
　　Query OK, 1 row affected (0.00 sec)
　　mysql> show master status;
　　+------------------+----------+--------------+------------------+
　　| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
　　+------------------+----------+--------------+------------------+
　　| mysql-bin.000003 |      941 |              |                  |
　　+------------------+----------+--------------+------------------+
　　[root@localhost mysql]# cp /var/lib/mysql/on.000001 /backup
　　#备份二进制文件到/backup 目录
　　#停止msyql服务，删除所有的数据文件
　　#service mysqld stop
　　[root@localhost mysql]# rm -rf /var/lib/mysql/*
　　[root@host52 ~]# ls /var/lib/mysql/
　　[root@host52 ~]#
　　重新启动mysql服务（如果是编译安装不能启动，rpm安装则会初始化数据库）
　　[root@localhost mysql]# service mysqld restart
　　Stopping mysqld:                                           [  OK  ]
　　Initializing MySQL database:  Installing MySQL system tables...
　　OK
　　Filling help tables...
　　OK
　　查看数据库，数据丢失！！！！没有之前的数据库了
　　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/backup.sql
　　开启二进制日志
　　mysql> set sql_log_bin=on;
　　查看数据库信息，发现数据库yema恢复了，但缺少haha.
　　mysql> show databases;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| mysql              |
　　| test               |
　　| yema               |
　　+--------------------+
　　4 rows in set (0.00 sec)
　　通过二进制日志增量恢复数据(haha), 两个POS点的值非常重要！！！
　　[root@host52 mysql]# cp /backup/mysql-bin.000003 /var/lib/mysql/
　　[root@msyql]#mysqlbinlog --start-position=787 --stop-position=941 mysql-bin.000003|mysql
　　mysql> show databases;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| mysql              |
　　| test               |
　　| yema               |
　　| haha               |
　　+--------------------+
　　4 rows in set (0.00 sec)
　　至此 完成了数据的完备+增量恢复!
　　四、使用lvm2快照备份
　　lvm2-snapshot知识回顾：
　　LVM快照是将快照的源分区一个时间点所有文件的元数据进行保存，
　　如果源文件没有改变，那么访问快照卷的相应文件则直接指向源分区的源文件；
　　如果源文件发生变化，快照卷中与之对应的文件不会发生变化。
　　（1）部署LVM环境、添加硬盘并分区
[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   #分区
　　…………
　　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>　　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
　　（2）创建逻辑卷并挂载
##创建逻辑卷　　[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
　　（4）mysql 配置文件设置，并导入数据库
[root@node1 ~]# vim /etc/my.cnf    #修改mysql配置文件的datadir如下　　datadir=/lvm_data
　　[root@node1 ~]# service mysqld restart  #重启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            |

　　|>　　+---------------------+
　　6 rows in set (0.00 sec)
　　mysql> SELECT COUNT(*) FROM employees;   #由于篇幅原因, 我们这里只看一下employees的行数为300024
　　+----------+
　　| COUNT(*) |
　　+----------+
　　|   300024 |
　　+----------+
　　1 row in set (0.05 sec)
　　（5）创建快照卷并备份
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
　　（6）数据恢复
[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备份
　　改天继续
　　参考文档：http://www.cnblogs.com/SQL888/p/5751631.html
　　马哥教育培训教程