linux下对mysql的初步管理

最新推荐文章于 2025-03-10 00:12:34 发布

PiaoBoYang

最新推荐文章于 2025-03-10 00:12:34 发布

阅读量269

点赞数

分类专栏：学习日志

本文链接：https://blog.youkuaiyun.com/a313434458/article/details/80515802

版权

学习日志专栏收录该内容

45 篇文章

订阅专栏

本文档介绍了在Linux系统中如何安装MySQL数据库，包括安装步骤、检查端口配置、安全设置及密码设定。接着详细阐述了数据库的基本操作，如显示数据库、选择使用特定数据库、查看表和数据、创建新数据库等。最后，讲解了数据库的修改方法，如更改数据库名称、数据表名，以及在数据表中添加和删除字段的操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、数据库的安装

1.安装

[root@foundation66 ~]# yum install mariadb-server -y  ##安装mariadb服务
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-
              : manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
rhel7.2                                                  | 4.1 kB     00:00     
Resolving Dependencies
--> Running transaction check
---> Package mariadb-server.x86_64 1:5.5.44-2.el7 will be installed
--> Processing Dependency: mariadb(x86-64) = 1:5.5.44-2.el7 for package: 1:mariadb-server-5.5.44-2.el7.x86_64
--> Processing Dependency: perl(DBI) for package: 1:mariadb-server-5.5.44-2.el7.x86_64
--> Processing Dependency: perl-DBD-MySQL for package: 1:mariadb-server-5.5.44-2.el7.x86_64
--> Processing Dependency: perl-DBI for package: 1:mariadb-server-5.5.44-2.el7.x86_64
--> Running transaction check
---> Package mariadb.x86_64 1:5.5.44-2.el7 will be installed
---> Package perl-DBD-MySQL.x86_64 0:4.023-5.el7 will be installed
---> Package perl-DBI.x86_64 0:1.627-4.el7 will be installed
--> Processing Dependency: perl(RPC::PlClient) >= 0.2000 for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(RPC::PlServer) >= 0.2001 for package: perl-DBI-1.627-4.el7.x86_64
--> Running transaction check
---> Package perl-PlRPC.noarch 0:0.2020-14.el7 will be installed
--> Processing Dependency: perl(Net::Daemon) >= 0.13 for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Net::Daemon::Log) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Net::Daemon::Test) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Running transaction check
---> Package perl-Net-Daemon.noarch 0:0.48-5.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package               Arch         Version                 Repository     Size
================================================================================
Installing:
 mariadb-server        x86_64       1:5.5.44-2.el7          rhel7.2        11 M
Installing for dependencies:
 mariadb               x86_64       1:5.5.44-2.el7          rhel7.2       9.0 M
 perl-DBD-MySQL        x86_64       4.023-5.el7             rhel7.2       140 k
 perl-DBI              x86_64       1.627-4.el7             rhel7.2       802 k
 perl-Net-Daemon       noarch       0.48-5.el7              rhel7.2        51 k
 perl-PlRPC            noarch       0.2020-14.el7           rhel7.2        36 k

Transaction Summary
================================================================================
Install  1 Package (+5 Dependent packages)

Total download size: 21 M
Installed size: 106 M
Downloading packages:
--------------------------------------------------------------------------------
Total                                               68 MB/s |  21 MB  00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : 1:mariadb-5.5.44-2.el7.x86_64                                1/6 
  Installing : perl-Net-Daemon-0.48-5.el7.noarch                            2/6 
  Installing : perl-PlRPC-0.2020-14.el7.noarch                              3/6 
  Installing : perl-DBI-1.627-4.el7.x86_64                                  4/6 
  Installing : perl-DBD-MySQL-4.023-5.el7.x86_64                            5/6 
  Installing : 1:mariadb-server-5.5.44-2.el7.x86_64                         6/6 
  Verifying  : perl-Net-Daemon-0.48-5.el7.noarch                            1/6 
  Verifying  : 1:mariadb-server-5.5.44-2.el7.x86_64                         2/6 
  Verifying  : perl-PlRPC-0.2020-14.el7.noarch                              3/6 
  Verifying  : 1:mariadb-5.5.44-2.el7.x86_64                                4/6 
  Verifying  : perl-DBD-MySQL-4.023-5.el7.x86_64                            5/6 
  Verifying  : perl-DBI-1.627-4.el7.x86_64                                  6/6 

Installed:
  mariadb-server.x86_64 1:5.5.44-2.el7                                          

Dependency Installed:
  mariadb.x86_64 1:5.5.44-2.el7         perl-DBD-MySQL.x86_64 0:4.023-5.el7    
  perl-DBI.x86_64 0:1.627-4.el7         perl-Net-Daemon.noarch 0:0.48-5.el7    
  perl-PlRPC.noarch 0:0.2020-14.el7    

Complete!
[root@foundation66 ~]# systemctl start mariadb  ##开启服务
[root@foundation66 ~]# systemctl stop firewalld   ##关闭火墙

2、检查数据库的端口信息并修改

[root@foundation66 ~]# vim /etc/my.cnf

