优化mysql

org.springframework.dao.IncorrectResultSizeDataAccessException

今天在写一个游戏接口的时候,以为数据库中就一条记录,想当然的写下了

select appid, code, name, url from hall_games where code=:code

测试机上一运行就出现这个异常。
org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 0 or 1, actual 8: com.xiaonei.in.dao.LoginUserDAO#getHallGameInfo

于是查看文档。
Data access exception thrown when a result was not of the expected size, for example when expecting a single row but getting 0 or more than 1 rows。
当期望返回的结果记录是1时,如果返回值为0或者>1会抛此异常。于是修改sql,当需要一条数据的时候,还是老实加上limit 1比较好。

select appid, code, name, url from hall_games where code=:code limit 1

work twitter-mysql insert or replace

When we need to insert some data into databases, we can utilise the basic “insert” sql, those we usually used in the past work,for example:

insert into someTable (colum1,colum2,..) values (val1, val2, ...);
insert into someTable values (val1, val2, val3 ...)

But in the development,we sometimes require some unique date in the database, in another word, when the sql insert some into the db, if find the same record has existed,then ignore it ,and continue to do the next query,

insert ignore into someTable (colum1,colum2,..) values (val1, val2, ...);
insert ignore into someTable values (val1, val2, val3 ...)

At the same time , some individuals assert that they may use the “replace” query to replace the “insert ignore”.
There are some attention that are alerted.
1.replace need the primary key or the unique index to update, thinking the below sql:

replace into tableA values(val1, val2, ...);
replace into tableA select val1, val2, ... form tableB
replact into tableA set colum1 = val1, colum2 = val2, ....., columN = concat(valN, "some constant");

Notice: if use the “concat” ,the updated data can not include NULL value, for instance, concat(“tom”, NULL, “david”), the result will be NULL, it can not be the “tomdavid”
2. if some want to update a special line, the next sql is good:

insert ignore into tableA (colum1, colum2, ..., columN) values (val1, val2, ..., valN); //Use ignore key word
update tableA set columN = columN | 1<< [$constant]           //calculate the new colum value 

Use crontab to finish works which will deal with access logs and load into infobright database

在昨天的文章中,我们讲述了如何使用Python来访问Java程序,在今天我们继续聊聊脚本的知识。最近由于业务的需要,给数据运维人员搞了个先进的数据查询引擎-Infobright,有关infobright的鼎鼎大名早已经是如雷贯耳,数据压缩上确实很牛叉,可以参考《Infobright数据仓库搭建》《Infobright的数据类型以及优化相关》。这几篇都跟mysql优化类似,但是略有不同还是注意的好,为了更好的优化效果,比如我在创建数据库表的时候就把原来的Varchar类型的字段创建成了Char,因为varchar的效率很低。
数据引擎搭建起来了,接下来我们需要自动的把数据加载到我们的数据库,由于众所周知的原因,目前社区办的ICE只支持load加载数据,所以我们得把我们的日志文件先处理成Infobright能够接受的类型,比如常见的csv格式。然后使用脚本自动的把我们的结果数据搞到数据库里面就可以了。
第一个脚本,完成日志目录的创建,日志合并,日志处理

#!/bin/sh

export LOG_DIR=/data1/remote-log-server/log/
today=`date "+%Y-%m-%d"`
yestoday=`date -d '1 days ago' "+%Y-%m-%d"`

GameLoginPath=$LOG_DIR/gamelogin/$today

#merge yesterday log
/bin/cat $LOG_DIR/gamelogin/$yestoday/gamelogin.log.* > $LOG_DIR/gamelogin/$yestoday/gamelogin.log
#use python script deal with access log to infobright data format
/usr/bin/python /data1/weige/python/gamelogin_weige.py $yestoday 99
#auto load data which can be accepted by infobright engine
/bin/sh /data1/remote-log-server/update_data_to_db.sh $yestoday

#create today log dir
if [ ! -d "$GameLoginPath" ]; then
        /bin/mkdir -p "$GameLoginPath"
fi

接下来的章节,我们将详细分拆解释我们这个脚本中间运行的这三个脚本,同时进一步对resin容器进行优化。

xtrabackup对数据库进行备份

一个大型的网站,数据的完整性往往是重中之重,因此,我们一般会使用传统的master-slave的架构来确保能有多份数据副本,但是,这种结构有其不 足之处,如被执行”delete”、”truncate”、”drop”,亦或是不可抗拒的原因造成的数据丢失情况,因此,这时就需要我们能够留下数据快 照,以便在发生这样情况的时候,尽可能的挽救我们的数据。

数据快照的方式多种多样,这里介绍一个现在正在使用的方式:使用xtrabackup(以下都以version=1.6为例)进行数据热备。

Xtrabackup是由percona开发的一个开源软件,此软件可以说是innodb热备工具ibbackup的一个 开源替代品。这个软件是由2个部分组成的:xtrabackup和innobackupex。Xtrabackup专门用于innodb引擎和 xtraDB引擎;而innobackupex是专门用于myisam和innodb引擎,及混合使用的引擎。如果你想使用源代码方式安装的话,则会发现其安装方式有点古怪,这是因为它采用的在MySQL源代码上打补丁构建的方式。

tar zxf xtrabackup-0.8.tar.gz
cd xtrabackup-0.8
./configure
make
##进行到这里时,千万别惯性使然接着make install,那样就会接着安装MySQL了,正确方法是接着:
cd innobase/xtrabackup/
make
make install

