04/4/14

haproxy+keepalived+mysql+xinetd 实现 mysql 双机机制

1、功能描述

a、192.168.0.103 (以下称103,vip:192.168.0.123),192.168.0.104(以下称104,vip:192.168.0.124)分别部署haproxy,并通过keepalived绑定vip(103->123,104->124)。

b、程序代码通过vip连接数据库,103、104各部署一套slave,104从103slave同步。

c、当103宕机,123ip迁移到104。此时104同时有123、124两个vip,并响应123和124数据库请求。同理,104宕机时,103同时为vip123,124提供服务。

d、haproxy通过xinetd服务,启动mysqlchk服务。数据库正常返回http 200,异常则返回503.

e、优点:一、程序迁移数据库改动比较少,运维可完全控制。二、运维可根据情况,安排调节slave使用权重,合理配置资源。三、高可用行,大大减少了单点故障影响。

2、部署过程

a、haproxy.cfg

global
log 127.0.0.1 local3 notice
maxconn 20480
uid mysql
gid mysql
daemon
nbproc 1
pidfile /opt/phpdba/haproxy/logs/haproxy.pid

defaults
option  dontlognull
retries 3
option  redispatch
#maxconn 20480
contimeout      5000
clitimeout      50000
srvtimeout      50000

frontend db_baobao_read
bind :33080
default_backend cluster_db_baobao_read

frontend db_baobao_write
bind :33081
default_backend cluster_db_baobao_write

frontend db_user_write
bind :8066
default_backend cluster_db_user_write

frontend web_haproxy
bind :80
default_backend web_status

backend cluster_db_baobao_read
mode tcp
option tcpka
balance roundrobin
option httpchk
server db103_slave 192.168.0.103:3316 weight 1 check port 8890 inter 1s rise 5 fall 3
server db104_slave 192.168.0.104:3316 weight 1 check port 8890 inter 1s rise 5 fall 3

backend cluster_db_baobao_write
mode tcp
option tcpka
balance roundrobin
option httpchk
server db110_master 192.168.0.110:3308 weight 1 check port 8890 inter 1s rise 5 fall 3

backend cluster_db_user_write
mode tcp
option tcpka
balance roundrobin
option httpchk
server db46_master 192.168.0.46:3306 weight 1 check port 8890 inter 1s rise 5 fall 3

backend web_status
mode http
stats enable
#stats scope
stats hide-version
stats refresh 5s
stats uri /status
stats realm Haproxy\ statistic
stats auth chen-123:phpdba

 

b、keepalvied_db.conf

! Configuration File for keepalived
global_defs {
router_id LVS_db_ci104
}

vrrp_script chk_http_port {
script “/opt/phpdba/keepalived/etc/keepalived/check_haproxy.sh”
interval 2
weight -10
}

vrrp_instance VI_CiUser {
state BACKUP
interface em2
virtual_router_id 58
priority 99
advert_int 1

authentication {
auth_type PASS
auth_pass phpdba
}

virtual_ipaddress {
192.168.0.123 dev em2 label em2:0
}
}

vrrp_instance VI_CiBlog_QQ {
state MASTER
interface em2
virtual_router_id 68
priority 110
advert_int 1
authentication {
auth_type PASS
auth_pass phpdba
}

track_script {
chk_http_port
}

virtual_ipaddress {
192.168.0.124 dev em2 label em2:1
}
}

c、check_haproxy.sh

#!/bin/bash
A=`ps -C haproxy –no-header |wc -l`
if [ $A -eq 0 ];then
/opt/phpdba/haproxy/sbin/haproxy -f /opt/phpdba/haproxy/conf/haproxy.cfg
sleep 3
if [ `ps -C haproxy --no-header |wc -l` -eq 0 ];then
/etc/init.d/keepalived stop
fi
fi

d、/etc/xinetd.d/mysqlchk

service mysqlcheck
{
flags           = REUSE
socket_type     = stream
port            = 8890
wait            = no
user            = root
server          = /opt/phpdba/keepalived/etc/keepalived/mysqlchk.sh
log_on_failure  += USERID
disable         = no
only_from       = 192.168.0.0/24
}

e、/opt/phpdba/keepalived/etc/keepalived/mysqlchk.sh

#!/bin/bash

MYSQL_HOST=”192.168.0.104″
MYSQL_PORT=”3316″
MYSQL_USERNAME=”admin”
MYSQL_PASSWORD=”123456″
MYSQL_MAXTHREADNUM=”200″

