基于mysql+mycat搭建稳定高可用集群负载均衡主备复制读写分离操作

数据库性能优化普遍采用集群方式，oracle集群软硬件投入昂贵，今天花了一天时间搭建基于mysql的集群环境。

主要思路

简单说，实现mysql主备复制-->利用mycat实现负载均衡。

比较了常用的读写分离方式，推荐mycat，社区活跃，性能稳定。

测试环境

mysql版本：server version: 5.5.53，到官网可以下载windwos安装包。

注意：确保mysql版本为5.5以后，以前版本主备同步配置方式不同。

linux实现思路类似，修改my.cnf即可。

• a主mysql。192.168.110.1:3306, 用户root，密码root。操作系统：win7 x64，内存：4g
•               安装路径：c:\program files\mysql\mysql server 5.5\bin
• b备mysql。192.168.110.2:3306, 用户root，密码root。操作系统：win2003 x64，内存：1g
•               安装路径：c:\program files\mysql\mysql server 5.5\bin
• a主、b备的mysql中创建sync_test数据库
  

实现mysql主备复制

主要思路：a主mysql开启日志，b备mysql读取操作日志，同步执行。

一般为主备同步，主主同步不推荐使用。

配置a主mysql

1）修改my.ini。需要在log-bin="c:/program files/mysql/mysql server 5.5/log/mysql-bin.log"的相关位置创建log目录，以及mysql-bin.log文件。

[mysqld]
server-id=1 #主机标示，整数
port=3306  
log-bin="c:/program files/mysql/mysql server 5.5/log/mysql-bin.log" #确保此文件可写
read-only=0  #主机，读写都可以
binlog-do-db=sync_test  #需要备份数据库，多个写多行
binlog-ignore-db=mysql #不需要备份的数据库，多个写多行

2）允许mysql远程访问

#登录mysql console
进入%home%/bin，执行mysql -uroot -proot
#授权。允许root用户，从192.168.110.*的ip范围 远程访问a主mysql
mysql>grant all privileges on *.* to 'root'@'192.168.110.*' identified by 'root' with grant option;
#生效。该操作很重要！
mysql>flush privileges;

3）重启a主mysql数据库

进入%home%/bin，执行mysql -uroot -proot

mysql>net stop mysql;
mysql>net start mysql;

4）查看主mysql日志状态

mysql> show master status\g;
*************************** 1. row ***************************
      file: mysql-bin.000003
    position: 107
  binlog_do_db: sync_test
binlog_ignore_db: mysql
1 row in set (0.00 sec)

error:
no query specified

配置b备mysql

1）修改my.ini。需要在log-bin="c:/program files/mysql/mysql server 5.5/log/mysql-bin.log"的相关位置创建log目录，以及mysql-bin.log文件。

[mysqld]
# add for sycn test
server-id=2            #从机标识
log-bin="c:/program files/mysql/mysql server 5.5/log/mysql-bin.log" #确保此文件可写
#master-host="192.168.110.1" #主机ip
#master-user=root         #数据库访问用户名
#master-pass=root           #数据库访问密码
#master-port=3306     #主机端口
#master-connect-retry=60   #如果从服务器发现主服务器断掉，重新连接的时间差(秒)
replicate-do-db=sync_test   #只复制某个库
replicate-ignore-db=mysql   #不复制某个库

2）重启b备mysql数据库

进入%home%/bin，执行mysql -uroot -proot

mysql>net stop mysql;
mysql>net start mysql;

3）配置b备数据库的数据来源，核实高亮处的状态是否正常。

