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-networkinglog_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
andSlave_SQL_Running
should beYes
:
Slave_IO_Running: Yes Slave_SQL_Running: Yes