当前位置: 首页 > 科技观察

MySQL+MyCat分库分表配置

时间:2023-03-12 09:21:31 科技观察

读写分离一、MySQL+MyCat分库分表1MyCat简介java编写的数据库中间件Mycat的运行环境需要JDK。Mycat是中间件,是运行在代码应用程序和MySQL数据库之间的应用程序。前身:corba是阿里开发的一款数据库中间件,实现了MySQL数据库分库分表的集群管理。发生了重大事故,二次开发形成了Mycat。使用MyCat后,所有编写的SQL语句都必须严格遵守SQL标准规范。插入表名(列名)值(列值);使用MyCat中间件后的结构图如下:2MyCat术语介绍2.1Segmentation逻辑切分。在物理层面,是通过使用多数据库[database]和多表[table]来实现的。分割。2.1.1垂直切分一个数据库被划分为多个数据库。配置方便,只实现两个表的表连接查询。一张表中的数据分散到数据库中结构相同的若干张表中。多个表的数据集合就是当前表的数据。2.1.2水平切分将一个表切分为多个表。相对于纵向切分的配置,比较麻烦,无法实现表连接查询。将一个表的字段分散到几个表中,将几个表连接在一起就是该表当前的完整数据。2.2逻辑库Mycat中定义的数据库逻辑上存在,但物理上未必存在。主要是为垂直分割提供的概念。访问MyCat就是把MyCat当作MySQL来使用。Db数据库就是MyCat中定义的数据库。通过SQL访问MyCat中的db库时,对应MySQL中的db1、db2、db3这三个库。物理数据库是db1、db2、db3。逻辑数据库是db。2.3逻辑表Mycat中定义的表逻辑上存在,但物理上不一定存在。主要是为了横向切分的概念。MyCat中的表,其字段分散在MySQL数据库的表table1、table2、table3中。2.4默认端口Mycat默认端口为80662.5数据主机——dataHost存储在MySQL中的物理主机地址。您可以使用主机名、IP、域名定义。2.6Datanode——dataNode的物理数据库是什么。保存数据的物理节点是数据库。2.7分片规则在控制数据时,如何访问物理数据库和表。是访问dataHost和dataNode的算法。Mycat在处理具体数据CRUD时,如何访问dataHost和dataNode的算法。如:hash算法,crc16算法等3Mycat搭建3.1稍微安装JDK3.2主从备份搭建完成3.3安装mycat解压:tar-zxfmycat-xxxx.tar.gz3.4Master提供Mycat可以访问的用户在mycat通过Master数据库的root用户访问Masterdatabase.grantallprivilegeson*.*to'username'@'ip'identifiedby'password'withgrantoption;grantallprivilegeson*.*to'mycat'@'%'identifiedby'mycat'withgrantoption;3.5上传mycatMycat-server-1.6-RELEASE-20161028204710-linux.tar.gz3.6解压tar-zxfMycat-server-1.6-RELEASE-20161028204710-linux.tar.gz3.7Mycat配置文件详解Mycat的所有配置文件都在应用程序的conf目录。3.7.1rule.xml用于定义分片规则的配置文件。主要是为了观赏。它很少被修改。mycat默认的分片规则:500万个单元来实现分片规则。逻辑库A对应dataNode——db1和db2。1-500万存入db1,500万和1-1000万存入db2,1000万和1-1500万存入db1,以此类推。3.7.2schema.xml是用来定义逻辑库和逻辑表的配置文件。在配置文件中,可以定义读写分离、逻辑库、逻辑表、dataHost、dataNode等信息。配置文件说明:3.7.2.1labelschema配置逻辑库标签3.7.2.1.1属性名逻辑库名3.7.2.1.2属性checkSQLschema是否检测SQL语法中的schema信息。例如:Mycat逻辑库名A,dataNode名BSQL:select*fromA.table;checkSQLschema值为true,Mycat向数据库发送的SQL是select*fromtable;checkSQLschema值为false,Mycat发送的数据库SQL为select*fromA.table;3.7.2.1.3sqlMaxLimitMycat执行SQL时,如果SQL语法中没有limit子句,则自动添加limit子句,避免gettoo一次处理很多数据,影响效率。limit子句的限制数量默认配置为100个。如果SQL中有特定的limit子句,则当前属性无效。SQL:从表中选择*。mycat解析后:select*fromtablelimit100SQL:select*fromtablelimit10.mycat没有做任何修改。3.7.2.2标签表定义了逻辑表的标签。如果需要定义多个逻辑表,写多个表标签需要逻辑表的表名与物理表(MySQL数据库中实际存在的表)的表名一致。3.7.2.2.1属性名逻辑表名3.7.2.2.2属性dataNode数据节点名。后面需要在配置文件中定义的标签(即物理数据库中的数据库名)。多个名称以逗号分隔。多个数据库定义后,代表分库。3.7.2.2.3属性规则分片规则名称。具体规则名称参见rule.xml配置文件。SQL语句发送到Mycat后,Mycat如何计算,应该将当前SQL语句发送给哪个物理数据库管理系统或物理数据库管理系统?数据库。3.7.2.3LabeldataNode定义了数据节点的标签,定义了具体的物理数据库信息。3.7.2.3.1属性名数据节点名是定义的逻辑名,对应于具体的物理数据库database3.7.2.3.2属性dataHost是指dataHost标签的name值,表示数据节点的位置和配置信息使用的物理数据库。3.7.2.3.3属性数据库在dataHost物理机中,物理数据库database的具体名称。3.7.2.4dataHost标签定义了数据主机的标签,也就是物理MYSQL实际安装的位置。3.7.2.4.1属性名定义逻辑数据主机名3.7.2.4.2属性maxCon/minCon***连接数、最大连接数、最小连接数、最小连接数3.7.2.4.3属性dbType数据库类型:mysql数据库3.7.2.4.4属性dbDriver数据库驱动类型,native,使用mycat提供的本地驱动。3.7.2.5dataHostsubtagwriteHost用于写入数据的数据库定义标签。实现读写分离操作。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.3server.xml配置Mycat服务信息。例如:Mycat中的用户,该用户可以访问的逻辑库,可以访问的逻辑表,服务的端口号等。常用修改:3.7.4启动Mycat命令bin/mycatstart3.7.5停止命令bin/mycatstop3.7.6重启命令bin/mycatrestart3。7.7查看Mycat状态bin/mycatstatus3.7.8访问方式可以通过命令行或者客户端软件访问。3.7.8.1命令行访问方式mysql-u用户名-p密码-hmycathostIP-P8066链接成功后,即可作为MySQL数据库使用。访问成功后不能直接使用。因为Mycat只能访问MYSQL的schema(数据库),不能自动创建逻辑库对应的物理库。并且不能自动创建逻辑表对应的物理表。必须手动链接主数据库并手动创建数据库。可以在mycat控制台中创建表。注意:mycat控制台中创建的表必须是schema.xml配置文件中定义的逻辑表。启动后,经过测试,crc32slot分片规则无效,执行DML语句时只能识别db1和db2。DDL语句可以识别db3。修改conf/rule.xml配置文件,找到tag修改count参数。修改为对应的物理数据库数量。3.7.9访问约束3.7.9.1表约束不能创建没有在schema.xml中配置的逻辑表3.7.9.2DML约束特别新:必须在insertinto语法后携带所有字段名。至少携带主键名称。因为分片规则,大部分都是按主键字段计算的。3.7.10查看Mycat日志logs/wrapper.log日志记录了mycat的所有操作。查看时主要看信息导致的异常信息2.MyCat配置读写分离1MySQL主从备份1.1主从备份的概念什么是主从备份:是主备中的数据库应用模式。主库(Master)数据与备库(Slave)数据完全一致。实施数据多重备份,确保数据安全。Master[InnoDB]和Slave[MyISAM]可以使用不同的数据库引擎,实现读写分离。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时,在slave模式之前,Master有数据库:db1,db2,db3。从机有数据库:db1、db2。创建主从模式。Master和Slave现在的情况是不一样的。创建主从模式成功后,在Master中drop数据库db3。Slave抛出数据库SQL异常。所有后续命令都无法同步。一旦出现错误,只能重新实现主从模式。1.2跳过安装MySQL。1.3主从备份配置主要操作MasterandSlaveConfiguration中的配置文件和DBMS。配置文件:定义主从模式的基本信息。如:日志、命令等DBMS配置:为用户提供主从访问,基础信息[主从位置、用户名、密码、日志文件名等]等。建议:建立主从MySQL多备份,并保证原有环境一致数据库、表、数据完全一致.1.3.1Master[主库]配置1.3.1.1修改Master配置文件/etc/my.cnf需要修改。建议在修改前复制一份备份文件。修改后的my.cnf配置文件是我的参考资料。cnf文件内容。1.3.1.1.1server-id在本环境下,server-id是1MySQL服务的唯一标识。唯一标识符是一个数字。配置自然数时有要求。1.3.1.1.1.2master-slave使用server-idMaster的唯一标识号必须小于Slave的唯一标识号。1.3.1.1.2log_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重启MySQLservicemysqldrestart1.3.1.3配置Master1.3.1.3.1访问MySQLmysql-uusername-ppassword1.3.1.3.2创建用户在MySQL数据库中,对不存在的用户进行授权,即创建用户并授权同步地。这个user是从库访问主库使用的用户ip地址,不能写成%。因为主从备份,当前创建的用户是从库Slave用来访问主库Master的。用户必须有指定的访问地址。它不能是一个通用地址。1.3.1.3.3查看用户usemysql;selecthost,namefromuser;1.3.1.3.4查看Master信息showmasterstatus;1.3.2Slave【从库】配置1.3.2.1修改Slave配置文件/etc/my.cnf1.3.2.1.1server_id唯一标识,本环境配置为:21.3.2.1.2log_bin可以使用默认配置,你还可以评论。1.3.2.2可选:修改uuid主从模式要求多个MySQL物理名不能相同。即根据Linux在MySQL安装过程中自动生成的物理标识。唯一的物理标志被命名为uuid。保存位置MySQL数据库的数据存放位置。默认在/var/lib/mysql目录中。文件名为auto.cnf。修改auto.cnf文件中的uuid数据。随意修改,不建议更改数据长度。建议更改数据内容。/var/lib/mysql/auto.cnf1.3.2.3重启MySQL服务servicemysqldrestart1.3.2.4配置Slave1.3.2.4.1访问mysqlmysql-uusername-ppassword1.3.2.4.2停止Slave函数stopsslave1.3.2.4.3配置主库信息需要修改的数据根据??Master的信息修改。ip是Master所在物理机的IP。用户名和密码为Master提供的Slave接入用户名和密码。日志文件由Master中查看的master库信息提供。在Master命令showmaster中使用status查看日志文件名.changemastertomaster_host='ip',master_user='username',master_password='password',master_log_file='log_file_name';changemastertomaster_host='192.168.199.212',master_user='slave',master_password='slave',master_log_file='master_log.000001';1.3.2.4.4启动Slave功能startslave;1.3.2.4.5查看Slave配置showslavestatusG;mysql>showslavestatusG;***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:192.168.120.139Master_User:slaveMaster_Port:3306Connect_Retry:60Master_Log_File:master-log.000001Read_Master_Log_Pos:427Relay_Log_File:mysqld-relay-bin.000002Relay_Log_Pos:591Relay_Master_Log_File:master-log.000001Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:427中继日志_Space:765Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert:NoLast_IO_Errno:0***一次错误的IO请求编号Last_IO_Error:Last_SQL_Errno:0***一次错误的执行SQL命令编号.Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:1Master_UUID:9ee988ac-8751-11e7-8a95-000c2953ac06Master_Info_File:/var/lib/mysql/master.infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Slavehasreadallrelaylog;waitingfortheslaveI/OthreadtoupdateitMaster_Retry_Count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position:01rowinset(0.00sec)1.3.3测试主从1.4主从模式下的逻辑图2MyCat读写分离配置修改conf/schema.xml配置文件,以下内容中,红色部分部分为重点内容