db stuff

db-stuff

############### mongodb: ################
1) not master and slaveok=false
在写多读少的应用中，使用Replica Sets来实现读写分离。通过在连接时指定或者在主库指定slaveOk，由Secondary来分担读的压力，Primary只承担写操作。
对于replica set 中的secondary 节点默认是不可读的，
[mongodb@rac4 bin]$ mongo 127.0.0.1:28019
MongoDB shell version: 2.0.1
connecting to: 127.0.0.1:28019/test
SECONDARY> 
SECONDARY> 
SECONDARY> db.yql.find()
error: { "$err" : "not master and slaveok=false", "code" : 13435 }
SECONDARY> db.getMongo()
connection to 127.0.0.1:28019
SECONDARY> db.getMongo().setSlaveOk();
not master and slaveok=false
 
在主库上设置 slaveok=ok
[mongodb@rac4 bin]$ mongo 127.0.0.1:28018
MongoDB shell version: 2.0.1
connecting to: 127.0.0.1:28019/test
PRIMARY> db.getMongo().setSlaveOk();
PRIMARY> 
在备库进行测试
SECONDARY> db.yql.find()
{ "_id" : ObjectId("4eb68b1540643e10a0000000"), "age" : 29, "id" : 0, "name" : "2" }
{ "_id" : ObjectId("4eb68b1540643e10a0000001"), "age" : 77, "id" : 1, "name" : "h5gfx6r6sz" }
{ "_id" : ObjectId("4eb68b1540643e10a0000002"), "age" : 12, "id" : 2, "name" : "lmj7w6uqanvntaco" }
{ "_id" : ObjectId("4eb68b1540643e10a0000003"), "age" : 56, "id" : 3, "name" : "1ifd3bmzguuv" }
{ "_id" : ObjectId("4eb68b1540643e10a0000004"), "age" : 33, "id" : 4, "name" : "ba0hchjhi2c16" }
{ "_id" : ObjectId("4eb68b1540643e10a0000005"), "age" : 70, "id" : 5, "name" : "fe6su2p3t2gdckt" }
{ "_id" : ObjectId("4eb68b1540643e10a0000006"), "age" : 57, "id" : 6, "name" : "cq" }
{ "_id" : ObjectId("4eb68b1540643e10a0000007"), "age" : 75, "id" : 7, "name" : "0j43jsovjmyvct" }
{ "_id" : ObjectId("4eb68b1540643e10a0000008"), "age" : 60, "id" : 8, "name" : "eusg4aa5a6ke64zf5ye7" }
{ "_id" : ObjectId("4eb68b1540643e10a0000009"), "age" : 40, "id" : 9, "name" : "iwzcrdjcpoc8f" }
{ "_id" : ObjectId("4eb68b1540643e10a000000a"), "age" : 59, "id" : 10, "name" : "er3iemdl" }
{ "_id" : ObjectId("4eb68b1540643e10a000000b"), "age" : 18, "id" : 11, "name" : "dnvz0u" }
{ "_id" : ObjectId("4eb68b1540643e10a000000c"), "age" : 5, "id" : 12, "name" : "n6ac2ad58fk88if02s" }
{ "_id" : ObjectId("4eb68b1540643e10a000000d"), "age" : 4, "id" : 13, "name" : "mob61x3oj0us" }
{ "_id" : ObjectId("4eb68b1540643e10a000000e"), "age" : 32, "id" : 14, "name" : "1ylc0" }
{ "_id" : ObjectId("4eb68b1540643e10a000000f"), "age" : 18, "id" : 15, "name" : "nbl9r0dnd0vmi" }
{ "_id" : ObjectId("4eb68b1540643e10a0000010"), "age" : 114, "id" : 16, "name" : "gfzc02j7npkv25" }
{ "_id" : ObjectId("4eb68b1540643e10a0000011"), "age" : 34, "id" : 17, "name" : "ac8kfl8" }
{ "_id" : ObjectId("4eb68b1540643e10a0000012"), "age" : 9, "id" : 18, "name" : "wtpqii0dh" }
{ "_id" : ObjectId("4eb68b1540643e10a0000013"), "age" : 81, "id" : 19, "name" : "xi7r4ej" }
has more
这样就可以实现读写分离操作了。

关于slaveOk:
slaveOk 基本上可以说是mongodb连接的一个属性。 在mongoConnection或com.mongodb.DB中设置了slaveOk,表示允许当前的连接从replset的secondary读取数据。似乎和mongodb replset的本身属性没有什么关联。

db.doc.update({"_id" : "362061268477948"}, {"$set" : {"v" : 1}})
getFile(): bad file number value (corrupt db?): run repair
当在我的机器连另一台机器上的mongod， 执行update时出现此错误。应该是没有权限导致的。

2) mongodb backup and restore:
   mongodump --port p --db dbName --collection collName --out destDir
   mongorestore --port p   dumpDir

### mongodb aggregate:
> db.totalInterfaceAccessAmount_raw.aggregate(
  [
    {$project: {"account": 1, "value": 1}},
    {$group: {_id: "$account", group_val: {$sum: "$value"}}},
    {"$sort": {"group_val": 1}}
    ]
  );

######################################### 


################# mysql #################
 
