数据库-基本操作和体系结构

一、数据库基本操作

1.设置数据库密码

[root@db04 service]# mysqladmin -uroot password '123456'

2.使用密码登录

#1.正确的登录方式
[root@db04 ~]# mysql -u root -p123456
[root@db04 ~]# mysql -uroot -p123456

#2.错误的登录方式
[root@db04 ~]# mysql -u root -p 123456

#3.原因：
for password options, the password value is optional:
　　If you use a -p or --password option and specify the password value, there must be no space between -p or --password= and the password following it.
#如果使用-p或者--password参数，他们与密码之间不能有空格　　

　　If you use a -p or --password option but do not specify the password value, the client program prompts you to enter the password. The password is not displayed as you enter it. This is more secure than giving the password on the command line. Other users on your system may be able to see a password specified on the command line by executing a command such as ps auxw. 
#如果只写-p或--password参数什么都不接，意思是让你手动输入密码

#4.登录数据库直接进入库
[root@db04 ~]# mysql -u root -p mysql

3.查询用户

mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | db04      |
| root | db04      |
|      | localhost |
| root | localhost |
+------+-----------+
6 rows in set (0.00 sec)

mysql>

4.删除用户（优化）

mysql> drop user root@'::1';
Query OK, 0 rows affected (0.00 sec)

mysql> drop user ''@'db04';
Query OK, 0 rows affected (0.00 sec)

mysql> drop user ''@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | db04      |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)

二、企业中误删除用户表所有用户故障

1.不小心执行了删除所有用户的命令

mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | db04      |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)

mysql> delete from mysql.user where 1=1;
Query OK, 3 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
Empty set (0.00 sec)

#删除所有用户后还是可以登录，重启后登录失败
[root@db04 ~]# systemctl restart mysql
[root@db04 ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

2.解决办法

1）停止数据库

[root@db04 ~]# systemctl stop mysql

2）跳过授权表和网络启动数据库

#1.跳过授权表启动
[root@db04 ~]# mysqld_safe --skip-grant-tables &

#2.跳过授权表与网络启动数据库
[root@db04 ~]# mysqld_safe --skip-grant-tables --skip-networking &

#注意：只跳过授权表登录很不安全，任何人都可以远程登录，所以还要限制只允许本机登录数据库

3）插入用户数据

#1.切换到mysql库
mysql> use mysql;

#2.dao入用户数据
mysql> insert into mysql.user values ('localhost','root',PASSWORD('123'),
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'',
'',
'',
'',
0,
0,
0,
0,
'mysql_native_password',
'',
'N');

#3.查看用户
mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | localhost |
+------+-----------+
1 row in set (0.00 sec)

4）正常启动数据库测试

[root@db04 ~]# mysqladmin shutdown
[root@db04 ~]# systemctl start mysql
[root@db04 ~]# mysql -uroot -p123
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.46 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lhd                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)

3.另一种解决办法

1）停止数据库

2）跳过授权表和网络登录

3）直接授权创建新用户

#1.刷新mysql系统权限相关表
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

#2.第一种授权方法
#授权一个新用户，但是grant默认没有允许权限
mysql> grant all on *.* to root@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)
#修改grant权限
mysql> update mysql.user set Grant_priv='Y' where user='root' and host='localhost';

#3.第二种授权方法
mysql> grant all on *.* to root@'localhost' identified by '123' with grant option;

三、mysql体系结构管理

1.客户端与服务器模型

1）mysql是一个典型的C/S服务结构

1.mysql自带的客户端程序（/service/mysql/bin）
    mysql
    mysqladmin
    mysqldump

2.mysqld一个二进制程序，后台的守护进程
    单进程
    多线程

2）mysql的两种连接方式

1.TCP/IP的连接方式
2.套接字连接方式，也就是socket连接

3.举例：
    3.1 TCP/IP连接
    mysql -uroot -p -h127.0.0.1
    mysql -uroot -p -h127.0.0.1 -S /tmp/mysql.sock
    3.2 socket连接
    mysql -uroot -p -hlocalhost
    mysql -uroot -p123

4.注意：
    4.1 不一定-h都是TCP/IP连接，-hlocalhost就是socket连接
    4.2 socket连接速度比TCP/IP连接快，因为TCP/IP连接需要建立三次握手

四、mysql服务构成

1.实例

1.MySQL的后台进程+线程+预分配的内存结构。
2.MySQL在启动的过程中会启动后台守护进程，并生成工作线程，预分配内存结构供MySQL处理数据使用。

3.什么是实例？
    一个进程 + 多个线程 + 分配内存空间
4.多实例？
    多个进程 + 多个线程 + 分配内存空间

2.mysqld服务程序构成

mysqld是一个守护进程

1）连接层

1.验证用户的身份，用户名密码是否匹配
2.提供两种连接方式（TCP/IP连接、socket连接）
3.连接层提供了一个与sql层交互的线程

2）SQL层

1.接收连接层传过来的SQL语句
2.验证执行的SQL语法
3.验证SQL的语义（DDL,DML,DQL,DCL）
4.解析器：解析SQL语句，生成执行计划
5.优化器：将解析器传来的执行计划选择最优的一条执行
6.执行器：将最优的一条执行
    6.1 与存储引擎层建立交互的线程
    6.2 将要执行的sql发给存储引擎层
7.如果有缓存，则走缓存
8.记录日志（binlog）

3）存储引擎层

1.接收SQL层传来的语句
2.与磁盘交互，获取数据，返回给sql层
3.建立与sql层交互的线程

五、mysql的结构

1.mysql的逻辑结构

1、库
2、表：元数据+真实数据行
3、元数据：列+其它属性（行数+占用空间大小+权限）
4、列：列名字+数据类型+其他约束（非空、唯一、主键、非负数、自增长、默认值）

MySQL逻辑结构与Linux系统对比

2.mysql物理结构

1）MySQL的最底层的物理结构是数据文件，也就是说，存储引擎层，打交道的文件，是数据文件。
2）存储引擎分为很多种类（Linux中的FS）
3）不同存储引擎的区别：存储方式、安全性、性能

#注意：开发创建数据库及表的时候全都要小写

3.mysql物理大小

1、段：理论上一个表就是一个段，由多个区构成，（分区表是一个分区一个段）
2、区：连续的多个页构成
3、页：最小的数据存储单元，默认是16k
4.分区表：一个区构成一个段就是一个表