- 主从复制
MySQL之间数据复制的基础是二进制日志文件(binary log file)。一台MySQL数据库一旦启用二进制日志后,其作为master,它的数据库中所有操作都会以“事件”的方式记录在二进制日志中,其他数据库作为slave通过一个I/O线程与主服务器保持通信,并监控master的二进制日志文件的变化,如果发现master二进制日志文件发生变化,则会把变化复制到自己的中继日志中,然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中,以此实现从数据库和主数据库的一致性,也就实现了主从复制。
原理: 其依赖于二进制日志,binary-log, 二进制日志中记录引起数据库发生改变的语句 Insert 、delete、update、create table - linux中MYSQL主从复制环境搭建
准备2台mysql服务器192.168.2.122(master主)、192.169.2.123(slave备)
- 主服务----登录122主服务器,修改配置
server_id=122 ###服务器id,不可重复(同一个局域网),可自定义,一般设为ip的末尾// 修改/etc/my.cnf文件 vi /etc/my.cnf ####################新增如下配置################### server_id=122 log-bin=mysql-bin // 保存,退出
log-bin=mysql-bin ###开启日志文件
查看配置的内容
保存后,重启mysql服务: systemctl restart mysqld - 主服务---验证122主服务器配置是否成功
使用mysql客户端链接工具navicat, 执行下面2条查询语句// 查询server_id show variables like '%server_id%'; // 查询主服务的二进制执行文件 show master status;
- 从服务----登录123从服务器,修改配置
// 修改从服务123的my.cnf配置 vi /etc/my.cnf ##############新增如下配置#################### server_id=123 log-bin=mysql-bin binlog_do_db=test // 保存,退出
server_id=123 ###服务id,不可重复
log-bin=mysql-bin ###开启日志文件
binlog_do_db=test ###需要同步的数据库, test数据库查看配置内容
保存后,重启mysql服务: systemctl restart mysqld - 从服务---验证123从服务器配置是否成功
使用mysql客户端链接工具navicat, 执行下面查询语句// 查询server_id show variables like '%server_id%';
- 从服务器配置同步数据
使用mysql客户端链接工具navicat, 链接192.168.123, 执行下面sql语句
master_host: 主服务的IP, 默认端口可不填###配置同步数据 change master to master_host='192.168.2.122',master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154; ###开启同步 start slave; ###查询同步状态 SHOW SLAVE STATUS;
master_user:主服务的账号
master_password: 主服务的账号密码
master_log_file: 在主服务使用命令: show master status; 查询的'File'的值
master_log_pos:在主服务使用命令: show master status; 查询的'Position'的值
查询同步状态看到,下面2个必须都是Yes才表示成功,我们继续查看错误日志
错误信息: 因为服务器克隆的时候交UUID产生了重复 ,解决办法
再次查看都为yes// 查看123从服务的/etc/my.cnf cat /etc/my.cnf // 找到datadir=/var/lib/mysql配置的目录 cd /var/lib/mysql // 删除 /var/lib/mysql下的auto.cnf文件 rm -rf auto.cnf // 重启服务 systemctl restart mysqld
- 测试
使用navicat链接,在主服务122上创建'test' , 在test数据库下创建tbl_user表,插入数据,
使用navicat链接,在从服务123上刷新,看看是否有库和表数据
主从复制搭建完成!!!
- 读写分离
- 什么是读写分离
在数据库集群架构中,让主库负责处理事务性查询,而从库只负责处理select查询,让两者分工明确达到提高数据库整体读写性能。当然,主数据库另外一个功能就是负责将事务性查询导致的数据变更同步到从库中,也就是写操作 - 读写分离优点
a. 分摊服务器压力,提高机器的系统处理效率,如果有一台服务器,当select很多时,update和delete会被这些select访问中的数据堵塞,等待select结束,并发性能并不高,而主从只负责各自的写和读,极大程度的缓解X锁和S锁争用;
b. 增加冗余,提高服务可用性,当一台数据库服务器宕机后可以调整另外一台从库以最快速度恢复服务
- mycat
- 什么是mycat
MyCAT是一款由阿里Cobar演变而来的用于支持数据库,读写分离、分表分库的分布式中间件。MyCAT支持Oracle、MSSQL、MYSQL、PG、DB2关系型数据库,同时也支持MongoDB等非关系型数据库。
MyCAT原理MyCAT主要是通过对SQL的拦截,然后经过一定规则的分片解析、路由分析、读写分离分析、缓存分析等,然后将SQL发给后端真实的数据块,并将返回的结果做适当处理返回给客户端。
其核心功能是分表分库,即将一个大表水平分割为N个小表,存储在后端MySQL服务器里或者其他数据库里
官方网站:http://www.mycat.io/
- MyCat实现读写分离
环境准备: 准备3台服务器, 提前关闭防火墙
192.168.2.122(主)、192.168.2.123(从) 将122和123提前搭建好mysql主从复制,上面有讲到,同步的数据库为test
192.168.2.124服务用来搭建和配置mycat
- 安装mycat
这里mycat安装完成!!!......下面的步骤都是配置读写分离// 登录192.168.2.124, 进入目录 cd /usr/local/ // 下载mycat wget http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz // 解压 tar -zxvf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz // 进入mycat目录 cd /usr/local/mycat
- 准备2个配置文件schema.xml和server.xml,内容如下
schema.xml 配置真实数据库信息:<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <!-- name="mycat_testdb"是mycat虚拟出的数据库,可自定义,客户端需要链接mycat虚拟数据库 dataNode节点中各属性说明:须对应 name:指定逻辑数据节点名称; dataHost:指定逻辑数据节点物理主机节点名称;须与下面对应 database="test" 指定真实的test数据库,如果一个节点上有多个库,可使用表达式db$0-99, 表示指定0-99这100个数据库; --> <schema name="mycat_testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema> <dataNode name="dn1" dataHost="localhost1" database="test" /> <!-- dataHost 节点中各属性说明: name:物理主机节点名称; maxCon:指定物理主机服务最大支持1000个连接; minCon:指定物理主机服务最小保持10个连接; writeType:指定写入类型; 0,只在writeHost节点写入; 1,在所有节点都写入。慎重开启,多节点写入顺序为默认写入根据配置顺序,第一个挂掉切换另一个; dbType:指定数据库类型; dbDriver:指定数据库驱动; balance:指定物理主机服务的负载模式。 0,不开启读写分离机制; 1,全部的readHost与stand by writeHost参与select语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与 M2互为主备),正常情况下,M2,S1,S2都参与select语句的负载均衡; 2,所有的readHost与writeHost都参与select语句的负载均衡,也就是说,当系统的写操作压力不大的情况下,所有主机都可以承担负载均衡; --> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- 可以配置多个主从 真实的连接账号和密码--> <writeHost host="hostM1" url="192.168.2.122:3306" user="root" password="123456"> <!-- 可以配置多个从库 --> <readHost host="hostS2" url="192.168.2.123:3306" user="root" password="123456" /> </writeHost> </dataHost> </mycat:schema>
server.xml 配置账号的权限:
注意: 在schema.xml 文件中 mycat_testdb是mycat虚拟的数据库,可自定义,客户端可通过192.168.2.124:8066端口链接mycat, 8066是mycat的虚拟库默认端口........<?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/"> <!-- 读写都可用的用户和密码 自定义账号和密码 账号: root 密码123456 ,使用此账号有读写权限 mycat_testdb是schema.xml配置的mycat的虚拟数据库 --> <user name="root" defaultAccount="true"> <property name="password">123456</property> <property name="schemas">mycat_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 密码123456 ,使用此账号只有读权限 mycat_testdb是schema.xml配置的mycat的虚拟数据库 --> <user name="user"> <property name="password">123456</property> <property name="schemas">mycat_testdb</property> <property name="readOnly">true</property> </user> </mycat:server>
在 server.xml 中账户root 密码123456用户拥有读写权限,账户user 密码123456只有读权限
- 上传文件
将schema.xml和server.xml上传到192.168.2.124的/usr/local/mycat/conf目录,直接覆盖。可以先将/usr/local/mycat/conf
下的这2个文件备份下 - 启动mycat
// 进入bin目录 cd /usr/local/mycat/bin // 启动mycat ./mycat start
- 查看是否启动成功
如下出现successfully,表示启动成功// 进入日志目录 cd /usr/local/mycat/logs // 查看错误或启动成功信息 cat wrapper.log
- 验证是否成功-----使用Navicat链接mycat虚拟数据库
使用root登录,密码123456,端口号8066,该账户拥有读、写权限
使用user登录,密码123456,端口号8066,该账户拥有读权限
-
这里安装完成!!!
-
mycat的常用命令
// 进入bin目录 cd /usr/local/mycat/bin // 停止 ./mycat stop // 启动 ./mycat start // 重启 ./mycat restart // 查看启动是否成功,或启动失败信息 cd /usr/local/mycat/logs cat wrapper.log ###wrapper.log如果出现successfully 则启动成功
-
springboot整合动态数据源切换--读写分离
配置多个数据源,根据业务需求访问不同的数据,指定对应的策略:增加,删除,修改操作访问对应数据,查询访问对应数据,不同数据库做好的数据一致性的处理。
在Spring 2.0.1中引入了AbstractRoutingDataSource, 该类充当了DataSource的路由中介, 能有在运行时, 根据某种key值来动态切换到真正的DataSource上。
-
............................
-
数据库分表分库策略--mycat
http://www.mycat.io/document/mycat-definitive-guide.pdf
-
垂直拆分
垂直拆分就是根据不同的业务,分为不同的数据库,比如会员数据库、订单数据库、支付数据库等,垂直拆分在大型电商系统中用的非常常见。
优点:拆分后业务清晰,拆分规则明确,系统之间整合或扩展容易。
缺点:部分业务表无法join,只能通过接口方式解决,提高了系统复杂度存在分布式事务问题。 -
水平拆分
水平拆分是把同一个表拆到不同的数据库中,或将一张表拆分多个小表。是将表的数据做分类,而是按照某个字段的某种规则来分散到多个库之中,每个表中包含一部分数据。简单来说,我们可以将数据的水平切分理解为是按照数据行的切分,就是将表中 的某些行切分到一个数据库,而另外的某些行又切分到其他的数据库中,主要有分表,分库两种模式
优点: 该方式提高了系统的稳定性跟负载能力,
缺点:跨库join性能较差。
-
使用MyCat实现水平分片策略
-
MyCat支持10种分片策略
1)求模算法
2)分片枚举
3)范围约定
4)日期指定
5)固定分片hash算法
6)通配取模
7)ASCII码求模通配
8)编程指定
9)字符串拆分hash解析
详细: http://www.mycat.io/document/mycat-definitive-guide.pdf
求模算法
地区分表
-
mycat实现取模算法
在192.168.2.122上创建数据库user_db1、user_db2、user_db3,在3张库都创建user_info表
CREATE TABLE `user_info` ( `id` int(11) DEFAULT NULL, `name` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-
本地前准备3个配置文件
rule.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:rule SYSTEM "rule.dtd"> <mycat:rule xmlns:mycat="http://io.mycat/"> <tableRule name="role1"> <rule> <!--分片字段id--> <columns>id</columns> <algorithm>mod-long</algorithm> </rule> </tableRule> <function name="mod-long" class="io.mycat.route.function.PartitionByMod"> <!--指定分片数量,不可以被更改--> <property name="count">3</property> </function> </mycat:rule>
schema.xml 内容如下:<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <!-- TESTDB1 是mycat的逻辑库名称,链接需要用的 --> <schema name="mycat_testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> <table name="user_info" dataNode="dn1,dn2,dn3" rule="role1"/> </schema> <!-- database 是MySQL数据库的库名 --> <dataNode name="dn1" dataHost="localhost1" database="user_db1" /> <dataNode name="dn2" dataHost="localhost1" database="user_db2" /> <dataNode name="dn3" dataHost="localhost1" database="user_db3" /> <!-- dataNode节点中各属性说明: name:指定逻辑数据节点名称; dataHost:指定逻辑数据节点物理主机节点名称; database:指定物理主机节点上。如果一个节点上有多个库,可使用表达式db$0-99, 表示指定0-99这100个数据库; dataHost 节点中各属性说明: name:物理主机节点名称; maxCon:指定物理主机服务最大支持1000个连接; minCon:指定物理主机服务最小保持10个连接; writeType:指定写入类型; 0,只在writeHost节点写入; 1,在所有节点都写入。慎重开启,多节点写入顺序为默认写入根据配置顺序,第一个挂掉切换另一个; dbType:指定数据库类型; dbDriver:指定数据库驱动; balance:指定物理主机服务的负载模式。 0,不开启读写分离机制; 1,全部的readHost与stand by writeHost参与select语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与 M2互为主备),正常情况下,M2,S1,S2都参与select语句的负载均衡; 2,所有的readHost与writeHost都参与select语句的负载均衡,也就是说,当系统的写操作压力不大的情况下,所有主机都可以承担负载均衡; --> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- 可以配置多个主从 --> <writeHost host="hostM1" url="192.168.2.122:3306" user="root" password="123456"> <!-- 可以配置多个从库 --> <readHost host="hostS2" url="192.2.123.203:3306" user="root" password="123456" /> </writeHost> </dataHost> </mycat:schema>
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/"> <!-- 读写都可用的用户 --> <user name="root" defaultAccount="true"> <property name="password">123456</property> <property name="schemas">mycat_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">123456</property> <property name="schemas">mycat_testdb</property> <property name="readOnly">true</property> </user> </mycat:server>
登录之前安装mycat的192.168.124服务器,将3个文件上传到/usr/local/mycat/conf目录下,可以把之前配置的读写分离文件备份下
-
启动myca
./mycat start
-
使用Navicat链接192.168.124mycat虚拟数据库mycat_testdb
使用root登录,密码123456,端口号8066,该账户拥有读、写权限
在user_info插入如下数据
-
查看结果
user_db1库中user_info的结果:
user_db1库中user_info的结果:
user_db1库中user_info的结果:
-
完成!!!!
-
修改mycat日志默认级别
cd /usr/local/mycat/conf cat log4j2.xml // 默认级别info, 可修改成debug调试,查看mycat的执行语句
-
a
-
a
-
a