第三章 Linux系统应用 - Mysql 8.0部署和操作 - 《Linux 系统运维》

Mariadb
Linux 部署
yum源
下载历史版本
查看安装默认密码
数据库密码策略
跳过密码
重置密码
安全初始化
数据类型
约束类型
MySQL索引
MySQL锁
MySQL存储引擎
SQL 分类 3个类别
DDL 语句
- MySQL 常用命令
- 删除数据库
- 创建表emp
- 修改表字段
- 增加表字段
- 调整字段位置
- 更改表名
- 删除表emp1
- 查看在哪个库
- 创建用户
- 修改用户密码
- 查看用户权限
- 用户授权
- 用户权限回收
- 用户角色权限管理
- 删除用户
DML 语句
- 1 插入记录

Mariadb

https://www.yuque.com/liweiming/linux/kca7w3

Linux 部署

https://dev.mysql.com/downloads/mysql/
rpm包标准安装包5个

mysql-community-server mysql-community-client mysql-community-libs mysql-community-common mysql-community-libs-compat

mysql-community-server-8.0.21-1.el7.x86_64.rpm
mysql-community-client-8.0.21-1.el7.x86_64.rpm
mysql-community-libs-8.0.21-1.el7.x86_64.rpm
mysql-community-common-8.0.21-1.el7.x86_64.rpm
mysql-community-libs-compat-8.0.21-1.el7.x86_64.rpm

[root@riyimei tmp]# ll
total 565996
-rw-r--r-- 1 root root  49947596 Jul 25 09:10 mysql-community-client-8.0.21-1.el7.x86_64.rpm
-rw-r--r-- 1 root root    631676 Jul 25 09:12 mysql-community-common-8.0.21-1.el7.x86_64.rpm
-rw-r--r-- 1 root root   4765268 Jul 25 09:11 mysql-community-libs-8.0.21-1.el7.x86_64.rpm
-rw-r--r-- 1 root root   1277020 Jul 25 09:12 mysql-community-libs-compat-8.0.21-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 522944772 Jul 25 09:06 mysql-community-server-8.0.21-1.el7.x86_64.rpm
[root@riyimei tmp]# yum localinstall mysql-community*
Loaded plugins: fastestmirror
Examining mysql-community-client-8.0.21-1.el7.x86_64.rpm: mysql-community-client-8.0.21-1.el7.x86_64
Marking mysql-community-client-8.0.21-1.el7.x86_64.rpm to be installed
Examining mysql-community-common-8.0.21-1.el7.x86_64.rpm: mysql-community-common-8.0.21-1.el7.x86_64
Marking mysql-community-common-8.0.21-1.el7.x86_64.rpm to be installed
Examining mysql-community-libs-8.0.21-1.el7.x86_64.rpm: mysql-community-libs-8.0.21-1.el7.x86_64
Marking mysql-community-libs-8.0.21-1.el7.x86_64.rpm to be installed
Examining mysql-community-libs-compat-8.0.21-1.el7.x86_64.rpm: mysql-community-libs-compat-8.0.21-1.el7.x86_64
Marking mysql-community-libs-compat-8.0.21-1.el7.x86_64.rpm to be installed
Examining mysql-community-server-8.0.21-1.el7.x86_64.rpm: mysql-community-server-8.0.21-1.el7.x86_64
Marking mysql-community-server-8.0.21-1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mariadb.x86_64 1:5.5.65-1.el7 will be obsoleted
---> Package mariadb-libs.x86_64 1:5.5.65-1.el7 will be obsoleted
---> Package mariadb-server.x86_64 1:5.5.65-1.el7 will be obsoleted
---> Package mysql-community-client.x86_64 0:8.0.21-1.el7 will be obsoleting
---> Package mysql-community-common.x86_64 0:8.0.21-1.el7 will be installed
---> Package mysql-community-libs.x86_64 0:8.0.21-1.el7 will be obsoleting
---> Package mysql-community-libs-compat.x86_64 0:8.0.21-1.el7 will be obsoleting
---> Package mysql-community-server.x86_64 0:8.0.21-1.el7 will be obsoleting
--> Finished Dependency Resolution
Dependencies Resolved
=======================================================================================================================================
 Package                            Arch          Version                Repository                                               Size
