3)mdb1和mdb2:
重启mysql
/etc/init.d/mysql restart
添加复制用户
mysql -uroot -pgaojinbo.com
GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.168.5.%’ IDENTIFIED BY ‘gaojinbo’;
记录日志文件和pos
mysql -uroot -pgaojinbo.com
show master status\G
4)mdb1:
change master to master_host=’192.168.5.12′,master_port=3306,master_user=’repl’,master_password=’gaojinbo’,master_log_file=’mysql-bin.000003′,master_log_pos=106;
start slave;
show slave status\G
说明：mysql-bin.000003和106是主库配置第3)步记录的信息
出现以下内容，说明同步ok
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5)mdb2:
change master to master_host=’192.168.5.11′,master_port=3306,master_user=’repl’,master_password=’gaojinbo’,master_log_file=’mysql-bin.000001′,master_log_pos=249;
start slave;
show slave status\G
说明：mysql-bin.000001和249是主库配置第3)步记录的信息
出现以下内容，说明同步ok
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
6）测试主-主同步
mdb1:
mysql -uroot -pgaojinbo.com
show databases;
create database gaojinbo;
mdb2:
mysql -uroot -pgaojinbo.com
show databases;
即可看到在mdb1上建立的数据库gaojinbo
sdb1和sdb2配置成mdb1的从库:
mysql -uroot -pgaojinbo.com
change master to master_host=’192.168.5.11′,master_port=3306,master_user=’repl’,master_password=’gaojinbo’,master_log_file=’mysql-bin.000001′,master_log_pos=345;
start slave;
show slave status\G
说明：mysql-bin.000001和345是主库配置第3)步记录的信息
出现以下内容，说明同步ok
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
sdb3和sdb4配置成mdb2的从库:
change master to master_host=’192.168.5.12′,master_port=3306,master_user=’repl’,master_password=’gaojinbo’,master_log_file=’mysql-bin.000003′,master_log_pos=106;
start slave;
show slave status\G
说明：mysql-bin.000003和106是主库配置第3)步记录的信息
出现以下内容，说明同步ok
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
测试：
mdb1:
mysql -uroot -pgaojinbo.com
show databases;
create database eossc;
在其他DB上，这时会看到刚建立的数据库eossc
至此4台从数据库配置完成！
6.编写mysql检测脚本 1）mdb1和mdb2:
vi /etc/xinetd.d/mysqlchk
#
# /etc/xinetd.d/mysqlchk
#
service mysqlchk_write
{
flags = REUSE
socket_type = stream
port = 9200
wait = no
user = nobody
server = /opt/mysqlchk_status.sh
log_on_failure += USERID
disable = no
only_from = 192.168.5.0/24 # recommended to put the IPs that need
# to connect exclusively (security purposes)
}
service mysqlchk_replication
{
flags = REUSE
socket_type = stream
port = 9201
wait = no
user = nobody
server = /opt/mysqlchk_replication.sh
log_on_failure += USERID
disable = no
only_from = 192.168.5.0/24 # recommended to put the IPs that need
# to connect exclusively (security purposes)
}
添加服务端口
vi /etc/services
mysqlchk_write 9200/tcp #mysqlchk_write
mysqlchk_replication 9201/tcp #mysqlchk_replication
mdb1上操作：
vi /opt/mysqlchk_status.sh
#!/bin/bash
MYSQL_HOST="192.168.5.11"
MYSQL_PORT="3306"
MYSQL_USERNAME="root"
MYSQL_PASSWORD="gaojinbo.com"
if [ "$ERROR_MSG" != "" ]
then
# mysql is fine, return http 200
/bin/echo -e "HTTP/1.1 200 OK\r\n"
/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
/bin/echo -e "\r\n"
/bin/echo -e "MySQL is running.\r\n"
/bin/echo -e "\r\n"
else
# mysql is down, return http 503
/bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
/bin/echo -e "\r\n"
/bin/echo -e "MySQL is *down*.\r\n"
/bin/echo -e "\r\n"
fi
vi /opt/mysqlchk_replication.sh
#!/bin/bash
MYSQL_HOST="192.168.5.11"
MYSQL_PORT="3306"
MYSQL_USERNAME="root"
MYSQL_PASSWORD="gaojinbo.com"
/usr/bin/mysql –host=$MYSQL_HOST –port=$MYSQL_PORT –user=$MYSQL_USERNAME –password=$MYSQL_PASSWORD -e "show slave status\G;" >/tmp/check_repl.txt
iostat=`grep "Slave_IO_Running" /tmp/check_repl.txt |awk ‘{print $2}’ `
sqlstat=`grep "Slave_SQL_Running" /tmp/check_repl.txt |awk ‘{print $2}’ `
#echo iostat:$iostat and sqlstat:$sqlstat
if [ "$iostat" = "No" ] || [ "$sqlstat" = "No" ];
then
# mysql is down, return http 503
/bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
/bin/echo -e "\r\n"
/bin/echo -e "MySQL replication is *down*.\r\n"
/bin/echo -e "\r\n"
else
# mysql is fine, return http 200
/bin/echo -e "HTTP/1.1 200 OK\r\n"
/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
/bin/echo -e "\r\n"
/bin/echo -e "MySQL replication is running.\r\n"
/bin/echo -e "\r\n"
fi
测试同步检测脚本：
mysql -uroot -pgaojinbo.com
stop slave sql_thread; #或者 stop slave io_thread;
/opt/mysqlchk_replication.sh
mdb2上操作：
添加和mdb1一样的脚本，把
/opt/mysqlchk_status.sh 里面的192.168.5.11修改为192.168.5.12
/opt/mysqlchk_replication.sh 里面的192.168.5.11修改为192.168.5.12
2）sdb1,sdb2,sdb3,sdb4上操作：
vi /etc/xinetd.d/mysqlchk
#
# /etc/xinetd.d/mysqlchk
#
service mysqlchk_replication
{
flags = REUSE
socket_type = stream
port = 9201
wait = no
user = nobody
server = /opt/mysqlchk_replication.sh
log_on_failure += USERID
disable = no
only_from = 192.168.5.0/24 # recommended to put the IPs that need
# to connect exclusively (security purposes)
}
vi /opt/mysqlchk_replication.sh
#!/bin/bash
MYSQL_HOST="192.168.5.21"
MYSQL_PORT="3306"
MYSQL_USERNAME="root"
MYSQL_PASSWORD="gaojinbo.com"
/usr/bin/mysql –host=$MYSQL_HOST –port=$MYSQL_PORT –user=$MYSQL_USERNAME –password=$MYSQL_PASSWORD -e "show slave status\G;" >/tmp/check_repl.txt
iostat=`grep "Slave_IO_Running" /tmp/check_repl.txt |awk ‘{print $2}’ `
sqlstat=`grep "Slave_SQL_Running" /tmp/check_repl.txt |awk ‘{print $2}’ `
#echo iostat:$iostat and sqlstat:$sqlstat
if [ "$iostat" = "No" ] || [ "$sqlstat" = "No" ];
then
# mysql is down, return http 503
/bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
/bin/echo -e "\r\n"
/bin/echo -e "MySQL replication is *down*.\r\n"
/bin/echo -e "\r\n"
else
# mysql is fine, return http 200
/bin/echo -e "HTTP/1.1 200 OK\r\n"
/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
/bin/echo -e "\r\n"
/bin/echo -e "MySQL replication is running.\r\n"
/bin/echo -e "\r\n"
fi
注：脚本/opt/mysqlchk_replication.sh里面的ip
sdb1 MYSQL_HOST="192.168.5.21"
sdb2 MYSQL_HOST="192.168.5.22"
sdb3 MYSQL_HOST="192.168.5.23"
sdb4 MYSQL_HOST="192.168.5.24"
添加服务端口
vi /etc/services
mysqlchk_replication 9201/tcp #mysqlchk_replication
7.haproxy安装配置 下载编译安装：
wget http://haproxy.1wt.eu/download/1.4/src/haproxy-1.4.11.tar.gz
tar xvzf haproxy-1.4.11.tar.gz
cd haproxy-1.4.11
make TARGET=linux26 ARCH=x86_64
make install
backend mdb2_replication
mode tcp
balance roundrobin
option tcpka
option httpchk
server mdb2 192.168.5.12:3306 check port 9201 inter 1s rise 1 fall 1
backend sdb1_replication
mode tcp
balance roundrobin
option tcpka
option httpchk
server sdb1 192.168.5.21:3306 check port 9201 inter 1s rise 1 fall 1
backend sdb2_replication
mode tcp
balance roundrobin
option tcpka
option httpchk
server sdb2 192.168.5.22:3306 check port 9201 inter 1s rise 1 fall 1
backend sdb3_replication
mode tcp
balance roundrobin
option tcpka
option httpchk
server sdb3 192.168.5.23:3306 check port 9201 inter 1s rise 1 fall 1
backend sdb4_replication
mode tcp
balance roundrobin
option tcpka
option httpchk
server sdb4 192.168.5.24:3306 check port 9201 inter 1s rise 1 fall 1
backend mdb1_status
mode tcp
balance roundrobin
option tcpka
option httpchk
server mdb1 192.168.5.11:3306 check port 9200 inter 1s rise 2 fall 2
backend mdb2_status
mode tcp
balance roundrobin
option tcpka
option httpchk
server mdb2 192.168.5.12:3306 check port 9200 inter 1s rise 2 fall 2
backend cluster_db_write
mode tcp
option tcpka
balance roundrobin
option httpchk GET /dbs
server mdb1 192.168.5.11:3306 weight 1 check port 9201 inter 1s rise 5 fall 1
server mdb2 192.168.5.12:3306 weight 1 check port 9201 inter 1s rise 5 fall 1 backup
server mdb1_backup 192.168.5.11:3306 weight 1 check port 9301 inter 1s rise 2 fall 2 addr 127.0.0.1 backup
server mdb2_backup 192.168.5.12:3306 weight 1 check port 9302 inter 1s rise 2 fall 2 addr 127.0.0.1 backup
backend cluster_db_read
mode tcp
option tcpka
balance roundrobin
option httpchk GET /dbs
server mdb1 192.168.5.11:3306 weight 1 track cluster_db_write/mdb1
server mdb2 192.168.5.12:3306 weight 1 track cluster_db_write/mdb2
server mdb1_backup 192.168.5.11:3306 weight 1 track cluster_db_write/mdb1_backup
server mdb2_backup 192.168.5.12:3306 weight 1 track cluster_db_write/mdb2_backup
server mdb1_splitbrain 192.168.5.11:3306 weight 1 check port 9300 inter 1s rise 1 fall 2 addr 127.0.0.1
server mdb2_splitbrain 192.168.5.12:3306 weight 1 check port 9300 inter 1s rise 1 fall 2 addr 127.0.0.1
server sdb1_slave 192.168.5.21:3306 weight 1 check port 9303 inter 1s rise 5 fall 1 addr 127.0.0.1
server sdb2_slave 192.168.5.22:3306 weight 1 check port 9304 inter 1s rise 5 fall 1 addr 127.0.0.1
server sdb3_slave 192.168.5.23:3306 weight 1 check port 9305 inter 1s rise 5 fall 1 addr 127.0.0.1
server sdb4_slave 192.168.5.24:3306 weight 1 check port 9306 inter 1s rise 5 fall 1 addr 127.0.0.1
