Mnesia

Summary: Author: 张亚飞 | 阅读时间: 5 minute read | Published: 2015-09-22
Filed under Categories: LinuxTags: Note,

Erlang Mnesia Database

最近学习erlang,在erlang中天然集成了一个分布式数据库名字叫mnesia,因为数据库天然就和erlang的元组,记录无缝结合,并且具有良好的分布式特性,能够自动地在几个erlang节点上实现数据复制,效果有点像zookeeper的节点集群, 而且mnesia还支持数据库事务特性,所以mnesia在erlang应用中使用得非常频繁.

要在本地创建一个mnesia数据库,并且要实现插入,查询功能需要实现以下这几步:

1.启动erlang节点:

#erl -mnesia dir '"d://tmp//mnesia"' -sname mynode
erl -mnesia dir '"C:\Users\yafei\DevWork\Erlang\dev\mnesia\data"' -name win@27.16.194.65 -setcookie 456789
sudo erl -mnesia dir '"/data/home/data/mnesia/"' -name ali@47.90.15.40 -setcookie 456789

注意路径双引号 以上这个 “/data/home/data/mnesia/” 目录是存放mnesia数据库的schema数据的,当然这个目录也可以不指定,那么mnesia数据库启动之后将把数据库的schema中的数据放在内存中,如果下次节点重启之后,之前数据库中创建的表结构数据就消失了.

2.接下来就是在erl shell中本地初始化一个空的 mnesia 数据库

mnesia:create_schema([node()]).

从参数上是一个节点列表,所以其实需要的话,可以在参数列表上设置多个erlang节点,就能在多个erlang节点上同时创建mnesia数据库并构成 Mnesia 集群.

3.启动mnesia数据库 初始化完成了mnesia数据库schema之后,接下来就可以启动mneisa数据库了.

mnesia:start().

至此,一个空的 mneisa 已经创建启动成功了,我们可以调用 mnesia:info(). 查看当前mnesia数据库的一些状态,结果如下:

(ali@localhost)2> mnesia:info().
===> System info in version "4.13.2", debug level = none <===
opt_disc. Directory "/data/home/data/mnesia" is used.
use fallback at restart = true
running db nodes   = []
stopped db nodes   = [ali@localhost]
ok
(ali@localhost)3> mnesia:start().
ok
(ali@localhost)4> mnesia:info().
---> Processes holding locks <---
---> Processes waiting for locks <---
---> Participant transactions <---
---> Coordinator transactions <---
---> Uncertain transactions <---
---> Active tables <---
schema         : with 1        records occupying 408      words of mem
===> System info in version "4.13.2", debug level = none <===
opt_disc. Directory "/data/home/data/mnesia" is used.
use fallback at restart = false
running db nodes   = [ali@localhost]
stopped db nodes   = []
master node tables = []
remote             = []
ram_copies         = []
disc_copies        = [schema]
disc_only_copies   = []
* [{ali@localhost,disc_copies}] = [schema]
2 transactions committed, 0 aborted, 0 restarted, 0 logged to disc
0 held locks, 0 in queue; 0 local transactions, 0 remote
0 transactions waits for other nodes: []
ok

从 [{ali@localhost,disc_copies}] = [schema] 这行上来看当前数据库中只有一个数据库的schema表.


Mnesia 集群

分别在 阿里云 ali 和 本机 win 启动两个节点

sudo -mnesia dir '"/data/home/data/mnesia/"' -name ali@47.90.15.40 -setcookie 456789

erl -mnesia dir '"C:\Users\yafei\DevWork\Erlang\dev\mnesia\data"' -name win@27.16.194.65 -setcookie 456789

erl -mnesia dir '"/data/home/data/mnesia/"' -name vultr@45.32.80.56 -setcookie 456789

erl -mnesia dir '"/Users/coam/DevErlang/dev/mnesia/data"' -name mac@171.43.194.4 -setcookie 456789

