1月 10

网页产生二级菜单的SQL优化

将菜单放一张表中,查询时通过用户id查到组id,然后再用组id查权限表,获得有权限的菜单id。
最后组装成两级菜单的数据。
数据列为菜单名,菜单链接,菜单级别,父菜单id,该级菜单的排序。sql如下:

CREATE TABLE `column` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `code` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `sort_no` int(11) DEFAULT NULL,
  `tree_path` varchar(300) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


INSERT INTO `column` VALUES ('1', '网络运营', 'p0', '0', '1', null);
INSERT INTO `column` VALUES ('2', '系统管理', 'p9', '0', '9', null);
INSERT INTO `column` VALUES ('3', '开账户', 'p4', '1', '1', null);
INSERT INTO `column` VALUES ('4', '开业务', 'p66', '1', '2', null);
INSERT INTO `column` VALUES ('5', '财务', 'p2', '0', '2', null);
INSERT INTO `column` VALUES ('6', '用户查业务', 'p33', '5', '1', null);
INSERT INTO `column` VALUES ('7', '业务查用户', 'p55', '5', '2', null);
INSERT INTO `column` VALUES ('8', '查缴费', 'p99', '5', '3', null);
INSERT INTO `column` VALUES ('9', '客服', 'p44', '0', '4', null);

最初想是将表关联然后查找,sql如下:

select xc1.id as id1, xc1.name as name1, xc1.code as code1, xc1.parent_id as pid1, 
xc2.name as name2,xc2.code as code2 from column as xc1 
LEFT JOIN column as xc2 on xc2.parent_id = xc1.id 
where xc1.id in ( 
  select column_id from group_column as gr where group_id in( 
   select group_id from group_user where user_id=1
  ) 
) and xc2.id in ( 
  select column_id from group_column as gr where group_id in( 
   select group_id from group_user where user_id=1
  )  
) 
and xc1.parent_id=0 ORDER BY xc1.parent_id,xc1.sort_no,xc2.sort_no

这个sql有个缺点,一比较长,二获得权限部分写了两次。然后换个写法,先把有权限的选出然后再关联。

select xc2.id as id1, xc2.name as name1, xc2.code as code1, xc2.parent_id as pid1, 
  xc1.name as name2, xc1.code as code2 from (
	 select id,name,code,parent_id,sort_no from column where id in ( 
		select column_id from group_column as gr where group_id in( 
		  select group_id from group_user where user_id=1
		 ) 
    ) and parent_id <>0 ORDER BY parent_id,sort_no 
) as xc1
left join column as xc2 on xc1.parent_id=xc2.id 

因为只有10条数据,所以通过多次手动执行也没有发现性能差多少,但是短了不少。

7月 13

CentOS6安装mariadb

在CentOS6.5的64位机器上安装mariadb10.0.12。

mariadb下载位置
https://downloads.mariadb.org/interstitial/mariadb-10.0.12/source/mariadb-10.0.12.tar.gz

升级遥控的工具和库
yum install -y cmake cmake-* openssl-* libevent libevent-devel
yum install -y boost boost-devel libxml libxml-devel
yum install -y pcre pcre-devel
yum install -y gcc* g++* gcc-c++
yum install -y suitable suitable-*
yum install -y ncurses-devel.x86_64
yum install -y libaio.x86_64
yum install -y bison.x86_64

建立用户
groupadd -g 800 mysql
useradd -u 800 -g 800 -M -s /sbin/nologin -r mysql

开始编译安装
tar zxvf mariadb-10.0.12.tar.gz
cd mariadb-10.0.12
mkdir build
cd build/
cmake -DCMAKE_INSTALL_PREFIX=/program/mariaDB -DMYSQL_DATADIR=/program/mariaDB/data -DSYSCONFDIR=/etc ..
make
make install

cd /program/mariaDB
cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod 755 /etc/init.d/mysqld
cp ./support-files/my-huge.cnf /etc/my.cnf
vi /etc/my.cnf #编辑配置文件,在 [mysqld] 部分增加
datadir = /program/mariaDB/data #添加MariaDB数据库路径

