Mysql(linux版)之体系结构和基础管理

MySQL C/S结构介绍

两种连接方式：
TCP/IP：mysql -uroot -poldboy123 -h 10.0.0.51 -P3306
Socket：mysql -uroot -poldboy123 -S /tmp/mysql.sock
我们通常用的是第一种登陆方式。

MySQL实例的构成

公司： 老板 + 经理 + 员工 + 办公区
实例： mysqld + master thread + 干活的Thread + 预分配的内存

如上图所示，mysqld是一个守护进程，7*24小时在内存工作，等着文件来，然后处理文件，内存里面的那一堆东西就是mysql的一个实例。

mysqld程序运行原理

mysqld程序结构

连接层
（1）提供连接协议：TCP/IP 、SOCKET
（2）提供验证：用户、密码，IP，SOCKET
（3）提供专用连接线程：接收用户SQL，返回结果
通过以下语句可以查看到连接线程基本情况
mysql> show processlist;

SQL层
（1）接收上层传送的SQL语句
（2）语法验证模块：验证语句语法,是否满足SQL_MODE
（3）语义检查：判断SQL语句的类型
DDL ：数据定义语言
DCL ：数据控制语言
DML ：数据操作语言
DQL： 数据查询语言
（4）权限检查：用户对库表有没有权限
（5）解析器：对语句执行前,进行预处理，生成解析树(执行计划),说白了就是生成多种执行方案.
（6）优化器：根据解析器得出的多种执行计划，进行判断，选择最优的执行计划,我们要考虑优化器的感受，写出合适的sql。
代价模型：资源（CPU IO MEM）的耗损评估性能好坏
（7）执行器：根据最优执行计划，执行SQL语句，产生执行结果
执行结果：在磁盘的xxxx位置上
（8）提供查询缓存（默认是没开启的），会使用redis tair替代查询缓存功能
（9）提供日志记录（日志管理章节）：binlog，默认是没开启的。

存储引擎层
负责根据SQL层执行的结果，从磁盘上拿数据。
将16进制的磁盘数据，交由SQL结构化化成表，
连接层的专用线程返回给用户。

逻辑结构

库包含库名，库属性。
表包含表名，属性列:列名(字段),列属性(数据类型,约束等)，数据行(记录)

物理存储结构

库的物理存储结构
用文件系统的目录来存储

表的物理存储结构
MyISAM(ext2)
user.frm ： 存储的表结构（列，列属性）
user.MYD : 存储的数据记录
user.MYI ： 存储索引

InnoDB(XFS)
time_zone.frm ： 存储的表结构（列，列属性）
time_zone.ibd ： 存储的数据记录和索引
ibdata1 : 数据字典信息（表属性，元数据）

innodb 段 区 页
一般情况下（非分区表）
一个表就是一个段
一个段由多个区构成
一个区在（16k），64个连续的页，1M大小

用户和权限管理

用户的定义

用户名@‘白名单’
wordpress@’%’
wordpress@‘localhost’
wordpress@‘127.0.0.1’
wordpress@‘10.0.0.%’
wordpress@‘10.0.0.5%’
wordpress@‘10.0.0.0/255.255.254.0’
wordpress@‘10.0.%’

用户的操作

建用户
mysql> create user oldboy@‘10.0.0.%’ identified by ‘123’;
Query OK, 0 rows affected (0.00 sec)

说明：
8.0以前，可以自动创建用户并授权
mysql> grant all on . to oldguo@‘10.0.0.%’ identified by ‘123’;

查询用户
mysql> select user,host from mysql.user;

修改用户密码
mysql> alter user oldguo@‘10.0.0.%’ identified by ‘123456’;

删除用户
mysql> drop user oldguo@‘10.0.0.%’ ;

权限管理

权限列表
ALL:
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
ALL : 以上所有权限，一般是普通管理员拥有的
with grant option：超级管理员才具备的，给别的用户授权的功能

授权命令
grant all on . to oldguo@‘10.0.0.%’ identified by ‘123’ with grant option;

grant 权限 on 作用目标 to 用户 identified by 密码 with grant option;

grant SELECT,INSERT, UPDATE, DELETE, CREATE on wordpress.* to

授权需求

1. 创建一个管理员用户root，可以通过10网段，管理数据库.
   grant all on . to root@‘10.0.0.%’ identified by ‘123’ with grant option;

