mysql8.0-学习

发布于：2024-07-04 ⋅ 阅读:(27) ⋅ 点赞:(0)

文章目录

mysql8.0基础知识-学习

安装mysql_8.0

利用xftp拖拽进来: mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz

image-20240420120142409

useradd mysql

cd /opt

tar xf mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz

ln -s /opt/mysql-8.0.27-linux-glibc2.12-x86_64 /usr/local/mysql

vim /etc/profile
export PATH=/usr/local/mysql/bin:$PATH

source /etc/profile

mysql -V

#创建目标并授权
mkdir -p /data/3306/data
chown mysql.mysql /data

#创建配置文件，删除里边原有的数据
vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=51
[mysql]
socket=/tmp/mysql.sock
  1. [mysqld]：这是一个组名，用于定义 MySQL 服务器的全局系统变量。这些变量影响整个数据库服务器的运行。
    • user=mysql：定义了运行 MySQL 服务器进程的用户。这里指定了 mysql 用户。
    • basedir=/usr/local/mysql：指定了 MySQL 安装的基本目录。这通常是 MySQL 的安装路径，其中包含了 MySQL 的二进制文件和库文件。
    • datadir=/data/3306/data：指定了 MySQL 数据文件存放的目录。这是数据库文件（如表的数据和索引）存储的地方。
    • socket=/tmp/mysql.sock：定义了 MySQL 服务器进程监听的 Unix 套接字文件。客户端可以通过这个套接字文件连接到 MySQL 服务器。
    • server_id=51：为 MySQL 服务器指定了一个唯一的 ID。在复制集群中，每个服务器都需要有一个不同的 server_id。
  2. [mysql]：这是另一个组名，用于定义 MySQL 客户端的系统变量。
    • socket=/tmp/mysql.sock：指定了 MySQL 客户端使用的 Unix 套接字文件。这通常与 [mysqld] 组中的 socket 变量相对应，确保客户端知道在哪里找到服务器进程。
#初始化数据(建库)，选第一个方法就行
mysqld --initialize-insecure          '这是常规的初始化方法'--->这个不会生成密码，需要自己设置

mysqld --initialize					 '这是第二种方法'--->会自动创建root的零时密码

#启动数据库
cd /usr/local/mysql/support-files/

./mysql.server start

# 做成服务
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

service mysqld restart

# 设置开机自启动
service mysqld stop
systemctl enable mysqld
systemctl start mysqld

初始化命令还可以写全一点：

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data

# 写成一键部署脚本

useradd mysql

cd /opt

tar xf mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz

ln -s /opt/mysql-8.0.27-linux-glibc2.12-x86_64 /usr/local/mysql

sudo cat << EOF >> /etc/profile
export PATH=/usr/local/mysql/bin:$PATH
EOF

source /etc/profile

#测试是否存在
mysql -V

mkdir -p /data/3306/data
chown mysql.mysql /data

#创建配置文件

登录

直接输入 mysql

[root@mysql-1 support-files]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 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>

mysql8.0的体系结构与管理

体系结构图

image-20240421114513316

连接mysql

image-20240421115247121

image-20240421115307144

mysql8.0的 “新姿势”

image-20240421115355252

image-20240421115404240

mysql的日常管理

用户安全

image-20240421122042827

image-20240421122342497

image-20240421122408542

image-20240421122425345

image-20240421122440314

image-20240421122506196

image-20240421122528041

image-20240421122539138

'查询用户的命令'
mysql> select user,host ,authentication_string ,plugin from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user             | host      | authentication_string                                                  | plugin                |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| root             | localhost |                                                                        | caching_sha2_password |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

mysql> 


'创建用户，设置密码'
mysql> create user gaohui@'192.168.182.%';
Query OK, 0 rows affected (0.00 sec)

mysql> 

mysql> create user test@'192.168.182.%' identified by '123';
Query OK, 0 rows affected (0.01 sec)

mysql> 
'一般如果要兼容老版本：用with mysql_native_password'
mysql> create user user1@'192.168.182.%' identified with mysql_native_password by '123
3';
Query OK, 0 rows affected (0.01 sec)

mysql> 


'修改---》密码'
alter user gaohui@'192.168.182.%' identified with mysql_native_password by '123456';
alter user gaohui@'192.168.182.%' identified by '12345';
刷新一下：
flush privileges;

'修改--》锁用户'
alter user user1@'192.168.182.%' account lock;
'解锁'
mysql> alter user user1@'192.168.182.%' account unlock;
mysql> select user,host ,authentication_string ,plugin,account_locked from mysql.user;
+------------------+---------------+------------------------------------------------------------------------+-----------------------+----------------+
| user             | host          | authentication_string                                                  | plugin                | account_locked |
+------------------+---------------+------------------------------------------------------------------------+-----------------------+----------------+
| user1            | 192.168.182.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257                              | mysql_native_password | N              |
| mysql.infoschema | localhost     | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | Y              |
| mysql.session    | localhost     | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | Y              |
| mysql.sys        | localhost     | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | Y              |
| root             | localhost     |                                                                        | caching_sha2_password | N              |
+------------------+---------------+------------------------------------------------------------------------+-----------------------+----------------+
5 rows in set (0.00 sec)

