如何解决误删除root用户

1
2
3
4
5
-- MySQL用户信息存储在以下系统表中
mysql.user     # 用户基本信息和权限
mysql.db       # 数据库级别权限
mysql.tables_priv    # 表级别权限
mysql.columns_priv   # 列级别权限

1
2
3
4
5
-- 这些SQL命令可能导致root用户问题
DELETE FROM mysql.user WHERE User='root';                    -- 删除root用户
UPDATE mysql.user SET authentication_string='' WHERE User='root';  -- 清空root密码
DROP DATABASE mysql;                                          -- 删除系统数据库
REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'localhost';        -- 撤销所有权限

1
2
3
4
5
-- 检查当前用户
SELECT USER();

-- 检查现有用户
SELECT User, Host FROM mysql.user;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 插入localhost的root用户记录
INSERT INTO user (Host, User, 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, 
                 password_expired, account_locked, Create_role_priv, Drop_role_priv)
VALUES ('localhost', 'root', '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, 'mysql_native_password', 'N', 'N', 'Y', 'Y');

1
2
3
-- 设置root用户密码为123456
UPDATE user SET authentication_string = CONCAT('*', UPPER(SHA1(UNHEX(SHA1('123456'))))) 
WHERE User = 'root' AND Host = 'localhost';

1
2
-- 刷新权限表
FLUSH PRIVILEGES;

1
2
3
4
5
6
7
8
9
10
-- 检查当前用户
SELECT USER();

-- 检查root用户权限
SHOW GRANTS FOR 'root'@'localhost';

-- 测试创建数据库
CREATE DATABASE test_recovery;
SHOW DATABASES;
DROP DATABASE test_recovery;

1
2
3
4
5
-- 查看所有root用户
SELECT User, Host, authentication_string FROM mysql.user WHERE User='root';

-- 确认localhost的root用户存在
SELECT User, Host FROM mysql.user WHERE User='root' AND Host='localhost';

1
2
3
-- 如果密码设置失败，可以使用ALTER USER命令
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

1
2
3
4
5
6
7
-- 检查用户状态
SELECT User, Host, authentication_string, account_locked 
FROM mysql.user WHERE User='root';

-- 如果账户被锁定，解锁账户
UPDATE mysql.user SET account_locked='N' WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES;

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建用户备份脚本
-- backup_users.sql

-- 备份用户表结构
SELECT CONCAT('CREATE TABLE mysql_user_backup_', DATE_FORMAT(NOW(), '%Y%m%d_%H%i%s'), ' LIKE mysql.user;') AS backup_command;

-- 备份用户数据
SELECT CONCAT('INSERT INTO mysql_user_backup_', DATE_FORMAT(NOW(), '%Y%m%d_%H%i%s'), ' SELECT * FROM mysql.user;') AS backup_command;

-- 导出用户权限
SELECT CONCAT('SHOW GRANTS FOR ''', User, '''@''', Host, ''';') AS grant_command
FROM mysql.user WHERE User != '';

1
2
3
4
5
6
7
-- 创建备用管理员账户
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'securepassword123';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- 验证账户创建
SELECT User, Host FROM mysql.user WHERE User='admin';

1
2
3
4
5
6
7
8
9
10
-- 设置密码策略
SET GLOBAL validate_password.policy=MEDIUM;
SET GLOBAL validate_password.length=8;
SET GLOBAL validate_password.mixed_case_count=1;
SET GLOBAL validate_password.number_count=1;
SET GLOBAL validate_password.special_char_count=1;

-- 修改root密码为强密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MySecurePass123!';
FLUSH PRIVILEGES;

1
2
3
4
5
6
-- 限制root用户只能从localhost访问
DELETE FROM mysql.user WHERE User='root' AND Host='%';

-- 或者限制特定IP访问
UPDATE mysql.user SET Host='192.168.1.100' WHERE User='root' AND Host='%';
FLUSH PRIVILEGES;

1
2
3
4
5
6
7
8
9
10
11
-- 启用查询日志
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = 'F:/MySQL_Data/Data/mysql.log';

-- 查看当前连接
SHOW PROCESSLIST;

-- 查看用户登录历史
SELECT User, Host, db, Command, Time, State 
FROM information_schema.PROCESSLIST 
WHERE User IS NOT NULL;

1
2
3
4
5
6
7
8
9
10
11
12
-- 检查用户状态
SELECT User, Host, authentication_string, account_locked, password_expired
FROM mysql.user 
WHERE User IN ('root', 'admin');

-- 检查权限
SHOW GRANTS FOR 'root'@'localhost';
SHOW GRANTS FOR 'admin'@'localhost';

-- 清理过期用户
DELETE FROM mysql.user WHERE User='' OR Host='';
FLUSH PRIVILEGES;

1
2
3
4
5
6
7
8
9
10
-- 检查用户表是否存在
SHOW TABLES FROM mysql LIKE 'user';

-- 检查root用户状态
SELECT User, Host, authentication_string, account_locked, password_expired
FROM mysql.user WHERE User='root';

-- 检查用户权限
SHOW GRANTS FOR 'root'@'localhost';
SHOW GRANTS FOR 'root'@'%';

1
2
3
4
5
6
7
8
-- 查看当前连接
SHOW PROCESSLIST;

-- 查看连接错误
SELECT * FROM performance_schema.host_cache;

-- 检查用户认证插件
SELECT User, Host, plugin FROM mysql.user WHERE User='root';

1
2
3
4
5
6
7
8
9
10
11
-- 查看系统变量
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'wait_timeout';

-- 查看状态信息
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Uptime';

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';