一个技术博客

LBS 初试

Tim阅读(4083)评论(2)

位置服务(LBS,Location Based Services)又称定位服务,LBS是由移动通信网络和卫星定位系统结合在一起提供的一种增值业务,通过一组定位技术获得移动终端的位置信息(如经纬度坐标数据),提供给移动用户本人或他人以及通信系统,实现各种与位置相关的业务。

­LBS相关的应用随处可见. 日常使用的微信、陌陌、地图、订餐等APP。查找附近(陌生人、餐馆、网吧、银行…), 数据量少可以在经纬度上加减进行初步筛选,然后计算距离排序,而需要在大量经纬度数据中检索的时候,就不太可行了。
博主总结了下常见的LBS实现方案

1、Mysql使用GeoHash转换成字符串存储
2、Postgresql的PostGis插件,支持空间计算
3、Mongodb 自带距离计算
4、Redis 自带距离计算
5、Mysql spitial
6、lbs云服务(百度、高德…)、基于搜索引擎的

测试机器配置均为单核 2G ram,数据库均为默认配置,结果仅供参考

一、GeoHash

GeoHash将二维的经纬度按规则转换成字符串存储,例如经度:114.775123,纬度:22.588995转换成的GeoHash字符串为ws18eb0wqjye,对GeoHash字符串建立索引,查询的时候对GeoHash字符串进行前缀匹配,

select id,lat,lon,geohash from lbs where geohash like ‘ws18eb%’,前缀匹配长度越长,距离越近。

原理和具体实现方式移步:GeoHash核心原理解析开发LBS应用之 根据一点的经纬度实现附近点的查询 – geohash

php添加GeoHash模块:https://github.com/shenzhe/geohash

在Mysql测试,使用php随机生成经度114~115,纬度22~23内1000w条数据,GeoHash列建索引

LBS 初试

实测效率如下
在like ‘ws18eb%’时,因为数据少(),速度很快,此时6位匹配到±0.61km左右LBS 初试

LBS 初试

当like ‘ws18e%’时,数据大概在,6位匹配到±2.4km左右,这个时候就慢了。like 前缀还是挺快的,慢的是计算距离然后排序这里,当然,计算公式还可以稍微优化下筛选出更少数据,为了方便使用,也可以写成存储过程LBS 初试

前缀长度不同,取出的记录也会有区别,这是因为GeoHash按照矩形区域划分导致的。

二、PostGis

PostGIS is a spatial database extender for PostgreSQL object-relational database. It adds support for geographic objects allowing location queries to be run in SQL.

PostGIS 是 PostgreSQL 关系数据库的空间操作扩展,支持空间对象,可以直接计算距离

测试数据如下,多了一列the_geom,使用PostGIS内置函数ST_GeomFromText(‘POINT(lon ,lat)’),4326)转换生成

LBS 初试

对the_geom建立gist索引

测试效率如下,配合order by <->非常快

LBS 初试

LBS 初试

由于<->类似近似值的效果,因此还是存在数据不精确的问题,不过大部分时候是足够用的,当limit 越来越多的时候,时间消耗也会越来越大

 

三、Mongodb

MongoDB 是由C++语言编写的,是一个基于分布式文件存储的开源数据库系统。
MongoDB 将数据存储为一个文档,数据结构由键值(key=>value)对组成。
MongoDB 文档类似于 JSON 对象。字段值可以包含其他文档,数组及文档数组。
Mongodb支持二维空间索引,使用空间索引,Mongodb支持一种特殊查询,如某地图网站上可以查找离你最近的咖啡厅,银行等信息。

测试数据如下LBS 初试

查找最近的10个,耗时1.6s
LBS 初试

查找最近的100个,耗时5.3sLBS 初试

Mongodb查询出来的数据比较精确

四、Redis

Redis是一个开源的使用ANSI C语言编写、支持网络、可基于内存亦可持久化的日志型、Key-Value数据库。之前的Redis需要dynamic-redis-unstable版并安装模块才支持,最新版的Redis(3.2)已经众望所归的添加了geo,支持GEORADIUS WITHDIST,在内存上计算效率也比较高。

以上测试数据均一致,1000w条,由于是无序集合,因此在Redis中测试数据是无序的。

LBS 初试

查找1km内的10条记录,非常快,时间小于0.5s,就没显示时间信息

LBS 初试

查找10km内101条记录,耗时0.53s,相当暴力

LBS 初试

Redis因为是基于内存的,在内存中查找计算效率会非常高,而且精确

五、Mysql spitial

Mysql空间函数索引在Myisam引擎很早之前就有了,5.6引入st_distance等函数,可以计算2点间的距离,5.7.5之后InnoDB引擎也支持空间索引了,
数据如下,1000w条,和以上测试数据一致,spa字段类型为point ,建立spatial索引

LBS 初试

查找耗时10s左右,因为需要先进行计算排序后limit,因此10条和100条记录基本一致,可以进一步控制数量后计算,速度会有较大提升

LBS 初试

LBS 初试

性能不高,不过博主在测试中发现速度也可以优化到0.5s左右,怎么做到呢(不是缓存的缘故),一般人先不告诉他LBS 初试

LBS 初试

六、lbs云服务(百度、高德…)、基于搜索引擎

 

此部分博主暂还未测试,看描述应该是支持的,将经纬度数据存入云,通过api调用即可,百度LBS云描述如下
LBS.云是百度地图针对LBS开发者推出的平台级服务,结合已有的地图API和SDK服务,通过开放服务端存储和计算能力,提供海量位置数据的实时存储、检索、展示一体化解决方案。

类似还有高德等lbs云服务商。