mysql>change master to master_host='192.168.110.1',master_port='3306',master_user='root',master_password='root';
mysql>slave start;
mysql>show slave status\g;
*************************** 1. row ***************************
        slave_io_state: waiting for master to send even
         master_host: 192.168.110.1
         master_user: root
         master_port: 3306
        connect_retry: 60
       master_log_file: mysql-bin.000003
     read_master_log_pos: 107
        relay_log_file: wjt-1c698d8a032-relay-bin.00001
        relay_log_pos: 253
    relay_master_log_file: mysql-bin.000003
       slave_io_running: yes
      slave_sql_running: yes
       replicate_do_db: sync_test
     replicate_ignore_db: mysql
      replicate_do_table:
    replicate_ignore_table:
   replicate_wild_do_table:
 replicate_wild_ignore_table:
          last_errno: 0
          last_error:
         skip_counter: 0
     exec_master_log_pos: 107
       relay_log_space: 565
       until_condition: none
        until_log_file:
        until_log_pos: 0
      master_ssl_allowed: no
      master_ssl_ca_file:
      master_ssl_ca_path:
       master_ssl_cert:
      master_ssl_cipher:
        master_ssl_key:
    seconds_behind_master: 0
master_ssl_verify_server_cert: no
        last_io_errno: 0
        last_io_error:
        last_sql_errno: 0
        last_sql_error:
 replicate_ignore_server_ids:
       master_server_id: 1
1 row in set (0.00 sec)
error:
no query specified

验证同步配置结果

• a主mysql：使用navicat工具，在sync_test库中创建sync_table表，并添加一些数据
• b备mysql：使用navicat工具，查看sync_test库，可以看到sync_table表和数据已被同步
  

实现读写分离

主要思路：使用mycat中间件，转发sql指令到后端mysql节点。mycat不负责数据库同步。

安装mycat

mycat是什么？可以认为它是一个数据库访问中间件，但更像f5、ngnix等产品，具备访问路由、多表分表分片操作等功能。总之很强大。

• 下载： ，本文使用的是：1.6-release
  
• 解压mycat-server-1.6-release-20161012170031-win.tar，到d:\dev-bin\mycat目录
  
• 确保java环境为jdk1.7以上，否则mycat将不支持

安装完毕

配置mycat

1）server.xml。配置访问用户及权限。修改高亮处信息，其中admin、user为访问mycat的用户，testdb为mycat虚拟的数据库，供上层应用访问。

<user name="admin">
    <property name="password">admin</property>
    <property name="schemas">testdb</property>
    <!-- 表级 dml 权限设置 -->
    <!--     
    <privileges check="false">
      <schema name="testdb" dml="0110" >
        <table name="tb01" dml="0000"></table>
        <table name="tb02" dml="1111"></table>
      </schema>
    </privileges>    
     -->
  </user>
  <user name="user">
    <property name="password">user</property>
    <property name="schemas">testdb</property>
    <property name="readonly">true</property>
  </user>

2）schema.xml。这部分不太好理解，精简了一下，主要分schema、datanode、datahost三个主要配置。

<scheme>节点定义了mycat的虚拟数据库为testdb， balance="1"：write操作路由到a机，读操作路由到b。

<?xml version="1.0"?>
<!doctype mycat:schema system "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
  <schema name="testdb" checksqlschema="false" sqlmaxlimit="100" datanode="dn1">
    <!-- 这里不配置，代表所有的表分片到dn1节点-->
  </schema>
  <datanode name="dn1" datahost="localhost1" database="sync_test" />
  <datahost name="localhost1" maxcon="1000" mincon="10" balance="1"
       writetype="0" dbtype="mysql" dbdriver="native" switchtype="1" slavethreshold="100">
    <heartbeat>select user()</heartbeat>
    <!-- can have multi write hosts -->
    <writehost host="hostm1" url="192.168.110.1:3306" user="root" password="root">
      <!-- can have multi read hosts -->
      <readhost host="hosts2" url="192.168.110.2:3306" user="root" password="root" />
    </writehost>
  </datahost>
</mycat:schema>

启动mycat

1）启动mycat

d:\dev-bin\mycat\bin>startup_nowrap.bat

后台信息如下：

