tr命令问题


在修改DBA常用工具时,运行工具是报错:

xxx.xxx:/home/xxx>$tbsql sql
tr: 参数太多

找到那段代码:echo “sql”|tr [A-Z] [a-z]

切到yyy帐号下运行就正常

于是找到另外一台机器:正常

$echo "SQL" | tr [A-Z] [a-z]
sql

回来原来的机器xxx.xxx,在tr参数加上双引号,运行正常:

xxx.xxx:/home/xxx>$echo "SQL" | tr "[A-Z]" "[a-z]"
sql

@俊达说加单引号也行,试了一把也可以

xxx.xxx:/home/xxx>$echo "sql"|tr '[A-Z]' '[a-z]'
sql

python远程调用脚本(二)


Socket方式调用远程脚本这里只贴一下代码,代码只是简单实现这个功能,并没有考虑异常处理情况,因为这两个方案(rpyc与socket),需要再次讨论确定后,再完善。

Server端:

import os,sys
import socket
 
def nserver():
        sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        sock.bind(("xxxx.xxxx.xxxx.xxxx", 11111))
        sock.listen(5)
        while True:
                  try:
                     conn, addr = sock.accept()
                     ret = conn.recv(1024)
                     print ret
                     result = os.system(ret)
                     conn.send(str(result))
                  except KeyboardInterrupt:
                     print "exit"
                     sys.exit(0)
        sock.close()
if __name__ == '__main__':
        nserver()

client端:

#!/usr/bin/env python
import os,sys
import socket
def send_command(command):
        sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        sock.connect(("xxxx.xxxx.xxxx.xxxx", 11111))
        sock.send(command)
        result = sock.recv(1024)
        sock.close()
        return result
if __name__ == '__main__':
   cmd=sys.argv[1]
   ifsuccess = send_command(cmd)
   if (ifsuccess == "0"):
      print "command is successful!"

client端分别执行:

$python client.py "/home/luoxuan/iamshell.sh"
command is successful!
 
$python client.py "/home/luoxuan/iamperl.pl"
command is successful!
 
$python client.py "/home/luoxuan/iampython.py"
command is successful!

服务端打印结果:

/home/luoxuan/iamshell.sh
I am shell
/home/luoxuan/iamperl.pl
I am perl/home/luoxuan/iampython.py
I am python

注意:相同的代码,perl执行时,少了换行符

python远程调用脚本(一)


上周@维西提出需求希望在Dbfree实现远程调用脚本模块,所有模块去访问主机上的脚本,不需要登陆主机,只要调用远程调用脚本模块的方法就能实现。

python进行远程调用脚本方法:
一、登陆主机办法,python模块支持如pssh pexpect
二、远程以方法方式调用(不需要登陆主机),python模块rpyc,支持分布式
三、socket方式,个人感觉比较复杂,需要熟悉网络协议,起点比较高

pssh、pexpect在以前文章有介绍,这次介绍一下rpyc,就像rpyc文档介绍一下,rpyc支持远程调用、分布式计算,以较少代码量实现需复杂socket编程。rpyc的认证方式学习。
以代码方式介绍:

需求:分别执行server端的三个不同脚本,并返回执行结果给client端

server端:

$ls
iamperl.pl  iampython.py  iamshell.sh  server.py
 
#!/usr/bin/en python
import os
from rpyc import Service
from rpyc.utils.server import ThreadedServer
class remote_call_script( Service ):
      def exposed_iamshell( self ):
          return os.system("/home/luoxuan/iamshell.sh")
 
      def exposed_iamperl( self ):
          return os.system("/home/luoxuan/iamperl.pl")
 
      def exposed_iampython( self ):
          return os.system("/home/luoxuan/iampython.py")
 
s = ThreadedServer( remote_call_script, port=11111, auto_register=False )
s.start()

client端:

$ls client.py 
client.py
 
#!/usr/bin/env python
import rpyc
c = rpyc.connect('xxxx.xxxx.xxxx.xxxx', 11111)
if c.root.iamshell() == 0:
   print "shell ok"
 
if c.root.iamperl() == 0:
   print "perl ok"
 
if c.root.iampython() == 0:
   print "python ok"
 
c.close()

因文档中这样例子很多,不详细介绍,需注意两点:

1、server端定义方法需要被client调用,必须定义以exposed 开头的方法,不然会报错AttributeError: ‘remote_call_script’ object has no attribute ‘exposed_iamshell’
2、server端默认不设认证机制,如果需要认证有推荐两种方法: ThreadedServer的authenticator参数与SSL模块

server端:
$nohup python server.py &
[1] 3246
$ps -ef|grep server.py|grep -v grep
luoxuan 3246 4386 0 14:45 pts/7 00:00:00 python server.py

