MariaDB replication slave

28/02/2020 - Ted

The complex, but really shouldn't be, steps to setup a replication slave.

Enable binary logging on master

The following need to be set on the master as a minimum:

# common server mysqld/mariadb options
[mysqld]
# unique 32 bit integer amongst the servers
server-id = 5
# meant to be a filename/path but it is just ignored as soon as log-basename is set
log-bin = ON
# STATEMENT or ROW; both are useless for generic databases, use mixed.
binlog_format = MIXED
 
# mariadb specific options
[mariadb]
log-basename = sql-master

Look out for additional settings of log-basename! It's a mariadb only setting so may be in its own section.

Optional additionals:

expire_logs_days = 10
max_binlog_size = 100M

This configuration above will allow for 1G of bin logs

NB: Server must be restarted for these settings to take effect (it's just easier as some require restart, while others can be changed on the fly).

Initial changes on the slave(s)

Set the server id:

server-id = <unique 32 bit integer>

Create replication user on master

GRANT REPLICATION SLAVE ON *.* TO repslaveuser@'10.99.0.0/255.255.255.0' IDENTIFIED BY 'super secret password1';

Dump master database

This should include all of the commands required to get the slave operating like it should:

mysqldump --master-data=1 --gtid --apply-slave-statements --all-databases > all_databases.sql

To set the slave position manually based off a comment in the dump file, use this instead:

mysqldump --master-data=2 --gtid --all-databases > all_databases.sql

This locks the tables and add commands to allow for it to be imported and work from there.

Initiate replication parameters

Use dud log file and position because there's no other way to get the host, port and most importantly the password in place without doing this:

CHANGE MASTER TO
  master_host='sql2.local',
  master_port=3306,
  master_user='repslaveuser',
  master_password='super secret password1',
  master_log_file='dummy',
  master_log_pos=1;

Import database to slave

Import the previously backed up data. This _should_ fix up the CHANGE MASTER settings and get things going, however it hasn't ever done this for me yet.

mysql < all_databases.sql

Initiate the slave replication

STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='sql-master-bin.000001', MASTER_LOG_POS=1034687;
START SLAVE;

Check status

> SHOW SLAVE STATUS \G
 
*************************** 1. row ***************************
[snip]
              Master_Log_File: sql-master-bin.000001
          Read_Master_Log_Pos: 81587468
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 80553323
        Relay_Master_Log_File: sql-master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[snip]

The Slave_IO_Running and Slave_SQL_Running being the important ones. Errors in either Last_IO_Error or Last_SQL_Error can avail you of any problems.

Selection of Errors

Lost connection to MySQL server at 'reading initial communication packet, system error: 0
Network no master is not available to the slave server.
Some possibilities:
  • Master has "skip-networking" set - it has no network available to connect to
  • Master has bind-address bound to address that is not available to slave eg. localhost
  • A firewall somewhere between the two
EHost 'ip' is not allowed to connect to this MySQL serverConnection
The IP or PTR of the slave is not in the grants table of the master - grant it access.
NB: This can be because the master has done a PTR lookup (or hosts) which then completely ignores the IP address setup.

Tags: