MySQL+MyCat分库分表 读写分离配置

一、 mysql+mycat分库分表

1 mycat简介

java编写的数据库中间件

mycat运行环境需要jdk.

mycat是中间件.运行在代码应用和mysql数据库之间的应用.

前身 : corba. 是阿里开发的数据库中间件.实现mysql数据库分库分表集群管理的中间件.曾经出现过重大事故. 二次开发,形成mycat.

使用mycat之后,编写的所有的sql语句,必须严格遵守sql标准规范.

insert into table_name(column_name) values(column_value);

使用mycat中间件后的结构图如下：

 

 

2 mycat术语简介

2.1 切分

逻辑上的切分. 在物理层面,是使用多库[database],多表[table]实现的切分.

2.1.1 纵向切分

把一个数据库切分成多个数据库,配置方便

只能实现两张表的表连接查询.

将一张表中的数据，分散到若干个database的同结构表中。多个表的数据的集合是当前表格的数据。

 

 

2.1.2 横向切分

把一个表切分成多个表,相比纵向切分配置麻烦

无法实现表连接查询.

将一张表的字段，分散到若干张表中，将若干表连接到一起，才是当前表的完整数据。

 

 

2.2 逻辑库

mycat中定义的database.是逻辑上存在的.但是物理上未必存在.

主要是针对纵向切分提供的概念.

访问mycat，就是将mycat当做mysql使用。

db数据库是mycat中定义的database。通过sql访问mycat中的db库的时候，对应的是mysql中的db1，db2，db3三个库。物理上的database是db1，db2，db3.逻辑上的database就是db。

 

 

2.3 逻辑表

mycat中定义的table.是逻辑上存在,物理上未必存在.

主要是针对横向切分提供的概念

mycat中的表格table，其字段分散到mysql数据库的表格table1,table2,table3中。

 

 

2.4 默认端口

mycat默认端口是8066

2.5 数据主机 - datahost

物理mysql存放的主机地址.可以使用主机名,ip,域名定义.

2.6 数据节点 - datanode

物理的database是什么.数据保存的物理节点.就是database.

2.7 分片规则

当控制数据的时候,如何访问物理database和table.

就是访问datahost和datanode的算法.

在mycat处理具体的数据crud的时候,如何访问datahost和datanode的算法.如:哈希算法,crc16算法等.

3 mycat搭建

3.1 安装jdk

略

3.2 主从备份搭建完成

3.3 安装mycat

解压缩: tar -zxf mycat-xxxx.tar.gz

3.4 master提供可被mycat访问的用户

在mycat中通过master数据库的root用户访问master数据库.

grant all privileges on *.* to ‘username’@’ip’ identified by ‘password’ with grant option;

grant all privileges on *.* to 'mycat'@'%' identified by 'mycat' with grant option;

3.5 上传mycat

mycat-server-1.6-release-20161028204710-linux.tar.gz

3.6 解压缩

tar -zxf mycat-server-1.6-release-20161028204710-linux.tar.gz

3.7 mycat配置文件详解

mycat所有的配置文件,都在应用的conf目录中.

3.7.1 rule.xml

用于定义分片规则的配置文件.

主要是查看.很少修改.

mycat默认的分片规则: 以500万为单位,实现分片规则.

逻辑库a对应datanode - db1和db2. 1-500万保存在db1中, 500万零1到1000万保存在db2中,1000万零1到1500万保存在db1中.依次类推.

 

 

3.7.2 schema.xml

用于定义逻辑库和逻辑表的配置文件.在配置文件中可以定义读写分离,逻辑库,逻辑表,datahost,datanode等信息.

配置文件解释:

3.7.2.1 标签schema

配置逻辑库的标签

3.7.2.1.1 属性name

逻辑库名称

3.7.2.1.2 属性checksqlschema

是否检测sql语法中的schema信息.

如: mycat逻辑库名称 a, datanode名称b

sql : select * from a.table;

checksqlschema值是true, mycat发送到数据库的sql是select * from table;

checksqlschema值是false,mycat发送的数据库的sql是select * from a.table;

3.7.2.1.3 sqlmaxlimit

mycat在执行sql的时候,如果sql语法中没有limit子句.自动增加limit子句. 避免一次性得到过多的数据,影响效率. limit子句的限制数量默认配置为100.如果sql中有具体的limit子句,当前属性失效.

sql : select * from table . mycat解析后: select * from table limit 100

sql : select * from table limit 10 . mycat不做任何操作修改.

3.7.2.2 标签table

定义逻辑表的标签，如果需要定义多个逻辑表，编写多个table标签。要求逻辑表的表名和物理表（mysql数据库中真实存在的表）的表名一致。

3.7.2.2.1 属性name

逻辑表名

3.7.2.2.2 属性datanode

数据节点名称. 配置文件中后续需要定义的标签（即物理数据库中的database名称）.多个名称使用逗号分隔.

