MySQL database replication

This week I was asked to setup a system with a single MySQL master server and a couple of slave servers. I have done that before, so there is no problem. The problem is that I do not do that on a daily basis, so I need to check the commands every time I set it up. So, as a note to myself I describe how to setup a single MySQL master with multiple slaves.

MySQL master with multiple slaves

The MySQL master server

Start by adding replication settings to the /etc/my.cnf file for the powerdns database

[mysqld]
port=3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1

ssl
server-id               = 1
log-bin                 = mysql-bin
log-bin-index           = mysql-bin.index
expire-logs-days        = 10
max-binlog-size         = 100M
binlog-do-db            = powerdns

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

and restart the MySQL server.

Now grant the slaves access to the replication functions. In MySQL type:

create user slave_user;
create user 'slave_user'@'*';
grant replication slave on *.* to slave_user identified by 'passwd';
flush privileges;

Now take a look at the master status:

show master status \G

this should look something like

*************************** 1. row ***************************
	    File: mysql-bin.000004
	Position: 14527
    Binlog_Do_DB: powerdns
Binlog_Ignore_DB:
1 row in set (0.00 sec)

and remember the File and Position values.

The MySQL slave server

Start by adding replication settings to the /etc/my.cnf file for the powerdns database

[mysqld]
port=3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1

ssl
server-id=2
master-connect-retry=60
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
replicate-do-db=powerdns

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

and restart the server. Keep a good eye on the server-id. These should be unique for all servers.

Now request replication access from the master. In MySQL type:

change master to
	master_host='192.168.63.1',
	master_user='slave_user',
	master_password='passwd',
	master_log_file='mysql-bin.000004',
	master_log_pos=14527;
start slave;

When everything works out, the command show slave status \G shoud show something like

*************************** 1. row ***************************
	     Slave_IO_State: Waiting for master to send event
		Master_Host: 192.168.63.1
		Master_User: slave_user
		Master_Port: 3306
	      Connect_Retry: 60
	    Master_Log_File: mysql-bin.000004
	Read_Master_Log_Pos: 14527
	     Relay_Log_File: slave-relay-bin.000032
	      Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000004
	   Slave_IO_Running: Yes
	  Slave_SQL_Running: Yes
	    Replicate_Do_DB: powerdns
	Replicate_Ignore_DB:
	 Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
		 Last_Errno: 0
		 Last_Error:
	       Skip_Counter: 0
	Exec_Master_Log_Pos: 14527
	    Relay_Log_Space: 235
	    Until_Condition: None
	     Until_Log_File:
	      Until_Log_Pos: 0
	 Master_SSL_Allowed: No
	 Master_SSL_CA_File:
	 Master_SSL_CA_Path:
	    Master_SSL_Cert:
	  Master_SSL_Cipher:
	     Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

This can now be replicated for all slaves, but make sure you set the server_id to something unique and that the master_log_file and master_log_pos are set correctly for every client.

mysql 

See also