• Archive by category "数据库"
  • (Page 2)

Blog Archives

Neo4j集群安装实践

前言

“图”一直是计算机算法研究的重要领域,但由于图的复杂性缺少成熟的产品。直到社交网络的普及,社会化关系网和复杂网络的更入研究,大家对于“图数据库”有了更强烈的需求。图数据库产品也趋向成熟,慢慢地走进了大家的视野。

Neo4j就是图数据库的代表之作。

关于作者:

  • 张丹(Conan), 程序员Java,R,PHP,Javascript
  • weibo:@Conan_Z
  • blog: http://blog.fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/nosql-neo4j-intro/

neo4j-intro

目录

  1. Neo4j简介
  2. Neo4j单机安装
  3. 创建一个简单的社交关系图
  4. Neo4j集群安装HA

1. Neo4j简介

Neo4j是一个用Java实现的、高性能的、NoSQL图形数据库。Neo4j 使用图(graph)相关的概念来描述数据模型,通过图中的节点和节点的关系来建模。Neo4j完全兼容ACID的事务性。Neo4j以“节点空间”来表达领域数据,相对于传统的关系型数据库的表、行和列来说,节点空间可以更好地存储由节点关系和属性构成的网络,如社交网络,朋友圈等。

由Neo4j构建“图”模型,也可以准确表达 数据库模型,key-value模型,文档模型的数据关系。

2. Neo4j单机安装

系统环境

  • Linux: Ubuntu 12.04.2 LTS 64bit Server
  • SUN Java: 1.6.0_29 64-Bit

下载neo4j-enterprise,企业版


~ wget http://dist.neo4j.org/neo4j-enterprise-1.9.4-unix.tar.gz
~ tar xvf neo4j-enterprise-1.9.4-unix.tar.gz
~ mv neo4j-enterprise-1.9.4 neo4j194
~ mv neo4j194/ /home/conan/toolkit/
~ cd /home/conan/toolkit/neo4j194

配置Neo4j服务器允许远程访问,修改neo4j-server.properties


~ vi conf/neo4j-server.properties

#取消注释
org.neo4j.server.webserver.address=0.0.0.0

启动Neo4j


~ bin/neo4j start
WARNING: Max 1024 open files allowed, minimum of 40 000 recommended. See the Neo4j manual.
WARNING! You are using an unsupported version of the Java runtime. Please use Oracle(R) Java(TM) Runtime Environment 7.
Using additional JVM arguments:  -server -XX:+DisableExplicitGC -Dorg.neo4j.server.properties=conf/neo4j-server.properties -Djava.util.logging.config.file=conf/logging.properties -Dlog4j.configuration=file:conf/log4j.properties -XX:+UseConcMarkSweepGC -XX:+CMSClassUnloadingEnabled
Starting Neo4j Server...WARNING: not changing user
process [2408]... waiting for server to be ready....... OK.
Go to http://localhost:7474/webadmin/ for administration interface.

通过浏览器打开Neo4j的web控制台, http://192.168.1.201:7474/webadmin/

neo4j-webadmin

非常简单地,完成了单机的Neo4j的安装。

3. 创建一个简单的社交关系图

neo4j-relations-1


~ bin/neo4j-shell

neo4j-sh (?)$ CREATE (A {id:1,name:'A'}), (B {id:2,name:'B'}), (C {id:3,name:'C'}), (D {id:4,name:'D'}),(A)-[:knows]->(B),(A)-[:knows]->(C),(B)-[:knows]->(D),(D)-[:knows]->(A);
+-------------------+
| No data returned. |
+-------------------+
Nodes created: 4
Relationships created: 4
Properties set: 8

neo4j-sh (?)$ START n=node(*) RETURN n;
+-------------------------+                                                           | n                       |
+-------------------------+
| Node[49]{name:"A",id:1} |
| Node[50]{name:"B",id:2} |
| Node[51]{name:"C",id:3} |
| Node[52]{name:"D",id:4} |
+-------------------------+
4 rows

通过控制台以图形展示

neo4j-graph

删除数据节点和关系


neo4j-sh (?)$ START n=node(*) MATCH n-[r]-() DELETE n, r;
+-------------------+
| No data returned. |
+-------------------+
Nodes deleted: 4
Relationships deleted: 4

neo4j-sh (?)$ START n=node(*) RETURN n;
+---+
| n |
+---+
+---+
0 row

4. Neo4j集群安装HA

模拟Neo4j的高可用:Neo4j企业版本才提供高可用性功能。

Neo4j HA主要提供以下两个功能:

  • 容错数据库架构 保存多个数据副本,即使硬件故障,也能保证可读写。
  • 水平方向扩展以读为主架构 读操作负载均衡。

Neo4j HA模式总有单个master,零个或多个slave。与其他ms复制架构,Neo4j HA的slave可以处理写操作,而无需重定向写入到master。

neo4j-ha

Neo4j的集群需要复制多份Neo4j的环境,我们这里准备构建3个节点


~ mkdir /home/conan/neo4j
~ cd /home/conan/neo4j
~ cp -R /home/conan/toolkit/neo4j194 /home/conan/neo4j
~ mv neo4j194/ n1
~ cp -R n1 n2
~ cp -R n1 n3
~ ls 
n1  n2  n3

分别修改各节点的配置文件

  • neo4j.properties
  • neo4j-server.properties
  • neo4j-server.properties

n1节点


~ vi n1/conf/neo4j.properties

ha.server_id=1
ha.server=127.0.0.1:6361
online_backup_server=127.0.0.1:6362
ha.cluster_server=127.0.0.1:5001
ha.initial_hosts=127.0.0.1:5001,127.0.0.1:5002,127.0.0.1:5003

~ vi n1/conf/neo4j-server.properties

org.neo4j.server.webserver.port=7474
org.neo4j.server.webserver.https.port=7473
org.neo4j.server.database.mode=HA

~ vi n1/conf/neo4j-wrapper.conf

wrapper.java.additional.4=-Dcom.sun.management.jmxremote.port=3637
wrapper.java.additional.5=-Dcom.sun.management.jmxremote.password.file=conf/jmx.password
wrapper.java.additional.6=-Dcom.sun.management.jmxremote.access.file=conf/jmx.access

n2节点


~ vi n2/conf/neo4j.properties

ha.server_id=2
ha.server=127.0.0.1:6363
online_backup_server=127.0.0.1:6364
ha.cluster_server=127.0.0.1:5002
ha.initial_hosts=127.0.0.1:5001,127.0.0.1:5002,127.0.0.1:5003

~ vi n2/conf/neo4j-server.properties

org.neo4j.server.webserver.port=7476
org.neo4j.server.webserver.https.port=7475
org.neo4j.server.database.mode=HA

~ vi n1/conf/neo4j-wrapper.conf

wrapper.java.additional.4=-Dcom.sun.management.jmxremote.port=3638
wrapper.java.additional.5=-Dcom.sun.management.jmxremote.password.file=conf/jmx.password
wrapper.java.additional.6=-Dcom.sun.management.jmxremote.access.file=conf/jmx.access

n3节点


~ vi n3/conf/neo4j.properties

ha.server_id=3
ha.server=127.0.0.1:6365
online_backup_server=127.0.0.1:6366
ha.cluster_server=127.0.0.1:5003
ha.initial_hosts=127.0.0.1:5001,127.0.0.1:5002,127.0.0.1:5003

~ vi n3/conf/neo4j-server.properties

org.neo4j.server.webserver.port=7478
org.neo4j.server.webserver.https.port=7478
org.neo4j.server.database.mode=HA

~ vi n1/conf/neo4j-wrapper.conf

wrapper.java.additional.4=-Dcom.sun.management.jmxremote.port=3639
wrapper.java.additional.5=-Dcom.sun.management.jmxremote.password.file=conf/jmx.password
wrapper.java.additional.6=-Dcom.sun.management.jmxremote.access.file=conf/jmx.access

分别启动3个节点:


~ n1/bin/neo4j start
~ n2/bin/neo4j start
~ n3/bin/neo4j start

~ jps
5033 Bootstrapper
4073 StartClient
5546 Jps
5393 Bootstrapper
5219 Bootstrapper

命令行curl访问测试:


curl -H "Content-Type:application/json" -d '["org.neo4j:*"]' http://localhost:7474/db/manage/server/jmx/query

打开浏览器控制台webadmin
http://192.168.1.201:7474/webadmin/#/info/org.neo4j/High%20Availability/

neo4j-cluster

这样就完成了 Neo4j集群对于高可用安装实践!

转载请注明出处:
http://blog.fens.me/nosql-neo4j-intro/

打赏作者

Hive学习路线图

Hadoop家族系列文章,主要介绍Hadoop家族产品,常用的项目包括Hadoop, Hive, Pig, HBase, Sqoop, Mahout, Zookeeper, Avro, Ambari, Chukwa,新增加的项目包括,YARN, Hcatalog, Oozie, Cassandra, Hama, Whirr, Flume, Bigtop, Crunch, Hue等。

从2011年开始,中国进入大数据风起云涌的时代,以Hadoop为代表的家族软件,占据了大数据处理的广阔地盘。开源界及厂商,所有数据软件,无一不向Hadoop靠拢。Hadoop也从小众的高富帅领域,变成了大数据开发的标准。在Hadoop原有技术基础之上,出现了Hadoop家族产品,通过“大数据”概念不断创新,推出科技进步。

作为IT界的开发人员,我们也要跟上节奏,抓住机遇,跟着Hadoop一起雄起!

关于作者:

  • 张丹(Conan), 程序员Java,R,PHP,Javascript
  • weibo:@Conan_Z
  • blog: http://blog.fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/hadoop-hive-roadmap/

hadoop-hive-roadmap-title

前言

Hive是Hadoop家族中一款数据仓库产品,Hive最大的特点就是提供了类SQL的语法,封装了底层的MapReduce过程,让有SQL基础的业务人员,也可以直接利用Hadoop进行大数据的操作。就是这一个点,解决了原数据分析人员对于大数据分析的瓶颈。

让我们把Hive的环境构建起来,帮助非开发人员也能更好地了解大数据。

目录

  1. Hive介绍
  2. Hive学习路线图
  3. 我的使用经历
  4. Hive的使用案例

1. Hive介绍

Hive起源于Facebook,它使得针对Hadoop进行SQL查询成为可能,从而非程序员也可以方便地使用。Hive是基于Hadoop的一个数据仓库工具,可以将结构化的数据文件映射为一张数据库表,并提供完整的SQL查询功能,可以将SQL语句转换为MapReduce任务运行。

Hive是建立在 Hadoop 上的数据仓库基础构架。它提供了一系列的工具,可以用来进行数据提取转化加载(ETL),这是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制。Hive 定义了简单的类 SQL 查询语言,称为 HQL,它允许熟悉 SQL 的用户查询数据。同时,这个语言也允许熟悉 MapReduce 开发者的开发自定义的 mapper 和 reducer 来处理内建的 mapper 和 reducer 无法完成的复杂的分析工作。

详细地Hive的安装和使用介绍,请参考文章:Hive安装及使用攻略

2. Hive学习路线图

hadoop-hive-roadmap

Hive的知识点,我已经列在图中,希望帮助其他人更好的了解Hive。

接下来,是我的使用经历,谁都没有捷径。把心踏实下来,就不那么难了。

3. 我的使用经历

我使用Hive有两个考虑:

  • 1. 帮助无开发经验的数据分析人员,有能力处理大数据
  • 2. 构建标准化的MapReduce开发过程

1). 帮助无开发经验的数据分析人员,有能力处理大数据

完全符合与Hive的设计理念,一直在强调,无需多言。

2). 构建标准化的MapReduce开发过程

这个方面是我们需要努力的方向。

首先,Hive已经用类SQL的语法封装了MapReduce过程,这个封装过程就是MapReduce的标准化的过程。

我们在做业务或者工具时,会针对场景用逻辑封装,这是第二层封装是在Hive之上的封装。在第二层封装时,我们要尽可能多的屏蔽Hive的细节,让接口单一化,低少灵活性,再次精简HQL的语法结构。只满足我们的系统要求,专用的接口。

在使用二次封装的接口时,我们已经可以不用知道Hive是什么, 更不用知道Hadoop是什么。我们只需要知道,SQL查询(SQL92标准),怎么写效率高,怎么写可以完成业务需要就可以了。

当我们完成了Hive的二次封装后,我们可以构建标准化的MapReduce开发过程。

hive-architect-2

通过上图的思路,我们可以统一企业内部各种应用对于Hive的依赖,并且当人员素质升高后,有可以剥离Hive,用更优秀的底层解决方案来替换,如果封装的接口的不变,甚至替换Hive时业务使用都不知道,我们已经替换了Hive。

这个过程是需要经历的,也是有意义的。当我在考虑构建Hadoop分析工具时,以Hive作为Hadoop访问接口是最有效的。

3). 有关Hive的运维:
因为Hive是基于Hadoop构建的,简单地说就是一套Hadoop的访问接口,Hive本身并没有太多的东西,所以运维上面我们注意下面几个问题就行了。

  • 1. 使用单独的数据库存储元数据
  • 2. 定义合理的表分区和键
  • 3. 设置合理的bucket数据量
  • 4. 进行表压缩
  • 5. 定义外部表使用规范
  • 6. 合理的控制Mapper, Reducer数量

4. Hive的使用案例

已经整理成文章的案例

相关文章:
Hadoop家族产品学习路线图

转载请注明出处:
http://blog.fens.me/hadoop-hive-roadmap/

打赏作者

RMySQL数据库编程指南

R的极客理想系列文章,涵盖了R的思想,使用,工具,创新等的一系列要点,以我个人的学习和体验去诠释R的强大。

R语言作为统计学一门语言,一直在小众领域闪耀着光芒。直到大数据的爆发,R语言变成了一门炙手可热的数据分析的利器。随着越来越多的工程背景的人的加入,R语言的社区在迅速扩大成长。现在已不仅仅是统计领域,教育,银行,电商,互联网….都在使用R语言。

要成为有理想的极客,我们不能停留在语法上,要掌握牢固的数学,概率,统计知识,同时还要有创新精神,把R语言发挥到各个领域。让我们一起动起来吧,开始R的极客理想。

关于作者:

  • 张丹(Conan), 程序员Java,R,PHP,Javascript
  • weibo:@Conan_Z
  • blog: http://blog.fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/r-mysql-rmysql/

r-rmysql

前言

MySQL是一款最常用到开源数据库软件,安装简单,运行稳定,非常适用于中小型的数据存储。R作为数据分析的工具,当然要支持数据库驱动接口。让R和MySQL配合在一起,所能爆发出的能量是巨大的。

由于操作系统的原因,让Win和Linux有不一样的字符集,不一样的运行时环境。所以,今天我们讲一下如何在Linux和Win上面安装和使用RMySQL。

目录

  1. RMySQL介绍
  2. RMySQL在Linux下安装
  3. RMySQL在Win7下安装
  4. RMySQL函数使用
  5. RMySQL案例实践

1. RMySQL介绍

RMySQL一个R语言程序包,提供了访问MySQL数据库的R语言接口程序,RMySQL需求依赖于DBI项目。RMySQL不仅提供了基本的数据库访问,SQL查询,还封装了一些方法。比较读整表,分页,data.frame快速插入等等的功能。掌握好RMySQL,数据库编辑将得心应手!!

2. RMySQL在Linux下安装

Linux系统环境:

  • Linux: Ubuntu 12.04.2 LTS 64bit server
  • Linux字符集: en_US.UTF-8
  • R: 3.0.1, x86_64-pc-linux-gnu (64-bit)
  • MySQL: Ver 14.14 Distrib 5.5.29 64bit server
  • MySQL字符集: utf8

~ uname -a
Linux conan 3.5.0-23-generic #35~precise1-Ubuntu SMP Fri Jan 25 17:13:26 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux

~ cat /etc/issue
Ubuntu 12.04.2 LTS \n \l

~ locale
LANG=en_US.UTF-8
LANGUAGE=
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=en_US.UTF-8

~ R --version
R version 3.0.1 (2013-05-16) -- "Good Sport"
Copyright (C) 2013 The R Foundation for Statistical Computing
Platform: x86_64-pc-linux-gnu (64-bit)

R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under the terms of the
GNU General Public License versions 2 or 3.
For more information about these matters see
http://www.gnu.org/licenses/.

~ mysql --version
mysql  Ver 14.14 Distrib 5.5.29, for debian-linux-gnu (x86_64) using readline 6.2

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

在R环境中安装RMySQL


~ R
> install.packages('RMySQL')

also installing the dependency ‘DBI’

trying URL 'http://cran.dataguru.cn/src/contrib/DBI_0.2-7.tar.gz'
Content type 'application/x-gzip' length 194699 bytes (190 Kb)
opened URL
==================================================
downloaded 190 Kb

trying URL 'http://cran.dataguru.cn/src/contrib/RMySQL_0.9-3.tar.gz'
Content type 'application/x-gzip' length 165363 bytes (161 Kb)
opened URL
==================================================
downloaded 161 Kb

...

Configuration error:
  could not find the MySQL installation include and/or library
  directories.  Manually specify the location of the MySQL
  libraries and the header files and re-run R CMD INSTALL.

INSTRUCTIONS:

1. Define and export the 2 shell variables PKG_CPPFLAGS and
   PKG_LIBS to include the directory for header files (*.h)
   and libraries, for example (using Bourne shell syntax):

      export PKG_CPPFLAGS="-I"
      export PKG_LIBS="-L -lmysqlclient"

   Re-run the R INSTALL command:

      R CMD INSTALL RMySQL_.tar.gz

