Pages

Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts

Thursday, June 26, 2014

Mysql HA with Haproxy and Master-Master replication

Once Mysql master -master replication is done we set HA with those using HAproxy

http://enekumvenamorublog.wordpress.com/2014/06/25/mysql-replication-master-master/

HAProxy

yum install haproxy

To use Haproxy with MYsql we need to create a user in mysql so that haproxy can access it .

GRANT ALL PRIVILEGES ON *.* TO 'haproxy'@'192.168.216.180' IDENTIFIED BY '';

Sample configuration  /etc/haproxy/haproxy.cfg

global
log 127.0.0.1 local0 notice
user haproxy
group haproxy

# turn on stats unix socket
stats socket /var/lib/haproxy/stats mode 777

defaults
log global
retries 2
timeout connect 1000
timeout server 5000
timeout client 5000
listen stats 192.168.255.180:80
mode http
stats enable
stats uri /stats
stats realm HAProxy\ Statistics
stats auth admin:password

listen MYSQL 192.168.255.190:3306
balance source
mode tcp
option mysql-check user haproxy
server controller1 192.168.216.130 check
server controller2 192.168.216.135 check
[root@HAPROXY ~]#

Wednesday, June 25, 2014

Mysql replication-Master-Master

MySQL Master-Master replication.

Master-1 my.cnf configuration:

mkdir /var/lib/mysql/log/
log-bin=/var/lib/mysql/log/mysql-bin
log_warnings
log_slow_queries = /var/lib/mysql/log/slow.log
long_query_time = 5
log_long_format
tmpdir = /tmp
server-id = 1
log_slave_updates
replicate-same-server-id = 0
auto_increment_increment = 10
auto_increment_offset = 1
relay-log = mysql-relay-bin

Master-2 my.cnf configuration:
mkdir /var/lib/mysql/log/
log-bin=/var/lib/mysql/log/mysql-bin
log_warnings
log_slow_queries = /var/lib/mysql/log/slow.log
long_query_time = 5
log_long_format
tmpdir = /tmp
server-id = 2
replicate-same-server-id = 0
auto_increment_increment = 2
auto_increment_offset = 2
relay-log = mysql-relay-bin

First setup Master1 as Master and Master2 as slave for Master-1:

Follow below steps:
On Master-1:

grant replication slave on *.* to 'root'@'192.168.216.135' identified by 'admin';
show master status;

It shows file name and position, Use these records on Master-2 to run it as slave for Master-1.
Step 3: Now log on to master-2 and run the below query:

CHANGE MASTER TO MASTER_HOST='192.168.216.130', MASTER_USER='root',MASTER_PASSWORD='admin', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=106;

Step 4: start slave
Step 5: show slave status \G

On this status, the following 2 records should be as follows
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

These 2 records indicates Replication status. If these parameters show “Yes” that means replication is running successfully.

Setup Master2 as Master and Master1 as slave for Master-2:

On Master-2 server:

grant replication slave on *.* to 'root'@'192.168.216.130' identified by 'admin

Step 2: mysql> show master status;

Step 3: Now log on to master-1 and run the below query:
CHANGE MASTER TO MASTER_HOST='192.168.216.135', MASTER_USER='root',MASTER_PASSWORD='admin', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=346;
Step 4: start slave
Step 5: show slave status \G

The following parameters should show “Yes”, so that replication is running successfully

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

On both servers “slave_IO_Running” and “slave_SQL_Running” parameters should always be “Yes” for successful Master-Master Replication.