ERROR_MSG=`/opt/phpdba/mysql/bin/mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USERNAME -p$MYSQL_PASSWORD -e “show databases;” 2>/dev/null`

if [ "$ERROR_MSG" != "" ]
then
/bin/echo -e “HTTP/1.1 200 OK\r\n”
/bin/echo -e “Content-Type: Content-Type: text/plain\r\n”
/bin/echo -e “\r\n”
/bin/echo -e “MySQL is running. \r\n”
/bin/echo -e “\r\n”
else
/bin/echo -e “HTTP/1.1 503 Service Unavailable\r\n”
/bin/echo -e “Content-Type: Content-Type: text/plain\r\n”
/bin/echo -e “\r\n”
/bin/echo -e “MySQL is down. \r\n”
/bin/echo -e “\r\n”
fi

f、/etc/services

mysqlcheck      8890/tcp
mysqlcheck      8890/udp

g、xinetd

yum install xinetd -f   &&  /etc/init.d/xinetd start

h、keepalvied

/etc/init.d/keepalived start

3、遇到的问题

a、haproxy 配置中check port 同一ip同一端口,只能监控一次。同一ip多个mysql实例监控,采用不同端口监控。

b、防火墙禁止了8890端口访问,导致check port失败。(iptables -I INPUT -p tcp –dport 8890 -j ACCEPT)

4、haproxy 状态页面

haproxy

 

01/22/14

数据库备份策略

数据库备份策略

1、备份目的

保障项目数据安全,防止服务器故障导致数据无法恢复的情况。

 

2、备份策略

综述:目前所有mysql实例已经实现了master、slave结构,我们备份一般在slave服务器进行。部分备份完成之后,会上传一份数据到存储机或者其他服务器进行异机及异地备份,另外备份保存一份。

 

一、备份方式

1、本机备份

该备份模式,适合于快速恢复数据。比如:误操作删除数据等

 

2、异机容灾备份

该备份模式,能比较好的规避单机故障问题。

 

3、异地容灾备份

该备份模式,规避大规模IDC故障(比如:火灾、地震、空调故障等)、数据安全问题

 

二、备份频率

1、主站www、bbs、ask、baobao、qq、blog等核心项目,进行每天完整备份。结合各项目数据库实例master与slave结构,以及当前slave服务器负载和带宽情况,采用crontab定时备份。

2、对于跟商业有关的项目,备份策略同核心项目。

3、非核心项目90%备份策略采用核心项目备份机制,其他采用每周备份策略。

4、对于近期大规模更新数据或者数据库结构变更的数据库实例,采用人员干预备份模式,即走备份申请流程。

三、备份准备

1、创建备份目录

/opt/phpdba/backup/database

2、脚本编写

详见第四项,备份脚本

3、加入计划任务 crontab

4、检查备份情况

5、添加每天检查列表

 

四、备份脚本

#!/bin/sh

LogFile=db$(date +%y%m%d).log

week=`date +%w`

cd /opt/phpdba/backup/database

for DBName in database

do

NewFile=db$DBName$(date +%y%m%d).tar.gz

OldLogFile=db$(date -d ’7 days ago’ +%y%m%d).log

if [ -f $OldLogFile ]

then

rm -f $OldLogFile >> $LogFile 2>&1

echo “[$OldLogFile]Delete Old log File Success!” >> $LogFile

else

echo “[$OldLogFile]No Old log File!” >> $LogFile

fi

 

case $week in

1)

date=`date -d ’56 days ago’ +%y%m%d`

OldFile=db$DBName$date.tar.gz

if [ -f $OldFile ]

then

rm -f $OldFile >> $LogFile 2>&1

echo “[$OldFile]Delete Old File Success!” >> $LogFile

else

echo “[$OldFile]No Old Backup File!” >> $LogFile

fi

;;

2|3|4|5|6|0)

date=`date -d ’7 days ago’ +%y%m%d`

OldFile=db$DBName$date.tar.gz

if [ -f $OldFile ]

then

rm -f $OldFile >> $LogFile 2>&1

echo “[$OldFile]Delete Old File Success!” >> $LogFile

else

echo “[$OldFile]No Old Backup File!” >> $LogFile

fi

;;

esac

if [ -f $NewFile ]

then

echo “[$NewFile]The Backup File is exists,Can’t Backup!” >> $LogFile

else

datestart=$(date +%s)

if [ -z $DBPasswd ]

then

mysqldump -u $DBUser –opt $DBName |gzip > $NewFile

else

echo “start backup” >> $LogFile

