MySQL主从复制与读写分离（详细部署案例）

一，主从复制了解：

1、为什么需要主从复制？

   
        在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

        做数据的热备

        架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

二，为什么使用主从复制、读写分离

        主从复制、读写分离一般是一起使用的。目的很简单，就是为了提高数据库的并发性能。你想，假设是单机，读写都在一台MySQL上面完成，性能肯定不高。如果有三台MySQL，一台mater只负责写操作，两台salve只负责读操作，性能不就能大大提高了吗？

三，mysql主从复制原理

        mysql的主从复制和MySQL的读写分离两者有着紧密联系，首选要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离。

1.mysql支持的复制类型

        基于语句的复制。在主服务器上执行的SQL语句，在从服务器上执行同样的语句。mysql默认采用基于语句的复制，效率比较高。

        基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。

        混合类型复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

2.复制的工作过程

      

3.mysql读写分离原理

常见的mysql读写分离分为两种：

一.基于程序代码内部实现：
         在代码中根据select，insert进行路由分类，这类方法也是目前生产环境应用最广泛的。优点是性能较好，因为在程序代码中实现，不需要增加额外的设备作为硬件开支；缺点是需要开发人员来实现，运维人员无从下手。

二.基于中间代理层实现

        代理一般位于客户端和服务器之间，代理服务器接到客户端的请求后通过判断后转发到后端数据库，有两个代表性程序。

        1.mysql-proxy。mysql-proxy为MySQL开源项目，通过其自带的lua脚本进行SQL判断，虽然是MySQL官方产品，但是mysql官方不建议将mysql-proxy用到生产环境中。

        2.amoeba（变形虫）。由陈思儒开发，其曾就职于阿里巴巴。该程序用于Java语言进行开发，阿里巴巴将其用于生产环境中。它不支持事务和存储过程。

二，搭建MySQL主从复制

注：在部署环境前请先部署好三台数据库，如有初学者可以查看前面的LAMP部署的mysql部署安装部分。

1.建立时间同步，在主部署NTP，在从服务器上进行时间同步。

[root@localhost ~]# yum -y install ntp
[root@localhost ~]# vim /etc/ntp.conf 
server 127.127.1.0
fudge 127.127.1.0 startum 8

[root@localhost ~]# systemctl restart ntpd
[root@localhost ~]# systemctl enable ntpd
Created symlink from /etc/systemd/system/multi-user.target.wants/ntpd.service to /usr/lib/systemd/system/ntpd.service.

        在每一台服务器上关闭firewalld或者指定端口，服务进行开放

[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
Removed symlink /etc/systemd/system/basic.target.wants/firewalld.service.

        在节点上进行时间同步

[root@localhost ~]# yum -y install ntpdate
[root@localhost ~]# ntpdate 192.168.2.1
 4 Sep 15:23:06 ntpdate[81050]: adjust time server 192.168.2.1 offset -0.000618 sec

2.配置MySQL master主服务器 

        在/etc/my.cnf中修改或者增加下面内容，重启服务。

[root@localhost ~]# vim /etc/my.cnf
server_id = 11        #指定id每一台MySQL服务器禁止相同
log_bin = master-bin        #开启master二进制文件
log-slave-updates = true    #开启slave从服务更新二进制文件


[root@localhost ~]# systemctl restart mysqld

        在登录MySQL程序，给从服务器以授权。

mysql> grant replication slave on *.* to 'myslave'@'192.168.2.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      410 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

 3.配置从服务器，两台节点配置相同，注意server_id不要相同。

         在/etc/my.cnf中修改或者增加下面内容，重启MySQL服务器

[root@localhost ~]# vim /etc/my.cnf
server_id = 22
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index

[root@localhost ~]# systemctl restart mysqld

        登录MySQL，配置同步，查看  Slave_IO_Running: Yes， Slave_SQL_Running: Yes两项是否为Yes。

[root@localhost ~]# mysql -u root -p
Enter password: 
mysql> change master to master_host='192.168.2.1',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=410;

Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.1
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 410
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 284
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 

4.验证主从复制效果。

         在主，从服务器上登录MySQL， 在主服务器上新建数据库db_test

mysql> create database db_test;
Query OK, 1 row affected (0.00 sec)

        在主，从服务器上分别查看数据库，显示数据库相同，则主从复制成功。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_test            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)

三，搭建MySQL读写分离 

1.在主服务器上部署amoeba上安装Java环境

注：因为amoeba是基于jbk开发的，所以官方团建使用jdk1.5或1.6版本，高版本不建议使用。

[root@localhost ~]# chmod +x jdk-6u14-linux-x64.bin
[root@localhost ~]# ./jdk-6u14-linux-x64.bin
[root@localhost ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6
[root@localhost ~]# vim /etc/profile
export  JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba/
export PATH=$PATH:$AMOEBA_HOME/bin
[root@localhost ~]# source /etc/profile
[root@localhost ~]# java -version
java version "1.6.0_14"
Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)

        java环境已配置成功

        安装并配置Amoeba软件。