2. Alternatively, you may pass the configure arguments
      --with-mysql-dir= (distribution directory)
   or
      --with-mysql-inc= (where MySQL header files reside)
      --with-mysql-lib= (where MySQL libraries reside)
   in the call to R INSTALL --configure-args='...'

   R CMD INSTALL --configure-args='--with-mysql-dir=DIR' RMySQL_.tar.gz

ERROR: configuration failed for package ‘RMySQL’
* removing ‘/home/conan/R/x86_64-pc-linux-gnu-library/3.0/RMySQL’

The downloaded source packages are in
        ‘/tmp/Rtmpu0Gn88/downloaded_packages’
Warning message:
In install.packages("RMySQL") :
  installation of package ‘RMySQL’ had non-zero exit status

安装出错了,提示我们需要增加MySQL安装目录的配置参数


# 安装mysql类库 
~ sudo apt-get install libdbd-mysql libmysqlclient-dev

# 找到mysql的安装目录
~ whereis mysql
mysql: /usr/bin/mysql /etc/mysql /usr/lib/mysql /usr/bin/X11/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz

# 找到刚刚下载的RMySQL_.tar.gz
~ ls /tmp/Rtmpu0Gn88/downloaded_packages
DBI_0.2-7.tar.gz  RMySQL_0.9-3.tar.gz

# 通过命令安装RMySQL
~ R CMD INSTALL --configure-args='--with-mysql-dir=/usr/lib/mysql' /tmp/Rtmpu0Gn88/downloaded_packages/RMySQL_0.9-3.tar.gz

* installing to library ‘/home/conan/R/x86_64-pc-linux-gnu-library/3.0’
* installing *source* package ‘RMySQL’ ...
** package ‘RMySQL’ successfully unpacked and MD5 sums checked
checking for gcc... gcc
checking for C compiler default output file name... a.out
checking whether the C compiler works... yes
checking whether we are cross compiling... no
checking for suffix of executables...
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ANSI C... none needed
checking how to run the C preprocessor... gcc -E
checking for compress in -lz... yes
checking for getopt_long in -lc... yes
checking for mysql_init in -lmysqlclient... yes
checking for egrep... grep -E
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking mysql.h usability... no
checking mysql.h presence... no
checking for mysql.h... no
checking /usr/local/include/mysql/mysql.h usability... no
checking /usr/local/include/mysql/mysql.h presence... no
checking for /usr/local/include/mysql/mysql.h... no
checking /usr/include/mysql/mysql.h usability... yes
checking /usr/include/mysql/mysql.h presence... yes
checking for /usr/include/mysql/mysql.h... yes
configure: creating ./config.status
config.status: creating src/Makevars
** libs
gcc -std=gnu99 -I/usr/share/R/include -DNDEBUG -I/usr/include/mysql     -fpic  -O3 -pipe  -g  -c RS-DBI.c -o RS-DBI.o
gcc -std=gnu99 -I/usr/share/R/include -DNDEBUG -I/usr/include/mysql     -fpic  -O3 -pipe  -g  -c RS-MySQL.c -o RS-MySQL.o
gcc -std=gnu99 -shared -o RMySQL.so RS-DBI.o RS-MySQL.o -lmysqlclient -lz -L/usr/lib/R/lib -lR
installing to /home/conan/R/x86_64-pc-linux-gnu-library/3.0/RMySQL/libs
** R
** inst
** preparing package for lazy loading
Creating a generic function for ‘format’ from package ‘base’ in package ‘RMySQL’
Creating a generic function for ‘print’ from package ‘base’ in package ‘RMySQL’
** help
*** installing help indices
** building package indices
** installing vignettes
** testing if installed package can be loaded
* DONE (RMySQL)

RMySQL安装成功.

在MySQL中建库建表


~ mysql -uroot -p

mysql> create database rmysql;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on rmysql.* to rmysql@'%' identified by 'rmysql';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on rmysql.* to rmysql@localhost identified by 'rmysql';
Query OK, 0 rows affected (0.00 sec)

mysql> use rmysql
Database changed

mysql> CREATE TABLE t_user(
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> user varchar(12) NOT NULL UNIQUE
    -> )ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t_user(user) values('A1'),('AB'),('fens.me');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t_user;
+----+---------+
| id | user    |
+----+---------+
|  1 | A1      |
|  2 | AB      |
|  3 | fens.me |
+----+---------+
3 rows in set (0.00 sec)

通过R程序,读MySQL数据库数据


~ R

> library(RMySQL)
Loading required package: DBI

> conn <- dbConnect(MySQL(), dbname = "rmysql", username="rmysql", password="rmysql")
> users = dbGetQuery(conn, "SELECT * FROM t_user")
> dbDisconnect(conn)
[1] TRUE
> users
  id    user
1  1      A1
2  2      AB
3  3 fens.me

好了,我们实现了在Linux下R和MySQL的连接。

3. RMySQL在Win7下安装

Win系统环境:

  • Win7: 64位 旗舰版
  • Win字符集: gbk,utf8
  • R: 3.0.1, x86_64-w64-mingw32/x64 (64-bit)
  • MySQL: mysql Ver 14.14 Distrib 5.6.11, for Win64 (x86_64)

~ R --version
R version 3.0.1 (2013-05-16) -- "Good Sport"
Copyright (C) 2013 The R Foundation for Statistical Computing
Platform: x86_64-w64-mingw32/x64 (64-bit)

R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under the terms of the
GNU General Public License versions 2 or 3.
For more information about these matters see
http://www.gnu.org/licenses/.

~ mysql --version
mysql  Ver 14.14 Distrib 5.6.11, for Win64 (x86_64)

mysql> show variables like '%char%';
+--------------------------+------------------------------------+
| Variable_name            | Value                              |
+--------------------------+------------------------------------+
| character_set_client     | gbk                                |
| character_set_connection | gbk                                |
| character_set_database   | utf8                               |
| character_set_filesystem | binary                             |
| character_set_results    | gbk                                |
| character_set_server     | utf8                               |
| character_set_system     | utf8                               |
| character_sets_dir       | D:\toolkit\mysql56\share\charsets\ |
+--------------------------+------------------------------------+
8 rows in set (0.07 sec)

在R环境中安装RMySQL


~ D:\workspace\R\mysql>R

> install.packages('RMySQl')
package 'RMySQl' is not available (for R version 3.0.1)

我们看到提示,没有对应的RMySQL安装版本。


# 下载RMySQL源代码包
> install.packages("RMySQL", type="source")
URLhttp://cran.dataguru.cn/src/contrib/RMySQL_0.9-3.tar.gz'
Content type 'application/x-gzip' length 165363 bytes (161 Kb)
URL
downloaded 161 Kb

* installing *source* package 'RMySQL' ...
** 'RMySQL'MD5
checking for $MYSQL_HOME... not found... searching registry...

cygwin warning:
  MS-DOS style path detected: C:/PROGRA~1/R/R-30~1.1/bin/x64/Rscript
  Preferred POSIX equivalent is: /cygdrive/c/PROGRA~1/R/R-30~1.1/bin/x64/Rscript
  CYGWIN environment variable option "nodosfilewarning" turns off this warning.
  Consult the user's guide for more details about POSIX paths:
    http://cygwin.com/cygwin-ug-net/using.html#using-pathnames
readRegistry("SOFTWARE\\MySQL AB", hive = "HLM", maxdepth = 2) :
  Registry key 'SOFTWARE\MySQL AB' not found

ERROR: configuration failed for package 'RMySQL'
* removing 'C:/Program Files/R/R-3.0.1/library/RMySQL'
        'C:\Users\Administrator\AppData\Local\Temp\RtmpsfqQjK\downloaded_packages'

In install.packages("RMySQL", type = "source") :
  'RMySQL'B0

找到源代码包:RMySQL_0.9-3.tar.gz


~ dir C:\Users\Administrator\AppData\Local\Temp\RtmpsfqQjK\downloaded_packages
2013-09-24  13:16           165,363 RMySQL_0.9-3.tar.gz

通过源代码包安装


~ D:\workspace\R\mysql>R CMD INSTALL C:\Users\Administrator\AppData\Local\Temp\RtmpsfqQjK\downloaded_packages\RMySQL_0.9-3
.tar.gz
* installing to library 'C:/Program Files/R/R-3.0.1/library'
* installing *source* package 'RMySQL' ...
** 'RMySQL'MD5
checking for $MYSQL_HOME... not found... searching registry...

cygwin warning:
  MS-DOS style path detected: C:/PROGRA~1/R/R-30~1.1/bin/x64/Rscript
  Preferred POSIX equivalent is: /cygdrive/c/PROGRA~1/R/R-30~1.1/bin/x64/Rscript
  CYGWIN environment variable option "nodosfilewarning" turns off this warning.
  Consult the user's guide for more details about POSIX paths:
    http://cygwin.com/cygwin-ug-net/using.html#using-pathnames
readRegistry("SOFTWARE\\MySQL AB", hive = "HLM", maxdepth = 2) :
  Registry key 'SOFTWARE\MySQL AB' not found

