Linux-Mariadb数据库

最新推荐文章于 2022-03-26 22:12:25 发布
最新推荐文章于 2022-03-26 22:12:25 发布
阅读量197 收藏
点赞数 1
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44822212/article/details/89527715
版权

一.数据库的安装及初始化
Mariadb 数据库管理系统是Mysql的一个分支，主要由开源社区在维护。
数据库
1.数据库的安装
（1）yum源是否配置正常

[root@localhost ~]# yum repolist
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.
repo id                                                                 repo name                                                               status
!westos                                                                 rhel7.3                                                                 4,751
repolist: 4,751

（2）查找数据库

[root@localhost ~]# yum search mysql
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.
============================== N/S matched: mysql ==============================
MySQL-python.x86_64 : An interface to MySQL
akonadi-mysql.x86_64 : Akonadi MySQL backend support
dovecot-mysql.x86_64 : MySQL back end for dovecot
libdbi-dbd-mysql.x86_64 : MySQL plugin for libdbi
mysql-connector-java.noarch : Official JDBC driver for MySQL
mysql-connector-odbc.x86_64 : ODBC driver for MySQL
pcp-pmda-mysql.x86_64 : Performance Co-Pilot (PCP) metrics for MySQL
perl-DBD-MySQL.x86_64 : A MySQL interface for Perl
php-mysql.x86_64 : A module for PHP applications that use MySQL databases
qt-mysql.i686 : MySQL driver for Qt's SQL classes
qt-mysql.x86_64 : MySQL driver for Qt's SQL classes
qt3-MySQL.i686 : MySQL drivers for Qt 3's SQL classes
qt3-MySQL.x86_64 : MySQL drivers for Qt 3's SQL classes
qt5-qtbase-mysql.i686 : MySQL driver for Qt5's SQL classes
qt5-qtbase-mysql.x86_64 : MySQL driver for Qt5's SQL classes
rsyslog-mysql.x86_64 : MySQL support for rsyslog
mariadb.x86_64 : A community developed branch of MySQL  ##需要安装的数据库
mariadb-devel.i686 : Files for development of MariaDB/MySQL applications
mariadb-devel.x86_64 : Files for development of MariaDB/MySQL applications
mariadb-libs.i686 : The shared libraries required for MariaDB/MySQL clients
mariadb-libs.x86_64 : The shared libraries required for MariaDB/MySQL clients

  Name and summary matches only, use "search all" for everything.

（3）安装数据库

[root@localhost ~]# yum install mariadb-server.x86_64 -y
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.
westos                                                   | 4.1 kB     00:00     
Resolving Dependencies
--> Running transaction check
---> Package mariadb-server.x86_64 1:5.5.52-1.el7 will be installed
--> Processing Dependency: mariadb(x86-64) = 1:5.5.52-1.el7 for package: 1:mariadb-server-5.5.52-1.el7.x86_64
--> Processing Dependency: perl-DBD-MySQL for package: 1:mariadb-server-5.5.52-1.el7.x86_64
--> Running transaction check
---> Package mariadb.x86_64 1:5.5.52-1.el7 will be installed
---> Package perl-DBD-MySQL.x86_64 0:4.023-5.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package               Arch          Version                Repository     Size
================================================================================
Installing:
 mariadb-server        x86_64        1:5.5.52-1.el7         westos         11 M
Installing for dependencies:
 mariadb               x86_64        1:5.5.52-1.el7         westos        8.6 M
 perl-DBD-MySQL        x86_64        4.023-5.el7            westos        140 k

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

Total download size: 19 M
Installed size: 104 M
Downloading packages:
--------------------------------------------------------------------------------
Total                                               33 MB/s |  19 MB  00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : 1:mariadb-5.5.52-1.el7.x86_64                                1/3 
  Installing : perl-DBD-MySQL-4.023-5.el7.x86_64                            2/3 
  Installing : 1:mariadb-server-5.5.52-1.el7.x86_64                         3/3 
  Verifying  : 1:mariadb-server-5.5.52-1.el7.x86_64                         1/3 
  Verifying  : perl-DBD-MySQL-4.023-5.el7.x86_64                            2/3 
  Verifying  : 1:mariadb-5.5.52-1.el7.x86_64                                3/3 

Installed:
  mariadb-server.x86_64 1:5.5.52-1.el7                                          

Dependency Installed:
  mariadb.x86_64 1:5.5.52-1.el7       perl-DBD-MySQL.x86_64 0:4.023-5.el7      

Complete!

（4）设置数据库的状态为开启

[root@localhost ~]# systemctl start mariadb.service 
[root@localhost ~]# systemctl status mariadb.service 
● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
   Active: active (running) since Fri 2019-04-26 05:57:36 CST; 18s ago  ##状态开启
  Process: 3525 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
  Process: 3446 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
 Main PID: 3524 (mysqld_safe)
   CGroup: /system.slice/mariadb.service
           ├─3524 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
           └─3682 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql...

Apr 26 05:57:33 localhost.localdomain mariadb-prepare-db-dir[3446]: The lates...
Apr 26 05:57:33 localhost.localdomain mariadb-prepare-db-dir[3446]: You can f...
Apr 26 05:57:33 localhost.localdomain mariadb-prepare-db-dir[3446]: http://de...
Apr 26 05:57:33 localhost.localdomain mariadb-prepare-db-dir[3446]: Support M...
Apr 26 05:57:33 localhost.localdomain mariadb-prepare-db-dir[3446]: Corporati...
Apr 26 05:57:33 localhost.localdomain mariadb-prepare-db-dir[3446]: Alternati...
Apr 26 05:57:33 localhost.localdomain mariadb-prepare-db-dir[3446]: http://ma...
Apr 26 05:57:33 localhost.localdomain mysqld_safe[3524]: 190426 05:57:33 mysq...
Apr 26 05:57:33 localhost.localdomain mysqld_safe[3524]: 190426 05:57:33 mysq...
Apr 26 05:57:36 localhost.localdomain systemd[1]: Started MariaDB database se...
Hint: Some lines were ellipsized, use -l to show in full.

（5）检验数据库是否可用

[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.52-MariaDB MariaDB Server

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

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

MariaDB [(none)]>   ##可用

2.安全初始化
（1）设置登录密码

[root@localhost ~]# mysql_secure_installation  ##进入密码设置模式

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):   ##输入之前的密码，无密码不用输
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:  ##输入新密码为redhat
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] y  ##禁止匿名用户登录
 ... 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] y  ##禁止超级用户远程登录
 ... 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] y  ##删除默认数据库的文件
 - 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] y  ##立即重新加载权限列表
 ... 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!

（2）超级用户输入密码登录数据库

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 5.5.52-MariaDB MariaDB Server

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

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

MariaDB [(none)]> exit
Bye

（3）拒绝外部通过网络访问数据库
查看对外开放接口

[root@localhost ~]# netstat -antlupe | grep mysql
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      27         37075      3682/mysqld  ##对外开放接口为3306

修改配置文件跳过所有关于网络的设置

[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# systemctl restart mariadb.service 
[root@localhost ~]# netstat -antlupe | grep mysql  ##接口关闭

在这里插入图片描述
二.数据库的管理
1.数据库的查询

[root@localhost ~]# mysql -uroot -p  ##登录数据库
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.52-MariaDB MariaDB Server

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

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

（1）SHOW DATABASES ##查询库信息

MariaDB [(none)]> SHOW DATABASES;  ##查询库信息
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

（2）USE DATABASENAME ##进入某一库

MariaDB [(none)]> USE mysql  ##进入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]>

（3）SHOW TABLES; ##查询库中的所有表

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)

（4）SELECT * FROM TABLES ##查看某一表的所有信息

MariaDB [mysql]> SELECT * FROM user;  ##查看user表中的所有信息
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host      | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| 127.0.0.1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| ::1       | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
3 rows in set (0.00 sec)

**（5）SELECT Host FROM user WHERE User=‘root’; ## 按条件查询表中信息
**

MariaDB [mysql]> SELECT Host FROM user WHERE User='root';  ##查询用户名为root的表中的信息
+-----------+
| Host      |
+-----------+
| 127.0.0.1 |
| ::1       |
| localhost |
+-----------+
3 rows in set (0.00 sec)

2.数据库的建立

CREATE DATABASE westos;   ## 新建一个库名称为westos
USE westos; # 进入新建的库
CREATE TABLE linux ( #在库中创建一个新的表
-> username varchar(10) not null, #表中包含的字段以及字段的要求
-> password varchar(50) not null
-> );
DESC linux; # 查看表结构
INSERT INTO linux VALUES(‘lee’,‘123’); #给表中插入内容
INSERT INTO toto VALUES(‘tom’,‘123’);
SELECT * FROM linux;
SELECT username FROM linux; 查看表中某一字段的内容
[root@localhost ~]# mysql -uroot -p  ##登录数据库
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.52-MariaDB MariaDB Server

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

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

