标签归档：mysql

mysql linux 备份脚本

发表评论 
#!/bin/sh
# mysql data backup script
#
# use mysqldump --help,get more detail.
#
BakDir=/root/back/mysql
LogFile=/root/back/mysql/mysqlbak.log
DATE=`date +%Y%m%d%H%M%S`
echo " " >> $LogFile
echo " " >> $LogFile
echo "-------------------------------------------" >> $LogFile 
echo $(date +"%y-%m-%d %H:%M:%S") >> $LogFile 
echo "--------------------------" >> $LogFile 
cd $BakDir
DumpFile=$DATE.sql
GZDumpFile=$DATE.sql.tar.gz
/usr/local/bin/mysqldump -u user -pname  --database -h 127.0.0.1 db> $DumpFile
echo "Dump Done" >> $LogFile
tar czvf $GZDumpFile $DumpFile >> $LogFile 2>&1 
echo "[$GZDumpFile]Backup Success!" >> $LogFile 
rm -f $DumpFile 
#cd $BakDir/daily
cd $BakDir   
echo "Backup Done!"
echo "please Check $BakDir Directory!"
echo "copy it to your local disk or ftp to somewhere !!!"
find $BakDir -ctime +30 -exec rm {} \;
echo "delete file over 30 days"

其中user，name和db请根据自己的情况修改

mysql自带的一些工具

发表评论

mysqlbinlog 这个大家都应该知道的，用来看mysql的binlog的。mysql自带。

mysqldumpslow 用于分析mysql的slow log。自带的。结果如下。

