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>