保证两个节点都启动,并且 mnesia 没有启用

mnesia:delete_schema(['win@27.16.194.65', 'ali@47.90.15.40']).
mnesia:create_schema(['win@27.16.194.65', 'ali@47.90.15.40']).

然后启动两个节点的 mnesia,查看运行详情

(win@27.16.194.65)9> erlang:get_cookie().
(win@27.16.194.65)9> mnesia:start().
(win@27.16.194.65)9> mnesia:info().
---> Processes holding locks <---
---> Processes waiting for locks <---
---> Participant transactions <---
---> Coordinator transactions <---
---> Uncertain transactions <---
---> Active tables <---
schema         : with 1        records occupying 414      words of mem
===> System info in version "4.13.2", debug level = none <===
opt_disc. Directory [99,58,47,85,115,101,114,115,47,121,97,102,101,105,47,68,
101,118,87,111,114,107,47,69,114,108,97,110,103,47,100,
101,118,47,109,110,101,115,105,97,47,85,115,101,114,115,
121,97,102,101,105,68,101,118,87,111,114,107,69,114,108,
97,110,103,127,101,118,109,110,101,115,105,97,127,97,116,
97] is used.
use fallback at restart = false
running db nodes   = ['ali@47.90.15.40','win@27.16.194.65']
stopped db nodes   = []
master node tables = []
remote             = []
ram_copies         = []
disc_copies        = [schema]
disc_only_copies   = []
* [{'ali@47.90.15.40',disc_copies},{'win@27.16.194.65',disc_copies}] = [schema]
3 transactions committed, 0 aborted, 1 restarted, 2 logged to disc
0 held locks, 0 in queue; 0 local transactions, 0 remote
0 transactions waits for other nodes: []
ok

根据 running db nodes = [‘ali@47.90.15.40’,‘win@27.16.194.65’] 可以看出两个节点已成功组成 节点

在任何一个节点添加数据表


erlang mnesia 数据库实现SQL查询

Mnesia是一个分布式数据库管理系统,适合于电信和其它需要持续运行和具备软实时特性的Erlang应用,越来越受关注和使用,但是目前Mnesia资料却不多,很多都只有官方的用户指南. 下面的内容将着重说明 Mnesia 数据库如何实现SQL查询,实现select / insert / update / where / order by / join / limit / delete等SQL操作.

参见示例 sql.erl

注:使用qlc模块查询,需要在文件顶部声明”-include_lib(“stdlib/include/qlc.hrl”).“,否则编译时会产生”Warning: qlc:q/1 called, but “qlc.hrl” not included”的警告.


使用单独的脚本连接 ejabberd mnesia 数据库

Cookie = 'YOUR_EJABBERD_COOKIE'. % mine was found in /var/lib/ejabberd/.erlang.cookie
EjabberdNode = 'ejabberd@127.0.0.1'. % or whatever you set as EJABBERD_NODE
erlang:set_cookie(EjabberdNode, Cookie).
net_adm:ping(EjabberdNode).
rpc:call(EjabberdNode, mnesia, system_info, [tables]).

参考 How can I connect to ejabberd’s Mnesia database with a separate script?


Ejabberd mnesia 远程连接

使用 observer 远程连接 mnesia 数据库

ejabberd mnesia 服务器

$ erl -name 'ejabberd@coopens.com' -mnesia dir '"/var/lib/ejabberd"' (ip为本机的ip,必须,否则不能ping通)
(ejabberd@coopens.com)1> auth:get_cookie(). (获取cookie,或者 erlang:get_cookie(). )
(ejabberd@coopens.com)2> 'JCNBOQTVXCDZNLCRJQUI'

复制cookie,然后在本机上操作:

$ erl -name yafei@192.168.1.102 -setcookie JCNBOQTVXCDZNLCRJQUI
(yafei@192.168.1.102)1> net_adm:ping('ejabberd@coopens.com').
(yafei@192.168.1.102)2> pong
(yafei@192.168.1.102)3> observer:start().

