Mysql架构学习

Mysql架构！！

单一架构

主从复制架构

核心解决的是数据的冗余备份问题

从节点监听主节点的log文件进行同步

架构搭建

1.集群规划
192.168.136.129  主节点
192.168.136.130  从节点
---
2.编辑配置
vim /etc/my.cnf
加入下面四句
#唯一标识  每个节点需要自己修改 不能重复 server-id 1..2..3..
#文件名前缀
#主节点有变化从节点执行
#从节点跳过错误的sql语句
server-id=1
log-bin=mysql-bin
log-slave-updates
slave-skip-errors=all
---
3.登录mysql查看是否生效
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
---
4.配置
主节点: show master status;
mysql> show master status;
+------------------+----------+
| File             | Position | 
+------------------+----------+
| mysql-bin.000001 |      155 | 
+------------------+----------+
记住这个155  从155行开始复制

从节点:
change master to
		master_host='192.168.136.129',
		master_user='root',
		master_password='数据库密码',
		master_log_file='mysql-bin.000001',
		master_log_pos=155;

5.开启
开启从结点: start slave;
关闭同步	stop slave;

查看从节点状态:
show slave status\G;    \G是为了格式化结果，不然太乱了看不了

Slave_IO_Running: No
Slave_SQL_Running: Yes
必须都为yes才是成功
由于是克隆的 两台MySQL的uuid相同  需要关闭服务删掉下面这个文件 之后重启服务 重新创建
 rm -rf /var/lib/mysql/auto.cnf 

读写分离架构

解决单节点压力过大

mycat默认端口8066

架构搭建

mycat

http://mycat.org.cn/about.html

下载解压

tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/
---
在/usr/mycat/conf下
vim schema.xml 

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">


        <!--定义MyCat的逻辑库 dataNode映射真实的数据节点-->
        <schema name="test_schema" checkSQLschema="false" sqlMaxLimit="100" dataNode="zaNode"></schema>
        <!--定义Mycat的数据节点 name的值要和dataNode值一致  dataHost:映射真实的主机  database真实的库-->
        <dataNode name="zaNode" dataHost="zaHost" database="test"/>

        <!--定义数据主机 name要与dataHost保持一致-->
        <dataHost name="zaHost" maxCon="1000" minCon="10" balance="1"
                        writeType="0" dbType="mysql" dbDriver="native" switchType="-1" slaveThreshold="100">
                <!--心跳检测-->
                <heartbeat>select user()</heartbeat>
                <!--写节点-->
                <writeHost host="hostM1" url="192.168.136.129:3306" user="root" password="za0813!">
                        <!--读节点-->
                        <readHost host="hostS1" url="192.168.136.130:3306" user="root" password="za0813!"/>
                </writeHost>
        </dataHost>


</mycat:schema>

 vim server.xml 

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
        - you may not use this file except in compliance with the License. - You 
        may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
        - - Unless required by applicable law or agreed to in writing, software - 
        distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
        WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
        License for the specific language governing permissions and - limitations 
        under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">

<system>
        <!--这里配置的都是一些系统属性-->
        <property name="defaultsqlParser">druidparser</property>
        <property name="charset">utf8mb4</property>
</system>

<user name="root">
        <property name="password">root</property>
        <property name="schemas">test_schema</property>
</user>

</mycat:server>

启动mycat

[root@localhost bin]# ./mycat
Usage: ./mycat { console | start | stop | restart | status | dump }
[root@localhost bin]# ./mycat console