MySQL学习2

1.1数据库的介绍

公司用了哪些数据库产品？

请列举一下你熟悉的数据库产品？

我们公司是制造业/贷款类/电商。。

我们公司用了

RDBMS:Mysql，Oracle，PG,MSSQL

Nosql:Redis,MongoDB,ES

RDS:MYSQL,REDIS...

1.2 Mysql 数据库企业版本选择

1.3安装

    5.7+

   mysql --initialize-insecure

--initialize-insecure

--initialize

  5.6

  mysql_install_db

1.4 体系结构

1.4.1 CS模型

          TCPIP

         Socket

1.4.2 实例

         mysql + 工作线程+ 预分配内存

1.4.3 mysqld 工作过程

server：

连接层：

      1.提供连接协议

          socket

           TCPIP

        2.验证

               user

                password

       3.提供连接线程

               show processlist

SQL层：

1.语法

2.语义：DDL DCL DML

3.权限：

4.解析、预处理：执行计划

5.优化：根据cost代价优化算法，选择执行计划

6.执行：根据优化器选择的执行计划，执行SQL

              得出结果：你需要的数据在XX段，XX区，XX页

7.query_cache：可以用redis

8.日志记录：二进制日志，通用日志

engine 存储引擎层：

段：一个表就是一个段（分区表除外），1个或多个区

区:一个区，连续的64页组成，默认1M

页：一个页，连续的4个os block，默认是16KB

1.4.4 逻辑结构

库：库名+库属性

表：表名+表属性+列+数据行

2.Mysql基础管理

2.1用户管理

2.1.1 用户的作用

Linux用户：

          登录Linux系统

         管理Linux对象：文件

Mysql用户：

         登录Mysql数据库

        管理mysql对象：表

2.1.2 用户的定义（长成啥样？）

Linux用户：用户名

MySQL用户：用户名@‘白名单’

白名单？

            地址列表，允许白名单的IP登录Mysql，管理Mysql

oldguo@‘localhost’：oldguo用户能够通过本地登录Mysql（socket）

oldguo@‘10.0.0.10’：oldguo用户能够通过10.0.0.10远程登录Mysql服务器

oldguo@‘10.0.0.%’：oldguo用户能够通过10.0.0.XX/24远程登录Mysql服务器

oldguo@‘10.0.0.5%’：...50-59...

oldguo@‘10.0.0.0/255.255.254.0’

oldguo@‘%’

2.1.3 用户管理

查： select user，host，authentication_string from mysql.user；

可以查到，带不带密码

select user，host  from mysql.user；

直接查用户

 增：

creat user oldguo@‘localhost’;

查询下是不是增加了

 建用户也建密码

create user oldboy@‘10.0.0.%’ identified by ‘123’;

有密码了，密码一样代码也是一样额删

改：

alter user oldguo@‘localhost’ indentified by ‘123’;

设置，更改密码

修改数据库root密码

改完后需要加 -uroot -p 进入

这样可隐藏密码

删：

drop user oldguo@‘localhost’;

修改

注意：8.0版本以前，是可以通过grant命令 建立用户+授权

8.0以后需要先建用户，再授权

2.2 权限管理

2.2.1 作用

用户对数据库对象，有哪些管理能力

linux 中权限是文件属性

         设置权限时是设置在文件上，

          如 chmod 755 /oldboy

               chown -R mysql.mysql /data

mysql是针对用户属性

2.2.2 权限的表现方式

linux权限的表现方式是什么？

CURD

读写执行

增删改查

mysql权限表现方式是什么？

是具体的命令

可设置权限列表

作用对象

对应的描述

alter：修改表

create:建库 建表 建索引

create user：

delete：对于表的操作，删除已存在的数据行

drop：

enent：事件的管理

grant option：给其他的用户授权的权限

2.3 授权和回收权限操作

(1)语法：

  8.0以前

       grant 权限 on  对象 to  用户   identified by '密码';

  8.0+

     create user 用户 identified by ‘密码’；

     grant 权限1，权限2，权限3，。。。 on 对象 to 用户 identified by ‘密码’ with grant option；

权限：

  ALL                                            ：管理员

  权限1，权限2，权限3，。。。 ：普通户用（业务用户，开发用户）

  grant option                              ：给别的用户授权

对象：库，表

*.*            :---->    chmod -R 755 /

oldguo.*  :---->    chmod -R 755 /oldguo

oldguo.t1:---->    chmod -R 755 /oldguo/t1

(2)授权的例子

    例子1：创建并授权管理员用户，能够通过10.0.0.%网段并管理数据库

   grant all on *.* to oldguo@'10.0.0.%' identified by '123' witn grant option;

查询创建的用户

成功创建

查询用户权限

权限授权成功

权限信息查询

select * from mysql.user\G

