MySQL更改root用户名称

 

MySQL更改root用户名称

 

出于安全问题，修改root用户名称，并限制网络访问。

参考MOS：Change the Name of the Root User Account (文档 ID 1023913.1)

方法1

SELECT host, user
FROM mysql.user
WHERE user = 'root';

(root@localhost 16:26:10) [(none)]> SELECT host, user FROM mysql.user WHERE user = 'root';
+-----------+------+
| host      | user |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.00 sec)

(root@localhost 16:26:44) [(none)]> SHOW GRANTS FOR 'root'@'localhost' \G
*************************** 1. row ***************************
Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@localhost: GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION
3 rows in set (0.00 sec)

 

--root管理员变更为admin，限制只能使用ip为1.124主机使用，并且只能远程访问

(root@localhost 16:34:16) [(none)]> create user 'admin'@'192.168.1.124' identified by 'admin';
Query OK, 0 rows affected (0.10 sec)

(root@localhost 16:34:29) [(none)]> grant all on *.* to 'admin'@'192.168.1.124' with grant option;
Query OK, 0 rows affected (0.52 sec)

(root@localhost 16:34:41) [(none)]> grant proxy on ``@`` TO 'admin'@'192.168.1.124' with grant option;
Query OK, 0 rows affected (0.14 sec)

 

(root@localhost 16:36:57) [(none)]> drop user 'root'@'localhost';   # 4.1 and 5.0 only

Query OK, 0 rows affected (0.06 sec)

# DELETE FROM mysql.user WHERE user = 'root'; # 4.0 only

(root@localhost 16:37:10) [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.03 sec)

 

 

 

 

方法2

SELECT host, user
FROM mysql.user
WHERE user = 'root';

SHOW GRANTS FOR 'root'@'localhost';

UPDATE mysql.user
SET name = 'admin'
WHERE user = 'root';
FLUSH PRIVILEGES;

 

 

PS：作为Oracle出身的DBA，以前刚接触MySQL的时候，查资料发现网上对于用户的修改（比如修改密码）有一种方法是update系统表mysql.user，就觉得挺奇葩的。

随意修改系统表不怕出问题？现在OracleMOS的文档都这么提供方法了看来没啥问题。