前言：DBA日常运维除了通过自动化平台来进行日常操作以外，免不了还是会登陆到实例上进行操作，今天花了点时间写了个极其简易的数据库跳板机。

数据库

  1. #建库：
  2. create database jumpmysql 
  4. #建表：
  5. CREATE TABLE `mysql_server` (
  6.   `id` int(11) NOT NULL AUTO_INCREMENT,
  7.   `tag` varchar(50) DEFAULT NULL,
  8.   `ip` varchar(15) DEFAULT NULL,
  9.   `port` int(11) DEFAULT NULL,
  10.   `soketdir` varchar(50) DEFAULT NULL,
  11.   `datadir` varchar(50) DEFAULT NULL,
  12.   `mysql_user` varchar(20) DEFAULT NULL,
  13.   `mysql_pass` varchar(50) DEFAULT NULL,
  14.   `status` int(11) DEFAULT NULL,
  15.   `info` varchar(500) DEFAULT NULL,
  16.   PRIMARY KEY (`id`)
  17. ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
  18. 1 row in set (0.00 sec)
  20. #插入测试数据：
  21. insert into mysql_server values(1,'xxx主库','10.100.xx.xxx',3306,'/storage/mysql/mysql.sock','/storage/mysql/data/','xxx','123456',1,'xxx主库');
  22. insert into mysql_server values(2,'xxx备库','10.100.xx.xxx',3306,'/storage/mysql/mysql.sock','/storage/mysql/data/','xxx','123456',1,'xxx备库');

python脚本

  1. #!/usr/bin/env python
  2. #-*- coding:utf-8 -*-
  3. import os
  4. import MySQLdb as mdb
  5. from passde import encrypt, decrypt
  7. conn = mdb.connect(host='127.0.0.1', port=3306, user='test', passwd=decrypt('e51bc8b1c7f3810573d0fb9d7c2f8c01'), db='jumpmysql', charset='utf8')
  9. while True :
  10.     cursor = conn.cursor()
  11.     cursor.execute("SELECT * FROM mysql_server")
  12.     servers = cursor.fetchall()
  14.     for server in servers:
  15.           id = server[0]
  16.           ip = server[2]
  17.           port = server[3]
  18.           tag = server[1]
  19.              # 打印结果
  20.           print "\033[1;33;44m %d \033[0m: %s  %d %s" % \
  21.                  (id, ip, port, tag)
  23.     serverid = raw_input("请选择实例ID：")
  25.    #获取连接信息
  26.     SQL = "SELECT ip,port,mysql_user,mysql_pass FROM mysql_server where id= {SERVER_ID}".format(SERVER_ID=serverid)
  28.     cursor.execute(SQL)
  29.     data = cursor.fetchone()
  30.     host = data[0]
  31.     port = data[1]
  32.     user = data[2]
  33.     password =  decrypt(str(data[3]))
  35.     connectinfo = "mysql -h %s -P%d -u%s -p%s" % (host, port, user, password)
  36.     try:
  37.         os.system(connectinfo)
  38.     except:
  39.         print "信息错误，请重试！

加密解密脚本

  1. [root@localhost src]# cat passde.py
  2. # -*- coding:utf-8 -*-
  3. #!/usr/bin/env python
  5. from Crypto.Cipher import AES
  6. from binascii import b2a_hex,a2b_hex
  7. # from deveops.settings import SECRET_KEY
  8. SECRET_KEY = '1x$!#dwp2_6^tdgs1nv8pwgutbc#4m%#qaz!m!0h_f*%6fp+vt'
  9. KEY = SECRET_KEY
  10. KEY_LENGTH=16
  11. def encrypt(text):
  12.     # 这里密钥key 长度必须为16（AES-128）、24（AES-192）、或32（AES-256）Bytes 长度.目前AES-128足够用
  13.     cryptor = AES.new(KEY[0:KEY_LENGTH], AES.MODE_CBC, KEY[0:KEY_LENGTH])
  14.     length = KEY_LENGTH
  15.     count = len(text)
  16.     add = length - (count % length)
  17.     text = text + ('\0' * add)
  18.     ciphertext = cryptor.encrypt(text)
  19.     # 因为AES加密时候得到的字符串不一定是ascii字符集的，输出到终端或者保存时候可能存在问题
  20.     # 所以这里统一把加密后的字符串转化为16进制字符串
  21.     return b2a_hex(ciphertext)
  23. def decrypt(text):
  24.     cryptor = AES.new(KEY[0:16], AES.MODE_CBC, KEY[0:16])
  25.     plain_text = cryptor.decrypt(a2b_hex(text))
  26.     return plain_text.rstrip('\0')
  28. if __name__ == '__main__':
  29.     e = encrypt("123456")
  30.     d = decrypt('e51bc8b1c7f3810573d0fb9d7c2f8c01')
  31.     print e, d

测试效果
运行脚本
【python】一个极其简易的数据库跳板机 - 图2
效果还不错