當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql 删除多余帐号_安装完mysql数据库后的优化（删除多余用户和数据库）

發布時間：2024/8/1 数据库 30 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql 删除多余帐号_安装完mysql数据库后的优化（删除多余用户和数据库）小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

安裝完mysql數據庫后的優化(刪除多余用戶和數據庫)

發布時間：2020-06-27 19:09:35

來源：51CTO

閱讀：2761

作者：冰凍vs西瓜

欄目：數據庫

1、查看數據庫的版本信息：MariaDB?[(none)]>?select?version();

+----------------+

|?version()??????|

+----------------+

|?5.5.52-MariaDB?|

+----------------+

1?row?in?set?(0.18?sec)

2、刪除多余的賬號(除root和localhost的)MariaDB?[(none)]>?use?mysql

MariaDB?[mysql]>?select?user,host?from?mysql.user;

+------+------------------------+

|?user?|?host???????????????????|

+------+------------------------+

|?root?|?127.0.0.1??????????????|

|?root?|?::1????????????????????|

|??????|?localhost??????????????|

|?root?|?localhost??????????????|

|??????|?lvs-dr01.saltstack.com?|

|?root?|?lvs-dr01.saltstack.com?|

+------+------------------------+

6?rows?in?set?(0.03?sec)

MariaDB?[mysql]>?delete?from?mysql.user?where?(user,host)?not?in?(select?'root','localhost');

Query?OK,?5?rows?affected?(0.05?sec)

MariaDB?[mysql]>?select?user,host?from?mysql.user;

+------+-----------+

|?user?|?host??????|

+------+-----------+

|?root?|?localhost?|

+------+-----------+

1?row?in?set?(0.05?sec)

3、修改mysql默認的mysql管理賬號

修改默認的mysql管理賬號(root改為mysql，并設置新密碼為redhat12345)MariaDB?[mysql]>?update?user?set?user="admin"?where?user="root";

MariaDB?[mysql]>?update?mysql.user?set?user='admin',password=password('redhat12345');

Query?OK,?1?row?affected?(0.08?sec)

Rows?matched:?1??Changed:?1??Warnings:?0

MariaDB?[mysql]>?flush?privileges;

Query?OK,?0?rows?affected?(0.03?sec)

[root@LVS-DR01?~]#?mysql?-uadmin?-p'redhat12345'

MariaDB?[(none)]>?user?mysql；

MariaDB?[mysql]>?show?tables;

繼續查詢：MariaDB?[mysql]>?select?user,host?from?mysql.user;

+-------+-----------+

|?user??|?host??????|

+-------+-----------+

|?admin?|?localhost?|

+-------+-----------+

1?row?in?set?(0.00?sec)

4、刪除test數據庫：MariaDB?[mysql]>?drop?database?test;

MariaDB?[mysql]>?flush?privileges;

MariaDB?[(none)]>?show?databases;

+--------------------+

|?Database???????????|

+--------------------+

|?information_schema?|

|?mysql??????????????|

|?performance_schema?|

+--------------------+

3?rows?in?set?(0.06?sec)

5、優化權限字典表mysql.db

如下所示：新建MySQL數據庫后，默認創建的test數據庫權限比較怪異，所有可連接的用戶都能夠擁有權限訪問該庫，并操作其中的對象，Host為%，User為空，說明了不受限制，所有能連接到MySQL的用戶，全部擁有test及test開頭的數據庫的幾乎所有權限。

MariaDB?[mysql]>?select?*?from?mysql.db?where?db?like?'test%'?\G

***************************?1.?row?***************************

Host:?%

Db:?test

User:

Select_priv:?Y

Insert_priv:?Y

Update_priv:?Y

Delete_priv:?Y

Create_priv:?Y

Drop_priv:?Y

Grant_priv:?N

References_priv:?Y

Index_priv:?Y

Alter_priv:?Y

Create_tmp_table_priv:?Y

Lock_tables_priv:?Y

Create_view_priv:?Y

Show_view_priv:?Y

Create_routine_priv:?Y

Alter_routine_priv:?N

Execute_priv:?N

Event_priv:?Y

Trigger_priv:?Y

***************************?2.?row?***************************

Host:?%

Db:?test\_%

User:

Select_priv:?Y

Insert_priv:?Y

Update_priv:?Y

Delete_priv:?Y

Create_priv:?Y

Drop_priv:?Y

Grant_priv:?N

References_priv:?Y

Index_priv:?Y

Alter_priv:?Y

Create_tmp_table_priv:?Y

Lock_tables_priv:?Y

Create_view_priv:?Y

Show_view_priv:?Y

Create_routine_priv:?Y

Alter_routine_priv:?N

Execute_priv:?N

Event_priv:?Y

Trigger_priv:?Y

2?rows?in?set?(0.31?sec)

優化的操作：

MariaDB?[mysql]>?truncate?table?mysql.db;

Query?OK,?0?rows?affected?(0.01?sec)

MariaDB?[mysql]>?select?*?from?mysql.db?where?db?like?'test%'?\G

Empty?set?(0.00?sec)

6、如何優化/root/.mysql_history文件[root@LVS-DR01?~]#?tail?-20?~/.mysql_history

flush?privileges;