mysql读写分离nginx_mysql读写分离ha的实现

解释说明

两台mysql服务器做数据复制，一台机器处于空闲状态，只是在做备份，如果mysql的读和写分开，会提高mysql的性能，所以用heartbeat做两个VIP，一个VIP在一台服务器上负责读，一个VIP另一台服务上负责写，当一个服务器down机后，down了的服务器的VIP漂到另一台服务器上。负责写的mysql服务器将数据同步到负责读的mysql服务器上保障数据一至性.

heartbeat安装(主从相同)

yum install heartbeat

# cp /usr/share/doc/heartbeat-2.1.3/ha.cf /etc/ha.d/

# cp /usr/share/doc/heartbeat-2.1.3/authkeys /etc/ha.d/

# cp /usr/share/doc/heartbeat-2.1.3/haresources /etc/ha.d/

# vi /etc/ha.d/ha.cf

debugfile /var/log/ha-debug

logfile /var/log/ha-log

logfacility     local0

keepalive 2

deadtime 30

warntime 10

initdead 120

udpport 694

bcast   eth1

auto_failback off

node    master

node    slave

# vi /etc/ha.d/haresources

master 172.20.67.23

slave 172.20.67.24

# vi /etc/ha.d/authkeys

auth 1

1 crc

# chmod 600 /etc/ha.d/authkeys

# chkconfig heartbeat off

设置服务自动启动(主从相同)

# vi /etc/rc.local

service heartbeat start

mysql主从复制配置

主服务器配置

mysql> grant replication slave on *.* to 'copy'@'%' identified by '123123';

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.37 sec)

主: # tar cvf /var/lib/mysql/mysql-snapshot.tar .并copy到从服务器上

从: # tar xvf mysql-snapshot.tar

主: mysql> show master status;(锁定表时)

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000006 |      106 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

记住以上内容

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

从服务器配置

# vi /etc/my.cnf

log-bin=mysql-bin

server-id       = 1

确保这两行存在

从服务器确保server-id       = 2存在

mysql> change master to master_host='master', master_user='copy', master_password='123123', master_log_file='mysql-bin.000006' , master_log_pos=106;

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

或在my.cnf文件设置如下信息

server-id       = 2

master-host=master

master-port=3306

master-user=copy

master-password=123123

master-connect-retry=60

report-host=slave

replicate-do-db=zds