Category Archives: MySQL

MySQL中使用innobackupex、xtrabackup进行大数据的备份和还原教程

大数据量备份与还原，始终是个难点。当MYSQL超10G，用mysqldump来导出就比较慢了。在这里推荐xtrabackup,这个工具比mysqldump要快很多。 一、Xtrabackup介绍 1、Xtrabackup是什么 Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份(备份时不影响数据读写)，是商业备份工具InnoDB Hotbackup的一个很好的替代品。 Xtrabackup有两个主要的工具：xtrabackup、innobackupex 1、xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表 2、 innobackupex是参考了InnoDB Hotbackup的innoback脚本修改而来的.innobackupex是一个perl脚本封装，封装了xtrabackup。主要是为了方便的 同时备份InnoDB和MyISAM引擎的表，但在处理myisam时需要加一个读锁。并且加入了一些使用的选项。如slave-info可以记录备份恢 复后，作为slave需要的一些信息，根据这些信息，可以很方便的利用备份来重做slave。 2、Xtrabackup可以做什么 : 在线(热)备份整个库的InnoDB、 XtraDB表 在xtrabackup的上一次整库备份基础上做增量备份(innodb only) 以流的形式产生备份，可以直接保存到远程机器上(本机硬盘空间不足时很有用) MySQL数据库本身提供的工具并不支持真正的增量备份，二进制日志恢复是point-in-time(时间点)的恢复而不是增量备份。 Xtrabackup工具支持对InnoDB存储引擎的增量备份，工作原理如下： (1)首先完成一个完全备份，并记录下此时检查点的LSN(Log Sequence Number)。 (2)在进程增量备份时，比较表空间中每个页的LSN是否大于上次备份时的LSN，如果是，则备份该页，同时记录当前检查点的LSN。 首 先，在logfile中找到并记录最后一个checkpoint(“last checkpoint LSN”)，然后开始从LSN的位置开始拷贝InnoDB的logfile到xtrabackup_logfile；接着，开始拷贝全部的数据文 件.ibd；在拷贝全部数据文件结束之后，才停止拷贝logfile。 因为logfile里面记录全部的数据修改情况，所以，即时在备份过程中数据文件被修改过了，恢复时仍然能够通过解析xtrabackup_logfile保持数据的一致。 因为innobackupex支持innodb,myisam,所以本文说一下，怎么使用innobackupex。 二，安装xtrabackup 1、下载地址 http://www.percona.com/downloads/XtraBackup/ 2、安装 根据需求，选择不同的版本，我选择的是rpm安装包，如果报以下错误 复制代码代码如下: [root@localhost xtrabackup]# rpm -ivh percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm warning: percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY error: Failed dependencies: perl(Time::HiRes) is needed by percona-xtrabackup-2.2.4-5004.el6.x86_64 解决办法： 复制代码代码如下: [root@localhost xtrabackup]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL //安装依赖包 [root@localhost xtrabackup]# rpm -ivh percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm   //重新安装 warning: percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key […]

MySQL 数据备份与还原

一、数据备份 1、使用mysqldump命令备份 mysqldump命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。 mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。 1、备份一个数据库 mysqldump基本语法： mysqldump -u username -p dbname table1 table2 …-> BackupName.sql 其中： dbname参数表示数据库的名称； table1和table2参数表示需要备份的表的名称，为空则整个数据库备份； BackupName.sql参数表设计备份文件的名称，文件名前面可以加上一个绝对路径。通常将数据库被分成一个后缀名为sql的文件； 使用root用户备份test数据库下的person表

其生成的脚本如下： 文件的开头会记录MySQL的版本、备份的主机名和数据库名。 文件中以“--”开头的都是SQL语言的注释，以"/*!40101"等形式开头的是与MySQL有关的注释。40101是MySQL数据库的版本号，如果MySQL的版本比1.11高，则/*!40101和*/之间的内容就被当做SQL命令来执行，如果比4.1.1低就会被当做注释。 2、备份多个数据库 语法：

加上了--databases选项，然后后面跟多个数据库

3、备份所有数据库 mysqldump命令备份所有数据库的语法如下：

示例：

