python批量通过mysql修改用户密码
1.先创建修改密码的表passmod.svrlist，并插入两条数据1
2
3
4
5
6
7
svr_host #远程连接的主机host
svr_port #通过此端口连接到远程主机
svr_user #远程连接的主机用户
svr_passwd_old#远程连接的主机密码
svr_passwd_new #远程连接的主机需要设置的新密码
mod_time #默认为空，修改成功后，返回修改的时间
mod_status enum('0','1')#默认为‘0’，修改成功变成‘1’
并授权insert，select,update操作给passmod用户'passmod'@'%'
1
vi tb_create.py #这里用一条sql语句i，甚至用shell编写更简单。主要为了学习MySQLdb模块
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
#!/usr/bin/python
import MySQLdb
try:
conn=MySQLdb.connect(host='192.168.163.138',user='root',passwd='zhang1992',port=3306)
cur=conn.cursor()
print 'connect begin...'
except:
print "connect mysql error!"
exit(1)
def create_tb(cur,db_name,tb_name):
cur.execute("drop database if exists %s"%db_name)
cur.execute("create database %s"%db_name)
cur.execute("use %s"%db_name)
cur.execute("create table %s(svr_host varchar(50) not null,svr_port varchar(20) not null,svr_user varchar(50) not null,svr_passwd_old varchar(50),svr_passwd_new varchar(50) not null,mod_time timestamp null,mod_status enum('0','1') default '0')"%tb_name)
return 0
def user_grant(cur,db_name,tb_name):
sql="grant select,update,insert on %s.%s to '%s'@'%%' identified by 'zhang1992'"%(db_name,tb_name,db_name)
create_tb(cur,db_name,tb_name)
cur.execute(sql)
cur.execute('flush privileges')
def main():
db_name='passmod'
tb_name='svrlist'
user_grant(cur,db_name,tb_name)
cur.execute("insert into %s.%s(svr_host,svr_port,svr_user,svr_passwd_new,svr_passwd_old) value ('192.168.163.138','22','root','zhang1992','zhang1992')"%(db_name,tb_name))
cur.execute("insert into %s.%s(svr_host,svr_port,svr_user,svr_passwd_new,svr_passwd_old) value ('192.168.163.226','22','root','zhang1992','zhang1992')"%(db_name,tb_name))
cur.close()
conn.commit()
conn.close()
print 'connect end...'
return 0
if __name__=='__main__':
main()
执行tb_create.py
1
python tb_create.py
查询数据库
2.编写mod_passwd.py脚本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
#!/usr/bin/python
import os,MySQLdb,paramiko,time
try:
#通过passmod用户去连接mysql，上一脚本已经授权
conn=MySQLdb.connect(host='192.168.163.138',user='passmod',passwd='zhang1992',db='passmod',port=3306)
except:
print "conn mysql error..."
exit(1)
cur=conn.cursor()
def connect_mod(svrlist):
#通过paramiko模块去远程登录主机，并修改密码
cli=paramiko.SSHClient()
cli.set_missing_host_key_policy(paramiko.AutoAddPolicy())
try:
cli.connect(hostname=svrlist,port=svrlist,username=svrlist,password=svrlist,timeout=5)
except:
print "%s connect error..."%svrlist
return 0
stdin,stdout,stderr=cli.exec_command("/bin/echo %s|/usr/bin/passwd --stdin %s" %(svrlist,svrlist))
if len(str(stderr.read()))!=0:
return 0
cli.close()
return 1
def main():
cur.execute("select * from svrlist")
for row incur.fetchall():
svr_host=str(row)
svr_port=int(row)
svr_user=str(row)
svr_passwd_old=str(row)
svr_passwd_new=str(row)
#只有mod_status为0，才会去修改密码
#修改密码后，update行，修改mod_status=1,mod_time为当前修改时间
if int(row)==0:
svrlist=(svr_host,svr_port,svr_user,svr_passwd_old,svr_passwd_new)
modpass=connect_mod(svrlist)
#修改成功，则modpass为1
if modpass==1:
print svrlist,":",svrlist,"Successful"
mod_time=time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(time.time()))
cur.execute("update svrlist set mod_status='1',mod_time='%s' where svr_host='%s' and svr_user='%s'"%(mod_time,svr_host,svr_user))
else:
print svrlist,":",svrlist,"Failed"
cur.close()
conn.commit()
conn.close()
return 0
if __name__=='__main__':
main()
执行mod_passwd.py
查询数据库
页:
[1]