数据库读写分离服务

读写分离服务器

准备工作：

主服务器：192.168.1.11
从服务器：192.168.1.12
mycat服务器：192.168.1.13
主从服务器需要安装mysql包，mycat服务器解压mycat压缩包

步骤：

步骤一：搭建主从同步结构

1）配置主服务器192.168.1.11

    ]# vim /etc/my.cnf
    [mysqld]
    server_id=11    //指定服务器ID号 
    log-bin=master11        //启用binlog日志，并指定文件名前缀
    ...
    [root@master10 ~]# systemctl restart mysqld        //重启mysqld

2）主服务器授权用户，查看binlog日志信息

    ]# mysql -uroot -p123456
    mysql> grant all on *.* to 'repluser'@'%' identified by '123qqq...A';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    mysql> show master status;
    +-----------------+----------+--------------+------------------+-------------------+
    | File      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +-----------------+----------+--------------+------------------+-------------------+
    | master11.000001 |      449 |              |                  |                   |
    +-----------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

3)配置从服务器192.168.1.12

]# vim /etc/my.cnf

[mysqld]
server_id=52 //指定服务器ID号，不要与Master的相同
:wq

]# systemctl restart mysqld

4）配置从服务192.168.1.12，指定主服务器的信息，日志文件、偏移位置（参考主服务器的状态输出）

    ]# mysql -uroot -p123qqq...A
    mysql> change master to master_host='192.168.4.11',
        -> master_user='repluser',
       -> master_password='123qqq...A',
        -> master_log_file='master11.000001',
        -> master_log_pos=449;
    Query OK, 0 rows affected, 2 warnings (0.01 sec)
   
    mysql> start slave;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show  slave status\G;  //IO线程和SQL线程都是YES状态才成功
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.4.51
                      Master_User: repluser
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: master51.000001
              Read_Master_Log_Pos: 738
                   Relay_Log_File: slave20-relay-bin.000002
                    Relay_Log_Pos: 319
            Relay_Master_Log_File: master51.000001
                 Slave_IO_Running: Yes        //IO线程YES
                Slave_SQL_Running: Yes        //SQL线程YES
......
    1 row in set (0.00 sec)

5)测试配置，在主服务器上创建一个数据库aa

    ]# mysql –uroot –p123qqq...A
    mysql> create database aa;
    Query OK, 1 row affected (0.00 sec)
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | aa                 |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)

6）从服务器上查看，有aa库

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | aa                 |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)

步骤二：部署mycat服务
1）安装java-1.8.0-openjdk-devel包

]# yum -y install java-1.8.0-openjdk //安装JDK

2）安装提供服务的软件包

[root@mycat56 ~]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz //解压源码
[root@mycat ~]# cp -r mycat /usr/local/

3）在主服务器上授权admin用户

mysql > grant all on *.* to admin@"%" identified by "123qqq...A";

4)在从服务器上授权读用户

mysql> create user 'read'@'%' IDENTIFIED BY '123qqq...A';
mysql> grant select on *.* to 'read'@'%';
mysql> select user,host from mysql.user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| admin     | %         |
| read      | %         |
| mysql.sys | localhost |
| root      | localhost |
+-----------+-----------+
4 rows in set (0.00 sec)

5）修改配置文件schema.xml文件

//删除schema之间的所有的信息，添加dataNode=“dn1”
//修改balance=“3”，
//添加写主机的ip地址，授权用户，密码
//添加读主机的ip地址，授权用户，密码
//删除多余的其他信息
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">	
        </schema>
         <dataNode name="dn1" dataHost="localhost1" database="db1" />
         
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"

                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->

                <writeHost host="hostmaster1" url="192.168.1.11:3306" user="admin"
                                   password="123qqq...A">

                        <!-- can have multi read hosts -->
                        <readHost host="slavehostS2" url="192.168.1.12:3306" user="read" password="123qqq...A" />
                </writeHost>
        </dataHost>
</mycat:schema>

6）启动服务

[root@mycat conf]# /usr/local/mycat/bin/mycat start

7）查看端口号

[root@mycat conf]# ss -antlp | grep 8066
LISTEN     0      100         :::8066                    :::*                   users:(("java",pid=18583,fd=78))

步骤三：测试mycat读写分离
1）登录到读写分离服务器的数据库

[root@mycat conf]# mysql -h192.168.1.13  -P8066 -uroot  -p123456
MySQL [TESTDB]> select @@hostname;
+------------+
| @@hostname |
+------------+
| slave      |
+------------+

创建数据库

MySQL [TESTDB]> create database db2;

在主从数据库都能查到数据库db2，所以实现了读操作是读取从服务器中数据库信息，而写操作是在主服务器的数据库操作。