Linux | MySQL基础

MySQL

MySQL是关系型数据库（RDBMS），采用表格结构存储数据

同类型的还有Oracle、SQL Server

主要特点

适用于中小规模、关系型数据库系统(RDBMS)

支持Linux、Unix、Windows等多种操作系统

支持Python、Java、Perl、PHP等编程语言

典型应用环境

LAMP平台，与ApacheHTTP Server组合

LNMP平台，与Nginx组合

构建MySQL服务器

1、安装软件

[root@hfj2 ~]# yum -y install mysql-server  mysql //安装提供服务和命令软件

[root@hfj2 ~]# rpm -q mysql-server  mysql
mysql-server-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64
mysql-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64

查看软件信息

[root@hfj2 ~]# rpm -qi mysql-server
Name        : mysql-server
Version     : 8.0.17
Release     : 3.module+el8.0.0+3898+e09bb8de
Architecture: x86_64
Install Date: Fri 05 Apr 2024 04:38:28 PM CST
Group       : Unspecified
Size        : 144964203
License     : GPLv2 with exceptions and LGPLv2 and BSD
Signature   : RSA/SHA256, Thu 08 Aug 2019 03:12:02 PM CST, Key ID 199e2f91fd431d51
Source RPM  : mysql-8.0.17-3.module+el8.0.0+3898+e09bb8de.src.rpm
Build Date  : Wed 07 Aug 2019 11:49:17 PM CST
Build Host  : x86-vm-02.build.eng.bos.redhat.com
Relocations : (not relocatable)
Packager    : Red Hat, Inc. <http://bugzilla.redhat.com/bugzilla>
Vendor      : Red Hat, Inc.
URL         : http://www.mysql.com
Summary     : The MySQL server and related files
Description :
MySQL is a multi-user, multi-threaded SQL database server. MySQL is a
client/server implementation consisting of a server daemon (mysqld)
and many different client programs and libraries. This package contains
the MySQL server and some accompanying files and directories.

启动服务，设置服务开机运行

[root@hfj2 ~]# systemctl  start  mysqld
[root@hfj2 ~]# systemctl  enable  mysqld
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.

2、查看端口号和进程名

MySQL 8中的3306端口是MySQL服务默认使用的端口，主要用于建立客户端与MySQL服务器之间的连接。

MySQL 8中的33060端口是MySQL Shell默认使用的管理端口，主要用于执行各种数据库管理任务。远程管理MySQL服务器：使用MySQL Shell连接到MySQL服务，并在远程管理控制台上执行各种数据库管理操作，例如创建、删除、备份和恢复数据库等。

仅查看mysqld进程

[root@hfj2 ~]# ps -C mysqld
    PID TTY          TIME CMD
   3656 ?        00:00:01 mysqld

查看端口

[root@hfj2 ~]# ss -utnlp  | grep 3306  
tcp    LISTEN   0        128                     *:3306                 *:*      users:(("mysqld",pid=3656,fd=32))                                              
tcp    LISTEN   0        70                      *:33060                *:*      users:(("mysqld",pid=3656,fd=34))  

仅查看mysqld进程

[root@hfj2 ~]# netstat  -utnlp  | grep  mysqld
tcp6       0      0 :::3306                 :::*                    LISTEN      3656/mysqld         
tcp6       0      0 :::33060                :::*                    LISTEN      3656/mysqld         

3、连接服务

数据库管理员本机登陆默认没有密码

[root@hfj2 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.17 Source distribution

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

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

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

mysql> exit
Bye

4、练习必备命令的使用

mysql> select version() ;  //查看数据库软件版本
+-----------+
| version() |
+-----------+
| 8.0.26    |
+-----------+
1 row in set (0.00 sec)
mysql> select user() ; //查看登陆的用户和客户端地址 
+----------------+
| user()         |
+----------------+
| root@localhost | 管理员root本机登陆
+----------------+
1 row in set (0.00 sec) 
mysql> show databases; //查看已有的库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

默认4个库 不可以删除，存储的是 服务运行时加载的不同功能的程序和数据。

information_schema：是MySQL数据库提供的一个虚拟的数据库，存储了MySQL数据库中的相关信息，比如数据库、表、列、索引、权限、角色等信息。它并不存储实际的数据，而是提供了一些视图和存储过程，用于查询和管理数据库的元数据信息。

mysql：存储了MySQL服务器的系统配置、用户、账号和权限信息等。它是MySQL数据库最基本的库，存储了MySQL服务器的核心信息。

performance_schema：存储了MySQL数据库的性能指标、事件和统计信息等数据，可以用于性能分析和优化。

sys：是MySQL 8.0引入的一个新库，它基于information_schema和performance_schema视图，提供了更方便、更直观的方式来查询和管理MySQL数据库的元数据和性能数据。

mysql> select database();  //查看当前在那个库里  null 表示没有在任何库里
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)
mysql> use mysql ; //切换到mysql库
mysql> select database();  // 再次显示所在的库
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)
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                                         |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version              |
| replication_group_member_actions                     |
| 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                                                 |
+------------------------------------------------------+
37 rows in set (0.00 sec)
mysql> exit ;  断开连接
Bye

密码管理

1、设置root密码为redhat1

2行输出是警告而已不用关心

[root@hfj2 ~]# mysqladmin  -uroot -p  password "redhat1"
Enter password: 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

密码登录

[root@hfj2 ~]# mysql -uroot -predhat1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.17 Source distribution

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

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

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

mysql> 

2、修改密码为redhat2

[root@hfj2 ~]# mysqladmin  -uroot -predhat1 password "redhat2"
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

3、破解root密码为redhat3

a.修改主配置文件

skip-grant-tables  //手动添加此行 作用登陆时不验证密码

[root@hfj2 ~]# vim /etc/my.cnf.d/mysql-server.cnf

 重启服务 作用让服务以新配置运行

[root@hfj2 ~]# systemctl  restart mysqld

连接服务，查看存放密码的表头名mysql.user，把mysql库下user表中 用户root的密码设置为无

Mysql> desc  mysql.user ;
mysql> update mysql.user set authentication_string="" where user="root";
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> exit;
Bye

编辑配置文件，注释添加的行  #skip-grant-tables

 重启服务 作用让服务以新配置运行

[root@hfj2 ~]# systemctl  restart mysqld

无密码登陆，设置root用户本机登陆密码

mysql> alter user root@"localhost" identified by "redhat3";
Query OK, 0 rows affected (0.00 sec)

用redhat3密码登录

[root@hfj2 ~]# mysql -uroot -predhat3
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.17 Source distribution

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

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

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

mysql>