MySQL
MySQL安装
卸载卸载预装mysql
#查看已安装:
rpm -qa | grep mariadb
#卸载:
rpm -e --nodeps mariadb-libs-5.5.65-1.el7.x86_64
#再次查看:
rpm -qa | grep mariadb
上传安装包,解压
# 解压到
tar -zxvf mysql-5.6.31-linux-glibc2.5-x86_64.tar.gz -C /usr/local
cd /usr/local
# 剪切重命名
mv mysql-5.6.31-linux-glibc2.5-x86_64 mysql
复制mysql的配置文件
cd mysql
cp support-files/my-default.cnf /etc/my.cnf
cp support-files/mysql.server /etc/rc.d/init.d/mysql
修改my.cnf
#修my.cnf
vim /etc/my.cnf
# 添加内容
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
log-error = /usr/local/mysql/data/error.log
pid-file = /usr/local/mysql/data/mysql.pid
user = root
tmpdir = /tmp
初始化数据库
# 先安装autoconf
yum -y install autoconf
# 初始化
./scripts/mysql_install_db --user=root --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/mysql.pid --tmpdir=/tmp
# 重启服务
service mysql start
# 查看启动状态
service mysql status
配置mysql命令支持,添加软连接
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
修改MySQL密码
mysql -u root
mysql> use mysql;
mysql> update user set password= password("1111") where user='root';
开放远程登录权限
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '1111' WITH GRANT OPTION;
mysql>FLUSH PRIVILEGES;
设置开机启动
[root@centos mysql] chkconfig mysql on
MySQL主从复制
克隆MySQL-1的虚拟机
MySQL-1为主,MySQL-2为从
配置主数据库
开启日志并修改server_id
#修改配置文件
vim /etc/my.cnf
#修改内容
log_bin=master_log
server_id=1
重启并查看日志是否开启
# 重启
service mysql restart
# 进入mysql
mysql -u root -p
show master status;
配置从数据库
修改server_id 为2
vim /etc/my.cnf
data文件夹auto.cnf编写当前mysql的uuid
cd /usr/local/mysql/data
vim auto.cnf
随便改一下跟主的不一样就可以
# 重启mysql
service mysql restart
修改slave
# 进入数据库
mysql -u root -p
#停止slave
mysql> stop slave;
mysql> change master to master_host='192.168.226.230',master_user='root',master_password='1111',master_log_file='master_log.000001';
# 启动
mysql> start slave;
# 查看slave状态
mysql>show slave status \G;
这样就成功了
在主数据库新建库,建表,插入数据
打开MySQL-2查看
已经实现了主从复制
MyCat
MyCat?是一个国产的数据库中间件,前身是阿里的cobar
官方网站:http://www.mycat.org.cn/
分库分表?
(垂直分割)分库:把shop拆分到3个mysql节点上
(水平分割)分表:把tb_item拆分到3个mysql节点上
mycat核心概念:
逻辑库(shchema):逻辑上完整的库
逻辑表(table):逻辑上完整的表
数据主机(dataHost):服务器
数据节点(dataNode):服务器上的mysql
分片规则(rule):分表的规则
mycat的配置文件有哪几个?作用是什么?
schema.xml:逻辑库、逻辑表、数据节点
rule.xml:分表规则(crc32slot)
server.xml:mycat用户名和密码
mycat原理
sql |分片分析| |读写分析分析|
client------------>mycat拦截 ------------>多mysql
|路由| | 聚合| |排序|
mycat的原理就是“拦截”,它拦截了客户端发送的sql语句,首先对sql语句做写特定的分析:分片分析、路由分析、读写分析
并将sql发送到真实的mysql去执行,最后讲返回的结果做聚合和排序处理,最终返回给客户端
安装
上传压缩包,解压
cd /usr/upload
tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local
启动和关闭MyCat
cd /usr/local/mycat/bin
# 启动
./mycat start
# 停止
./mycat stop
# 重启
./mycat restart
# 查看状态
./mycat status
可以使用mysql的客户端直接连接mycat服务。默认服务端口为8066
MyCat分库分表、读写分离
这里使用主虚拟机,3个数据库演示
修改配置文件schema.xml、server.xml、rule.xml
schema.xml
Schema.xml作为MyCat中重要的配置文件之一,管理着MyCat的逻辑库、表、分片规则、DataNode以及DataHost。
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- schema -->
<schema name="power_shop" checkSQLschema="false" sqlMaxLimit="100">
<!-- table -->
<table name="tb_content" dataNode="dn1,dn2,dn3" rule="crc32slot" />
<table name="tb_content_category" dataNode="dn1,dn2,dn3" rule="crc32slot1"/>
<table name="tb_item" dataNode="dn1,dn2,dn3" rule="crc32slot2" />
<table name="tb_item_cat" dataNode="dn1,dn2,dn3" rule="crc32slot3" />
<table name="tb_item_desc" dataNode="dn1,dn2,dn3" rule="crc32slot4" />
<table name="tb_item_param" dataNode="dn1,dn2,dn3" rule="crc32slot5" />
<table name="tb_item_param_item" dataNode="dn1,dn2,dn3" rule="crc32slot6" />
<table name="tb_order" dataNode="dn1,dn2,dn3" rule="crc32slot7" />
<table name="tb_order_item" dataNode="dn1,dn2,dn3" rule="crc32slot8" />
<table name="tb_order_shipping" dataNode="dn1,dn2,dn3" rule="crc32slot9" />
<table name="tb_user" dataNode="dn1,dn2,dn3" rule="crc32slot10" />
</schema>
<!-- dataNode -->
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" />
<!-- dataHost -->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- writeHost -->
<writeHost host="hostM1" url="192.168.226.230:3306" user="root" password="1111">
<!-- readHost -->
<readHost host="hostS2" url="192.168.226.231:3306" user="root" password="1111" />
</writeHost>
</dataHost>
</mycat:schema>
server.xml
server.xml几乎保存了所有mycat需要的系统配置信息。最常用的是在此配置用户名、密码及权限。
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 -->
<property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 -->
<property name="sequnceHandlerType">2</property>
<!-- <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
<!-- <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
<!-- <property name="processorBufferChunk">40960</property> -->
<!--
<property name="processors">1</property>
<property name="processorExecutor">32</property>
-->
<!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
<property name="processorBufferPoolType">0</property>
<property name="handleDistributedTransactions">0</property>
<!--
off heap for merge/order/group/limit 1开启 0关闭
-->
<property name="useOffHeapForMerge">1</property>
<!--
单位为m
-->
<property name="memoryPageSize">1m</property>
<!--
单位为k
-->
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<!--
单位为m
-->
<property name="systemReserveMemorySize">384m</property>
<user name="root">
<property name="password">1111</property>
<property name="schemas">power_shop</property>
</user>
<user name="user">
<property name="password">1111</property>
<property name="schemas">power_shop</property>
<property name="readOnly">true</property>
</user>
</mycat:server>
rule.xml
分片规则
auto-sharding-long 规则
以 500 万为单位,实现分片规则:
1-500 万保存在 db1 中, 500 万零 1 到 1000 万保存在 db2 中,1000 万零 1 到 1500 万保存在 db3 中.
crc32slot 规则
在 CRUD 操作时,根据具体数据的 crc32 算法计算,数据应该保存在哪一个dataNode 中
<?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="rule1">
<rule>
<columns>id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>
<tableRule name="rule2">
<rule>
<columns>user_id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-intfile">
<rule>
<columns>sharding_id</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<tableRule name="mod-long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-murmur">
<rule>
<columns>id</columns>
<algorithm>murmur</algorithm>
</rule>
</tableRule>
<tableRule name="crc32slot">
<rule>
<columns>id</columns>
<algorithm>crc32slot</algorithm>
</rule>
</tableRule>
<tableRule name="crc32slot1">
<rule>
<columns>id</columns>
<algorithm>crc32slot</algorithm>
</rule>
</tableRule>
<tableRule name="crc32slot2">
<rule>
<columns>id</columns>
<algorithm>crc32slot</algorithm>
</rule>
</tableRule>
<tableRule name="crc32slot3">
<rule>
<columns>id</columns>
<algorithm>crc32slot</algorithm>
</rule>
</tableRule>
<tableRule name="crc32slot4">
<rule>
<columns>item_id</columns>
<algorithm>crc32slot</algorithm>
</rule>
</tableRule>
<tableRule name="crc32slot5">
<rule>
<columns>id</columns>
<algorithm>crc32slot</algorithm>
</rule>
</tableRule>
<tableRule name="crc32slot6">
<rule>
<columns>id</columns>
<algorithm>crc32slot</algorithm>
</rule>
</tableRule>
<tableRule name="crc32slot7">
<rule>
<columns>order_id</columns>
<algorithm>crc32slot</algorithm>
</rule>
</tableRule>
<tableRule name="crc32slot8">
<rule>
<columns>id</columns>
<algorithm>crc32slot</algorithm>
</rule>
</tableRule>
<tableRule name="crc32slot9">
<rule>
<columns>order_id</columns>
<algorithm>crc32slot</algorithm>
</rule>
</tableRule>
<tableRule name="crc32slot10">
<rule>
<columns>id</columns>
<algorithm>crc32slot</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-month">
<rule>
<columns>create_time</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>
<tableRule name="latest-month-calldate">
<rule>
<columns>calldate</columns>
<algorithm>latestMonth</algorithm>
</rule>
</tableRule>
<tableRule name="auto-sharding-rang-mod">
<rule>
<columns>id</columns>
<algorithm>rang-mod</algorithm>
</rule>
</tableRule>
<tableRule name="jch">
<rule>
<columns>id</columns>
<algorithm>jump-consistent-hash</algorithm>
</rule>
</tableRule>
<function name="murmur"
class="io.mycat.route.function.PartitionByMurmurHash">
<property name="seed">0</property><!-- 默认是0 -->
<property name="count">2</property><!-- 要分片的数据库节点数量,必须指定,否则没法分片 -->
<property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点,默认是160倍,也就是虚拟节点数是物理节点数的160倍 -->
<!-- <property name="weightMapFile">weightMapFile</property> 节点的权重,没有指定权重的节点默认是1。以properties文件的格式填写,以从0开始到count-1的整数值也就是节点索引为key,以节点权重值为值。所有权重值必须是正整数,否则以1代替 -->
<!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property>
用于测试时观察各物理节点与虚拟节点的分布情况,如果指定了这个属性,会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件,没有默认值,如果不指定,就不会输出任何东西 -->
</function>
<function name="crc32slot"
class="io.mycat.route.function.PartitionByCRC32PreSlot">
<property name="count">3</property><!-- 要分片的数据库节点数量,必须指定,否则没法分片 -->
</function>
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
</function>
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">3</property>
</function>
<function name="func1" class="io.mycat.route.function.PartitionByLong">
<property name="partitionCount">8</property>
<property name="partitionLength">128</property>
</function>
<function name="latestMonth"
class="io.mycat.route.function.LatestMonthPartion">
<property name="splitOneDay">24</property>
</function>
<function name="partbymonth"
class="io.mycat.route.function.PartitionByMonth">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2015-01-01</property>
</function>
<function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
<property name="mapFile">partition-range-mod.txt</property>
</function>
<function name="jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash">
<property name="totalBuckets">3</property>
</function>
</mycat:rule>
测试
# 启动MyCat
./mycat start
测试连接
连接MyCat成功 打开表报错或查不到数据,把这里改成true