cd /opt/phpdba/data/database

#sh stop3310.sh

/opt/phpdba/mysql/bin/mysql -S /tmp/mysql.sock -p’123456′ -e “stop slave;flush tables”

tar zcf /opt/phpdba/backup/database/$NewFile var

#sh start3310.sh

/opt/phpdba/mysql/bin/mysql -S /tmp/mysq.sock -p’123456′ -e “start slave”

cd /opt/phpdba/backup/database

fi

echo “[$NewFile]Backup Success!” >> $LogFile

dateend=$(date +%s)

let time=$dateend-$datestart

echo “The Backup Time Is:[$time] ” >> $LogFile

filesize=`ls -l /opt/phpdba/backup/ database /$NewFile | awk ‘{print $5}’`

if [ $filesize -eq 20 ]

then

echo “$NewFile Backup File Size:[$filesize] is error”

echo “——————————————-”

fi

echo “The Backup File Size:[$filesize] ” >> $LogFile

fi

done

echo “——————————————-” >> $LogFile

 

五、后期实现binlog不设置过期时间,日志全备份和迁移。

 

3、备份检查

1、每个季度安排专人对备份数据库文件的可用性检查,主要包括文件导入是否可用。

人工将每份备份导入测试库,并进行可用性测试。后期尽量实现自动化或者半自动化操作。

2、每天程序自动检查,生成备份报告邮件给相关人员。

Python 远程登录每台备份服务器统计备份目录文件状态,生产邮件报告。

3、备份服务器空间监控

主要nagios监控 cacti监控

4、备份有效期

现在备份主要采用shell脚本进行备份,分数据库文件压缩打包备份和导出sql文件进行备份两种形式。

规则是:

1、周一检查是否存在56天前备份的文件,进行销毁

2、周二至周日检查7天钱备份的文件,进行销毁

3、90%的mysql实例的binlog保存90天;5%的mysql实例永久保存binlog;其他实例保存30天。

 

5、备份恢复

1、邮件描述恢复申请需求

2、运维找到相关备份文件

3、领导审核同意

4、实施恢复进程

5、恢复需求总结。比如:程序sql没加limit,导致整个表或者大部分数据更新相同内容。

 

6、备份申请单

 

数据备份申请表

应用系统名称

申请部门

申请日期

申请人

申请描述

备份内容

备份要求

(备份频率、数据保留时间、建议完成时间等)

申请部门负责人

签字

申请审批

备份策略

备份操作人员签字:

                      日期:

审批意见

  技术负责人签字:

                      日期:

申请部门负责人

确认签字

处理日期

 

 

 

 

12/12/13

php kill掉超过max_sleep_time秒select like 的sql线程

<?php
define('MAX_SLEEP_TIME', 30);

$hostname = "127.0.0.1";
$username = "monitor";
$password = "123456";

$connect = mysql_connect($hostname, $username, $password) 
              or die(mysql_error());
$result = mysql_query("SHOW FULL PROCESSLIST", $connect)
              or die(mysql_error());
echo "\n开始时间:".date("Y-m-d H:i:s")."\n";
while ($proc = mysql_fetch_assoc($result)) {
        if ($proc["Command"] == "Sleep" && \
                  $proc["Time"] > MAX_SLEEP_TIME) {
                if($proc['State'] == 'Locked' || \
                 preg_match("/(.*)select(.*)like(.*)/i",$proc['Info'])){               
                        var_dump($proc);
                        @mysql_query("KILL " . $proc["Id"], $connect);
                }
        }
}
mysql_close($connect);
echo "\n结束时间:".date("Y-m-d H:i:s")."\n";
?>
11/6/13

mysql int范围与最大值分析

1、mysql中int(11)中的11代表显示宽度
整数列的显示宽度,与mysql需要用多少个字符来显示该列数值,与该整数需要的存储空间的大小都没有关系。比如,不管设定了显示宽度是多少个字符,bigint都要占用8个字节。

a、int是整型,(11)是指显示字符的宽度,最大为255。
b、int(11)是记录行数的id,插入10条记录,那么它就显示00000000001 ~~~00000000010。
c、当字符的位数超过11,它也只显示11位。
d、如果没有加未满11位就前面加0的参数,就不会在前面加0。
e、如果没有给它指定显示宽度,MySQL会为它指定一个默认值。显示宽度只用于显示,并不能限制取值范围和占用空间。
f、INT(3)会占用4个字节的存储空间,并且允许的最大值也不会是999,而是INT整型所允许的最大值。