=======================================================================================================================================
Installing:
 mysql-community-client             x86_64        8.0.21-1.el7           /mysql-community-client-8.0.21-1.el7.x86_64             231 M
     replacing  mariadb.x86_64 1:5.5.65-1.el7
 mysql-community-common             x86_64        8.0.21-1.el7           /mysql-community-common-8.0.21-1.el7.x86_64             8.8 M
 mysql-community-libs               x86_64        8.0.21-1.el7           /mysql-community-libs-8.0.21-1.el7.x86_64                22 M
     replacing  mariadb-libs.x86_64 1:5.5.65-1.el7
 mysql-community-libs-compat        x86_64        8.0.21-1.el7           /mysql-community-libs-compat-8.0.21-1.el7.x86_64        6.1 M
     replacing  mariadb-libs.x86_64 1:5.5.65-1.el7
 mysql-community-server             x86_64        8.0.21-1.el7           /mysql-community-server-8.0.21-1.el7.x86_64             2.3 G
     replacing  mariadb-server.x86_64 1:5.5.65-1.el7
Transaction Summary
=======================================================================================================================================
Install  5 Packages
Total size: 2.6 G
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql-community-common-8.0.21-1.el7.x86_64                                                                          1/8 
  Installing : mysql-community-libs-8.0.21-1.el7.x86_64                                                                            2/8 
  Installing : mysql-community-client-8.0.21-1.el7.x86_64                                                                          3/8 
  Installing : mysql-community-server-8.0.21-1.el7.x86_64                                                                          4/8 
  Installing : mysql-community-libs-compat-8.0.21-1.el7.x86_64                                                                     5/8 
  Erasing    : 1:mariadb-server-5.5.65-1.el7.x86_64                                                                                6/8 
warning: /var/log/mariadb/mariadb.log saved as /var/log/mariadb/mariadb.log.rpmsave
  Erasing    : 1:mariadb-5.5.65-1.el7.x86_64                                                                                       7/8 
  Erasing    : 1:mariadb-libs-5.5.65-1.el7.x86_64                                                                                  8/8 
  Verifying  : mysql-community-server-8.0.21-1.el7.x86_64                                                                          1/8 
  Verifying  : mysql-community-libs-8.0.21-1.el7.x86_64                                                                            2/8 
  Verifying  : mysql-community-client-8.0.21-1.el7.x86_64                                                                          3/8 
  Verifying  : mysql-community-libs-compat-8.0.21-1.el7.x86_64                                                                     4/8 
  Verifying  : mysql-community-common-8.0.21-1.el7.x86_64                                                                          5/8 
  Verifying  : 1:mariadb-server-5.5.65-1.el7.x86_64                                                                                6/8 
  Verifying  : 1:mariadb-libs-5.5.65-1.el7.x86_64                                                                                  7/8 
  Verifying  : 1:mariadb-5.5.65-1.el7.x86_64                                                                                       8/8 
Installed:
  mysql-community-client.x86_64 0:8.0.21-1.el7                    mysql-community-common.x86_64 0:8.0.21-1.el7                        
  mysql-community-libs.x86_64 0:8.0.21-1.el7                      mysql-community-libs-compat.x86_64 0:8.0.21-1.el7                   
  mysql-community-server.x86_64 0:8.0.21-1.el7                   
Replaced:
  mariadb.x86_64 1:5.5.65-1.el7           mariadb-libs.x86_64 1:5.5.65-1.el7           mariadb-server.x86_64 1:5.5.65-1.el7          
Complete!
[root@riyimei tmp]#

