文章目录
- mysql8.0基础知识-学习
- 安装mysql_8.0
- 登录
- mysql8.0的体系结构与管理
- 体系结构图
- 连接mysql
- mysql8.0的 “新姿势”
- mysql的日常管理
- 用户安全
- 权限
- 练习
- 查看用户的权限
- 回收:revoke
- 角色
- mysql的多种连接方式
- socket
- 显示系统中当前运行的所有线程
- tcp/ip
- 客户端工具
- 基于SSL的安全连接
- mysql8.0初始化配置方式
- 初始化配置文件应用
- mysql8.0的启动和关闭
- MySQL 8.0多实例的配置应用
- 不同版本的
- MySQL 8.0的工具日志配置管理
- 错误日志
- 二进制日志
- 慢日志(slow_log)
- general_log
mysql8.0基础知识-学习
安装mysql_8.0
利用xftp拖拽进来: mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz
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
[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
。[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的体系结构与管理
体系结构图
连接mysql
mysql8.0的 “新姿势”
mysql的日常管理
用户安全
'查询用户的命令'
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';
权限
练习
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)
角色
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
socket —》前提: 1.数据库启动 2.必须是localhost白名单的用户才能socket登录
显示系统中当前运行的所有线程
tcp/ip
客户端工具
基于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>