多个database定义后，代表分库。

3.7.2.2.3 属性rule

分片规则名称.具体的规则名称参考rule.xml配置文件.

sql语句发送到mycat中后，mycat如何计算，应该将当期的sql发送到哪一个物理数据库管理系统或物理database中。

3.7.2.3 标签datanode

定义数据节点的标签， 定义具体的物理database信息的。

3.7.2.3.1 属性name

数据节点名称, 是定义的逻辑名称,对应具体的物理数据库database

3.7.2.3.2 属性datahost

引用datahost标签的name值,代表使用的物理数据库所在位置和配置信息.

3.7.2.3.3 属性database

在datahost物理机中,具体的物理数据库database名称.

3.7.2.4 datahost标签

定义数据主机的标签， 就是物理mysql真实安装的位置。

3.7.2.4.1 属性name

定义逻辑上的数据主机名称

3.7.2.4.2 属性maxcon/mincon

最大连接数, max connections

最小连接数, min connections

3.7.2.4.3 属性dbtype

数据库类型 : mysql数据库

3.7.2.4.4 属性dbdriver

数据库驱动类型, native,使用mycat提供的本地驱动.

3.7.2.5 datahost子标签writehost

写数据的数据库定义标签. 实现读写分离操作.

3.7.2.5.1 属性 host

数据库命名

3.7.2.5.2 属性url

数据库访问路径

3.7.2.5.3 属性user

数据库访问用户名

3.7.2.5.4 属性password

访问用户密码

3.7.2.6 测试配置文件

 

 

 

 

 

 

 

 

3.7.3 server.xml

配置mycat服务信息的.

如: mycat中的用户,用户可以访问的逻辑库,可以访问的逻辑表,服务的端口号等.

常见修改内容:

 

 

 

 

 

 

 

3.7.4 启动mycat命令

bin/mycat start

3.7.5 停止命令

bin/mycat stop

3.7.6 重启命令

bin/mycat restart

3.7.7 查看mycat状态

bin/mycat status

3.7.8 访问方式

可以使用命令行访问或客户端软件访问.

3.7.8.1 命令行访问方式

mysql -u用户名 -p密码 -hmycat主机ip -p8066

链接成功后,可以当做mysql数据库使用.

访问成功后，不能直接使用。因为mycat只能访问mysql的schema（database），不能自动创建逻辑库对应的物理库。且不能自动创建逻辑表对应的物理表。

必须人工链接master数据库，手动创建database。

表格可以在mycat控制台创建。注意：在mycat控制台创建的表，必须是schema.xml配置文件中定义过的逻辑表。

启动后，经过测试，crc32slot分片规则无效，执行dml语句的时候只能识别db1和db2。

ddl语句，可以识别db3。

修改conf/rule.xml配置文件，找标签

 

 

 

 

 

 

 

修改count参数。修改为对应的物理database数量。

 

3.7.9 访问约束

3.7.9.1 表约束

不能创建未在schema.xml中配置的逻辑表

3.7.9.2 dml约束

尤其是新增: 必须在insert into语法后携带所有的字段名称.至少携带主键名称.

因为分片规则,绝大多数都是通过主键字段计算数据分片规则的.

3.7.10 查看mycat日志

logs/wrapper.log

日志中记录的是所有的mycat操作. 查看的时候主要看异常信息caused by信息

 

二、 mycat配置读写分离

1 mysql主从备份

1.1 主从备份概念

什么是主从备份: 就是一种主备模式的数据库应用.

主库(master)数据与备库(slave)数据完全一致.

实现数据的多重备份, 保证数据的安全.

可以在master[innodb]和slave[myisam]中使用不同的数据库引擎,实现读写的分离

1.1.1 mysql5.5版本后本身支持主从备份

在老旧版本的mysql数据库系统中,不支持主从备份,需要安装额外的rpm包.

如果需要安装rpm,只能在一个位置节点安装.

1.1.2 主从备份目的

1.1.2.1 实现主备模式

保证数据的安全. 尽量避免数据丢失的可能.

1.1.2.2 实现读写分离

使用不同的数据库引擎,实现读写分离.提高所有的操作效率.

innodb使用dml语法操作. myisam使用dql语法操作.

1.1.3 主从备份效果

1.1.3.1 主库操作同步到备库

所有对master的操作,都会同步到slave中.

如果master和salve天生上环境不同,那么对master的操作,可能会在slave中出现错误

如: 在创建主从模式之前,master有database : db1, db2, db3. slave有database: db1, db2.

创建主从模式.现在的情况master和slave天生不同.

主从模式创建成功后,在master中drop database db3. slave中抛出数据库sql异常.后续所有的命令不能同步.

一旦出现错误. 只能重新实现主从模式.

1.2 安装mysql

略过.

1.3 主从备份配置

主要操作master和slave中的配置文件和dbms的配置.

配置文件: 定义主从模式的基础信息. 如: 日志, 命令等.

