安装完mysql数据库后的优化(删除多余用户和数据库)
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; select user,host from mysql.user; delete from mysql.user where user="'molewan1'@'10.10.10.%'"; delete from mysql.db where user='molewan1'@'10.10.10.%'; select user,host from mysql.user; delete from mysql.user where user=molewan1; delete from mysql.user where user='molewan1@10.10.10.%'; flush privileges; select user,host from mysql.user; delete from mysql.user where user="molewan1" and host ="10.10.10.%"; flush privileges; select user,host from mysql.user; delete from mysql.user where user="molewan" and host="10.10.10.%"; flush privileges; create user molewan@'10.10.10.%' identified by 'molewan'; select user,host from mysql.user; desc mysql.user; update mysql.user set password=password('admin') where user='admin' and host='localhost'; flush privileges; \q
说明:在Linux/Unix系统下,使用mysql命令行工具执行的所有操作,都会被记录到一个名为.mysql_history的文件中,该文件默认保存在当前用户的根目录下
这个设定原本是为了提升mysql命令行操作体验,在mysql中操作命令就可以上下翻动了,但某些情况下缺会造成隐患。
如何消除隐患:
方法1:基于DB层的操作 修改MYSQL_HISTFILE环境变量,将其值改为/dev/null,这样所有的操作都会被输出到空,操作的历史 自然不会被保留。 方法2:基于系统层操作 仍旧保留这个文件,但是改文件实际上未/dev/null的软链接,这样所有的操作都会被输出到空,操作的历史自然不会被保留。 ln -f -s /dev/null ~/.mysql_history [root@LVS-DR01 ~]# tail -20 ~/.mysql_history 这时候就没有输出了
到此,安装完成后的基本优化已经完成
文章题目:安装完mysql数据库后的优化(删除多余用户和数据库)
文章起源:http://myzitong.com/article/ihodhs.html