Pages: 5/12 First page Previous page 1 2 3 4 5 6 7 8 9 10 Next page Final page [ View by Articles | List ]

change mysql data directory

[不指定 2009/07/30 12:08 | by askwan ]
案例描述：
某公司某数据原来安装于系统的/usr/local/mysql下
数据库文件存放路径自然是/usr/local/mysql/data目录

现在挂载这个区的空间快full了，增加了块1T硬盘挂在系统下/opt/mysql下

1.停mysql数据库
2.转移原来的/usr/local/mysql/data 到/opt/mysql 下
3.修改my.cnf配置文件
[mysqld]段修改datadir=/opt/mysql/data

[mysqld]
datadir=/opt/mysql/data
4.按照原方式启动mysql

-----------END------------

Tags: , , ,
DataBase | Comments(0) | Trackbacks(0) | Reads(1424)

PostgreSQL的mulitmaster replication解决方案Cybercluster

[不指定 2009/07/24 10:50 | by askwan ]
简介：    
     Cybercluster是一种Postgresql数据库的mulitmaster replication solution, shared-nothing构架，基于BSD协议发放，由奥地利一家做基于postgresql数据库解决方案和服务的公司团队维护开发，cybercluster整体架构设计基于古老的pgcluster。
     因工作原因，小小研究了一番。

四类节点：

LoadBlancer 节点 ：负责给客户端应用请求做负载均衡，在一个架构中是可选的节点
Cluster DB 节点：存储数据的节点，在一个架构中是必须的节点，只少要一个（这不废话吗，呵呵）
Replication Manager 节点： 负责分析和判断应用请求以及同步数据的节点
Monitor 节点 ：负责监控架构中各个cluster 节点

对于读请求，看下图
Open in new window
通过LoadBlancer 节点分配到负载最小的一个cluster db 节点

对于写请求，看下图
Open in new window
如果是写等修改数据库内容请求，则Replication Manager节点会连接每一个activer的 cluster db都执行同一操作
这个flash比较形象 ，http://www.postgresql.at/flash/cybercluster.swf

从这个意义上说，这种同步方式属于 SQL Statement类型的 ，而不是Row level型。

环境搭建：
这个可以参看软件包内说明，比较简单。

故障恢复：
cybercluster提供了三种cluster db node 故障恢复方式，例如，启动cluster db node 节点，指定
1. pg_ctl -D /usr/local/pgsql/data start  -o "-R"
2. pg_ctl -D /usr/local/pgsql/data start  -o "-u"
3. pg_ctl -D /usr/local/pgsql/data start  -o "-U"

前两种方式属于cold recovery,依赖于架构配置好ssh + rsync 无密码访问，简言之，通过rsync来做文件级别的copy达到恢复数据的目地，这个比较特别
如下我做实验的恢复过程：
Start in recovery mode!
Please wait until a data synchronization finishes from Master DB...
1st recovery step of [global] directory...OK
1st recovery step of [base] directory...OK
1st recovery step of [pg_clog] directory...OK
1st recovery step of [pg_xlog] directory...OK
1st sync_table_space OK
2nd recovery step of [global] directory...OK
2nd recovery step of [base] directory...OK
2nd recovery step of [pg_clog] directory...OK
2nd recovery step of [pg_xlog] directory...OK
2nd sync_table_space OK
2nd recovery successed
LOG:  database system was interrupted; last known up at 2009-07-24 10:02:05 CST
LOG:  database system was not properly shut down; automatic recovery in progress
Start in recovery mode!
Please wait until a data synchronization finishes from Master DB...
OK!  The data synchronization with Master DB was finished.
LOG:  redo starts at 0/4FC8A0
LOG:  record with zero length at 0/522AE4
LOG:  redo done at 0/522AB8
LOG:  last completed transaction was at log time 2009-07-24 10:01:53.911028+08
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections


后一种则是基于逻辑意义上的恢复，
如下我做的是实验的恢复过程：

Start in recovery mode!
Please wait until a data synchronization finishes from Master DB...
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
1st restore from pg_dump 1st exec:[/usr/local/pgsql/bin/pg_dumpall -i -o -c -h cluster1 -p 5432 -U postgres | /usr/local/pgsql/bin/psql -p 5432 template1]
You are now connected to database "postgres".
ERROR:  current user cannot be dropped
STATEMENT:  DROP ROLE postgres;
ERROR:  current user cannot be dropped
ERROR:  role "postgres" already exists
STATEMENT:  CREATE ROLE postgres;
ERROR:  role "postgres" already exists
ALTER ROLE
DROP DATABASE
CREATE DATABASE
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "askwan01".
CREATE TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "postgres".
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "template1".
COMMENT
REVOKE
REVOKE
GRANT
GRANT
->OK
2nd restore from pg_dump 2nd exec:[/usr/local/pgsql/bin/pg_dump -i -Fc -o -b -h cluster1 -p 5432 -U postgres postgres | /usr/local/pgsql/bin/pg_restore -i -c -p 5432 -d postgres]
.2nd exec:[/usr/local/pgsql/bin/pg_dump -i -Fc -o -b -h cluster1 -p 5432 -U postgres askwan01 | /usr/local/pgsql/bin/pg_restore -i -c -p 5432 -d askwan01]
.->OK
2nd recovery successed
OK!  The data synchronization with Master DB was finished.


