MySQL数据切分、负载均衡和集群
1系统环境
系统:CentOS 7。
版本:MySQL 5.6.35。
2概述
3数据切分(Sharding)
参考:http://zhengdl126.iteye.com/blog/419850
http://blog.csdn.net/bluishglc/article/details/6161475
水平分库分表需用一个冗余字段(如id)作为切分依据和标记字段,常用有如下策略:
Øid取模,将不同段位id分配到对应库表。
Øhash取模,根据id的hash值,划分到不同库表。
Ø路由表,由一个服务器保存id和对应库表的映射关系,通过查询该映射连接到对应库表。
3.1 分库
分库分表均有水平切分和垂直切分两种方式。水平切分即增加相同结构的数据库,库名不一样,分担单库存取压力;垂直切分即根据业务进行分析,把一个库里的表归类,分到不同的数据库里。
3.2 分表
水平切分即增加相同结构的数据表,表名不一样,分担单表的存取压力;垂直切分即根据业务进行分析,把一张表里的属性分类,分到不同的表里。
3.3 MySQL自带的分表分区功能
3.3.1表分区
参考:https://my.oschina.net/ydsakyclguozi/blog/393583
http://blog.csdn.net/laoyang360/article/details/52886987
http://www.codeceo.com/article/mysql-partition.html
http://blog.csdn.net/laoyang360/article/details/52887016配置不同分区存储到指定路径
MySQL 5 之后才有数据表分区功能,仅支持水平分区,不支持垂直分区。分区后的表数据存储在不同的数据文件里,表还是一张表,只是数据存储在不同数据文件。如:
按RANGE分区:
CREATE TABLE emp (
id INT NOT NULLAUTO_INCREMENT,
nameVARCHAR(30),
age int,
addr text,
PRIMARY KEY(id)
)
PARTITION BY RANGE (id) (
PARTITION p0VALUES LESS THAN (3),
PARTITION p1VALUES LESS THAN (6),
PARTITION p2VALUES LESS THAN (10),
PARTITION p3VALUES LESS THAN MAXVALUE
);
查看分区文件:
[root@dn03 ~]# ll /var/lib/mysql/test/
total 512
-rw-rw---- 1 mysql mysql 61 Apr 17 09:41 db.opt
-rw-rw---- 1 mysql mysql 8644 Apr 17 10:05 emp.frm
-rw-rw---- 1 mysql mysql 32 Apr 17 10:05 emp.par
-rw-rw---- 1 mysql mysql 98304 Apr 17 10:07emp#P#p0.ibd
-rw-rw---- 1 mysql mysql 98304 Apr 17 10:07emp#P#p1.ibd
-rw-rw---- 1 mysql mysql 98304 Apr 17 10:07 emp#P#p2.ibd
-rw-rw---- 1 mysql mysql 98304 Apr 17 10:07emp#P#p3.ibd
MySQL数据库中的数据是以文件的形势存在磁盘上的,默认放在/mysql/data下面(可以通过my.cnf中的datadir来查看),一张表主要对应着三个文件,一个是frm存放表结构的,一个是myd存放表数据的,一个是myi存表索引的。
3.3.1.1 分区类型
其实就是路由算法了。
ØRANGE分区:基于属于一个[给定连续区间]的列值,把多行分配给分区。
ØLIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个[离散值集合]中的某个值来进行选择。
ØHASH分区:基于[用户定义的表达式的返回值]来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
ØKEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
Ø复合分区:对分区的再次分区。
3.3.2分表(merge引擎)
参考:http://www.cnblogs.com/miketwais/articles/mysql_partition.html
通过Merger存储引擎(mrg_myisam引擎)进行分表,是把一张大表分成多张子表,数据存储到各个子表里,大表不实际存储数据,只起到整合子表的作用,但可以对大表进行数据的CRUD操作,分表对上层应用来说是透明的。
注:
Ø该方式仅适用myisam引擎的表。
ØID如果自增的话,子表里可能含有相同ID,则根据ID查询主表,结果为最后一条子表插入的ID符合条件的数据。
示例:
create table tb_member1(
id bigint primary key auto_increment ,
name varchar(20),
sex tinyint not null default '0'
)ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
create table tb_member2(
id bigint primary key auto_increment ,
name varchar(20),
sex tinyint not null default '0'
)ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
create table tb_member3 like tb_member1;
insert into tb_member1(id,name,sex) values (1,'jack','0');
insert into tb_member1(name,sex) select name,sex from tb_member1;
create table tb_member(
id bigint primary key auto_increment ,
name varchar(20),
sex tinyint not null default '0'
)ENGINE=MERGE UNION=(tb_member1,tb_member2,tb_member3) INSERT_METHOD=LAST CHARSET=utf8 AUTO_INCREMENT=1 ;
3.3.3表分区与分表的区别
分区从逻辑上来讲只有一张表,而分表则是将一张表分解成多张表。分表和分区不矛盾,可以相互配合使用。
4MySQL Cluster集群
MySQL集群包含3种节点:
Ø管理(MGM)节点:负责管理MySQL Cluster内的其他节点,如提供配置数据、启动并停止节点、运行备份等。由于这类节点负责管理其他节点的配置,应在启动其他节点之前首先启动这类节点。MGM节点是用命令“ndb_mgmd”启动的。
Ø数据节点:用于保存 Cluster的数据。数据节点的数目与副本的数目相关,是片段的倍数。数据节点是用命令“ndbd”启动的。
ØSQL节点:用来访问 Cluster数据的节点。也就是Mysql服务,可以使用service mysqld start启动。
管理服务器(MGM节点)负责管理 Cluster配置文件和 Cluster日志。 Cluster中的每个节点从管理服务器检索配置数据,并请求确定管理服务器所在位置的方式。当数据节点内出现新的事件时,节点将关于这类事件的信息传输到管理服务器,然后,将这类信息写入 Cluster日志。
“NDB” 是一种“内存中”的存储引擎,也是事务型存储引擎,具备ACID属性。
注:mysql-cluste与非集群时用的mysql-server与mysql-client没有任何关系,mysql-cluste安装包中已自带了集群用的server与client,启动mysql也是启动mysql-cluste中的mysql,与原先的mysql-server没有任何关系。
官方文档:https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html
参考:http://www.cnblogs.com/shihaiming/p/6084965.html
4.1 下载
下载地址:https://dev.mysql.com/downloads/cluster
选择合适版本下载,本文选择版本为:mysql-cluster-gpl-7.5.6-linux-glibc2.5-x86_64。
4.2 安装
官网文档:https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-install-linux-binary.html
解压下载的tar.gz文件,将解压后的文件夹移动到合适位置。安装需在不同类型(管理节点、数据节点、SQL节点)的节点上进行。
本例集群规划如下:
节点类型 |
IP规划 |
SQL节点 |
192.168.1.210、192.168.1.212 |
数据节点 |
192.168.1.210、192.168.1.212 |
管理节点 |
192.168.1.213 |
在各个类型的节点上进行对应节点的操作。
4.2.1SQL节点
4.2.1.1 创建mysql用户组和mysql用户
若系统不存在mysql用户和mysql用户组,则需创建:
# groupadd mysql
# useradd -g mysql -s /bin/false mysql
4.2.1.2 建立数据库
创建目录:
# mkdir /usr/local/mysql
进入解压包里的bin文件夹,执行以下命令建立数据库:
# ./mysqld --initialize
执行成功后将为MySQL root用户生成一个随机密码,记得记下该密码。
4.2.2数据节点
添加ndbd和ndbmtd到系统bin目录:
# cp bin/ndbd /usr/local/bin/ndbd
# cp bin/ndbmtd /usr/local/bin/ndbmtd
设置权限:
# cd /usr/local/bin
# chmod +x ndb*
4.2.3管理节点
# cp bin/ndb_mgm* /usr/local/bin
# cd /usr/local/bin
# chmod +x ndb_mgm*
4.3 配置
官网文档:https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-install-configuration.html
4.3.1 配置数据节点和SQL节点
数据节点和SQL节点需要一个my.cnf文件,位于/etc目录:
# vi /etc/my.cnf
内容如下:
[mysqld]
# Options for mysqld process:
ndbcluster # run NDB storage engine
[mysql_cluster]
# Options for NDB Cluster processes:
ndb-connectstring=192.168.1.213 # location of management server
4.3.2 配置管理节点
管理节点需一个config.ini文件:
# mkdir /var/lib/mysql-cluster
# cd /var/lib/mysql-cluster
# vi config.ini
内容如下:
[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2 # Number of replicas
DataMemory=80M # How much memory to allocate for data storage
IndexMemory=18M # How much memory to allocate for index storage
# For DataMemory and IndexMemory, we have used the
# default values. Since the "world" database takes up
# only about 500KB, this should be more than enough for
# this example NDB Cluster setup.
ServerPort=2202 # This the default value; however, you can use any
# port that is free for all the hosts in the cluster
# Note1: It is recommended that you do not specify the port
# number at all and simply allow the default value to be used
# instead
# Note2: The port was formerly specified using the PortNumber
# TCP parameter; this parameter is no longer available in NDB
# Cluster 7.5.
[ndb_mgmd]
# Management process options:
HostName=192.168.1.213 # Hostname or IP address of MGM node
DataDir=/var/lib/mysql-cluster # Directory for MGM node log files
[ndbd]
# Options for data node "A":
# (one [ndbd] section per data node)
HostName=192.168.1.210 # Hostname or IP address
NodeId=2 # Node ID for this data node
DataDir=/usr/local/mysql/data # Directory for this data node's data files
[ndbd]
# Options for data node "B":
HostName=192.168.1.212 # Hostname or IP address
NodeId=3 # Node ID for this data node
DataDir=/usr/local/mysql/data # Directory for this data node's data files
[mysqld]
# SQL node options:
HostName=192.168.1.210 # Hostname or IP address
# (additional mysqld connections can be
# specified for this node for various
# purposes such as running ndb_restore)
[mysqld]
# SQL node options:
HostName=192.168.1.212 # Hostname or IP address
# (additional mysqld connections can be
# specified for this node for various
# purposes such as running ndb_restore)
4.4 启动
官网文档:https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-install-first-start.html
启动MySQL Cluster集群需要在各个节点上启动。
确保目录存在:/usr/local/mysql,不存在则创建。
4.4.1启动管理节点
在管理节点上执行如下命令:
# ndb_mgmd -f /var/lib/mysql-cluster/config.ini
4.4.2启动数据节点
在各个数据节点上执行如下命令:
# ndbd
2017-04-20 12:56:56 [ndbd] INFO -- Angel connected to '192.168.1.213:1186'
2017-04-20 12:56:56 [ndbd] INFO -- Angel allocated nodeid: 3
4.5 连接管理节点
如果一切操作成功,则可在管理节点上使用ndb_mgm命令连接集群管理节点:
# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2node(s)
id=2@192.168.1.210 (mysql-5.7.18ndb-7.5.6, Nodegroup: 0, *)
id=3@192.168.1.212 (mysql-5.7.18ndb-7.5.6, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1@192.168.1.213 (mysql-5.7.18ndb-7.5.6)
[mysqld(API)] 2node(s)
id=4 (not connected, accepting connect from192.168.1.210)
id=5 (not connected, accepting connect from192.168.1.212)
ndb_mgm>
4.6 关闭
在管理节点上执行:
# ndb_mgm -e shutdown
4.7 启动集群MySQL Server
在各个SQL节点上,进入集群解压包里的bin目录,执行:
# ./mysqld_safe --user=mysql&
若出现如下错误:
[root@dn01 bin]# Logging to'/usr/local/mysql/data/dn01.hadoop.fjmb.com.err'.
2017-04-20T09:01:11.350004Z mysqld_safe Starting mysqld daemonwith databases from /usr/local/mysql/data
2017-04-20T09:01:11.746171Z mysqld_safe mysqld from pid file/usr/local/mysql/data/dn01.hadoop.fjmb.com.pid ended
则执行如下命令,赋予mysql用户该路径的权限:
# chown -R mysql/usr/local/mysql/data
再次执行:
# ./mysqld_safe --user=mysql &
则成功启动mysql服务。
4.8 连接MySQL Server
进入mysql cluster bin目录,执行:
# ./mysql -u root -p
回车后根据提示输入密码则成功连接server,并进入mysql操作提示符。
4.8.1创建NDBCLUSTER表
在一个节点上,创建数据库和表
mysql> CREATE DATABASE test;
mysql> USE test;
mysql> CREATE TABLE ctest (i INT) ENGINE=NDBCLUSTER;
mysql> SHOW CREATE TABLE ctest \G
则可在所有启动的数据节点上看到创建的库表。建表时注意引擎为:ENGINE=NDBCLUSTER 或者 ENGINE=NDB。
至此,NDB集群搭建成功。