Count: 1 Time=4.00s (4s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[127.0.0.1] select * from user ignore key (PRIMARY) where address = ‘S’ order by id asc limit NCount: 3 Time=4.00s (12s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[127.0.0.1] select * from user where address = ‘S’ order by id asc limit NCount: 2 Time=4.00s (8s) Lock=0.00s (0s) Rows=32.0 (64), root[root]@[127.0.0.1] select count(*), age from user group by age with rollup

mysql_explain_log 分析mysql的通用查询日志（用log选项打开的），自带的。需要注意的是用客户端连接mysql的时候需要指定数据库，使用use XXX选择数据库的时候此工具无法分辨出sql使用的是什么数据库。

explain_log provided by http://www.mobile.de=========== ================================Index usage ————————————Table test.user: — count key: 1 PRIMARY count possible_keys: 1 PRIMARY count type: 1 ALL 1 rangeQueries causing table scans ——————-EXPLAIN select * from test.userSum: 1 table scansSummary —————————————Select: 4 queriesUpdate: 0 queriesInit: 0 timesField: 6 timesRefresh: 0 timesQuery: 35 timesStatistics: 0 timesLogfile: 59 linesStarted: Tue Mar 27 10:45:12 2007Finished: Tue Mar 27 10:45:12 2007

perror 显示错误码对应的错误信息。mysql自带。

shell>perror 13OS error code 13: Permission denied

mysqlslap 一个性能测试的工具。mysql的test suite中带的。

mtop 一个进程监视的工具，可以直接在里面显示出explain的结果。也可以使用watch -n 1 mysqladmin status processlist来监视。

127.0.0.1 mysqld 5.0.27-standard-log up 0 day(s), 0:10 hrs1 threads: 1 running, 1 cached. Queries/slow: 26/0 Cache Hit: 100.00%Opened tables: 0 RRN: 467 TLW: 0 SFJ: 0 SMP: 0 QPS: 0ID USER HOST DB TIME COMMAND STATE INFO26 root 1270.0.1:52841 Query show full processlist

mysql_explain_slow_log 和mysql_explain_log差不多，不过这个是分析slow log的。

mysql_explain_slow_log======================Index usage ————————————Table test: — count type: 10 ALLTable test.click: — count type: 1 ALLTable test.user: — count key: 1 username,useraa 9 usertime 16 PRIMARY 29 useraa count possible_keys: 1 PRIMARY 7 useraa,username 9 usertime 23 useraa count type: 1 index_merge 2 range 23 ref 29 index 31 ALLQueries causing table scans ——————-EXPLAIN select * from test.userEXPLAIN select * from test.user order by rand() limit 5省略若干行…Sum: 56 table scansSummary —————————————Select: 97 queriesUpdate: 0 queriesLoad: 33 queriesLogfile: 1657 linesStarted: Tue Mar 27 11:01:14 2007Finished: Tue Mar 27 11:01:14 2007

mysqlreport 监视分析mysql状态的工具。

MySQL 5.0.27-standard-l uptime 0 0:17:57 Tue Mar 27 11:04:15 2007__ Key _________________________________________________________________Buffer used 0 of 32.00M %Used: 0.00 Current 3.68M %Usage: 11.51Write ratio 0.000Read ratio 0.000__ Questions ___________________________________________________________Total 170 0.2/sSlow 0 0/s %Total: 0.00 %DMS: 0.00DMS 105 0.1/s 61.76__ Table Locks _________________________________________________________Waited 0 0/s %Total: 0.00Immediate 109 0.1/s__ Tables ______________________________________________________________Open 8 of 2048 %Cache: 0.39Opened 14 0.0/s__ Connections _________________________________________________________Max used 2 of 500 %Max: 0.40Total 38 0.0/s__ Created Temp ________________________________________________________Disk table 0 0/sTable 16 0.0/sFile 5 0.0/s

mysqlsla 分析mysql的各种日志。分析slow log的结果如下。

Reading slow log ‘db-slow.log’170 total queries, 72 uniqueSorting by ‘t’__ 001 _______________________________________________________________________Count : 15 (8%)Time : 1297.000 total, 86.467 avg, 3.000 min to 833.000 max 9:20% 10:13% 833:6% 67:6% 57:6% 3:6% 61:6% 58:6% 20:6% 103:6% (87%)Lock : 0.000 total, 0.000 avg, 0.000 min to 0.000 maxRows sent : 0 avg, 0 min to 0 maxRows examined : 0 avg, 0 min to 0 maxUser : root[root]@/127.0.0.1 (100%)SET insert_id=N;LOAD data infile ‘S’ INTO table user;省略若干行…

mysqlsniffer 监听mysql通讯的工具。

mysqlsniffer listening for MySQL on interface eth0 port 3306server > 192.168.1.170.32958: ID 0 len 65 Handshake 192.168.1.170.32958 > server: ID 1 len 38 Handshake (new auth) server > 192.168.1.170.32958: ID 2 len 7 OK 192.168.1.170.32958 > server: ID 0 len 18 COM_QUERY: SELECT DATABASE()server > 192.168.1.170.32958: ID 1 len 1 1 Fields ID 2 len 32 Field: ..DATABASE() ID 3 len 5 End ID 4 len 1 || NULL || ID 5 len 5 End 192.168.1.170.32958 > server: ID 0 len 5 COM_INIT_DB: testserver > 192.168.1.170.32958: ID 1 len 7 OK 192.168.1.170.32958 > server: ID 0 len 15 COM_QUERY: show databasesserver > 192.168.1.170.32958: ID 1 len 1 1 Fields ID 2 len 49 Field: .SCHEMATA.Database ID 3 len 5 End ID 4 len 19 || information_schema || ID 5 len 3 || aa || ID 6 len 6 || mysql || ID 7 len 7 || sakila || ID 8 len 5 || test || ID 9 len 6 || world || ID 10 len 5 End 192.168.1.170.32958 > server: ID 0 len 12 COM_QUERY: show tablesserver > 192.168.1.170.32958: ID 1 len 1 1 Fields ID 2 len 57 Field: .TABLE_NAMES.Tables_in_test ID 3 len 5 End ID 4 len 6 || click || ID 5 len 8 || s_click || ID 6 len 5 || user || ID 7 len 5 End 192.168.1.170.32958 > server: ID 0 len 7 COM_FIELD_LIST: clickserver > 192.168.1.170.32958: ID 1 len 42 Field: test.click.id ID 2 len 51 Field: test.click.user_id ID 3 len 62 Field: test.click.promotion_id ID 4 len 49 Field: test.click.status ID 5 len 78 Field: test.click.record_time ID 6 len 5 End 192.168.1.170.32958 > server: ID 0 len 9 COM_FIELD_LIST: s_clickserver > 192.168.1.170.32958: ID 1 len 46 Field: test.s_click.id ID 2 len 66 Field: test.s_click.promotion_id ID 3 len 53 Field: test.s_click.status ID 4 len 59 Field: test.s_click.click_num ID 5 len 73 Field: test.s_click.record_date ID 6 len 5 End 192.168.1.170.32958 > server: ID 0 len 6 COM_FIELD_LIST: userserver > 192.168.1.170.32958: ID 1 len 40 Field: test.user.id ID 2 len 43 Field: test.user.name ID 3 len 45 Field: test.user.email ID 4 len 49 Field: test.user.address ID 5 len 41 Field: test.user.age ID 6 len 76 Field: test.user.regist_time ID 7 len 5 End 192.168.1.170.32958 > server: ID 0 len 27 COM_QUERY: select * from user limit 1server > 192.168.1.170.32958: ID 1 len 1 6 Fields ID 2 len 38 Field: test.user.id ID 3 len 42 Field: test.user.name ID 4 len 44 Field: test.user.email ID 5 len 48 Field: test.user.address ID 6 len 40 Field: test.user.age ID 7 len 56 Field: test.user.regist_time ID 8 len 5 End ID 9 len 34 || 1 | ll | ff | ll | 10 | 2007-03-21 09:58:07 || ID 10 len 5 End 192.168.1.170.32958 > server: ID 0 len 1 COM_QUIT192.168.1.57.60248 > server: ID 0 len 48 COM_QUERY: SELECT COUNT(*) FROM product WHERE index_flag=0server > 192.168.1.57.60248: ID 1 len 1 1 Fields ID 2 len 30 Field: ..COUNT(*) ID 3 len 1 End ID 4 len 2 || 0 || ID 5 len 5 End 70 MySQL packets captured (2022 bytes)

freebsd升级mysql client

3条回复

mysql serve 是5.1.x的，客户端是 5.0.x的，用phpmyadmin,总有一个提示，说版本不匹配，感觉不爽。
用Ports的方法升级客户端，不行，报错。说5.1和5.0有冲突，只能先删除。
用了 portmaster -o databases/mysql51-client mysql-client-5.0.89
也不行。
加上 export CONFLICTS=” 后执行，还是不行。看了
pkg_info mysql\*
有
Required by:
php5-mysql-5.2.12
php5-mysqli-5.2.12
因为还跑着网站，就没敢贸然行事。
这天趁网站人少的时候，干了一把
删除老的package
#pkg_delete -f mysql-client-5.0.89
到mysql5.1 client的目录
#cd /usr/ports/databases/mysql51-client
装
#make install clean
。。
。。
等待
。。
好了看一下版本
#mysql -V
mysql Ver 14.14 Distrib 5.1.42, for portbld-freebsd8.0 (amd64) using 5.2
用portmaster升级2个有依赖的php组件
#portmaster php5-mysql php5-mysqli
等待
。
。
重启apache，好了。
出乎意料的是安装过程中，php可以正常的链接mysql。网站正常，还以为pkg_delete会删除使用的so文件，导致网站出状况。

感谢hshh

用oracle的merge实现mysql的replace into

发表评论

mysql有一个replace into的dml语句，类似insert，但是会在insert之前检查表的唯一索引或主键。如果存在，就改为update操作。
这在很多应用中是一个很常用的操作。有了这个replace into ，就可以将一个 select后判断后做update or insert改为一句话，甚是方便。

oracle9以后(好像)有了merge into，也可以实现类似功能，sql demo如下

merge into qq a
using (select ‘2022’ company_no, ‘cname’ company_name from qq where rownum<2) b on (a.company_no = b.company_no) WHEN MATCHED THEN UPDATE SET a.company_name = a.company_name|| 'a' WHEN NOT MATCHED THEN INSERT (a.company_no, a.company_name) VALUES (b.company_no, b.company_name); [/code]

mysql里面的oracle decode

1条回复

oracle里面有一个decode的函数，顾名思义，可以将1,2,3按照需求转成a,b,c，最后还有一个缺省值。

mysql里面好像没有这个函数。但是可以用另外的函数来实现类似的效果。

IF(expr1,expr2,expr3)
如果expr1是TRUE(expr1<>;0且expr1<>;NULL)，那么IF()返回expr2，否则它返回expr3。IF()返回一个数字或字符串值

oracle的写法

SELECT decode(ttype,1,'a',2,'b',3,'c','d') FROM taba

可以在mysql里写成

SELECT if( ttype =1, 'a', if( ttype =2, 'b', if( ttype =3, 'c', 'd' ) ) ) 
FROM taba

mysql load data infile

1条回复

如何提高mysql load data infile的速度
测试数据2.5G，共有数据9427567条。用的mysql的large服务器的配置。
load一次需要大概10分钟左右。
建的表用的是MYISAM，调整了几个session的参数值

SET SESSION BULK_INSERT_BUFFER_SIZE=256217728;
SET SESSION MYISAM_SORT_BUFFER_SIZE=256217728;

运行结果如下

Query OK, 9427567 rows affected, 1558 warnings (3 min 55.21 sec)
Records: 9427567 Deleted: 0 Skipped: 0 Warnings: 0
google到的还可以

set global KEY_BUFFER_SIZE=256217728;
alter table tablename disable keys;

如何load数据里面带反斜杠(backslash)”\” 的数据
由于如果你没有指定FIELDS子句，则默认值为假设您写下如下语句时的值：
FIELDS TERMINATED BY ‘\t’ ENCLOSED BY ” ESCAPED BY ‘\\’
所以，如果你的数据里面有反斜杠(backslash)”\”的时候，数据会出现被截断的问题。出现这种问题，只要写上如下的fields子句即可
FIELDS TERMINATED BY ‘\t’ ENCLOSED BY ” ESCAPED BY ”

如何load不同编码的数据
原来用的4.X的mysql，我是select INTO OUTFILE ，只后用iconv,或者其他软件来做。可以参考这里，但是由于这次数据大，用ultraedit等软件打开都要半天。好在新版的mysql可以增加一个新的参数
CHARACTER SET gbk
我的文本数据是GBK的编码，数据表是utf8的，用这种方法测试成功。
如何load的时候只insert特定的列
比如表里面有比元数据多的列。可以在load的时候指定要插入的字段名字。

示例的代码如下：

LOAD DATA INFILE '~/data.txt'   
INTO TABLE fb0505   
CHARACTER SET  gbk   
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '' 
LINES TERMINATED BY '\n' STARTING BY ''  
(seller_id,fb_type,fb_date,item_url);

其中表fb0505里面还有一列是id。

mysql去除特殊asc码

发表评论

要做一个mysql数据导入并且更新的工作。数据源是excel里来的。
选中excel里面左上角的元素，ctrl+shift+end，选中全部有内容的cell。另存为一个abc.txt的文件。
数据只有2列，建了一个有2列的表，准备把数据load进去，用root用户登录的，在mysql的shell运行如下命令
load data infile ‘/root/test.dat’ into table mytable FIELDS TERMINATED BY ‘\t’;
结果报错。
ERROR 13 (HY000): Can’t get stat of ‘/root/test.dat’ (Errcode: 13)
后来google，说是权限的问题，看来可能是mysql不能读root目录下的文件，随便换了个目录，问题解决。
load data infile ‘/tmp/test.dat’ into table ppname FIELDS TERMINATED BY ‘\t’;
但是后来出现的问题，基本让我崩溃。
举个例子

mysql> select * from mytable;
+------+------+
| a    | b    |
+------+------+
 |aaa  | aaa
+------+------+
1 row in set (0.00 sec)

肉眼看上去，a和b是一样的，然后我就按照一样的去匹配，奇怪的是怎么做都不行，刚开始以为是大小写的问题，作了lower操作还是不行。
后来开始不相信自己的眼睛了，查看一下到底是什么

mysql> select hex(a),hex(b) from mytable;
+--------+----------+
| hex(a) | hex(b)   |
+--------+----------+
| 616161 | 6161610D |
+--------+----------+
1 row in set (0.00 sec)

这个时候才发现每个字符后边多了一个
0D
被这个问题搞了个把钟头，郁闷的要死。做了以下处理，终于乌云散尽看见阳光了。
update mytable set b=UNHEX(TRIM(TRAILING ‘0D’ FROM hex(b)));

奇怪的是我模拟不出来这种数据，本来以为是没有加“LINES TERMINATED BY ‘\n’”的问题，可是后来加了也不行，:S
看来眼见不一定为实啊。

用mysqldumpslow分析mysql的slow query log

4条回复

mysql有一个功能就是可以log下来运行的比较慢的sql语句，默认是没有这个log的，为了开启这个功能，要修改my.cnf或者在mysql启动的时候加入一些参数。如果在my.cnf里面修改，需增加如下几行

long_query_time = 1
log-slow-queries = /var/youpath/slow.log
log-queries-not-using-indexes

long_query_time 是指执行超过多久的sql会被log下来，这里是1秒。
log-slow-queries 设置把日志写在那里，可以为空，系统会给一个缺省的文件host_name-slow.log，我生成的log就在mysql的data目录
log-queries-not-using-indexes 就是字面意思，log下来没有使用索引的query。

把上述参数打开，运行一段时间，就可以关掉了，省得影响生产环境。

接下来就是分析了，我这里的文件名字叫host-slow.log。
先mysqldumpslow –help以下，俺主要用的是
-s ORDER what to sort by (t, at, l, al, r, ar etc), ‘at’ is default
-t NUM just show the top n queries
-g PATTERN grep: only consider stmts that include this string

-s，是order的顺序，说明写的不够详细，俺用下来，包括看了代码，主要有
c,t,l,r和ac,at,al,ar，分别是按照query次数，时间，lock的时间和返回的记录数来排序，前面加了a的时倒叙
-t，是top n的意思，即为返回前面多少条的数据
-g，后边可以写一个正则匹配模式，大小写不敏感的

mysqldumpslow -s c -t 20 host-slow.log
mysqldumpslow -s r -t 20 host-slow.log

上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。
mysqldumpslow -t 10 -s t -g “left join” host-slow.log
这个是按照时间返回前10条里面含有左连接的sql语句。

用了这个工具就可以查询出来那些sql语句是性能的瓶颈，进行优化，比如加索引，该应用的实现方式等。

apache child pid exit signal File size limit exceeded error

发表评论

一个正常使用的网站，LAMP架构，突然打不开了，但是另外一个二级域名的网站，在同一台服务器上可以打开，静态文件也可以打开，因为出故障前有段时间程序没有更新，程序错误的可能性不大。
telnet 到网站的80端口，也可以打开，现象很奇怪。
重起了apache，没用，mysql用命令行也可以使用，没问题，一时没了方向。
于是tail了apache的log，一个是正常的web log，一个是error_log。
观察之后，发现了异常现象，apache的error_log一直输出错误信息如下：

[Thu May 24 23:11:27 2007] [notice] child pid 23406 exit signal File size limit exceeded (25)
[Thu May 24 23:12:01 2007] [notice] child pid 23544 exit signal File size limit exceeded (25)
[Thu May 24 23:12:47 2007] [notice] child pid 23633 exit signal File size limit exceeded (25)
[Thu May 24 23:12:53 2007] [notice] child pid 22799 exit signal File size limit exceeded (25)

说有文件大小超过了限制。
google了一下，说是超过了apache 2G的文件限制，试用find命令在系统里面查找超大的文件

find / -size +1000000k

查找到2个文件，一个是

/xxx/xxx/page_parse_time.log
/xxx/yyy/support-popbytes_log

第一个文件page_parse_time.log的大小在2G左右，压缩备份后，

> /xxx/xxx/page_parse_time.log

，问题解决。
page_parse_time.log的文件内容是程序记录sql query的log以及执行时间的log，每个页面请求都会写上一些数据，这么超过2G也不奇怪了。

上次还遇到过一次mysql数据条数过多，的问题
报错信息如下：

LOGSQL Insert Failed: insert into adodb_logsql (created,sql0,sql1,params,tracer,timer) values( NOW(),?,?,?,?,?) The table 'adodb_logsql' is full

经查

mysql> select count(*) from adodb_logsql ;
 +----------+ 
| count(*) |
 +----------+ 
| 33127244 | 
+----------+

3千万，，，可能是有点大了，最后truncate了这个表，问题解决
可是最后查mysql的文档，关于Scalability and Limits部分没有说mysql最多可以存多少条记录，估计跟表的类型也相关。原文说的是
Handles large databases. We use MySQL Server with databases that contain 50 million records. We also know of users who use MySQL Server with 60,000 tables and about 5,000,000,000 rows.
只是不知道它说的50 million是一个database里面的所有记录还是所有表的记录加起来。后边的应该是所有60,000个表加起来的数据量。
————————
结论，必要的日志是需要的，但是要控制大小，有循环机制，或者有监控机制，切记，切记。

discuz mysql 数据库编码转换utf8->utf8

6条回复

帮朋友的论坛做了一次编码转换的工作。系统是windows 2003,mysql4.1 discuz 5
本来以为很简单，dump数据
mysqldump -u root -p –opt –default-character-set=utf8 \
–skip-set-charset olddb cdb_members > d:\bak\cdb_members.sql
出来，用工具转一下编码，然后再导入
mysql -u root -p –default-character-set=utf8 newdb < d:\bak\cdb_members.sql

实际操作并没有想象的那么简单，最后解决的方案，也不是那么复杂。
继续阅读 →