数据库高级知识——mysql架构介绍（一）

1.MySQL简介

1.1 概述

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。
MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

Mysql是开源的，所以你不需要支付额外的费用。
Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。

Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL使用标准的SQL数据语言形式。
Mysql可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。

1.2 Mysql高级

数据库内部结构和原理
数据库建模优化
数据库索引建立
SQL语句优化
SQL编程
mysql服务器的安装配置
数据库的性能监控分析与系统优化
各种参数常量设定
主从复制
分布式架构搭建、垂直切割和水平切割
数据迁移
容灾备份和恢复
shell或python等脚本语言开发
对开源数据库进行二次开发

2.Mysql Linux版的安装

Ubuntu20.04+MySQL8.0.25

2.1 MySQL下载官网

MySQL linux版

2.2 安装与卸载

安装：
MySQL安装

卸载：
参考1
参考2

2.3 查看MySQL安装版本

或者可以执行 mysqladmin --version命令，类似java -version如果打出消息，即为成功。

通过vim 查看 mysql组 和mysql组 

2.4 mysql服务的启+停

启动：

关闭：

查看MySQL状态：

sudo service mysql status

root@zhaoxr-ThinkPad-E450:/etc/mysql# service mysql status
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Thu 2021-05-27 16:13:51 CST; 13min ago
    Process: 78631 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 78639 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 4343)
     Memory: 334.3M
     CGroup: /system.slice/mysql.service
             └─78639 /usr/sbin/mysqld

5月 27 16:13:49 zhaoxr-ThinkPad-E450 systemd[1]: Starting MySQL Community Server...
5月 27 16:13:51 zhaoxr-ThinkPad-E450 systemd[1]: Started MySQL Community Server.

2.5 登录mysql

切换到root用户，使用root用户可以不用密码直接登录

root@zhaoxr-ThinkPad-E450:/home/zhaoxr/mysql# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.25-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> 

2.6 设置开机自启动

设置开机自启动：

sudo update-rc.d -f mysql defaults

关闭开机自启动：

sudo update-rc.d -f mysql remove

查看MySQL状态：

sudo service mysql status

2.7 MySQL相关文件

文件位置	解释
/usr/bin	客户端程序和mysql_install_db
/var/lib/mysql	数据库和日志文件
/var/run/mysqld	服务器
/etc/mysql	配置文件my.cnf
/usr/share/mysql	字符集，基准程序和错误消息
/etc/init.d/mysql	启动mysql服务器

2.8 修改字符集

查看字符集

show variables like '%char%';

show variables like '%char%';

mysql> show variables like 'character%'; 
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

mysql> show variables like '%char%';
+--------------------------------------+----------------------------+
| Variable_name                        | Value                      |
+--------------------------------------+----------------------------+
| character_set_client                 | utf8mb4                    |
| character_set_connection             | utf8mb4                    |
| character_set_database               | utf8mb4                    |
| character_set_filesystem             | binary                     |
| character_set_results                | utf8mb4                    |
| character_set_server                 | utf8mb4                    |
| character_set_system                 | utf8mb3                    |
| character_sets_dir                   | /usr/share/mysql/charsets/ |
| validate_password.special_char_count | 1                          |
+--------------------------------------+----------------------------+
9 rows in set (0.00 sec)

修改

3.Mysql配置文件

Ubuntu20.04+MySQL8.0.25的配置文件
/etc/mysql
/etc/mysql/mysql.conf.d

root@zhaoxr-ThinkPad-E450:/etc/mysql/mysql.conf.d# cat mysqld.cnf 
#
# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld]
#
# * Basic Settings
#
user            = mysql
# pid-file      = /var/run/mysqld/mysqld.pid
# socket        = /var/run/mysqld/mysqld.sock
# port          = 3306
# datadir       = /var/lib/mysql


# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir                = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
mysqlx-bind-address     = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size         = 16M
# max_allowed_packet    = 64M
# thread_stack          = 256K

# thread_cache_size       = -1

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP

# max_connections        = 151

