Master-Master Replication.

Generate private/public pair of keys to allow a 'ssh' connection without asking for a password for both servers and copy public keys to each server

ssh-keygen -t rsa -b 2048

ssh-copy-id -i /root/.ssh/id_rsa.pub root@remoteserverIP

test ssh login ssh 'root@ remoteserverIP '

Trasnfer your website files on the Secondary server

rsync -avzhe ssh /var/www/vhosts/yourdomain.com/httpdocs/

root@ remoteserverIP:/var/www/vhosts/yourdomain.com/httpdocs/

We need to adjust the database configuration for Primary database and Secondary to enable what’s called binary logging. The database’s binary log can be thought of as a log of all the SQL statements it has received.

In Primary setup, the MySQL configuration is included from files in another directory. Open MySQL configuration file /etc/my.cnf to confirm that the line with “/etc/my.cnf.d” is present. (This file does nothing but includes the files from other directories.).

Make sure that the following line is present in this file:

!includedir /etc/my.cnf.d

Now we will edit the file “/etc/my.cnf.d/server.cnf”.

Here’s what we want in server.cnf for database A:

server_id = 1

log_bin = /var/log/mariadb/mysql-bin.log

log_bin_index = /var/log/ mariadb/mysql-bin.log.index

relay_log = /var/log/ mariadb/mysql-relay-bin

relay_log_index = /var/log/ mariadb/mysql-relay-bin.index

expire_logs_days = 10

max_binlog_size = 100M

log_slave_updates = 1

auto-increment-increment = 2

auto-increment-offset = 1

In Secondary Server we need to adjust the database configuration (/etc/my.cnf)

And here’s what we want for database B: (the records below must be included on the section [mysqld] and NOT in [mysqld_safe]. Log-bin needs to be in the [mysql])

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.index

expire_logs_days = 10

max_binlog_size = 100M

log_slave_updates = 1

auto-increment-increment = 2

auto-increment-offset = 2

Note that they each have a unique server_id. That’s important because when these replication logs are flying all over the place, MySQL needs to know from which database the statement originated (to avoid duplication and things like that).

The auto-increment-offset and auto-increment-increment are also very important. If we’re going to allow writes to each database, we don’t want collisions for auto-incrementing ID fields. To avoid this, we set each server to increment by 2 instead of 1, and set database A to only use odd IDs and database B to use even ones.

Changes to my.cnf require a database restart to take effect.

The first thing that we want to do is enable MySQL daemon to listen on the private or public IP address (NOT localhost). By default, the daemon binds itself with the loopback IP address. So, we change the line (Primary Server):

bind-address = 127.0.0.1 (or comment the live)

To look like:

bind-address = remoteserverIP

We make the same changes on the Secondary Server MySQL server.

bind-address = primaryserverIP

Now that our MySQL servers are set to listen on the public IP addresses, it’s time to enable replication in MySQL configuration. We need to uncomment the line binlog_do_db and mention the database that we are going to replicate.

binlog_do_db replicate only the database that we choose. binlog_ignore_db replicate all the others databases and ignore the selected. After changes, it will look like this:

binlog_do_db = dbname_

#binlog_ignore_db = include_database_name

The configuration files are changed on both servers, we will restart the MySQL service:

Primary Server# systemctl restart mariadb.service

Secondary Server# service mysql restart

We can check that our configuration changes are loaded and server is listening on the correct IP address:

# netstat -ntpl | grep mysql

For MySQL replication, we need to create a new user for replication that will have replication permission on all the databases. Let’s create for both servers, users with the below MySQL queries:

mysql -uadmin -p`cat /etc/psa/.psa.shadow`

Provide your password that you chose while MySQL server installation. It will drop you at the MySQL prompt. Enter the following commands at this prompt:

create user 'username'@'%' identified by 'password';

grant replication slave on *.* to 'username'@'%';

When we slave up database we need to tell it where Primary database’s binary log is to start working from. To do this we will lock the database, dump the data, record the position in the binary log, and release the lock. The lock is necessary so that we’re assured binary log position doesn’t change while we’re exporting the data. Without this lock, pesky users could be inserting and  updating rows during our data dump! 

Ok, here we go. Login to mysql on Primary database and issue these commands to lock the database:

Primary mysql> FLUSH TABLES WITH READ LOCK;

Primary mysql> SHOW MASTER STATUS;

First of all, we will check the Master status of Primary server. Run the following command at MySQL prompt to check the master status:

Record the output of the last statement. It’s the binary log position! Here, we need 2 pieces of information: The File (mysql-bin.000004) and the Position (500) for setting up this server as master of Secondary (along with the username and password we set in the last step). You’re going to use the File and Position column values in a few. \

Now connect to Secondary server and type the following sql commands:

stop slave;

Run the following command on Secondary server to tell it that Primary Server is its master:

CHANGE MASTER TO MASTER_HOST = ' primaryserverIP', MASTER_USER = 'databasename', MASTER_PASSWORD =

'databasepassword', MASTER_LOG_FILE = 'mysql-bin.000004', MASTER_LOG_POS = 500;

slave start;

The output from Server B should look similar to the following:

Query OK, 0 rows affected (0.01 sec)

Now in another terminal on Primary server database’s we dump the data:

As soon as that dump is completed, release the read lock:

HE mysql> UNLOCK TABLES;

Now we have the export of the database and the exact position in the binary log to which it

corresponds.

Import dump sql file to the Secondary server database:

Now connect to Secondary server database and type the following sql commands:

stop slave;

Now you need to do the same thing from the other side. That is, replicate all the commands received  by Secondary server to Primary server, configuring the latter as a slave of the former. Connect OVH and type the following sql command: 

OVH mysql> show master status;

Run the following command on Primary Server to tell it that Secondary Server is its master:

CHANGE MASTER TO MASTER_HOST = 'Secondary_IP', MASTER_USER = DB_User', MASTER_PASSWORD =

'Password', MASTER_LOG_FILE = 'mysql-bin.000002', MASTER_LOG_POS = 361;

START SLAVE;

Your MySQL Servers are now in Master-Master replication mode.

Debug Master Master Replication

MariaDB [(none)]> show slave status \G;

Reset slave and master replication servers and continue from step 7

mysql> stop slave;

mysql> reset slave;

mysql> reset master;