2、直接复制整个数据库目录 MySQL有一种非常简单的备份方法，就是将MySQL中的数据库文件直接复制出来。这是最简单，速度最快的方法。 不过在此之前，要先将服务器停止，这样才可以保证在复制期间数据库的数据不会发生变化。如果在复制数据库的过程中还有数据写入，就会造成数据不一致。这种情况在开发环境可以，但是在生产环境中很难允许备份服务器。 注意：这种方法不适用于InnoDB存储引擎的表，而对于MyISAM存储引擎的表很方便。同时，还原时MySQL的版本最好相同。 3、使用mysqlhotcopy工具快速备份 一看名字就知道是热备份。因此,mysqlhotcopy支持不停止MySQL服务器备份。而且，mysqlhotcopy的备份方式比mysqldump快。mysqlhotcopy是一个perl脚本，主要在Linux系统下使用。其使用LOCK TABLES、FLUSH TABLES和cp来进行快速备份。 原理：先将需要备份的数据库加上一个读锁，然后用FLUSH TABLES将内存中的数据写回到硬盘上的数据库，最后，把需要备份的数据库文件复制到目标目录。 命令格式如下：

dbname：数据库名称； backupDir：备份到哪个文件夹下； 常用选项： --help：查看mysqlhotcopy帮助； --allowold：如果备份目录下存在相同的备份文件，将旧的备份文件加上_old； --keepold：如果备份目录下存在相同的备份文件，不删除旧的备份文件，而是将旧的文件更名； --flushlog：本次辈分之后，将对数据库的更新记录到日志中； --noindices：只备份数据文件，不备份索引文件； --user=用户名：用来指定用户名，可以用-u代替； --password=密码：用来指定密码，可以用-p代替。使用-p时，密码与-p之间没有空格； --port=端口号：用来指定访问端口，可以用-P代替； --socket=socket文件：用来指定socket文件，可以用-S代替； mysqlhotcopy并非mysql自带，需要安装Perl的数据库接口包；下载地址为:http://dev.mysql.com/downloads/dbi.html 目前，该工具也仅仅能够备份MyISAM类型的表。 二、数据还原 　　1、还原使用mysqldump命令备份的数据库的语法如下： 　　mysql -u root -p [dbname] < backup.sq 示例：

2、还原直接复制目录的备份 　　通过这种方式还原时，必须保证两个MySQL数据库的版本号是相同的。MyISAM类型的表有效，对于InnoDB类型的表不可用，InnoDB表的表空间不能直接复制。 from:http://www.cnblogs.com/kissdodog/p/4174421.html

mysql数据库ibdata1文件瘦身

MYSQL运行2年之后ibdata1文件变的非常巨大，传说ibdata1是InnoDB的产物，而且只会增大不会减少。 上网搜了一下解决方法。大体思路就是备份数据，然后删除数据库再还原数据库。 # 备份数据库： mysqldump -uDBuser -pPassword --quick --force --routines --add-drop-database --all-databases --add-drop-table > /data/bkup/mysqldump.sql # 停止数据库 service mysqld stop # 删除这些大文件 rm /usr/local/mysql/var/ibdata1 rm /usr/local/mysql/var/ib_logfile* # 手动删除除Mysql之外所有数据库文件夹，然后启动数据库 service mysqld start # 还原数据 mysql -uDBuser -pPassword < /data/bkup/mysqldump.sql from:http://blog.sina.com.cn/s/blog_40ce02d7010169zr.html

使用 MYSQLBINLOG 来恢复数据

BINLOG就是一个记录SQL语句的过程，和普通的LOG一样。不过只是她是二进制存储，普通的是十进制存储罢了。 1、配置文件里要写的东西： [mysqld] log-bin=mysql-bin(名字可以改成自己的，如果不改名字的话，默认是以主机名字命名) 重新启动MSYQL服务。 二进制文件里面的东西显示的就是执行所有语句的详细记录，当然一些语句不被记录在内，要了解详细的，见手册页。 2、查看自己的BINLOG的名字是什么。 show binlog events; query result(1 records) Log_name Pos Event_type Server_id End_log_pos Info yueliangdao_binglog.000001 4 Format_desc 1 106 Server ver: 5.1.22-rc-community-log, Binlog ver: 4 3、我做了几次操作后，她就记录了下来。 又一次 show binlog events 的结果。 query result(4 records)   Log_name Pos Event_type Server_id End_log_pos Info yueliangdao_binglog.000001 4 Format_desc 1 106 Server ver: 5.1.22-rc-community-log, Binlog ver: 4 yueliangdao_binglog.000001 106 Intvar 1 134 INSERT_ID=1 yueliangdao_binglog.000001 134 Query 1 254 use test; create table a1(id int not null auto_increment primary key, str varchar(1000)) engine=myisam yueliangdao_binglog.000001 254 Query 1 330 use […]

Uber 从 Postgres 切换到 MySQL