2、mysql有五种整型数据列类型,即TINYINT,SMALLINT,MEDIUMINT,INT和BIGINT。

a、区别是取值范围不同,存储空间不相同。
b、在整型数据列后加上UNSIGNED属性可以禁止负数,取值从0开始。
c、int范围:

Type Bytes Minimum Value Maximum Value
(Signed/Unsigned) (Signed/Unsigned)
TINYINT 1 -128 127
0 255

SMALLINT 2 -32768 32767
0 65535

MEDIUMINT 3 -8388608 8388607
0 16777215

INT 4 -2147483648 2147483647
0 4294967295

BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615

10/31/13

HAProxy+keepalived MySQL负载均衡

环境:vip 192.168.0.233

mysql1  192.168.0.227:3306

mysql2  192.168.0.230:3306

1. 监控脚本编写(mysqlrep_status.sh)

#!/bin/bash
#
# /opt/phpdba/keepalived/etc/keepalived/mysqlrep_status.sh
#
# This script checks if a mysql server is healthy running on localhost. It will return:
#
# "HTTP/1.x 200 OK\r" (if mysql is running smoothly)
#
# – OR –
#
# "HTTP/1.x 503 Internal Server Error\r" (else)
#

MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL_USERNAME="root"
MYSQL_PASSWORD="123456"
MYSQL_MAXTHREADNUM="30"

# We perform a simple query that should return a few results
/opt/phpdba/mysql/bin/mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USERNAME -p$MYSQL_PASSWORD -e "show full processlist;" >/tmp/processlist.txt
/opt/phpdba/mysql/bin/mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USERNAME -p$MYSQL_PASSWORD -e "show slave status\G;" >/tmp/rep.txt
iostat=`grep "Slave_IO_Running" /tmp/rep.txt  |awk '{print $2}'`
sqlstat=`grep "Slave_SQL_Running" /tmp/rep.txt |awk '{print $2}'`
result=$(cat /tmp/processlist.txt|wc -l)

#echo processlist num:$result iostat:$iostat and sqlstat:$sqlstat
# if slave_IO_Running and Slave_sql_Running ok,then return 200 code
if [ "$result" -lt "$MYSQL_MAXTHREADNUM" ] && [ "$iostat" = "Yes" ] && [ "$sqlstat" = "Yes" ];

then
        # mysql is fine, return http 200
        /bin/echo -e "HTTP/1.1 200 OK\r\n"

else
        # mysql is down, return http 503
        /bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"

fi

 

2、系统监听服务

a、修改/etc/service文件,

mysqlcheck      8890/tcp                # Mysql Check
mysqlcheck      8890/udp                # Mysql Check

b、在/etc/xinetd.d/目录下添加mysqlchk

service mysqlcheck
{
flags           = REUSE
socket_type     = stream
port            = 8890
wait            = no
user            = root
server          = /opt/phpdba/keepalived/etc/keepalived/mysqlrep_status.sh
log_on_failure  += USERID
disable         = no
}

或者 /etc/xinetd.conf 添加

mysqlcheck stream tcp nowait root /opt/phpdba/keepalived/etc/keepalived/mysqlrep_status.sh mysqlcheck

c、重启xinetd (如无此服务,yum -y install xinetd)

/etc/init.d/xinetd restart   or service xinetd restart

d、telnet 测试

telnet 192.168.0.233 8890
Trying 192.168.0.233…
Connected to 192.168.0.233.
Escape character is ‘^]’.
HTTP/1.1 200 OK

Connection closed by foreign host.

3、haproxy配置

global
        log 127.0.0.1 local3 notice
        maxconn 20480
        uid mysql
        gid mysql
        daemon
        nbproc 1
        pidfile /opt/phpdba/haproxy/logs/haproxy.pid

defaults
        log     global
        mode    http
        #option httplog
        option  dontlognull
        retries 3
        option  redispatch
        maxconn 20480
        contimeout      5000
        clitimeout      50000
        srvtimeout      50000

listen  MYSQL_SLAVE  0.0.0.0:3333
        #cookie SERVERID rewrite
        mode tcp
        maxconn 20480
        balance roundrobin
        option httpchk OPTIONS * HTTP/1.1\r\nHost:\ www
        server  mysql_227 192.168.0.227:3306  check port 8890 inter 5s rise 2 fall 3
        server  mysql_230 192.168.0.230:3306  check port 8890 inter 5s rise 2 fall 3
        srvtimeout      20000

