MySQL数据库-简介与安装

MySQL 介绍和安装部署

1. 什么是数据库

1.1 简介

data base
数据 介质

1.2 什么是数据

一般认为：
文字，图片，视频，表格，用户名，密码。。。。。。
计算机认为：
01010101010

1.3 什么是介质

txt ,word , excel , ppt …notepad++ ,DBMS(数据库管理系统)

1.4 DBMS 数据库管理系统

1.4.1 作用

业务中关键型的数据，通常会使用DBMS软件进行数据的存储和管理

1.4.2 DBMS类型

RDBMS ： Oracle ， MySQL ，MSSQL ，PG
NoSQL ： MongoDB ， ES ，Redis
云数据库： RDS系列 ， PolarDB ， TDSQL
NewSQL ： Spanner ， TIDB ， 国产的。。。

2. MySQL 企业版本选择

2.1 MySQL 多厂家支持

Oracle MySQL
Percona MySQL
MariaDB
云厂商 ：RDS ， TDSQL
美团
爱可生
Pincap

2.2 Oracle MySQL 产品线

社区版 ： 开源版
企业版 ： 收费版
集群版 ： 几乎没人用

2.3 Oracle MySQL企业版本选择

5.6 ：第二名, 5.6.34+ 以上的双数版
5.7 ：第一名, 5.7.20+ 以上的双数版
8.0 ：第三名, 8.0.11+ 以上的版本,建议 8.0.17以后版本

3.手撕MySQL 5.7.28 二进制版本安装

3.1 环境准备

3.1.1 创建目录

[root@db01 ~]# mkdir -p /data/app/ /data/3306/data  /data/3306/binlog

3.1.2 创建MySQL相关用户、组

[root@db01 ~]# useradd mysql
[root@db01 ~]# chown -R mysql.mysql /data

3.1.3 系统残留清空

[root@db01 ~]# yum remove mariadb-libs -y

3.2 软件上传和解压

cd /data/app 
[root@db01 app]# tar xf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz 
[root@db01 app]# ln -s mysql-5.7.28-linux-glibc2.12-x86_64 mysql
[root@db01 app]# chown -R mysql. /data

3.3 环境变量设置

vim /etc/profile
#添加一行： 
export PATH=/data/app/mysql/bin:$PATH 

# 生效配置
source /etc/profile
# 检查设置
[root@db01 bin]# mysql -V
mysql  Ver 14.14 Distrib 5.7.28, for linux-glibc2.12 (x86_64) using  EditLine wrapper

3.4 准备基础配置文件

cat >/etc/my.cnf <<EOF 
[mysqld]
user=mysql
basedir=/data/app/mysql 
datadir=/data/3306/data
server_id=6
socket=/tmp/mysql.sock 
[client]
socket=/tmp/mysql.sock
EOF

3.5 初始化数据

# 5.7 + 版本 ： 
[root@db01 bin]# mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data

#########报错###############
mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
[root@db01 bin]# 
########################
# 报错处理： 
[root@db01 bin]# yum install -y libaio-devel

再次运行初始化命令
[root@db01 bin]# mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data
2020-03-13T03:33:34.530498Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-03-13T03:33:34.923532Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-03-13T03:33:35.000129Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-03-13T03:33:35.061644Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 6b64f882-64db-11ea-88a1-000c29248f69.
2020-03-13T03:33:35.065248Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-03-13T03:33:36.340554Z 0 [Warning] CA certificate ca.pem is self signed.
2020-03-13T03:33:36.603604Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.


+++++++++++++++++报错++++++++++++++++++++++
[root@db01 bin]# mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data
2020-03-13T03:35:50.039977Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-03-13T03:35:50.043386Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2020-03-13T03:35:50.043441Z 0 [ERROR] Aborting

[root@db01 bin]# 

处理方法： 清空datadir目录下所有数据，重新初始化。
！！！！！！生产需要详细确认。！！！！！！！！！！
+++++++++++++++++++++++++++++++++++++++++

3.6 准备启动脚本

方法一： 自带脚本（适合于单机单MySQL）

[root@db01 ~]# cp /data/app/mysql/support-files/mysql.server /etc/init.d/mysqld
启动方式 1： SYS-V (C6服务管理方式)
[root@db01 ~]# service mysqld start /stop /restart
[root@db01 ~]# service mysqld start 
Starting MySQL.Logging to '/data/3306/data/db01.err'.
 SUCCESS! 
