Modify the MariaDB /etc/mysql/mariadb.conf.d/50-server.cnf config file.
bind=0.0.0.0
[mariadb]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.indexRestart MariaDB
systemctl restart mariadb.serviceLogin to MariaDB
mysqlCreate a replication user
CREATE USER 'replication'@'%' IDENTIFIED BY '********';Grant replication access to the new user
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';Flush privileges
FLUSH PRIVILEGES;Check the master server status
SHOW MASTER STATUS;Lock all tables and gather master info for later
flush tables with read lock;
show master status;Leave the latter session open! Open a second CLI session to the server.
Dump all databases to file
mysqldump -u root --all-databases --lock-all-tables --events > mastersql_dump.sqlNow back to the original session where the MySQL CLI is still running
Unlock all tables
unlock tables;Exit
exitTransfer the mastersql_dump.sql file to the slave server.
Import the SQL file
mysql -u root < mastersql_dump.sqlModify the MariaDB /etc/mysql/mariadb.conf.d/50-server.cnf config file.
bind=0.0.0.0
[mariadb]
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.indexRestart MariaDB
systemctl restart mariadb.serviceLogin to MariaDB
mysqlStop the slave threads as shown below:
STOP SLAVE;Setup the slave to replicate the master:
CHANGE MASTER TO MASTER_HOST='your-master-host-ip', MASTER_USER='replication', MASTER_PASSWORD='your-password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=313;Start the slave threads and show the slave status
START SLAVE;
SHOW SLAVE STATUS\GRESET SLAVE;Over time, I've had issues with slaves connecting to the master over a VPN connection, resulting in the master locking the slave out. Set the max_connection_errors to something greater than the default 10, then flush hosts.
[mysqld]
max_connect_errors=100Flush hosts
mariadb-admin flush-hostsIf a Slave server loses its sync with the Master's log, try this:
On the Master, show the current LOG_FILE and LOG_POS
SHOW MASTER STATUS;On the Slave(s), modify the LOG_FILE and LOG_POS to match the Master:
Stop the Slave first
SLAVE STOP;Modify
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.######', MASTER_LOG_POS=#####;Start Slave again
SLAVE START;If this doesn't fix it, you'll probably need to dump the Master and replace the Slave(s) DB and set the LOG_FILE and LOG_POS again - like during initial replication setup.