d:\dev-bin\mycat\bin>startup_nowrap.bat
d:\dev-bin\mycat\bin>rem check java_home & java
d:\dev-bin\mycat\bin>set "java_cmd=c:\program files (x86)\java\jdk1.7.0_13/bin/java"
d:\dev-bin\mycat\bin>if "c:\program files (x86)\java\jdk1.7.0_13" == "" goto nojavahome
d:\dev-bin\mycat\bin>if exist "c:\program files (x86)\java\jdk1.7.0_13\bin\java.exe" goto mainentry
d:\dev-bin\mycat\bin>rem set home_dir
d:\dev-bin\mycat\bin>set "curr_dir=d:\dev-bin\mycat\bin"
d:\dev-bin\mycat\bin>cd ..
d:\dev-bin\mycat>set "mycat_home=d:\dev-bin\mycat"
d:\dev-bin\mycat>cd d:\dev-bin\mycat\bin
#如果启动失败，请修改d:\dev-bin\mycat\bin\startup_nowrap.bat文件中的以下参数。默认占用内存为2g
d:\dev-bin\mycat\bin>"c:\program files (x86)\java\jdk1.7.0_13/bin/java" -server -xms512m -xmx512m -xx:maxpermsize=64m  -xx:+aggressiveopts -xx:maxdirectmemorysize=768m -dmycat_home=d:\
p "..\conf;..\lib\*" io.mycat.mycatstartup

mycat server startup successfully. see logs in logs/mycat.log   #启动成功将看到如下信息。

注意：如日志中出现192.168.110.2 not connected 等信息，请允许b备mysql远程访问。

#登录mysql console
进入%home%/bin，执行mysql -uroot -proot
#授权。允许root用户，从192.168.110.*的ip范围 远程访问bmysql
mysql>grant all privileges on *.* to 'root'@'192.168.110.*' identified by 'root' with grant option;
#生效,该操作很重要！
mysql>flush privileges;

测试读写分离

验证是否同步

• 使用navicat连接mycat，操作方式和连接物理mysql库一致，用户admin，密码admin，端口8066
• 在testdb虚拟库中，创建新表test2，增加一些数据
• 查看a节点、b节点数据已同步

关闭b备mysql的同步，验证读写分离

mysql> slave stop;
query ok, 0 rows affected (0.00 sec)
mysql> show slave status\g;
*************************** 1. row ***************************
        slave_io_state:
         master_host: 192.168.110.1
         master_user: root
         master_port: 3306
        connect_retry: 60
       master_log_file: mysql-bin.000003
     read_master_log_pos: 478
        relay_log_file: wjt-1c698d8a032-relay-bin.00001
        relay_log_pos: 624
    relay_master_log_file: mysql-bin.000003
       slave_io_running: no
      slave_sql_running: no
       replicate_do_db: sync_test
     replicate_ignore_db: mysql
      replicate_do_table:
    replicate_ignore_table:
   replicate_wild_do_table:
 replicate_wild_ignore_table:
          last_errno: 0
          last_error:
         skip_counter: 0
     exec_master_log_pos: 478
       relay_log_space: 936
       until_condition: none
        until_log_file:
        until_log_pos: 0
      master_ssl_allowed: no
      master_ssl_ca_file:
      master_ssl_ca_path:
       master_ssl_cert:
      master_ssl_cipher:
        master_ssl_key:
    seconds_behind_master: null
master_ssl_verify_server_cert: no
        last_io_errno: 0
        last_io_error:
        last_sql_errno: 0
        last_sql_error:
 replicate_ignore_server_ids:
       master_server_id: 1
1 row in set (0.00 sec)
error:
no query specified

3）使用navicat连接mycat，操作方式和连接物理mysql库一致，用户admin，密码admin，端口8066

• 连接成功后，将看到testdb数据库和test数据表
• 在test表中添加一些数据，保存
• 执行select * from test查看test操作，将看到数据未更新

原因：mycat将查询sq路由到b，因此读取的结果集不一致。

最后，mycat使用可以参见官网的权威指南学习。双主双备架构待后续更新。

总结

以上所述是小编给大家介绍的基于mysql+mycat搭建稳定高可用集群负载均衡主备复制读写分离操作，希望对大家有所帮助