[root@db01 ~]# service mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@db01 ~]# service mysqld restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL. SUCCESS! 
[root@db01 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 


启动方式2： C7 管理方式 systemd
[root@db01 ~]# chkconfig --add mysqld
[root@db01 ~]# systemctl stop mysqld
[root@db01 ~]# systemctl start mysqld
[root@db01 ~]# systemctl restart mysqld

方法二： 手工定制（单机多实例的模式）

vim /etc/systemd/system/my.service 
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/data/app/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000

# 注意： 
需要将之前启动过的mysql用老的方式关闭掉。再启动。
[root@db01 ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@db01 ~]# systemctl start my.service

3.7 小彩蛋 初始化命令的介绍

在 5.7+ 版本中 ： 
方法1： 
mysqld --initialize-insecure --user=  --basedir= --datadir= 
提示： 
root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

特点： 
1. 空密码的管理员用户。
2. 密码复杂度低。

方法2: 
mysqld --initialize  --user=  --basedir= --datadir= 
A temporary password is generated for root@localhost: jwhscLjji5<3
[root@db01 data]# 

特点： 
1. 管理员用户生成默认密码，12位，4种复杂度。
2. 临时密码不能用来管理数据库，第一次登陆前改掉它。

# 5.7 以前版本初始化方式
/data/app/mysql/scripts/mysql_install_db  --user=  --basedir= --datadir= 

基础管理

1. 安全管理

1.1 用户管理

#Linux中用户
登录系统
管理系统对象
#MySQL 用户
登陆MySQL
管理MySQL的对象

1.1.2 用户的命名

#Linux 中
字符串代表用户
#MySQL 中
用户名@‘白名单’
白名单？
定义的是允许登陆数据库的IP列表。

白名单支持的方式：
###oldboy@‘localhost’ # 允许本地的oldboy用户登陆数据库
oldboy@‘10.0.0.2’ # 允许10.0.0.2地址上的oldboy用户登陆数据库
###oldboy@‘10.0.0.%’ # 允许10.0.0.0/24网段的地址oldboy用户登陆数据库
###oldboy@‘10.0.0.0/255.255.254.0’ # 允许10.0.0.0/23网段的地址oldboy用户登陆数据库
###oldboy@‘10.0.0.5%’ # 允许10.0.0.50-59
oldboy@’%’ # 允许所有IP地址链接
oldboy@‘db01’ # 允许db01主机名的地址登陆

1.1.3 用户管理

# 查询用户
mysql> select user,host ,authentication_string from mysql.user;
±--------------±----------±------------------------------------------+
| user | host | authentication_string |
±--------------±----------±------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
±--------------±----------±------------------------------------------+
# 创建用户
mysql> create user oldguo@‘10.0.0.%’ identified by ‘123’;
mysql> create user oldboy@‘localhost’ identified by ‘123’;

# 删除用户
mysql> drop user oldboy@‘localhost’;

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

管理员密码忘记的处理

[root@db01 ~]# mysql -uroot -pasdfasdfasdf
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
（1）停数据库
[root@db01 ~]# /etc/init.d/mysqld stop
（2）“单用户”模式启动数据库
service mysqld start --skip-grant-tables --skip-networking 
或者：
mysqld_safe --skip-grant-tables --skip-networking &
（3）登陆数据库改密码 
mysql> alter user root@'localhost' identified by '123';
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.01 sec)

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