Uber工程师在官方博客上描述了他们为什么要从 Postgres 切换到 MySQL 数据库。Uber的早期架构是由 Python编写的后端应用构成，使用了 Postgres 数据库。但此后，Uber的架构发生了显著的改变，转变到了微服务模型和新的数据平台。以前他们使用 Postgres，现在则改用了基于 MySQL 的数据库分片层。Uber工程师称他们之所以切换到Schemaless和其它基于 MySQL 的后端服务，最主要的原因是Postgres 数据复制效率低下，Postgres更新已有行的效率低于 MySQL，Postgres需要重写每一个行索引，而MySQL只更新改变的索引。 相关链接 MySQL 的详细介绍：请点这里 MySQL 的下载地址：请点这里   from:http://www.oschina.net/news/75638/uber-mysql-migration

阿里巴巴分布式数据库同步系统 otter

otter 基于数据库增量日志解析，准实时同步到本机房或异地机房的mysql/oracle数据库. 一个分布式数据库同步系统。 工作原理： 原理描述： 1. 基于Canal开源产品，获取数据库增量日志数据。 什么是Canal, 请点击 2. 典型管理系统架构，manager(web管理)+node(工作节点) a. manager运行时推送同步配置到node节点 b. node节点将同步状态反馈到manager上 3. 基于zookeeper，解决分布式状态调度的，允许多node节点之间协同工作.   osc地址：http://www.oschina.net/p/otter

Incorrect string value: '\xF0\x90\x8D\x83…' for column… Emoji表情字符过滤的Java实现

Emoji表情字符现在在APP已经广泛支持了。但是Mysql的UTF8编码对Emoji字符的支持却不是那么好。所以我们经常会遇到这样的异常： Incorrect string value: '\xF0\x90\x8D\x83…' for column 原因是Mysql里UTF8编码最多只能支持3个字节，而Emoji表情字符使用的UTF8编码，很多都是4个字节，有些甚至是6个字节。 解决的方案有两种： 1.使用utf8mb4的mysql编码来容纳这些字符。 2.过滤掉这些特殊的表情字符。 关于第一种解决方法，请参考：http://segmentfault.com/a/1190000000616820 和 http://info.michael-simons.eu/2013/01/21/java-mysql-and-multi-byte-utf-8-support/ 有大量细节需要注意，例如：mysql版本，mysql的配置，mysql connector的版本等等。。 因为我们使用的云数据库，所以我选择了过滤这些特殊字符。其实过滤的方式很简单，直接使用正则表达式匹配编码范围，然后替换就行了。 下面是我的代码。 更多可以参考：http://stackoverflow.com/questions/27820971/why-a-surrogate-java-regexp-finds-hypen-minus import org.apache.commons.lang3.StringUtils; public class EmojiFilterUtils { /**      * 将emoji表情替换成*      *       * @param source      * @return 过滤后的字符串      */ public static String filterEmoji(String source) { if(StringUtils.isNotBlank(source)){ return source.replaceAll("[\\ud800\\udc00-\\udbff\\udfff\\ud800-\\udfff]", "*");         }else{ return source;         }     } public static void main(String[] arg ){ try{             String text = "This is a smiley \uD83C\uDFA6 face\uD860\uDD5D \uD860\uDE07 \uD860\uDEE2 \uD863\uDCCA \uD863\uDCCD \uD863\uDCD2 \uD867\uDD98 ";             System.out.println(text);             System.out.println(text.length());             System.out.println(text.replaceAll("[\\ud83c\\udc00-\\ud83c\\udfff]|[\\ud83d\\udc00-\\ud83d\\udfff]|[\\u2600-\\u27ff]", "*"));             System.out.println(filterEmoji(text));         }catch (Exception ex){             ex.printStackTrace();         }     } } from:http://blog.csdn.net/shootyou/article/details/44852639

一篇文章，掌握所有开源数据库的现状