skip-network 设置跳过网络

检查

[root@foundation66 ~]# systemctl restart mariadb
[root@foundation66 ~]# netstat -antlpe | grep mysql

[root@foundation66 ~]# ##端口信息消失

设置安全的登陆mysql

[root@foundation66 ~]# mysql_secure_installation 
/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB 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? [Y/n] 
 ... 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? [Y/n] 
 ... Success!

By default, MariaDB 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? [Y/n] 
 - 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? [Y/n] 
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

回车一次过后，设置密码

之后全都确认就可以

3、登陆

[root@foundation66 ~]# mysql -uroot -p  ##-u选择用户 -p密码 -p后可直接输入密码但不安全
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 5.5.44-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

二、数据库mysql的基本功能

显示数据库

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

选择使用mysql数据库

MariaDB [(none)]> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]>

查看数据库中的表

MariaDB [mysql]> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

查看表中的数据

MariaDB [mysql]> SELECT User,Host,Password FROM user;  ##选取目标显示
+------+-----------+-------------------------------------------+
| User | Host      | Password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root | 127.0.0.1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root | ::1       | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [mysql]> SELECT User,Host,Password FROM user where Host='localhost'; ##增加显示的条件
+------+-----------+-------------------------------------------+
| User | Host      | Password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+------+-----------+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [mysql]>

创建新的数据库和内容

MariaDB [mysql]> CREATE DATABASE westos  ##创建数据库westos
    -> ;
Query OK, 1 row affected (0.00 sec)

MariaDB [mysql]> SHOW DATABASES;  
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| westos             |           ##创建成功
+--------------------+
4 rows in set (0.00 sec)

MariaDB [mysql]> use westos;   ##进入数据库
Database changed
MariaDB [westos]> show tables   
    -> ;
Empty set (0.00 sec)

MariaDB [westos]> create table flash
    -> ;                                ##创建数据表
ERROR 1113 (42000): A table must have at least 1 column
MariaDB [westos]> create table flash(    ##在表中写入内容
    -> username varchar(6) not null,                  ##username的数据类型为 varchar 最多为6位
    -> password varchar(8) not null);                 ##password的数据同上               8位
Query OK, 0 rows affected (0.15 sec)

MariaDB [westos]> show tables;        ##创建成功
+------------------+
| Tables_in_westos |
+------------------+
| flash            |
+------------------+
1 row in set (0.00 sec)

MariaDB [westos]> DESC flash     显示表属性
    -> ;
+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| username | varchar(6) | NO   |     | NULL    |       |
| password | varchar(8) | NO   |     | NULL    |       |
+----------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

MariaDB [westos]>

写入数据

MariaDB [westos]> insert into flash values ('uuu','pppp');
Query OK, 1 row affected (1.73 sec)
MariaDB [westos]> select * from flash;
+----------+----------+
| username | password |
+----------+----------+
| uuu      | pppp     |
+----------+----------+
1 row in set (0.00 sec)

MariaDB [westos]>

三、数据库的修改

1.修改数据库的名称

MariaDB [westos]> exit    ##退出
Bye
[root@foundation66 ~]# cd /var/lib/m
machines/ misc/     mlocate/  mysql/    
[root@foundation66 ~]# cd /var/lib/mysql/  
[root@foundation66 mysql]# ls
aria_log.00000001  ibdata1      ib_logfile1  mysql.sock          westos
aria_log_control   ib_logfile0  mysql        performance_schema
[root@foundation66 mysql]# mv westos linux  ##改名字
[root@foundation66 mysql]# systemctl restart mariadb   ##重启服务
[root@foundation66 mysql]# mysql -uroot -predhat    ##重新登陆
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.44-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> SHOW DATABASES      ##查看
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux              |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>

也可以不重启，进入数据库之后，执行刷新命令flush privileges

2、修改数据表名

MariaDB [linux]> alter table flash rename message;
Query OK, 0 rows affected (0.02 sec)  
  
MariaDB [westos]> show tables;  
+------------------+  
| Tables_in_westos |  
+------------------+  
| message          |  
+------------------+  
1 row in set (0.00 sec)

3、在数据表中添加字段

    MariaDB [westos]> alter table message add age varchar(4);      
    Query OK, 1 row affected (0.16 sec)                  
    Records: 1  Duplicates: 0  Warnings: 0  
      
    MariaDB [westos]> select * from message;  
    +----------+----------+------+  
    | username | password | age  |  
    +----------+----------+------+  
    | zm       | redhat   | NULL |  
    +----------+----------+------+  
    1 row in set (0.00 sec)

4、删除

MariaDB [westos]> drop table message;             
Query OK, 0 rows affected (0.02 sec)  
  
MariaDB [westos]> show tables;  
Empty set (0.00 sec)
MariaDB [westos]> drop database westos;              
Query OK, 0 rows affected (0.00 sec)  
  
MariaDB [(none)]> show databases;  
+--------------------+  
| Database           |  
+--------------------+  
| information_schema |  
| mysql              |  
| performance_schema |  
+--------------------+  
3 rows in set (0.00 sec)