client端:
$python client.py
shell ok
perl ok
python ok

当然还需要考虑很多异常处理,如超时、验证失败等

下篇介绍Socket方式调用

Slave修复脚本


上周在帮同事处理一个数据订正时,遇到一个bug ,最终skip error,在slave新建表,再apply binlog解决

如果手工skip,可要执行好几百次,于是写了脚本,跳出错误binlog event

#!/usr/local/bin/perl
#author:luoxuan
#usage:fix_repl hostname
#version 0.1
 
use warnings;
use strict;
use DBIx::DWIW;
 
# @ARGV is null
print "fix_repl hostname";exit unless @ARGV;
 
#autoflush
$|=1;
 
our $conn;
our $host;
my $hostname = shift @ARGV;
 
#get connection from db
sub get_conn{
	$host = shift @_;
	$conn = DBIx::DWIW->Connect(
			DB =>"test",
			User =>"xxx",
			Pass =>"xxx",
			Host =>$host,
			Port =>3306) or die "Couldn't connect to database!";
}
 
#fix slave
sub fix_repl{
	print "checking $host ... \n";
	my $info = $conn->Hash("SHOW SLAVE STATUS") or die $@;
	my $fix_cmd;
	my $start_cmd;
        #slave not start
	if ($info->{Slave_SQL_Running} eq 'No')
	{
		$fix_cmd = "SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1";
		$start_cmd = "SLAVE START";
	}
	else
	{
		print "Slave is health!\n";
	}
 
        print "Fixing ... ";
	while(1){
		if ($info->{Slave_SQL_Running} eq 'No' ){
			$conn->Execute($fix_cmd);
			$conn->Execute($start_cmd);
		}
                print "waiting ..."; 
                sleep(1);      
	        $info = $conn->Hash("SHOW SLAVE STATUS") or die $@;
                if ($info->{Slave_SQL_Running} eq 'Yes'){
                        print "Done ... ";
                        last;
		}
	}
}
sub main{
    &get_conn($hostname);
    &fix_repl;
}
eval{&main}

注:这个脚本只在5.1.48版本测试通过

MySQL大表检查脚本


MySQL表大了,如果要对表进行DDL,那简直就是噩梦,如果表非常大,pt-online-schema也会不给力,那我们就对即将成为大表(2G以上)做好监控,写了简单脚本,来监控即将成为的大表,提前预警。

脚本如下:

#!/usr/bin/env python
#check_tabsize.py -- check big table
#Author:luoxuan
#Version:0.1
####################################
 
#-*- UTF-8 -*-
 
import sys
import getopt
import MySQLdb
 
def get_options():
    opts, args = getopt.getopt(sys.argv[1:], "n:s:u:p:h",["hostname","segsize","username","password","help"])
    global hostname,segsize,username,password
    for o, a in opts:
        if o in ("-n","--hostname"):
                hostname = a
        elif o in ("-s","segsize"):
                segsize = a
        elif o in ("-u","username"):
                username = a
        elif o in ("-p","password"):
                password = a
        elif o in ("-h","--help"):
               print "check_tabsize.py -n hostname -s size -u username -p password"
               sys.exit(0)
 
def print_bigtable(host,user,passwd,segsize):
    db_conn = MySQLdb.connect(host=host,user=user,passwd=passwd)
    db_cursor = db_conn.cursor()
    param = int(segsize) * 1000000000
    sql   = "SELECT table_schema,table_name,table_rows,data_length,index_length FROM information_schema.TABLES where (data_length + index_length) > %s ORDER  BY (data_length + index_length) DESC"
    db_cursor.execute(sql,param),
    print "-----------------------------------------------------------------------------------------------"
    print "    Schema    |        Table_name         |      rows    |     Data_size     |  Index_size    |"
    print "-----------------------------------------------------------------------------------------------"
    for row in db_cursor.fetchall():   
        print "%14s|"    % row[0].rjust(14),
        print "%26s|"    % row[1].rjust(26),
        print "%13s|"    % row[2],
        print "%18s|"    % row[3],
        print "%15s|"    % row[4]
    db_conn.close()
 
def main():
    get_options()
    print_bigtable(hostname,username,password,segsize)
 
if __name__=="__main__":
       try:
           main() 
       except:
           print "check_tabsize.py -n hostname -s size -u username -p password"
           sys.exit(0)

该脚本在dbconsole端执行,需要访问被检查mysql实例information_schema.TABLES访问权限

运行效果:

$./check_tabsize.py -n hostname -s table_size -u username -p password
-----------------------------------------------------------------------------------------------
    Schema    |        Table_name         |      rows    |     Data_size     |  Index_size    |
-----------------------------------------------------------------------------------------------
  xxx         |  xxx                      |        591652|         1252458496|       123764736|