MariaDB [(none)]> CREATE DATABASE westos;  ##创建一个库westos
Query OK, 1 row affected (0.00 sec)

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

MariaDB [(none)]> USE westos  ##调用新建库westos
Database changed
MariaDB [westos]> CREATE TABLE linux (
    -> username varchar(10) not null,
    -> password varchar(50) not null
    -> );
Query OK, 0 rows affected (0.11 sec)

MariaDB [westos]> DESC linux  ##查看调用的表结构，是否与建立的一致
    -> ;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   |     | NULL    |       |
| password | varchar(50) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [westos]> INSERT INTO linux VALUES ('lee','123');  ##给表中插入内容lee 123
Query OK, 1 row affected (0.18 sec)

MariaDB [westos]> INSERT INTO linux VALUES ('tom','123');  ##给表中插入内容 tom 123
Query OK, 1 row affected (0.09 sec)

MariaDB [westos]> SELECT * FROM linux;  ##查看linux表中的内容与建立的是否一致
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
| tom      | 123      |
+----------+----------+
2 rows in set (0.00 sec)
MariaDB [westos]> SELECT username,password from linux;  ##查看linux表中关于username和password的内容
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
| tom      | 123      |
+----------+----------+
2 rows in set (0.00 sec)

3.数据库的更改
（1） ALTER TABLE linux RENAME redhat; ##更改数据表格的名字

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.52-MariaDB MariaDB Server

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

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

MariaDB [(none)]> USE westos;
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 [westos]> SHOW TABLES
    -> ;
+------------------+
| Tables_in_westos |
+------------------+
| linux            |
+------------------+
1 row in set (0.00 sec)

MariaDB [westos]> ALTER TABLE linux RENAME redhat;  ##更改数据表格的名字
Query OK, 0 rows affected (0.14 sec)

MariaDB [westos]> SHOW TABLES;
+------------------+
| Tables_in_westos |
+------------------+
| redhat           |
+------------------+
1 row in set (0.00 sec)

MariaDB [westos]> ALTER TABLE redhat RENAME linux;
Query OK, 0 rows affected (0.09 sec)

MariaDB [westos]> SHOW TABLES;
+------------------+
| Tables_in_westos |
+------------------+
| linux            |
+------------------+
1 row in set (0.00 sec)

MariaDB [westos]> SELECT * FROM linux;
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
| tom      | 123      |
+----------+----------+
2 rows in set (0.00 sec)

（2）ALTER TABLE linux ADD class varchar(20) not null;##给表中添加一个字段，默认在最后添加

MariaDB [westos]> ALTER TABLE linux ADD class varchar(20) not null;
Query OK, 2 rows affected (0.23 sec)               
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [westos]> SELECT * FROM linux;
+----------+----------+-------+
| username | password | class |
+----------+----------+-------+
| lee      | 123      |       |
| tom      | 123      |       |
+----------+----------+-------+
2 rows in set (0.00 sec)

（3）ALTER TABLE linux DROP class; ##移除表格中某一字段

MariaDB [westos]> ALTER TABLE linux DROP class;
Query OK, 2 rows affected (0.16 sec)               
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [westos]> SELECT * FROM linux;
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
| tom      | 123      |
+----------+----------+
2 rows in set (0.00 sec)

（4）ALTER TABLE linux ADD class varchar(20) not null AFTER ##在username后面添加一字段

MariaDB [westos]> ALTER TABLE linux ADD class varchar(20) not null AFTER username;
Query OK, 2 rows affected (0.16 sec)               
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [westos]> SELECT * FROM linux;
+----------+-------+----------+
| username | class | password |
+----------+-------+----------+
| lee      |       | 123      |
| tom      |       | 123      |
+----------+-------+----------+
2 rows in set (0.00 sec)

（5） UPDATE linux set password=‘456’ ##更改某一字段