2. 创建一个应用用户wordpress，可以通过10网段，wordpress库下的所有表进行SELECT,INSERT, UPDATE, DELETE.
   grant SELECT,INSERT, UPDATE, DELETE on wordpress.* to wordpress@‘10.0.0.%’ identified by ‘123’;

回收权限
show grants for wordpress@‘10.0.0.%’;
mysql> revoke delete on wordpress.* from ‘wordpress’@‘10.0.0.%’;
mysql> show grants for wordpress@‘10.0.0.%’;

关于生产中开用户
（1）如何沟通开用户

1. 是否有邮件批复
2. 对哪些库和表做操作
3. 做什么操作
4. 从什么地址来登录
   （2）开发人员找你要root用户密码？
5. 走流程拒绝他
6. 如果是金融类的公司
   （1）原则上是不允许任何非DBA人员持有或申请root
   （2）如果有人私下索要root密码，及时举报。

MySQL的启动和关闭

1.1 日常启停
mysql.server start —> mysqld_safe —> mysqld
mysql.service —> mysqld
需要依赖于 /etc/my.cnf
上面两种方式都只能start，stop这些，并不能加参数。
1.2 维护性的任务
mysqld_safe --skip-grant-tables --skip-networking &
我们一般会将我们需要的参数临时加到命令行.
也会读取/etc/my.cnf的内容,但是如果冲突,命令行优先级最高
下面还有一种启动关闭的方法，看下面两行：
[root@db01 ~]# mysqld_safe &
[root@db01 ~]# mysqladmin -uroot -p123 shutdown

初始化配置

作用
(1)影响数据库的启动
(2)影响到客户端的功能

初始化配置的方法
(1)初始化配置文件(例如/etc/my.cnf)
(2)启动命令行上进行设置(例如:mysqld_safe mysqld)
(3)预编译时设置(仅限于编译安装时设置)

初始化配置文件的书写格式
[标签]
xxx=xxx
[标签]
xxx=xxx

配置文件标签的归类
服务器端:
[mysqld]
[mysqld_safe]
[server]

客户端:
[mysql]
[mysqladmin]
[mysqldump]
[client]

配置文件设置样板(5.7)
#服务器端配置
[mysqld]
#用户
user=mysql
#软件安装目录
basedir=/application/mysql
#数据路径
datadir=/data/mysql/data
#socket文件位置
socket=/tmp/mysql.sock
#服务器id号
server_id=6
#短口号
port=3306
#客户端配置
[mysql]
#socket文件位置
socket=/tmp/mysql.sock
【注】客户端服务端socket目录要一致。

配置文件读取顺序
[root@db01 ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
从上可看出，上面的几个位置都是mysqld自动读取配置文件的位置，从左向右一次读取。

强制使用自定义配置文件

–defautls-file (可强制指定读取哪个配置文件)
[root@db01 tmp]# mysqld_safe --defaults-file=/tmp/aa.txt &

MySQL的连接管理

mysql 命令
注意:提前应该将用户授权做好
mysql> grant all on . to root@‘10.0.0.%’ identified by ‘123’;
TCPIP :
mysql -uroot -p -h 10.0.0.51 -P3306
本地登录host就是localhost

Socket
mysql -uroot -p -S /tmp/mysql.sock
【注】如果-S和-h一起出现，则以tcp\ip为主。
客户端工具
sqlyog
navicat

多实例管理

准备多个目录
mkdir -p /data/330{7,8,9}/data

准备配置文件
cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF

cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF

cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF

初始化三套数据

mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/application/mysql

systemd管理多实例
cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service

vim mysqld3307.service
修改为:
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
vim mysqld3308.service
修改为:
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
vim mysqld3309.service
修改为:
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf

[root@db01 system]# grep “ExecStart” mysqld3309.service
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
[root@db01 system]# grep “ExecStart” mysqld3308.service
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
[root@db01 system]# grep “ExecStart” mysqld3307.service
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
[root@db01 system]#

授权
chown -R mysql.mysql /data/*
启动
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service

验证多实例
netstat -lnp|grep 330
mysql -S /data/3307/mysql.sock -e “select @@server_id”
mysql -S /data/3308/mysql.sock -e “select @@server_id”
mysql -S /data/3309/mysql.sock -e “select @@server_id”