常见问题

  • mac 下启动 observer:start(). 报如下错误
(mac@171.43.194.4)5> observer:start().
{error,{{load_driver,"No driver found"},
        [{wxe_server,start,1,[{file,"wxe_server.erl"},{line,65}]},
         {wx,new,1,[{file,"wx.erl"},{line,115}]},
         {observer_wx,init,1,[{file,"observer_wx.erl"},{line,101}]},
         {wx_object,init_it,6,[{file,"wx_object.erl"},{line,343}]},
         {proc_lib,init_p_do_apply,3,
                   [{file,"proc_lib.erl"},{line,247}]}]}}
(mac@171.43.194.4)6> 
=ERROR REPORT==== 10-Aug-2016::15:06:42 ===
ERROR: Could not find 'wxe_driver.so' in: /usr/local/lib/erlang/lib/wx-1.7/priv
  • 按以下方式安装无效 - 以下是网上拷贝的错误,注意路径 /usr/local/Cellar/erlang/R16B03
debugger:start().
=ERROR REPORT==== 23-Jan-2014::11:59:08 ===
ERROR: Could not find 'wxe_driver.so' in: /usr/local/Cellar/erlang/R16B03/lib/erlang/lib/wx-1.1.1/priv
{ok,<0.42.0>}
brew install wxmac

发现在 /usr/local/Cellar/erlang/19.0.2/lib 下有 此库文件,而不是默认的 /usr/local/lib

但是当前系统 /usr/local/lib 有 erlang 程序库,但是没有 wxe_driver.so 文件

MacPro:wx-1.7 coam$ ls /usr/local/lib/erlang/lib/wx-1.7
ebin		examples	include		src

两个地方的 erlang 都是最新的 19.02 可能一个是通过 brew install erlang 安装的,另一个是通过 源码编译 otp-19.0.zip 安装的吧,暂且建立该文件软连接

ln -s /usr/local/Cellar/erlang/19.0.2/lib/erlang/lib/wx-1.7/priv/wxe_driver.so /usr/local/lib/erlang/lib/wx-1.7/priv/wxe_driver.so
  • 注意,后来发现删除掉 /usr/local/lib 后输入命令 erl 发现系统找不到命令了,然后使用以下命令重新卸载安装 erlang 居然
brew uninstall erlang
brew install erlang
ls /usr/local/Cellar/erlang/19.0.2/lib/erlang/lib/wx-1.7
ebin		examples	include		priv		src
ls /usr/local/Cellar/erlang/19.0.2/lib/erlang/lib/wx-1.7/priv
erl_gl.so		erlang-logo32.png	erlang-logo64.png	wxe_driver.so

居然自动安装了 /priv/wxe_driver.so ,附重新安装 erlang 日志(卸载掉 wxmac 等后,下面又自己安装 wxmac -不解)