ERROR: configuration failed for package 'RMySQL'
* removing 'C:/Program Files/R/R-3.0.1/library/RMySQL'

设置MYSQL_HOME的环境变量


set MYSQL_HOME=D:\toolkit\mysql56

注: MYSQL_HOME建议设置在系统环境变量中。

再一次安装RMySQL


D:\workspace\R\mysql>R CMD INSTALL C:\Users\Administrator\AppData\Local\Temp\RtmpsfqQjK\downloaded_packages\RMySQL_0.9-3
.tar.gz
* installing to library 'C:/Program Files/R/R-3.0.1/library'
* installing *source* package 'RMySQL' ...
** 'RMySQL'MD5
checking for $MYSQL_HOME... D:\toolkit\mysql56
cygwin warning:
  MS-DOS style path detected: D:\toolkit\mysql56
  Preferred POSIX equivalent is: /cygdrive/d/toolkit/mysql56
  CYGWIN environment variable option "nodosfilewarning" turns off this warning.
  Consult the user's guide for more details about POSIX paths:
    http://cygwin.com/cygwin-ug-net/using.html#using-pathnames
** libs
: this package has a non-empty 'configure.win' file,
so building only the main architecture

cygwin warning:
  MS-DOS style path detected: C:/PROGRA~1/R/R-30~1.1/etc/x64/Makeconf
  Preferred POSIX equivalent is: /cygdrive/c/PROGRA~1/R/R-30~1.1/etc/x64/Makeconf
  CYGWIN environment variable option "nodosfilewarning" turns off this warning.
  Consult the user's guide for more details about POSIX paths:
    http://cygwin.com/cygwin-ug-net/using.html#using-pathnames
gcc -m64 -I"C:/PROGRA~1/R/R-30~1.1/include" -DNDEBUG -I"D:\toolkit\mysql56"/include    -I"d:/RCompile/CRANpkg/extralibs6
4/local/include"     -O2 -Wall  -std=gnu99 -mtune=core2 -c RS-DBI.c -o RS-DBI.o
RS-DBI.c: In function 'RS_na_set':
RS-DBI.c:1219:11: warning: variable 'c' set but not used [-Wunused-but-set-variable]
gcc -m64 -I"C:/PROGRA~1/R/R-30~1.1/include" -DNDEBUG -I"D:\toolkit\mysql56"/include    -I"d:/RCompile/CRANpkg/extralibs6
4/local/include"     -O2 -Wall  -std=gnu99 -mtune=core2 -c RS-MySQL.c -o RS-MySQL.o
RS-MySQL.c: In function 'RS_MySQL_fetch':
RS-MySQL.c:657:13: warning: variable 'fld_nullOk' set but not used [-Wunused-but-set-variable]
RS-MySQL.c: In function 'RS_DBI_invokeBeginGroup':
RS-MySQL.c:1137:30: warning: variable 'val' set but not used [-Wunused-but-set-variable]
RS-MySQL.c: In function 'RS_DBI_invokeNewRecord':
RS-MySQL.c:1158:20: warning: variable 'val' set but not used [-Wunused-but-set-variable]
RS-MySQL.c: In function 'RS_MySQL_dbApply':
RS-MySQL.c:1219:38: warning: variable 'fld_nullOk' set but not used [-Wunused-but-set-variable]
gcc -m64 -shared -s -static-libgcc -o RMySQL.dll tmp.def RS-DBI.o RS-MySQL.o D:\toolkit\mysql56/bin/libmySQL.dll -Ld:/RC
ompile/CRANpkg/extralibs64/local/lib/x64 -Ld:/RCompile/CRANpkg/extralibs64/local/lib -LC:/PROGRA~1/R/R-30~1.1/bin/x64 -l
R
gcc.exe: error: D:\toolkit\mysql56/bin/libmySQL.dll: No such file or directory
ERROR: compilation failed for package 'RMySQL'
* removing 'C:/Program Files/R/R-3.0.1/library/RMySQL'

错误为没有找到动态链接库:D:\toolkit\mysql56/bin/libmySQL.dll


# 复制动态链接库libmySQL.dll
cp D:\toolkit\mysql56\lib\libmysql.dll D:\toolkit\mysql56\bin\
mv D:\toolkit\mysql56\bin\libmysql.dll D:\toolkit\mysql56\bin\libmySQL.dll

再一次安装RMySQL


~ D:\workspace\R\mysql>R CMD INSTALL C:\Users\Administrator\AppData\Local\Temp\RtmpsfqQjK\downloaded_packages\RMySQL_0.9-3
.tar.gz
* installing to library 'C:/Program Files/R/R-3.0.1/library'
* installing *source* package 'RMySQL' ...
** 'RMySQL'MD5
checking for $MYSQL_HOME... D:\toolkit\mysql56
cygwin warning:
  MS-DOS style path detected: D:\toolkit\mysql56
  Preferred POSIX equivalent is: /cygdrive/d/toolkit/mysql56
  CYGWIN environment variable option "nodosfilewarning" turns off this warning.
  Consult the user's guide for more details about POSIX paths:
    http://cygwin.com/cygwin-ug-net/using.html#using-pathnames
** libs
: this package has a non-empty 'configure.win' file,
so building only the main architecture

cygwin warning:
  MS-DOS style path detected: C:/PROGRA~1/R/R-30~1.1/etc/x64/Makeconf
  Preferred POSIX equivalent is: /cygdrive/c/PROGRA~1/R/R-30~1.1/etc/x64/Makeconf
  CYGWIN environment variable option "nodosfilewarning" turns off this warning.
  Consult the user's guide for more details about POSIX paths:
    http://cygwin.com/cygwin-ug-net/using.html#using-pathnames
gcc -m64 -I"C:/PROGRA~1/R/R-30~1.1/include" -DNDEBUG -I"D:\toolkit\mysql56"/include    -I"d:/RCompile/CRANpkg/extralibs6
4/local/include"     -O2 -Wall  -std=gnu99 -mtune=core2 -c RS-DBI.c -o RS-DBI.o
RS-DBI.c: In function 'RS_na_set':
RS-DBI.c:1219:11: warning: variable 'c' set but not used [-Wunused-but-set-variable]
gcc -m64 -I"C:/PROGRA~1/R/R-30~1.1/include" -DNDEBUG -I"D:\toolkit\mysql56"/include    -I"d:/RCompile/CRANpkg/extralibs6
4/local/include"     -O2 -Wall  -std=gnu99 -mtune=core2 -c RS-MySQL.c -o RS-MySQL.o
RS-MySQL.c: In function 'RS_MySQL_fetch':
RS-MySQL.c:657:13: warning: variable 'fld_nullOk' set but not used [-Wunused-but-set-variable]
RS-MySQL.c: In function 'RS_DBI_invokeBeginGroup':
RS-MySQL.c:1137:30: warning: variable 'val' set but not used [-Wunused-but-set-variable]
RS-MySQL.c: In function 'RS_DBI_invokeNewRecord':
RS-MySQL.c:1158:20: warning: variable 'val' set but not used [-Wunused-but-set-variable]
RS-MySQL.c: In function 'RS_MySQL_dbApply':
RS-MySQL.c:1219:38: warning: variable 'fld_nullOk' set but not used [-Wunused-but-set-variable]
gcc -m64 -shared -s -static-libgcc -o RMySQL.dll tmp.def RS-DBI.o RS-MySQL.o D:\toolkit\mysql56/bin/libmySQL.dll -Ld:/RC
ompile/CRANpkg/extralibs64/local/lib/x64 -Ld:/RCompile/CRANpkg/extralibs64/local/lib -LC:/PROGRA~1/R/R-30~1.1/bin/x64 -l
R
installing to C:/Program Files/R/R-3.0.1/library/RMySQL/libs/x64
** R
** inst
** preparing package for lazy loading
Creating a generic function for 'format' from package 'base' in package 'RMySQL'
Creating a generic function for 'print' from package 'base' in package 'RMySQL'
** help
*** installing help indices
** building package indices
** installing vignettes
** testing if installed package can be loaded
MYSQL_HOME defined as D:\toolkit\mysql56
* DONE (RMySQL)

安装成功!

在MySQL中建库建表


~ mysql -uroot -p

mysql> create database rmysql;
Query OK, 1 row affected (0.04 sec)

mysql> grant all on rmysql.* to rmysql@'%' identified by 'rmysql';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on rmysql.* to rmysql@localhost identified by 'rmysql';
Query OK, 0 rows affected (0.00 sec)

mysql> use rmysql
Database changed
mysql> CREATE TABLE t_user(
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> user varchar(12) NOT NULL UNIQUE
    -> )ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (1.01 sec)

mysql>
mysql> INSERT INTO t_user(user) values('A1'),('AB'),('fens.me');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t_user;
+----+---------+
| id | user    |
+----+---------+
|  1 | A1      |
|  2 | AB      |
|  3 | fens.me |
+----+---------+
3 rows in set (0.03 sec)

