MySQL基础【学习至基本语句】

一、安装与配置

1、安装

yum install -y mysql-server.x86_64

2、MySQL安装完成后，启动报错，查看MySQL的状态，发现是3306端口被占用

[root@iZ56kkvaq4nlfhZ etc]# systemctl status mysqld.service
● mysqld.service - MySQL 8.0 database serverLoaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)Active: failed (Result: exit-code) since Tue 2023-09-12 18:58:37 CST; 10s agoProcess: 655712 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=0/SUCCESS)Process: 655672 ExecStart=/usr/libexec/mysqld --basedir=/usr (code=exited, status=1/FAILURE)Process: 655635 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS)Process: 655610 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)Main PID: 655672 (code=exited, status=1/FAILURE)Status: "Server startup in progress"Error: 98 (Address already in use)Sep 12 18:58:34 iZ56kkvaq4nlfhZ systemd[1]: Starting MySQL 8.0 database server...
Sep 12 18:58:37 iZ56kkvaq4nlfhZ systemd[1]: mysqld.service: Main process exited, code=exited, status=1/FAILURE
Sep 12 18:58:37 iZ56kkvaq4nlfhZ systemd[1]: mysqld.service: Failed with result 'exit-code'.
Sep 12 18:58:37 iZ56kkvaq4nlfhZ systemd[1]: Failed to start MySQL 8.0 database server.
[root@iZ56kkvaq4nlfhZ etc]# 

此时，可以查询是谁占用了3306，然后把占用进程kill掉，此处就可以使用命令kill 57039，但是我不想这么做，这个进程和我的一个服务有关，不能杀

[root@iZ56kkvaq4nlfhZ etc]# lsof -i :3306
COMMAND   PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  57039  www   35u  IPv6 149967      0t0  TCP *:mysql (LISTEN)
[root@iZ56kkvaq4nlfhZ etc]# netstat -anp | grep :3306
tcp6       0      0 :::3306                 :::*                    LISTEN      57039/mysqld 

于是，我选择修改MySQL的启动端口，将启动端口修改为3307，编辑下面的配置文件

 vim /etc/my.cnf.d/mysql-server.cnf

添加一行内容，如下：

port=3307

此时配置文件就变成了：

[root@iZ56kkvaq4nlfhZ ~]# cat /etc/my.cnf.d/mysql-server.cnf 
#
# This group are read by MySQL server.
# Use it for options that only the server (but not clients) should see
#
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/en/server-configuration-defaults.html# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
port=3307    # 添加这一行，端口修改为3307[root@iZ56kkvaq4nlfhZ ~]# 

然后重启MySQL，就可以正常启动了

[root@iZ56kkvaq4nlfhZ etc]# systemctl restart mysqld.service 
[root@iZ56kkvaq4nlfhZ etc]# systemctl status mysqld.service 
● mysqld.service - MySQL 8.0 database serverLoaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor prese>   Active: active (running) since Tue 2023-09-12 19:09:51 CST; 16min agoProcess: 655712 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=>  Process: 656204 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, st>  Process: 656122 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (>  Process: 656097 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, stat> Main PID: 656159 (mysqld)Status: "Server is operational"Tasks: 37 (limit: 10836)Memory: 354.7MCGroup: /system.slice/mysqld.service└─656159 /usr/libexec/mysqld --basedir=/usrSep 12 19:09:50 iZ56kkvaq4nlfhZ systemd[1]: Starting MySQL 8.0 database server...
Sep 12 19:09:51 iZ56kkvaq4nlfhZ systemd[1]: Started MySQL 8.0 database server.

修改MySQL的密码，修改密码之后，再登录MySQL需要使用密码。

mysql  # 进入MySQL命令行

# MySQL的用户名密码存储在这个数据库中，修改密码，必须使用这个数据库
mysql> use mysql;   
# 查看数据库中的用户
mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
# 修改'root'@'localhost'的密码
mysql> alter user 'root'@'localhost' identified with mysql_native_password by '12345';
Query OK, 0 rows affected (0.01 sec)
# 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)mysql> 
# 此时端口开放3307
mysql> show variables like '%port%' ;
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| admin_port               | 33062 |
| large_files_support      | ON    |
| mysqlx_port              | 33060 |
| mysqlx_port_open_timeout | 0     |
| port                     | 3307  |
| report_host              |       |
| report_password          |       |
| report_port              | 3307  |
| report_user              |       |
| require_secure_transport | OFF   |
+--------------------------+-------+
10 rows in set (0.01 sec)mysql> exit
Bye

设置MySQL监听所有IP，更改配置文件后重启MySQL

[root@iZ56kkvaq4nlfhZ etc]# vim /etc/my.cnf.d/mysql-server.cnf 
[root@iZ56kkvaq4nlfhZ etc]# cat /etc/my.cnf.d/mysql-server.cnf
#
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
port=3307
bind-address=0.0.0.0   # 添加这一行
[root@iZ56kkvaq4nlfhZ etc]#

[root@iZ56kkvaq4nlfhZ etc]# systemctl restart mysqld.service 
[root@iZ56kkvaq4nlfhZ etc]# systemctl status mysqld.service 
● mysqld.service - MySQL 8.0 database serverLoaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)Active: active (running) since Tue 2023-09-12 20:56:18 CST; 11s agoProcess: 657283 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=0/SUCCESS)Process: 657493 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS)Process: 657411 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS)Process: 657385 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)Main PID: 657448 (mysqld)Status: "Server is operational"Tasks: 38 (limit: 10836)Memory: 356.1MCGroup: /system.slice/mysqld.service└─657448 /usr/libexec/mysqld --basedir=/usrSep 12 20:56:17 iZ56kkvaq4nlfhZ systemd[1]: Starting MySQL 8.0 database server...
Sep 12 20:56:18 iZ56kkvaq4nlfhZ systemd[1]: Started MySQL 8.0 database server.
[root@iZ56kkvaq4nlfhZ etc]#

创建一个可以远程连接的root用户

CREATE USER 'root'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

二、常用命令