1) 安装MySQL
sudo apt-get install mysql-server
 
2) 配置MySQL
注意，在Ubuntu下MySQL缺省是只允许本地访问的，如果你要其他机器也能够访问的话，那么需要改变/etc/mysql/my.cnf配置文件了！下面我们一步步地来：
默认的MySQL安装之后根用户是没有密码的，所以首先用根用户进入：
$mysql -u root
在这里之所以用-u root是因为我现在是一般用户（firehare），如果不加-u root的话，mysql会以为是firehare在登录。注意，我在这里没有进入根用户模式，因为没必要。一般来说，对mysql中的数据库进行操作，根本没必要进入根用户模式，只有在设置时才有这种可能。
进入mysql之后，最要紧的就是要设置Mysql中的root用户密码了，否则，Mysql服务无安全可言了。
mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY "123456";
这样的话，就设置好了MySQL中的root用户密码了，然后就用root用户建立你所需要的数据库。我这里就以xoops为例：
mysql>CREATE DATABASE xoops；
mysql>GRANT ALL PRIVILEGES ON xoops.* TO xoops_root@localhost IDENTIFIED BY "654321";
这样就建立了一个xoops_roots的用户，它对数据库xoops有着全部权限。以后就用xoops_root来对xoops数据库进行管理，而无需要再用root用户了，而该用户的权限也只被限定在xoops数据库中。
如果你想进行远程访问或控制，那么你要做两件事：
其一：
mysql>GRANT ALL PRIVILEGES ON xoops.* TO xoops_root@"%" IDENTIFIED BY "654321";
允许xoops_root用户可以从任意机器上登入MySQL。
其二：
$sudo gedit /etc/mysql/my.cnf
老的版本中
>skip-networking => # skip-networking
新的版本中
>bind-address=127.0.0.1 => bind-address= 你机器的IP
这样就可以允许其他机器访问MySQL了。
 
3) mysql -h localhost -u root -p

mysql errors:

a) $ service mysqld
mysqld: unrecognized service

$ chkconfig --list mysql
mysql 0:off 1:off 2:off 3:off 4:off 5:off 6:off
$ chkconfig --list mysqld
mysqld: unknown service
$ whereis mysql
mysql: /usr/bin/mysql /etc/mysql /usr/lib/mysql /usr/bin/X11/mysql /usr/include/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz

4) stop/restart mysql:
   /etc/init.d/mysql stop/start/restart  or  service mysql stop/start/restart

#########################################

################## postgresql ################
1. install postgresql on ubuntu:
   sudo apt-get install postgresql

2. connect to postgres:
   sudo -u postgres psql postgres
   --> \password
   ctrl-d to quit.

3. can only install postgresql-client to connect to a server:
   sudo apt-get install postgresql-client
   psql -h host-name-or-ip dbname user

-- postgres add user:
   su - postgres
   psql
   \c template1
   grant all privileges on database policy to klzhong;
   create user klzhong with password '123456';        
   \q

## postgres sql:
==> manage:
   \list  //list databases
   \dt    //list tables in current db.
   \c db-name  // connect to a certain db.

   \?   //help

   \d+ tablename  // desc table.

==> create database(paperfare for example):
    step1. psql -h localhost postgres kzhong
    step2. CREATE DATABASE paperfare OWNER klzhong;
        ERROR:  permission denied to create database
    step3. grant user create database: relogin as admin user:
           psql -h localhost postgres postgres;
           ALTER USER klzhong CREATEDB;
           SET ROLE klzhong  //switch to user klzhong
    step4. exec step2.
    step5. \l+

==> DDL:

==> DML:

--- Postgres on mac:
## use brew instead of port to install postgresql:
-- install brew first:
   sudo su
   curl -L http://github.com/mxcl/homebrew/tarball/master | tar xz --strip 1 -C /usr/local

   --> brew can not work with macport installed. uninstall all soft ware that installed by macport and remove macport.
   -- show installed software in macport: 
     port installed
	 
   -- remove all software installed by macports:
     sudo port -f uninstall installed
   -- remove macport files:

   ==> install postgresql with brew success.
       for help , use command: brew info postgres     

-- start postgresql on mac montain lion:
   --> sudo rm -rf /usr/local/var/postgres  // rm if exist.
       sudo mkdir /usr/local/var/postgres
       sudo chown klzhong /usr/local/var/postgres
       initdb /usr/local/var/postgres -E utf8
   --> pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

-- problem when run psql connect to postgresql server: 
   psql: could not connect to server: No such file or directory
   Is the server running locally and accepting
   connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?       
   --> resolve:
       pg_ctl -D /usr/local/var/postgres stop
       sudo mkdir /var/pg_socket
       sudo chown klzhong /var/pg_socket
       modify /usr/local/var/postgres/postgresql.conf:
              unix_socket_directory = '/var/pgsql_socket'
       pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

   ==> use: psql postgres  connect to postgresql server.


##############################################


####### ORACLE 11 ########

-- three kind of authority in oracle: dba, user, all.

1. select oracle version via sql:
   select * from v$version;

2. show all schemas:
   select * from all_users;

3. show all tables in schema:
   select owner, object_name from all_objects where object_type='TABLE' and owner='schema-name';
##########################