MariaDB [westos]> UPDATE linux set password='456';
Query OK, 2 rows affected (0.37 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [westos]> SELECT * FROM linux;
+----------+-------+----------+
| username | class | password |
+----------+-------+----------+
| lee      |       | 456      |
| tom      |       | 456      |
+----------+-------+----------+
2 rows in set (0.00 sec)

MariaDB [westos]> UPDATE linux set password='123';
Query OK, 2 rows affected (0.09 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [westos]> SELECT * FROM linux;
+----------+-------+----------+
| username | class | password |
+----------+-------+----------+
| lee      |       | 123      |
| tom      |       | 123      |
+----------+-------+----------+
2 rows in set (0.00 sec)

（6）UPDATE linux set password=‘456’ WHERE username=‘lee’ ##更改某一具体字段的某一内容

MariaDB [westos]> UPDATE linux set password='456' WHERE username='lee';
Query OK, 1 row affected (0.43 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [westos]> SELECT * FROM linux;
+----------+-------+----------+
| username | class | password |
+----------+-------+----------+
| lee      |       | 456      |
| tom      |       | 123      |
+----------+-------+----------+
2 rows in set (0.00 sec)

4.数据库的删除
（1）DELETE FROM linux WHERE username=‘lee’; ##删除某一字段

MariaDB [westos]> DELETE FROM linux WHERE username='lee';
Query OK, 1 row affected (0.36 sec)

MariaDB [westos]> SELECT * FROM linux;
+----------+-------+----------+
| username | class | password |
+----------+-------+----------+
| tom      |       | 123      |
+----------+-------+----------+
1 row in set (0.00 sec)

（2） DROP TABLE linux; ##删除表格

MariaDB [westos]> DROP TABLE linux;
Query OK, 0 rows affected (0.13 sec)

MariaDB [westos]> SHOW TABLES;
Empty set (0.00 sec)

（3）DROP DATABASE westos; ##删除数据库

MariaDB [westos]> DROP DATABASE westos;
Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]> SHOW TABLES;
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

三.用户的授权与权力回收
1.用户的授权

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.52-MariaDB MariaDB Server

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
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]>CREATE USER lee@'localhost' indetified by 'westos';  ##给用户lee授权
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> SELECT User FROM user;  ##查看是否有新用户lee产生
+------+
| User |
+------+
| root |
| root |
| lee  |
| root |
+------+
4 rows in set (0.00 sec)
MariaDB [mysql]>GRANT SELECT ON westos.* TO lee@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> SHOW GRANTS FOR lee@localhost;  ##查看lee现在拥有的权力
+------------------------------------------------------------------------------------------------------------+
| Grants for lee@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'localhost' IDENTIFIED BY PASSWORD '*28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96' |
| GRANT SELECT ON `westos`.* TO 'lee'@'localhost'  仅选泽权                                                        |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
[root@localhost ~]# mysql -ulee -p  ##用户lee可以登录数据库
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, 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 |
| westos             |
+--------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> USE westos;
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 [westos]> INSERT INTO linux VALUES('westos','123');  ##在表格中插入字符不成功时没有授予其可写权力
ERROR 1142 (42000): INSERT command denied to user 'lee'@'localhost' for table 'linux'
MariaDB [mysql]> GRANT INSERT ON westos.* TO lee@localhost;  ##授予其可写权
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> SHOW GRANTS FOR lee@localhost;  ##查看权力
+------------------------------------------------------------------------------------------------------------+
| Grants for lee@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'localhost' IDENTIFIED BY PASSWORD '*28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96' |
| GRANT SELECT, INSERT ON `westos`.* TO  'lee'@'localhost'   ##选择，可写权力均有                                                  |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [mysql]> USE westos
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 [westos]> INSERT INTO linux VALUES('westos','123')  ##插入字符
    -> ;
Query OK, 1 row affected (0.45 sec)

MariaDB [westos]> SELECT * FROM westos;
ERROR 1146 (42S02): Table 'westos.westos' doesn't exist
MariaDB [westos]> SELECT * FROM linux;  ##插入字符成功
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
| tom      | 123      |
| westos   | 123      |
+----------+----------+
3 rows in set (0.00 sec)

2.权力的回收

MariaDB [mysql]> REVOKE INSERT ON westos.* FROM lee@localhost;  ##移除插入权力
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> SHOW GRANTS FOR lee@localhost;
+-------------------------------------------------+
| Grants for lee@localhost                        |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'localhost'         |
| GRANT SELECT ON `westos`.* TO 'lee'@'localhost' |  ##插入权力被回收
+-------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [mysql]> DROP USER lee@localhost;  ##删除用户
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> SELECT User FROM mysql.user;  ##lee用户被删除
+------+
| User |
+------+
| root |
| root |
| root |
+------+
3 rows in set (0.00 sec)
MariaDB [mysql]> FLUSH PRIVILEGES;  ##刷新
Query OK, 0 rows affected (0.00 sec)‘

四.用户数据库的备份
1.数据库进行备份

[root@localhost ~]# mysqldump -uroot -p westos >/mnt/westos.sql  ##将库westos备份至/mnt/westos.sql
Enter password: 
[root@localhost ~]# mysqldump -uroot -p --all-database --no-data  ##对所有库进行备份只备份结构不备份数据
Enter password: 
-- MySQL dump 10.14  Distrib 5.5.52-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version	5.5.52-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

2.对库进行删除

MariaDB [(none)]> DROP DATABASE westos;  ##删除库westos
Query OK, 1 row affected (0.13 sec)

MariaDB [(none)]> SHOW DATABASES;  ##此时库中无westos数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> exit
Bye

3.数据库的恢复
恢复方式1

[root@localhost ~]# mysql -uroot -p -e "CREATE DATABASE westos;"  ##在数据库中新建一个库westos
Enter password: 
[root@localhost ~]# mysql -uroot -p westos < /mnt/westos.sql  ##将备份的数据放入新建的库中
Enter password: 
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 5.5.52-MariaDB MariaDB Server

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

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

MariaDB [(none)]> SHOW DATABASES;  ##westos出现则备份成功
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| westos             |
+--------------------+
4 rows in set (0.00 sec)
Database changed
MariaDB [westos]> SHOW TABLES;  ##linux表格出现，备份成功
+------------------+
| Tables_in_westos |
+------------------+
| linux            |
+------------------+
1 row in set (0.00 sec

恢复方式2

[root@localhost ~]# vim /mnt/westos.sql
[root@localhost ~]# mysql -uroot -p westos < /mnt/westos.sql
Enter password:

在这里插入图片描述
四.修改超级用户密码
1.当超级用户知道密码需要更改

[root@localhost ~]# mysqladmin -uroot -predhat password lee  ##更改超级用户密码为lee
[root@localhost ~]# mysql -uroot -p  ##登录数据库
Enter password: 输入密码lee登录成功
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.52-MariaDB MariaDB Server

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

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

MariaDB [(none)]>

2.超级用户忘记密码需要更改

[root@shareserver ~]# systemctl stop mariadb.service ##关闭数据库服务
[root@localhost ~]# mysqld_safe --skip-grant-tables &  ##跳过验证列表并且进入后台运行登录
[1] 7304
[root@localhost ~]# 190429 20:55:55 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
190429 20:55:55 mysqld_safe A mysqld process already exists
^C
[1]+  Exit 1                  mysqld_safe --skip-grant-tables

[root@localhost ~]# mysql  ##进入数据库
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.52-MariaDB MariaDB Server

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> UPDATE mysql.user set Password=password('redhat') WHERE User='MariaDB [(none)]> UPDATE mysql.user set Password=password('redhat') WHERE User='root';  ##更改超级用户密码为redhat
Query OK, 1 row affected (0.00 sec)
Rows matched: 3  Changed: 1  Warnings: 0

MariaDB [(none)]> exit
Bye
[root@localhost ~]# ps aux | grep mysql  ##查看mysql的所有进程
root      7043  0.0  0.1 113252  1576 pts/0    S+   20:52   0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql     7198  0.0  8.4 906916 85624 pts/0    Sl+  20:52   0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root      7558  0.0  0.0 112648   960 pts/2    R+   21:02   0:00 grep --color=auto mysql
[root@localhost ~]# kill -9  7043  ##关闭没有停止的进程7043
[root@localhost ~]# kill -9  7198  ##关闭没有停止的进程 7198
[root@localhost ~]# kill -9  7558
-bash: kill: (7558) - No such process
[root@localhost ~]# systemctl restart mariadb.service  ##重启服务
[root@localhost ~]# mysql -uroot -p  ##以超级用户身份登录数据库
Enter password:   ##输入密码redhat,验证成功
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.52-MariaDB MariaDB Server

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

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

MariaDB [(none)]>
weixin_44822212
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
红帽linux安装FTP服务
weixin_47853811的博客
12-13 1205
红帽linux安装FTP服务 [root@localhost ~]# yum install -y vsftpd 已加载插件：langpacks, product-id, search-disabled-repos, subscription-manager This system is not registered with an entitlement server. You can use subscription-manager to register. There are no enabled
linux的mariadb数据库
qq_39592974的博客
08-14 408
一.数据库mariadb简介 二.安装与启动服务，检查端口状态 三.安装后使用前的准备 1.关闭数据库开启的网络接口 vim /etc/my.cnf skip-networking=1     关闭数据库开启的网络接口 2.数据库安全初始化  mysql_secure_installation 提问解释： En
Linux中mariadb数据库的安装与配置
Mencre的博客
09-22 507
1、通过yum安装mariadb 选择y 2、下载完成后启动数据库 systemctl start mariadb.service 启动服务 3、设置密码 mysql_secure_installation 键入当前密码，如果无回车 然后一直点y 4、登录 mysql -u root -p 登录 5、设置外网访问权限 mys
Linux上mariadb的安装与配置
菲宇运维
10-08 3万+
yum源安装MySQL和开启设置服务 　　首先，我们来看看使用yum源安装MySQL的优缺点： 　　优点：安装方便快捷，安装方式简单 　　缺点：太过于死板，定死了各个文件的位置 1、打开官方网站，上边有各种版本的yum源，找到自己想要的版本设置yum源 官网：https://downloads.mariadb.org/mariadb/repositories/ 2、如果我们使用官网...
yum加快安装速度的优化
zzy287dy的专栏
11-04 2094
一、简介 每次yum安装的时候，都可以发现yum使用了fast-mirrors，但这个过程可能很慢，为了加快速度，对这个过程进行了分析； [zl@zlbox ~]$ yum makecache 已加载插件：fastestmirror, langpacks, product-id, search-disabled-repos, subscription-manager Determining fastest mirrors #这里是在判断哪个是最快的源 * base: mirrors.a
MariaDB（mariadb-10.6.7-linux-systemd-x86_64.tar.gz）
03-27
MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB（英语：XtraDB）来...
MariaDB（mariadb-10.6.8-linux-systemd-x86_64.tar.gz）
06-07
MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB（英语：XtraDB）来...
redmine-mariadb-solution：具有Redmine和MariaDB数据库的服务器
02-05
总结来说，"redmine-mariadb-solution"提供了一个集成的环境，帮助用户轻松地在服务器上部署和运行Redmine项目管理软件，搭配MariaDB数据库，确保了高效、稳定的数据存储。通过了解上述知识点，你可以更好地管理和...
autoInstall_mariadb-5.5.68-linux-x86_64
11-15
**自动安装MariaDB 5.5.68在Linux x86_64平台上的详细步骤** MariaDB是一个开源的关系型数据库管理系统，它是MySQL的一个分支，由MySQL的创始人开发，旨在保持与MySQL的兼容性同时提供更先进的特性。在本教程中，...
mariadb-10.11.2-linux-systemd-x86-64.tar.gz
02-18
MariaDB是一个开源的关系型数据库管理系统，它源自于MySQL，并在MySQL的基础上进行了一些改进和扩展，以提供更先进的功能和更好的性能。MariaDB的名字是为了纪念MySQL创始人Monty Widenius的女儿Maria而命名的。这个...
软件管理拓展
chenfeifei12345的博客
06-22 581
1.搭建yum源安装软件 [root@localhost network-scripts]# gpk-application ##这是一个类似与软件商店的文件 ##我们先下载一个匹配系统版本的镜像，然后再使用secureCRT传输到我们的虚拟机里面 [root@localhost root]# mount /dev/cdrom /media/##我们把光盘镜像挂载到media上，我们要是用虚拟机本来没有挂载的话，可以使用mount 下载的镜像 要挂载的位置实现挂载比如[root@localhost Des
centos7安装初次mariadb启动错误解决思路
weixin_42507504的博客
08-10 3739
【注意】叙述的是第一次安装mariadb时候出现systemctl start mariadb时启动失败。 [root@db01 ~]# systemctl status mariadb ● mariadb.service - MariaDB database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled) Active: failed (Resu
Linux进行yum程序安装时遇到：This system is not registered with an entitlement server. You can use subscription
weixin_42692989的博客
03-20 1万+
#安装linux遇到： [root@localhost pluginconf.d]# yum install samba 已加载插件：langpacks, product-id, search-disabled-repos, subscription-manager This system is not registered with an entitlement server. You can ...
yum源常见问题及解决方案 ~
lilygg的博客
10-31 3万+
yum源常搭建常见问题及解决方案 如果虚拟机无法下载软件 可能的原因有： 1.真机yum源没有配置好 2.真机共享yum源的挂载错误 3.真机apache没有开启 4.真机火墙没有关闭（或者是真机火墙开着的但未添加http服务到火墙策略中） 5.虚拟机的yum源指向错误 6.所挂载的镜像本身就有问题 排错1： 我这里的真机曾经用过7.3版本的镜像资源安装过apache，而此时挂载的是7.2版本的...
编译安装redis6.2.6
weixin_43438161的博客
03-26 2254
编译安装redis6.2.6 参考文档 #redis6.0.5编译安装 https://segmentfault.com/a/1190000023048088 下载源码包 ##redis仓库 http://download.redis.io/releases/ ##官方链接 wget https://download.redis.io/releases/redis-6.2.6.tar.gz 编译 ###解压 tar -zxvf redis-6.2.6.tar.gz cd redis-6.2.6/ ###
Linux基础(2)-Rhel7基本配置
麦子地的专栏
01-12 4381
固定的可以联网IP地址 关闭防火墙 关闭selinux(强制访问控制安全模块) 光盘自动挂载 配置好本地yum源 固定的可以联网IP地址修改IP的三种方法Rhel6 中可以配置IP的方法有三种：ifconfig（临时）、配置文件（永久）、setup/nmtui（需图形化界面）。 Rhel7 中弱化了setup的功能，对于网络管理来说，network服务的功能弱化，主要通过NetworkManage
linux下使用yum时出现This system is not registered to Red Hat Subscription Management
cry_mumu的博客
03-25 1285
我在使用yum时遇到了这种情况 然后上百度搜了原因和解决方法 发现跟我的情况不一样 就字面上来说 这句话是使用redhat的yum源需要注册。 但我的情况是在/etc/yum.repos.d/目录下（我的repo文件命名为rhel_dvd.repo） 而文本里面方框的内容是[redhat 7] 所以当我输入yum repolist命令的时候 出现了一下报错 [root@localhost yum...
MySQL错误ERROR 1046 (3D000): No database selected解决办法
热门推荐
qq_43128354的博客
09-03 5万+
No database selected可以理解为没有选择种数据库 先在查看数据库种包括的database有哪些，选择自己要操作的数据 记住database后面一定加s 选择自己要操作那一个数据库(这样选择之后mysql>就和上面的不是同一个意思) 这样之后就可以操作数据库，所以每次进入cmd要操作数据库时，都要选择特定的数据库 ...
PXC(Percona-XtraDB-Cluster)5.7版本集群安装和使用初探
csd753111111的博客
10-25 1257
环境介绍： pxc三个节点: 192.168.1.6 test06 192.168.1.7 test07 192.168.1.8 test08 操作系统版本:C...
linux安装mariadb-server数据库
最新发布
08-23
要在Linux上安装MariaDB数据库服务器，您可以按照以下步骤进行操作： 1. 打开终端并使用以下命令安装MariaDB服务器： yum install mariadb-server -y 2. 安装完成后，使用以下命令启动MariaDB服务： systemctl start mariadb 3. 如果您希望MariaDB在系统启动时自动启动，可以使用以下命令启用自启动： systemctl enable mariadb 4. 在安装完成后，建议运行安全配置脚本以增加数据库的安全性。您可以使用以下命令运行安全配置脚本： mysql_secure_installation 这个脚本将引导您设置root密码、删除匿名用户、禁止远程root登录等安全设置。 5. 安装成功后，您就可以开始使用MariaDB数据库了。MariaDB是MySQL的一个分支，因此与MySQL相比，它具有更好的兼容性。您可以使用MySQL的API和命令行工具来管理和操作MariaDB数据库。 请注意，安装和配置数据库服务器需要具有管理员权限的用户。在执行命令时，请确保您有足够的权限。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* [LINUX---mariadb-server数据库服务](https://blog.csdn.net/qq_42736288/article/details/81587130)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 33.333333333333336%"] - *2* [Linux 上安装Mariadb-server](https://blog.csdn.net/Jerris_Gigl/article/details/124486553)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 33.333333333333336%"] - *3* [MariaDB（mariadb-10.6.8-linux-systemd-x86_64.tar.gz）](https://download.csdn.net/download/weixin_43800734/85576775)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 33.333333333333336%"] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值