1 MySQL数据库 介绍 安装(CentOS 8) 体系结构和基础管理 DDL应用

MySQL数据库

1 数据库的介绍

分类：

1. RDBMS
   关系数据库管理系统，Relational Database Management System
   代表：MySQL(核心数据)
2. NoSQL
   NoSQL，(Not Only SQL、Non-relational SQL)，泛指非关系型的数据库。
   代表：Redis(缓存)、MongoDB(索引数据)、ES(搜索框全文检索)
3. NewSQL
   NewSQL是对各种新的可扩展/高性能数据库的简称，这类数据库不仅具有NoSQL对海量数据的存储管理能力，还保持了传统数据库支持ACID和SQL等特性。
   代表：PolarDB(阿里云)、TiDB

2 MySQL的介绍和安装配置

2.1 版本选择

5.5
5.6：目前基本不会再发布更新
5.7：主流版本，5.7.28 + 双数版本
8.0：次主流版本 ，8.0.18 + 双数版版

2.2 Linux安装MySQL

2.2.1 准备

Linux系统：CentOS-8.2.2004-x86_64
MySQL：mysql-8.0.21-linux-glibc2.12-x86_64
这里选择 Linux-Generic

2.2.2 安装

1. 创建存放数据的目录和存放软件的目录

mkdir -p /data/3306/data /data/app

2. 创建mysql用户

useradd mysql

3. 上传软件到/data/app，解压，创建软连接

rz -E

tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.7.30-linux-glibc2.12-x86_64 mysql

4. 修改环境变量

vim /etc/profile

添加一行

export PATH=/data/app/mysql/bin:$PATH 

使配置生效

source /etc/profile

解决错误

mysql -V
mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory

find / -name "libtinfo*"
/usr/lib64/libtinfo.so.6
/usr/lib64/libtinfo.so.6.1

CentOS 8中需要做系统库文件软链接。

ln -s /usr/lib64/libtinfo.so.6 /usr/lib64/libtinfo.so.5

测试

mysql -V
mysql  Ver 8.0.21 for Linux on x86_64 (MySQL Community Server - GPL)

5. 数据初始化

mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data

如果初始化的目录非空，会报错。

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
[ERROR] --initialize specified but the data directory has files in it. Aborting.
[ERROR] Aborting

解决错误

