分享一个mysql批量操作脚本

数据库性能测试可以使用专业的sysbench,这次到客户那里需要测试复制性能,于是自己用python写了一个并发脚本,可以实现多进程并发dml操作数据库,

import MySQLdb
import time
import multiprocessing as  mul 

def insert():
    db=MySQLdb.connect(host='192.168.56.25', user='root', passwd='root', port=3306)
    for i in range(0,10000):
        sql='insert into dsgtest2 select * from dsgtest2 limit 100'
        cur=db.cursor()
        cur.execute('use major')
        cur.execute(sql)
        db.commit()
        time.sleep(1)


def delete():
    db=MySQLdb.connect(host='192.168.56.25', user='root', passwd='root', port=3306)
    for i in range(0,10000):
        sql='delete from dsgtest2 limit 100'
        cur=db.cursor()
        cur.execute('use major')
        cur.execute(sql)
        db.commit()
        time.sleep(1)

def update():
    db=MySQLdb.connect(host='192.168.56.25', user='root', passwd='root', port=3306)
    for i in range(0,10000):
        sql='update dsgtest2 set id=1 limit 100'
        cur=db.cursor()
        cur.execute('use major')
        cur.execute(sql)
        db.commit()
        time.sleep(1)

if __name__ == "__main__":
    for i in range(25):
        j=i*3
        name='p'+str(j)
        name=mul.Process(target=insert)
        name.start()
        j += 1
        name='p'+str(j)
        name=mul.Process(target=insert)
        name.start()
        j += 1
        name='p'+str(j)
        name=mul.Process(target=insert)
        name.start()