主从复制搭建-读写分离mysql

使用mycat实现读写分离要在已经实现了主从复制的条件下执行

WINDOWS下Mysql搭建主从服务器


目录

WINDOWS下Mysql搭建主从服务器

要求

主库配置

为从库创建账户

从服务器配置

修改同步用户数据

排错

检验

半同步复制

宕机处理





[要求]
数据库要求版本一致
mysql5.5以上版本
在my.ini 文件中找到[mysqld]

（如果没有my.ini 请自行创建文件或将my_default.ini 重命名为 my.ini）

[主库配置]
### 主库配置
 
#给服务器取一个独特的ID
server-id=1
 
#申明二进制日志的文件为mysql-bin.xxxx
log-bin=mysql-bin
 
#二进制文件类型 mixed 混合  statement 记录执行语句  row 记录磁盘变化
binlog-format=mixed

# 待同步的数据库

binlog-do-db=数据库名


并重启mysql 服务

net stop mysql
net start mysql
确认主库的server-id,在主库执行

SHOW VARIABLES LIKE 'server_id';
mysql> SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)
继续执行如下代码，查看同步库的状态

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

每次修改主服务器my.ini File参数和Position 都会改变，从服务器可能需要重新配置，具体参考后面。

[为从库创建账户]
执行如下代码：

GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO  user@'192.168.1.102' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO  user@'192.168.1.102' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
注意：

user为用户名，192.168.1.102为从库IP，多个从服务器为安全起见，请增加多个！也可配置为132.168.1.% 局域网内均可连接

password 为密码;配置完成请校验从服务器是否可以连接主服务器。

执行如下代码：

use mysql;
select host,user,password from user;
mysql> select host,user,password from user;
+----------------+-------+-------------------------------------------+
| host           | user  | password                                  |
+----------------+-------+-------------------------------------------+
| %              | root  | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| 127.0.0.1      | root  |                                           |
| ::1            | root  |                                           |
| localhost      |       |                                           |
| 192.168.1.102  | user | *1B44DF3B7924252ED1F3CF2DAB1CD12E06EB47DD |
+----------------+-------+-------------------------------------------+
7 rows in set (0.00 sec)
至此！主库配置完成！！！

呱唧呱唧！！！

[从服务器配置]
my.ini 添加如下代码：

### 从库配置
 
#给服务器取一个独特的ID
server-id=2
 
#申明二进制日志的文件为mysql-bin.xxxx
log-bin=mysql-bin
 
#打开从服务器的relay日志
relay-log=mysql-relay
 
#二进制文件类型 mixed 混合  statement 记录执行语句  row 记录磁盘变化
binlog-format=mixed
 # 待同步的数据库

replicate-do-db=数据库名   #注意这里和主库不一样
并重启mysql 服务

net stop mysql
net start mysql
[修改同步用户数据]
停止从库的slave，方便进一步修改

stop slave;
在从库执行如下代码：

CHANGE MASTER TO MASTER_HOST='192.168.1.101', #主服务器IP
MASTER_PORT=3306, #主服务器端口
MASTER_USER='user1', #主服务器用户名
MASTER_PASSWORD='userpwd', #主服务器用户密码
MASTER_LOG_FILE='mysql-bin.000001', #日志文件名，获取方法往下看
MASTER_LOG_POS=2235; #也从下面的方法获取
 
[获取上面配置的数据]
在主库上执行如下代码：

show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     2235 | test         | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
所以，看清楚了吧，为什么刚开始特别说修改my.ini File和Position的值会变，因为这儿要用得到啊。不然会出一些问题~

File的值就是MASTER_LOG_FILE的值，Position的值就是MASTER_LOG_POS的值。

复制执行就好了。

输入如下代码，启动SLAVE进程：

start slave;
检查是否可以打通

执行如下代码：

show  slave status\G
末尾不加分号

如果你是sqlyog或者其他的图形界面的话也可以执行如下代码：

show  slave status;
mysql> show  slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.102
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2440
               Relay_Log_File: WIN-27C9C1NUFHU-relay-bin.000003
                Relay_Log_Pos: 1960
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes     重点看这里，必须两个都是Yes
            Slave_SQL_Running: Yes     重点看这里，必须两个都是Yes
              Replicate_Do_DB: 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: 2440
              Relay_Log_Space: 2949
              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
                  Master_UUID: 683c15c0-b656-11e8-8284-00259030014d
             Master_Info_File: D:\mysql-5.6.32-winx64\data\master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the sla
ve I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)
提示：

同一台window服务器不能设置多个mysql从库，会造成 Slave_IO_Running 互相踢下去的情况。

同一台服务器设置一主一从虽然不会互踢进程，但实测仍无法完成同步。

[排错]
如果Slave_IO_Running不是Yes

请检查 MASTER_LOG_FILE 的值是否正确，就是上面强调的那个修改ini会改变的那两个家伙。

如果Slave_SQL_Running不是Yes

执行如下代码,记住，要分别一条一条执行。

mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;
可能执行一次没反应，多执行几次看看。

直到Slave_IO_Running Slave_SQL_Running 的值都为Yes，说明同步配置完成。

如果Slave_IO_Running一直为连接中，请检查账号是否具有权限或防火墙端口是否放开

[检验]
在主库上创建个表，写点数据，看看从库上有没有。

此时，数据库已经自己同步了。

搞定了！

:)
如果没有的话，就自己百度吧。

以上为异步mysql集群搭建过程，但在操作事务时候容易造成事务丢失。所以在mysql5.5引入了版同步复制来解决问题。

半同步复制参考如下：

半同步复制
首先判断MySQL服务器是否支持动态增加插件

执行如下代码

select @@have_dynamic_loading;
mysql> select @@have_dynamic_loading;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES                    |
+------------------------+
1 row in set (0.00 sec)
确认mysql安装目录下面是否有下面两个文件

/lib/plugin/semisync_master.dll

/lib/plugin/semisync_slave.dll

在主库上执行如下代码

install plugin rpl_semi_sync_master SONAME 'semisync_master.dll';
mysql> install plugin rpl_semi_sync_master SONAME 'semisync_master.dll';
Query OK, 0 rows affected (0.04 sec)
在从库上执行如下代码

install plugin rpl_semi_sync_slave SONAME 'semisync_slave.dll';
mysql> install plugin rpl_semi_sync_slave SONAME 'semisync_slave.dll';
Query OK, 0 rows affected (0.03 sec)
检查安装状态

mysql> select * from mysql.plugin;
+---------------------+--------------------+
| name                | dl                 |
+---------------------+--------------------+
| rpl_semi_sync_slave | semisync_slave.dll |
+---------------------+--------------------+
1 row in set (0.04 sec)
在主库和从库上配置参数打开半同步semi-sync

主库：
mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.04 sec)
mysql> set global rpl_semi_sync_master_timeout=30000;
Query OK, 0 rows affected (0.04 sec)
从库
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.04 sec)
重启I/O线程

mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.05 sec)
 
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.04 sec)
配置完成了,再给自己打个气吧~

:)
宕机处理
从库宕机后，首先重启从库

#### 获取到File值和Position的值
 
```mysql
#暂停主库数据更新操作
#注意！！！此操作会阻塞著数据库的所有更新操作
FLUSH TABLES WITH READ LOCK;
 
#SHOW出日志名和偏移量
SHOW MASTER STATUS；
 
#强制从库进行同步
select MASTER_POS_WAIT('mysql-bin.000001','974');
 
#释放主库
UNLOCK TABLES;

————————————————
版权声明：本文为CSDN博主「xirigh」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/xirigh/article/details/82712556

 

 

window下使用MyCat实现简单的读写分离
目录

参考文档
MyCat安装
MyCat目录介绍
前置
配置信息
预期效果
 

回到顶部
参考文档
MyCat权威指南
MyCat项目主页
学会数据库读写分离、分表分库——用Mycat，这一篇就够了！

回到顶部
MyCat安装
Java SDK下载（必须JDK7或更高版本）
MYSQL下载 （MyCat支持多种数据库接入 如：MYSQL、SQLService、Oracle、MongDB等）
MyCat下载（选择稳定版且适合自己系统的压缩包）

回到顶部
MyCat目录介绍
目录	说明
bin	封装成服务的命令
catlet	mycat扩展功能
conf	配置信息
lib	引用的jar包
log	日志文件，包括mycat启动和运行日志
其中着重说明下conf目录下的配置文件信息

文件	说明
server.xml	定义用户以及系统相关变量
schema.xml	定义逻辑库，表，分片节点
rule.xml	定义分片规则
回到顶部
前置
服务器	IP	说明
MyCat	127.0.0.1	mycat服务器
MYSQL DB 1	127.0.0.1:3306	mysql数据库
MYSQL DB 2	127.0.0.1:3307	mysql数据库
mycat服务端口8806，可以通过shell或者sql管理工具来直接连接。
mycat管理端口9906，连接后可以根据命令查看mycat的运行情况。可以使用mycat-web来配置一个可视化的UI管理后台。
DB1和DB2先建立主从同步功能，DB1为主DB2为从，具体配置请参考上一节随笔。

回到顶部
配置信息
server.xml

<!-- 用户节点配置 -->
<!-- name：连接mycat的用户名 -->
<user name="root" defaultAccount="true">
    <!-- password：连接mycat的密码 -->
    <property name="password">123456</property>
    <!-- 数据库名，与schema.xml中的配置关联，多个用逗号分开 -->
    <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>
schema.xml

<!-- 数据库配置，与server.xml中的数据库对应 -->
<!-- 
    name：逻辑数据库名，与server.xml中的schema对应
    checkSQLschema：数据库前缀相关设置
    sqlMaxLimit：查询时默认limit，避免全表查询
-->
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<!-- 分片配置 -->
<dataNode name="dn1" dataHost="localhost1" database="db01" />
<!-- 物理数据库配置 -->
<!--
    minCon：指定每个读写实例连接池的最小连接，初始化连接池的大小
    maxCon：指定每个读写实例连接池的最大连接。也就是说，标签内嵌套的 writeHost、readHost 标签都会使用这个属性的值来实例化出连接池的最大连接数。
    dbDriver：指定连接后端数据库使用的 Driver，目前可选的值有 native 和 JDBC。从 1.6 版本开始支持 postgresql 的 native 原始协议。
    balance：均衡负载方式
        0：不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上
        1：全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡
        2：所有读操作都随机的在 writeHost、readhost 上分发
        3：所有读请求随机的分发到 wiriterHost 对应的 readhost 执行，writerHost 不负担读压力
    writeType：均衡负载方式
        0： 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .
        1：所有写操作都随机的发送到配置的 writeHost，1.5 以后废弃不推荐
    switchType：切换方式
        -1： 表示不自动切换
        1：默认值，自动切换
        2：基于 MySQL 主从同步的状态决定是否切换
        3：基于 MySQL galary cluster 的切换机制（适合集群），心跳语句为 show status like ‘wsrep%’.
-->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
          writeType="0" dbType="mysql" dbDriver="native" switchType="0"  slaveThreshold="100">
    <!--心跳检测-->
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostMaster" url="127.0.0.1:3306" user="root"
               password="123456">
        <readHost host="hostSlave" url="127.0.0.1:3307" user="root" password="123456" />
    </writeHost>
</dataHost>
回到顶部
预期效果
开启MYSQL3306和3307服务，启动MyCat（mycat安装目录bin下，点击startup_nowrap.bat即可），使用Navicat Premium可视化管理工具，连接三者。在mycat服务端下，为表CRUD时，可看到相应的效果。
image
在查询时，查看mycat运行日志mycat
.log可以看到连接的是配置的读数据库3307。

INFO [$_NIOREACTOR-3-RW] (io.mycat.backend.mysql.nio.handler.NewConnectionRespHandler.connectionAcquired(NewConnectionRespHandler.java:44)) - connectionAcquired MySQLConnection [id=17, lastTime=1526867502288, user=root, schema=db01, old shema=db01, borrowed=true, fromSlaveDB=true, threadId=34, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=127.0.0.1, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
非查询语句时则操作的是数据库3306。

可使用@@port来追踪操作的是哪个库
如果您认为这篇文章还不错或者有所收获，您可以点击右下角的【推荐】按钮【精神支持】，因为这种支持是我继续写作，分享的最大动力！
作者：warren
来源：http://wuchen1314.cnblogs.com/