MacPro:~ coam$ brew uninstall erlang
Uninstalling /usr/local/Cellar/erlang/19.0.2... (7,292 files, 278.6M)
MacPro:~ coam$ brew install erlang
==> Installing dependencies for erlang: jpeg, libpng, libtiff, wxmac
==> Installing erlang dependency: jpeg
==> Downloading https://homebrew.bintray.com/bottles/jpeg-8d.el_capitan.bottle.2.tar.gz
Already downloaded: /Users/coam/Library/Caches/Homebrew/jpeg-8d.el_capitan.bottle.2.tar.gz
==> Pouring jpeg-8d.el_capitan.bottle.2.tar.gz
🍺  /usr/local/Cellar/jpeg/8d: 19 files, 713.7K
==> Installing erlang dependency: libpng
==> Downloading https://homebrew.bintray.com/bottles/libpng-1.6.23.el_capitan.bottle.tar.gz
Already downloaded: /Users/coam/Library/Caches/Homebrew/libpng-1.6.23.el_capitan.bottle.tar.gz
==> Pouring libpng-1.6.23.el_capitan.bottle.tar.gz
🍺  /usr/local/Cellar/libpng/1.6.23: 25 files, 1.2M
==> Installing erlang dependency: libtiff
==> Downloading https://homebrew.bintray.com/bottles/libtiff-4.0.6_2.el_capitan.bottle.tar.gz
Already downloaded: /Users/coam/Library/Caches/Homebrew/libtiff-4.0.6_2.el_capitan.bottle.tar.gz
==> Pouring libtiff-4.0.6_2.el_capitan.bottle.tar.gz
🍺  /usr/local/Cellar/libtiff/4.0.6_2: 261 files, 3.4M
==> Installing erlang dependency: wxmac
==> Downloading https://homebrew.bintray.com/bottles/wxmac-3.0.2_2.el_capitan.bottle.1.tar.gz
Already downloaded: /Users/coam/Library/Caches/Homebrew/wxmac-3.0.2_2.el_capitan.bottle.1.tar.gz
==> Pouring wxmac-3.0.2_2.el_capitan.bottle.1.tar.gz
Error: The `brew link` step did not complete successfully
The formula built, but is not symlinked into /usr/local
Could not symlink bin/wxrc
Target /usr/local/bin/wxrc
already exists. You may want to remove it:
  rm '/usr/local/bin/wxrc'

To force the link and overwrite all conflicting files:
  brew link --overwrite wxmac

To list all files that would be deleted:
  brew link --overwrite --dry-run wxmac

Possible conflicting files are:
/usr/local/bin/wxrc -> /usr/local/bin/wxrc-3.1
==> Summary
🍺  /usr/local/Cellar/wxmac/3.0.2_2: 809 files, 23.6M
==> Installing erlang
==> Downloading https://homebrew.bintray.com/bottles/erlang-19.0.2.el_capitan.bottle.tar.gz
Already downloaded: /Users/coam/Library/Caches/Homebrew/erlang-19.0.2.el_capitan.bottle.tar.gz
==> Pouring erlang-19.0.2.el_capitan.bottle.tar.gz
==> Caveats
Man pages can be found in:
  /usr/local/opt/erlang/lib/erlang/man

Access them with `erl -man`, or add this directory to MANPATH.
==> Summary
🍺  /usr/local/Cellar/erlang/19.0.2: 7,292 files, 278.6M
MacPro:~ coam$ brew list
elixir	erlang	jpeg	libpng	libtiff	openssl	wget	wxmac

参考列表


mnesia表数据Id自增长

1.使用函数:

mnesia:dirty_update_counter(unique_id, mine_result, 1).

2.该函数参数示意, unique_id 表名, mine_result 表名,每次自增N. 3. 1.创建如下结构的mnesia数据库表 -record(unique_id, {item, uid}),该表是为了存储 mine_result 表的最大的Id值; 2.每为 mine_result 表加入一条新记录时,需要得到新的id值:

 mnesia:dirty_update_counter(unique_id,  mine_result , 1),

从unique_id表中取出最大的Id值加1,更新unique_id表的数据,返回最大Id值加1, 注意:请在建立unique_id表的时候,往里面插入数据;表名,初始值,{ mine_result ,0}, 我做了一个实验如果unique_id表中没有数据的时候,并发向mine_result库中插入100条数据时,不稳定. 但是unique_id表中加入一个数据后,并发数提高到30000,还是很稳定.


参考 * wudaijun’s blog - Erlang mnesia * 创设Mnesia数据库 * Erlang的Mnesia——为高伸缩性应用准备的数据库管理系统 * [Erlang-0008][OTP] 高效指南 – 表和数据库(ets mnesia) * Erlang基础Mnesia 之应用场景 * erlang Mnesia - 尚待研究 * mnesia数据库使用体验 * mnesia数据库优化

Comments

Cor-Ethan, the beverage → www.iirii.com