[root@riyimei mysql]# pwd
/var/lib/mysql
[root@riyimei mysql]# ls
auto.cnf       binlog.index     client-key.pem     ib_buffer_pool  ibtmp1        mysql.sock          public_key.pem   test1
babytun        ca-key.pem       hisystem           ibdata1         #innodb_temp  mysql.sock.lock     server-cert.pem  undo_001
binlog.000001  ca.pem           #ib_16384_0.dblwr  ib_logfile0     mysql         performance_schema  server-key.pem   undo_002
binlog.000002  client-cert.pem  #ib_16384_1.dblwr  ib_logfile1     mysql.ibd     private_key.pem     sys
[root@riyimei mysql]# cd mysql/
[root@riyimei mysql]# ls
general_log_207.sdi  general_log.CSM  general_log.CSV  slow_log_208.sdi  slow_log.CSM  slow_log.CSV
[root@riyimei mysql]# cd /var/run/mysqld/
[root@riyimei mysqld]# ls
mysqld.pid  mysqlx.sock  mysqlx.sock.lock
[root@riyimei mysqld]#

[root@riyimei ~]# netstat -lntup
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      1061/sshd           
tcp6       0      0 :::33060                :::*                    LISTEN      1091/mysqld         
tcp6       0      0 :::3306                 :::*                    LISTEN      1091/mysqld         
tcp6       0      0 :::22                   :::*                    LISTEN      1061/sshd           
[root@riyimei ~]# 
[root@riyimei ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@riyimei ~]#

yum源

https://dev.mysql.com/downloads/repo/yum/

wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

[root@n9e tmp]# ll
total 28
-rw-r--r-- 1 root root 26024 Feb 19 16:36 mysql80-community-release-el7-3.noarch.rpm
[root@n9e tmp]# rpm -ivh mysql80-community-release-el7-3.noarch.rpm 
warning: mysql80-community-release-el7-3.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql80-community-release-el7-3  ################################# [100%]
[root@n9e tmp]# 
[root@n9e tmp]# cat /etc/yum.repos.d/m
mariadb.repo.bak             mariadb_repo_setup           mysql-community.repo         mysql-community-source.repo  
[root@n9e tmp]# cat /etc/yum.repos.d/mysql-community.repo 
# Enable to use MySQL 5.5
[mysql55-community]
name=MySQL 5.5 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.5-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
# Enable to use MySQL 5.6
[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
# Enable to use MySQL 5.7
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/7/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql-tools-community]
name=MySQL Tools Community
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/7/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql-tools-preview]
name=MySQL Tools Preview
baseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql-cluster-7.5-community]
name=MySQL Cluster 7.5 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-7.5-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql-cluster-7.6-community]
name=MySQL Cluster 7.6 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-7.6-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql-cluster-8.0-community]
name=MySQL Cluster 8.0 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-8.0-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[root@n9e tmp]#

yum install mysql-community-client mysql-community-server

[root@n9e ~]# yum repolist 
Loaded plugins: fastestmirror, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
Loading mirror speeds from cached hostfile
repo id                                                                    repo name                                                            status
mysql-connectors-community/x86_64                                          MySQL Connectors Community                                           185
mysql-tools-community/x86_64                                               MySQL Tools Community                                                123
mysql80-community/x86_64                                                   MySQL 8.0 Community Server                                           229
repolist: 537
[root@n9e ~]# yum install mysql-community-client mysql-community-server
Loaded plugins: fastestmirror, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-client.x86_64 0:8.0.23-1.el7 will be installed
--> Processing Dependency: mysql-community-client-plugins = 8.0.23-1.el7 for package: mysql-community-client-8.0.23-1.el7.x86_64
--> Processing Dependency: mysql-community-libs(x86-64) >= 8.0.11 for package: mysql-community-client-8.0.23-1.el7.x86_64
---> Package mysql-community-server.x86_64 0:8.0.23-1.el7 will be installed
--> Processing Dependency: mysql-community-common(x86-64) = 8.0.23-1.el7 for package: mysql-community-server-8.0.23-1.el7.x86_64
--> Running transaction check
---> Package mysql-community-client-plugins.x86_64 0:8.0.23-1.el7 will be installed
---> Package mysql-community-common.x86_64 0:8.0.23-1.el7 will be installed
---> Package mysql-community-libs.x86_64 0:8.0.23-1.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
======================================================================================================================================================
 Package                                          Arch                     Version                          Repository                           Size
======================================================================================================================================================
Installing:
 mysql-community-client                           x86_64                   8.0.23-1.el7                     mysql80-community                    48 M
 mysql-community-server                           x86_64                   8.0.23-1.el7                     mysql80-community                   518 M