# table_open_cache       = 4000

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
#
# Log all queries
# Be aware that this log type is a performance killer.
# general_log_file        = /var/log/mysql/query.log
# general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
# slow_query_log                = 1
# slow_query_log_file   = /var/log/mysql/mysql-slow.log
# long_query_time = 2
# log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
# server-id             = 1
# log_bin                       = /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds    = 2592000
max_binlog_size   = 100M
# binlog_do_db          = include_database_name
# binlog_ignore_db      = include_database_name

3.1二进制日志log-bin

log_bin                       = /var/log/mysql/mysql-bin.log

log-bin 中存放了所有的操作记录(写？)，可以用于恢复。相当于 Redis 中的 AOF    
log-bin配置(默认关闭)

3.2 错误日志log-error

log_error = /var/log/mysql/error.log

默认是打开的,记录严重的警告和错误信息，每次启动和关闭的详细信息等。

3.3 慢查询日志log

slow_query_log_file   = /var/log/mysql/mysql-slow.log

默认关闭，记录查询的sql语句，如果开启会减低mysql的整体性能，因为记录日志也是需要消耗系统资源的
可自定义“慢”的概念:0-10秒之间的一个数。
慢查询日志会将超过这个查询事件的查询记录下来，方便找到需要优化的 sql 。
用于优化sql语句是使用。 

4.Mysql 的用户与权限管理

4.1 MySQL的用户管理

查看密码校验规范：

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 8     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 1     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)

各个密码等级，可以设置的账户密码如下所示：

账户密码设置规范

创建用户：

mysql>create user zhang3 identified by '123123';
#表示创建名称为zhang3的用户，密码设为123123；

查看用户：

mysql> select user, host, plugin, authentication_string from mysql.user;
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| user             | host      | plugin                | authentication_string                                                  |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| zhaoxr           | %         | caching_sha2_password | $A$005$^-~pbXT1[86`p3gH2nttGGHB5FpbQgt7TeCCDEMhUKJRZp.VDgqYZj9D         |
| debian-sys-maint | localhost | caching_sha2_password | $A$005$R UJ|`]!LfSpJ7Re9jW5ODzmiNgfyP/4ncIV73                          |
| mysql.infoschema | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | auth_socket           |                                                                        |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
6 rows in set (0.00 sec)

 host ：   表示连接类型
    % 表示所有远程通过 TCP方式的连接
    IP 地址 如 (192.168.1.2,127.0.0.1) 通过制定ip地址进行的TCP方式的连接
   机器名   通过制定i网络中的机器名进行的TCP方式的连接
    ::1   IPv6的本地ip地址  等同于IPv4的 127.0.0.1
    localhost 本地方式通过命令行方式的连接 ，比如mysql -u xxx -p 123xxx 方式的连接。
 User:表示用户名
   同一用户通过不同方式链接的权限是不一样的。

删除用户

mysql> drop user li4 ;#删除li4用户

4.2 权限管理

授予权限：

授权命令： 
grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令’;
该权限如果发现没有该用户，则会直接新建一个用户。
比如  
grant select,insert,delete,drop on atguigudb.* to li4@localhost  ;
给li4用户用本地命令行方式下，授予atguigudb这个库下的所有表的插删改查的权限。
 
grant all privileges on *.* to joe@'%'  [identified by '123']; 
授予通过网络方式登录的的joe用户 ，对所有库所有表的全部权限，[密码设为123]可以不用加.
就算 all privileges 了所有权限，grant_priv 权限也只有 root 才能拥有。
 
给 root 赋连接口令 grant all privileges on *.* to root@'%'  ;后新建的连接没有密码，需要设置密码才能远程连接。
update user set password=password('root') where user='root' and host='%';

收回权限：

收回权限： 
revoke  权限1,权限2,…权限n on 数据库名称.表名称  from  用户名@用户地址 ;
 
REVOKE ALL PRIVILEGES ON mysql.* FROM joe@localhost;
#若赋的全库的表就 收回全库全表的所有权限
 
REVOKE select,insert,update,delete ON mysql.* FROM joe@localhost;
#收回mysql库下的所有表的插删改查权限
 对比赋予权限的方法。
 必须用户重新登录后才能生效

查看权限：

查看当前用户权限
show grants;
 
查看某用户的全局权限
select  * from user ;
 
查看某用户的某库的权限
select * from  db;
 
查看某用户的某个表的权限
select * from tables_priv;