listen  admin_status
        mode  http
        bind 0.0.0.0:8899
        option httplog
        log global
        stats enable
        stats refresh 10s
        stats hide-version
        stats realm Haproxy\ Statistics
        stats uri  /admin-status
        stats auth  admin:123456
        stats admin if TRUE

4、keepalived.conf

! Configuration File for keepalived

vrrp_script chk_http_port {
        script "/opt/phpdba/keepalived/etc/keepalived/check_haproxy.sh"
        interval 2
        weight 2

        global_defs {
                router_id LVS_db_phpdba
        }

        vrrp_instance VI_1 {
                state MASTER
                interface em1
                virtual_router_id 51
                priority 150
                advert_int 1
                authentication {
                        auth_type PASS
                        auth_pass phpdba
                }

                track_script {
                        chk_http_port
                }

                virtual_ipaddress {
                        192.168.0.233
                }
        }
}

5、haproxy进程监控(check_haproxy.sh)

#!/bin/bash
A=`ps -C haproxy --no-header |wc -l`
if [ $A -eq 0 ];then
        /opt/phpdba/haproxy/sbin/haproxy -f /opt/phpdba/haproxy/conf/haproxy.cfg
        sleep 3
        if [ `ps -C haproxy --no-header |wc -l` -eq 0 ];then
                /etc/init.d/keepalived stop
        fi
fi

6、haproxy 启动脚本

cat /etc/init.d/haproxyd

#! /bin/sh
set -e

PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/haproxy/sbin
PROGDIR=/opt/phpdba/haproxy
PROGNAME=haproxy
DAEMON=$PROGDIR/sbin/$PROGNAME
CONFIG=$PROGDIR/conf/$PROGNAME.conf
PIDFILE=$PROGDIR/run/$PROGNAME.pid
DESC="HAProxy daemon"
SCRIPTNAME=/etc/init.d/$PROGNAME

# Gracefully exit if the package has been removed.
test -x $DAEMON || exit 0

start()
{
        echo -n "Starting $DESC: $PROGNAME"
        $DAEMON -f $CONFIG
        echo "."
}

stop()
{
        echo -n "Stopping $DESC: $PROGNAME"
        haproxy_pid=cat $PIDFILE
        kill $haproxy_pid
        echo "."
}

restart()
{
        echo -n "Restarting $DESC: $PROGNAME"
        $DAEMON -f $CONFIG -p $PIDFILE -sf $(cat $PIDFILE)
        echo "."
}

case "$1" in
  start)
        start
        ;;
  stop)
        stop
        ;;
  restart)
        restart
        ;;
  *)
        echo "Usage: $SCRIPTNAME {start|stop|restart}" >&2
        exit 1
        ;;
esac

exit 0

 

10/29/13

MySQL中间件 Atlas-1.0.3 安装记录

1、wget https://github.com/Qihoo360/Atlas/archive/1.0.3.tar.gz -o Atlas-1.0.3.tar.gz

2、tar zxvf Atlas-1.0.3.tar.gz

3、cd Atlas-1.0.3

4、vim bootstrap.sh

#!/bin/sh
base=$(cd “$(dirname “$0″)”; pwd)
cd $base
PKG_CONFIG_PATH=/usr/local/lib/pkgconfig ./configure –with-mysql=/opt/phpdba/mysql/bin/mysql_config –prefix=/opt/phpdba/atlas_mysql_proxy CFLAGS=”-DHAVE_LUA_H -O2″ LDFLAGS=”-lm -ldl -lcrypto” LUA_CFLAGS=”-I/usr/local/include/” LUA_LIBS=”-L/usr/local/lib -llua”

5、./bootstrap.sh glib版本低,升级之

checking for GLIB… configure: error: Package requirements (glib-2.0 >= 2.32.0) were not met:

Requested ‘glib-2.0 >= 2.32.0′ but version of GLib is 2.22.5

Consider adjusting the PKG_CONFIG_PATH environment variable if you
installed software in a non-standard prefix.

6、升级glib

a、wget http://ftp.gnome.org/pub/gnome/sources/glib/2.36/glib-2.36.3.tar.xz

b、xz -d glib-2.36.3.tar.xz && tar xvf glib-2.36.3.tar

c、cd glib-2.36.3 &&  ./configure && make

保存信息:

