web mysql架构_web系统架构-mysql主从同步配置（单主单从）

1、概述

mysql主从配置，实现读写分离

大型网站为了软解大量的并发访问，除了在网站实现分布式负载均衡，远远不够。到了数据业务层、数据访问层，如果还是传统的数据结构，或者只是单单靠一台服务器扛，如此多的数据库连接操作，数据库必然会崩溃，数据丢失的话，后果更是 不堪设想。这时候，我们会考虑如何减少数据库的联接，一方面采用优秀的代码框架，进行代码的优化，采用优秀的数据缓存技术如：memcached,如果资金丰厚的话，必然会想到假设服务器群，来分担主数据库的压力。Ok切入今天微博主题，利用MySQL主从配置，实现读写分离，减轻数据库压力。

2、原理

主服务器(Master)负责网站NonQuery操作，从服务器负责Query操作，用户可以根据网站功能模特性块固定访问Slave服务器，或者自己写个池或队列，自由为请求分配从服务器连接。主从服务器利用MySQL的二进制日志文件，实现数据同步。二进制日志由主服务器产生，从服务器响应获取同步数据库。

3、实现

两台服务器，配置centos 64位，mysql版本5.6.10，mysql安装教程参考：web系统架构-centos下mysql安装卸载和管理，

master：192.168.150.137，slave：192.168.150.156。

3.1 master服务器配置

1)修改服务器配置

vi my.cnf

[mysqld]

log-bin=mysql-bin #[必须]启用二进制日志

server-id=137 #[必须]服务器唯一ID，默认是1，一般取IP最后一段

2)重启数据库

service mysqld restart

3)创建账号并授权slave

mysql -uroot -pmyadmin

授权slave：

mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'q123456';

一般不用root帐号，'%'表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.150.156，加强安全。

4)登陆master数据库，查看master的状态

show master status;

显示如下表示正常

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 | 1196 | | | |

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

1 row in set (0.00 sec)

File：表示生成的二进制日志名字

Position：位置偏移量，每次操作master数据库后，这个偏移量会自动增加

上述两个参数在配置slave中需要对应，否则slave不能完成同步，稍后会详细描述。

3.2 slave服务器配置

1)修改服务器配置

vi my.cnf

[mysqld]

log-bin=mysql-bin #[必须]启用二进制日志(slave不是必须开启的)

server-id=156 #[必须]服务器唯一ID，默认是1，一般取IP最后一段

2)配置slave选项

登陆数据库

change master to master_host='192.168.150.137',master_user='mysync',master_password='q123456',master_log_file='mysql-bin.000001',master_log_pos=831;

参数说明：

server-id=n //设置数据库id默认主服务器是1可以随便设置但是如果有多台从服务器则不能重复。

master-host=192.168.150.137 //主服务器的IP地址或者域名

master-port=3306 //主数据库的端口号

master-user=mysync //同步数据库的用户

master-password=q123456 //同步数据库的密码

master-connect-retry=60 //如果从服务器发现主服务器断掉，重新连接的时间差

replicate-do-db=dcs //进行同步的数据库

master_log_file=mysql-bin.000001 //master服务器的二进制日志文件

master_log_pos=831 //master服务器二进制文件的偏移量(可以从0开始)

3)启动slave

在2)基础上继续执行

start slave;

4)查看slave状态

show slave status\G

显示如下：

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.150.137

Master_User: mysync

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 971

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 423

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 971

Relay_Log_Space: 600

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 137

Master_UUID: 2117e492-5573-11e6-8010-000c29965f14

Master_Info_File: /data/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

如果这两个参数显示如下，则表示同步正常；

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

如果出现异常，可以查看msyql的错误日志，或者在show slave status\G中查看。

3.3维护

1)如果从服务器只能读不能写，可以多设置一个参数：

设置从服务器为readonly

mysql -e "set global read_only=1;"

完成以上步骤，可以在主表中进行测试了。

2)监控slave运行的脚本

#!/bin/bash

user=***

password=***

echo -e "please inpute slave portarl ip:"

read host

cd `dirname $0`

mysql -u$user -p$password -h$host -e "show slave status\G;" | grep -i running >slave.log

#grep -i, --ignore-case

# 忽略大小写，包含要搜寻的样式及被搜寻的档案。

if [ `cat slave.log | grep -i io | awk '{print $2}'` = Yes ]

then

echo "slave IO process is OK"

else

echo "slave IO process is error"

fi

if [ `cat slave.log | grep -i sql | awk '{print $2}'` = Yes ]

then

echo "slave SQL process is OK"

else

echo "slave SQL process is error"

fi

rm -rf slave_status

监控：Slave_IO_Running和Slave_SQL_Running是否为yes即可。