标签归档:mysql

NoSQL to MySQL with Memcached

InnoDB with Memcached是在提供MySQL服务的同一进程中提供Memcached服务。memcached是作为MySQL的插件程式,通过访问本地的InnoDB API直接访问innodb数据。
架构图如下所示:

innodb
当前版本提供的功能有:
1. memcached作为mysqld的守护插件:mysqld和memcached是在同一进程空间中运行,对数据的访问具有非常低的延迟
2.直接访问InnoDB:绕过SQL解析器和优化
3. 支持memcapable的标准协议:同时支持memcached的基于文本的协议和二进制协议,所有的55 memcapable测试都通过
4. 支持多列:用户可以通过value映射到多个列,该值是分离预先定义的“分离器”。
5. 可选的本地缓存:“cache-only”, “innodb-only”, 和 “caching”, 适用于Memcached的set,get,delete和flush操作. 不但可以省去开发中使用Memcached来缓存数据的麻烦,并且具有更好的可靠性和数据一致性
6.批量操作:用户可以通过指定daemon_memcached_r_batch_size和daemon_memcached_w_batch_size大小来进行批量提交
7.支持所有的memcached配置选项,通过daemon_memcached_option参数设置
目前还处于预览版本,下载地址如下:
http://downloads.mysql.com/snapshots/pb/mysql-5.6-labs-innodb-memcached/binary-release-community_linux2.6-x86-64bit_tar-gz/mysql-5.6.2-labs-innodb-memcached-linux2.6-x86_64.tar.gz

MySQL开启命令自动补全功能(auto-rehash)

mysql命令行工具自带这个功能,但是默认是禁用的。想启用其实很简单,打开配置文件找到no-auto-rehash,用符号 # 将其注释,另外增加auto-rehash即可。

是不是很简单?

分区表的使用

1.1. 场景

在每个系统中都会生成一些日志,往往有些日志都是需要记录的,比如:生成订单的过程的一些记录、某个账号活动的一些信息。这样一来每天生成的日志会很多,而且还会生成到数据库中。

像这些记录信息在某段时间过后就没用了。为了节省空间资源,和产品确认这些信息只保留3个月的。3个月之前的都可以删了。

1.2. 使用普通表记录这些数据

一般情况下大家都知道创建一些日志表存放这些数据,之后就在每个月初去删除三个月以前的日志记录(不管用手动人工执行删除或使用定时任务)。

这种方法看上去很可行的。当是,使用delete删除3月以前的数据,其实磁盘的空间是没有人减少的。懂得的人会去做Optimize、或者从新导入导出数据。可是像这种优化和导入导出的方法在数据量大的时候是很不可行的。因此,就会陷入尴尬的境地。

1.3. 使用partition table(分区表)

使用partition table的方法来存储这些数据就很有优势了。我们只要在每个月初创建下一个月的分区,在删除3月前的分区就好了。由于每个分区都是存储在不同的表空间文件中(这里使用的不是共享表空间模式)。所以在删除了分区之后空间会直接的释放出来。

提示:可以使用压缩比高的TokuDB引擎,该引擎的数据压缩会比原来的小5-10倍。

 

1.4. 使用普通表和partition table对比演示

  • 使用普通表的演示

查看normal_table表占用磁盘空间大小

上面可以看到表的数据是1.8G。这时候按需求我们删除前3月的数据(这里我们删除一月份的数据)

继续查看现在的磁盘空间

理想的情况下应该是normal_table.ibd文件的空间大小应该会减少才对,可是并不是想象的那样。它的空间大小还是不变的。

这时候你需要让空间稍稍的释放出来就需要使用优化表命令

可以看到空间被释放出来了。

  • 使用partition table

查看使用磁盘的容量情况

上面我们可以看到分区表的结构是每一个分区有着自己的表空间。

现在我们删除1月份的数据,这里我们只需要删除p1分区就好。

可以看到只要我们删除表空间就能直接的释放磁盘空间。

1.5. 总结

上面的演示可以看到在保存日志之类的数据使用partition是在合适不过的了,不仅能节省空间还很方便我们的维护。

 

