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.

image::/images/master-slaves.png[MySQL master with multiple slaves,title="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