X-Files Wiki

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.

• server_id : A unique identifier given to the master - all replicas must also be given an identifier.
• log-basename : If unset the hostname will be used, which can cause trouble if the servername changes.
• log-bin : Location of the binlog files.
• binlog-format: Mixed also the default option you could change to “row” which requires more space.
• binlog-do-db : What database to replicate if not used all databases will be replicated.
• Usefull tip : Instad of “binlog-do-db” you can also use “binlog-ignore-db” which is the opposite.

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.

• If skip-networking=1 the server will limit connections to localhost only
• If bind-address=127.0.0.1 the server will only listen on localhost and replication will fail.

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:

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Relay_Log_Pos: xxxxxxxx
• Exec_Master_Log_Pos: xxxxxxxx

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.