例子2：创建并授权一个app@'10.0.0.%'业务用户，能够对app库下所有对象进行create，select，update，delete，insert操作

grant create,select,update,delete,insert on app.* to app@10.0.0.%' identified by '123';

use mysql

show tables     ----->   信息在以下的表中

用户基本信息在user表中

select * from mysql.user \G

select * from mysql.db \G 对于库级别是在db中

select * from mysql.tables_priv \G 对于表级别是在tables_priv中

扩展：

mysql 授权表：

mysql库下存放：

user                :*.*

db                    :app.*

tables_priv       :app.t1

columns_priv   :列

（3）回收权限

linux：

chmod -R 644 /oldguo -----> chmod -R 755 /oldguo

mysql：

注意：mysql中不能通过重复授权，修改权限，只能通过回收权限方式进行修改

revoke create

create权限 没了

2.3 超级管理员密码忘记了，怎么找回

mysql程序中哪个部分负责用户密码验证？

连接层

连接协议：TCP/IP SOCKET

用户验证

链接线程

关闭数据库

（1）关闭数据库

systemctl stop mysqld

（2）使用安全模式启动

mysql_safe --skip-grant-tables --skip-networking &

或 service mysql start --skip-grant-tables --skip-networking

（3）登录数据库并修改密码

mysql

alter user root@‘localhost’ identified by ‘123’；

flush privileges； #手工加载授权表

alter user root@‘localhost’ identified by ‘123’；

（4）重启数据库到正常模式

service  mysqld restart

2.4 mysql连接管理

2.4.1 mysql自带客户端

（1）mysql

参数列表：

  -u 用户

  -p 密码

  -s  本地socket文件位置

 -h  数据库IP地址

  -p  数据库端口号

  -e  免交互执行数据库命令

  <  导入sql脚本

本地socket：

       前提：数据库中必须实现授权oldguo@‘localhost’用户

mysql  -uoldguo -p123 -S /tmp/mysql.sock

mysql  -uoldguo -p -S /tmp/mysql.sock

mysql  -uroot -p123

tcp/ip:

    前提：必须提前创建好，可以远程连接的用户（例如：oldguo@'10.0.0.%'）

  mysql -uoldguo -p123 -h 10.0.0.51 -P 3306

  mysql -uoldguo -p -h 10.0.0.51 -P 3306

例子：

1.授权oldboy管理用户，可以通过本机socket登录

（1）创建管理员用户

（2）登录测试

2.授权oldboy管理用户，可以远程登录

（1）创建oldboy用户

（2）登录测试

show processlist

可以区分远程登录还是本地登录

mysqldump  备份工具

mysqladmin 管理工具

2.4.2mysql远程客户端程序

前提：必须提前创建好，可以远程连接的用户（例如：oldguo@'10.0.0.%'）

2.4.3程序链接

yum install -y php -mysql

pip3 install pymysql

2.5 初始化配置

2.5.1 方式

源码安装时-----》编译过程中设置初始化参数

配置文件------》数据库启动之前，设定配置文件参数 /etc/my.cnf

启动脚本命令行 ------》 mysqld_safe --skip-grant-tables --skip-networking &

说明：my.cnf---> port=3306

mysqld_safe --port=3307 &

命令行优先

2.5.2 配置文件应用

（1）配置文件读取顺序

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

意外情况：

           手工定制配置文件位置点：/opt/my.cnf

   mysqld      --defaults-file=/opt/my.cnf &

    mysqld_safe     --defaults-file=/opt/my.cnf &

(2) 配置文件的书写格式

【标签】

配置参数=XXX

标签是什么？

区分不同程序参数的

【服务器端】：影响数据库服务端运行参数设定。

[mysqld_safe]

[mysql]

[server]

【客户端】:影响本地客户端连接，不影响远程客户端

[mysql]

[mysqldump]

[client]

配置文件基础模板说明

2.6 mysql的启动和关闭

2.6.1 启动

mysqld_safe  是脚本 可以编辑

support-files/mysql.server 是脚本文件

如果是用 mysqld 或mysqld_safe 开启数据库  用shut down 关数据库

2.6.2 关闭

2.7 mysql的多实例

2.7.1 同版本多实例

（1）规划

         软件1份

         配置文件3份  ：/data/330{7..9}/my.cnf

        数据目录 3 份  /data/330{7..9}

        初始化数据  3份

        日志目录 3份  /binlog/330{7..9}

         socket   3份    /tmp/mysql330{7..9}.sock

        端口       3份  port=3307,3308,3309

        server_id  3份 server_id=7,8,9

(2)配置过程

1.创建需要的目录

   
2.创建配置文件

3.初始化数据

报错处理

1.多创建一层新目录

2.修改配置文件

同时创建3308 3309

3.重新初始化

4.准备启动脚本

5.启动多实例