mysqlbackup

#!/bin/sh
#backup mysql database
#一、如果没有密码用
#mysqldump -q -a -c -u root  --opt $DBName | gzip > $BackupPath/$DBName.$rq.sql.gz
#二、定时任务 每天凌晨三点
#vi /etc/crontab
#01 3 * * * root /usr/sbin/mysql.sh
#三、重新启动crond
#/etc/rc.d/init.d/crond restart
#:set ff 或 :set fileformat
#:set ff=unix 或 :set fileformat=unix

DBName=test
DBUser=root
DBPasswd=
BackupPath=/data/backup/mysql

#delete file 删除7天前的备份
find $BackupPath -type f -mtime +7 -exec rm {} \;

rq=`date +%Y%m%d%H%M`
#backup each database
mysqldump -q -a -c -u root -p$DBPasswd --opt $DBName | gzip > $BackupPath/$DBName.$rq.sql.gz

蛋疼的mysql

今天蛋疼的吧mysql升级了下,用yum直接导致mysql启动失败。主要两个问题

1.my.cnf总table..size去掉

2.share/mysql/language/…这写文件路径有问题

要查看问题只要看下log   (/var/log/mysql.log)就很清楚

MySQL常用操作

1.修改MySQL数据库默认编码

linux系统下,修改MySQL数据库默认编码的步骤为:
停止MySQL的运行
/etc/init.d/mysql start (stop) 为启动和停止服务器
MySQL主配置文件为my.cnf,一般目录为/etc/mysql
var/lib/mysql/ 放置的是数据库表文件夹,这里的mysql相当于windows下mysql的date文件夹
当我们需要修改MySQL数据库的默认编码时,需要编辑my.cnf文件进行编码修改,在linux下修改mysql的配置文件my.cnf,文件位置默认/etc/my.cnf文件

找到客户端配置[client] 在下面添加
default-character-set=utf8 默认字符集为utf8
在找到[mysql] 添加
default-character-set=utf8 默认字符集为utf8
在找到[mysqld] 添加
default-character-set=utf8 默认字符集为utf8

如果Starting MySQL…………The server quit without updating PID file (/var/lib/mysql/node1.pid). [faild]

改成 character_set_server=utf8;
init_connect=’SET NAMES utf8′ (设定连接mysql数据库时使用utf8编码,以让mysql数据库为utf8运行)

修改好后,重新启动mysql 即可,重新查询数据库编码可发现编码方式的改变:
>show variables like ‘character%';
+————————–+—————————-+
| 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/ |
+————————–+—————————-+

PS:jdbc:mysql://localhost:3306/mysql?useUnicode=true&characterEncoding=utf-8

 

查看表各列的字符编码:
show full columns from 表名;

2.修改用户访问权限

grant all on *.* to root@”%” identified by ‘abc123′;

3.创建用户并授权

create database taxioncall;
CREATE USER ‘taxioncall’@’db01.yijiaoche.net’ IDENTIFIED BY ‘123456’;
grant all on *.* to taxioncall@”db01.yijiaoche.net” identified by ‘123456’;
grant all on *.* to taxioncall@”localhost” identified by ‘123456’;

1)创建数据库bugs
create database reviewboard;
2)对bugs进行设置权限
grant all privileges on reviewboard.* to ‘reviewboard’@’localhost’ identified by ‘reviewboard';
grant all privileges on reviewboard.* to ‘reviewboard’@’%’ identified by ‘reviewboard';
其中on后面的bugs为数据库名,to后面的bugs为用户名,by后面的root为密码(用户可自行设置)
3)从mysql数据库授权表中重新装载权限
Flush privileges;

4.修改表字符集

ALTER TABLE temp CONVERT TO CHARACTER SET utf8 collate utf8_unicode_ci

对中、英文来说没有实质的差别。
utf8_general_ci校对速度快,但准确度稍差。
utf8_unicode_ci准确度高,但校对速度稍慢。

如果你的应用有德语、法语或者俄语,请一定使用utf8_unicode_ci。

我自己一般用utf8_general_ci就够了,到现在也没发现问题。。。

