Run the following using sudo or root.
Find the config file MariaDB is using, this would normally be /etc/mysql/mariadb.conf.d/50-server.cnf
ps -waux |grep "mariadb"
You will get an output telling you what configuration is used it looks something like this.
root 47642 0.0 0.0 7588 2524 pts/0 S+ 11:49 0:00 less /etc/mysql/mariadb.conf.d/50-server.cnf
We need to add a few things to this file.
vi /etc/mysql/mariadb.conf.d/50-server.cnf
Find this line.
bind-address = 0.0.0.0
And add below that the following:
# MariaDB Primary Replication Fields server_id=1 log-basename=master1 log-bin=/var/log/mysql/mariadb-bin binlog-format=mixed binlog-do-db=databasename # MariaDB Primary Replication Fields
What the above means and what to look out for.
Next we need to set up a replication user.
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%' IDENTIFIED BY 'replication_password'; FLUSH PRIVILEGES;
Note: What to look out for in your configuration.
Pro Tip: If you need to replicate more than 1 database you would set it up like this.
# MariaDB Primary Replication Fields server_id=1 log-basename=master1 log-bin=/var/log/mysql/mariadb-bin binlog-format=mixed binlog-do-db=databasename1 binlog-do-db=databasename2 # MariaDB Primary Replication Fields
Restart the primary server for the changes to take effect.
As with the Primary we need to assign a unique identifier among other things. This guide makes the assumption that the configuration file on the Replica resides in the same location as on the Primary.
vi /etc/mysql/mariadb.conf.d/50-server.cnf
Find this line.
bind-address = 0.0.0.0
And add below that the following:
# MariaDB Replica Fields server_id=2 replicate-do-db=databasename # MariaDB Replica Replica Fields
Important: Be sure to check the server_id and make sure it's unique.
Pro Tip: If you need to replicate more than 1 database you would set it up like this notice we changed the server_id on the second replica.
# MariaDB Replica Fields server_id=2 replicate-do-db=databasename1 replicate-do-db=databasename2 # MariaDB Replica Replica Fields
Restart the replica for the changes to take effect.
Optional On The Replica: Create a replication user on that server as well so that if we ever need to promote the Replica to Primary the user already exists and we then can skip this step.
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%' IDENTIFIED BY 'replication_password'; FLUSH PRIVILEGES;
Now we need to determine the Binlog Co-ordinates. While we get the position we will prevent any changes to be made on the primary server.
On The Primary Server:
FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
You will get an output that looks like below, please note the values shown here are examples only.
+--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | master1-bin.000096 | 568 | | | +--------------------+----------+--------------+------------------+
Take a note of the “Master Bin File Value” as well as the “Position Value” as we will use it later.
While we have the “Read Lock In Place” make a backup of the database(s)
mysqldump -u root -p databasename > databasename-backup-ddmmyy.sql
Once the backup is done you can unlock the tables on the Primary.
On The Primary Server:
UNLOCK TABLES;
Transfer the backup to the Replica and import it.
On The Replica Server:
mysql -u root -p databasename < databasename-backup-ddmmyy.sql
Once the backup has been imported we are ready to start replicating.
On The Replica Server:
STOP SLAVE; CHANGE MASTER TO MASTER_HOST='Primary_Ip_Address', MASTER_USER='replication_user', MASTER_PASSWORD='replication_password', MASTER_PORT=3306, MASTER_LOG_FILE='master1-bin.000096', MASTER_LOG_POS=568, MASTER_CONNECT_RETRY=10; START SLAVE;
Note: “Primary_Ip_Address” does not have to be an IP address but could be a domain as well.
Instead of Filename/Offset replication you could use “Global Transagtion IDs” starting from MariaDB 10.0 this is however a MariaDB only feature.
On The Replica Server:
SHOW SLAVE STATUS\G;
What to look for here:
Compare the position values with those on the Primary server.
On The Primary:
SHOW MASTER STATUS\G;
Notes on MySQL: The above should also work on MySQL except for the option “log-basename” which MySQL does not support.