搜索引擎ElasticSearch官网 https://www.elastic.co/,lucene 等,使用搜索引擎检索速度也会很快,读者可以自行研究,此部分以后测试过再补上。

总结:需要高精确度和速度可以使用Redis,基于内存,性能可谓暴力,对精确度不是很敏感可以考虑mysql geohash实现,不改变mysql现有架构,5.7.5后mysql支持spatial索引可以直接使用MBRcontains先检索后排序,在数量不多的情况下,速度也可以接受,mongodb和postgis按需使用,另外还可以使用lbs云服务,投资小,见效快,O(∩_∩)O~

Percona Toolkit 2.2工具集使用

Tim阅读(3436)评论(0)

percona-toolkit是一组高级命令行工具的集合,用来执行各种通过手工执行非常复杂和麻烦的MySQL和系统任务,这些任务包括:
检查master和slave数据的一致性(pt-table-checksum),并进行数据重新同步(pt-table-sync)
有效地对记录进行归档
查找重复的索引
对服务器信息进行汇总
分析来自日志和tcpdump的查询
当系统出问题的时候收集重要的系统信息

percona-toolkit 2.2工具集合中包含以下组件,官网文档

1、安装percona-toolkit   需要注意mysql版本需和系统版本(el6,el7等)对应,如果不对应会提示libmysqlclient错误导致安装不上,http://dev.mysql.com/downloads/repo/yum/   

首先安装依赖包

下载并安装

如有以下报错,还需要安装yum install -y perl-Digest-MD5

error: Failed dependencies:
perl(Digest::MD5) is needed by percona-toolkit-2.2.16-1.noarch

安装完成

可以看到/usr/bin/新增了许多文件

查看任意组件版本

如果只需要单个程序可以单独下载,例如pt-align

之后授权即可运行

 

2、percona-toolkit 2.2 使用和介绍

(1) pt-align   用于对文件格式进行格式化输出

align.txt 内容如下

pt-align  align.txt   后效果如下,每列都对齐首行了

Percona Toolkit 2.2工具集使用

(2) pt-archiver 清理、导出、转移数据

pt-archiver [OPTIONS] –source DSN –where WHERE

–purge 清理数据,直接删除源表里的数据,谨慎使用;
–where 指定删除条件,必须的条件,有字符串的,要用引号括起来
–limit 表示一次要删除多少行数据
–no-delete 表示不删除源表里的数据,默认是删除的
–file 指定数据要导出到哪个文件
–source 源数据库信息
–dest 指定远程数据库信息

test表内有如下数据

执行pt-archiver  –purge

执行完成,id<10的记录都被清除

导出数据到文件

博主实测最后一行无法导出,pt-archiver查询语句如下

可以看到最后一行WHERE (1=1) AND (id < ’12’) 导致id=12的记录查不出来,此处明显缺失=号,应该为id <= ’12’,因此需要注意 pt-archiver 2.2.6 有最后一行无法导出的bug

 迁移到远程数据库,依然会有最后一行的bug。

从查询日志中看出是边界值导致最后一行无法导出的bug,博主尝试将pt-archiver 第6247的$col <修改为$col<=

6244 ) {
6245 my $col = $q->quote($sel_stmt->{scols}->[0]);
6246 my ($val) = $dbh->selectrow_array(“SELECT MAX($col) FROM $src->{db_tbl}”);
6247 $first_sql .= ” AND ($col <= ” . $q->quote_val($val) . “)”;
6248 }
6249

之后再运行,此时查询日志变为

查询语句中原来为 AND (id < ’12’) 变为   AND (id <= ’12’) 变为 ,因为每行都有limit 1,因此这样修改应该是可行的,也得到了需要的数据

(3)pt-config-diff  比对.cnf 配置文件

(4)pt-deadlock-logger 

显示指定的DSN的死锁日志信息,他能够标准输出到屏幕也可以把信息写日志文件中(–log参数)甚至可以保留到指定的表中(–dest参数),该工具默认是永久执行,除非指定–run-time –iterations

用法:percona-toolkit 之 【pt-deadlock-logger】说明 

(5)pt-diskstats 打印磁盘io统计信息,类似iostat,但是这个工具是交互式并且比iostat更详细。可以分析从远程机器收集的数据。

(6)pt-duplicate-key-checker 查找重复的索引,并提供删除语句,显示索引总数

(7)pt-fifo-split 模拟切割文件并通过管道传递给先入先出队列而不用真正的切割文件

每次读取一百万行记录:

每次读取一百万行,指定fifo文件为/tmp/my-fifo,并使用load data命令导入到mysql中:

对大文件的数据导入数据库非常有用,具体的可以查看http://www.mysqlperformanceblog.com/2008/07/03/how-to-load-large-files-safely-into-innodb-with-load-data-infile/

(8)pt-find 找出表并执行命令,类似GNU find

找出创建于一天前MyISAM表,并打印出来

找出innodb引擎的表,并且把他们转换为myisam表:

找出没有记录的表并删除

找出大于5G的表

列出所有的表,包括表的总行数和索引的size,并按总数倒序排序(sort,pt-find本身没排序功能)

列出所有的表,并把输出信息保存到tablesize表里:

(9)pt-fingerprint  用于生成查询指纹。主要将将sql查询生成queryID,pt-query-digest中的ID即是通过此工具来完成的。

(10)pt-fk-error-logger 提取和记录mysql外键错误信息

创建测试表,并插入一行数据,使其报外键错误

在test数据库建表foreign_key_errors

找出外键错误并保存到foreign_key_errors表中

需要注意:如果没有外键错误,程序会一直在等待,需要Ctrl+C强制退出,或者加上–daemonize选项,后台运行。