通过R程序,读MySQL数据库数据。

注:如果刚才没有把MYSQL_HOME的变量写到环境变更中,每次在启动R之前要,先设置变量。


~ set MYSQL_HOME=D:\toolkit\mysql56
~ R

> library(RMySQL)
DBI
MYSQL_HOME defined as D:\toolkit\mysql56

> conn <- dbConnect(MySQL(), dbname = "rmysql", username="root", password="",client.flag=CLIENT_MULTI_STATEMENTS)
> users = dbGetQuery(conn, "SELECT * FROM t_user")
> dbDisconnect(conn)
[1] TRUE
> users
  id    user
1  1      A1
2  2      AB
3  3 fens.me

好了,我们实现了在Win7下R和MySQL的连接。

4. RMySQL函数使用

环境都安装好了,接下来我们具体使用一下RMySQL的包。

  • RMySQL辅助操作
  • RMySQL数据库操作
  • 针对win的字符集设置

1). RMySQL辅助操作

加载类库

> library(RMySQL)

建立本地连接


> conn <- dbConnect(MySQL(), dbname = "rmysql", username="rmysql", password="rmysql",client.flag=CLIENT_MULTI_STATEMENTS)

建立远程连接


> conn <- dbConnect(MySQL(), dbname = "rmysql", username="rmysql", password="rmysql",host="192.168.1.201",port=3306)

关闭连接

dbDisconnect(conn)

查看数据库的表


> dbListTables(conn)
[1] "t_user"

查看表的字段


> dbListFields(conn, "t_user")
[1] "id"   "user"

查询MySQL信息


> summary(MySQL(), verbose = TRUE)
<MySQLDriver:(23864)> 
  Driver name:  MySQL 
  Max  connections: 16 
  Conn. processed: 3 
  Default records per fetch: 500 
  DBI API version:  

# MySQL连接实例信息
> summary(conn, verbose = TRUE)
<MySQLConnection:(23864,2)> 
  User: root 
  Host: localhost 
  Dbname: rmysql 
  Connection type: localhost via TCP/IP 
  MySQL server version:  5.6.11 
  MySQL client version:  5.6.11 
  MySQL protocol version:  10 
  MySQL server thread id:  35 
  No resultSet available

# MySQL连接信息
> dbListConnections(MySQL())
[[1]]
<MySQLConnection:(23864,2)> 

2). RMySQL数据库操作
RMySQL数据库操作


# 建表并插入数据
> t_demo<-data.frame(
  a=seq(1:10),
  b=letters[1:10],
  c=rnorm(10)
)
> dbWriteTable(conn, "t_demo", t_demo)

# 获得整个表数据
> dbReadTable(conn, "t_demo")
    a b           c
1   1 a  0.98868164
2   2 b -0.66935770
3   3 c  0.27703638
4   4 d  1.36137156
5   5 e -0.70291017
6   6 f  1.61235088
7   7 g  0.17616068
8   8 h  0.29700017
9   9 i  0.19032719
10 10 j -0.06222173

# 插入新数据
> dbWriteTable(conn, "t_demo", t_demo, append=TRUE)
> dbReadTable(conn, "t_demo")
   row_names  a b           c
1          1  1 a  0.98868164
2          2  2 b -0.66935770
3          3  3 c  0.27703638
4          4  4 d  1.36137156
5          5  5 e -0.70291017
6          6  6 f  1.61235088
7          7  7 g  0.17616068
8          8  8 h  0.29700017
9          9  9 i  0.19032719
10        10 10 j -0.06222173
11         1  1 a  0.98868164
12         2  2 b -0.66935770
13         3  3 c  0.27703638
14         4  4 d  1.36137156
15         5  5 e -0.70291017
16         6  6 f  1.61235088
17         7  7 g  0.17616068
18         8  8 h  0.29700017
19         9  9 i  0.19032719
20        10 10 j -0.06222173

# 覆盖原表数据
> dbWriteTable(conn, "t_demo", t_demo, overwrite=TRUE)

# 1). 查询数据
> d0 <- dbGetQuery(conn, "SELECT * FROM t_demo where c>0")
> class(d0)
[1] "data.frame"

> d0
  row_names a b         c
1         1 1 a 0.9886816
2         3 3 c 0.2770364
3         4 4 d 1.3613716
4         6 6 f 1.6123509
5         7 7 g 0.1761607
6         8 8 h 0.2970002
7         9 9 i 0.1903272

# 2). 执行SQL脚本查询,并分页
> rs <- dbSendQuery(conn, "SELECT * FROM t_demo where c>0")
> class(rs)
[1] "MySQLResult"
attr(,"package")
[1] "RMySQL"
> mysqlCloseResult(rs)
[1] TRUE

> d1 <- fetch(rs, n = 3)
> d1
  row_names a b         c
1         1 1 a 0.9886816
2         3 3 c 0.2770364
3         4 4 d 1.3613716

# 3). 查看集统计信息
> summary(rs, verbose = TRUE)
  row_names               a              b                   c         
 Length:7           Min.   :1.000   Length:7           Min.   :0.1762  
 Class :character   1st Qu.:3.500   Class :character   1st Qu.:0.2337  
 Mode  :character   Median :6.000   Mode  :character   Median :0.2970  
                    Mean   :5.429                      Mean   :0.7004  
                    3rd Qu.:7.500                      3rd Qu.:1.1750  
                    Max.   :9.000                      Max.   :1.6124

# 不插入row.names字段
> dbWriteTable(conn, "t_demo", t_demo,row.names=FALSE,overwrite=TRUE)
> dbGetQuery(conn, "SELECT * FROM t_demo where c>0")
  a b         c
1 1 a 0.9886816
2 3 c 0.2770364
3 4 d 1.3613716
4 6 f 1.6123509
5 7 g 0.1761607
6 8 h 0.2970002
7 9 i 0.1903272

# 删除表
> if(dbExistsTable(conn,'t_demo')){
+     dbRemoveTable(conn, "t_demo")
+ }
[1] TRUE

执行SQL语句,dbSendQuery


> query<-dbSendQuery(conn, "show tables")
> data <- fetch(query, n = -1)
> data
  Tables_in_rmysql
1           t_demo
2           t_user
> mysqlCloseResult(query)
[1] TRUE

4). win的字符集设置
在win7中,向MySQL插入中文


mysql> INSERT INTO t_user(user) values('小朋友'),('你好'),('正确了');
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_user;
+----+---------+
| id | user    |
+----+---------+
|  1 | A1      |
|  2 | AB      |
|  3 | fens.me |
|  5 | 你好    |
|  4 | 小朋友  |
|  6 | 正确了  |
+----+---------+
6 rows in set (0.07 sec)

通过RMySQL查询


> dbGetQuery(conn, "SELECT * FROM t_user")
  id    user
1  1      A1
2  2      AB
3  3 fens.me
4  5      ??
5  4     ???
6  6     ???

设置GKB字符集


> dbDisconnect(conn)
> conn <- dbConnect(MySQL(), dbname = "rmysql", username="root", password="",client.flag=CLIENT_MULTI_STATEMENTS)
> dbSendQuery(conn,'SET NAMES gbk')
 
> query<-dbSendQuery(conn, "SELECT * FROM t_user")
> data <- fetch(query, n = -1)
> mysqlCloseResult(query)
[1] TRUE
> data
  id    user
1  1      A1
2  2      AB
3  3 fens.me
4  5    你好
5  4  小朋友
6  6  正确了

OK,我们在win下面修正字符编号的问题。

5. RMySQL案例实践

系统需求描述:Linux MySQL,Win7的R环境,远程连接

  • 1. 通过SQL新建表t_blog,主键索引,唯一键索引
  • 2. 用RMySQL插入数据,包括中文字段
  • 3. 再用RMySQL取出数据

1). 通过SQL新建表t_blog,主键索引,唯一键索引
建表语句


CREATE TABLE t_blog(
id INT PRIMARY KEY AUTO_INCREMENT,
title varchar(12) NOT NULL UNIQUE,
author varchar(12) NOT NULL, 
length int NOT NULL,
create_date timestamp NOT NULL DEFAULT now()
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

mysql> desc t_blog;
+-------------+-------------+------+-----+-------------------+----------------+
| Field       | Type        | Null | Key | Default           | Extra          |
+-------------+-------------+------+-----+-------------------+----------------+
| id          | int(11)     | NO   | PRI | NULL              | auto_increment |
| title       | varchar(12) | NO   | UNI | NULL              |                |
| author      | varchar(12) | NO   |     | NULL              |                |
| length      | int(11)     | NO   |     | NULL              |                |
| create_date | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
+-------------+-------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)

mysql> show indexes from t_blog;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_blog |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| t_blog |          0 | title    |            1 | title       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)


INSERT INTO t_blog(title,author,length) values('你好,第一篇','Conan',20),('RMySQL数据库编程','Conan',99),('R的极客理想系列文章','Conan',15);