/root/chen-123/glib-2.36.3/missing: line 81: automake-1.13: command not found
WARNING: ‘automake-1.13′ is missing on your system.
You should only need it if you modified ‘Makefile.am’ or
‘configure.ac’ or m4 files included by ‘configure.ac’.
The ‘automake’ program is part of the GNU Automake package:
<http://www.gnu.org/software/automake>
It also requires GNU Autoconf, GNU m4 and Perl in order to run:
<http://www.gnu.org/software/autoconf>
<http://www.gnu.org/software/m4/>
<http://www.perl.org/>
make[4]: *** [Makefile.in] 错误 127
make[4]: Leaving directory `/root/chen-123/glib-2.36.3/docs/reference/glib’
make[3]: *** [all-recursive] 错误 1
make[3]: Leaving directory `/root/chen-123/glib-2.36.3/docs/reference’
make[2]: *** [all-recursive] 错误 1
make[2]: Leaving directory `/root/chen-123/glib-2.36.3/docs’
make[1]: *** [all-recursive] 错误 1
make[1]: Leaving directory `/root/chen-123/glib-2.36.3′
make: *** [all] 错误 2

d、wget http://ftp.gnu.org/gnu/automake/automake-1.13.3.tar.gz  &&  tar zxvf  automake-1.13.3.tar.gz  && cd automake-1.13.3 &&  ./configure

错误信息:

checking whether autoconf is recent enough… no
configure: error: Autoconf 2.65 or better is required.

e、wget ftp://ftp.gnu.org/gnu/autoconf/autoconf-2.69.tar.gz && tar zxvf  autoconf-2.69.tar.gz && cd autoconf-2.69 && ./configure && make && make install

f、cd automake-1.13.3 && ./configure && make   &&make install

g、cd glib-2.36.3 &&  ./configure && make

报错:

configure.ac:66: error: version mismatch.  This is Automake 1.13.3,
configure.ac:66: but the definition used by this AM_INIT_AUTOMAKE
configure.ac:66: comes from Automake 1.13.2.  You should recreate
configure.ac:66: aclocal.m4 with aclocal and run automake again.
gtk-doc.make:32: warning: EXTRA_DIST multiply defined in condition TRUE …
docs/reference/glib/Makefile.am:85:   ‘gtk-doc.make’ included from here
Makefile.decl:8: … ‘EXTRA_DIST’ previously defined here
docs/reference/glib/Makefile.am:2:   ‘Makefile.decl’ included from here
WARNING: ‘automake-1.13′ is probably too old.

h、cd automake-1.13.3 &&  make uninstall  && wget http://ftp.gnu.org/gnu/automake/automake-1.13.2.tar.gz  &&  tar zxvf  automake-1.13.2.tar.gz  && cd automake-1.13.2 &&  ./configure && make && make install

i、cd glib-2.36.3 &&  ./configure && make && make install

7、cd Atlas-1.0.3  &&  ./bootstrap.sh && make && make install    安装完成

10/16/13

mysql-proxy 安装记录

1、tar zxvf mysql-proxy-0.8.3.tar.gz
2、cd mysql-proxy-0.8.3
3、./configure –prefix=/opt/phpdba/mysql-proxy
configure: error: mysql_config is not found, use $ ./configure –with-mysql=/path/to/mysql_config【mysql 安装没配置系统环境变量】
4、 ./configure –prefix=/opt/phpdba/mysql-proxy –with-mysql=/opt/phpdba/mysql/bin/mysql_config
checking for LUA… no
… checked for Lua via pkg-config: No package ‘lua’ found. retrying with lua5.1
checking for LUA… no
configure: error: checked for Lua via pkg-config: No package ‘lua5.1′ found. Make sure lua and its devel-package, which includes the lua5.1.pc (debian and friends) or lua.pc (all others) file, is installed
5、安装lua 5.1
tar zxvf lua-5.1.5.tar.gz
cd lua-5.1.5
make linux
在包含自 lua.h:16 的文件中,
从 lua.c:15:
luaconf.h:275:31: 错误:readline/readline.h:没有那个文件或目录
luaconf.h:276:30: 错误:readline/history.h:没有那个文件或目录
lua.c: 在函数‘pushline’中:
lua.c:182: 警告:隐式声明函数‘readline’
lua.c:182: 警告:赋值时将整数赋给指针,未作类型转换
lua.c: 在函数‘loadline’中:
lua.c:210: 警告:隐式声明函数‘add_history’

yum -y install readline
yum -y install readline-devel
make linux
make linux install
6、重新编译mysql-proxy
checking for LUA… no
… checked for Lua via pkg-config: No package ‘lua’ found. retrying with lua5.1
checking for LUA… no
configure: error: checked for Lua via pkg-config: No package ‘lua5.1′ found. Make sure lua and its devel-package, which includes the lua5.1.pc (debian and friends) or lua.pc (all others) file, is installed