在备份数据的过程中,我们需要解决几个问题:

1、数据库服务器空间不足,就现有服务器来说,一般都是600G硬盘,往往数据文件就有300G+,因此,本机备份不可取,而且也不太能满足容灾的需要

2、备份的维护成本,尽量使备份能够可配置化。

为了解决上面2个问题,我们这样来操作:

先做下面几个假设,以便能够进行讲解:

1、假设我们需要备份的database在服务器IP:A,并且在A上已经安装xtrabackup

2、假设我们要将数据备份到服务器IP:B

在B上执行的脚本片段如下:

for .. in {…..};do

……

#在本地后台启动1234监听,以便接受备份文件

nc -d -l 1234 > /data/$A/20100118000001.tar &

#ssh到服务器A执行备份,并将备份包推送到备份机B上。同时记录下log。

ssh $A ‘innobackupex-1.5.1 –stream=tar –include=’renren.*’ –throttle=500 –socket=/data/mysql/backup/my
sql.sock –defaults-file=/data/dbbackup/my.cnf –user=root –password=xxxxxxx –slave-info /data/dbbackup | nc
$B 1234′ < /dev/null > /data/xtrabackup.log 2>&1

……

done;

从上面脚本片段我们能看出:我们使用了xtrabackup的备份脚本 innobackupex-1.5.1,对上面涉及到的参数进行解释:

–stream=tar : 告诉xtrabackup,备份需要以tar的文件流方式输出。

–include=’renren.*’:备份包含的库表,如例:意思是要备份renren库中所有的表。如果需要全备份,则省略这个参数;如果需 要备份renren库下的2个表:tableA & tableB,则写成:–include=’renren.tableA|renren.tableB’;再如果renren库下只有2个前缀是 table的表,你还可以写成:–include=’renren.table*’。

–throttle=500:xtrabackup在备份过程中,IO操作比较多,因此需要限定一下IO操作。以免服务器压力过大,不过好像作用不太明显,也不知道是不是设置的还是过大。待测…

–socket=/data/mysql/backup/mysql.sock:指定mysql.sock所在位置,以便备份进程登录mysql.

–defaults-file=/data/dbbackup/my.cnf :如果您的/etc/my.cnf中没有使用如[mysqld3306]的片段启动mysql,则不需要这行参数,如果使用了上面的片段,那么 xtrabackup是无法解析/etc/my.cnf文件的(严格上来说是会解析错误),所以,我们需要手工完成一个my.cnf文件,文件片段如下, 只需要指定6个参数即可让xtrabackup正常工作,各参数意思请参考mysql文档:

[mysqld] datadir=/data/var
innodb_data_home_dir = /data/ibdata
innodb_data_file_path=ibdata1:10M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /data/iblogs
innodb_log_files_in_group = 2
innodb_log_file_size = 1G

–user=root –password=xxxxxxx :登录账号密码,root用户

–slave-info :告诉xtrabackup,最后备份文件中包含slave信息

xtrabackup可以在不加锁的情况下备份innodb数据表,不过此工具不能操作myisam。innobackupex-1.5.1是一个脚本封装,能同时处理innodb和myisam,但在处理myisam时需要加一个读锁。 更多的使用方法和注意事项,可以参考这篇文章

mysql使用小记optimize和analyze

收录几个常用的命令,免得自己每次查找起来费劲。

1. 优化表使用optimize
语法 : optimize table 表名字
另外,还可以使用mysqlcheck
语法:

# mysqlcheck -o 数据库名 表名 -uroot -p111111 (一张表)
# mysqlcheck -o 数据库名 表名1 表名2 -uroot -p111111 (多张表)
# mysqlcheck -o 数据库名 -uroot -p111111 (对整个数据库)

最后,myisamchk一样可以用来完成类似的工作。语法会复杂一点,dba一般喜欢用这种的,或者习惯了linux命令操作的想必也会喜欢。基本语法如下:

# myisamchk --quick --check-only-changed --sort-index --analyze 表名
# myisamchk -r 表名 (参数-r表示对表进行修复,同时也删去了浪费的空间)
# myisamchk -r /usr/local/mysql/data/testblog/article (指定表所在的路径)

新手还是推荐使用optimize吧,简单容易操作。需要特别注意的是,optimize需要有足够的硬盘空间,否则可能会破坏表,导致不能操作,还得请optimize的师兄repaire来帮忙。

2. 对数据库表进行分析

同样有三种方法。
连接到MySQL时,使用ANALYZE TABLE语句;
利用mysqlcheck命令行工具(服务器需要运行,并且只对MyISAM表起作用);
利用myisamchk命令行工具(服务器不应该运行,或无对所操作的表发生互操作)

操作示例:

# ANALYZE TABLE 表名;
# mysqlcheck -a 数据库名 表名 -uroot -p111111
# mysqlcheck -a 数据库名 表名1 表名2 -uroot -p111111
如果试图对不支持分析操作的表进行分析(如InnoDB),那操作将无法进行
# myisamchk -a /usr/local/mysql/data/数据库/表名

analyze的结果如下:
mysql> ANALYZE TABLE users;
+———-+———+———-+———-+
| Table | Op | Msg_type | Msg_text |
+———-+———+———-+———-+
| fb.users | analyze | status | OK |
+———-+———+———-+———-+
1 row in set (1.03 sec)

更多的Mysql使用,可以参考mysql用户权限管理和mysql操作技巧,获取记录的ID和父ID.