MySQL安装&数据库、表操作

最新推荐文章于 2025-07-07 12:30:30 发布

原创最新推荐文章于 2025-07-07 12:30:30 发布 · 327 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#linux #mysql #centos #数据库

本文详细介绍了在CentOS8上安装MySQL5.7的步骤，包括添加仓库、关闭默认AppStream仓库、安装配置、安全设置以及如何使用MySQL命令行工具进行数据库和表的操作。

centos8安装mysql5.7

添加MySQL5.7仓库

关闭Centos8中MySQL默认的AppStream仓库：

[root@localhost ~]# dnf remove @mysql
CentOS Stream 8 - AppStream                                                    1.2 MB/s | 6.7 MB     00:05    
CentOS Stream 8 - BaseOS                                                       1.4 MB/s | 2.3 MB     00:01    
CentOS Stream 8 - Extras                                                       9.6 kB/s | 9.3 kB     00:00    
无法匹配参数 mysql 中的配置档案
依赖关系解决。
无需任何处理。
完毕！

[root@localhost ~]# dnf module reset mysql && sudo dnf module disable mysql
上次元数据过期检查：0:00:16 前，执行于q 2021年04月28日 星期三 08时40分48秒。
依赖关系解决。
无需任何处理。
完毕！
上次元数据过期检查：0:00:17 前，执行于 2021年04月28日 星期三 08时40分48秒。
依赖关系解决。
===============================================================================================================
 软件包                    架构                     版本                       仓库                       大小
===============================================================================================================
禁用模块:
 mysql                                                                                                        

事务概要
===============================================================================================================

确定吗？[y/N]： y
完毕！

目前还没有EL8版本的MySQL仓库，所以我们这里用EL7的代替，创建一个新的仓库文件：

[root@localhost ~]# vi /etc/yum.repos.d/mysql-community.repo

然后将以下内容粘贴到新建的仓库文件中：

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0

[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/7/$basearch/
enabled=1
gpgcheck=0

[mysql-tools-community]
name=MySQL Tools Community
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/7/$basearch/
enabled=1
gpgcheck=0

开始安装MySQL5.7

[root@localhost ~]# dnf --enablerepo=mysql57-community install mysql-community-server			//过程中可能需要输入两个y（同意

安装完成以后开始配置

[root@localhost ~]# systemctl enable --now mysqld		//启动mysql并设置开机自动启动
[root@localhost ~]# systemctl status mysqld				//查看mysql的状态
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2021-04-28 08:57:31 EDT; 34min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 40119 (mysqld)
    Tasks: 28 (limit: 11201)
   Memory: 309.7M
   CGroup: /system.slice/mysqld.service
           └─40119 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

4月 28 08:57:28 localhost.localdomain systemd[1]: Starting MySQL Server...
4月 28 08:57:31 localhost.localdomain systemd[1]: Started MySQL Server.

查看3306端口是否开启

[root@localhost ~]# ss -antl
State       Recv-Q       Send-Q             Local Address:Port             Peer Address:Port      Process      
LISTEN      0            128                      0.0.0.0:22                    0.0.0.0:*                      
LISTEN      0            80                             *:3306                        *:*                      
LISTEN      0            128                         [::]:22                       [::]:*

在日志文件中找出临时密码

[root@localhost ~]# grep "password" /var/log/mysqld.log
2021-04-28T12:57:28.951612Z 1 [Note] A temporary password is generated for root@localhost: tJ>)gt7Mh#ds				//：后面就是临时密码

使用刚才获取到的临时密码登录mysql

[root@localhost ~]# mysql -u root -p
Enter password: 			//这里输入密码，可以粘贴也可以手动输入
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.34 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> 					//看到这个标识就是成功登录了

mysql> quit					//输入quit退出mysql
Bye
[root@localhost ~]#

接着开始对mysql进行安全配置，通过MySQL Secure Installation去修改密码、关闭root远程登陆权限,、删除匿名用户、删除测试数据库等：

[root@localhost ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 			//这里输入root用户的密码（临时获取的）
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n	//修改root用户密码?((按y|Y为Yes，按其他键为No):

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y	//删除匿名用户?(按y|Y为Yes，按其他键为No): 
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n		//禁止root远程登录?(按y|Y为“是”，按其他键为“否”):

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y	//删除测试数据库并访问它?(按y| y为Yes，按其他键为No): 
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : n		//现在重新加载特权表?(按y| y为“是”，按其他键为“否”):

 ... skipping.
All done!

现在就开始使用MySQL命令行工具操作数据库了

修改MySQL登录密码

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'P@ssw0rd???';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

MySQL工具使用

//语法：mysql [OPTIONS] [database]
//常用的OPTIONS：
    -uUSERNAME      //指定用户名，默认为root
    -hHOST          //指定服务器主机，默认为localhost，推荐使用ip地址
    -pPASSWORD      //指定用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
    -V              //查看当前使用的mysql版本
    -e          //不登录mysql执行sql语句后退出，常用于脚本

服务器监听的两种socket地址

socket类型	说明
ip socket	默认监听在tcp的3306端口，支持远程通信
unix sock	监听在sock文件上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock）仅支持本地通信 server地址只能是：localhost，127.0.0.1

//查看当前使用的mysql版本
[root@localhost ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.34, for Linux (x86_64) using  EditLine wrapper

//指定服务器主机，默认为localhost，推荐使用ip地址
[root@localhost ~]# mysql -uroot -pP@ssw0rd??? -h127.0.0.1	
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 14
Server version: 5.7.34 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> quit
Bye
//注意，不推荐直接在命令行里直接用-pPASSWORD的方式登录，而是使用-p选项，然后交互式输入密码

[root@localhost ~]# mysql -uroot -p -h127.0.0.1
Enter password: 		//这样输密码就很安全 不会被别人看见
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.34 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>

//不登录mysql执行sql语句后退出，常用于脚本
[root@localhost ~]# mysql -uroot -p -h 127.0.0.1 -e 'SHOW DATABASES;'
Enter password: 		//这里输入密码
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+			
[root@localhost ~]#

DDL操作

数据库操作

//创建数据库
//语法：CREATE DATABASE [IF NOT EXISTS] 'DB_NAME';
//创建数据库yzy
mysql> CREATE DATABASE IF NOT EXISTS yzy;
Query OK, 1 row affected (0.00 sec)

//查看当前有哪些数据库
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yzy                |
+--------------------+
5 rows in set (0.00 sec)

//删除数据库
//语法：DROP DATABASE [IF EXISTS] 'DB_NAME';
//删除数据库yzy
mysql> DROP DATABASE IF EXISTS yzy;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

表操作

//创建表
//语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型';
//在数据库life里创建表happy
mysql> CREATE DATABASE life;			//创建数据库life
Query OK, 1 row affected (0.00 sec)

mysql> use life					//进入数据库life
Database changed
mysql> CREATE TABLE happy (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint);		//创建表happy
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW TABLES;			//查看当前数据库有哪些表
+----------------+
| Tables_in_life |
+----------------+
| happy          |
+----------------+
1 row in set (0.00 sec)

//删除表
//语法：DROP TABLE [ IF EXISTS ] 'table_name';
//删除表happy
mysql> DROP TABLE happy;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
Empty set (0.00 sec)