MySQL Replication on CentOS 6.5 and Haproxy

yiwai

　　MySQL 主从：
　　
　　On Master server: 192.168.20.14
1. disable selinux and iptables
service iptables stop
chkconfig iptables off; chkconfig ip6tables off

setenforce 0
vi /etc/selinux/config
SELINUX=disabled

2. yum -y install mysql-server
service mysqld start; chkconfig mysqld on
mysql_secure_installation

3. vi /etc/my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
　　expire_logs_days=10
max_binlog_size=100M
　　
service mysqld restart

4. mysql -uroot -p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysql_rep'@'%' IDENTIFIED BY 'password';
mysql> flush privileges;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status;
　　mysql> show processlist\G
　　

　　mysqldump -q -u root -p --all-databases --master-data > /root/dbdump.db

mysql -uroot -p
mysql> UNLOCK TABLES;
mysql> quit;

scp /root/dbdump.db root@192.168.20.15:/root/

On Slave server: 192.168.20.15
1. disable selinux and iptables
service iptables stop
chkconfig iptables off; chkconfig ip6tables off

setenforce 0
vi /etc/selinux/config
SELINUX=disabled

2. yum -y install mysql-server
service mysqld start; chkconfig mysqld on
mysql_secure_installation

3. vi /etc/my.cnf
[mysqld]
server-id = 2
　　log-bin = mysql-bin
　　expire_logs_days=10
max_binlog_size=100M
　　

　　service mysqld restart

mysql -uroot -p
mysql>stop slave;

mysql -u root -p < /root/dbdump.db

4. mysql -uroot -p
mysql> change master to master_host = '192.168.20.14',master_user = 'mysql_rep',master_password = 'password',master_log_file = 'mysql-bin.000001',master_log_pos = 106;
mysql> start slave;
mysql> show slave status\G
　　mysql> show processlist\G
　　
Notes: use "show master status" on master server to get master_log_file and master_log_pos
　　

　　HAproxy配置：

　　mysql master server: 192.168.20.14
mysql slave server: 192.168.20.15
haproxy server 1: 192.168.20.12
haproxy server 2: 192.168.20.13
haproxy VIP: 192.168.20.10

On master mysql server:
mysql -uroot -p
mysql> CREATE USER 'haproxy'@'192.168.20.12';
　　mysql> CREATE USER 'haproxy'@'192.168.20.13';
　　mysql> flush privileges;


On two haproxy server:

yum -y install mysql

vi /etc/haproxy/haproxy.cfg
defaults
#    option                  httplog
#    option http-server-close
#    option forwardfor       except 127.0.0.0/8

listen mysqld-status 192.168.20.10:3306
        balance source
        mode tcp
        option tcpka
        option mysql-check user haproxy
        server MySQL1 192.168.20.14:3306 check weight 1
        server MySQL2 192.168.20.15:3306 check backup weight 1

service haproxy reload

　　

　　Testing:
　　On master mysql server:
mysql -uroot -p
mysql> grant all on *.* to root@'%' identified by 'password' with grant option;
　　

　　from mysql client:

　　mysql -h 192.168.20.10 -uroot -p

　　

　　On Master Server:
mysql> create database yourdb;
mysql> use yourdb;
mysql> CREATE TABLE employee (c int);
mysql> INSERT INTO employee (c) VALUES (1);
mysql> SELECT * FROM employee;

On Slave Server:
mysql> use yourdb;
mysql> SELECT * FROM employee;
　　

　　主从切换：
　　first stop mysql service on old master
　　

　　On Old Slave server: 192.168.20.15
mysql -uroot -p
mysql> stop slave;
mysql> reset master;
mysql> show master status;
　　

　　cd /var/lib/mysql
　　rm -rf *relay*
　　rm -rf master.info
　　

　　On Old Master server: 192.168.20.14
　　service mysql start
mysql -uroot -p
mysql> stop slave;
mysql> change master to master_host = '192.168.20.15',master_user = 'mysql_rep',master_password = 'password',master_log_file = 'mysql-bin.000001',master_log_pos = 106;
　　mysql> start slave;
mysql> show slave status\G

　　

　　1. Backup

　　full backup:
mysqldump -q -uroot -ppassword --flush-logs --master-data=2 --all-databases > backup.sql

incremental backup:
do changes on database
mysqladmin -uroot -ppassword flush-logs

　　copy the second latest mysql-bin.x to safe place


　　
　　2. Restore

　　full restore:
mysql -uroot -ppassword < backup.sql

incremental restore:
mysqlbinlog mysql-bin.000002 mysql-bin.00000X ...(expect the latest mysql-bin.x)| mysql -uroot -ppassword

　　

　　mysql -uroot -ppassword
　　mysql> flush privileges;