Automate MySQL Master‑Slave Setup with Python and Paramiko

Environment Preparation

Deploy a master service and a slave service, disable firewalls on both nodes, and grant remote root access:

grant all on *.* to root@'%' identified by 'root';

Master

Use Python's paramiko to SSH into the master host, edit /etc/my.cnf.d/server.cnf to set server_id=1 and log_bin=mysql_bin, restart MariaDB, and configure replication privileges.

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"'''
stdin, stderr, stdout = ssh.exec_command(d)
res = stdout.read().decode('utf-8') + stderr.read().decode('utf-8')
print(res)

Verify the configuration on Linux with mysql -uroot -p1 -e or by executing the SQL directly via Paramiko.

Slave

On the slave node, adjust server_id=2, restart MariaDB, and run CHANGE MASTER TO with the master’s IP, log file and position, then start the slave.

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)
res = stdout.read().decode('utf-8') + stderr.read().decode('utf-8')
print(res)

If Slave_IO_Running or Slave_SQL_Running shows “No”, check that the master’s configuration hasn’t changed and that firewalls are disabled.

If you encounter the error “The server is not configured as slave; fix in config file or with CHANGE MASTER TO”, ensure server_id=2 is set and the CHANGE MASTER command executed correctly.

Test replication by creating a database on the master and confirming it appears on the slave.