MySQL 分布式主从读写分离架构及实战

2015-03-31 09:15:53   最后更新: 2015-04-01 09:15:26   访问数量:3364




随着网站业务的不断发展,用户量不断增加,数据量成倍增长,数据库的负载压力也越来越大,同时,为了防止数据库异常造成的数据丢失,我们常常需要两台或多态数据库的主从关系,可以将一台数据库服务器的数据更新同步到另一台

网站可以利用 mysql 提供的特性,实现数据库读写分离,从而改善数据库的负载能力,并提供稳定的服务依托

 

如上图,所谓的服务器读写分离就是让 web 服务器在写数据的时候选择主服务器,主数据库通过主从复制机制将数据更新同步到从数据库

这样当 web 服务器读取数据的时候,就可以通过从数据库获取数据

这一方案使得大量度操作的 web 应用可以轻松读取数据,而主数据库也只会承受少量写入操作,并且实现了热备份,收到一举两得的效果

 

MySQL 主从备份共分为三个步骤:

  1. Master 将改变记录到二进制日志 binlog 中
  2. Slaver 将 Master 的二进制日志事件拷贝到他的中继日志 relay log 中
  3. Slaver 重做中继日志中的事件,将 Master 上的改变反映到自己数据库中

 

Slaver 服务器中有一个 IO 线程在不断监听 Master 的 binlog 是否有更新,从而在 binlog 更新后进行上述第二步操作

同时,Slaver 中还有一个 SQL 线程,从中继日志中读取事件,完成上述第三步操作

 

安装完成 mysql 后,修改 /etc/my.cnf,添加下列配置:

[client] default-character-set=utf8 [mysqld] character_set_server=utf8 sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION log-bin=master-bin log-bin-index=master.bin.index server-id=1

 

 

同时要记得去掉下面的配置:

bind-address = 127.0.0.1

 

否则只能用本机登录 mysql

 

然后执行下列命令重启 mysql

sudo service mysql stop sudo service mysql start

 

 

执行:

sudo netstat -tap | grep mysql

 

可以看到:

 

说明 mysql 已启动

 

同时,打开 mysql,执行:

SHOW MASTER STATUS;

 

可以看到:

 

 

Slaver 想要访问 Master,必须 Master 授予相应的权限:

create user mysql_msuser; GRANT REPLICATION SLAVE ON *.* TO 'mysql_msuser'@'192.168.1.141' IDENTIFIED BY 'mysql_slave';

 

 

首先创建了用户 mysql_msuser 并为该用户授予了 REPLICATION SLAVE 权限,密码为 mysql_slave,这个用户所在服务器 IP 地址为 192.168.1.141,除此之外的其他权限则无需添加

此时,你可以登录到 Slaver 机器上查看是否可以通过远程登录 Master 上的 mysql

 

在从服务器上,同样要打开 MySQL 的配置文件,添加下列配置:

[client] port=3306 default-character-set=utf8 [mysqld] port=3306 #character_set_server=utf8 一定要这样写; character_set_server=utf8 #sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 这个有问题,在创建完新用户登录时报错 sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION #从服务器的配置 #01.为服务器添加唯一的编号 server-id=2 #02.开启中继日志 relay-log=slave-relay-log-bin #03.使用中继日志的索引文件 relay-log-index=slave-relay-log-bin.index

 

 

与 master 一样,更新配置之后需要重启 mysql 服务

 

使用 root 帐号登录 mysql,执行下列语句配置主从关系:

change master to master_host="192.168.1.140", master_port=3306, master_user="mysql_msuser", master_password="mysql_slave", master_log_file="mysql-bin.000001", master_log_pos=0; start slave;

 

 

到这一步配置工作就已经大功告成了

可以执行:

show slave status \G

 

查看 slave 状态:

 

 

可以看到:

Slave_IO_Running=Yes

Slave_SQL_Running=Yes

说明 slave 的 IO 和 SQL 线程都已经开始运行

 

千万要注意,从库只读,轻易不可以进行写操作,否则可能造成同步失败

对于同步失败的情况,首先执行 stop slave 停止 slave 服务,然后手动同步到当前状态

然后通过指定 master_log_pos 重新执行 change master to 语句,start slave 即可

 

欢迎关注微信公众号,以技术为主,涉及历史、人文等多领域的学习与感悟,每周三到七篇推文,只有全部原创,只有干货没有鸡汤

 






技术帖      linux      unix      mysql      database      技术分享      server      分布式      读写分离      分布式系统      架构      搭建      mysqld      service     


京ICP备2021035038号