Mysql之一：mysqldump和LVM逻辑卷快照

#####################################

常见的数据备份类型和备份工具

mysqldump备份工具

使用LVM逻辑卷快照备份

数据的重要性我就不多说了……，常见的数据备份类型是根据不同的备份性质和要求来划分的，他们分别是：根据业务是否可在线、根据物理和逻辑、根据备份范围，下面就来简单的扫盲一下：

根据业务是否可在线

热备份：读写操作不受影响

温备份：仅可以执行读操作

冷备份：离线备份，读写操作均不能进行

根据物理和逻辑

物理备份：直接复制数据文件，速度快(在文件系统级别完成，不需要mysql进程参与)，移植性差。

逻辑备份：将数据导出至文本文件中，速度慢(需要mysql进程参数)，移植性好，丢失浮点数精度，可以方便使用文本处理工具。

根据备份范围

完全备份：备份全部数据

增量备份：仅备份上次完全备份或增量备份以后变化的数据(比增量备份多占据空间，但是还原的时候方便)

差异备份：仅备份上次完全备份以来变化的数据

这里特意盗用一幅图来更加直观的阐述增量备份和差异备份的区别：

完全+增量(还原时间长，复杂，占用空间小)

完全+差异(还原时间短，简单，占用空间大)

他们之间没有约束，比如完全在线物理备份……

另外备份出的数据要经常测试是否可用，和制定好紧急还原方案………

备份的对象有数据、配置文件、二进制日志、事务日志等……

各个备份工具功能和性能对比如下图所示：

备份策略：周完全+每日增量(二进制日志)

完全备份

<code>mysql> create database soulboy;</code>

<code>Query OK, 1 row affected (0.00 sec)</code>

<code>mysql> use soulboy;</code>

<code>Database changed</code>

<code>mysql> create table tb1 (</code><code>id</code> <code>int auto_increment primary key,name char(30));</code>

<code>Query OK, 0 rows affected (0.01 sec)</code>

<code>mysql></code>

<code>mysql> insert into tb1 (name) values (</code><code>'kobe'</code><code>);</code>

<code>mysql> </code><code>select</code> <code>* from tb1; </code><code>#新库</code>

<code>完全备份，比例中表类型是InnoDB</code>

<code>mysqldump -uroot -p --master-data=2 --single-transaction --all-databases > </code><code>/backup/alldatabases</code><code>.sql</code>

<code>mysqldum只能对MyISAM引擎做温备份，备份之前必须锁定表,如下：</code>

<code>mysqldump -uroot -p --master-data=2 --flush-logs --all-databases --lock-all-tables > </code><code>/root/alldatabases</code><code>.sql</code>

<code>##########################mysqldump选项###########################</code>

<code>-u </code><code>#指定用户名</code>

<code>-p </code><code>#指定用户密码</code>

<code>-h </code><code>#指定主机地址</code>

<code>-A|--all-databases </code><code>#备份所有数据库</code>

<code>--databases </code><code>#备份指定数据库</code>

<code>--single-transcation </code><code>#基于此项可以实现对InnoDB表做热备份，但不需要使用</code>

<code>--lock-all-tables </code><code>#执行备份时为所有表请求加锁</code>

<code>-E|--events </code><code>#备份事件调度器代码</code>

<code>--opt </code><code>#同时启动各种高级选项</code>

<code>-R|--routines </code><code>#备份存储过程和存储函数</code>

<code>--flush-logs </code><code>#备份之前刷新日志</code>

<code>--triggers </code><code>#备份触发器</code>

<code>--master-data=2 </code><code>#该选项将会记录binlog的日志位置与文件名并追加到文件中，如果为1将会输出CHANGE</code>

第一天

<code>mysql> insert into tb1 (name) values (</code><code>'wade'</code><code>);</code>

<code>mysql> </code><code>select</code> <code>* from tb1;</code>

<code>####滚动二进制日志，进入第二天</code>

<code>mysql> FLUSH LOGS; </code>

<code>####备份第一天二进制日志为SQL格式并重命名为firstdays.sql</code>

<code>mysqlbinlog </code><code>/mydata/data/mysql-bin</code><code>.000008 > </code><code>/backup/firstday</code><code>.sql</code>

第二天

<code>#####插入新数据</code>

<code>mysql> insert into tb1 (name) values (</code><code>'nash'</code><code>);</code>

<code>#####备份第二天二进制日志为SQL格式并重命名为secondday.sql</code>

<code>#####注意生产环境中务必将二进制日志存储在不同于数据目录的设备中</code>

<code> </code><code>mysqlbinlog </code><code>/mydata/data/mysql-bin</code><code>.000009 > </code><code>/backup/secondday</code><code>.sql</code>

模拟故障

<code>cd</code> <code>/mydata/data</code>

<code>#####删除数据目录中所有文件</code>

<code>cd</code> <code>/usr/local/mysql</code>

<code>#####初始化数据库</code>

<code>scripts</code><code>/mysql_install_db</code> <code>--user=myslq --datadir=</code><code>/mydata/data/</code>

<code>#####杀死僵死进程</code>

<code>ps</code> <code>aux | </code><code>grep</code> <code>mysql</code>

<code>service mysqld restart</code>

数据还原

<code>#####查看当前使用的二进制日志文件以及所处位置</code>

<code>show master status;</code>

<code>#####禁止还原过程中记录二进制日志</code>

<code>mysql> </code><code>set</code> <code>sql_log_bin=0;</code>

<code>#####还原完全备份</code>