[root@localhost ~]# mkdir /usr/local/amoeba
[root@localhost ~]# tar zxf /mnt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@localhost ~]# chmod -R 755 /usr/local/amoeba/
[root@localhost ~]# /usr/local/amoeba/bin/amoeba
amoeba start|stop

        配置Amoeba读写分离，两个slave读负载均衡。

        在master，slave1，slave2中开放权限给Amoeba访问

mysql> grant all on *.* to test@'192.168.2.%' identified by '123.com';
Query OK, 0 rows affected (0.00 sec)

        编辑amoeba.xml配置文件，设置客户端登录用户名和密码。

[root@localhost ~]# cd /usr/local/amoeba/
[root@localhost amoeba]# vim conf/amoeba.xml 
                        <property name="authenticator">
                                <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">

                                        <property name="user">amoeba</property>

                                        <property name="password">123456</property>

                                        <property name="filter">
                                                <bean class="com.meidusa.amoeba.server.IPAccessController">
                                                        <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
                                                </bean>
                                        </property>
                                </bean>
                        </property>

                指定写入数据库master，指定读取使用slaves组。

 <queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
                <property name="ruleLoader">
                        <bean class="com.meidusa.amoeba.route.TableRuleFileLoader">
                                <property name="ruleFile">${amoeba.home}/conf/rule.xml</property>
                                <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>
                        </bean>
                </property>
                <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
                <property name="LRUMapSize">1500</property>
                <property name="defaultPool">master</property>


                <property name="writePool">master</property>
                <property name="readPool">slaves</property>

                <property name="needParse">true</property>
        </queryRouter>

        编辑dbServer.xml配置文件，指定刚刚授权的用户和密码，来获取信息。

[root@localhost amoeba]# vim conf/dbServers.xml 

<!-- mysql user -->
                        <property name="user">test</property>

                        <property name="password">123.com</property>

        指定mysql数据库的地址，和slave组的服务器地址。

     <dbServer name="master"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">192.168.2.1</property>
                </factoryConfig>
        </dbServer>
        <dbServer name="slave1"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">192.168.2.2</property>
                </factoryConfig>
        </dbServer>

        <dbServer name="slave2"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">192.168.2.3</property>
                </factoryConfig>
        </dbServer>

        <dbServer name="slaves" virtual="true">
                <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
                        <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
                        <property name="loadbalance">1</property>

                        <!-- Separated by commas,such as: server1,server2,server1 -->
                        <property name="poolNames">slave1,slave2</property>
                </poolConfig>
        </dbServer>

        配置无误后，可以启动amoeba软件，其默认端口为tcp 8066

[root@localhost amoeba]# bin/amoeba start&
[1] 33254
[root@localhost amoeba]# netstat -napt | grep java
tcp6       0      0 127.0.0.1:37645         :::*                    LISTEN      33254/java          
tcp6       0      0 :::8066                 :::*                    LISTEN      33254/java          
tcp6       0      0 192.168.2.1:52608       192.168.2.3:3306        ESTABLISHED 33254/java          
tcp6       0      0 192.168.2.1:56320       192.168.2.1:3306        ESTABLISHED 33254/java          
tcp6       0      0 192.168.2.1:45090       192.168.2.2:3306        ESTABLISHED 33254/java     

        测试。 

        在client主机上安装mariadb数据库

yum -y install mariadb

        通过代理访问MySQL数据库：

[root@localhost ~]# mysql -u amoeba -p123456 -h 192.168.2.1 -P8066
mysql> use db_test;
Database changed
mysql> create table name_table (id int(10),name varchar(10),address varchar(20));
Query OK, 0 rows affected (0.09 sec)

        分别在两台从服务器上，关闭slave复制

mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)

        从服务器上同步了表，手动插入其他内容。

salve1：
mysql> use db_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into name_table values ('2','li','244');
Query OK, 1 row affected (0.01 sec)

salve2：
mysql> use db_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> insert into name_table values ('1','zhang','233');
Query OK, 1 row affected (0.02 sec)

        在client主机上第一次和第二次查看结果:

mysql> select * from name_table;
+------+------+---------+
| id   | name | address |
+------+------+---------+
|    2 | li   | 244     |
+------+------+---------+
1 row in set (0.01 sec)

mysql> select * from name_table;
+------+-------+---------+
| id   | name  | address |
+------+-------+---------+
|    1 | zhang | 233     |
+------+-------+---------+
1 row in set (0.00 sec)

        测试写入操作，在client主机上插入一条语句：

mysql> insert into name_table values('3','wang','255');
Query OK, 1 row affected (0.05 sec)

        但是在cilent上查询不到，只有master上才能查看到这条语句内容，说明写操作在master服务器上。

mysql> select * from name_table;
+------+------+---------+
| id   | name | address |
+------+------+---------+
|    3 | wang | 255     |
+------+------+---------+
1 row in set (0.00 sec)

由此验证，以及实现了MySQL读写分离，目前所有的写操作都全部在master（主服务器上），用来避免数据的不同步；所有读数据都分摊给了slave（从服务器） ，用来分担数据库压力。