dbms配置: 提供主从访问的用户,基础信息[master和slave的位置,用户名,密码,日志文件名等]等.

建议：建立主从备份的多个mysql，最好原始环境一致。database，table，data完全一致。

1.3.1 master[主库]配置

1.3.1.1 修改master配置文件

/etc/my.cnf

需要修改. 在修改前建议复制一份备份文件.

修改后的my.cnf配置文件,参考资料中的my.cnf文件内容.

1.3.1.1.1 server-id

本环境中server-id是1

 

mysql服务唯一标识

唯一标识是数字. 自然数

配置的时候有要求

1.3.1.1.1.1 单机使用

server-id 任意配置,只要是数字即可

1.3.1.1.1.2 主从使用

server-id master唯一标识数字必须小于slave唯一标识数字.

1.3.1.1.2 log_bin

本环境中log_bin值 : master_log

日志文件命名, 开启日志功能。此日志是命令日志。就是记录主库中执行的所有的sql命令的。

1.3.1.1.2.1 开启日志

mysql的log_bin不是执行日志,状态日志. 是操作日志.就是在dbms中所有的sql命令

log_bin日志不是必要的.只有配置主从备份时才必要。

1.3.1.1.2.2 日志文件配置

变量的值就是日志文件名称.是日志文件名称的主体.

mysql数据库自动增加文件名后缀和文件类型.

1.3.1.2 重启mysql

service mysqld restart

1.3.1.3 配置master

1.3.1.3.1 访问mysql

mysql -uusername -ppassword

1.3.1.3.2 创建用户

在mysql数据库中,为不存在的用户授权,就是同步创建用户并授权.

此用户是从库访问主库使用的用户

ip地址不能写为%. 因为主从备份中,当前创建的用户,是给从库slave访问主库master使用的.用户必须有指定的访问地址.不能是通用地址.

 

 

 

 

 

 

 

 

1.3.1.3.3 查看用户

use mysql;

select host, name from user;

1.3.1.3.4 查看master信息

show master status;

1.3.2 slave[从库]配置

1.3.2.1 修改slave配置文件

/etc/my.cnf

1.3.2.1.1 server_id

唯一标识, 本环境中配置为 : 2

1.3.2.1.2 log_bin

可以使用默认配置, 也可以注释.

1.3.2.2 可选: 修改uuid

主从模式要求多个mysql物理名称不能相同. 即按装mysql过程中linux自动生成的物理标志. 唯一物理标志命名为uuid. 保存位置是mysql数据库的数据存放位置. 默认为/var/lib/mysql目录中. 文件名是auto.cnf.

修改auto.cnf文件中的uuid数据. 随意修改,不建议改变数据长度.建议改变数据内容.

/var/lib/mysql/auto.cnf

1.3.2.3 重启mysql服务

service mysqld restart

1.3.2.4 配置slave

1.3.2.4.1 访问mysql

mysql -uusername -ppassword

1.3.2.4.2 停止slave功能

stop slave

1.3.2.4.3 配置主库信息

需要修改的数据是依据master信息修改的. ip是master所在物理机ip. 用户名和密码是master提供的slave访问用户名和密码. 日志文件是在master中查看的主库信息提供的.在master中使用命令show master status查看日志文件名称.

 

change master to master_host=’ip’, master_user=’username’, master_password=’password’, master_log_file=’log_file_name’;

change master   to master_host='192.168.199.212',   master_user='slave', master_password='slave', master_log_file='master_log.000001';

 

 

1.3.2.4.4 启动slave功能

 

start slave;

1.3.2.4.5 查看slave配置

show slave status \g;

mysql> show   slave status \g;

***************************   1. row ***************************

slave_io_state: waiting for   master to send event

master_host: 192.168.120.139

master_user: slave

master_port: 3306

connect_retry: 60

 master_log_file: master-log.000001

read_master_log_pos: 427

relay_log_file:   mysqld-relay-bin.000002

relay_log_pos: 591

relay_master_log_file:   master-log.000001

slave_io_running: yes

slave_sql_running: yes

replicate_do_db:

replicate_ignore_db:

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: 427

relay_log_space: 765

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 最后一次错误的io请求编号

last_io_error:

last_sql_errno: 0 最后一次错误的执行sql命令编号.

last_sql_error:

replicate_ignore_server_ids:

master_server_id: 1

master_uuid:   9ee988ac-8751-11e7-8a95-000c2953ac06

master_info_file:   /var/lib/mysql/master.info

sql_delay: 0

sql_remaining_delay: null

slave_sql_running_state: slave has read   all relay log; waiting for the slave 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)

 

 

1.3.3 测试主从

1.4 主从模式下的逻辑图

 

 

 

2 mycat读写分离配置

修改conf/schema.xml配置文件，下述内容中，红色部分为重点内容。

 

 

 

 

如对本文有疑问， 点击进行留言回复！！