<code>mysql> </code><code>source</code> <code>/backup/alldatabases</code><code>.sql;</code>

<code>#####还原第一天备份</code>

<code>mysql> </code><code>source</code> <code>/backup/firstday</code><code>.sql;</code>

<code>#####还原第二天备份，全部数据完全到此结束</code>

<code>mysql> </code><code>source</code> <code>/backup/secondday</code><code>.sql;</code>

<code>#####开启二进制日志记录功能</code>

<code>mysql> </code><code>set</code> <code>sql_log_bin=1;</code>

<code>#####测试数据是否还原成功</code>

锁表并滚动日志

<code>mysql> FLUSH TABLES WITH READ LOCK;</code>

<code>#####滚动二进制日志文件</code>

<code>mysql> FLUSH LOGS;</code>

<code>#####即时点恢复时候需要使用，记录LVM时刻二进制日志文件和所处位置</code>

<code>mysql -e </code><code>'SHOW MASTER STATUS\G'</code> <code>> </code><code>/backup/master-</code><code>`</code><code>date</code> <code>+%F`.info</code>

数据目录和事务文件创建LVM快照

<code>#####对mydata目录进行快照</code>

<code>lvcreate -L 200M -s -p r -n mydata-snap </code><code>/dev/myvg/mydata</code>

<code>#####以只读方式挂载镜像文件</code>

<code>mount</code> <code>/dev/myvg/mydata-snap</code> <code>/mnt</code> <code>-o ro</code>

<code>#####将镜像文件内容复制到/save目录中</code>

<code>mkdir</code> <code>/save</code>

<code>#####删除镜像文件</code>

<code>umount</code> <code>/mnt</code>

<code>lvremove --force </code><code>/dev/myvg/mydata-snap</code>

<code>#####删除二进制日志文件</code>

<code>rm</code> <code>-rf </code><code>/save/mysql-bin</code><code>.*</code>

解锁并插入数据

<code>mysql> UNLOCK TABLES;</code>

<code>#####查看当前表内容</code>

<code>#####插入新数据，模拟第一天</code>

<code>insert into tb1 (name) values (</code><code>'firstday'</code><code>);</code>

<code>#####滚动二进制日志文件，模拟第二天</code>

<code>flush logs; </code>

<code>insert into tb1 (name) values (</code><code>'sencondday'</code><code>);</code>

查看快照时间和当前二进制日志信息

<code>#####查看快照时刻的二进制日志记录信息</code>

<code>cat</code> <code>/backup/master-2013-09-19</code><code>.info </code>

<code> </code><code>File: mysql-bin.000005</code>

<code> </code><code>Position: 107</code>

<code> </code><code>Binlog_Do_DB:</code>

<code>Binlog_Ignore_DB:</code>

<code>#####查看使用当前二进制日志和所处位置 </code>

<code>mysql> SHOW MASTER STATUS; </code>

基于多个二进制日志生成单个SQL格式文件

<code>#####查看快照时刻所处位置的时间点</code>

<code>[root@localhost save]</code><code># mysqlbinlog --start-position=107 /mydata/data/mysql-bin.000005</code>

<code>/*!40019 SET @@session.max_insert_delayed_threads=0*/;</code>

<code>/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;</code>

<code>DELIMITER /*!*/;</code>

<code>#130919 11:46:29 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.28-log created 130919 11:46:29 #时间</code>

<code>#####基于时间做限定来合并多个日志文件为一个SQL格式的文件</code>

<code>mysqlbinlog --start-datetime=</code><code>'2013-09-19 11:46:29'</code> <code>/mydata/data/mysql-bin</code><code>.000005 </code><code>/mydata/data/mysql-bin</code><code>.000006 > </code><code>/backup/incremental-</code><code>`</code><code>date</code> <code>+%F`.sql</code>

<code>service mysqld stop</code>

<code>#####删除数目目录下所有文件</code>

<code>rm</code> <code>-rf </code><code>/mydata/data/</code><code>*</code>

还原快照时刻数据

<code>#####复制快照时刻数据到数据目录</code>

<code>cp</code> <code>-a </code><code>/save/</code><code>* </code><code>/mydata/data/</code>

<code>service mysqld start</code>

<code>#####查看当前表内数据</code>

还原增量数据

<code>#####关闭二进制日志记录功能</code>

<code>#####还原增量备份的数据</code>

<code>mysql> </code><code>source</code> <code>/backup/incremental-2013-09-19</code><code>.sql</code>

<code>#####查看表内数据，还原成功</code>

<code>| 4 | firstday |</code>

<code>| 5 | sencondday |</code>

本文转自 ftmoonfans 51CTO博客，原文链接:http://blog.51cto.com/soulboy/1298999

Mysql之一：mysqldump和LVM逻辑卷快照

继续阅读

SQL优化SQL语句优化的目的

数据迁移方法数据迁移原则数据迁移之双写方案数据迁移之级联同步方案

redis集群数据一致性_RedisRaft为Redis集群带来强大的数据一致性

JAVA高效编程指南

宝塔面板mysql恢复2018.1.8更新

Centos7 MySQL 5.7 安装MySQL 5.7 安装

查找入职员工时间排名倒数第三的员工所有信息

Hibernate使用Hibernate的“3个准备，7个步骤”Hibernate API简介操作实体对象对象识别

云计算面试题——mysql/存储引擎/备份

关于SQL语言

SQL语言基础：常用的数据查询语句

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

MySQL的4种隔离级别？出现问题

neo4j之cypher使用文档

mysql使用source命令导入.sql文件

sqlServer根据经纬查距离