mysql> select * from t_blog;
+----+------------------------------+--------+--------+---------------------+
| id | title                        | author | length | create_date         |
+----+------------------------------+--------+--------+---------------------+
|  1 | 你好,第一篇                 | Conan  |     20 | 2013-08-15 00:13:13 |
|  2 | RMySQL数据库编程             | Conan  |     99 | 2013-08-15 00:13:13 |
|  3 | R的极客理想系列文章          | Conan  |     15 | 2013-08-15 00:13:13 |
+----+------------------------------+--------+--------+---------------------+
3 rows in set (0.00 sec)

2). 用RMySQL插入数据,包括中文字段,再取出数据


> library(RMySQL)
> conn <- dbConnect(MySQL(), dbname = "rmysql", username="rmysql", password="rmysql",host="192.168.1.201",port=3306)
> 
> dbSendQuery(conn,'SET NAMES gbk')
 
> dbSendQuery(conn,"INSERT INTO t_blog(title,author,length) values('R插入的新文章','Conan',50)");
 
> 
> query<-dbSendQuery(conn, "SELECT * FROM t_blog")
Warning message:
In mysqlExecStatement(conn, statement, ...) :
  RS-DBI driver warning: (unrecognized MySQL field type 7 in column 4 imported as character)
> data <- fetch(query, n = -1)
> mysqlCloseResult(query)
[1] TRUE
> print(data)
  id               title author length         create_date
1  1        你好,第一篇  Conan     20 2013-08-15 00:13:13
2  2    RMySQL数据库编程  Conan     99 2013-08-15 00:13:13
3  3 R的极客理想系列文章  Conan     15 2013-08-15 00:13:13
4  4       R插入的新文章  Conan     50 2013-08-15 00:29:45
> 
> dbDisconnect(conn)
[1] TRUE

特别提示,不能用dbWriteTable函数!!

我们已经完成,掌握了RMySQL的各种使用技巧,希望大家理解原理后,能少犯错误,提高工作效率!

转载请注明出处:
http://blog.fens.me/r-mysql-rmysql/

打赏作者

用Nodejs连接MySQL

从零开始nodejs系列文章,将介绍如何利Javascript做为服务端脚本,通过Nodejs框架web开发。Nodejs框架是基于V8的引擎,是目前速度最快的Javascript引擎。chrome浏览器就基于V8,同时打开20-30个网页都很流畅。Nodejs标准的web开发框架Express,可以帮助我们迅速建立web站点,比起PHP的开发效率更高,而且学习曲线更低。非常适合小型网站,个性化网站,我们自己的Geek网站!!

关于作者

  • 张丹(Conan), 程序员Java,R,PHP,Javascript
  • weibo:@Conan_Z
  • blog: http://blog.fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/nodejs-mysql-intro/

nodejs-mysql

前言

MySQL是一款常用的开源数据库产品,通常也是免费数据库的首选。查了一下NPM列表,发现Nodejs有13库可以访问MySQL,felixge/node-mysql似乎是最受关注项目,我也决定尝试用一下。

要注意名字,”felixge/node-mysql”非”node-mysql”,安装部分会介绍这个小插曲!

目录

  1. node-mysql介绍
  2. 建立MySQL测试库
  3. node-mysql安装
  4. node-mysql使用

1. node-mysql介绍

felixge/node-mysql是一个纯nodejs的用javascript实现的一个MySQL客户端程序。felixge/node-mysql封装了Nodejs对MySQL的基本操作,100% MIT公共许可证。

项目地址:https://github.com/felixge/node-mysql

2. 建立MySQL测试库

本地创建MySQL测试库:nodejs


~ mysql -uroot -p
mysql> CREATE DATABASE nodejs;
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nodejs             |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql> GRANT ALL ON nodejs.* to nodejs@'%' IDENTIFIED BY 'nodejs';
mysql> GRANT ALL ON nodejs.* to nodejs@localhost IDENTIFIED BY 'nodejs';

重新登陆MySQL


C:\Users\Administrator>mysql -unodejs -p
Enter password: ******

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| nodejs             |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> USE nodejs
Database changed

新建一个user表


CREATE TABLE t_user(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(16) NOT NULL ,
create_date TIMESTAMP NULL DEFAULT now()
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE UNIQUE INDEX t_quiz_IDX_0 on t_user(name);

mysql> SHOW TABLES;
+------------------+
| Tables_in_nodejs |
+------------------+
| t_user           |
+------------------+
1 row in set (0.04 sec)

3. node-mysql安装

我的系统环境

  • win7 64bit
  • Nodejs:v0.10.5
  • Npm:1.2.19
  • MySQL:Server version: 5.6.11 MySQL Community Server (GPL)

创建工程:nodejs-node-mysql

~ D:\workspace\javascript>mkdir nodejs-node-mysql
~ D:\workspace\javascript>cd nodejs-node-mysql
~ D:\workspace\javascript\nodejs-node-mysql>npm install node-mysql
node-mysql@0.2.0 node_modules\node-mysql
├── better-js-class@0.1.2
├── cps@0.1.7
├── underscore@1.5.2
└── mysql@2.0.0-alpha9 (require-all@0.0.3, bignumber.js@1.0.1)

这里有一个小插曲

安装“node-mysql”后,打开package.json文件发现,这个项目地址是

https://github.com/redblaze/node-mysql.git

从依赖关系可以看到,它依赖于mysql库,是对felixge/node-mysql的封装。

node-mysql1

由于这个项目star是0,fork也是0. 所以,我也不准备花时间测试了,重新安装felixge/node-mysql的包。

重新安装node-mysql

~ D:\workspace\javascript\nodejs-node-mysql>rm -rf node_modules
~ D:\workspace\javascript\nodejs-node-mysql>npm install mysql@2.0.0-alpha9
npm http GET https://registry.npmjs.org/mysql/2.0.0-alpha9
npm http 200 https://registry.npmjs.org/mysql/2.0.0-alpha9
npm http GET https://registry.npmjs.org/mysql/-/mysql-2.0.0-alpha9.tgz
npm http 200 https://registry.npmjs.org/mysql/-/mysql-2.0.0-alpha9.tgz
npm http GET https://registry.npmjs.org/require-all/0.0.3
npm http GET https://registry.npmjs.org/bignumber.js/1.0.1
npm http 304 https://registry.npmjs.org/require-all/0.0.3
npm http 304 https://registry.npmjs.org/bignumber.js/1.0.1
mysql@2.0.0-alpha9 node_modules\mysql
├── require-all@0.0.3
└── bignumber.js@1.0.1

这回就对了,继续下面的开发!

创建node程序启动文件:app.js

第一个测试

~ vi app.js

var mysql = require('mysql');
var conn = mysql.createConnection({
    host: 'localhost',
    user: 'nodejs',
    password: 'nodejs',
    database:'nodejs',
    port: 3306
});
conn.connect();
conn.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
    if (err) throw err;
    console.log('The solution is: ', rows[0].solution);
});
conn.end();

运行node

~ D:\workspace\javascript\nodejs-node-mysql>node app.js
The solution is:  2

这样我们就让Nodejs连接上了MySQL。

4. node-mysql使用

下面我们要对node-mysql的API进行常用的测试。

  • 表新删改查
  • 连接池配置
  • MySQL断线重连
  • 连接池超时测试

1). 表新删改查
修改app.js


~ vi app.js

var mysql = require('mysql');
var conn = mysql.createConnection({
    host: 'localhost',
    user: 'nodejs',
    password: 'nodejs',
    database: 'nodejs',
    port: 3306
});
conn.connect();

var insertSQL = 'insert into t_user(name) values("conan"),("fens.me")';
var selectSQL = 'select * from t_user limit 10';
var deleteSQL = 'delete from t_user';
var updateSQL = 'update t_user set name="conan update"  where name="conan"';

//delete
conn.query(deleteSQL, function (err0, res0) {
    if (err0) console.log(err0);
    console.log("DELETE Return ==> ");
    console.log(res0);

    //insert
    conn.query(insertSQL, function (err1, res1) {
        if (err1) console.log(err1);
        console.log("INSERT Return ==> ");
        console.log(res1);

        //query
        conn.query(selectSQL, function (err2, rows) {
            if (err2) console.log(err2);

            console.log("SELECT ==> ");
            for (var i in rows) {
                console.log(rows[i]);
            }

            //update
            conn.query(updateSQL, function (err3, res3) {
                if (err3) console.log(err3);
                console.log("UPDATE Return ==> ");
                console.log(res3);

                //query
                conn.query(selectSQL, function (err4, rows2) {
                    if (err4) console.log(err4);

                    console.log("SELECT ==> ");
                    for (var i in rows2) {
                        console.log(rows2[i]);
                    }
                });
            });
        });
    });
});

//conn.end();

控制台输出:

D:\workspace\javascript\nodejs-node-mysql>node app.js
DELETE Return ==>
{ fieldCount: 0,
  affectedRows: 2,
  insertId: 0,
  serverStatus: 34,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }
