Xinu's Note - 数据库优化
type: channel
link: https://xbug.top/index.php/tag/数据库优化/
items: mysql优化之主从复制与读写分离部署

mysql优化之主从复制与读写分离部署
type: item
date: 2017-10-18T21:50:00+08:00
description: mysql优化之主从复制与读写分离部署1. ?读写分离与主从复制 如上图所示，当web server1/2/3要写入数据时，则向mysql db Master(主服务器）发出写入请求（即写入到master），如果要进入读操作时，则只向从服务器 mysql DB Slave1或2或3发出读取请求。如此将原本读写在同一台服务器的工作量分摊到了一台负责写入，N台负责读取（大部分的网站都是读取请求远大于写入请求），从而从一定程度时实现了负载均衡（如果有N台从服务器，则由主从代理系统自动分配某个具体请求从哪一个从服务器读取）。 读写分离是靠主从复制来实现的。即当一个数据写入到主服务器后，主服务器会将写入信息写入到binlog（二进制日志）里，同时同步（或异步或半同步）到从服务器里。从服务器根据主服务器传来的binlog，生成relay-log(中继日志），然后mysql服务器再用relay-log的信息将数据写入到数据库。 如此做的优点除了实现负责均衡之外，还为我们保留了两份实时热备的数据binlog和relay-log。当服务器发生灾难时，我们可以用他们将数据恢复到任何一个时间点。2.?准备在linux上安装好两台mysql服务器（mysql至少5.5以上），且两台服务器最好在一个机房（在一个局域网内，访问速度远远大于公网，无需流量）。假定主的IP为172.16.0.1，从的IP为172.16.0.2。关闭两台服务器的防火墙（稍后再配置）：iptables stop （这是centos6.5关闭方法，其他系统请问度娘）如果你没有创建数据库，则进行如下操作(1)在主服务器上登录mysql# mysql -uroot -p你的密码(2)创建数据库mysql> create database test1;mysql> create database test2;(3)如果已经有历史数据，则将历史数据备份成sql文件上传到主服务器，运行如下命令mysql> source /disk/test1.sql;（'/disk/test1.sql '换成你自己的路径即可）3. ?主服务器配置mysql已经提供了完美的读写分离与主从复制支持，我们只需要作如下设置即可。1、配置my.cnf# vi /etc/my.cnf(1)去掉log-bin=mysql-bin前的“#”号。这一行的意思是允许mysql使用binlog，同时为主从复制打开了大门。这是关键中的关键中的关键。(2)如果有多个数据库，则添加如下行：binlog-do-db=数据库名1binlog-do-db=数据库名2binlog-do-db=数据库名3binlog-ignore-db=mysql(忽略mysql自身用的数据库)(3)查看server-id是后的数字并记住。slave(从服务器)的server-id不能与该值重复。(4)查看expire-log-day。binlog过期时间，默认为10天。你可以根据你硬盘空间大小及每天产生的数据量修改。如果你有冷备，则一般设成2-3天即可。2、操作数据库(1)在linux命令行下登录mysql# mysql -uroot -p(你的root密码)(2)创建并授权一个帐号mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_account'@'172.16.0.2' identified by '123456'这句的意思是，允许从服务器'172.16.0.2 '使用'slave-account'及'123456'这个帐号密码对对主服务器的所有数据库（.)进行主从复制（'REPLICATION SLAVE').(3)重启mysql使上述配置生效 # etc/init.d/mysql restart 或# service mysqld restart(4)查看主服务器binlog状态mysql> show master stauts;记下File及Position下的值。以备在配置从服务器时使用。注：File：当前binlog的文件名，每重启一次mysql，就会生成一个新binlog文件 Position：当前binlog的指针位置4.?从服务器配置1、配置mysql.cnf# vi /etc/my.cnf(1)修改server-id=2（该值不能与主服务器的server-id同。如果有多个从服务器，则该值顺延）(2)添加如下两行：relay-log-index=slave-relay-bin.index (中继日志的索引文件）relay-log=slave-relay-bin （中继日志的文件前缀）(3)重启mysql使上述配置生效# /etc/init.d/mysql restart(4)登录mysql#…