其他
使用python脚本部署mariadb主从架构
原创地址:https://www.cnblogs.com/zzzynx/p/11125892.html
原创作者:四次元猪肉
环境准备
grant all on *.* to root@'%' identified by 'root' ;
master
import paramiko
ssh=paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(hostname='192.168.253.168',port=22,username='root',password='root')
a="sed -i -e '12aserver_id=1' -e '13alog_bin=mysql_bin' /etc/my.cnf.d/server.cnf"
b= 'systemctl restart mariadb'
c='''mysql -uroot -proot -e "grant replication slave on *.* to 'slave'@'%' identified by 'slave'"'''
d='''mysql -uroot -proot -e "show master status" '''
当然也可以将abcd四条命令写入一个列表,使用for语句循环出来放入下面命令执行。
stdin,stderr,stdout=ssh.exec_command(d) #将abcd分别执行
res = stdout.read().decode('utf-8') + stderr.read().decode('utf-8')
print(res)
运行显示结果:
sed -i -e '12aserver_id=1' -e '13alog_bin=mysql_bin' /etc/my.cnf.d/server.cnf
systemctl restart mariadb
mysql -uroot -p1 -e "grant replication slave on *.* to 'slave'@'%' identified by 'slave'"
mysql -uroot -p1 -e "show master status"
File Position Binlog_Do_DB Binlog_Ignore_DB
mysql_bin.000012 887350
mysql -uroot -p1 -e
此命令可以使用paramiko模块直接执行sql语句。e是edit的意思。slave
Slave_IO_Running: No
Slave_SQL_Running: No
或者一个YES一个connecting的话,这是因为主节点的master状态发生了变化或者两台主机的某一台防火墙没有关闭。master_ip ='192.168.253.168'
log_file='mysql_bin.000012'
pos=887350
import paramiko
ssh=paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(hostname='192.168.253.167',port=22,username='root',password='root')
a="sed -i '12aserver_id=2' /etc/my.cnf.d/server.cnf"
b='systemctl restart mariadb'
c='''mysql -uroot -proot -e "CHANGE MASTER TO MASTER_HOST='%s', MASTER_USER='slave', MASTER_PASSWORD='slave', MASTER_LOG_FILE='%s', MASTER_LOG_POS=%s" '''%(master_ip,log_file,pos)
d="mysql -uroot -proot -e 'start slave'"
stdin,stderr,stdout=ssh.exec_command(d) #将abcd分别执行
res = stdout.read().decode('utf-8')+ stderr.read().decode('utf-8')
print(res)
如果报错:The server is not configured as slave; fix in config file or with CHANGE MASTER TO
mysql -uroot -p1 -e "CHANGE MASTER TO MASTER_HOST='%s', MASTER_USER='slave', MASTER_PASSWORD='slave', MASTER_LOG_FILE='%s', MASTER_LOG_POS=%s" ''' % (master_ip,log_file,pos)
推荐阅读
【01】我硬生生地把C代码塞进了Python和Ruby!【02】自己「焊」键盘:使用Python编写【03】C++ 转 Python 这三年,我都经历了什么?【04】给大神鼓掌:400行Python代码实现文语处理助手!【05】使用 Python 来开发 STM32F411 !