INSERT Return ==>
{ fieldCount: 0,
  affectedRows: 2,
  insertId: 33,
  serverStatus: 2,
  warningCount: 0,
  message: '&Records: 2  Duplicates: 0  Warnings: 0',
  protocol41: true,
  changedRows: 0 }
SELECT ==>
{ id: 33,
  name: 'conan',
  create_date: Wed Sep 11 2013 12:09:15 GMT+0800 (中国标准时间) }
{ id: 34,
  name: 'fens.me',
  create_date: Wed Sep 11 2013 12:09:15 GMT+0800 (中国标准时间) }
UPDATE Return ==>
{ fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '(Rows matched: 1  Changed: 1  Warnings: 0',
  protocol41: true,
  changedRows: 1 }
SELECT ==>
{ id: 33,
  name: 'conan update',
  create_date: Wed Sep 11 2013 12:09:15 GMT+0800 (中国标准时间) }
{ id: 34,
  name: 'fens.me',
  create_date: Wed Sep 11 2013 12:09:15 GMT+0800 (中国标准时间) }

由于node的异步的,上面是一个连续的操作,代码会被写的支离破碎。我们可以通过async库对上面代码进行封装,请参考文章:Nodejs异步流程控制Async

2). 连接池配置

增加文件:app-pooling.js

~ vi app-pooling.js

var mysql = require('mysql');
var pool = mysql.createPool({
    host: 'localhost',
    user: 'nodejs',
    password: 'nodejs',
    database: 'nodejs',
    port: 3306
});

var selectSQL = 'select * from t_user limit 10';

pool.getConnection(function (err, conn) {
    if (err) console.log("POOL ==> " + err);

    conn.query(selectSQL,function(err,rows){
        if (err) console.log(err);
        console.log("SELECT ==> ");
        for (var i in rows) {
            console.log(rows[i]);
        }
        conn.release();
    });
});

控制台输出:

D:\workspace\javascript\nodejs-node-mysql>node app-pooling.js
SELECT ==>
{ id: 39,
  name: 'conan update',
  create_date: Wed Sep 11 2013 13:41:18 GMT+0800 (中国标准时间) }
{ id: 40,
  name: 'fens.me',
  create_date: Wed Sep 11 2013 13:41:18 GMT+0800 (中国标准时间) }

3). MySQL断线重连

分别模拟3种错误

  • 登陆密码错误
  • 数据库宕机
  • 数据库连接超时

新增文件:app-reconnect.js

~ vi app-reconnect.js

var mysql = require('mysql');
var conn;
function handleError () {
    conn = mysql.createConnection({
        host: 'localhost',
        user: 'nodejs',
        password: 'nodejs',
        database: 'nodejs',
        port: 3306
    });

    //连接错误,2秒重试
    conn.connect(function (err) {
        if (err) {
            console.log('error when connecting to db:', err);
            setTimeout(handleError , 2000);
        }
    });

    conn.on('error', function (err) {
        console.log('db error', err);
        // 如果是连接断开,自动重新连接
        if (err.code === 'PROTOCOL_CONNECTION_LOST') {
            handleError();
        } else {
            throw err;
        }
    });
}
handleError();

a. 模拟密码错误

修改password: ‘nodejs11’

控制台输出。

D:\workspace\javascript\nodejs-node-mysql>node app-reconnect.js
error when connecting to db: { [Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'nodejs'@'localhost' (using pass
rd: YES)]
  code: 'ER_ACCESS_DENIED_ERROR',
  errno: 1045,
  sqlState: '28000',
  fatal: true }
error when connecting to db: { [Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'nodejs'@'localhost' (using pass
rd: YES)]
  code: 'ER_ACCESS_DENIED_ERROR',
  errno: 1045,
  sqlState: '28000',
  fatal: true }

b. 模拟数据库宕机
正常启动node,然后杀掉mysqld的进程。

控制台输出。


D:\workspace\javascript\nodejs-node-mysql>node app-reconnect.js
db error { [Error: read ECONNRESET]
  code: 'ECONNRESET',
  errno: 'ECONNRESET',
  syscall: 'read',
  fatal: true }

Error: read ECONNRESET
    at errnoException (net.js:884:11)
    at TCP.onread (net.js:539:19)

这个异常,直接导致node程序被杀死!

c. 模拟连接超时,PROTOCOL_CONNECTION_LOST
切换到root账户, 修改MySQL的wait_timeout参数,设置为10毫秒超时。

~ mysql -uroot -p
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 10    |
+---------------+-------+
1 row in set (0.00 sec)

修改文件:app-reconnection.js,在最后增加代码

~ vi app-reconnection.js

function query(){
    console.log(new Date());
    var sql = "show variables like 'wait_timeout'";
    conn.query(sql, function (err, res) {
        console.log(res);
    });
}

query();
setInterval(query, 15*1000);

程序会每融15秒,做一次查询。

控制台输出

D:\workspace\javascript\nodejs-node-mysql>node app-reconnect.js
Wed Sep 11 2013 15:21:14 GMT+0800 (中国标准时间)
[ { Variable_name: 'wait_timeout', Value: '10' } ]
db error { [Error: Connection lost: The server closed the connection.] fatal: true, code: 'PROTOCOL_CONNECTION_LOST' }
Wed Sep 11 2013 15:21:28 GMT+0800 (中国标准时间)
[ { Variable_name: 'wait_timeout', Value: '10' } ]
db error { [Error: Connection lost: The server closed the connection.] fatal: true, code: 'PROTOCOL_CONNECTION_LOST' }
Wed Sep 11 2013 15:21:43 GMT+0800 (中国标准时间)
[ { Variable_name: 'wait_timeout', Value: '10' } ]

我们自己的程序捕获了“PROTOCOL_CONNECTION_LOST”异常,并自动的实现了数据库重连。

4). MySQL连接池的超时测试

针对wait_timeout问题,我们再对连接做一下测试。

修改app-pooling.js文件


var mysql = require('mysql');
var pool = mysql.createPool({
    host: 'localhost',
    user: 'nodejs',
    password: 'nodejs',
    database: 'nodejs',
    port: 3306
});

var selectSQL ="show variables like 'wait_timeout'";

pool.getConnection(function (err, conn) {
    if (err) console.log("POOL ==> " + err);

    function query(){
        conn.query(selectSQL, function (err, res) {
            console.log(new Date());
            console.log(res);
            conn.release();
        });
    }
    query();
    setInterval(query, 5000);
});

控制台输出:

D:\workspace\javascript\nodejs-node-mysql>node app-pooling.js
Wed Sep 11 2013 15:32:25 GMT+0800 (中国标准时间)
[ { Variable_name: 'wait_timeout', Value: '10' } ]
Wed Sep 11 2013 15:32:30 GMT+0800 (中国标准时间)
[ { Variable_name: 'wait_timeout', Value: '10' } ]
Wed Sep 11 2013 15:32:35 GMT+0800 (中国标准时间)
[ { Variable_name: 'wait_timeout', Value: '10' } ]

连接池,已经解决了自动重连的问题了,后面我们的开发,可以尽量使用pooling的方式。

转载请注明出处:
http://blog.fens.me/nodejs-mysql-intro/

打赏作者

[MySQL优化]为MySQL数据文件ibdata1瘦身

前言

MySQL在运行一段时间后,ibdata1的文件会增长大小,就算删除了表的数据,ibdata1的体积也不会减小。由于硬盘空间有限,这样一直膨胀下去磁盘空间接近崩溃。今天在导出数据的时候就发现了,磁盘竟然满了,明明预留了1个月的用量,1周就占满了,下面就要给ibdata1做个瘦身。

关于作者:

  • 张丹(Conan), 程序员Java,R,PHP,Javascript
  • weibo:@Conan_Z
  • blog: http://blog.fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/mysql-ibdata1/

mysql-ibdata1

目录

  1. 系统环境
  2. 发现问题
  3. 解决问题

1. 系统环境

Linux Ubuntu 13.04 64bit server

~ uname -a
Linux d2 3.8.0-21-generic #32-Ubuntu SMP Tue May 14 22:16:46 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
~ cat /etc/issue
Ubuntu 13.04 \n \l

MySQL: 5.5.31-0ubuntu0.13.04.1

~ mysql --version
mysql  Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (x86_64) using readline 6.2

硬盘:36G+4G+4G+36G


~ df -h
Filesystem                    Size  Used Avail Use% Mounted on
/dev/mapper/server3--vg-root   36G   31G  3.2G  91% /
none                          4.0K     0  4.0K   0% /sys/fs/cgroup
udev                          4.1G  1.1G  3.0G  26% /dev
tmpfs                         824M  280K  823M   1% /run
none                          5.0M     0  5.0M   0% /run/lock
none                          4.1G  3.4G  729M  83% /run/shm
none                          100M     0  100M   0% /run/user
/dev/vda1                     228M   30M  187M  14% /boot
192.168.1.10:/home/amg/data    36G   13G   21G  39% /home/amg/data