以下为选做工作。
#./scripts/mysql_install_db –user=mysql #生成MariaDB系统数据库
#下面这两行把MariaDB的库文件链接到系统默认的位置,这样你在编译类似PHP等软件时可以不用指定MariaDB的库文件地址。
#ln -s /program/mariaDB/lib/mysql /usr/lib/mysql
#ln -s /program/mariaDB/include/mysql /usr/include/mysql
#直接修改密码/program/mariaDB/bin/mysqladmin -u root -p password “123456”

5月 23

python 生成统计图

  想用python+matplotlib生成每天用户使用的统计图。测试环境,CentOS6.5,数据存放于mysql中,python2.7,matplotlib使用easy_install安装。

  首先建一个表。

CREATE TABLE `member` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `user` varchar(15) DEFAULT NULL,
  `access_date` datetime DEFAULT NULL,
  `mark` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  测试数据部分还是写个python脚本生成,这样批量做就方便了。

#!/bin/python
#-*- coding:utf-8 -*-
# Filename:    main.py
# Revision:    1.0
# Date:        2012-06-14
# Author:      simonzhang
# web:         www.simonzhang.net
# Email:       simon-zzm@163.com
### END INIT INFO
import time
import random

# 基础设置
dict = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm',
        'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z',
        'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
        'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']
# 生成日期的unix起始时间
create_unix_time = 1396281600
# 生成每次日期的间隔时间。单位是小时。
hour_skip_start = 1
hour_skip_end = 12

def main():
    start_unix_time = create_unix_time 
    f = open('create.sql', 'wb')
    # 开始循环生成sql
    for i in range(280):
        # 生成用户名
        user_name = ''
        for j in range(random.randint(1,5)):
            user_name = user_name+random.choice(dict)
        # 生成时间
        start_unix_time = start_unix_time+(random.randint(hour_skip_start, hour_skip_end)*3600)
        get_create_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(start_unix_time))
        f.write("INSERT INTO `member` VALUES ('%s', '%s', '%s', null);\n" % (i, user_name, get_create_time))
    f.close()

if __name__ == '__main__':
    main()

  开始正式部分,获取数据并生成图片。

#!/bin/python
#-*- coding:utf-8 -*-
# Filename:    main.py
# Revision:    1.0
# Date:        2012-06-14
# Author:      simonzhang
# web:         www.simonzhang.net
# Email:       simon-zzm@163.com
### END INIT INFO
import time
import MySQLdb
from pylab import *
from matplotlib.font_manager import FontProperties

#### set
nearest_day = 32

def create_pic(memberData):
    print memberData
    # 将数据x,y整理两个列表
    x_date = []
    y_count = []
    for i in memberData:
        x_date.append(i[2])
        y_count.append(i[1])
    # 将x轴日期元组化
    zu = []
    da = []
    for j in range(1, len(x_date)/5+1):
        zu.append(j*5)
        da.append(x_date[j*5])
    # 开始生成表
    font = FontProperties(fname=r"/usr/share/fonts/truetype/freefont/msyh.ttf", size=10) 
    cla()
    label = u'测试' 
    plot(y_count)
    # x和y轴定义
    xlabel(u'日期', fontproperties=font)
    ylabel(u'统计数量', fontproperties=font)
    xticks(tuple(zu), tuple(da), fontproperties=font)
    title(u'统计', fontproperties=font)
    grid(True)
    legend(loc = 'lower right')
    savefig("test1.png")

def main():
    try: 
        conn=MySQLdb.connect(host='115.28.42.253',user='test',passwd='123456',db='pymatplotlib',port=3306,charset='utf8')
        cur=conn.cursor()
        start_date = time.strftime('%Y-%m-%d', time.localtime(time.time()-3600*24*nearest_day))
        end_date = time.strftime('%Y-%m-%d', time.localtime(time.time()))
        my_sql = "SELECT id,count(*),DATE_FORMAT(access_date, '%Y-%m-%d' ) \
                  FROM member \
                  WHERE access_date BETWEEN '"+start_date+"' and '"+end_date+"' \
                  GROUP BY DATE_FORMAT(access_date, '%Y-%m-%d' ) "
        print my_sql
        cur.execute(my_sql)
        cds=cur.fetchall()
        conn.close()
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])
    create_pic(cds)