(11)pt-heartbeat  用于检测主从延迟时间

在主库(Master)上开启守护进程来更新test.heartbeat表,第一次运行加入–create-table 选项会新建heartbeat表:

在从库(Slave)上运行,监控从的延迟情况:

更详细的文档:pt-heartbeat使用实践 – yayun

(12)pt-index-usage 从日志记录(log文件)中读取查询语句,并用explain分析他们是如何利用索引。完成分析之后会生成一份关于索引没有被查询使用过的报告。

分析查询会在slow.log里面并且打印报告:

不打印出来,而且对于后来的分析把结果存入percona数据库里面,使用–create-save-results-database会自动生成数据库和表来保存结果。

(13)pt-ioprofile  

如果提示如下报错,则需要安装strace,和lsof依赖包(yum -y install strace  lsof)

由于实现方式是使用strace注入到线程中,所以运行时需要root或sudo

博主在使用docker容器中运行pt-ioprofile时,有如下报错


暂未找到解决方法,不过可以在运行docker容器的宿主机上正常运行,pt-ioprofile -p 360 ,360为mysql进程在宿主机上的进程号。
生产环境中使用ioprofile时请谨慎使用。
官方也有如下提示,大意是使用strace注入,有可能影响到进程

pt-ioprofile should be considered an intrusive tool, and should not be used on production servers unless you understand and accept the risks.

更详细的文档:pt-ioprofile分析查看mysql的真实IO情况

(14)pt-kill kill MySQL连接的一个工具,此工具从show processlist 中获取满足条件的连接或者从包含show processlist的文件中读取满足条件的连接并打印或者杀掉或者执行其他操作

打印出运行时间超过60秒的线程,只打印不kill

每10秒执行一次,kill掉sleep线程

找出select超过10秒的线程并kill掉,记录到test.kill_log 表内,第一次运行可以加上–create-log-table选项会自动创建表

(15)pt-mext  用于记录固定时间间隔内status返回值的变化,类似 SHOW GLOBAL STATUS

计算 10 秒内的变化

(16)pt-mysql-summary 查看mysql统计信息

也可以用来监控:MySQL运行状态监控(pt-mysql-summary)

(17)pt-online-schema-change  InnoDB执行在线DDL的时候,修改过程中不会造成读写阻塞

详细文档:MySQL使用pt-online-change-schema实现在线加字段

DDL原理: 【MySQL】online ddl 工具之pt-online-schema-change

(18)pt-pmp   进行GDB跟踪调试,需要安装gdb( yum -y install gdb )

一个类似的穷人剖析器 http://poormansprofiler.org
博主测试无法在docker容器中正常运行,
在虚拟机中运行显示如下,不明觉厉,先Mark下

 

(19)pt-query-digest  用于分析mysql慢查询的一个工具,它可以分析log文件(binlog、general log、slowlog),也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。

直接分析慢查询文件:

可配合pt-fingerprint生成id

更详细的文档:pt-query-digest查询日志分析工具

(20)pt-show-grants 提取、排序和打印MySQL 相关账户
pt-show-grants默认是只读,风险低。如果使用–flush,将执行flush privileges。
至今没有已知bug导致用户的损失。

从一台服务器上将权限复制到另一台机器上;可以很简单地从第一台服务器上抽取相关权限,然后直接导入第二台机器上;
将权限信息放在版本控制中。使用自动grant dump到版本控制中,可能会获得一些没有变化的授权。原因在于mysql按照看似随机的顺序打印授权信息。

可以看到如下信息

更详细的文档:pt-show-grants

(21)pt-sift 用于浏览pt-stalk生成的文件

(22)pt-slave-delay 延迟时间从Master上同步

在主从复制的架构中,正常情况下主上操作的记录也会在从上进行操作,虽说是异步复制,但操作会“实时”的同步到从。比如在主上不小心误操作了,还没等反应过来从上也会马上执行误操作,后期只有通过二进制或则备份恢复数据了,费时,又费力,没有任何回旋的余地,而且也会影响到网站的功能。而pt-slave-delay故意让主上的操作延迟制定的时间写入到从,这样就可以快速的处理上面说的问题了。

在Slave上执行

#–delay :从库延迟主库的时间,上面为1分钟。

#–interval :检查的间隔时间,上面为15s检查一次。(可选),不选则1分钟检查一次。
#–run-time :该命令运行时间,上面为该命令运行10分钟关闭。(可选),不选则永远运行。
#–daemonize :后台运行
其他的为链接数据库的账号信息,账号要有PROCESS, REPLICATION CLIENT, and SUPER权限

注意:延迟的时间实际为 delay+interval,即该命令的让从延迟主75s。

更详细的文档: percona-toolkit 之 【pt-slave-delay】说明

(23)pt-slave-find 连接mysql主服务器并查找其所有的从,然后打印出所有从服务器的层级关系

会显示如下信息

(24)pt-slave-restart  监视mysql复制错误,并尝试重启mysql复制当复制停止的时候

详细文档: MySQL Slave异常关机的处理 (pt-slave-restart)

类似sql_slave_skip_counter,sql_slave_skip_counter操作能够暂时让主从恢复工作,但多半数据一致性已经被破坏的更严重了,早晚有一天被掩盖的问题会再次爆发出来。建议使用pt-table-checksum和pt-table-sync彻底解决 MySQL主从服务器数据一致性的核对与修复

(25)pt-stalk 收集数据,供MySQL故障时分析

在MySQL服务器出现短暂(5~30秒)的性能波动的时候,一般的性能监控工具都很难抓住故障现场,也就很难收集对应较细粒度的诊断信息。另外,如果这种波动出现的频率很低,例如几天才一次,我们也很难人为的抓住现场,收集数据。这正是pt-stalk所解决的问题。