能清楚的看到这两个之间的区别。

监控节点:
没有什么好说的，做的非常简陋
postgres@testpg1$ pgrplmon rep1 9000
RPLMonitor v1.0
DB server #1
        Hostname: cluster1
        Resolved host: 192.168.233.144
        Port: 5432
        # of started transactions: 0
        # of COMMITed transactions: 0
        # of ROLLBACKed transactions: 0
        # of INSERT statements: 13
        # of UPDATE statements: 0
        # of DELETE statements: 0
        # of DDL statements: 6
DB server #2
        Hostname: cluster2
        Resolved host: 192.168.233.145
        Port: 5432
        # of started transactions: 0
        # of COMMITed transactions: 0
        # of ROLLBACKed transactions: 0
        # of INSERT statements: 0
        # of UPDATE statements: 0
        # of DELETE statements: 0
        # of DDL statements: 0

感受：

优点：
1.架设简单。
2.支持负载均衡。
3.原理易懂，应用不需要做任何变更。
4.可扩展性比较好,各类型节点可以很容易的增加到集群中。

缺点：
1.文档其少，相关资料也少，要使用它，真得慎重
2.恢复的时候，Replication Manager节点配置文件中cluster db node位置得手动修改，希望高版本改进！

性能：
没有时间做测试，暂不评价！
Tags: , , , ,
DataBase | Comments(0) | Trackbacks(0) | Reads(1206)

imp导入数据到ORACLE遭遇ORA-12899错误

[不指定 2009/05/26 23:32 | by askwan ]
错如信息：
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "JRGAZX"."DTLMB"."CC" (actual: 66, maximum: 50)

初步断定是字符集问题，中文在UTF-8里占3个字节，ZHS16GBK里占2个字节,而源dmp文件字符集是ZHS16GBK的库到出来的，现在要导入到目标字符集为UFT-8的库里。
Quotation
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL>ALTER DATABASE OPEN

SQL> ALTER DATABASE CHARACTER SET ZHS16GBK ;
ALTER DATABASE CHARACTER SET ZHS16GBK
*ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
报字符集不兼容，此时下INTERNAL_USE指令不对字符集超集进行检查：
Quotation
SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP


再次用imp导入，就没有问题了。
oracle@server$ imp jrgazx/jrgazx file=/opt/jrgazx1/jrgazx1.dmp  fromuser=jrgazx TABLES=CWFXZB,CXQK,DQBGYYPLSJB,DQTZSM,DSYP,DTLMB
Quotation
Import: Release 10.2.0.1.0 - Production on Wed May 27 01:22:54 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing JRGAZX's objects into JRGAZX
. importing JRGAZX's objects into JRGAZX
. . importing table                       "CWFXZB"      57197 rows imported
. . importing table                         "CXQK"       8471 rows imported
. . importing table                  "DQBGYYPLSJB"      30593 rows imported
. . importing table                       "DQTZSM"      11173 rows imported
. . importing table                         "DSYP"       4906 rows imported
. . importing table                        "DTLMB"     390372 rows imported
Import terminated successfully without warnings.
---------END-----------
Tags: , , , ,
DataBase | Comments(0) | Trackbacks(0) | Reads(2131)

遭遇 ORA-01102: cannot mount database in EXCLUSIVE mode

[不指定 2009/05/18 16:38 | by askwan ]
刚刚装好的库，简单测试没有问题后，放下，不到半个钟头，再次连接上去，发现数据库已经不可用了……

Quotation
oracle@staging:~$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 18 16:13:29 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn  / as sysdba
Connected to an idle instance.

SQL> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-01034: ORACLE not available


机器并没有重启，oracle莫名其妙的到了不可用状态！我开始以为是谁关闭了，下开启指令
Quotation
SQL> startup
ORACLE instance started.

Total System Global Area 1224736768 bytes
Fixed Size                  2020384 bytes
Variable Size             318770144 bytes
Database Buffers          889192448 bytes
Redo Buffers               14753792 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode


这下遭遇ORA-01102: cannot mount database in EXCLUSIVE mode 错误了
Search On Google，then get the problem's anwser
大意是 “Oracle被异常关闭时，有资源没有被释放……”

检查警告日志，也证实了这一点，系统异常

马上查$ORACLE_HOME/dbs,发现一lk【SID】 文件，果然呆在这里
Quotation
staging:dbs# fuser -u lkCAPITALV
lkCAPITALV:            368(oracle)   370(oracle)   372(oracle)   374(oracle)   376(oracle)   378(oracle)   380(oracle)   382(oracle)   384(oracle)   386(oracle)   388(oracle)   397(oracle)  1472(oracle)  1557(oracle)  8852(oracle)


杀掉先：
Quotation
staging:dbs# fuser -k lkCAPITALV


再查，发现已经没有资源占用
Quotation
staging:dbs# fuser -u lkCAPITALV  

ok！
再次启动Oracle
Quotation
oracle@staging:~$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 18 16:24:05 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 1224736768 bytes
Fixed Size                  2020384 bytes
Variable Size             318770144 bytes
Database Buffers          889192448 bytes
Redo Buffers               14753792 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

----------------------END----------------------------
Tags: ,
DataBase | Comments(0) | Trackbacks(0) | Reads(2818)
Pages: 5/12 First page Previous page 1 2 3 4 5 6 7 8 9 10 Next page Final page [ View by Articles | List ]