mysql with keepalived

selectshen發表於2015-07-01
----主機資訊
os:cent os 6.5
host: ct6505/192.108.56.115 ct6506/192.108.56.116
mysql:5.6.24
keepalived:1.2.17

----安裝mysql
--ct6505,ct6506
[root@ct6505/6 ~]# ls My*
MySQL-client-advanced-5.6.24-1.el6.x86_64.rpm
MySQL-server-advanced-5.6.24-1.el6.x86_64.rpm

[root@ct6505/6 ~]# yum remove mysql-libs-5.1.71-1.el6.x86_64
[root@ct6505/6 ~]# yum install libaio
[root@ct6505/6 ~]# yum  install perl
[root@ct6505/6 ~]# rpm -ivh MySQL-*

----配置mysql主主複製
--ct6505,ct6506
[root@ct6505/6 ~]# mkdir -p  /u02/mysqldata
[root@ct6505/6 mysql]# cd /var/lib/mysql
[root@ct6505/6 mysql]# cp -r * /u02/mysqldata/
[root@ct6505/6 mysql]# chown -R mysql.mysql /u02/mysqldata

--ct6505
[root@ct6505 u02]# vi /usr/my.cnf
[mysqld]
pid-file=/u02/mysqldata/mysql01.pid
datadir =/u02/mysqldata

log_bin=/u02/mysqldata/mysql01.log
log_error=/u02/mysqldata/mysql01.err

server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog_format=row
innodb_support_xa=1

innodb_buffer_pool_size=512M
innodb_additional_mem_pool_size=20M
innodb_log_file_size=256M
innodb_log_files_in_group=3
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

--ct6506
[root@ct6506 u02]# vi /usr/my.cnf
[mysqld]
pid-file=/u02/mysqldata/mysql02.pid
datadir =/u02/mysqldata

log_bin=/u02/mysqldata/mysql02.log
log_error=/u02/mysqldata/mysql02.err

server-id=2
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog_format=row
innodb_support_xa=1

innodb_buffer_pool_size=512M
innodb_additional_mem_pool_size=20M
innodb_log_file_size=256M
innodb_log_files_in_group=3
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

--ct6505,ct6506
[root@ct6505/6 ~]# cat /root/.mysql_secret
# The random password set for the root user at Fri Jun 19 13:25:21 2015 (local time): Igi1I1PJxfzOdRLq

[root@ct6505/6 mysql]# mysql -uroot -p
mysql> SET PASSWORD = PASSWORD('system');
mysql>  use mysql;
mysql> update user set host='%' where host='localhost';
mysql> flush privileges;

--ct6506
mysql> SHOW MASTER STATUS

--ct6505
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='192.108.56.116',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='repl',
    MASTER_LOG_FILE='mysql02.000001',    --此處的值為ct6506上SHOW MASTER STATUS得到的結果
    MASTER_LOG_POS=123;     --此處的值為ct6506上SHOW MASTER STATUS得到的結果
mysql> START SLAVE;

--ct6505
mysql> SHOW MASTER STATUS

--ct6506
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='192.108.56.115',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='repl',
    MASTER_LOG_FILE='mysql02.000001',    --此處的值為ct6505上SHOW MASTER STATUS得到的結果
    MASTER_LOG_POS=321;    --此處的值為ct6505上SHOW MASTER STATUS得到的結果
mysql> START SLAVE;


----安裝keepalived
--ct6505,ct6506
[root@ct6505/6 ~]# tar -xzvf keepalived-1.2.17.tar.gz
[root@ct6505/6 keepalived-1.2.17]# yum install gcc
[root@ct6505/6 keepalived-1.2.17]# yum install openssl openssl-devel
[root@ct6505/6 keepalived-1.2.17]# ./configure
[root@ct6505/6 keepalived-1.2.17]# make && make install

[root@ct6505/6 keepalived-1.2.17]# mkdir /etc/keepalived

----配置keepalived
--ct6505
[root@ct6505 keepalived-1.2.17]#  vi /etc/keepalived/keepalived.conf
global_defs {
router_id mysql-ha
}
vrrp_instance VI_1 {
state backup
interface eth0
virtual_router_id 51
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.108.56.117/24
#192.108.56.117 label eht0:1
}
}
virtual_server 192.108.56.117 3306 {
    delay_loop 2
     lb_algo wrr
     lb_kind DR
persistence_timeout 60
      protocol TCP
real_server 192.108.56.115 3306 {
      weight 3
      notify_down /etc/keepalived/mysql-ha.sh
     TCP_CHECK {
      connect_timeout 10
     nb_get_retry 3
      delay_before_retry 3
      connect_port 3306
      }
    }
}


[root@ct6505 ~]# vi /etc/keepalived/mysql-ha.sh
#!/bin/sh
pkill keepalived

[root@ct6505 ~]# chmod +x /etc/keepalived/mysql-ha.sh

--ct6506
[root@ct6506 mysql]# mkdir /etc/keepalived
[root@ct6506 mysql]# vi /etc/keepalived/keepalived.conf
global_defs {
router_id mysql-ha
}
vrrp_instance VI_1 {
state backup
interface eth0
virtual_router_id 51
priority 90
advert_int 1
#nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.108.56.117/24
#192.108.56.117 label eht0:1
}
}
virtual_server 192.108.56.117 3306 {
    delay_loop 2
     lb_algo wrr
     lb_kind DR
 persistence_timeout 60
      protocol TCP
     real_server 192.108.56.116 3306 {
      weight 3
      notify_down /etc/keepalived/mysql-ha.sh
 TCP_CHECK {
      connect_timeout 10
     nb_get_retry 3
      delay_before_retry 3
      connect_port 3306
      }
    }
}

[root@ct6506 ~]# vi /etc/keepalived/mysql-ha.sh
#!/bin/sh
pkill keepalived

[root@ct6506 ~]# chmod +x /etc/keepalived/mysql-ha.sh

--ct6505,ct6506
[root@ct6505/6 ~]keepalived -D

PS:測試省略.keepalived也可以配置成服務,更方便.

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28539951/viewspace-1719377/,如需轉載,請註明出處,否則將追究法律責任。

相關文章