昵称:HH
QQ:275258836
ttlsa群交流沟通(QQ群②:6690706 QQ群③:168085569 QQ群④:415230207(新) 微信公众号:ttlsacom)

感觉本文内容不错,读后有收获?

逛逛衣服店,鼓励作者写出更好文章。

MySQL GTID 复制

GTID简介

什么是GTID

GTID(Global Transaction ID)是对于一个已提交事务的编号,并且是一个全局唯一的编号。 GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量,并且随着事务提交单调递增。下面是一个GTID的具体形式

更详细的介绍可以参见:官方文档

GTID的作用

那么GTID功能的目的是什么呢?具体归纳主要有以下两点:

  • 根据GTID可以知道事务最初是在哪个实例上提交的
  • GTID的存在方便了Replication的Failover

这里详细解释下第二点。我们可以看下在MySQL 5.6的GTID出现以前replication failover的操作过程。假设我们有一个如下图的环境

gtid

此时,Server A的服务器宕机,需要将业务切换到Server B上。同时,我们又需要将Server C的复制源改成Server B。复制源修改的命令语法很简单即CHANGE MASTER TO MASTER_HOST='xxx', MASTER_LOG_FILE='xxx', MASTER_LOG_POS=nnnn。而难点在于,由于同一个事务在每台机器上所在的binlog名字和位置都不一样,那么怎么找到Server C当前同步停止点,对应Server Bmaster_log_filemaster_log_pos是什么的时候就成为了难题。这也就是为什么M-S复制集群需要使用MMM,MHA这样的额外管理工具的一个重要原因。 这个问题在5.6的GTID出现后,就显得非常的简单。由于同一事务的GTID在所有节点上的值一致,那么根据Server C当前停止点的GTID就能唯一定位到Server B上的GTID。甚至由于MASTER_AUTO_POSITION功能的出现,我们都不需要知道GTID的具体值,直接使用CHANGE MASTER TO MASTER_HOST='xxx', MASTER_AUTO_POSITION命令就可以直接完成failover的工作。 So easy不是么?

搭建

本次搭建使用了mysql_sandbox脚本为基础,先创建了一个一主三从的基于位置复制的环境。然后通过配置修改,将整个架构专为基于GTID的复制。如果你还不熟悉mysql_sandbox,可以阅读博客之前的文章博客之前的文章一步步的安装。 根据MySQL官方文档给出的GTID搭建建议。需要一次对主从节点做配置修改,并重启服务。这样的操作,显然在production环境进行升级时是不可接受的。Facebook,Booking.com,Percona都对此通过patch做了优化,做到了更优雅的升级。具体的操作方式会在以后的博文当中介绍到。这里我们就按照官方文档,进行一次实验性的升级。 主要的升级步骤会有以下几步:

  • 确保主从同步
  • 在master上配置read_only,保证没有新数据写入
  • 修改master上的my.cnf,并重启服务
  • 修改slave上的my.cnf,并重启服务
  • 在slave上执行change master to并带上master_auto_position=1启用基于GTID的复制

由于是实验环境,read_only和服务重启并无大碍。只要按照官方的GTID搭建建议做就能顺利完成升级,这里就不赘述详细过程了。下面列举了一些在升级过程中容易遇到的错误。

常见错误

gtid_mode=ON,log_slave_updates,enforce_gtid_consistency这三个参数一定要同时在my.cnf中配置。否则在mysql.err中会出现如下的报错

change master to 后的warnings