Oracle——distinct的用法

distinct这个关键字来过滤掉多余的重复记录只保留一条,但往往只用 它来返回不重复记录的条数,而不是用它来返回不重记录的所有值。其原因是distinct只有用二重循环查询来解决,而这样对于一个数据量非常大的站来说,无疑是会直接影响到效率的。

下面先来看看例子:

table表

字段1     字段2
id        name
1           a
2           b
3           c
4           c
5           b

库结构大概这样,这只是一个简单的例子,实际情况会复杂得多。

比如我想用一条语句查询得到name不重复的所有数据,那就必须使用distinct去掉多余的重复记录。

select distinct name from table
得到的结果是:

 

———-

name
a
b
c

好像达到效果了,可是,我想要得到的是id值呢?改一下查询语句吧:

select distinct name, id from table

结果会是:

———-

id name
1 a
2 b
3 c
4 c
5 b

distinct怎么没起作用?作用是起了的,不过他同时作用了两个字段,也就是必须得id与name都相同的才会被排除。。。。。。。

我们再改改查询语句:

select id, distinct name from table

很遗憾,除了错误信息你什么也得不到,distinct必须放在开头。难到不能把distinct放到where条件里?能,照样报错。

————————————————————————————————————

下面方法可行:

select *, count(distinct name) from table group by name

结果:

id name count(distinct name)
1 a 1
2 b 1
3 c 1

最后一项是多余的,不用管就行了,目的达到。。。。。

group by 必须放在 order by 和 limit之前,不然会报错

 

sql之left join、right join、inner join的区别

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行

举例如下:
——————————————–
表A记录如下:
aID     aNum
1     a20050111
2     a20050112
3     a20050113
4     a20050114
5     a20050115

表B记录如下:
bID     bName
1     2006032401
2     2006032402
3     2006032403
4     2006032404
8     2006032408

——————————————–
1.left join
sql语句如下:
select * from A
left join B
on A.aID = B.bID

结果如下:
aID     aNum     bID     bName
1     a20050111    1     2006032401
2     a20050112    2     2006032402
3     a20050113    3     2006032403
4     a20050114    4     2006032404
5     a20050115    NULL     NULL

(所影响的行数为 5 行)
结果说明:
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL.
——————————————–
2.right join
sql语句如下:
select * from A
right join B
on A.aID = B.bID

结果如下:
aID     aNum     bID     bName
1     a20050111    1     2006032401
2     a20050112    2     2006032402
3     a20050113    3     2006032403
4     a20050114    4     2006032404
NULL     NULL     8     2006032408

(所影响的行数为 5 行)
结果说明:
仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.
——————————————–
3.inner join
sql语句如下:
select * from A
innerjoin B
on A.aID = B.bID

结果如下:
aID     aNum     bID     bName
1     a20050111    1     2006032401
2     a20050112    2     2006032402
3     a20050113    3     2006032403
4     a20050114    4     2006032404

结果说明:
很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.
——————————————–
注:
LEFT JOIN操作用于在任何的 FROM 子句中,组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接。左边外部联接将包含了从第一个(左边)开始的两个表中的全部记录,即使在第二个(右边)表中并没有相符值的记录。

语法:FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2

说明:table1, table2参数用于指定要将记录组合的表的名称。
field1, field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据,但它们不需要有相同的名称。
compopr参数指定关系比较运算符:”=”, “<“, “>”, “<=”, “>=” 或 “<>”。
如果在INNER JOIN操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段,将会发生错误.

 

sybase和oracle的几点不同

1.sybase可以用TOP在取几条数据,而oracle需要用rownum来处理

2.sybase对表或者字段取别名时候可以用as,没有也可以。而oracle不支持as表别名,但是支持字段as别名,直接跟上别名就OK.

3.下面一条sysbase语句

