企业架构单点Mysql数据库服务器

学习目标和内容

  1. 能够实现虚拟机的克隆复制
  2. 能够配置新克隆机的网卡并启动
  3. 能够实现业务数据库的迁移
  4. 能够实现业务代码修改连接数据库
  5. 能够部署配置phpMyAdmin工具
  6. 能够了解MySQL优化方向

一、背景描述及其方案设计

1、业务背景描述

时间:2009.6-2010.9

发布产品类型:互联⽹动态站点 商城

⽤户数量: 2000-4000(⽤户量猛增 翻了4倍)

PV : 8000-50000(24⼩时访问次数总和)

QPS: 50-100*(每秒访问次数)

DAU: 200-400(每⽇活跃⽤户数)

数据量翻倍了,使用数据库的次数增多,数据库连接也变多了。之前LNMP单点服务器机构中,ningx、php、mysql之间存在资源(cpu、内存)争抢[等待],最终,用户访问请求返回时间变长了,用户直观体验,网站访问慢了。

模拟运维设计方案

之前已经搭建好的业务架构

升级后的业务架构

二、数据服务器迁移部署

1、克隆复制虚拟机

2、配置FQDN

①/etc/hosts

②/etc/sysconfig/network

③查看一下配置效果

hostname hostname -f

配置新虚拟机的网卡

问题:

解决方案

配置这个文件

/etc/sysconfig/network-scripts/ifcfg-eth0

重启服务器

shell > reboot

数据迁移

数据迁移步骤:

①源数据库服务器导出数据

②导入数据到新数据库服务器

数据备份文件获取

业务代码配置连接数据库

mysql数据库迁移到其他服务器,WEB服务器里的mysql应该停止了,业务代码也需要切换到新的单点mysql数据库服务器了

①停止到server01 web服务器的mysql服务

②业务代码连接数据库配置到新的数据库服务器

问题:

mysql默认不允许远程登录,mysql.user表里有个Host字段,可以进行控制,需要改为%[任意匹配]

解决方案:

Tip:mysql host限制和套接字连接

MySQL相关命令

CURD 它代表创建(Create)、更新(Update)、读取(Retrieve)和删除(Delete)操作