export LUA_CFLAGS=”-I/usr/local/include”
export LUA_LIBS=”-L/usr/local/lib -llua -ldl”
7、make
/usr/bin/ld: /usr/local/lib/liblua.a(lapi.o): relocation R_X86_64_32 against `luaO_nilobject_’ can not be used when making a shared object; recompile with -fPIC
/usr/local/lib/liblua.a: could not read symbols: Bad value
8、重新编译lua

make clean
cd src/

修改Makefile 中CFLAGS 添加 -fPIC

9、cd mysql-proxy-0.8.3 ;make
./.libs/libmysql-chassis.so: undefined reference to `sqrt’
./.libs/libmysql-chassis.so: undefined reference to `floor’
./.libs/libmysql-chassis.so: undefined reference to `ceil’
./.libs/libmysql-chassis.so: undefined reference to `cosh’
./.libs/libmysql-chassis.so: undefined reference to `tan’
./.libs/libmysql-chassis.so: undefined reference to `tanh’
./.libs/libmysql-chassis.so: undefined reference to `asin’
./.libs/libmysql-chassis.so: undefined reference to `log’
./.libs/libmysql-chassis.so: undefined reference to `atan’
./.libs/libmysql-chassis.so: undefined reference to `sinh’
./.libs/libmysql-chassis.so: undefined reference to `fmod’
./.libs/libmysql-chassis.so: undefined reference to `acos’
./.libs/libmysql-chassis.so: undefined reference to `exp’
./.libs/libmysql-chassis.so: undefined reference to `sin’
./.libs/libmysql-chassis.so: undefined reference to `pow’
./.libs/libmysql-chassis.so: undefined reference to `atan2′
./.libs/libmysql-chassis.so: undefined reference to `cos’
./.libs/libmysql-chassis.so: undefined reference to `log10′

10、重新编译
./configure –prefix=/opt/phpdba/mysql-proxy –with-mysql=/opt/phpdba/mysql/bin/mysql_config CFLAGS=-g LIBS=-lm
make && make install
安装成功!

10/11/13

shell 监控mysql 锁表并报警

1、monitor_dbLocked

#!/bin/sh
mark=`date +%Y%m%d%H%M`
time_out=20
status=”ok”
check_status=”Locked”
send_mail=”off”
mysql_default_password=”xxxxxxxx”
host_name=`hostname`

declare -a mysqllist
declare -a mysql_exec_path
declare -a mysql_password
declare -a mysql_locked_port
declare -a mysql_show_port

function usage(){
cat << HELP

$0 — USAGE: sh $0 mysql_exec_path mysql_password mysql_socket

Example: sh $0 /opt/xxxx/mysql/bin/mysql xxxxxxx /tmp/socket
HELP
}
function script_arg_init(){
if [ "x$1" != "x0" ] ; then
mysql_exec_path=$1
else
mysql_exec_path=”/opt/xxxx/mysql/bin/mysql”
fi

if [ "x$2" != "x0" ];then
mysql_password=$2
else
mysql_password=”xxxxxx”
fi

if [ "x$3" != "x0" ];then
mysql_socket=$3
else
mysql_socket=”/tmy/mysql.sock”
fi

if [ "x$4" != "x" ];then
mysql_locked_port=$4
else
mysql_locked_port=3306
fi
}

for i in $mysqllist;do
for j in `echo $i|awk -F ‘:’ ‘{ k=1;while(k<=NF){print $k;k++}}’`;do

if [ -n "${j}" ] && ([ `echo $j|grep 'datadir='` ] || [ `echo $j|grep 'socket='` ] || [ `echo $j|grep 'port='` ]);then
#mysql_datastr=(`echo $j|awk -F “=” ‘{print $2}’`)
if [ `echo $j|grep 'datadir='` ];then
mysql_datadir=(`echo $j|awk -F “=” ‘{print $2}’`)
elif [ `echo $j|grep 'socket='` ];then
mysql_socket=(`echo $j|awk -F “=” ‘{print $2}’`)
elif [ `echo $j|grep 'port='` ];then
mysql_port=(`echo $j|awk -F “=” ‘{print $2}’`)
fi
fi
done
if [ "$mysql_port" == "3312" ];then
main 0 “xxxxxxxxx” $mysql_socket $mysql_port
else
main 0 $mysql_default_password $mysql_socket $mysql_port
fi
done

if [ "$send_mail" == "on" ] ;then
ip=`/bin/sh get_ip.sh|/usr/bin/head -n 1`
email_content=`cat log_mail/log_mail_$mark.txt`
sendEmail -s smtp.163.com -f xxxx@163.com -t xxxx@xxx.com -a log_mail/log_mail_$mark.txt -u “ALERT:$host_name[$ip] Locked Table $mysql_show_port ” -m “$email_content” -xu xxxx -xp xxxxxxx
echo “$mark send email”
fi

2、get_ip

#!/bin/sh
OS=`uname`
IO=””
case $OS in
Linux) IP=`/sbin/ifconfig  | grep ‘inet addr:’| grep -v -E ’127.0.0.1|192.168.’ | cut -d: -f2 | awk ‘{ print $1}’`;;
FreeBSD|OpenBSD) IP=`ifconfig  | grep -E ‘inet.[0-9]‘ | grep -v -E ’127.0.0.1|192.168.’ | awk ‘{ print $2}’` ;;
SunOS) IP=`ifconfig -a | grep inet | grep -v -E ’127.0.0.1|192.168.’ | awk ‘{ print $2} ‘` ;;
*) IP=”Unknown”;;
esac
echo “$IP”

3、sendEmail

sendEmail -f ADDRESS [options]

Required:
-f ADDRESS                from (sender) email address
* At least one recipient required via -t, -cc, or -bcc
* Message body required via -m, STDIN, or -o message-file=FILE

Common:
-t ADDRESS [ADDR ...]     to email address(es)
-u SUBJECT                message subject
-m MESSAGE                message body
-s SERVER[:PORT]          smtp mail relay, default is localhost:25

Optional:
-a   FILE [FILE ...]      file attachment(s)
-cc  ADDRESS [ADDR ...]   cc  email address(es)
-bcc ADDRESS [ADDR ...]   bcc email address(es)
-xu  USERNAME             username for SMTP authentication
-xp  PASSWORD             password for SMTP authentication
Paranormal:
-b BINDADDR[:PORT]        local host bind address
-l LOGFILE                log to the specified file
-v                        verbosity, use multiple times for greater effect
-q                        be quiet (i.e. no STDOUT output)
-o NAME=VALUE             advanced options, for details try: –help misc
-o message-content-type=<auto|text|html>
-o message-file=FILE         -o message-format=raw
-o message-header=HEADER     -o message-charset=CHARSET
-o reply-to=ADDRESS          -o timeout=SECONDS
-o username=USERNAME         -o password=PASSWORD
-o tls=<auto|yes|no>         -o fqdn=FQDN

Help:
–help                    the helpful overview you’re reading now
–help addressing         explain addressing and related options
–help message            explain message body input and related options
–help networking         explain -s, -b, etc
–help output             explain logging and other output options
–help misc               explain -o options, TLS, SMTP auth, and more

4、脚本下载

monitor_dblocked_v1.tar

09/29/13

内网内容后台断网断电处理预案分享

环境描述:

1、公司内网有个内容后台,需要每天更新、统计频道数据

2、一旦公司断网,后台无法正常访问,影响比较大

3、域名动态解析至网关

4、国庆期间,保障稳定、可用,采用一旦公司断电断网,直接采用服务器替换

实施步骤:

1、程序统计需要使用的库,在服务器上部署mysql slave,同步部分表。

2、由于公司ip动态获取,故采用域名+端口映射模式,进行slave同步。

3、replicate-wild-do-table=sphinx.publishdata_log  my.cnf配置文件,设置需要同步的表。

4、网关上设置端口应到到内网服务器mysql端口,

RED (ppp0) Any 9292 GREEN (eth0) 192.168.0.19 3306 TCP ACCEPT

5、设置master

CHANGE MASTER TO master_host=’blog.phpdba.com’,master_port=9292,master_user=’repli’,master_password=’123456′,MASTER_LOG_FILE=’xxxxx.00056′, MASTER_LOG_POS=xxxx;

6、开启同步

start slave;

show slave status\G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: sphinx_article.publishdata_log,sphinx_article.publishdata_draft,sphinx_article.citysite_stats,sphinx_article.coaistcoat

遇到的问题:

a、replicate-wild-do-table放置位置,“[mysqld]”。当放置在“[mysqld_safe]”或者“[mysql.server]”,则无法同步。

b、zend框架中,mysql pdo连接问题。端口需要单独设置,不能直接冒号连接。