在按照文档的操作change master to后,会发现有两个warnings。其实是两个安全性警告,不影响正常的同步(有兴趣的读者可以看下关于该warning的具体介绍。warning的具体内容如下:

实验一:如果slave所需要事务对应的GTID在master上已经被purge了

根据show global variables like '%gtid%'的命令结果我们可以看到,和GTID相关的变量中有一个gtid_purged。从字面意思以及 官方文档可以知道该变量中记录的是本机上已经执行过,但是已经被purge binary logs to命令清理的gtid_set。 本节中我们就要试验下,如果master上把某些slave还没有fetch到的gtid event purge后会有什么样的结果。

以下指令在master上执行

在slave2上重新做一次主从,以下命令在slave2上执行

实验二:忽略purged的部分,强行同步

那么实际生产应用当中,偶尔会遇到这样的情况:某个slave从备份恢复后(或者load data infile)后,DBA可以人为保证该slave数据和master一致;或者即使不一致,这些差异也不会导致今后的主从异常(例如:所有master上只有insert没有update)。这样的前提下,我们又想使slave通过replication从master进行数据复制。此时我们就需要跳过master已经被purge的部分,那么实际该如何操作呢? 我们还是以实验一的情况为例:

先确认master上已经purge的部分。从下面的命令结果可以知道master上已经缺失24024e52-bd95-11e4-9c6d-926853670d0b:1这一条事务的相关日志

在slave上通过set global gtid_purged='xxxx'的方式,跳过已经purge的部分

可以看到此时slave已经可以正常同步,并补齐了24024e52-bd95-11e4-9c6d-926853670d0b:2-3范围的binlog日志。

MySQL管理工具MySQL Utilities — mysqlprocgrep (29)

mysqlprocgrep 望文生义搜索进程的。搜索出给定时间内(–age)和指定条件相匹配(–match-xxx)的进程,显示出来或执行某些动作。

如果没有指定–age和–match-xxx选项,打印出所有的进程。

–match-xxx 选项如同INFORMATION_SCHEMA.PROCESSLIST 表列名。

执行该命令需要 PROCESS 和 SUPER 权限。没有PROCESS权限,没法查看其他用户的权限。没有SUPER权限,不能对其他用户的进程执行某些动作。

输出格式

  • grid (default)
  • csv
  • tab
  • vertical

选项

实例

生成杀死用户是pro_user的空闲进程的存储过程(不含CREATE PROCEDURE)

生成杀死用户是pro_user的空闲进程的存储过程(含CREATE PROCEDURE)

杀死用户nobody在1分钟内创建的所有进程

杀死所有超过1小时的空闲进程

权限

需要对mysql数据库的SELECT权限。

Inception所支持的参数变量(8)

考虑到不同用户的规范会有所不同,Inception支持很多可配置的参数,这些配置参数都是全局参数,因为对于同一个服务的规则,不应该经常变化,或者说不应该出现一些业务是这样的规则,而另一些业务是那样的规则,所以这些变量一经设置,就影响所有的审核。如果确实一个公司有多个规则,则建议配置多套Inception服务,在各自的配置文件中指定相应的参数的值。

设置或者打印这些变量的值,可以通过MySQL客户端连接到Inception服务器,通过新的语法命令来实现。连接Inception的时候,只需要指定Inception的地址及端口即可,其它用户名密码可以不指定,因为Inception没有权限验证的过程。

Inception打印变量时,不支持像原来的MySQL服务器一样可以show variables like ‘%name%’这样实现模糊匹配,只能是精确匹配,如果找不到则返回空结果集,或者可以打印所有变量。语法如下:


支持语句 意义
inception get variables ‘variable_name’; 通过variable_name指定变量名称,只显示指定的变量名的值
inception get variables; 显示Inception所有变量的值
inception set variable_name=value; 设置变量名为variable_name的变量的值

Inception目前所支持的变量参数如下表所示:


参数名字 可选参数 默认值 功能说明
inception_check_insert_field ON/OFF ON 是不是要检查插入语句中的列链表的存在性
inception_check_dml_where ON/OFF ON 在DML语句中没有WHERE条件时,是不是要报错
inception_check_dml_limit ON/OFF ON 在DML语句中使用了LIMIT时,是不是要报错
inception_check_dml_orderby ON/OFF ON 在DML语句中使用了Order By时,是不是要报错
inception_enable_select_star ON/OFF ON Select*时是不是要报错
inception_enable_orderby_rand ON/OFF ON order by rand时是不是报错
inception_enable_nullable ON/OFF ON 创建或者新增列时如果列为NULL,是不是报错
inception_enable_foreign_key ON/OFF ON 是不是支持外键
inception_max_key_parts 1-64 5 一个索引中,列的最大个数,超过这个数目则报错
inception_max_update_rows 1-MAX 10000 在一个修改语句中,预计影响的最大行数,超过这个数就报错
inception_max_keys 1-1024 16 一个表中,最大的索引数目,超过这个数则报错
inception_enable_not_innodb ON/OFF OFF 建表指定的存储引擎不为Innodb,不报错
inception_support_charset MySQL支持字符集 “utf8mb4” 表示在建表或者建库时支持的字符集,如果需要多个,则用逗号分隔,影响的范围是建表、设置会话字符集、修改表字符集属性等
inception_check_table_comment ON/OFF ON 建表时,表没有注释时报错
inception_check_column_comment ON/OFF ON 建表时,列没有注释时报错
inception_check_primary_key ON/OFF On 建表时,如果没有主键,则报错
inception_enable_partition_table ON/OFF OFF 是不是支持分区表
inception_enable_enum_set_bit ON/OFF OFF 是不是支持enum,set,bit数据类型
inception_check_index_prefix ON/OFF ON 是不是要检查索引名字前缀为”idx_”,检查唯一索引前缀是不是”uniq_”
inception_enable_autoincrement_unsigned ON/OFF ON 自增列是不是要为无符号型
inception_max_char_length 1-MAX 16 当char类型的长度大于这个值时,就提示将其转换为VARCHAR
inception_check_autoincrement_init_value ON/OFF ON 当建表时自增列的值指定的不为1,则报错
inception_check_autoincrement_datatype ON/OFF ON 当建表时自增列的类型不为int或者bigint时报错
inception_check_timestamp_default ON/OFF ON 建表时,如果没有为timestamp类型指定默认值,则报错
inception_enable_column_charset ON/OFF OFF 允许列自己设置字符集
inception_check_autoincrement_name ON/OFF ON 建表时,如果指定的自增列的名字不为ID,则报错,说明是有意义的,给提示
inception_merge_alter_table ON/OFF ON 在多个改同一个表的语句出现是,报错,提示合成一个
inception_check_column_default_value ON/OFF ON 检查在建表、修改列、新增列时,新的列属性是不是要有默认值
inception_enable_blob_type ON/OFF ON 检查是不是支持BLOB字段,包括建表、修改列、新增列操作
inception_enable_identifer_keyword ON/OFF OFF 检查在SQL语句中,是不是有标识符被写成MySQL的关键字,默认值为报警。
auto_commit ON/OFF OFF 这个参数的作用是为了匹配Python客户端每次自动设置auto_commit=0的,如果取消则会报错,针对Inception本身没有实际意义
bind_address string * 这个参数实际上就是MySQL数据库原来的参数,因为Incpetion没有权限验证过程,那么为了实现更安全的访问,可以给Inception服务器的这个参数设置某台机器(Inception上层的应用程序)不地址,这样其它非法程序是不可访问的,那么再加上Inception执行的选项中的用户名密码,对MySQL就更加安全
general_log ON/OFF ON 这个参数就是原生的MySQL的参数,用来记录在Inception服务上执行过哪些语句,用来定位一些问题等
general_log_file string inception.log 设置general log写入的文件路径
inception_user string empty 这个用户名在配置之后,在连接Inception的选项中可以不指定user,这样线上数据库的用户名及密码就可以不暴露了,可以做为临时使用的一种方式,但这个用户现在只能是用来审核,也就是说,即使在选项中指定–enable-execute,也不能执行,这个是只能用来审核的帐号。
inception_password string empty 与上面的参数是一对,这个参数对应的是选项中的password,设置这个参数之后,可以在选项中不指定password
inception_enable_sql_statistic ON/OFF ON 设置是不是支持统计Inception执行过的语句中,各种语句分别占多大比例,如果打开这个参数,则每次执行的情况都会在备份数据库实例中的inception库的statistic表中以一条记录存储这次操作的统计情况,每次操作对应一条记录,这条记录中含有的信息是各种类型的语句执行次数情况,具体的信息需要参考后面一章<<Inception 的统计功能>>
inception_read_only ON/OFF OFF 设置当前Inception服务器是不是只读的,这是为了防止一些人具有修改权限的帐号时,通过Inception误修改一些数据,如果inception_read_only设置为ON,则即使开了enable-execute,同时又有执行权限,也不会去执行,审核完成即返回
inception_check_identifier ON/OFF ON 打开与关闭Inception对SQL语句中各种名字的检查,如果设置为ON,则如果发现名字中存在除数字、字母、下划线之外的字符时,会报Identifier “invalidname” is invalid, valid options: [a-z,A-Z,0-9,_].

注意事项

上面已经说了,可以用MySQL客户端通过命令inception get variables;查看Inception支持的所有参数变量,所有以inception开头的参数,都是专门为Inception加的,而其它的则大都是MySQL原生的,大部分没有任何作用的都已经去除了,有些则没有,这个在使用过程中可着情处理。 而以inception开头的参数中,还有一部分是以inception_osc开头的,这十几个参数主要是用来控制Inception使用OSC工具来执行ALTER表操作时使用的,这部分会在后面一章<<Inception 对OSC的支持>>中详细叙述。

表迁移工具的选型(1)

1.1. 前言

在DBA的工作当中导数据可谓是要经常要做的一件事,在数据量小的时候随随便便整库更新也是无所谓的。但是单数据量达到百G那整库更新起来可谓是十分费力,而且测试库的磁盘也不好。因此,经常发生的应该是单表的迁移或拷贝。

1.2. 导数据的利器

导出数据的(工具/方法)有很多:

  • 逻辑迁移(mysqldump、mysqldumper、mysqlpump(5.7新增))
  • MySQL Utilities 中的mysqldbexport和mysqldbimport
  • SELECT … INTO OUTFILE … FROM xxx 的方法
  • 使用mysql单表迁移的方法
  • 使用xtrabackup 恢复单表的方法

想必上面的工具或方法多多少少大家都有尝试过。

1.3. 工具/方法的选择

一般在什么样的情况下应该使用哪种工具会比较好呢?

有许多人可能都知道数据小就用逻辑导出呗(mysqldump),如果数据大我们就使用物理导出呗。

那我就想问怎么样算是数据量大,怎么样算数据量小呢?

有很多人会直接去查看表的行数来定,如:

还有的人会使用操作系统的命令来查看磁盘数据的大小,如:

其实比较正确的方法是使用数据字典information_schema.tables

之后,我们会模拟三种在不同场景下如何选择表迁移的方法:mysqlpump、xtrabackup以及复制*.ibd的方法。

我们这边主要的目的是不是叫你如何使用这些工具,因为这些工具在网上能找到一大片使用的方法。我们的目的在于如何选择使用它(当然会附带完整的例子)

1.4. 我的环境

我使用的是Percona Server

 

昵称:HH
QQ:275258836
ttlsa群交流沟通(QQ群②:6690706 QQ群③:168085569 QQ群④:415230207(新) 微信公众号:ttlsacom)

感觉本文内容不错,读后有收获?

逛逛衣服店,鼓励作者写出更好文章。

MySQL字段何时拆分

前言

在数据库的维护当中对表的垂直才分是必然的,基本上在业务刚开始准守 3NF 是明智的,当然也可以有一些反范式的设计。但是,建议还是应该在3NF的基础上再酌情考虑反范式。

当遇到真的要对一些表进行拆分,那要拆那些字段嘞?下面我们就来分析一下。

场景

在新业务上线后导致TPS突然增高,这时我们对新上的业务又不是很懂。而问题又要分析解决。

分析解决步骤

  1. 解析近期生成的binlog文件获得是哪个表哪个字段操作的多。

这边使用到了 吴炳锡 大神的一个工具 parsebinlog

该工具可以解析出表的操作情况

链接: https://github.com/wubx/mysql-binlog-statistic

上面工具只能解析单个binlog文件的操作,如果要解析多个文件的可以使用 笔者的工具 pasrebinlog_stat.py

pasrebinlog_stat.py 是对执行parsebinlog解析完之后的数据进行的统计生成excel文件的工具

具体使用方法(在github最后有一点小小的说明):https://github.com/daiguadaidai/mysql-binlog-statistic

使用笔者的方法统计后会生成 5 个文件:

如果关心update操作可以查看sort_by_update.xls 其中是按update操作次数降序排列的。

然后根据要了解的 表名 到format.txt中查看哪个字段更新平凡

  1. 查看解析出的文件相关 excel

如这边我在sort_by_update.xls文件中看到 t1 表在定义行,说明他的总update量最多。

然后在format.txt找到 t1 表的统计格式如下:

从上可以很清楚的看到 ‘6 col’ 和 ‘7 col’操作占用了大多的update操作。

通过查看数据库表结构可以知道这两个字段分表是 price 和 inventory。

  1. 拆分字段

知道了哪个表的那个字段update频繁,可以先将字段从表中剥离出单独的表。至于需要不要开另外的库需要看会不会对其他主要业务有影响(如:下单付款等)。如果有影响在拆到其他库中。

拆出来的目的主要是为了让每一个page能存储更多的数据,并且不会让 t1 表的数据在缓存中能保存的更长久,不会出现平凡的age out 显现(没有解决TPS高的问题)。

  1. 对于要提高TPS一般有两种方法

第一种:将TPS分散,也就是需要将表进行分区到不同库(一般这样要考虑的东西太多。数据量不大一般不考虑)

第二种:使用能提供更高TPS的产品(这边建议 redis 是不错的选择)。

这边排除第一种

使用第二种:

更具时间经验值:一般使用redis 能提供 TPS:3-5W 更具机器情况还有所提高。

QPS:7-10W 更具机器情况还有所提高。

对于我们的TPS的情况 3-5W TPS 的redis一般能够胜任

这边主要担心的就是有关 持久化 的问题,这就是架构上需要设计的了。

  1. redis 自身具有持久化功能,每秒持久化一次。
  2. 更具我们 同步的情况其实同步可以忍受短时间不实时现象。如果出现redis失效(宕机或怎么的可以重启redis重新同步所有数据)
  3. 可以搭建 redis的master-slave 或 cluster 都行这样就能很好的解决一台redis宕机问题。
  4. 可以根据 数据库软件设计的某些原理和借鉴秒杀架构,在后台不定期的将redis的数据同步到MySQL。

步骤可以有:

  1. 先将相关数据 格式化 的写入到日志文件(有能力提供消息队列更好)。
  2. 写入日志成功之后再将数据在redis做操作。确保出问题有数据库可查。

最终配合redis的架构图:

mysql

 

昵称: HH

QQ: 275258836

ttlsa群交流沟通(QQ群②: 6690706 QQ群③: 168085569 QQ群④: 415230207(新) 微信公众号: ttlsacom)

感觉本文内容不错,读后有收获?

逛逛衣服店,鼓励作者写出更好文章。

Inception备份库表数据中文乱码解决

MySQL环境

字符集: utf8mb4

乱码重现

  1. 要执行的 python 代码

2.执行后查看备份库中 $_$inception_backup_information$_$ 和 alifeba_user 表记录如下输出

从上面的输出可以看到 $_$inception_backup_information$_$ 中的数据已经乱码了

3.将sql中添加 set names utf8mb4; 如下部分代码

4.执行修改后的代码再次查看 $_$inception_backup_information$_$ 和 alifeba_user 表记录

由上面可以看到中文乱码已经解决

 

昵称: HH

QQ: 275258836

ttlsa群交流沟通(QQ群②: 6690706 QQ群③: 168085569 QQ群④: 415230207(新) 微信公众号: ttlsacom)

感觉本文内容不错,读后有收获?

逛逛衣服店,鼓励作者写出更好文章。

MySQL多线程备份工具mydumper

Mydumper介绍

Mydumper是一个针对MySQL和Drizzle的高性能多线程备份和恢复工具。开发人员主要来自MySQL,Facebook,SkySQL公司。目前已经在一些线上使用了Mydumper。

Mydumper主要特性:

  • 轻量级C语言写的
  • 执行速度比mysqldump快10倍
  • 事务性和非事务性表一致的快照(适用于0.2.2以上版本)
  • 快速的文件压缩
  • 支持导出binlog
  • 多线程恢复(适用于0.2.1以上版本)
  • 以守护进程的工作方式,定时快照和连续二进制日志(适用于0.5.0以上版本)
  • 开源 (GNU GPLv3)

Mydumper安装

mydumper参数介绍:

mydumper用例

Mydumper官网:http://www.mydumper.org/