Installing for dependencies:
 mysql-community-client-plugins                   x86_64                   8.0.23-1.el7                     mysql80-community                   237 k
 mysql-community-common                           x86_64                   8.0.23-1.el7                     mysql80-community                   621 k
 mysql-community-libs                             x86_64                   8.0.23-1.el7                     mysql80-community                   4.6 M
Transaction Summary
======================================================================================================================================================
Install  2 Packages (+3 Dependent packages)
Total download size: 572 M
Installed size: 2.6 G
Is this ok [y/d/N]:

[root@n9e ~]# rm -rf /var/lib/mysql/*
[root@n9e ~]# 
[root@n9e ~]# systemctl enable --now mysqld.service
[root@n9e ~]# systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2021-02-19 16:57:25 CST; 6s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 7856 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 7932 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─7932 /usr/sbin/mysqld
Feb 19 16:57:21 n9e systemd[1]: Starting MySQL Server...
Feb 19 16:57:25 n9e systemd[1]: Started MySQL Server.
[root@n9e ~]#

[root@n9e ~]# grep 'temporary password' /var/log/mysqld.log
2021-02-19T08:57:23.093478Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: wIgiWtyj-4)v
[root@n9e ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.23
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>

mysql> alter user 'root'@'localhost' identified by "!Q2w3e4r";
Query OK, 0 rows affected (0.03 sec)
mysql> exit
Bye
[root@n9e ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.23 MySQL Community Server - GPL
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>

下载历史版本

https://downloads.mysql.com/archives/cluster/

经典版本 MySQL5.5 MySQL5.6 MySQL5.7 读写性能对比

Mysql 8.0部署和操作 - 图3

Mysql 8.0部署和操作 - 图4

[root@riyimei tmp]# systemctl restart mysqld.service 
[root@riyimei tmp]# 
[root@riyimei tmp]# 
[root@riyimei tmp]# 
[root@riyimei tmp]# systemctl status  mysqld.service 
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2020-07-25 12:16:13 CST; 9s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 2312 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 2387 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─2387 /usr/sbin/mysqld
Jul 25 12:16:09 riyimei systemd[1]: Starting MySQL Server...
Jul 25 12:16:13 riyimei systemd[1]: Started MySQL Server.
[root@riyimei tmp]# netstat -lntup
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      2157/sshd           
tcp6       0      0 :::33060                :::*                    LISTEN      2387/mysqld         
tcp6       0      0 :::3306                 :::*                    LISTEN      2387/mysqld         
tcp6       0      0 :::22                   :::*                    LISTEN      2157/sshd           
[root@riyimei tmp]#

查看安装默认密码

[root@riyimei tmp]# grep 'temporary password' /var/log/mysqld.log
2020-07-25T04:16:10.579453Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ;<6%b1N5.tvm
[root@riyimei tmp]#

[root@riyimei tmp]# grep 'temporary password' /var/log/mysqld.log
2020-07-25T04:16:10.579453Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ;<6%b1N5.tvm
[root@riyimei tmp]# 
[root@riyimei tmp]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.21
Copyright (c) 2000, 2020, 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>

修改密码 alter user ‘root’@’localhost’ identified by “!Q2w3e4r”;

数据库密码策略

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             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)
mysql>

[root@riyimei tmp]# grep 'temporary password' /var/log/mysqld.log
2020-07-25T04:16:10.579453Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ;<6%b1N5.tvm
[root@riyimei tmp]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.21
Copyright (c) 2000, 2020, 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> alter user 'root'@'localhost' identified by "!Q2w3e4r";
Query OK, 0 rows affected (0.00 sec)
mysql>

跳过密码

skip-grant-tables

[root@n9e ~]# vim /etc/my.cnf
[root@n9e ~]# 
[root@n9e ~]# systemctl restart mysqld.service 
[root@n9e ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.23 MySQL Community Server - GPL
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> exit
Bye
[root@n9e ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-grant-tables
[root@n9e ~]#

重置密码

skip-grant-tables

use mysql
select host, user, authentication_string, plugin from user;
update user set authentication_string=’’ where user=’root’;

sed -i ‘$d’ /etc/my.cnf
systemctl restart mysqld.service
alter user ‘root’@’localhost’ identified by “!Q2w3e4r”;

[root@n9e ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-grant-tables
[root@n9e ~]# 
[root@n9e ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.23 MySQL Community Server - GPL
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> use mysql
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> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root             | $A$005$<5l!vq1Ov+[vt!OIfCFAoW.4ILpThA7TiD44PQEAbJ3GPuAq82TR4eBroC | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)
mysql> update user set authentication_string='' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> exit
Bye
[root@n9e ~]# sed -i '$d' /etc/my.cnf
[root@n9e ~]# systemctl restart mysqld.service 
[root@n9e ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.23 MySQL Community Server - GPL
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> alter user 'root'@'localhost' identified by "!Q2w3e4r";
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
[root@n9e ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.23 MySQL Community Server - GPL
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> exit
Bye
[root@n9e ~]#

安全初始化

mysql_secure_installation

[root@riyimei tmp]# mysql_secure_installation 
Securing the MySQL server deployment.
Enter password for user root: 
The 'validate_password' component is installed on the server.
The subsequent steps will run with the existing configuration
of the component.
Using existing password for root.
Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : 
 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : 
 ... skipping.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : 
 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : 
 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : 
 ... skipping.
All done! 
[root@riyimei tmp]#

数据类型

常见数据类型
字符串类型（CHAR(0-255固定长度)，VARCHAR(0-255 可变长度) ）
数值类型（INT （整数型）、FLOAT（浮点型））
日期和时间类型（DATA (年月日) 、TIME（时分秒））

8.0.1 7版本开始，TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT类型的显示宽度将失效

MySQL数据类型.pdf

约束类型

主键约束 primary key

外键约束 foreign key

唯一约束 unique

非空约束 not null 与默认值 default

MySQL索引

MySQL锁

MySQL存储引擎

MYSAM
INNODB
MEMORY

数据库系统
表示层：数据库-数据表-记录（字段）
逻辑层：数据库的存储引擎
物理层：数据库文件（*.sql）

文件系统
表示层：文件名（文件类型 .txt…）
逻辑层：文件系统类型（ext4、NFS）
物理层：分区块（数据块 data block）扇区

事务

SQL 分类 3个类别

DDL 语句
DML 语句
DCL 语句

DDL 语句：数据定义语言
定义了不同的数据段、数据库、表、列、索引等数据库对象（常用语句关键字：create、drop、alter）

DML 语句：数据操作语句
用于添加、删除、更新和查询数据库记录、检查数据完整性(常用语句关键字：insert、delete、update、select)

DCL 语句：数据控制语句
定义了数据库、表、字段、用户的访问权限和安全级别（常用语句关键字：grant、revoke）

DDL 语句

MySQL 常用命令

创建数据库：test1
CREATE DATABASE test1;
查看数据库
SHOW DATABASES;
选择数据库
USE mysql;
查看数据的表
SHOW TABLES;
删除数据库
DROP DATABASE test1;

ADD\CHANGE\MODIFY

mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.01 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.01 sec)
mysql>

系统库：

information_schema 用户表信息 mysql 存储系统用户权限信息 performance_schema 存储系统性能参数 sys 系统视图

mysql> 
mysql> USE mysql;
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_mysql           |
+---------------------------+
| columns_priv              |
| component                 |
| db                        |
| default_roles             |
| engine_cost               |
| func                      |
| general_log               |
| global_grants             |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| password_history          |
| plugin                    |
| procs_priv                |
| proxies_priv              |
| role_edges                |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
33 rows in set (0.00 sec)
mysql>

删除数据库

mysql> DROP DATABASE test1;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql>

创建表emp

mysql> use test1;
Database changed
mysql> 
mysql> 
mysql> show tables;
Empty set (0.00 sec)
mysql> 
mysql> create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2));
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| emp             |
+-----------------+
1 row in set (0.00 sec)
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>

mysql> create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2));
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| emp             |
+-----------------+
1 row in set (0.00 sec)
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show create table emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `ename` varchar(10) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `deptno` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.02 sec)
ERROR: 
No query specified
mysql>

修改表字段

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table emp modify ename varchar(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>

增加表字段

mysql> alter table emp add column age int(3);
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
| age      | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>

字段改名

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
| age      | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table emp change  age age1 int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
| age1     | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>

change和modify 都可以修改表的定义
change需要写两次列名 change可以修改列的名称、modify不可以

删除表字段

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
| age      | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table emp drop column age;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>

调整字段位置

选项 first|after column_name
ADD 增加的新字段默认加在表的最后位置，CHANGE/MODIFY默认都不会修改字段位置

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
| age1     | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table emp add birth date after ename;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
| age1     | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql>

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
| age      | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> alter table emp modify age int first;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| age      | int           | YES  |     | NULL    |       |
| ename    | varchar(20)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>

更改表名

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| age      | int           | YES  |     | NULL    |       |
| ename    | varchar(20)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> alter table emp rename emp1;
Query OK, 0 rows affected (0.01 sec)
mysql> desc emp;
ERROR 1146 (42S02): Table 'test1.emp' doesn't exist
mysql> desc emp1;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| age      | int           | YES  |     | NULL    |       |
| ename    | varchar(20)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int           | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql>

删除表emp1

mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| emp1            |
+-----------------+
1 row in set (0.00 sec)
mysql> drop table emp1;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql>

查看在哪个库

select database();

mysql> select database();
+------------+
| database() |
+------------+
| test1      |
+------------+
1 row in set (0.00 sec)
mysql>

创建用户

mysql> use mysql;
Database changed
mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
4 rows in set (0.00 sec)
mysql> create user 'liwm'@'localhost' identified by '!Q2w3e4r';
Query OK, 0 rows affected (0.00 sec)
mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | liwm             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
5 rows in set (0.01 sec)
mysql>

修改用户密码

mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | liwm             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
9 rows in set (0.00 sec)
mysql> alter user 'liwm'@'localhost' identified by '!Q2w3e4r';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> 
[root@riyimei mysqld]# mysql -uliwm  -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 8.0.21 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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 |
+--------------------+
1 row in set (0.00 sec)
mysql>

查看用户权限

mysql> SELECT user,host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| liwm             | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM mysql.user WHERE user='liwm'\G;
*************************** 1. row ***************************
                    Host: localhost
                    User: liwm
             Select_priv: N
             Insert_priv: N
             Update_priv: N
             Delete_priv: N
             Create_priv: N
               Drop_priv: N
             Reload_priv: N
           Shutdown_priv: N
            Process_priv: N
               File_priv: N
              Grant_priv: N
         References_priv: N
              Index_priv: N
              Alter_priv: N
            Show_db_priv: N
              Super_priv: N
   Create_tmp_table_priv: N
        Lock_tables_priv: N
            Execute_priv: N
         Repl_slave_priv: N
        Repl_client_priv: N
        Create_view_priv: N
          Show_view_priv: N
     Create_routine_priv: N
      Alter_routine_priv: N
        Create_user_priv: N
              Event_priv: N
            Trigger_priv: N
  Create_tablespace_priv: N
                ssl_type: 
              ssl_cipher: 0x
             x509_issuer: 0x
            x509_subject: 0x
           max_questions: 0
             max_updates: 0
         max_connections: 0
    max_user_connections: 0
                  plugin: caching_sha2_password
   authentication_string: $A$005$YT%;JO ~5JxnA6nv=6e3/XsJh3e8692Jcwlu0hNG.NLgbUJM60SGdot3TBQA
        password_expired: N
   password_last_changed: 2020-07-26 18:30:35
       password_lifetime: NULL
          account_locked: N
        Create_role_priv: N
          Drop_role_priv: N
  Password_reuse_history: NULL
     Password_reuse_time: NULL
Password_require_current: NULL
         User_attributes: NULL
1 row in set (0.00 sec)
ERROR: 
No query specified
mysql>

mysql> desc user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                    | Type                              | Null | Key | Default               | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                     | char(255)                         | NO   | PRI |                       |       |
| User                     | char(32)                          | NO   | PRI |                       |       |
| Select_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv                | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv                | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv      | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv   | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type                 | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher               | blob                              | NO   |     | NULL                  |       |
| x509_issuer              | blob                              | NO   |     | NULL                  |       |
| x509_subject             | blob                              | NO   |     | NULL                  |       |
| max_questions            | int unsigned                      | NO   |     | 0                     |       |
| max_updates              | int unsigned                      | NO   |     | 0                     |       |
| max_connections          | int unsigned                      | NO   |     | 0                     |       |
| max_user_connections     | int unsigned                      | NO   |     | 0                     |       |
| plugin                   | char(64)                          | NO   |     | caching_sha2_password |       |
| authentication_string    | text                              | YES  |     | NULL                  |       |
| password_expired         | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed    | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime        | smallint unsigned                 | YES  |     | NULL                  |       |
| account_locked           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_role_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_role_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Password_reuse_history   | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_reuse_time      | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_require_current | enum('N','Y')                     | YES  |     | NULL                  |       |
| User_attributes          | json                              | YES  |     | NULL                  |       |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.00 sec)

用户授权

mysql> grant all on *.* to 'liwm'@'localhost';
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT user,host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| liwm             | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM mysql.user WHERE user='liwm'\G;
*************************** 1. row ***************************
                    Host: localhost
                    User: liwm
             Select_priv: Y
             Insert_priv: Y
             Update_priv: Y
             Delete_priv: Y
             Create_priv: Y
               Drop_priv: Y
             Reload_priv: Y
           Shutdown_priv: Y
            Process_priv: Y
               File_priv: Y
              Grant_priv: N
         References_priv: Y
              Index_priv: Y
              Alter_priv: Y
            Show_db_priv: Y
              Super_priv: Y
   Create_tmp_table_priv: Y
        Lock_tables_priv: Y
            Execute_priv: Y
         Repl_slave_priv: Y
        Repl_client_priv: Y
        Create_view_priv: Y
          Show_view_priv: Y
     Create_routine_priv: Y
      Alter_routine_priv: Y
        Create_user_priv: Y
              Event_priv: Y
            Trigger_priv: Y
  Create_tablespace_priv: Y
                ssl_type: 
              ssl_cipher: 0x
             x509_issuer: 0x
            x509_subject: 0x
           max_questions: 0
             max_updates: 0
         max_connections: 0
    max_user_connections: 0
                  plugin: caching_sha2_password
   authentication_string: $A$005$M#oi(
                                      xeeg 3fYqN8zHbU7.vv3EDBKqmY4pzKUN1dNYg4KN.j5k8dQ5
        password_expired: N
   password_last_changed: 2020-07-26 20:12:10
       password_lifetime: NULL
          account_locked: N
        Create_role_priv: Y
          Drop_role_priv: Y
  Password_reuse_history: NULL
     Password_reuse_time: NULL
Password_require_current: NULL
         User_attributes: NULL
1 row in set (0.00 sec)
ERROR: 
No query specified
mysql>

[root@riyimei mysqld]# mysql -uliwm  -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 8.0.21 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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           |
+--------------------+
| babytun            |
| hisystem           |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
7 rows in set (0.00 sec)
mysql>

用户权限回收

用户角色权限管理

删除用户

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| component                 |
| db                        |
| default_roles             |
| engine_cost               |
| func                      |
| general_log               |
| global_grants             |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| password_history          |
| plugin                    |
| procs_priv                |
| proxies_priv              |
| role_edges                |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
33 rows in set (0.00 sec)
mysql> SELECT user,host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| liwm             | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
mysql> drop user 'liwm'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql>

DML 语句

指的是对数据库中表的操作记录、包括表记录的插入（inser）、更新（update）、删除（delete）、和查询（select）

1 插入记录

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| babytun            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
6 rows in set (0.00 sec)
mysql> use babytun;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_babytun |
+-------------------+
| t_category        |
| t_goods           |
| t_goods_cover     |
| t_goods_detail    |
| t_goods_param     |
+-------------------+
5 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM t_goods_cover;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_goods_cover | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9469 |   100.00 | NULL  |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>