MySQL的ibdata1占用空间:20G


~ cd /var/lib/mysql
~ ls -l
drwxr-xr-x 2 mysql mysql        4096 Aug  2 19:38 CB
drwxr-xr-x 2 mysql mysql        4096 Jun 24 23:08 conan
drwxr-xr-x 2 mysql mysql        4096 Jun  2 00:52 dbwordpress
-rwxr-xr-x 1 root  root            0 May 23 00:48 debian-5.5.flag
-rwxr-xr-x 1 mysql mysql 20101201920 Aug  2 20:08 ibdata1
-rwxr-xr-x 1 mysql mysql     5242880 Aug  2 20:08 ib_logfile0
-rwxr-xr-x 1 mysql mysql     5242880 Aug  2 19:38 ib_logfile1
drwxr-xr-x 2 mysql mysql        4096 Jun 26 09:03 Macro
drwxr-xr-x 2 mysql root         4096 May 23 00:48 mysql
-rwxr-xr-x 1 root  root            6 May 23 00:48 mysql_upgrade_info
drwxr-xr-x 2 mysql mysql        4096 May 23 00:48 performance_schema
drwxr-xr-x 2 mysql mysql        4096 May 23 00:53 phpmyadmin
drwxr-xr-x 2 mysql root         4096 May 23 00:48 test
drwxr-xr-x 2 mysql mysql        4096 Jul 22 14:09 TF
drwxr-xr-x 2 mysql mysql        4096 Jun  2 01:04 wordpress

业务数据表


mysql> show tables;
+-----------------+
| Tables_in_CB    |
+-----------------+
| NSpremium       |
| cb_hft          |
| cb_hft_20130801 |
| cb_hft_20130802 |
+-----------------+
4 rows in set (0.00 sec)

2. 发现问题

ibdata1单个文件占用20G大小。
1. MySQL默认设置,没有按表空间分离数据,所有的表的数据都被放到ibdata1文件中。
2. 业务操作,每天会产生一张表cb_hft,晚上的时候对表进行重命名。


RENAME TABLE cb_hft TO cb_hft_20130801;
create table cb_hft like cb_hft_20130801;

3. 每周会把数据导出,同时drop表。但drop后,ibdata1不会减少,随着数据的积累ibdata1越来越大,根空间已经不够用了。

3. 解决问题

1). 导出数据
现在数据库中,有两个数据表,cb_hft_20130801,cb_hft_20130802,分别导出到/run/shm, /dev


~ cd /dev
~ mysqldump -uroot -p CB cb_hft_20130802 > export_cb_hft_20130802.sql

~ cd /run/shm
~ mysqldump -uroot -p CB cb_hft_20130801 > export_cb_hft_20130801.sql

~ df -h
Filesystem                    Size  Used Avail Use% Mounted on
/dev/mapper/server3--vg-root   36G   31G  3.2G  91% /
none                          4.0K     0  4.0K   0% /sys/fs/cgroup
udev                          4.1G  3.7G  368M  92% /dev
tmpfs                         824M  280K  823M   1% /run
none                          5.0M     0  5.0M   0% /run/lock
none                          4.1G  3.4G  729M  83% /run/shm
none                          100M     0  100M   0% /run/user
/dev/vda1                     228M   30M  187M  14% /boot
192.168.1.10:/home/amg/data    36G   13G   21G  39% /home/amg/data

两张表分别占了,3.4G,3.7G。

登陆mysql删除CB数据库


~ mysql -uroot -p
~ drop database CB

导出其他数据库数据


~ cd /run/shm
~ mysqldump -uroot -p -R -q --all-databases > others.sql

2). 修改配置文件/etc/mysql/my.cnf
对每张表使用单独的数据文件存储innodb_file_per_table

停止mysql服务器

~ sudo /etc/init.d/mysql stop

#清空所有数据文件
~ sudo rm -rf /var/lib/mysql/*

修改配置文件


~ sudo vi /etc/mysql/my.cnf

[mysqld]
innodb_file_per_table

重新构建数据库实例


~ /usr/bin/mysql_install_db

~ ls /var/lib/mysql
mysql  performance_schema  test

#启动MySQL
~ sudo /etc/init.d/mysql start

3). 恢复其他数据库


~ mysql < /run/shm/others.sql 
~ mysql -umysql -p 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Macro              |
| TF                 |
| conan              |
| dbwordpress        |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
| wordpress          |
+--------------------+
10 rows in set (0.01 sec)

#查看ibdata1大小
~ ls -l /var/lib/mysql
drwx------ 2 mysql mysql     4096 Aug  2 21:33 CB
drwx------ 2 mysql mysql     4096 Aug  2 21:23 conan
drwx------ 2 mysql mysql     4096 Aug  2 21:23 dbwordpress
-rw-rw---- 1 mysql mysql 18874368 Aug  2 21:34 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Aug  2 21:34 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Aug  2 21:34 ib_logfile1
drwx------ 2 mysql mysql     4096 Aug  2 21:23 Macro
drwx------ 2 mysql root      4096 Aug  2 21:23 mysql
drwx------ 2 mysql mysql     4096 Aug  2 21:19 performance_schema
drwx------ 2 mysql mysql     4096 Aug  2 21:23 phpmyadmin
drwx------ 2 mysql root      4096 Aug  2 21:19 test
drwx------ 2 mysql mysql     4096 Aug  2 21:23 TF
drwx------ 2 mysql mysql     4096 Aug  2 21:23 wordpress

4). 重置root密码


mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
mysql> FLUSH PRIVILEGES;

5). 恢复CB数据库


mysql> create database CB;
Query OK, 1 row affected (0.00 sec)

~  mysql --database CB < /run/shm/export_cb_hft_20130801.sql
~  mysql --database CB < /dev/export_cb_hft_20130802.sql
~  mysql --database CB < /dev/export_NSpremium.sql

#查看ibdata1大小:还是出初始值没有增长
~ ls -l /var/lib/mysql
drwx------ 2 mysql mysql     4096 Aug  2 21:33 CB
drwx------ 2 mysql mysql     4096 Aug  2 21:23 conan
drwx------ 2 mysql mysql     4096 Aug  2 21:23 dbwordpress
-rw-rw---- 1 mysql mysql 18874368 Aug  2 22:01 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Aug  2 22:01 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Aug  2 22:01 ib_logfile1
drwx------ 2 mysql mysql     4096 Aug  2 21:23 Macro
drwx------ 2 mysql root      4096 Aug  2 21:23 mysql
drwx------ 2 mysql mysql     4096 Aug  2 21:19 performance_schema
drwx------ 2 mysql mysql     4096 Aug  2 21:23 phpmyadmin
drwx------ 2 mysql root      4096 Aug  2 21:19 test
drwx------ 2 mysql mysql     4096 Aug  2 21:23 TF
drwx------ 2 mysql mysql     4096 Aug  2 21:23 wordpress

#查看CB库目录:所有的数据都保存在自己单独的数据文件
~ ls -l /var/lib/mysql/CB
-rw-rw---- 1 mysql mysql       9928 Aug  2 21:33 cb_hft_20130801.frm
-rw-rw---- 1 mysql mysql 7159676928 Aug  2 22:08 cb_hft_20130801.ibd
-rw-rw---- 1 mysql mysql       9928 Aug  2 22:09 cb_hft_20130802.frm
-rw-rw---- 1 mysql mysql 7805599744 Aug  2 22:38 cb_hft_20130802.ibd
-rw-rw---- 1 mysql mysql         61 Aug  2 21:30 db.opt

刚才设置的innodb_file_per_table参数已经起作用了,当我们再导出表drop后,对应的数据文件idb就会被删除,系统硬盘空间使用就会在正常值范围内。

查看表数据


mysql> show tables;
+-----------------+
| Tables_in_CB    |
+-----------------+
| cb_hft_20130801 |
| cb_hft_20130802 |
+-----------------+
2 rows in set (0.00 sec)

mysql> select count(1) from cb_hft_20130801;
+----------+
| count(1) |
+----------+
| 21063172 |
+----------+
1 row in set (1 min 1.46 sec)

#删除表
~ drop table cb_hft_20130801;

#查看数据文件
~ ls -l /var/lib/mysql/CB
-rw-rw---- 1 mysql mysql       9928 Aug  2 22:09 cb_hft_20130802.frm
-rw-rw---- 1 mysql mysql 7805599744 Aug  2 22:38 cb_hft_20130802.ibd
-rw-rw---- 1 mysql mysql         61 Aug  2 21:30 db.opt
-rw-rw---- 1 mysql mysql       9274 Aug  2 22:52 NSpremium.frm
-rw-rw---- 1 mysql mysql      98304 Aug  2 22:53 NSpremium.ibd

drop后,数据就一起被删除了。

经过对MySQL的调优,ibdata1已经被瘦身!数据库又可以继续正常的稳定的工作了。

转载请注明出处:
http://blog.fens.me/mysql-ibdata1/

打赏作者