Sysadmin

MariaDB replication

Configuring the Master

Add the following into my.cnf:

[server]
log-bin
server_id=1
log-basename=master
binlog-format=row
binlog-do-db=mydatabase1
binlog-do-db=mydatabase2

Restart the database:

# systemctl restart mariadb.service

Create the replication user:

# mysql -u root -p
MariaDB [(none)]>  CREATE USER 'replicator'@'%' IDENTIFIED BY 'bigs3cret';
MariaDB [(none)]>  GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
MariaDB [(none)]>  FLUSH PRIVILEGES;

Configure the firewall in order to allow the slave to connect to the master on port 3306. Replace 192.168.0.2/24 below by your slave's IP.

You can try beforehand to connect from the slave to the master mysql -u replicator -h 192.168.0.1 -p (192.168.0.1 being my master's IP). Connection should be blocked.

# firewall-cmd --permanent --new-zone=replication
# firewall-cmd --reload
# firewall-cmd --permanent --zone=replication --add-source=192.168.0.2/24
# firewall-cmd --permanent --zone=replication --add-service=mysql
# firewall-cmd --reload
# firewall-cmd --get-active-zones

Get the master's binary log position

Prevent any changes to the data while you view the binary log position. Keep this session running; exiting the session will release the lock.

# mysql -u root -p
MariaDB [(none)]>  FLUSH TABLES WITH READ LOCK;
MariaDB [(none)]>  SHOW MASTER STATUS;

Save the values of MASTER_LOG_FILE and MASTER_LOG_POS.

Export the database from the master

In another session, export the master database:

# mysqldump -u root -p mydatabase1 > mydatabase1.sql

Once the data has been exported to a local SQL file (mydatabase1.sql in this example), you can release the lock on the master:

MariaDB [(none)]>  UNLOCK TABLES;
MariaDB [(none)]>  exit

Import the database to the slave

Upload the exported SQL file to the server hosting the slave db:

# scp mydatabase1.sql root@192.168.0.2:/root/

On the slave, import the database:

# mysql -u root -p mydatabase1 < /root/mydatabase1.sql

Configuring the Slave

Add the following into my.cnf:

[server]
server_id=2
replicate-do-db=mydatabase1
replicate-do-db=mydatabase2

Restart the database:

# systemctl restart mariadb.service

Add the master settings on the slave (replace MASTER_LOG_FILE and MASTER_LOG_POS by your parameters):

# mysql -u root -p
MariaDB [(none)]>  STOP SLAVE;
MariaDB [(none)]>  CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_USER='replicator', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=460, MASTER_CONNECT_RETRY=10, MASTER_USE_GTID=slave_pos;
MariaDB [(none)]>  START SLAVE;
MariaDB [(none)]>  SHOW SLAVE STATUS\G;
MariaDB [(none)]>  exit

To check if the replication works correctly, both of these values should be Yes:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Comments

Comments including links will not be approved.