一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第13天，点击查看活动详情
数据库基本介绍
- 数据库的优点:
- 数据库的存储特点:
- 数据存放到表中,然后将表存放到库中
- 一个库中可以有多张表,每张表有唯一的表名标识
- 表中有一个或者多个列字段
- 表中可以存放多行数据
- 数据库的分类:
- 关系型数据库:
- MySQL
- Oracle
- DB2
- SQL Server
- 非关系型数据库:
- 键值存储的数据库:
- Redis
- Memcached
- MemcacheDB
- 列存储的数据库:
- 面向文档的数据库:
- 图形数据库:
- SQL语言的分类:
- 数据查询语言DQL :
- 数据操作语言DML :
- 数据定义语言DDL :
- create
- alter
- drop
- truncate
- 数据控制语言DCL :
- 事务控制语言TCL :
MySQL数据库基本介绍
MySQL数据库安装
Windows
- 下载MySQL安装包
- 打开MySQL数据库安装包:
- 接受MySQL数据库协议:
- 选择自定义custom安装类型:
- 选择需要安装的功能:
- 完成这些准备工作后,点击安装Install按钮开始安装MySQL数据库:
- 可以在界面上查看安装进度:
- 出现以下界面说明MySQL数据库安装成功:
- MySQL数据库安装完成后,可以进行MySQL数据库实例配置:
- 选择详细配置Detailed Configuration的配置类型:
- 在服务器类型中,选择Developer Machine :
- 选择数据库的作用为Multifunctional Database :
- 选择安装数据库引擎的文件的磁盘:
- 根据数据库链接数选择合适的类型:
- 配置MySQL数据库的网络选项:
- 配置MySQL数据库默认的角色:
- 进行MySQL数据库的Windows安装配置:
- 进行MySQL数据库的安全配置,配置root用户密码:
- MySQL数据库实例配置项配置完成后,点击执行Execute按钮:
- 执行完成以后,点击完成Finish按钮:
- MySQL数据库实例执行完成后,开始进行SQLyog的安装:
- 选择需要安装的SQLyog功能:
- 配置SQLyog的安装路径,配置完成后点击安装按钮:
- 可以在界面上查看SQLyog的安装进度:
- SQLyog安装完成后,点击下一步按钮:
- 这时 ,MySQL数据库最终安装完成:
Linux
- 安装完成Linux系统:
- 使用SecureCRT等工具连接Linux系统,进入控制台:
- 删除Linux系统自带的MySQL数据库:
rpm -qa | grep mysql
rpm -e MySQL数据库名称 --nodeps
yum -y install numactl perl libaio wget
wget https://cdn.mysql.com//Downloads/MySQL-5.6/MySQL-server-5.6.49-1.el6.x86_64.rpm
ll MySQL-server-5.6.49-1.el6.x86_64.rpm
rpm -ivh MySQL-server-5.6.49-1.x86_64.rpm
service mysql start
cat /root/.mysql.secret
mysql -uroot -p初始密码
SET password=password('root');
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root';
FLUSH PRIVILEGES;
EXIT;
/sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
/etc/rc.d/init.d/iptables save
chkconfig --add mysql
chkconfig mysql on
MySQL数据库操作
net start mysql
net stop mysql
mysql -h主机名 -P端口号 -u用户名 -p密码
EXIT;
MySQL数据库备份恢复
数据库导出
mysqldump -uroot -proot oxford user_info > E:/oxford/user_info.sql
mysqldump -uroot -proot oxford > E:/oxford/oxford.sql
数据库导入
mysql -uroot -proot
USE oxford;
SOURCE E:/oxford/user_info.sql
mysql -uroot -proot < E:/oxford/oxford.sql
MySQL数据库控制语言
- 授权权限列表:
- 数据权限:
- SELECT
- INSERT
- UPDATE
- DELET
- FILE
- 结构权限:
- CREATE
- ALTER
- INDEX
- DROP
- CREATE TEMPORARY TABLES
- SHOW VIEW
- CREATE ROUTINE
- ALTER ROUTINE
- EXECUTE
- CREATE VIEW
- EVENT
- TRIGGER
- 管理权限:
- GRANT
- SUPER
- PROCESS
- RELOAD
- SHUTDOWN
- SHOW DATABASE
- LOCK TABLES
- REFERENCES
- REPLICATION CLIENT
- REPLICATION SLAVE
- CREATE USER
- 创建用户:
CREATE USER 用户名@IP地址 IDENTIFIED BY 密码
DROP USER 用户名@IP地址
GRANT 权限1,权限2... ON 数据库名.* TO 用户名@IP地址 IDENTIFIED BY 密码;
REVOKE 权限1,权限2,... ON 数据库名.* FROM 用户名@IP地址 IDENTIFIED BY 密码;
FLUSH PRIVILEGES;
SHOW GRANTS FOR 用户名@IP地址;
SET PASSOWRD=PASSWORD("oxford");
GRANT ALL PRIVILEGES ON *.* TO root@% IDENTIFIED BY oxford;
FLUSH PRIVILEGES;
- 如果忘记登录密码,可以通过以下方式设置新密码登录:
- 在MySQL的配置文件中的mysqld的参数组下添加skip-grant-tables, 表示跳过授权
- 这样重启MySQL再次登录后就不需要密码,进入MySQL账户后修改密码,修改后刷新授权,就可以使用新的密码登录了
- 修改密码完成后,删除配置文件中的skip-grant-tables选项,重新启动MySQL服务
- 使用修改后的新密码即可登录MySQL
MySQL的数据类型
数值型
| 类型 | 字节 |
|---|
| TINYINT | 1 |
| SMALLINT | 2 |
| MEDIUMINT | 3 |
| INT(INTEGER) | 4 |
| BIGINT | 8 |
- 都是可以设置有符号和无符号.默认是有符号的,通过unsigned设置为无符号
- 如果超过字节范围,会抛出out of range异常,插入临界值,也就是这个类型的最大值或者最小值
- 可以不指定长度,会有一个默认长度,这个长度代表显示的最大宽度,如果不够则在左边使用0来填充,需要配置zerofill使用,默认为无符号整型
- 如果对数据没有特殊要求,数值类型推荐使用INT(INTEGER) 类型
浮点型
- 定点数:
- DEC(M,D) : M+2字节
- DECIMAL(M,D) : M+2字节
- 浮点数:
- FLOAT(M,D) : 4字节
- DOUBLE(M,D) : 8字节
- 浮点型特点:
- M表示整数部位和小数部位的个数之和 ,D代表小数部位的个数
- 如果超过字节范围,会抛出out of range异常,并且插入临界值,也就是这个类型的最大值或者最小值
- M和D可以省略.对于定点数,M默认为10,D默认为0
- 如果对数据的精度要求较高,浮点类型推荐使用定点数
字符型
| 类型 | 说明 |
|---|
| BINARY | 二进制 |
| VARBINARY | 二进制字符串 |
| ENUM | 枚举 |
| SET | 集合 |
| TEXT | 文本 |
| BLOB | 二进制大型对象 |
| CHAR(M) | 固定长度的字符 |
| VARCHAR(M) | 可变长度的字符 |
- 固定长度的字符CHAR(M), 最大长度不能超过M. 这里M可以省略不写,默认值为1
- 可变长度的字符VARCHAR(M), 最大长度不能超过M. 这里M不可以省略
- 如果对数据没有特殊要求,字符类型推荐使用VARCHAR(M)
日期型
| 类型 | 说明 |
|---|
| YEAR | 年份 |
| DATE | 日期 |
| TIME | 时间 |
| DATETIME | 日期时间 |
| TIMESTAMP | 日期时间 |
- TIMESTAMP会受到时区,语法模式,版本的影响,更能反映出当前时区的真实时间 .DATETIME只能反映出插入时当时时区的时间
- TIMESTAMP支持的时间范围比较小 .DATETIME支持1000-1-1至9999-12-31的时间范围
- TIMESTAMP的属性受到MySQL版本和SQLMode的影响很大
- 如果对数据没有特殊要求,日期类型推荐使用DATETIME
MySQL数据库约束
- 数据库约束: 数据的一种限制,用于限制数据库表中的数据,保证数据的准确性和可靠性
- 数据库约束分类:
- NOT NULL : 非空.这个字段必须填写有值
- UNIQUE : 唯一.这个字段的值不能重复
- DEFAULT : 默认值.这个字段不手动插入有默认的值
- CHECK : 检查. 这个约束在MySQL中不支持
- PRIMARY KEY : 主键.这个字段的值非空且唯一
- FOREIGN KEY : 外键.这个字段的值引用了另外一个表的字段
- 主键:
- 一个表中至多只能有一个主键
- 主键不可以为空
- 具有唯一性
- 支持组合键,但是不推荐使用
- 唯一:
- 一个表中可以有多个唯一
- 唯一可以为空
- 具有唯一性
- 支持组合键,但是不推荐使用
- 外键:
- 外键用于限制两个表之间的关系从表的字段值引用了主表的字段值
- 外键的字段列和主表被引用的字段列的类型要一致,意义一致.名称可以不同
- 主表中被引用的列要求是一个键key, 通常就是使用主键
- 插入数据时,先插入主表. 删除数据时,先删除从表
- 可以通过以下两种方式来删除主表的记录:
ALTER TABLE user_info ADD CONSTRAINT fk_user_major FOREIGN KEY(majorid) REFERENCE major(id) ON DELETE CASCADE;
ALTER TABLE user_info ADD CONSTRAINT fk_user_major FOREIGN KEY(majorid) REFERENCE major(id) ON DELETE SET NULL;
MySQL数据库事务
- 数据库事务: 一条或者多条SQL语句组成一个执行单位,这一组SQL语句要么都执行,要么都不执行
- 数据库事务的特点 : ACID
- 原子性 Atomicity : 一个事务是不可分隔的整体,要么都执行,要么都不执行
- 一致性 Consistency : 一个事务的执行不能破坏数据库数据的完整性和一致性
- 隔离性 Isolation : 一个事务不会受到其余事务的干扰,多个事务是相互隔离的
- 持久性 Durability : 一个事务如果提交成功,就会永久的持久化到本地
- 数据库事务的分类:
事务并发
- 事务并发问题发生时机: 读问题.多个事务同时操作同一个数据库的相同数据时可能会导致事务并发问题
- 事务并发问题有以下几种: 一个事务对数据进行修改,增加操作,另一个事务对相同的数据进行读操作
- 脏读: 一个事务读取到另一个事务还未提交的UPDATE数据,导致多次查询结果不一致
- 不可重复读: 一个事务读取到另一个事务已经提交的UPDATE数据,导致多次查询结果不一致
- 幻读: 一个事务读取到另一个事务已经提交的INSERT数据,导致多次查询结果不一致
- 可以通过设置隔离级别来解决数据库事务的并发问题
| 隔离级别 | 说明 | 脏读 | 不可重复读 | 幻读 |
|---|
| READ UNCOMMITTED | 读未提交 | × | × | × |
| READ COMMITTED | 读已提交 | √ | × | × |
| REPEATABLE READ | 可重复读 | √ | √ | × |
| SERIALZABLE | 序列化 | √ | √ | √ |
丢失更新
- 丢失更新: 写问题
- 多个事务同时对同一个数据库的相同数据进行修改和增加操作时导致的问题
- 丢失更新问题的解决方法:
- 注意点:
- 账户交易类场景推荐使用悲观锁
- 数据库性能高,并发度不高的场景悲观锁和乐观锁都可以使用
- 交易减库存的场景推荐使用乐观锁,保证并发度
MySQL数据库变量
全局变量
- 全局变量:
- 服务器层面上的,
- 必须拥有super权限才能够为系统变量赋值
- 作用域为整个服务器,针对于所有的会话连接有效,不能跨重启
SHOW GLOBAL VARIABLES;
SHOW GLOBAL VARIABLES LIKE '%char%';
SELECT @@global 系统变量名称;
SET GLOBAL 系统变量名称=值;
SET @@global 系统变量名称=值;
会话变量
- 会话变量:
- 服务器为每一个连接的客户端提供的系统变量
- 作用域为当前的会话连接
SHOW [SESSION] VARIABLES;
SHOW [SESSION] VARIABLE LIKE '%char%';
SELECT [SESSION] 会话变量名称;
SET [SESSION] 会话变量名称=值
用户变量
- 用户变量:
- 用户变量只对当前会话连接生效
- 位置可以在begin和end里面,也可以放在begin和end外面
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
SELECT 值 INTO @变量名 FROM 表;
SELECT 用户变量名称;
MySQL数据库事件
- 数据库事件:
- MySQL 5.1以后推出事件调度器Event Scheduler
- 事件调度器Event Scheduler和事件触发器trigger不同,事件调度器Event Scheduler和Linux Crontab计划任务类似,用于定时触发
- 数据库事件的特点:
- 数据库事件是一组SQL集合.也就是MySQL中的定时器,到指定的时间就会执行
- 事件由一个特定的线程也就是事件调度器来管理.事件不能直接调用,要通过单独的或者调用存储过程使用,在某一个特定的时间点,触发相关SQL语句和存储过程
语法操作
- 创建事件:
- MySQL事件调度器event_scheduler负责调用事件.默认是关闭的
- 这个事件调度器会不断监视一个事件是否需要调用.如果要创建事件,必须首先打开事件调度器
SET GLOBAL event_scheduler=ON;
SET @@global.event_scheduler=ON;
SET GLOBAL event_scheduler=1;
SET @@global.event_scheduler=1;
DELIMITER $
CREATE EVENT 事件名称
ON SCHEDULE 执行时间和频率
DO
BEGIN
...
END$
DELIMITER;
STARTS CURRENT_TIMESTAMP;
STARTS YYYY-MM-DD HH:MM:SS;
- 可以使用ENDS关键字指定结束时间,这是一个可选项:
ENDS CURRENT_TIMESTAMP;
ENDS YYYY-MM-DD HH:MM:SS;
- 可以设定事件EVENT的生命周期,这是一个可选项:
ON COMPLETION NOT PRESERVE;
ON COMPLETION PRESERVE;
ENABLE;
DISABLE;
COMMENT '备注';
SET GLOBAL event_scheduler=OFF;
SET @@global.event_scheduler=OFF;
SET GLOBAL event_scheduler=0;
SET @@global.event_scheduler=0;
DROP EVENT 事件名称;
- 修改事件: 先对事件进行删除,然后再重新创建新的事件.等同于修改事件的操作
- 查看事件:
SHOW EVENTS;
ALTER EVENT 事件名称 ENABLE;
ALTER EVENT 事件名称 DISABLE;
- 注意点:
- 默认创建事件存储在当前库中.也可以创建事件到指定的库中
- 通过SHOW EVENT语句只能查看到当前库中创建的事件
- 事件执行完就释放.比如立即执行事件,在执行完成后,事件就会自动删除,多次调用事件或者等待执行事件可以查看到
- 如果存在两个事件需要在同一时刻调用 ,MySQL会确定事件的调用顺序,如果需要执行调用顺序,需要确保两个事件执行时间差至少为1秒
- 对于递归调度的事件,结束的日期不能在开始日期之前
- 事件中可以包含SELECT语句,但是语句的执行结果消失就类似于未执行过
MySQL数据库分布式操作
MySQL主从复制
- MySQL主从复制:
- MySQL主从复制允许将来自主服务器的MySQL数据库服务器的数据复制到另一个或者多个从服务器的MySQL数据库服务器中
- 只需要在主服务器创建数据库,创建表,添加数据.从节点会自动获取主节点中的内容,从节点和主节点内容保持同步.这就是主从复制的相关内容
主节点配置
vi /usr/my.cnf
log-bin=mysql-bin
binlog-format=row
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
sync_master_info=1
service mysql restart
mysql -uroot -proot
GRANT REPLICATION ON slave ON *.* ON 'root'@'%' IDENTIFIED BY 'root';
FLUSH PRIVILEGES;
SHOW master STATUS\G;
从节点配置
vi /usr/my.cnf
- 在配置文件中的mysqld的分组下面添加以下配置:
log-bin=mysql-bin
binlog-format=row
server-id=2
read_only=1
- 停止并删除旧配置的MySQL数据库:
- 这里不能直接重启MySQL数据库服务器
- 因为两者的auto.cnf的server-uuid是一样的,后续配置可能会失败
- 所以需要删除从库中的server-uuid, 在启动时重新自动生成一个新的server-uuid
service mysql stop
rm -f /var/lib/mysql/auto.cnf
service mysql start
mysql -uroot -proot
CHANGE MASTER TO
MASTER_HOST='192.168.166.168',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=396;
START slave;
- 查看从节点状态:
- 检查Master_Log_File和Read_Master_Log_Pos是否和主节点中的信息一致
- Slave_IO_Running和Slave_SQL_Running是否为YES
SHOW slave STATUS\G;
MySQL读写分离
- MySQL数据库读写分离:
- 通过在一主一从的的基础配置上,添加一个proxysql实现MySQL读写分离
- proxysql支持MySQL协议的数据库代理,程序不会访问MySQL数据库,而是访问proxysql的代理程序
- 用户请求发送到proxysql, 如果是写请求就传递给主节点.如果是读请求就传递给从节点组中,这样的读写分离可以分担主数据库的IO压力
- 一主两从实现读写分离:
从节点配置
service mysql stop
rm -f /var/lib/mysql/auto.cnf
vi /usr/my.cnf
log-bin=mysql-bin
binlog-format=row
server-id=2
read_only=1
service mysql start
mysql -uroot -proot
CHANGE MASTER TO
MASTER_HOST='192.168.166.168',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=396;
START slave;
SHOW slave STATUSs\G;
数据库代理proxysql安装
cat << EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
EOF
yum install -y mysql-libs perl-DBI perl-DBD-MySQL
yum install -y proxysql-2.0.13-1
/sbin/iptables -I INPUT -p tcp --dport 6066 -j ACCEPT
/etc/rc.d/init.d/iptables save
/sbin/iptables -I INPUT -p tcp --dport 6068 -j ACCEPT
/etc/rc.d/init.d/iptables save
chkconfig --add proxysql
chkconfig proxysql on
rm -f /etc/proxysql.cnf
vi /etc/proxysql.cnf
datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"
admin_variables=
{
admin_credentials="admin:admin"
mysql_ifaces="0.0.0.0:6066"
}
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:6068"
default_schema="information_schema"
stacksize=1048576
server_version="5.6.49"
connect_timeout_server=60000
monitor_username="monitor"
monitor_password="monitor"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
mysql_servers=
(
)
mysql_users:
(
)
mysql_query_rules:
(
)
schedules=
(
)
mysql_replication_hostgroups=
(
)
service proxysql start
MySQL数据库安装
rpm -qa | grep mysql
rpm -e MySQL数据库名称 --nodeps
yum -y install numactl perl libaio wget
wget https://cdn.mysql.com//Downloads/MySQL-5.6/MySQL-server-5.6.49-1.el6.x86_64.rpm
ll MySQL-server-5.6.49-1.el6.x86_64.rpm
rpm -ivh MySQL-server-5.6.49-1.el6.x86_64.rpm
service mysql start
cat /root/.mysql_secret
mysql -uroot -p数据库初始密码
SET password=password('root');
EXIT;
主节点配置
- 在主节点为proxysql创建两个账号,创建完成后,账号信息会自动同步到从节点,便于proxysql监控和远程登录的认证连接
SELECT user,host FROM mysql.user;
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%' IDENTIFIED BY 'monitor';
FLUSH PRIVILEGES;
CREATE USER 'proxysql'@'%' IDENTIFIED BY 'proxysql';
GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%' IDENTIFIED BY 'proxysql';
FLUSH PRIVILEGES;
EXIT;
从节点配置proxysql
- 在安装proxysql的服务器上登录MySQL数据库的proxysql的管理账户admin账户:
mysql -uadmin -padmin -h127.0.0.1 -P6066 --prompt 'admin>'
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) VALUES(10,'192.168.206.128',3306,1,1000,10,'write mysql');
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) VALUES(20,'192.168.206.129',3306,1,1000,10,'read mysql');
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) VALUES(20,'192.168.206.130',3306,1,1000,10,'read mysql');
INSERT INTO mysql_users(username,passowrd,active,default_hostgroup,transaction_persistent) VALUES('proxysql','proxysql',1,20,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',10,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',20,1);
LOAD mysql servers TO runtime;
LOAD mysql users TO runtime;
LOAD mysql variables TO runtime;
LOAD mysql query rules TO runtime;
SAVE mysql servers TO DISK;
SAVE mysql users TO DISK;
SAVE mysql variables TO DISK;
SAVE mysql query rules TO DISK;
EXIT;
远程连接proxysql
mysql -uproxysql -pproxysql -h192.168.206.130 -P6068
SHOW DATABASES;
- 使用图形化工具SQLyog连接,注意端口为proxysql的远程管理端口6068 :
查询proxysql监控数据
- 查看SQL语句执行的数据库,可以在proxysql服务器上执行以下语句查询:
mysql -uadmin -padmin -h127.0.0.1 -P6066 -prompt 'admin>'
SELECT * FROM stats_mysql_query_digest;
MySQL分片集群
- MySQL数据库分片集群:
- 随着互联网的发展,数据的量级成指数级增长,从GB到TB到PB. 对数据的操作也更加困难,传统的关系型数据库无法满足快速查询和插入数据的需求.这时非关系型数据库NoSQL暂时解决这一问题,非关系型数据库NoSQL通过降低数据的安全性,减少对事务的支持,减少对复杂查询的支持来获取性能的提升
- 但是在一些绝对要有事务和安全指标等相关场景下,非关系数据库NoSQL是无法支持的,还是需要使用关系型数据库
- 这时就要通过数据库集群来使用关系型数据库解决海量存储的问题:
- 为了提高查询性能将一个数据库的数据分散到不同的数据库中存储
- 通过某种特定的条件,将存放在同一个数据库中的数据分散存放到多个数据库或者主机上,以达到分散单个数据库或者主机的负载
- 这时就需要一个数据库中间件来完成数据库的分片集群,可以使用Mycat
- 数据库中间件Mycat :
- 新型的数据库中间件产品,支持MySQL集群或者MariaDB集群,提供高可用的数据分片集群
- 数据库中间件Mycat可以无感使用
- 支持MySQL, Oracle, SQL Server, PostgreSQL, MongoDB等大部分数据库
分片集群架构
- MySQL数据库分片集群架构: 三组一主两从的服务器,加上一个数据库中间件服务器.总共需要10台服务器
Mycat分片
- 分片: 通过某种特定的条件,将存放在同一个数据库中的数据分散存储到其余多个数据库或者主机上,这样来达到分散单台设备负载的效果
- 数据切分Sharding可以根据划分的类型分为以下两种切分方式:
- 垂直切分: 按照不同的表或者schema来将数据切分到不同的数据库或者主机上
- 水平切分: 根据数据库的表中的数据的逻辑关系,将同一个表中的数据库按照某种条件切分到多台数据库或者主机上
Mycat安装
- 数据库中间件Mycat安装要求:
- JDK : 要求JDK必须是1.7及以上版本
- MySQL: 推荐MySQL使用5.5及以上版本
- 默认端口号: 8066
- 安装JDK :
rpm -qa | grep java
rpm -qa | grep jdk
rpm -qa | grep jdk | xargs rpm -e --nodeps
rpm -qa | grep java | xargs rpm -e --nodeps
yum install -y java-1.8.0-openjdk java-1.8.0-openjdk-devel
ll /usr/lib/jvm/
vi /etc/profile
export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.262.b10-0.el6_10.x86_64
export JRE_HOME=$JAVA_HOME/jre
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/jre/lib/tools.jar:$JRE_HOME/lib:$CLASSPATH
export PATH=$JAVA_HOME/bin:$PATH
source /etc/profile
java -version
wget http://dl.mycat.org.cn/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
mv mycat /usr/local
- 开放Mycat的默认端口8066和管理端口9066 :
/sbin/iptables -I INPUT -p tcp --dport 8066 -j ACCEPT
/sbin/iptables -I INPUT -p tcp --dport 9066 -j ACCEPT
/etc/rc.d/init.diptables save
vi /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.206.128 chova
192.168.206.128 master1
192.168.206.129 slave11
192.168.206.130 slave12
数据库中间件Mycat常用命令
/usr/local/mycat/bin/mycat start
/usr/local/mycat/bin/mycat stop
/usr/local/mycat/bin/mycat console
/usr/local/mycat/bin/mycat restart
/usr/local/mycat/bin/mycat pause
/usr/local/mycat/bin/mycat status
Mycat配置
创建数据库
- 首先在主服务器上创建三个数据库db1,db2,db3 :
CREATE DATABASE 'db1' CHARACTER SET utf8;
CREATE DATABASE 'db2' CHARACTER SET utf8;
CREATE DATABASE 'db3' CHARACTER SET utf8;
修改schema.xml文件
rm -f /usr/local/mycat/conf/schema.xml
vi /usr/local/mycat/conf/schema.xml
<?xml version="1.0">
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="STOREDB" checkSQLschema="false" sqlMaxLimit="100">
<!-- 规则采用按照主键范围分片,主键名为ID -->
<table name="TB_USERS" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
<!-- 规则采用按哈希一致分片,主键名为自定义order_id -->
<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="sharding-by-murmur-order" />
</schema>
<dataNode name="dn1" dataHost="OneMasterAndTwoSlave" database="db1" />
<dataNode name="dn2" dataHost="OneMasterAndTwoSlave" database="db2" />
<dataNode name="dn3" dataHost="OneMasterAndTwoSlave" database="db3" />
<dataHost name="OneMasterANdTwoSlave" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<!-- 心跳语句 -->
<heartbeat>select user()</heartbeat>
<!-- 一主两从 -->
<writeHost host="hostM1" url="master1:3306" user="root" password="root">
<readHost host="hostS1" url="slave11:3306" user="root" password="root" />
<readHost host="hostS2" url="slave12:3306" user="root" password="root" />
</writeHost>
</dataHost>
</mycat:schema>
修改server.xml文件
- 在server.xml文件中保存数据库中间件Mycat的所有系统配置信息,可以在server.xml文件中配置用户名,密码和权限
vi /usr/local/mycat/conf/server.xml
<property name="charset">utf8</property>
<user name="root">
<property name="password">root</property>
<property name="schemas">STOREDB</property>
</user>
修改auto-sharding-rang-mod.txt文件
rm -f /usr/local/mycat/conf/auto-sharding-rang-mod.txt
vi /usr/local/mycat/conf/auto-sharding-rang-mod.txt
0-500M=0
500M1-1000M=1
1000M1-1500M=2
修改rule.xml文件
- 修改数据库中间件Mycat中的规则文件rule.xml :
vi /usr/local/mycat/conf/rule.xml
- 新增哈希订单主键:
- 默认的主键名为id, 如果需要自定义主键,就需要拷贝一份,重新命名
- 修改columns为数据库表中的主键名称
<tableRule name="sharding-by-murmur-order">
<rule>
<columns>order_id</columns>
<algorithm>murmur</algorithm>
</rule>
</tableRule>
- 修改分片数量:
- 这时存在db1,db2,db3三个分片
- 将 < function name="murmur" > 中的count数量修改为3
<function name="murmur" class="io.mycat.route.function.PartitionByMurmurHash">
<property name="seed">0</property>
<property name="count">3</property>
<property name="virtualBucketTimes">160</property>
<property name="weightMapFile">weightMapFile</property>
<property name="bucketMapPath">/etc/mycat/bucketMapPath</property>
</function>
Mycat启动
- 依次在主节点和各个从节点登录MySQL数据库服务器后执行以下语句:
USE mysql;
UPDATE user SET password=password('root') WHERE user='root';
FLUSH PRIVILEGES;
SET password FOR 'root'=password('root');
SET password=password('root');
- 在安装了数据库中间件Mycat的服务器上启动Mycat :
/usr/local/mycat/bin/mycat start
总结
- 数据库中间件Mycat只是一个数据库中间件,不是一个真正的数据库.所以需要使用SQL语句操作,不能使用窗口直接操作
- 数据库中间件Mycat依赖JDK 1.7以上的版本,推荐使用JDK 1.8
- 数据库中间件Mycat只是完成数据分片的操作,主从复制还是要自定义实现
- 在创建表的时候,数据库中间件Mycat默认会将表名转换为大写,所以创建时就应该直接将表名和数据库的名称大写,否则可能因为大小写不对应而找不到对应的数据库和表
- 如果数据库表的主键字段不是ID时,直接在ruls.xml中拷贝一份对应类型的tableRule,然后修改规则名称和字段名称,再引用自定义的字段名即可
