Mysql Master Master Replication & LoadBalancing using HaProxy

Requirements
##############

Two server running Ubuntu 16.04.
Static IP address 10.0.1.12 (dn1) configured on first master server.
Static IP address 10.0.1.14 (dn2) configured on second master server.
A non-root user with sudo privileges configure on both server.

First, you will need to install mysql server and client on dn1 server. You can install it with the following command:

# sudo apt-get install mysql-server mysql-client

Next, you will need to make some changes in my.cnf file of First server as below:
----------------------------------------------------------------------------------

vi /etc/mysql/mysql.conf.d/mysqld.cnf

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size   = 100M

# service mysql restart

Next, you will need to make some changes in my.cnf file of dn2 server as below:
----------------------------------------------------------------------------------

vi /etc/mysql/mysql.conf.d/mysqld.cnf

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size   = 100M

# service mysql restart



On DN1 Server:
################

mysql -u root -p

GRANT REPLICATION SLAVE ON *.* TO 'replication1'@'10.0.1.14' IDENTIFIED BY 'password';

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      912 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Here, we need 2 pieces of information: the File (mysql-bin.000001) and the Position (605) of DN2 for setting DN2 as master of this server. (along with the username and password we set in the last step).

Run the following command on the server to tell it that dn2 is its master:

mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST = 'dn2.hostname.com', MASTER_USER = 'replication2', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 605;
mysql> start slave;



On DN2 Server:
#################


mysql -u root -p

GRANT REPLICATION SLAVE ON *.* TO 'replication2'@'10.0.1.12' IDENTIFIED BY 'password';

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      605 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Here, we need 2 pieces of information: the File (mysql-bin.000001) and the Position (912) of DN1 for setting DN1 as master of this server (along with the username and password we set in the last step).

Run the following command on the server to tell it that dn1 is its master:

mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST = 'dn1.hostname.com', MASTER_USER = 'replication1', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 912;
mysql> start slave;

Testing:
#######

mysql> SHOW SLAVE STATUS \G;

Now if you create a DB on DN1 it will be created on the DN2 also.

How to Exclude a query from replication
#################################

SET sql_log_bin = 0;
drop index index_1 on recipes;
SET sql_log_bin = 1;

The above drop command will be only executed in the server which it is executed, and not replicated to its master.

https://dev.mysql.com/doc/refman/5.7/en/set-sql-log-bin.html


Errors & Fixes:
##############

[ERROR] Slave SQL for channel 'group_replication_applier': Slave failed to initialize relay log info structure from the repository, Error_code: 1872

Fix :
------

USE mysql;
TRUNCATE TABLE slave_relay_log_info;

Than restart the mysql:

service mysql restart

Error:
-------
MAR 19 09:56:08 http2 mysqld[1380]: 080529 9:56:08 [ERROR] Slave: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist' on query. Default database: 'mydb'. Query: 'UPDATE thr

Fix:
----

mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

mysql> START SLAVE;


HA Proxy load balancing MYSql connections:
##########################################

Droplet 1 - Load Balancer
Hostname: haproxy.hostname.com
OS: Ubuntu Private IP: 10.0.1.9

Droplet 2 - Dn1
Hostname: mysqldn1 
OS: Debian 7 Private IP: 10.0.1.12

Droplet 2 - Dn2 
Hostname: mysqldn1 
OS: Debian 7 Private IP: 10.0.1.14

First Login to Dn1 & Dn2 and give the below permissions:
------------------------------------------------------
As there is replication done you only have to do this in any one of the server.

Make sure you have an fqdn as hostname or set the entries in the /etc/hosts file of Dn1 and Dn2

mysql> use mysql
mysql > INSERT INTO user (Host,User,ssl_cipher,x509_issuer,x509_subject) VALUES ('haproxy.hostname.com', 'haproxy_check','','','');FLUSH PRIVILEGES;
mysql > GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'haproxy.hostname.com' IDENTIFIED BY 'yourstrongpassword' WITH GRANT OPTION; FLUSH PRIVILEGES";



In haproxy server (10.0.1.9):
--------------------------

[root@haproxy-lb ~]# vi /etc/haproxy/haproxy.cfg

listen mysql-cluster
    bind *:3306
    mode tcp
    option mysql-check user haproxy_check
    balance roundrobin
    server mysqldn1 10.0.1.8:3306 check
    server mysqldn2 10.0.1.4:3306 check
    
[root@haproxy-lb ~]# sudo service haproxy reload
    
# Installing mysql client in haproxy server

[root@haproxy-lb ~]# yum install mysql

# Testing the connection from haproxy server:

[root@haproxy-lb ~]# mysql -h 10.0.1.8 -u haproxy_root -p -e "SHOW DATABASES"

Testing Load Balancing and Failover
##################################

ubuntu@namenode:~$ mysql -h 10.0.1.9 -u haproxy_root -p -e "show variables like 'server_id'";
Enter password: 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
ubuntu@namenode:~$ mysql -h 10.0.1.9 -u haproxy_root -p -e "show variables like 'server_id'";
Enter password: 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
ubuntu@namenode:~$ mysql -h 10.0.1.9 -u haproxy_root -p -e "show variables like 'server_id'";
Enter password: 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
ubuntu@namenode:~$ mysql -h 10.0.1.9 -u haproxy_root -p -e "show variables like 'server_id'";
Enter password: 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+

Ref:
####


https://www.digitalocean.com/community/tutorials/how-to-use-haproxy-to-set-up-mysql-load-balancing--3