if __name__ == '__main__':
    main()

  运行脚本完成,生成图片如下:
mysql数据生成图片

试验代码部分

6月 24

监控mysql从机同步状态脚本1.1

  之前写了个一个检查mysql从机的脚本(http://www.simonzhang.net/?p=1823),但是在使用中发现一个问题。如果数据库被重启了,但是同步的没有启动,此脚本检查还是正常,不会进行报警,数据不会同步。
  我做了个调整,每次检查同步主机的pos,通过crontab进行调用,如果多次都没有变化则进行告警。如果10分钟调用一次,设为3次,就是半个小时内没有更新则报警。
crontab配置如下:
*/10 * * * * /bin/bash /script/check_mysql_slave/check_mysql_slave.sh start >/dev/null 2>&1
部分代码如下:

#!/usr/local/bin/python
# -*- coding:utf-8 -*-
# -------------------------------------------------------------------------------
# Filename:    check_nagios.py
# Revision:    1.1
# Date:        2013-06-24
# Author:      simonzhang
# Email:       simon-zzm@163.com
# -------------------------------------------------------------------------------
import os
import pexpect
import time
import smtplib
from email.mime.text import MIMEText

#### base se
mysql_bin = '/program/mysql5/bin/mysql'
mysql_user = 'checkslavestatus'
mysql_pass = 'xxxxxxxxxx'
#设置错多少次开始告警
max_error = 3
mail_host = 'smtp.exmail.qq.com'
mail_user = 'warning@xxx.net'
mail_pwd = 'xxxxxxxxx'
mail_cc = "simon-zzm@163.com"
####

def mail_warn(error_ip):
    content = 'IP %s mysql slave is error!'%error_ip
    msg = MIMEText(content)
    msg['From'] = mail_user
    msg['Subject'] = 'mysql warnning %s'%error_ip
    msg['To'] = mail_to
    try:
        s = smtplib.SMTP()
        s.connect(mail_host)
        s.login(mail_user,mail_pwd)
        s.sendmail(mail_user,[mail_to],msg.as_string())
        s.close()
    except Exception ,e:
        print e

def main():
    error_context = ''
    #读取上次检查master同步点的记录
    try:
        f = open('MasterPos.txt', 'rb').read()
        try:
            old_master_pos = f.split(':')[0]
            error_count = f.split(':')[1]
        except:
            old_master_pos = 0
            error_count = 0
    except:
        old_master_pos = 0
        error_count = 0
        pass
    # 获得数据库同步状态
    status = os.popen("%s -u%s -p%s -e 'show slave status\G'"%
                      (mysql_bin,mysql_user,mysql_pass)).readlines()
    # 查看同步主节点数据
    for status_l in status:
        if status_l.find('Read_Master_Log_Pos: ') > 0:
            f = open('MasterPos.txt', 'wb')
            # 防止出现空值
            try:
                new_master_pos = int(status_l.split(': ')[1])
            except:
                new_master_pos = 0
            if int(new_master_pos) == int(old_master_pos) or int(old_master_pos):
                f.write('%s:%s' % (new_master_pos, int(error_count)+1))
            else:
                f.write('%s:0' % new_master_pos)
            f.close()
            if int(error_count)+1 > max_error:
                error_context += 'slave error!'
    # 判断是否报警
    print error_context:
    if len(error_context) > 1:
        ip = os.popen("/sbin/ifconfig|grep 'inet addr'|awk '{print $2}'").read()
        get_local_ip = ip[ip.find(':')+1:ip.find('n')]
        mail_warn("%s"%get_local_ip)

if __name__ == "__main__":
    main()

源代码