数据库作为业务的核心，在整个基础软件栈中是非常重要的一环。近几年社区也是新的方案和思想层出不穷，接下来我将总结一下近几年一些主流的开源数据库方案，其背后的设计思想以及适用场景。本人才疏学浅如有遗漏或者错误请见谅。本次分享聚焦于数据库既结构化数据存储 OLTP 及 NoSQL 领域，不会涉及 OLAP、对象存储、分布式文件系统。 1 开源RDBMS与互联网的崛起 很长时间以来，关系型数据库一直是大公司的专利，市场被 Oracle / DB2 等企业数据库牢牢把持。但是随着互联网的崛起、开源社区的发展，上世纪九十年代 MySQL 1.0 的发布，标志着关系型数据库的领域社区终于有可选择的方案。 MySQL 第一个介绍的单机RDBMS就是MySQL。相信大多数朋友都已经对 MySQL 非常熟悉，基本上 MySQL 的成长史就是互联网的成长史。我接触的第一个 MySQL 版本是 MySQL 4.0，到后来的 MySQL 5.5 更是经典——基本所有的互联网公司都在使用。 MySQL 也普及了「可插拔」引擎这一概念，针对不同的业务场景选用不同的存储引擎是 MySQL tuning 的一个重要的方式。比如对于有事务需求的场景使用 InnoDB;对于并发读取的场景 MyISAM 可能比较合适;但是现在我推荐绝大多数情况还是使用 InnoDB，毕竟 5.6 后已经成为了官方的默认引擎。大多数朋友都基本知道什么场景适用 MySQL(几乎所有需要持久化结构化数据的场景)，我就不赘述了。 另外值得一提的是 MySQL 5.6中引入了多线程复制和 GTID，使得故障恢复和主从的运维变得比较方便。另外，5.7(目前处于 GA 版本) 是 MySQL 的一个重大更新，主要是读写性能和复制性能上有了长足的进步(在5.6版本中实现了SCHEMA级别的并行复制，不过意义不大，倒是MariaDB的多线程并行复制大放异彩，有不少人因为这个特性选择MariaDB。MySQL 5.7 MTS支持两种模式，一种是和5.6一样，另一种则是基于binlog group commit实现的多线程复制，也就是MASTER上同时提交的binlog在SLAVE端也可以同时被apply，实现并行复制)。 如果有单机数据库技术选型的朋友，基本上只需要考虑 5.7 或者 MariaDB 就好了，而且 5.6、5.7 由 Oracle 接手后，性能和稳定性上都有了明显的提升。 PostgreSQL PostgreSQL的历史也非常悠久，其前身是UCB的Ingres，主持这个项目的 Michael Stronebraker 于 2015 年获得图灵奖。后来项目更名为 Post-Ingres，项目基于 BSD license 下开源。 1995 年几个 UCB 的学生为 Post-Ingres 开发了 SQL 的接口，正式发布了 PostgreSQL95，随后一步步在开源社区中成长起来。 和 MySQL 一样，PostgreSQL 也是一个单机的关系型数据库，但是与 MySQL […]

大型网站应用中 MySQL 的架构演变史

