Master-slave replication on MariaDB 10.4 using mysqldump and binary log

You just installed a MariaDB server and want to set up a slave that can be used for backups, reports and other read-only tasks.

Configuring the Master

Enable binary logging if it’s not already enabled.
Setup binlog format
Give the master a unique server_id.
Check that server is configured to allow remote connections
Create a dedicated slave user, and grant that user permission only to replicate (REPLICATION SLAVE permission).

Add the following into your /etc/mysql/my.cnf file and restart the database.

[mysqld]
bind-address = 0.0.0.0
skip-networking
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
server_id=1000
binlog_format=row

Create mysql user for replication:

CREATE USER 'replication_user'@'%' IDENTIFIED BY 'bigs3cret';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

Configuring the Slave

Give the slave a unique server_id.
Setup relay log

server_id = 1001
relay_log = /var/log/mysql/relay-bin
relay_log_index = /var/log/mysql/relay-bin.index
relay_log_info_file = /var/log/mysql/relay-bin.info
relay_log_purge = 1
relay_log_recovery = 1

Dump data and get binary log position

If you have an InnoDB/MyISAM combination of tables, you need to prevent any changes to the data while you dump the databases.

mysqldump --all-databases --flush-logs --hex-blob --master-data=2 > all_databases

Be aware that the above command acquiring a global read lock for the duration of the whole dump.

If you have only InnoDB tables, then you can dump the databases without needing a global lock:

mysqldump --single-transaction --all-databases --flush-logs --hex-blob --master-data=2 > all_databases

Binary log position is at the beginning of the file tanks to –master-data=2, which append a comment with the command to execute on the slave.

Start the Slave

Once the databases are imported, you are ready to start replication. Run the following command to create a connection to the master server:

CHANGE MASTER TO
  MASTER_HOST='master.domain.com',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000096',
  MASTER_LOG_POS=568,
  MASTER_CONNECT_RETRY=10;

If you are starting a slave against a fresh master that was configured for replication from the start, then you don’t have to specify MASTER_LOG_FILE and MASTER_LOG_POS.

Now start the slave with the START SLAVE command:

START SLAVE;

Check that the replication is working by executing the SHOW SLAVE STATUS command:

SHOW SLAVE STATUS \G

If replication is working correctly, both the values of Slave_IO_Running and Slave_SQL_Running should be Yes:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes