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