SELECT b.* FROM commend_articles_bykind b
WHERE b.blog_type=1 AND b.group_type=2 AND b.default_kind_id=1 AND b.cooperator_id=0
AND EXISTS(SELECT 1 FROM info_article a WHERE a.id=b.article_id AND a.power=1 AND b.blog_id=a.blog_id)
UNION ALL SELECT b.* FROM commend_articles_bykind b WHERE b.blog_type=1 AND b.group_type=2 and b.default_kind_id=1 AND b.cooperator_id=0
AND EXISTS(SELECT 1 FROM info_article a where a.id=b.article_id and a.power=1 AND b.blog_id!=a.blog_id)
AND EXISTS(SELECT 1 FROM info_article_blog bl,info_kind k where bl.blog_id=k.blog_id and k.id=bl.kind_id and bl.article_id=b.article_id and k.default_kind_id=b.default_kind_id)
ORDER BY b.creation_time DESC

在sybase下执行没有问题,但是到了oracle会提示b.creation_time 为无效标识

可以有两种处理方式:

a.select的时候不用*而是吧所有的需要的字段都列出来

b.先把union后的数据全抱起来,在用select order by来处理

select * from
(
SELECT b.* FROM commend_articles_bykind b
WHERE b.blog_type=1 AND b.group_type=2 AND b.default_kind_id=1 AND b.cooperator_id=0
AND EXISTS(SELECT 1 FROM info_article a WHERE a.id=b.article_id AND a.power=1 AND b.blog_id=a.blog_id)
UNION ALL
SELECT b.* FROM commend_articles_bykind b WHERE b.blog_type=1 AND b.group_type=2 and b.default_kind_id=1 AND b.cooperator_id=0
AND EXISTS(SELECT 1 FROM info_article a where a.id=b.article_id and a.power=1 AND b.blog_id!=a.blog_id)
AND EXISTS(SELECT 1 FROM info_article_blog bl,info_kind k where bl.blog_id=k.blog_id and k.id=bl.kind_id and bl.article_id=b.article_id and k.default_kind_id=b.default_kind_id)
) t
ORDER BY t.creation_time DESC;

下面那条语句,sybase也是支持的。

4.其它一些特殊的函数类如getdate等~这个根据实际处理。

5.oracle就不用指明走哪个索引,我们就不写出来了,让oracle自己去分析。因为即使你这个写上去了 他也可能不去选择这个索引 只是提示。oracle有成本分析 会选择最优的执行的

6.

sybase:

WHERE r.id=*sch.region_id AND sch.id=*stu.school_id 表示右连接

WHERE r.id*=sch.region_id AND sch.id*=stu.school_id 表示左连接

oracle

WHERE r.id(+)=sch.region_id AND sch.id(+)=stu.school_id 表示右连接

WHERE r.id=sch.region_id(+) AND sch.id=stu.school_id(+) 表示左连接

7.时间,sybase可以直接传入时间字符串,oracle需要to_date(2011-12-05 14:00:00”,’yyyy-mm-dd hh24:mi:ss’)转换,但是强烈建议直接用Date类型传入。

Oracle 中 rownum

先看

select * from (select * from sys_option order by id desc) where rownum <=2

select * from sys_option where rownum <=2 order by id desc

上面两条SQL执行一般情况下是相同的,但是推荐使用第一种,第二种会先根据预期情况执行select * from sys_option where rownum <=2然后根据得到的这两条数据进行id排序,这里因为sys_option默认可能就是根据id进行了排序所以两个结果是一样的

在SQL稍微复杂点的情况下,很可能第二种情况不能取到正确的结果,所以推荐使用第一种方式,虽然写起来麻烦一些~

DBCP 的validationQuery

网上很多评论说DBCP有很多BUG,但是都没有指明是什么BUG,只有一部分人说数据库如果因为某种原因断掉后再DBCP取道的连接都是失效的连接,而没有重新取。就此研读了一下DBCP的代码,共享之。

分析

DBCP使用apache的对象池ObjectPool作为连接池的实现,有以下主要的方法

Object borrowObject() throws Exception;从对象池取得一个有效对象

void returnObject(Object obj) throws Exception;使用完的对象放回对象池

void invalidateObject(Object obj) throws Exception;使对象失效