mysql> 

'删除'
drop user gaohui@'192.168.182.%';
drop user test@'192.168.182.%';

mysql> select user,host from mysql.user where (user='' or host='' or
authentication_string='') and user!='root';

权限

image-20240421122625990

image-20240421171339026

image-20240421172259234

练习

mysql> create user test@'192.168.182.%' identified with mysql_native_password by '123';
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> create user test@'localhost' identified with mysql_native_password by '123';
Query OK, 0 rows affected (0.01 sec)

mysql> 

mysql> grant all on *.* to test@'192.168.182.%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to test@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql>

假如来了个开发人员

dev_user1

可以远程登录，开发，dev库

 Create ,Create routine,Create temporary tables,Create view,Show view
,Delete ,Event ,Execute,Insert ,References,Select,Trigger,Update

mysql> grant  Create ,Create routine,Create temporary tables,Create view,Show view on dev_db.* to dev_user1@'192.168.182.%';
mysql> create database dev_db charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql>

创建一个主从复制相关用户

repl，复制用户

create user repl@'192.168.182.%' identified with mysql_native_password by '123';
grant replication slave,replication client on *.* to repl@'192.168.182.%';

查看用户的权限

mysql> show grants for repl@'192.168.182.%';
+------------------------------------------------------------------------------+
| Grants for repl@192.168.182.%                                                |
+------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `repl`@`192.168.182.%` |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 


mysql> select * from mysql.user where user='dev_user1' \G
*************************** 1. row ***************************
                    Host: 192.168.182.%
                    User: dev_user1
             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: mysql_native_password
   authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
        password_expired: N
   password_last_changed: 2024-04-24 14:37:48
       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)

mysql> 

select * from mysql.db where user='dev_user1' \G

回收:revoke

mysql> show grants for dev_user1@'192.168.182.%';
+------------------------------------------------------------------------------------------------------------------------------------+
| Grants for dev_user1@192.168.182.%                                                                                                 |
+------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev_user1`@`192.168.182.%`                                                                                  |
| GRANT DELETE, CREATE, CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE ON `dev_db`.* TO `dev_user1`@`192.168.182.%` |
+------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> 

mysql> revoke delete on dev_db.* from dev_user1@'192.168.182.%';
Query OK, 0 rows affected (0.00 sec)

image-20240424152009653

角色

image-20240424152118440

mysql> create role dev_r@'192.168.182.%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW, DELETE, EVENT, EXECUTE, INSERT, REFERENCES, SELECT, TRIGGER, UPDATE on dev_db.* to 'dev_r'@'192.168.182.%';
Query OK, 0 rows affected (0.00 sec)

mysql>

create user dev_1@'192.168.182.%' identified with mysql_native_password by '123';
grant dev_r@'192.168.182.%' to dev1@'192.168.182.%';

mysql的多种连接方式

socket

image-20240424153117999

socket —》前提： 1.数据库启动 2.必须是localhost白名单的用户才能socket登录

image-20240424153832869

显示系统中当前运行的所有线程

image-20240424154331944

tcp/ip

image-20240424153136161

客户端工具

image-20240424153151000

基于SSL的安全连接

mysql> show variables like '%ssl%';
+-------------------------------------+-----------------+
| Variable_name                       | Value           |
+-------------------------------------+-----------------+
| admin_ssl_ca                        |                 |
| admin_ssl_capath                    |                 |
| admin_ssl_cert                      |                 |
| admin_ssl_cipher                    |                 |
| admin_ssl_crl                       |                 |
| admin_ssl_crlpath                   |                 |
| admin_ssl_key                       |                 |
| have_openssl                        | YES             |
| have_ssl                            | YES             |
| mysqlx_ssl_ca                       |                 |
| mysqlx_ssl_capath                   |                 |
| mysqlx_ssl_cert                     |                 |
| mysqlx_ssl_cipher                   |                 |
| mysqlx_ssl_crl                      |                 |
| mysqlx_ssl_crlpath                  |                 |
| mysqlx_ssl_key                      |                 |
| performance_schema_show_processlist | OFF             |
| ssl_ca                              | ca.pem          |
| ssl_capath                          |                 |
| ssl_cert                            | server-cert.pem |
| ssl_cipher                          |                 |
| ssl_crl                             |                 |
| ssl_crlpath                         |                 |
| ssl_fips_mode                       | OFF             |
| ssl_key                             | server-key.pem  |
+-------------------------------------+-----------------+
25 rows in set (0.01 sec)

mysql>

mysql8.0初始化配置方式

初始化配置文件应用

image-20240424153315681

image-20240424153336546

mysql8.0的启动和关闭

image-20240424214659028

image-20240424214800629

MySQL 8.0多实例的配置应用

image-20240424215846879

image-20240424215859543

image-20240424220017116

image-20240424220023928

不同版本的

image-20240424220043454

image-20240424220054870

image-20240424220113458

image-20240424220124899

MySQL 8.0的工具日志配置管理

image-20240424220306749

错误日志

image-20240424220342496

image-20240424220359417

image-20240424220411366

二进制日志

image-20240424220432094

image-20240424220502694

慢日志（slow_log）

image-20240424220616809

general_log

image-20240424220644499

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布