上面的命令表示,让pt-stalk后台运行(–daemonize),并监视SHOW GLOBAL STATUS中的Threads_connected状态值,如果该值超过2500,则触发收集主机和MySQL的性能、状态信息。pt-stalk会每隔一秒检查一次状态值,如果连续5次满足触发条件,则开始收集。

详细文档:使用pt-stalk诊断MySQL问题Percona Toolkit by example – pt-stalk

使用pt-stalk分析MySQL的性能波动

(26)pt-summary 查看系统摘要报告,打印出来的信息包括:CPU、内存、硬盘、网卡等信息,还包括文件系统、磁盘调度和队列大小、LVM、RAID、网络链接信息、netstat 的统计,以及前10的负载占用信息和vmstat信息。

(27)pt-table-checksum  找出(M-S)主从不同步的数据

Master上执行,如下检查test库中的test表,会自动创建percona.checksums 表

Slave上执行SQL语句

可以看到行数不一样,下面使用pt-table-sync进行同步消除差异。

(28)pt-table-sync 修复(M-S)主从不同步的数据

Master上执行,打印修复语句,只打印不执行,如需执行将–print替换为–execute

得到如下

将SQL语句到Slave上执行即可,执行完成后,

再次在Master上使用pt-table-checksum进行检查

然后在Slave上查询,为空即同步完成

pt-table-checksum原理:用pt-table-checksum校验数据一致性

用pt-table-sync修复不一致的数据

(29)pt-table-usage 分析查询如何使用表

使用报错,网上相关文档甚少。

(30)pt-upgrade 用来检查在新版本中运行的SQL是否与老版本一样,返回相同的结果,最好的应用场景就是数据迁移的时候。

(31)pt-variable-advisor 分析MySQL变量并且就可能问题提出建议。

(32)pt-visual-explain 树状显示explain

 

总结:percona-toolkit是一个非常强大的工具集合,这些工具主要包括开发、性能、配置、监控、复制、系统、实用六大类,里面有的工具对DBA非常有用,如果能掌握并加以灵活应用,将能极大的提高工作效率。

其中pt-table-checksum和pt-table-sync (找出主从不同步数据并修复),pt-duplicate-key-checker(找出重复索引),pt-online-schema-change(在线DDL) 比较重要

 

Percona XtraBackup 使用、备份、恢复

Tim阅读(3237)评论(0)

一、安装Percona XtraBackup

继续偷懒使用yum安装,官方安装文档https://www.percona.com/doc/percona-xtrabackup/2.4/installation/yum_repo.html,博主安装的是2.4目前最新版

(1)安装yum源

–查看安装包

可以看到类似信息

(2)安装依赖包,此步需注意,官网文档没有写明需要安装依赖包,不过博主亲测Percona XtraBackup 2.4不安装依赖包会报错

(3)安装percona-xtrabackup-24 还需要libev,而libev需要在EPEL中获取,启用EPEL软件库(点击查看安装EPEL)

安装

看到类似信息,libev安装完成

(4)安装percona-xtrabackup-24

可以看到类似信息

Percona XtraBackup 2.4 安装完成

二、Percona XtraBackup使用

XtraBackup由以下5个工具组成,官网描述如下

innobackupex is the symlink for xtrabackup. innobackupex still supports all features and syntax as 2.2 version did, but is now deprecated and will be removed in next major release.
xtrabackup compiled C binary that provides functionality to backup a whole MySQL database instance with MyISAM, InnoDB, andXtraDB tables.
xbcrypt utility used for encrypting and decrypting backup files.(对备份文件加解密)
xbstream utility that allows streaming and extracting files to/from the xbstream format.(使用流压缩备份)
xbcloud utility used for downloading and uploading full or part of xbstream archive from/to cloud.

1、innobackupex备份与恢复

innobackupex 可以理解为用Perl封装的xtrabackup,在以前的版本中配合xtrabackup完成全量及增量的备份,最新版本中xtrabackup更强大,可以实现innobackupex的所有功能。甚至官网有如下说明 innobackupex still supports all features and syntax as 2.2 version did, but is now deprecated and will be removed in next major release.(innobackupex(v2.4) 仍然支持v2.2的所有功能和语法,不过已被废弃,并将在下一主要版本移除)

(1)innobackupex 热备完整备份,–no-timestamp 表示不创建时间点子目录,不加此选项表示创建时间点子目录,类似2016-04-28_16-41-29,–parallel 表示线程。
innobackupex –user=user –password=password  /data/backup/hotbackup/base –no-timestamp –parallel=2

增量备份,仅对基于Innodb或者使用Xtradb引擎的表进行增量备份,其他存储引擎会执行全备
innobackupex –user=user –password=password –incremental /data/backup/hotbackup/inc_1 –incremental-basedir=/data/backup/hotbackup/base  –no-timestamp –parallel=2

(2)innobackupex恢复
恢复准备基础备份,–apply-log选项时 可选–use-memory参数 表示使用多少内存,不超内存可用值时,越大越快

执行后看到类似信息继续

增量备份添加到基础备份

执行后可看到类似信息

注:当准备基础备份和合并增量备份(最后一个增量备份除外)的时候,需要使用–redo-only参数.
当所有的增量备份合并完成之后,再一次准备全备份,回滚未提交的事务,并生成ib_logfile*等文件

首先清空mysql的数据目录,并关闭,service mysqld stop
将备份文件还原到数据目录,修改文件属组并启动

 

2、xtrabackup备份与恢复

官网安装文档,2.4版已经支持MyISAM。备份分为(1)创建完全备份 (2)创建增量备份 (3)

