MemSQL学习笔记-类似MySQL的数据库
来源：互联网 发布：淘宝产品首页营销图片 编辑：程序博客网 时间：2024/05/01 06:23
http://gigaom.com/cloud/ex-facebookers-launch-memsql-to-make-your-database-fly/
-- 多主-从
http://www.mysqlops.com/2012/02/14/diy_multi_master_replication.html
http://www.cnblogs.com/liuhao/archive/2012/06/26/2563702.html
前facebook员工和前微软sql server工程师联合搞的一个分布式关系数据库
全部内存运行，将sql转化成速度更快的c++, 原理类似HipHop
有mysql api, 完全兼容mysql,没有学习使用成本
速度是mysql的30倍，每秒可处理150万的事务
官方网站：http://memsql.com/
参考文档：http://space.itpub.net/7607759/viewspace-733545
参考官方文档：http://developers.memsql.com/docs/1b/
http://developers.memsql.com/docs/1b/
-- ==============================================================
cd memsqlbin
./check_system
Warning: The version of CentOS that you're using (5.5) is too low.
MemSQL supports versions 6.0 and up.
[root@banggo ~]#
wget http://ftp.gnu.org/pub/gnu/glibc/glibc-2.11.tar.gz
tar zxvf glibc-2.11.tar.gz
cd glibc-2.11
mkdir glibc-2.11-build
退出去在别的目录执行如下编译
/usr/src/glibc-2.11/configure --prefix=/usr/src/glibc-2.11/glibc-2.11-build/
error:
checking for .cfi_personality and .cfi_lsda pseudo-ops... no
configure: error: assembler too old, .cfi_personality support missing
【解决】
参考网址
http://groups.google.com/group/rocks-clusters/browse_thread/thread/4c9298adecf5a335
http://www.caritasem.com/?p=162
I couldn't overcome that problem, and I'm wondering how I can, but I
said, "hey cuda only requires glibc-2.7, so lets compile that". I went
ahead, and installed it into /share/apps/glibc-2.7. So here comes the
main question: how can I link CUDA C to the glibc-2.7 installed in
/share/apps ? Years ago, Tim Carlson has said "It is really not
practical to try and upgrade glibc to 2.5. You might want to try
installing a glibc 2.5 from source in /share/apps/glibc-2.5 and then
use LD_PRELOAD to load that library in for you CMAQ binary.", but I
don't know how to use LD_PRELOAD, and I would appreciate any
guideline.
[换成2.8]
[root@banggo ~]#
wget http://ftp.gnu.org/pub/gnu/glibc/glibc-2.8.tar.gz
tar zxvf glibc-2.8.tar.gz
cd glibc-2.8
mkdir glibc-2.8-build
退出去在别的目录执行如下编译
/usr/src/glibc-2.8/configure --prefix=/usr/src/glibc-2.8/glibc-2.8-build/
make
make install
继续执行刚才的check_system
[root@banggo memsqlbin]# ./check_system
Warning: The version of CentOS that you're using (5.5) is too low.
MemSQL supports versions 6.0 and up.
/home/manchun/memsqlbin/objdir/usr/local/bin/memsql-g++: /lib64/libc.so.6: version 'GLIBC_2.11' not found (required by /home/manchun/memsqlbin/objdir/usr/local/bin/memsql-g++)
【】我不得不承认，我out了，仔细一看，原来需要centos 6.0的。
2 重新安装
wget http://download.memsql.com/814d3816f4084953833243fbf6c40d37/memsqlbin_amd64.tar.gz
tar xvfz memsqlbin_amd64.tar.gz
cd memsqlbin
./check_system
报错如下：
Warning: The recommended configuration for MemSQL is at least 8 GB of RAM
【】解决方案：
let "mem_mb=$(free -m | sed -n -e '/^Mem:/s/^[^0-9]*\([0-9]*\) .*/\1/p')";
if [ $mem_mb -lt 1000 ]; then
echo "Warning: The recommended configuration for MemSQL is at least 1 GB of RAM" 1>&2
invalid='1'
fi
[root@memdb memsqlbin]# ./check_system
System check successful
3 登陆测试
[root@memdb memsqlbin]# ./memsqld --port 3308
299691 2012-06-27 13:26:26 WARNING: SSE4.2 is not supported. Resorting to software CRC32C.
497378 2012-06-27 13:26:27 INFO: Log level changed to 0
120627 13:26:27 [ERROR] Fatal error: Please run MemSQL as a non-root user or specify -u root on the command line.
120627 13:26:27 [ERROR] Aborting
120627 13:26:27 [Note] ./memsqld: Shutdown complete
[root@memdb memsqlbin]# ./memsqld -u root --port 3308
292356 2012-06-27 13:26:54 WARNING: SSE4.2 is not supported. Resorting to software CRC32C.
480363 2012-06-27 13:26:55 INFO: Log level changed to 0
120627 13:26:55 [Note] ./memsqld: ready for connections.
Version: '1b' socket: '/tmp/memsql.sock' port: 3308
4 客户端使用
[root@memdb ~]# mysql -uroot -p -h 127.0.0.1 -P3308 --prompt="MemSQL> "
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 253
Server version: 5.5.8 MemSQL source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MemSQL>
MemSQL> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| memsql |
+--------------------+
2 rows in set (0.00 sec)
MemSQL> use memsql;
Database changed
MemSQL> show tables;
Empty set (0.00 sec)
MemSQL> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MemSQL> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
28 rows in set (0.00 sec)
MemSQL>
库名字很mysql一模一样啊，好惊奇啊，居然information_schema都有，而且里面的表名字也是一模一样的。
5 配置文件
看看memsql有没有跟mysql类似的配置文件呢，如下，果然找到了
[root@memdb memsqlbin]# ll /root/memsqlbin/memsql.cnf
-rw-r--r--. 1 1001 1001 1026 6月 18 20:07 /root/memsqlbin/memsql.cnf
6 用户管理测试
-- 创建新账号
MemSQL> grant all on *.* to tim@'%' identified by "tim" with grant option;
Query OK, 1 row affected (0.01 sec)
MemSQL> exit
Bye
-- 重新登录，输入-p密码，进不去，不输入密码一回车，反倒进去了，怪哉!
[root@memdb memsqlbin]# mysql -utim -ptim -h 127.0.0.1 -P3308 --prompt="MemSQL> "
ERROR 1045 (28000): Access denied for user 'tim'@'localhost' (using password: YES)
[root@memdb memsqlbin]# mysql -utim -p -h 127.0.0.1 -P3308 --prompt="MemSQL> "
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 253
Server version: 5.5.8 MemSQL source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MemSQL>
CREATE TABLE test (
id int(11) NOT NULL AUTO_INCREMENT,
excel_id varchar(100) NOT NULL,
rpt_id int(10) unsigned NOT NULL,
acc_std tinyint(3) unsigned NOT NULL,
prd_year int(10) unsigned NOT NULL,
prd_qtr tinyint(3) unsigned NOT NULL,
accumulate char(3) NOT NULL,
biz_cd tinyint(3) unsigned NOT NULL,
acc_cd tinyint(3) unsigned NOT NULL,
row_num int(10) unsigned NOT NULL,
col_num int(10) unsigned NOT NULL,
data_id int(10) unsigned NOT NULL,
data_trace mediumtext NOT NULL COMMENT 'trace信息',
trace_edit tinyint(1) NOT NULL,
upd_stmp timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (id)
) ;
-- 多主-从
http://www.mysqlops.com/2012/02/14/diy_multi_master_replication.html
http://www.cnblogs.com/liuhao/archive/2012/06/26/2563702.html
前facebook员工和前微软sql server工程师联合搞的一个分布式关系数据库
全部内存运行，将sql转化成速度更快的c++, 原理类似HipHop
有mysql api, 完全兼容mysql,没有学习使用成本
速度是mysql的30倍，每秒可处理150万的事务
官方网站：http://memsql.com/
参考文档：http://space.itpub.net/7607759/viewspace-733545
参考官方文档：http://developers.memsql.com/docs/1b/
http://developers.memsql.com/docs/1b/
-- ==============================================================
1 Install
wget http://download.memsql.com/814d3816f4084953833243fbf6c40d37/memsqlbin_amd64.tar.gz
tar xvfz memsqlbin_amd64.tar.gzcd memsqlbin
./check_system
Warning: The version of CentOS that you're using (5.5) is too low.
MemSQL supports versions 6.0 and up.
/home/manchun/memsqlbin/objdir/usr/local/bin/memsql-g++: /lib64/libc.so.6: version GLIBC_2.11' not found (required by /home/manchun/memsqlbin/objdir/usr/local/bin/memsql-g++)
[root@banggo ~]#
wget http://ftp.gnu.org/pub/gnu/glibc/glibc-2.11.tar.gz
tar zxvf glibc-2.11.tar.gz
cd glibc-2.11
mkdir glibc-2.11-build
退出去在别的目录执行如下编译
/usr/src/glibc-2.11/configure --prefix=/usr/src/glibc-2.11/glibc-2.11-build/
error:
checking for .cfi_personality and .cfi_lsda pseudo-ops... no
configure: error: assembler too old, .cfi_personality support missing
【解决】
参考网址
http://groups.google.com/group/rocks-clusters/browse_thread/thread/4c9298adecf5a335
http://www.caritasem.com/?p=162
I couldn't overcome that problem, and I'm wondering how I can, but I
said, "hey cuda only requires glibc-2.7, so lets compile that". I went
ahead, and installed it into /share/apps/glibc-2.7. So here comes the
main question: how can I link CUDA C to the glibc-2.7 installed in
/share/apps ? Years ago, Tim Carlson has said "It is really not
practical to try and upgrade glibc to 2.5. You might want to try
installing a glibc 2.5 from source in /share/apps/glibc-2.5 and then
use LD_PRELOAD to load that library in for you CMAQ binary.", but I
don't know how to use LD_PRELOAD, and I would appreciate any
guideline.
[换成2.8]
[root@banggo ~]#
wget http://ftp.gnu.org/pub/gnu/glibc/glibc-2.8.tar.gz
tar zxvf glibc-2.8.tar.gz
cd glibc-2.8
mkdir glibc-2.8-build
退出去在别的目录执行如下编译
/usr/src/glibc-2.8/configure --prefix=/usr/src/glibc-2.8/glibc-2.8-build/
make
make install
继续执行刚才的check_system
[root@banggo memsqlbin]# ./check_system
Warning: The version of CentOS that you're using (5.5) is too low.
MemSQL supports versions 6.0 and up.
/home/manchun/memsqlbin/objdir/usr/local/bin/memsql-g++: /lib64/libc.so.6: version 'GLIBC_2.11' not found (required by /home/manchun/memsqlbin/objdir/usr/local/bin/memsql-g++)
【】我不得不承认，我out了，仔细一看，原来需要centos 6.0的。
2 重新安装
wget http://download.memsql.com/814d3816f4084953833243fbf6c40d37/memsqlbin_amd64.tar.gz
tar xvfz memsqlbin_amd64.tar.gz
cd memsqlbin
./check_system
报错如下：
Warning: The recommended configuration for MemSQL is at least 8 GB of RAM
【】解决方案：
let "mem_mb=$(free -m | sed -n -e '/^Mem:/s/^[^0-9]*\([0-9]*\) .*/\1/p')";
if [ $mem_mb -lt 1000 ]; then
echo "Warning: The recommended configuration for MemSQL is at least 1 GB of RAM" 1>&2
invalid='1'
fi
[root@memdb memsqlbin]# ./check_system
System check successful
3 登陆测试
[root@memdb memsqlbin]# ./memsqld --port 3308
299691 2012-06-27 13:26:26 WARNING: SSE4.2 is not supported. Resorting to software CRC32C.
497378 2012-06-27 13:26:27 INFO: Log level changed to 0
120627 13:26:27 [ERROR] Fatal error: Please run MemSQL as a non-root user or specify -u root on the command line.
120627 13:26:27 [ERROR] Aborting
120627 13:26:27 [Note] ./memsqld: Shutdown complete
[root@memdb memsqlbin]# ./memsqld -u root --port 3308
292356 2012-06-27 13:26:54 WARNING: SSE4.2 is not supported. Resorting to software CRC32C.
480363 2012-06-27 13:26:55 INFO: Log level changed to 0
120627 13:26:55 [Note] ./memsqld: ready for connections.
Version: '1b' socket: '/tmp/memsql.sock' port: 3308
4 客户端使用
[root@memdb ~]# mysql -uroot -p -h 127.0.0.1 -P3308 --prompt="MemSQL> "
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 253
Server version: 5.5.8 MemSQL source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MemSQL>
MemSQL> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| memsql |
+--------------------+
2 rows in set (0.00 sec)
MemSQL> use memsql;
Database changed
MemSQL> show tables;
Empty set (0.00 sec)
MemSQL> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MemSQL> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
28 rows in set (0.00 sec)
MemSQL>
库名字很mysql一模一样啊，好惊奇啊，居然information_schema都有，而且里面的表名字也是一模一样的。
5 配置文件
看看memsql有没有跟mysql类似的配置文件呢，如下，果然找到了
[root@memdb memsqlbin]# ll /root/memsqlbin/memsql.cnf
-rw-r--r--. 1 1001 1001 1026 6月 18 20:07 /root/memsqlbin/memsql.cnf
6 用户管理测试
-- 创建新账号
MemSQL> grant all on *.* to tim@'%' identified by "tim" with grant option;
Query OK, 1 row affected (0.01 sec)
MemSQL> exit
Bye
-- 重新登录，输入-p密码，进不去，不输入密码一回车，反倒进去了，怪哉!
[root@memdb memsqlbin]# mysql -utim -ptim -h 127.0.0.1 -P3308 --prompt="MemSQL> "
ERROR 1045 (28000): Access denied for user 'tim'@'localhost' (using password: YES)
[root@memdb memsqlbin]# mysql -utim -p -h 127.0.0.1 -P3308 --prompt="MemSQL> "
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 253
Server version: 5.5.8 MemSQL source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MemSQL>
CREATE TABLE test (
id int(11) NOT NULL AUTO_INCREMENT,
excel_id varchar(100) NOT NULL,
rpt_id int(10) unsigned NOT NULL,
acc_std tinyint(3) unsigned NOT NULL,
prd_year int(10) unsigned NOT NULL,
prd_qtr tinyint(3) unsigned NOT NULL,
accumulate char(3) NOT NULL,
biz_cd tinyint(3) unsigned NOT NULL,
acc_cd tinyint(3) unsigned NOT NULL,
row_num int(10) unsigned NOT NULL,
col_num int(10) unsigned NOT NULL,
data_id int(10) unsigned NOT NULL,
data_trace mediumtext NOT NULL COMMENT 'trace信息',
trace_edit tinyint(1) NOT NULL,
upd_stmp timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (id)
) ;
- MemSQL学习笔记-类似MySQL的数据库
- 安装memsql与将数据库从 MySQL 移植到 MemSQL
- MemSQL 的安装和简单使用 比Mysql快30倍的关系型数据库
- MemSQL：号称世界上最快的内存-关系型数据库 兼容MySQL但快30倍
- MemSQL，号称世界上最快的内存数据库
- mysql数据库学习笔记
- MYSQL数据库学习笔记
- 数据库MYSQL学习笔记
- MySQL数据库学习笔记
- 我的MySQL数据库学习笔记
- 我的MySQL数据库学习笔记
- 速度最快的数据库---MEMSQL的安装与部署
- mysql学习笔记之mysql数据库的安装
- CENTOS：memSQL的删除
- MemSQL 是由前Facebook工程师创办的，号称世界上最快的分布式关系型数据库，兼容MySQL但快30倍
- 类似查询mysql数据库的查询XML的JS类
- MySQL数据库学习笔记[转]
- MySQL数据库程序设计--学习笔记
- 写在Aras Innovator技术贴 前面
- UVA 11404 Palindromic Subsequence
- 深入学习Django源码基础1 - 深刻理解Python中的元类(metaclass)
- SOJ 1310. Right-Heavy Tree
- 中国集成电路发展探秘
- MemSQL学习笔记-类似MySQL的数据库
- [LeetCode] Minimum Window Substring
- 黑马程序员--Java语法
- Javass高级理论听课笔记
- 将Emacs配置成一个C/C++的IDE编程环境
- Java 通过Web Services访问Aras Innovator PLM
- ADO访问数据库
- storm
- [.Net码农]SqlBulkCopy的几点注意事项