rm -rf /data/3306/data/*
mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data

查看启动所需数据

cd /data/3306/data/
ls

 auto.cnf          client-key.pem       ibdata1         mysql                public_key.pem    undo_001
 ca-key.pem       '#ib_16384_0.dblwr'   ib_logfile0     mysql.ibd            server-cert.pem   undo_002
 ca.pem           '#ib_16384_1.dblwr'   ib_logfile1     performance_schema   server-key.pem
 client-cert.pem   ib_buffer_pool      '#innodb_temp'   private_key.pem      sys

6. 准备配置文件

vim /etc/my.cnf

[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

7. 准备启动脚本
   为目录设置权限

chown -R mysql.mysql /data

简化操作

cp /data/app/mysql/support-files/mysql.server /etc/init.d/mysqld 

8. 操作

/etc/init.d/mysqld start
/etc/init.d/mysqld stop
/etc/init.d/mysqld restart
/etc/init.d/mysqld status

登录mysql

mysql

3 MySQL体系结构和基础管理

3.1 MySQL体系结构-C/S连接模型

模型分为客户端和服务端两部分。

3.1.1 客户端

1. 本地套接字文件
   仅限于本地连接，/tmp/mysql.sock
2. TCP/IP网络Socket
   IP+PORT建立网络连接

3.1.2 连接

1. 使用自带的客户端命令；
2. 开发工具，例如sqlyog、navcat、workbench；
3. 编写程序连接，例如pymysql。

3.2 MySQL服务端

3.2.1 实例的构成

MySQL属于单进程多线程工作模式，即存在守护进程。
实例：
mysqld(进程) + THREADS(线程：Master Thread、IO、SQL、Purge…) + 专用的(私有的)内存结构(InnoDB Buffer Pool、InnoDB Log Buffer…)

3.2.2 服务器端体系结构

Server层：连接层、SQL层
Engine层(存储引擎层)

用户向服务端发送访问数据库的请求。

3.2.2.1 连接层

1. 连接数据库
   提供连接协议，接受来自客户端的信息，包括IP、port、username、password…
2. 利用授权表进行用户验证；
3. 生成连接线程。

mysql> show processlist;

3.2.2.2 SQL层

SQL层负责处理连接后用户的操作。
SQL层也会生成专用的SQL处理线程。

1. 语法语义检查，用户权限检查；
2. 解析器，生成解析树；
3. 优化器，生成最终执行计划；
4. 执行器，执行SQL语句，找到目标数据在磁盘上的存储位置(page)；
5. 将执行结果交给Engine层。

3.2.2.3 Engine层

1. Engine层生成IO线程，接受到SQL层的请求;
2. 按照执行方案获取数据。

3.2.2.4 分析

如果某条SQL语句执行速度慢，分析问题。
执行速度慢，前提是已经完成连接，说明问题不在连接层。

SQL层中，解析优化执行过程可能影响执行速度。
IO层中，IO过程会影响执行速度。

数据的查询量级、查询条件的精细程度，是否走索引都会影响IO处理速度。

3.3 MySQL基础管理

3.3.1 用户管理

用户定义：“用户名”@“白名单”

白名单指的是IP地址列表。

'oldguo'@'10.0.0.10'
'oldguo'@'db01'  # 需要解析
'oldguo'@'10.0.0.%' # 默认24位掩码
'oldguo'@'10.0.0.5%'
'oldguo'@'10.0.0.0/255.255.254.0'  # 指定23位掩码
'oldguo'@'%'  
'oldguo'@'localhost'  # 本地用户白名单

mysql> select user, host, authentication_string, plugin from mysql.user;

+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user             | host      | authentication_string                                                  | plugin                |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| root             | localhost |                                                                        | caching_sha2_password |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+

user - 用户名
host - 白名单
authentication_string - 密码
plugin - 加密插件

创建用户，设置密码123

mysql> create user testuser@'10.0.0.%' identified by '123';

登录用户

mysql> mysql -utestuser -p123 -h 10.0.0.101;

修改用户

mysql> alter user root@'localhost' identified by '123';

删除用户

mysql> drop user testuser@'10.0.0.%';

8.0版本前，在使用grant对用户授权时，如果用户不存在会自动创建。

mysql> grant all on *.* to test@'%' identified by '123';

语句中all指的是权限，on *.*指的是所有库中的所有表。

8.0版本及以后，必须先建立用户再授权。

8.0版本及以后采用了全新的加密插件(sha2)，可能导致一些程序无法连接。
解决方法，指定使用旧的加密方式创建用户。

mysql> create user testuser@'10.0.0.%' identified with mysql_native_password by '123';

3.3.2 权限管理

授权命令

mysql> grant all on *.* to root@'%' identified by '123';

语句中all指的是权限，all指的是除了grant option(为其他用户授权)的所有权限。

3.3.2.1 权限定义

查看权限

mysql> show privileges;

1. All
   all指的是除了grant option(为其他用户授权)的所有权限；
2. Usage
   只允许连接，没有任何权限，新建用户的初始权限。

3.3.2.2 权限作用范围

. ：全局，一般针对管理员
testdb.*：针对普通用户
testdb.t1：针对普通用户

3.3.2.3 权限管理

授予权限

# 管理用户
mysql> grant all on *.* to oldguo@'10.0.0.%' identified by '123';

# 业务用户
mysql> grant select, delete, update, insert on testdb.* to oldzhang@'10.0.0.%' identified by '123';

回收权限

mysql> revoke delete on testdb.* to oldzhang@'10.0.0.%' identified by '123';

查询权限

mysql> show grants for oldzhang@'10.0.0.%';

3.3.3 初始化配置

3.3.3.1 方式

源码编译安装
配置文件
命令行参数（优先级最高）

3.3.3.2 配置文件默认读取顺序

mysqld --help --verbose|grep my.cnf

/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
                      my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default

说明： 如果在启动时（命令行mysqld、mysqld_safe），添加了–defaults-file=xxx ，就会已此文件作为默认配置文件。

3.3.3.3 配置文件格式

[标签项]
配置=xxx 

标签项，又分为了两种：
[服务器端标签]：[mysqld] [mysqld_safe]…[server]
[客户端标签]：[mysql] [mysqldump]…[client]

基础配置

cat /etc/my.cnf

[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

3.3.4 MySQL的启动和关闭

3.3.4.1 启动

方式1：mysqld

mysqld &

方式2：mysqld_safe

mysqld_safe &

方式3：

service mysqld stop
Shutting down MySQL.. SUCCESS! 

service mysqld start
Starting MySQL. SUCCESS! 

service mysqld status
SUCCESS! MySQL running (13317)
 
ls /etc/init.d/mysqld 
/etc/init.d/mysqld

方式 4：

systemctl enable mysqld
mysqld.service is not a native service, redirecting to systemd-sysv-install.
Executing: /usr/lib/systemd/systemd-sysv-install enable mysqld

systemctl restart mysqld

3.3.4.2 关闭

方式1：

service mysqld stop
systemctl stop mysqld

方式2：登陆后关闭

mysqladmin -uroot -p123 shutdown

3.3.4.3 维护案例 root@'localhost’密码找回

1. 正常关闭数据库

/etc/init.d/mysqld stop

2. 启动到安全模式
   跳过授权表，防止远程用户连接数据库，实现本地免密登录。

mysqld_safe --skip-grant-tables --skip-networking &

3. 手工刷新授权表 ，修改密码

mysql> alter user root@'localhost' identified by '123456';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user root@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

4. 正常重启数据库

3.3.5 MySQL连接管理

3.3.5.1 方式一 本地套接字文件连接

cat /etc/my.cnf

[mysqld]
socket=/tmp/mysql.sock

前提：必须提前创建好localhost的用户。

mysql -uroot -p123456 -S /tmp/mysql.sock

3.3.5.2 方式二 TCP/IP网络连接

前提： 必须提前创建好远程能够连接的用户。

mysql -uoldguo -p123 -h10.0.0.151 -P3306

3.4 MySQL多实例应用

多实例指的是一台服务器上启动多个MySQL管理多套数据。

3.4.1 规划

3307    ---> /data/3307/data
3308    ---> /data/3308/data
3309    ---> /data/3309/data

3.4.2 创建多个目录

mkdir -p /data/330{7..9}/data

3.4.3 初始化数据

mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3307/data
mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3308/data
mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3309/data

3.4.4 准备配置文件

vim /data/3307/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3307/data
socket=/tmp/mysql3307.sock
server_id=7
port=3307

vim /data/3308/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3308/data
socket=/tmp/mysql3308.sock
server_id=8
port=3308

vim /data/3309/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3309/data
socket=/tmp/mysql3309.sock
server_id=9
port=3309
[mysql]
socket=/tmp/mysql3309.sock

3.4.5 启动多实例

授权

chown -R mysql.mysql /data

启动时指定配置文件

mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &

查询端口

netstat -tulnp

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      916/sshd            
tcp6       0      0 :::22                   :::*                    LISTEN      916/sshd            
tcp6       0      0 :::3306                 :::*                    LISTEN      14379/mysqld        
tcp6       0      0 :::3307                 :::*                    LISTEN      14692/mysqld        
tcp6       0      0 :::3308                 :::*                    LISTEN      14871/mysqld        
tcp6       0      0 :::3309                 :::*                    LISTEN      15023/mysqld  

3.4.6 测试连接

mysql -S /tmp/mysql3307.sock 
mysql -S /tmp/mysql3308.sock 
mysql -S /tmp/mysql3309.sock

4 SQL应用

4.1 介绍

1. 什么是SQL？
   SQL，Structured Query Language，结构化查询语言，是关系型数据库的通用语言，符合SQL的标准：SQL89、92(经典)、99、03。

2. SQL常用种类
   DDL：数据定义语言(Data Definition Language)，对数据库中的对象（库、表、视图、索引、过程函数、事件、触发器…）进行创建、删除、修改等操作。
   DCL：数据控制语言(Data Control Language)，权限设定。
   DML：数据操作语言(Data Manipulation Language)，对表中数据行的增、删、改、查。

3. 预备知识

数据类型
数字：整数、小数
字符串：varchar char enum set
时间：datetime timestamp
二进制：不建议使用
json：文档类型

选择的基准：合适、简短、足够

字符集
utf8：最多存储3字节的字符
utf8mb4：最多存储4字节的字符，例如emoji表情字符

校对（排序）规则
处理字符集的排序规则。

约束
PK 主键
NN 非空
UK 唯一索引
FK 外键
FK会产生额外的锁(例如主从关系中)，产生额外的开销，降低执行效率。
在一些复杂的集群(例如分布式集群)中可能禁止使用FK。
在一些非结构化数据中一般不使用外键关系，通过数据冗余的方式(会违反第三范式)来设定表结构。

属性
default
auto_incremant
comment

4.2 DDL应用

4.2.1 库的DDL

# 建库，建议显式地指定字符集
mysql> create database oldboy charset utf8;

# 修改库，不要出现从大字符集向小字符集切换
mysql> alter database oldboy charset utf8mb4;

# 删库
mysql> drop database oldboy;

# 查询库信息
mysql> show databases;  # 库名
mysql> show create database oldboy;  # 建库语句

4.2.2 表的DDL

建表

mysql> create table student (
	id int not null primary key auto_increment comment '主键',
	name varchar(20) not null comment '姓名',
	age tinyint unsigned not null comment '年龄',
	gender char(1) not null default 'M' comment '性别' 
) engine=innodb charset=utf8mb4 comment '学生表';

查询表定义

mysql> use oldboy
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed


mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| student          |
+------------------+


mysql> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | int(11)             | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)         | NO   |     | NULL    |                |
| age    | tinyint(3) unsigned | NO   |     | NULL    |                |
| gender | char(1)             | NO   |     | M       |                |
+--------+---------------------+------+-----+---------+----------------+


mysql> show create table student;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                        |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(20) NOT NULL COMMENT '姓名',
  `age` tinyint(3) unsigned NOT NULL COMMENT '年龄',
  `gender` char(1) NOT NULL DEFAULT 'M' COMMENT '性别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表'            |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

使用系统视图查询表定义

mysql> use information_schema

mysql> select * from information_schema.tables where table_schema='oldboy'\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: oldboy
     TABLE_NAME: student
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 1
    CREATE_TIME: 2020-08-17 16:32:27
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 学生表
1 row in set (0.00 sec)

修改表定义

# (默认在最后)增加一列
mysql> alter table student add column telnum char(11) not null unique key comment '手机号';

# 在指定列(name)后面增加一列
mysql> alter table student add column cardid char(18) not null unique key comment '身份证' after name;

# 在首位增加一列
mysql> alter table student add column uuid int not null comment '序号' first;

# 删列
mysql> alter table student drop uuid;

# 修改列的数据类型，使用modify 
mysql> alter table student modify gender tinyint not null;

# 修改列名和数据类型，使用change 
mysql> alter table student change gender gd char(1) not null  comment '1' ;


# 删除表定义 
mysql> drop table student;


# 其它操作 
# 克隆表 create like
mysql> create table stu like student;
# 清空表的数据
mysql> truncate table student;

truncate 是物理删除数据，只剩下表结构；
delete 实际上仅仅是打上删除标签。

4.2.3 DDL开发规范

1. 库名、表名：
   (1) 名称与业务有关
   例如：CRM客户管理系统
   库名：crmdb
   表名：crm_users
   (2) 不使用纯数字命名
   (3) 不能含有大写字符。
   Windows系统中目录名和文件名对大小写字母不敏感，但是Linux系统中目录名和文件名对大小写字母敏感，为了多平台兼容，命名时不能含有大写字符。
2. 建库：语句要显式地设置字符集。
3. 建表：语句要显式地设置字符集、存储引擎、注释。
4. 建表：每个表都建议创建主键列，尽量是自增数字列，且非业务相关。
   非业务相关就能保证值不会被修改。
5. 建表，选择合适的、足够的、简短的数据类型。
6. 建表，每个列尽量设置not null，和默认值。
   数字类型，默认值为0
   字符串类型，默认值为"null"
7. 尽量少使用外键。
   推荐：一张表与其它表的关联不要超过4个，列的数量不要超过40。
8. 每个列要有注释。
9. DDL修改表结构时，不要在业务繁忙期间做，因为DDL操作会涉及锁表，遇到十分紧急的情况，建议使用pt-osc(MySQL在线DDL工具)，可以减少对业务的影响。