数据库操作流程:database(数据库)=>table(数据表)=>column(列[字段]

6.1、创建语句

列类型(字段)

数字类型:

整数类型 TINYINT SMALLINT MEDIUMINT INT BIGINT

浮点数类型 FLOAT DOUBLE

定点数类型 DECIMAL

位类型 BIT

文本类型:

CHAR系列 CHAR VARCHAR

TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT

BLOB(binary large object) 系列

TINYBLOB BLOB MEDIUMBLOB LONGBLOB

BINARY系列 BINARY VARBINARY

枚举类型: ENUM [单选]

集合类型: SET [多选,复选]

时间和日期类型: DATE TIME DATETIME(Y-M-D H:i:s) TIMESTAMP YEAR

存储引擎:

汽车引擎 起速快 跑的也快 拉不太重的东西

拖拉机引擎 起速慢 跑的也慢 拉的东西可以拉很重

物理小知识:做功的方式不同,轮胎不一样

mysql中的存储引擎,也是一样,不同的存储引擎,数据的存储方式和功能都不同,合理选择对应的存储引擎,是数据库设计和优化中,重要的部分。

常用的

MyISAM 一般的是默认的

InnoDB 并发写入更好一些,可以进行行级别锁

创建一个表

6.2、插入语句

语法:

insert into tablename values (xx,xx,xx);

6.3、查询语句

语法:

select 查询显示的列 from 表名称 [where][limit][order by];

like查询模糊查询

like introduce ‘%服务器%’

6.4、修改语句

语法:

update 表名称 set 表字段 [where] 查询条件;

6.5、删除语句

基本上实际业务环境下不会使用

语法:

delete from 表名称 [where] 查询条件

不管是修改还是删除语句,最好在where条件判断之后加上一个limit 1,这样即使条件出现的了错误,也只会影响一条。

6.6、系统信息

查看数据库版本

mysql > select version();

查看当前登录用户

mysql > select user();

查看数据库运行时间

mysql > show global status like 'uptime';

查询数据库链接数

mysql > show processlist;

三、phpMyAdmin部署使用

1、介绍

phpmyadmin web端方式,管理mysql数据库的软件工具

要求:由php开发,需要服务器已经安装好PHP

官方网址:https://www.phpmyadmin.net/

client/s browser/s

上传到服务器

server02 MySQL服务器,把相关的工具在server02使用

上传软件包

解压改名

修改名称路径最好不要直接修改为phpmyadmin,隐晦一点儿,让别人猜不到是什么。

mymymysql

配置

需要使用ip和目录方式访问

默认访问第一个虚拟机,localhost,目录在html下

登录查看

也可以设置一个域名进行访问

注意同时解析域名操作

登录后操作管理平台

如果phpmyadmin的web服务器和mysql服务器不在同一上。就需要修改一下相关配置

①复制示列配置文件

shell > cd /usr/local/nginx/html/mymymysql

shell > cp config.sample.inc.php config.inc.php

②修改对应的host参数

更加详细的配置选项 具有所有的配置项

一般做法是,使用默认phpmyadmin根目录下的config.inc.php最小化配置

如果需要的配置项没有存在于config.inc.php中,就从原始的配置文件phpmyadmin的网站根目录/libraries/config.default.php复制出来,进行修改。

简单使用

案例一:创建一个简单表

案例二:CURD操作

MySQL优化方向介绍(扩展)

DBA数据库管理员和开发一般需要掌握的

1、优化方向

问:如果出现数据访问变慢的情况,试提出解决方案或者优化方向,如何优化mysql?

①存储层 存储引擎 列类型选择 范式(三范式[约束规范])

②设计层 索引 缓存 分区表

③sql层 使用执行效率高的sql语句 explain 执行计划 慢查询日志记录

④架构层 分布式数据库架构 使用多台数据库服务器,解决数据库访问并发的问题

主从复制

课程进行到以上部分

2、索引

生活中实际索引的体现

书的目录 指引

公交站牌

办公室的指引牌

索引是一种数据结构(存储数据的方式),存储字段值的内容和对应真实数据的物理地址。

查询数据通过索引查询到物理地址,再通过物理地址直接定位数据。

索引是一种以空间换取时间的方式,牺牲了写的速度,提高查询速度。

案例:是否使用索引的区别

上传数据文件到数据目录下

添加一个主键(默认主键索引)

mysql > alter table emp add primary key(empno);

增加之后测试查询返回时间

索引的类型:

mysql中使用关键字 key index 标识索引

主键索引 primary key 主键约束 唯一性 不能为null

唯一索引 unique key 唯一约束 唯一性 可以为null

普通索引 key

全文索引 fulltext key 只支持文本类型(char varchar text),文本中的内容进行分词,分别建立索引 mysql5.6以下是innodb不支持 官方全文索引不使用,使用第三方工具

sphinx(搜索分词)、xunsearch(讯搜)、luncene(分词索引)、solr、elasticsearch

特殊索引类型:

复合(联合)索引 多个字段共同组成一个索引 查询条件有多个时,可以使用到

前缀索引

创建索引的方式:

①建立表结构设计索引

create table 表名(

字段~~~~~~~~~

primary key (id),

unique index 名字,

index 名字(字段),

index 名字(字段,字段), --复合索引

fulltext index 名字(字段);

)

②使用修改结构语法的语句(alter table)

alter table 表名

add primary key (id),

add unique key名字(字段),

add key|index 名字(字段),

add key 名字(字段,字段), --复合索引

add fulltext key 名字(字段);

创建索引

增加一个普通索引和复合索引

删除索引

alter table 表名称 drop key索引名称

主键 alter table 表名称 drop primary key

缓存

缓存:

获取数据经过sql解析等一系列操作才可以获取,如果查询时间较长,每次都从头开始解析执行获取,很浪费时间。

本次查询完毕,如果数据没有改变,就没有必要重新再查一次。把之前查询后的结果,存储起来,这个结果就是一个缓存。

需要经过一系列操作得到的结果存储起来,之后得到结果直接返回。

①查看并开启缓存

临时生效

mysql > show variables like 'query_cache%';

mysql > set global query_cache_size=64*1024*1024;

永久生效

 vim /etc/my.cnf

[mysqld]中添加:

query_cache_size = 64M

query_cache_type = ON

修改配置文件之后,需要重启mysqld服务

②测试缓存后的查询效果

③查看缓存使用的状态

mysql > show status like 'Qcache%';

④清空缓存

mysql > reset query cache;

注意:

①如果sql语句不想使用缓存在语句前加入sql_no_cache

②如果数据表发生了改变,缓存失效

③SQL区别大小写 会被认为是不同sql

④SQL中如果有随机的函数,缓存使用不到

SQL语句

建立索引是为了能够提高查询效率。检查索引是否使用到。

explain执行计划,不会真实执行,分析sql语句执行的过程和使用的资源。

别名语法desc

语法:desc|explain 执行的sql语句 \G;

分库、分表

如果数据表数量在千万级别,通过增加索引和缓存,就可以达到很不错的效果了。

当数据表单表亿级别,通过把表拆分进行存储,分表操作。

分库:一个业务分配一库

分区、分表类型:

①逻辑分区 分表 真实还是一个表 逻辑分为多个 使用的sql语句和单表相同

key/hash range/list

②物理分表 把数据分配到几个真实数据表中,sql语句需要确定操作哪个表

案列一:逻辑分区

语法:

create(

字段 类型

……

)

partition by key (字段) partitions 分区数目

案列二:物理分表 (水平分表)

案列三:物理分表(垂直分表)

user表

id username password sex hobby cardid

需求:如果进行用户登录操作,需要查询id username password

解决方案:把user表通过常用的字段和不太常用的字段,分割为两个表

user表

id username password

user_ext表

id sex hobby cardid user_id