数据库应用——MySQL主从复制与读写分离

                                                 MySQL主从复制与读写分离

一、MySQL主从复制
1、主从复制原理
(1)支持的复制类型
①基于语句的复制：在服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高
②基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一次。
③混合类型的复制：默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制
(2)复制的工作过程
①每个事务更新数据完成之前，master在二进制日志九路这些改变。写入二进制日志完成后，master通知存储引擎提交事务。
②slave将master的binary log复制到其中级日志。首先，slave开始一个工作线程——IO线程，该线程在master上打开一个普通连接，开始binlog dump process。binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。该线程将这些事件写入中继日志
③sql slave thread处理该过程最后一步。从中继日志读取事件，并重放其中的时间而跟新slave的数据。
④复制过程中有一个重要限制，即复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作

2、案例实施
(1)建立时间同步环境，在主节点搭建时间同步服务器（也可直接同步到官方时间服务器）
①安装NTP：yum -y install ntp
②配置NTP：vim /etc/ntp.conf
1)server 127.127.1.0
2)fudge 127.127.1.0 stratum 8
③重启服务：service ntpd restart
(2)从节点进行时间同步
①ntpdate 192.168.11.41
②也可将主从服务器都同步到其他服务器：ntpdate 0.asia.pool.ntp.org
(3)关闭防火墙或设置开放端口
(4)配置mysql master主服务器
①修改配置文件my.cnf
1)修改：server-id = 11
2)修改：log-bin=master-bin
3)增加：log-slave-updates=true
②重启mysqld服务
③给从服务器授权
1)mysql -uroot -p
2)grant replication slave on *.* to ‘userslave’@’%’ identified by’111111’;
3)flush privileges;
4)获取日志名及偏移量：show master status;
(5)配置从服务器
①修改my.cnf
1)修改：server-id = 22 （ID不能与主服务器相同）
2)修改：log-bin=relay-log-bin
3)增加：relay-log-index=slave-relay-bin.index
②重启mysqld服务
③登录mysql，设置同步
1)mysql -uroot -p
2)change master to master_host=’192.168.11.41’,master_user=’userslave’,master_password=’111111’,master_log_file=’master-bin.000001’,master_log_pos=339;
3)start slave;
④查看slave状态：show slave status\G，确保slave_io_runnint及slave_sql_running为yes
(6)测试：
①主服务器

②从服务器

 

二、MySQL读写分离
1、读写分离原理
(1)只在主服务器上写，只在从服务器上读
(2)实现方式
①程序代码实现
②基于中间代理层实现
1)MySQL-Proxy：通过自带的lua脚本进行SQL判断，官方不建议将其用到生产环境
2)Amoeba：由java语言开发，不支持事务和存储过程

2、案例实施
(1)安装java环境（jdk1.5或1.6）
①赋予可执行权限：chmod +x jdk-6u14-linux-x64.bin
②执行安装：./ jdk-6u14-linux-x64.bin
③移动文件夹：mv jdk1.6.0_14/ /usr/local/jdk1.6
④设置环境变量：vim /etc/profile
1)export JAVA_HOME=/usr/local/jdk1.6
2)export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
3)export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/lib:$PATH:$HOME/bin:$JAVA_HOME/bin
4)export AMOEBA_HOME=/usr/local/amoeba
5)export PATH=$PATH:$AMOEBA_HOME/bin
⑤导入环境变量：source /etc/profile
(2)安装配置amoeba
①创建目录：mkdir /usr/local/amoeba
②解包：tar -zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
③文件夹授权：chmod -R 755 /usr/local/amoeba
(3)配置读写分离，两个slave负载均衡
①master、slave1、slave2中开放权限给amoeba
1)grant all on *.* to amuser@'%' identified by '111111';
②编辑amoeba.xml配置文件
1)

2)

③编辑dbServers.xml文件
1)

2)

④启动amoeba，默认端口8066：amoeba start
(4)测试
①master服务器创建测试数据库：create database test2;
②master服务器创建表：create table testtable(id int);
③关闭slave服务器的主从复制：slave stop；
④client登录amoeba服务器：mysql -uamoeba -p123456 -h 192.168.11.44 -P8066
⑤client在test2库中给testtable插入数据：insert into test2.testtable values(1);
⑥client中查询testtable的数据，结果为空说明是在从服务器进行读取