没有什么东西是一成不变的，包含我们的理想和生活！MySQL作为一个免费的开源的关系型数据库，深受大家喜爱，从最初的无人问津到当下的去IOE，都体现出了MySQL举足轻重的作用。今天我们就从淘宝的发展来阐述MySQL在大型网站下的架构演变史！ MySQL的可扩展性 架构的可扩展性往往和并发是息息相关，没有并发的增长，也就没有必要做高可扩展性的架构，这里对可扩展性进行简单介绍一下，常用的扩展手段有以下两种 Scale-up : 纵向扩展，通过替换为更好的机器和资源来实现伸缩，提升服务能力 Scale-out : 横向扩展,  通过加节点（机器）来实现伸缩，提升服务能力 对于互联网的高并发应用来说，无疑Scale out才是出路，通过纵向的买更高端的机器一直是我们所避讳的问题，也不是长久之计，在scale out的理论下，可扩展性的理想状态是什么？ 可扩展性的理想状态 一个服务，当面临更高的并发的时候，能够通过简单增加机器来提升服务支撑的并发度，且增加机器过程中对线上服务无影响(no down time)，这就是可扩展性的理想状态！ MySQL架构的演变 MySQL简单网站架构(V1.0) 一个简单的小型网站或者应用背后的架构可以非常简单,  数据存储只需要一个mysql instance就能满足数据读取和写入需求（这里忽略掉了数据备份的实例），处于这个时间段的网站，一般会把所有的信息存到一个database instance里面。 在这样的架构下，我们来看看数据存储的瓶颈是什么？ 1.数据量的总大小  一个机器放不下时 2.数据的索引（B+ Tree）一个机器的内存放不下时 3.访问量(读写混合)一个实例不能承受 只有当以上3件事情任何一件或多件满足时，我们才需要考虑往下一级演变。 从此我们可以看出，事实上对于很多小公司小应用，这种架构已经足够满足他们的需求了，初期数据量的准确评估是杜绝过度设计很重要的一环，毕竟没有人愿意为不可能发生的事情而浪费自己的经历。 这里简单举个我的例子，对于用户信息这类表 （3个索引），16G内存能放下大概2000W行数据的索引，简单的读和写混合访问量3000/s左右没有问题，你的应用场景是否 MySQL的垂直架构(V2.0) 一般当V1.0 遇到瓶颈时，首先最简便的拆分方法就是垂直拆分，何谓垂直？就是从业务角度来看，将关联性不强的数据拆分到不同的instance上，从而达到消除瓶颈的目标。以图中的为例，将用户信息数据，和业务数据拆分到不同的三个实例上。对于重复读类型比较多的场景，我们还可以加一层cache，来减少对DB的压力。 在这样的架构下，我们来看看数据存储的瓶颈是什么？ 单实例单业务，依然存在V1.0所述瓶颈，遇到瓶颈时可以考虑往本文更高V版本升级, 若是读请求导致达到性能瓶颈可以考虑往V3.0升级， 其他瓶颈考虑往V4.0升级 MySQL的主从架构(V3.0) 此类架构主要解决V2.0架构下的读问题，通过给Instance挂数据实时备份的思路来迁移读取的压力，在Mysql的场景下就是通过主从结构，主库抗写压力，通过从库来分担读压力，对于写少读多的应用，V3.0主从架构完全能够胜任 在这样的架构下，我们来看看数据存储的瓶颈是什么？ 写入量主库不能承受 MySQL的路由多集群架构(V4.0) 对于V2.0 V3.0方案遇到瓶颈时，都可以通过水平拆分来解决，水平拆分和垂直拆分有较大区别，垂直拆分拆完的结果，在一个实例上是拥有全量数据的，而水平拆分之后，任何实例都只有全量的1/n的数据，以下图Userinfo的拆分为例，将userinfo拆分为3个cluster，每个cluster持有总量的1/3数据，3个cluster数据的总和等于一份完整数据（注：这里不再叫单个实例 而是叫一个cluster 代表包含主从的一个小mysql集群） 数据如何路由？ 1.Range拆分 sharding key按连续区间段路由，一般用在有严格自增ID需求的场景上，如Userid, Userid Range的小例子：以userid 3000W 为Range进行拆分 1号cluster userid 1-3000W 2号cluster userid 3001W-6000W 2.List拆分 List拆分与Range拆分思路一样，都是通过给不同的sharding key来路由到不同的cluster,但是具体方法有些不同,List主要用来做sharding key不是连续区间的序列落到一个cluster的情况，如以下场景： 假定有20个音像店，分布在4个有经销权的地区，如下表所示： 业务希望能够把一个地区的所有数据组织到一起来搜索，这种场景List拆分可以轻松搞定 3.Hash拆分 通过对sharding key 进行哈希的方式来进行拆分，常用的哈希方法有除余,字符串哈希等等，除余如按userid%n 的值来决定数据读写哪个cluster，其他哈希类算法这里就不细展开讲了。 数据拆分后引入的问题： 数据水平拆分引入的问题主要是只能通过sharding key来读写操作，例如以userid为sharding key的切分例子，读userid的详细信息时，一定需要先知道userid,这样才能推算出再哪个cluster进而进行查询，假设我需要按username进行检索用户信息，需要引入额外的反向索引机制（类似HBASE二级索引），如在redis上存储username->userid的映射，以username查询的例子变成了先通过查询username->userid，再通过userid查询相应的信息。 实际上这个做法很简单，但是我们不要忽略了一个额外的隐患，那就是数据不一致的隐患。存储在redis里的username->userid和存储在mysql里的userid->username必须需要是一致的，这个保证起来很多时候是一件比较困难的事情，举个例子来说，对于修改用户名这个场景，你需要同时修改redis和mysql,这两个东西是很难做到事务保证的,如mysql操作成功 但是redis却操作失败了（分布式事务引入成本较高）,对于互联网应用来说，可用性是最重要的，一致性是其次，所以能够容忍小量的不一致出现. 毕竟从占比来说，这类的不一致的比例可以微乎其微到忽略不计（一般写更新也会采用mq来保证直到成功为止才停止重试操作） 在这样的架构下，我们来看看数据存储的瓶颈是什么？ 在这个拆分理念上搭建起来的架构，理论上不存在瓶颈（sharding key能确保各cluster流量相对均衡的前提下),不过确有一件恶心的事情，那就是cluster扩容的时候重做数据的成本，如我原来有3个cluster，但是现在我的数据增长比较快，我需要6个cluster，那么我们需要将每个cluster 一拆为二，一般的做法是 1.摘下一个slave,停同步, 2.对写记录增量log（实现上可以业务方对写操作 多一次写持久化mq  或者mysql主创建trigger记录写 等等方式） […]