void addObject() throws Exception;生成一个新对象
ObjectPool的一个实现就是GenericObjectPool,这个类使用对象工厂PoolableObjectFactory实现对象的生成,失效检查等等功能,以其实现数据库连接工厂PoolableConnectionFactory做以说明,主要方法:

Object makeObject() throws Exception; 使用ConnectionFactory生成新连接

void destroyObject(Object obj) throws Exception;关闭连接

boolean validateObject(Object obj); 验证连接是否有效,如果_validationQuery不空,则使用该属性作为验证连接是否有效的sql语句,查询数据库

void activateObject(Object obj) throws Exception;激活连接对象

void passivateObject(Object obj) throws Exception; 关闭连接生成过的Statement和ResultSet,使连接处于非活动状态

 

而GenericObjectPool有几个主要属性

_timeBetweenEvictionRunsMillis:失效检查线程运行时间间隔,默认-1

_maxIdle:对象池中对象最大个数

_minIdle:对象池中对象最小个数

_maxActive:可以从对象池中取出的对象最大个数,为0则表示没有限制,默认为8

在构造GenericObjectPool时,会生成一个内嵌类Evictor,实现自Runnable接口。如果_timeBetweenEvictionRunsMillis大于0,每过_timeBetweenEvictionRunsMillis毫秒Evictor会调用evict()方法,检查对象的闲置时间是否大于 _minEvictableIdleTimeMillis毫秒(_minEvictableIdleTimeMillis小于等于0时则忽略,默认为30分钟),是则销毁此对象,否则就激活并校验对象,然后调用ensureMinIdle方法检查确保池中对象个数不小于_minIdle。在调用returnObject方法把对象放回对象池,首先检查该对象是否有效,然后调用PoolableObjectFactory 的passivateObject方法使对象处于非活动状态。再检查对象池中对象个数是否小于_maxIdle,是则可以把此对象放回对象池,否则销毁此对象

还有几个很重要的属性,_testOnBorrow、_testOnReturn、_testWhileIdle,这些属性的意义是取得、返回对象和空闲时是否进行验证,检查对象是否有效,默认都为false即不验证。所以当使用DBCP时,数据库连接因为某种原因断掉后,再从连接池中取得连接又不进行验证,这时取得的连接实际已经时无效的数据库连接了。网上很多说DBCP的bug应该都是如此吧,只有把这些属性设为true,再提供_validationQuery语句就可以保证数据库连接始终有效了,oracle数据库可以使用SELECT COUNT(*) FROM DUAL,不过DBCP要求_validationQuery语句查询的记录集必须不为空,可能这也可以算一个小小的BUG,其实只要_validationQuery语句执行通过就可以了。

注意事项

所以使用DBCP连接池放必须注意构造GenericObjectPool对象时

validationQuery:SELECT COUNT(*) FROM DUAL

_testOnBorrow、_testOnReturn、_testWhileIdle:最好都设为true

_minEvictableIdleTimeMillis:大于0 ,进行连接空闲时间判断,或为0,对空闲的连接不进行验证

_timeBetweenEvictionRunsMillis:失效检查线程运行时间间隔,如果小于等于0,不会启动检查线程

 

一个PL/PGSQL写的postgreSQL触发行数