（4）重启数据库到正常模式
[root@db01 ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

[root@db01 ~]# mysql -uroot -p123

1.2 权限管理

1.2.0

GRANT  权限   ON  权限范围  TO 用户  IDENTIFIED BY '密码';

8.0以前版本：
GRANT  权限   ON  权限范围  TO 用户  IDENTIFIED BY '密码';
功能：
	1. 创建用户 
	2. 授权

8.0 以后注意： Grant 命令只是授权命令，不能进行建用户和改密码。
语法变为了： 
GRANT  权限   ON  权限范围  TO 用户 ，不支持改密码。

8.0 以后密码方面的“坑”。
密码验证方式从Native 换成了sha2插件。

1.2.1 权限定义

ALL ： 以下列表中除了 Grant option 所有权限 
Create,Delete,Insert ,....

mysql> show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege               | Context                               | Comment                                               |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                   | Tables                                | To alter the table                                    |
| Alter routine           | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                  | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine          | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create temporary tables | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view             | Tables                                | To create new views                                   |
| Create user             | Server Admin                          | To create new users                                   |
| Delete                  | Tables                                | To delete existing rows                               |
| Drop                    | Databases,Tables                      | To drop databases, tables, and views                  |
| Event                   | Server Admin                          | To create, alter, drop and execute events             |
| Execute                 | Functions,Procedures                  | To execute stored routines                            |
| File                    | File access on server                 | To read and write files on the server                 |
| Grant option            | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                   | Tables                                | To create or drop indexes                             |
| Insert                  | Tables                                | To insert data into tables                            |
| Lock tables             | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                 | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                   | Server Admin                          | To make proxy user possible                           |
| References              | Databases,Tables                      | To have references on tables                          |
| Reload                  | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client      | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave       | Server Admin                          | To read binary log events from the master             |
| Select                  | Tables                                | To retrieve rows from table                           |
| Show databases          | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view               | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                | Server Admin                          | To shut down the server                               |
| Super                   | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                 | Tables                                | To use triggers                                       |
| Create tablespace       | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                  | Tables                                | To update existing rows                               |
| Usage                   | Server Admin                          | No privileges - allow connect only                    |
+-------------------------+---------------------------------------+-------------------------------------------------------+

1.2.2 权限范围

chmod -R  777  /
chmod -R  777  /oldboy 
chmod -R  777  /oldboy/t1

*.*          : 管理员    全库级别
oldboy.*     ：普通用户  单库级别
oldboy.t1    ：不常用    单表级别
列           ：不常用    单列级别

1.2.3 授权管理

# 授权例子 ： 
# 1. 授权一个管理员用户 
grant all on *.* to adm_oldguo@'10.0.0.5%' identified by 'OldGuo@123.Com' with grant option;

# 2. 授权一个业务用户wp_user1用户可以通过10.0.0.% 操作wordpress库下所有表
grant select ,insert ,update, delete on wordpress.*  to wp_user1@'10.0.0.%' identified by 'Wpuser@123.Com';

# 回收权限
注意： MySQL中 修改权限，不能重复授权。所有授权都是相加关系。所以需要回收权限。
mysql> revoke delete on wordpress.* from wp_user1@'10.0.0.%';


# 3. 查看授权 
mysql> show grants for wp_user1@'10.0.0.%';

# 4. 彩蛋 我们的权限都存在哪里呢？
授权表？
user         ： 存储用户信息 + 全库级别的权限
db           :  用户针对单库级别的权限 
tables_priv  ： 针对表级别的权限
columns_priv ： 针对列级别的权限
procs_priv   ： 
proxies_priv ：

2. MySQL 的连接管理

2.1 自带客户端程序

 mysql 命令 

2.1.1 连接方式（协议）

# 本地 socket 方式登陆

前提： 需要提前准备能够通过localhost登陆的用户。例如： oldguo@'localhost'
# 1. 授权本地用户
mysql> grant all on *.* to oldguo@'localhost' identified by '123';
# 2. 连接测试 
mysql> select @@socket;
+-----------------+
| @@socket        |
+-----------------+
| /tmp/mysql.sock |
+-----------------+
1 row in set (0.00 sec)

[root@db01 ~]# mysql -uoldguo -p123 -S /tmp/mysql.sock

# 远程 TCP/IP 方式登陆

# 前提： 必须要提前授权远程连接的用户。例如：hui@'10.0.0.%'
# 1. 授权远程用户
mysql> grant all on *.* to 	hui@'10.0.0.%' identified by '123';
# 2. 连接测试 
[root@db01 ~]# mysql -uhui -p123 -h 10.0.0.51 -P 3306 

2.2 客户端工具

SQLyog
Navicat

2.3 程序连接

PHP : php-mysql API驱动
Python ： pyMysql
JAVA
GO
C , C++
.NET
C#
perl
ruby
…

3. 初始化配置管理

3.1 方式

源码编译时
配置文件
命令行

3.2 配置文件应用

3.2.1 默认配置文件读取顺序

[root@db01 ~]# mysql --help --verbose |grep my.cnf
/etc/my.cnf  /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf  ~/.my.cnf 

假设： /etc/my.cnf --->  socket=/tmp/mysql.sock     ~/.my.cnf -->  socket=/tmp/my.sock

3.2.2 配置文件作用

# 1. 影响数据库服务端的启动和运行
# 2. 本地客户端的连接

3.2.3 配置文件的结构

[标签]
配置项=xxxx

[标签]作用是什么？
用来区分客户端和服务端的

标签的分类：
	服务端  ： [server] 、 [mysqld] 、[mysqld_safe]
	客户端  :  [client] 、 [mysql] 、 [mysqldump]....

[root@db01 ~]# cat /etc/my.cnf 
[mysqld]                     # 服务器程序标签
user=mysql                   # 管理用户
basedir=/data/app/mysql      # 程序目录
datadir=/data/3306/data      # 数据目录
server_id=6                  # 数据库ID号，主从时使用，区分不同节点。
socket=/tmp/mysql.sock       # socket文件位置

[client]                     # 客户端标签
socket=/tmp/mysql.sock       # 连接数据库时使用的socket位置

3.2.4 自定制配置文件位置

mv /etc/my.cnf /opt
pkill mysqld 
[root@db01 ~]# mysqld_safe --defaults-file=/opt/my.cnf & 

4. MySQL的启动和关闭

4.1 画图说明mysql的启动方式

1. 通过直接调用程序启动
/data/app/mysql/bin/mysqld &
/data/app/mysql/bin/mysqld_safe   &

好处在于，灵活的在命令行添加需要的配置参数。
2. 通过sys-v  systemd 方式启动 
service mysqld restart /stop /start /status 
systemctl start/stop/restart/status  mysqld
好处在于，启动关闭方便。但是需要依赖配置文件，不能单独添加（service除外）参数启动数据库。

4.2 关闭方法

service mysqld stop
systemctl stop mysqld
mysql -e "shutdown"
mysqladmin shutdown 

多实例的实现

1.1 准备多个目录

mkdir -p /data/330{7,8,9}/data

1.2 准备配置文件

cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/data/app/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=/data/app/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=/data/app/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

1.3 初始化三套数据

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

1.4 systemd管理多实例

cd /etc/systemd/system
cp my.service mysqld3307.service
cp my.service mysqld3308.service
cp my.service mysqld3309.service


修改以下内容：

vim mysqld3307.service
ExecStart=/data/app/mysql/bin/mysqld  --defaults-file=/data/3307/my.cnf

vim mysqld3308.service
ExecStart=/data/app/mysql/bin/mysqld  --defaults-file=/data/3308/my.cnf


vim mysqld3309.service
ExecStart=/data/app/mysql/bin/mysqld  --defaults-file=/data/3309/my.cnf

1.5 授权

chown -R mysql.mysql /data/*

1.6 启动

systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service

1.7 验证多实例

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"

MySQL的体系结构

1 MySQL C/S工作模型

客户端 ------> 服务端
C                S

2 实例概念

实例：
mysqld核心守护进程（Boss）+
Master Thread（经理）+
干活的线程（员工）+
预分配内存（办公室）

3 mysqld核心主程序结构

连接层 ： 连接协议，验证，连接线程
SQL层 ： 语法，语义，权限， 解析预处理（代价），选择执行计划，执行SQL
小细节：
1. 查询缓存。
select user,host from mysql.user; hash—》 HASH_ID + 结果。
use mysql ; select user,host from user;
2. 日志记录。binlog , slowlog …

存储引擎层： 和磁盘交互的层次。相当于MySQL内置的文件系统。负责数据的存取。

4 逻辑结构（抽象结构）

库 ： 库名 ， 库属性 ，相当于Linux中的目录。
表 ： 表名，列，数据行，表属性

Linux中一切皆文件，一切皆命令。
MySQL中一切皆表，一切皆SQL。

5 物理层

5.1 宏观角度

/data/3306/data 下的数据信息介绍：
库： 目录

表： 多个构成
mysql.user表 (MyISAM)：
user.frm : 存储的表的列信息
user.MYD ：存储的表的数据行
user.MYI ：存储的是索引(相当一本书中的目录)

mysql.time_zone(InnoDB):
time_zone.frm ： 存储的表的列信息
time_zone.ibd ： 存储的表的数据行+索引
其他的信息：

5.2 InnoDB物理微观角度

段 ： segment，表段，非分区表就是一个段。一个或多个extents组成
区 ： extents ，簇，连续64个页，默认1M
页 ： pages ，数据页，默认大小是16KB，连续4个OS block

为什么这么设计？
为了能够让MySQL的使用磁盘尽可能的都是顺序IO。