创建备份目录

mkdir -p /data/backups/

(1)创建完全备份,–datadir会从mysql默认配置文件中读取

可看到如下信息

completed OK! 即完整备份完成

(2)创建增量备份,–target-dir是增量备份存放路径,–incremental-basedir是前一步完整备份路径

可看到如下信息

completed OK!  增量备份完成。

此时查看文件夹大小,可以发现增量备份比完整备份小

也可以查看备份文件中的xtrabackup_checkpoints,有详细说明

 

(3)Prepare,将增量备份文件添加到完整备份

首先,Prepare the base backup

看到 completed OK!后,继续下一步增量备份文件添加到完整备份

出现 completed OK! 后,再次Prepare完整备份,Prepare the whole backup to be ready to use

此步骤会生成ib_logfile* (重做日志文件)和ibtmp1(临时表空间)

信息如下

此时恢复文件准备完成。

(4)复制到mysql数据目录下,并修改文件属组

关闭数据库

备份之前的目录,如果有必要

使用rsync或者cp命令复制到mysql数据目录下

cp -r /data/backups/base/* /var/lib/mysql/

修改属组并重启

 

Xtrabackup是一款支持MyISAM、InnoDB、XtraDB,并能在线热备,全备,增量备份非常强大备份工具,备份速度接近IO,且事务不丢失。

mysqldump是mysql自带的于转存储数据库的备份工具,它主要产生一个SQL脚本,结合binlog日志,可以实现基本的增量备份。Innodb使用–single-transaction 选项,相当于一个快照,保证备份的一致性。

如果数据量非常大可以采用Xtrabackup,采用每周进行一次全备,剩下6天增量备份的策略,数据量小于几十G时,mysqldump的速度也可以接受,且mysqldump产生的sql脚本可以直观阅读,灵活性更好。两者属于互补关系,并不是替代。因此需要根据实际情况制定备份策略,简单适用即可。

 

 

 

Tips:

(1)关闭selinux

有次使用innobackupex时偶然遇到如下报错,mysql无法启动,百思不得其解,最后发现是selinux惹的祸,关闭selinux即可

 

查看selinux

临时关闭

永久关闭selinux
编辑/etc/selinux/config,找到SELINUX 行(SELINUX=enforcing)修改成为:SELINUX=disabled 后重启
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing – SELinux security policy is enforced.
# permissive – SELinux prints warnings instead of enforcing.
# disabled – No SELinux policy is loaded.
SELINUX=enforcing
# SELINUXTYPE= can take one of these two values:
# targeted – Only targeted network daemons are protected.
# strict – Full SELinux protection.
SELINUXTYPE=targeted

(2)log_timestamps,变量说明

细心的童鞋会发现,上一个tip中,mysql错误日志中时间如下2016-04-28T02:16:18.136334Z,而博主当时的服务器时间是2016-04-28 10:16 左右,这是为何呢?

通过文档发现Mysql 5.7.2后新增log_timestamps,日志的时间格式,默认UTC,世界协调时间(UTC)和格林威治时间(GMT)处于同个时区零时区(中时区),比北京时间(CST,东八区) 刚好晚了8个小时

As of MySQL 5.7.2, the log_timestamps system variable controls the timestamp time zone of messages written to the error log (as well as to general query log and slow query log files). Permitted values are UTC (the default) and SYSTEM (local system time zone). Before MySQL 5.7.2, messages use the local system time zone.

查看log_timestamps

目前只有两个值UTC和SYSTEM

Introduced 5.7.2
Command-Line Format --log_timestamps=#
System Variable Name log_timestamps
Variable Scope Global
Dynamic Variable Yes
Permitted Values Type enumeration
Default UTC
Valid Values UTC
SYSTEM

在配置文件my.cnf中[mysqld]中添加如下行,即记录本地时间

(3)*.pem

对比下xtrabackup复制的文件,可以发现并没有备份mysql目录下的*.pem文件,那这些文件有什么用呢?

使用show variables like ‘%ssl%’; 查看可以发现这些文件是用来进行加密连接的。

MySQL 5.7版本下默认就会使用SSL的方式来进行连接,远程连接是可以用\s 进行查看,类似SSL: Cipher in use is DHE-RSA-AES256-SHA

更详细的说明http://www.2cto.com/database/201512/453098.html
官网使用文档http://dev.mysql.com/doc/refman/5.7/en/creating-ssl-rsa-files-using-mysql.html

Mongodb 小试 php连接操作Mongodb

Tim阅读(2476)评论(0)

最近Mysql新加入了X plugin,可以将Mysql扩展为文件数据库存储数据,使用X Protocol协议连接,当基于ACID的存储引擎时可以实现事务等特性,提供接近NoSQL的操作,命令类似Mongodb,博主之前没接触过Mongodb,因此安装来小试下,Mysql X plugin的介绍可以查看上一篇文章

(二) Mysql5.7 原生JSON格式支持 新增X plugin

MongoDB 是由C++语言编写的,是一个基于分布式文件存储的开源数据库系统。
在高负载的情况下,添加更多的节点,可以保证服务器性能。
MongoDB 旨在为WEB应用提供可扩展的高性能数据存储解决方案。
MongoDB 将数据存储为一个文档,数据结构由键值(key=>value)对组成。MongoDB 文档类似于 JSON 对象。字段值可以包含其他文档,数组及文档数组。

Mongodb 小试 php连接操作Mongodb

 

NoSQL(NoSQL = Not Only SQL ),意即”不仅仅是SQL”。

在mongodb中基本的概念是文档、集合、数据库

SQL术语/概念 MongoDB术语/概念 解释/说明
database database 数据库
table collection 数据库表/集合
row document 数据记录行/文档
column field 数据字段/域
index index 索引
table joins 表连接,MongoDB不支持
primary key primary key 主键,MongoDB自动将_id字段设置为主键

Mongodb 小试 php连接操作Mongodb

一、安装Mongodb

博主使用的Centos 6.5 64bit,继续使用yum安装,官方安装文档https://docs.mongodb.org/master/tutorial/install-mongodb-on-red-hat/

(1)添加yum源

vi /etc/yum.repos.d/mongodb-org-3.2.repo

添加如下,并退出保存

(2)安装

(3)创建数据目录

(4)启动

安装过程很简单,下载文件有点慢

(4)使用

使用/usr/bin/mongo进入命令行

进行简单的js计算

创建数据库使用use命令,show dbs显示数据库(需要里面有数据才会显示),db显示当前数据库

创建数据表,也可以直接插入数据数据表会一起创建,Mongodb里表对应的是collection集合

显示集合

使用db.collection_name.insert(); 插入一行数据,db.collection_name.find(); 查看数据,可以看到由键值(key=>value)对组成,类似于 JSON 对象。

其他的CRUD等操作可以到官网找到相应命令https://docs.mongodb.org/master/tutorial/insert-documents/

也可以看看这个入门教程http://www.runoob.com/mongodb/mongodb-create-database.html

二、PHP远程连接Mongodb

Mongodb出来几年了,已经有不少公司应用到了生产环境,因此也支持编程语言:RUBY,PYTHON,JAVA,C++,PHP,C#等多种语言。

博主使用PHP远程连接Mongodb,环境为Win7 64bit,Wamp集成环境Apache/2.4.17 (Win64) PHP/5.6.16

(1)安装Wamp3

Wamp3集成环境下载地址:Wampserver (64 bits & PHP 5.6.15 & PHP 7) 3

下载后先安装VC 2012和2015库,否则会报VCRUNTIME140.dll和其他dll不存在导致Wamp不能正常运行

Wamp官网安装需要提示安装VC 2011地址是http://www.microsoft.com/en-us/download/details.aspx?id=30679 ,然而进去下载的是VC 2012,按版本我下载的VSU_4\vcredist_x64.exe (大约6.85MB),然而安装完依然报错,Ps:  这这这Apache不是很严谨啊,之前安装Hadoop也是各种不愉快。(⊙o⊙)…

直到安装上VC 2015 ,vc_redist.x64.exe (大约13.9MB),才正常安装运行,VC 2015地址:http://www.microsoft.com/en-us/download/details.aspx?id=48145    或者直接在微软主页搜索Visual C++ Redistributable for Visual Studio 2015。也有可能其实只需要安装VC 2015 ,Wamp官方给的地址是VC2012 update的,读者可以自行尝试,安装过程中不提示缺少.dll,之后能正常运行就行

Wamp3安装完成

Mongodb 小试 php连接操作Mongodb

(2)添加php_mongo.dll扩展

Win系统内使用.dll ,Linux使用.so ,下载地址http://pecl.php.net/package/mongo
扩展一般会区分non thread safe (非线程安全)和thread safe(线程安全)区别就在于,非线程安全一般搭配IIS环境使用,线程安全搭配apache使用
以Win7 64bit,Php5.6为例下载5.6 Thread Safe (TS) x64

Mongodb 小试 php连接操作Mongodb

Mongodb 小试 php连接操作Mongodb

下载好以后打开压缩包我们会发现php_mongo.dll文件,将此文件复制到C:\wamp64\bin\php\php5.6.16\ext  (默认,如果修改过路径自行替换)
打开从Wamp里打开php.ini 在最底下添加一行  extension=php_mongo.dll
之后退出Wamp,重新打开查看phpinfo();  能看到mongo模块,添加成功

Mongodb 小试 php连接操作Mongodb

参考文档wamp环境PHP安装mongodb扩展

(3)php连接Mongodb

首先在远程的Centos 上配置Mongodb

屏蔽如下一行,保存退出
#bindIp: 127.0.0.1  # Listen to local interface only, comment to listen on all interfaces.

重启Mongodb

可以看到0.0.0.0:27017

配置iptables或者关闭防火墙

在Win7 上Wamp运行目录 C:\wamp64\www 下新建testmongodb.php

内容如下,MongoClient类文档http://www.php.net/MongoClient.construct

执行成功

Mongodb 小试 php连接操作Mongodb

进入命令行可以看到

之后可以使用其他内置函数进行操作http://us3.php.net/manual/en/book.mongo.php

可以看到Mysql的X plugin基本上和Mongodb功能一致,X plugin目前刚推出还没看到有编程语言支持,相关的文档也不多见,加以时日,或许能看到X plugin商用

(二) Mysql5.7 原生JSON格式支持 新增X plugin

Tim阅读(3777)评论(0)

一、原生JSON格式支持

JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式。 易于人阅读和编写,同时也易于机器解析和生成(一般用于提升网络传输速率)。JSON的格式非常简单:名称/键值。之前MySQL版本里面要实现这样的存储,通常使用VARCHAR或者TEXT。 并且由程序进行解析,之前MariaDB也有对非结构化的数据进行存储的方案,称为dynamic column,但是方案是通过BLOB类型的方式来存储。查询性能不高,不能有效建立索引。有兴趣的同学可以了解下dynamic column     通过MARIADB的DYNAMIC COLUMN功能实现非结构化存储

现在Mysql5.7.9(GA)InnoDB存储引擎已经原生支持JSON格式,该格式不是简单的BLOB类似的替换。原生的JSON格式支持有以下的优势:

(1)JSON数据有效性检查:BLOB类型无法在数据库层做这样的约束性检查
(2)查询性能的提升:查询不需要遍历所有字符串才能找到数据
(3)支持索引:通过虚拟列的功能可以对JSON中的部分数据进行索引

建表

将列data定义为了JSON类型。这意味着我们可以对插入的数据做JSON格式检查,确保其符合JSON格式的约束,如插入一条不合法的JSON数据会报错

插入一行,使用内置的json_object转换成JSON格式

此时jsontest表内数据如下

使用json_extract解析出来

可以看到json_object,json_extract类似php里的json_encode,json_decode; 同时Mysql还提供了合并多个Json格式的json_merge,移除元素的json_remove等

详见http://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

最令人的激动的功能应该是MySQL 5.7的虚拟列功能,通过传统的B+树索引即可实现对JSON格式部分属性的快速查询。使用方法是首先创建该虚拟列,然后在该虚拟列上创建索引:

为虚拟列添加索引

通过explain可以看到优化器已经选择了在虚拟列上创建的新索引。

 

二、X plugin存储文件数据

在Mysql最新发布的5.7.12(GA)上发布了X plugin,官方解释如下,大意为X plugin可以将Mysql扩展为文件数据库存储数据,X Plugin使用X Protocol协议连接,当基于ACID的存储引擎时可以实现事务等特性,提供接近NoSQL的操作。

X Plugin extends MySQL Server to be able to function as a document store. Running X Plugin enables MySQL Server to communicate with clients using the X Protocol, which is designed to expose the ACID compliant storage abilities of MySQL as a document store. Documents are stored in JSON format and enable schema-less storage. Using the X DevAPI you can use a NoSQL-like syntax to execute Create, Read, Update, Delete (CRUD) operations against these documents.

1、安装mysql-shell,官方安装文档

If you already have the MySQL Yum repository as a software repository on your system and the repository was configured with the new release package mysql57-community-release, skip to the next step (“Enable the MySQL Tools Preview subrepository…”).

看到如上这段,因为我之前装Mysql5.7时使用了MySQL Yum repository,因此直接到Enable the MySQL Tools Preview subrepository这一段,读者可以根据自行情况安装。

vi /etc/yum.repos.d/mysql-community.repo 添加如下段,并将修改enabled=1

安装mysql-shell

安装完成,可以看到Mysql shell 版本为1.0.3
此时在运行show plugins; 也可以看到

2、连接

第一次使用mysqlsh -u root -h localhost -p –classic –dba enableXProtocol 进行初始化,密码为Mysql的密码

mysqlsh默认端口为33060
使用mysqlsh –u root 连接,进入mysql-js命令行模式

\h可以查看帮助,\sql可以进入sql模式

 

导入示例库,world_x-db.zip下载地址http://downloads.mysql.com/docs/world_x-db.zip

解压后有两个文件,查看README.txt中有执行命令,写的很详细,看到需要MySQL Server 5.7.12 or higher

导入world_x-db.zip示例库

进入world_x示例库

查看数据

创建schema,可以看到在js模式创建会报错

需要切换到sql模式,不过sql模式需要运行在Node session,因此mysqlsh –session-type=node -u root -p world_x重新连接并进入sql模式

切换到testx

创建collection

 

切换回world_x示例库,这里数据多点,接下来在world_x内操作

db.CountryInfo.add()添加数据,要注意,输入完成后需要回车两次 

db.CountryInfo.find()查看

查找符合条件的,引号内还可以使用算术符号等,如db.CountryInfo.find(“GNP*1000000/demographics.Population > 30000”)

.bind关联多个字段条件,.fields([“GNP”, “Name”])显示选择字段,.limit(N)返回N行结果,.sort([“IndepYear desc”]) 排序 等,

更多增删改查可以参阅官方文档Documents and Collections,文档还有介绍Relational Tables,接近Mysql中的表操作。

感兴趣的同学可以切换到sql模式,查看下数据库testx下id的表结构

可以看到doc是json字段存储,id列索引是利用了5.7新特性虚拟列,为_id创建了一个唯一约束并存储到磁盘,利用的innodb引擎,这就可以让collection支持事物行锁等innodb引擎的特性。X plugin利用独有的协议可以模拟类似mongodb等No-sql的操作,很强大的功能。

通过.ibd文件恢复Innodb单表数据 percona-data-recovery-tool-for-innodb使用

Tim阅读(3093)评论(0)

使用共享表空间存储方式时,Innodb的所有数据保存在一个单独的表空间里面(ibdata)innodb-file-per-table = 1 表示设置每个表单独存放, 现在innodb默认为1,在innodb-file-per-table = 1的时候Mysql Innodb表物理文件由.frm和.ibd文件组成,.frm存放表结构,元数据; .ibd文件存放的是表数据,Mysql数据目录下ibdata 是共享表空间,存放Innodb表的一些信息比如space id、index id,还有未提交的undo页等

ib_logfile* 是事务日志或称redo日志,数据库crash时用于恢复

mysql-bin*,bin* 存放的是二进制日志,可以用来point-in-time 恢复(需要设置log-bin)

网上有很多文档写了使用.frm恢复表结构,但楼主实测并不能单独使用.frm恢复innodb的表结构,只能在有ibdata文件的情况下,恢复表结构,也就是冷备。

单独使用.frm恢复innodb的表结构先留空,等以后看到好方法了再补上。

使用.ibd文件恢复表数据

一、使用alter table tablename discard/import tablespace,相当于重新建立关系,较简单

本文测试使用的user.ibd下载

现在有一个user.ibd 文件,表结构为

新建一个数据库(测试环境上)

使用如上user建表语句建表;之后进入相应文件夹可以看到user.ibd文件

有兴趣的小伙伴可以用16进制查看user.ibd文件0000024-25和0000028-29内的数据,我的是0006,如红框内,这个是innodb表的id

通过.ibd文件恢复Innodb单表数据 percona-data-recovery-tool-for-innodb使用

进入mysql将user表的表空间废弃

此时数据目录下没有了user.ibd

然后将要还原的.ibd文件拷贝到该目录下,并更改文件属主

可以看下需要还原user.ibd中的表id,可以看到为0027

通过.ibd文件恢复Innodb单表数据 percona-data-recovery-tool-for-innodb使用

 

现在我们进入mysql,导入表数据,有一条警告信息不是本文重点,读者可自行研究

至此表还原成功

此时可以在看user.ibd中的表id,发现变成了0006,与最初建表一致。

通过.ibd文件恢复Innodb单表数据 percona-data-recovery-tool-for-innodb使用

 

vi中查看16进制使用    :%!xxd 查看,安装xxd  yum -y install vim-common

 

此方法亲测在Mysql5.6,5.7上均可成功,如跨版本,须使表的行格式一致,否则会报错,查看innodb表行格式可以使用show table status like ‘user’;

 

二、使用percona-data-recovery-tool-for-innodb 恢复.ibd 文件

在某些情况下上面的可能恢复不了,这个时候可以用到这个工具,percona-data-recovery-tool-for-innodb用来从InnoDB的数据文件(即.ibd)恢复丢失或损坏的数据,由Mysql三大分支之一的Percona的创建并开源。

1、安装percona-data-recovery-tool-for-innodb

percona-data-recovery-tool-for-innodb下载

也可以使用wget下载percona-data-recovery-tool-for-innodb-0.5/mysql-source目录

安装依赖包,否则在./configure 和  ./create_defs.pl 步骤会报错,perl包会比较多

解压并进入

在mysql-source执行

configure完后到上层目录percona-data-recovery-tool-for-innodb-0.5/下make

percona-data-recovery-tool-for-innodb  安装完成

安装参考文档http://blog.itpub.net/22664653/viewspace-771993/

2、percona-data-recovery-tool-for-innodb恢复.ibd数据

本文测试使用的user.ibd下载

现在我们有了安装好的恢复软件,和一个需要恢复的.ibd文件,剩下就是恢复了

(1)首先依然是创建数据库和表结构,此时表内数据为空

(2)使用percona-data-recovery-tool-for-innodb分解.ibd数据页

可以看到类似信息,最后在目录下生产一个类似pages-1460629302的目录 这里面就是从.ibd文件分解的innodb页

(3)获取表结构在,percona-data-recovery-tool-for-innodb-0.5/目录下执行,需要能连接的数据库

执行成功后

有兴趣的同学可以查看下table_defs.h文件,可以看到相应字段信息

获得表结构后返回percona-data-recovery-tool-for-innodb-0.5/目录下执行make

执行 ./constraints_parser  -5 -f pages-1460629302/FIL_PAGE_INDEX/0-63/ > /tmp/user.txt
首先可以看下帮助

如果用来还原删除可以加-D,-5 是compact行格式,-f 页文件,需要恢复全部数据,pages-1460629302,因此执行命令:

pages-1460629302/FIL_PAGE_INDEX/0-63/ 读者可以自行进目录查看0-x,不同的.ibd大小是不一样的

会生成/tmp/user.txt 文件和一条命令

查看文件内容,已经可以看到数据,成功提取数据了

之后将./constraints_parser步骤产生load file命令中,路径替换成/tmp/user.txt后在mysql执行

成功还原数据

参考文档https://www.percona.com/blog/2012/02/20/how-to-recover-deleted-rows-from-an-innodb-tablespace/

本文仅抛砖引玉,如果你有更好的方法或发现本文存在纰漏欢迎在评论区回复

引申阅读(以上操作建议在测试环境中尝试,尝试前可先冷备,如有问题用冷备文件覆盖还原,这样就可以重复尝试)

MySQL:如何从ibd文件中恢复数据 http://blog.csdn.net/ylqmf/article/details/7229562
Innodb单表数据物理恢复 http://cenalulu.github.io/mysql/innodb-single-tablespace-recovery/
frm+ibd文件还原data http://my.oschina.net/sansom/blog/179116?fromerr=tWcPc1bf

注:以上链接有部分楼主进行尝试并不能成功,甚至还有按教程损坏ibdata1的情况,因此读者可自行尝试

 

 

(一) Mysql5.7 SYS Schema

Tim阅读(2150)评论(0)

MySQL4.1 提供了information_schema 数据字典。从此可以很简单的用SQL语句来检索需要的系统元数据了。
MySQL5.5 提供了performance_schema 性能字典。 但是这个字典比较专业,一般人可能也就看看就不了了之了。

sys schema是MySQL 5.7.7中引入的一个系统库,包含了一系列的视图、存储过程、自定义函数,以及来帮助我们快速的了解系统的元数据信息, 用于提升MySQL的易用性,例如,我们可以通过sys schema快速的知道,哪些语句使用了临时表,哪个用户请求了最多的io,哪个线程占用了最多的内存,哪些索引是无用索引等。

sys schema结合了information_schema和performance_schema的相关数据,让我们更加容易的检索元数据。

官网详细说明

可以通过以下语句快速查看sys schema包含的视图、函数和存储过程

 

关于带不带x$,去掉x$同名的视图他们的数据是相同的,区别在于不带x$的单位更加符合直接阅读经过了转换,而带x$是为了某些工具存在而使用的原始单位(多数应该是mysql默认的)