第一次接触这个语法,可能写的有点不规范,功能能实现,发上来主要是可以看出写几本的语法规则,要深入了解还是需要看看文档。也不难,我当时从完全不了解到写出来也就差不多一个下午个晚上的时间。记得那天下很大的雪,朋友来我这里住了一个晚上。

 
[SQL]
CREATE OR REPLACE FUNCTION user_upgrade() RETURNS trigger AS $BODY$
DECLARE
member_condition integer; –升级所需要的消费数
shower_condition integer; –升级所需要的积分数
xf_amount bigint; –某个会员本自然月消费的金币总额,注意去掉金币类礼物消费
jf_amount bigint; –某个嘉宾本自然月获得的积分总额,注意去掉金币类礼物积分
member_now_level integer; –某个会员此时的等级id
shower_now_level integer; –某个嘉宾此时的等级id
BEGIN
/*
* 首先对会员消费升级
*/
— 取得当前会员的等级
SELECT nlevel_id FROM tbuseraccount INTO member_now_level WHERE nuserid = NEW.nuserid;
IF member_now_level >= 101 AND member_now_level <= 104 THEN -- 升级所需要的消费数 SELECT user_grade_condition FROM tb_user_grade INTO member_condition WHERE user_grade_id = member_now_level + 1; -- 取得送礼会员此月的消费总额 SELECT SUM(nkbmoney) FROM tbusertradelog INTO xf_amount WHERE (ngiftid<406 OR ngiftid>412) AND date_trunc(‘MONTH’, dtime)=date_trunc(‘MONTH’, current_timestamp) AND nuserid = NEW.nuserid;
— 升级
IF xf_amount >= member_condition THEN
— LEVEL+1
UPDATE tbuseraccount set nlevel_id = nlevel_id + 1 WHERE nuserid = NEW.nuserid;
— HEXLEVEL+256,如果member_now_level=102则+4128512
IF member_now_level = 102 THEN
UPDATE tbuseraccount set nlevel = nlevel + 4128512 WHERE nuserid = NEW.nbuddyid;
ELSE
UPDATE tbuseraccount set nlevel = nlevel + 256 WHERE nuserid = NEW.nbuddyid;
END IF;
— 写升级日志表
INSERT INTO tb_user_levellog (nuserid,oldlevel,nowlevel,dtime) VALUES (NEW.nuserid,member_now_level,member_now_level + 1,current_timestamp);
END IF;
END IF;

/*
* 第二步对嘉宾积分升级
*/
— 取得当前嘉宾的等级
SELECT nlevel_id FROM tbuseraccount INTO shower_now_level WHERE nuserid = NEW.nbuddyid;
IF shower_now_level >= 201 AND shower_now_level <= 204 THEN -- 升级所需要的积分数 SELECT user_grade_condition FROM tb_user_grade INTO shower_condition WHERE user_grade_id = shower_now_level + 1; -- 取得收礼会员此月的积分总额 SELECT SUM(nmoney) FROM tbusertradelog INTO jf_amount WHERE (ngiftid<406 OR ngiftid>412) AND date_trunc(‘MONTH’, dtime)=date_trunc(‘MONTH’, current_timestamp) AND nbuddyid = NEW.nbuddyid;
— 升级
— RAISE EXCEPTION ‘jf_amount:%;shower_condition:%’,jf_amount,shower_condition;
IF jf_amount >= shower_condition THEN
— LEVEL+1
UPDATE tbuseraccount set nlevel_id = nlevel_id + 1 WHERE nuserid = NEW.nbuddyid;
— hexLEVEL+256
UPDATE tbuseraccount set nlevel = nlevel + 256 WHERE nuserid = NEW.nbuddyid;
— 写升级日志表
INSERT INTO tb_user_levellog (nuserid,oldlevel,nowlevel,dtime) VALUES (NEW.nbuddyid,shower_now_level,shower_now_level + 1,current_timestamp);
END IF;
END IF;
RETURN NULL;
END;
$BODY$ LANGUAGE ‘plpgsql’

— 下面是给表添加个触发器
— Trigger: user_upgrade on tbusertradelog

— DROP TRIGGER user_upgrade ON tbusertradelog;

CREATE TRIGGER user_upgrade
AFTER INSERT
ON tbusertradelog
FOR EACH ROW
EXECUTE PROCEDURE user_upgrade();
[/SQL]

远程无法访问本地SqlServer解决

1,查看netstat -an看看1433端口有没有启动

2,如果没有启动则到http://www.microsoft.com/downloads/details.aspx?displaylang=zh-cn&FamilyID=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5下载SQL2000-KB884525-SP4-x86-CHS.EXE

3,安装过程中出现以前进行的程序安装创建了挂起的文件操作”请按照下面步骤解决:

a、重启机器,再进行安装,如果发现还有该错误,请按下面步骤
b、在开始->运行中输入regedit
c、到HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager 位置
d、选择文件->倒出,保存
e、在右边窗口右击PendingFileRenameOperations,选择删除,然后确认
f、重启安装,问题解决