測試環境
删除前
mysql> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | jinch | |
| root | 127.0.0.1 | |
| root | ::1 | |
| test | 10.0.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
删除root使用者
mysql> drop user root@'localhost';
為了測試，把其他root相關都删除了，指令就不一一展示了，顯示結果。
删除後
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| test | localhost |
+------+-----------+
1 row in set (0.00 sec)
退出再登陸
[[email protected] ~]# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
現在已經無法登陸了，之前誤删過一次。。。無奈那會兒不知道如何解決，就隻能重裝了，但是，經過一段時間的學習，發現并不是沒有解決辦法的。
解決方法
首先，關閉資料庫
service mysqld stop
用安全模式設定開啟免密登陸
mysqld_safe --skip-grant-tables --skip-networking &
--skip-grant-tables 關閉連接配接層的驗證子產品
--skip-networking 關閉遠端連接配接資料庫(隻允許本地登陸，可設定防火牆達到相同效果)
[[email protected] ~]# mysqld_safe --skip-grant-tables
181010 16:57:03 mysqld_safe Logging to '/usr/local/mysql/data/jinch.err'.
181010 16:57:04 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
然後使用mysql指令就可以直接進入資料庫
進入之後，使用insert 指令重新插入表
mysql> insert into user(user,host,password,ssl_type,ssl_cipher,x509_issuer,x509_subject) values('root','localhost',PASSWORD('123'),'','','','');
PASSWORD大寫的原因：在mysql中密碼都是密文的，是以這裡是調用一個函數，将明文加密成密文。
然後退出重新開機一下資料庫就可以重新使用賬号密碼登入了
mysql> quit
Bye
[[email protected] ~]# /etc/init.d/mysqld restart
[[email protected] ~]# mysql -uroot -p123
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> --這邊就放一部分啦，
誤删root使用者無法登陸的解決就到這裡啦
注意 如果成功後通路出現1142沒有權限問題:
mysql> select user,password from mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'root'@'localhost' for table 'user'
看了一下報錯資訊，權限不夠。。。那就是沒有權限了
退出資料庫并且關閉mysql服務
mysql> quit
Bye
安全模式啟動mysql，root使用者登入
[[email protected] ~]# mysqld_safe --skip-grant-tables &
[[email protected] ~]# mysql -uroot -p123 mysql
切換資料庫&檢視表資訊中的root使用者的localhost權限
mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)
mysql> select * from user where user='root' and host='localhost'\G;
*************************** 1. row ***************************
Host: localhost
User: root
Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: NULL
password_expired: N
1 row in set (0.00 sec)
ERROR:
No query specified
這裡發現全部都是N ，表示root使用者本地登陸沒有權限
修改root使用者的localhost權限
01.
update mysql.user set Grant_priv='Y',Super_priv='Y' where user='root';
flush privileges;
grant all on *.* to 'root'@'localhost';
下面這寫行不用輸入,上面指令的實質就是下面 的操作
mysql> update user set `Insert_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Update_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Delete_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Create_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Drop_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Reload_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Shutdown_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Process_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `File_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Grant_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `References_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Index_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Alter_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Show_db_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Super_priv` ='Y',`Create_tmp_table_priv` = 'Y' where user='root'' and host='localhost';
mysql> update user set `Lock_tables_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Execute_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Repl_slave_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Repl_client_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Create_view_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Show_view_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Create_routine_priv` ='Y' where user='root' and host='localhost'';
mysql> update user set `Alter_routine_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Create_user_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Event_priv` ='Y' where user='root' and host='localhost';
mysql> update user set `Trigger_priv` ='Y' where user='root' and host='localhost';
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
退出&重新開機&登陸
mysql> quit
Bye
[[email protected] ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
[[email protected] ~]# mysql -uroot -p123
切換庫
mysql> 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
##### 檢視表資訊
mysql> select * from user\G;
*************************** 1. row ***************************
Host: localhost
User: root
Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: NULL
password_expired: N
1 row in set (0.01 sec)
ERROR:
No query specified
權限已經基本都有了
測試一下
mysql> create database jinc;
Query OK, 1 row affected (0.00 sec)
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | localhost |
+------+-----------+
1 row in set (0.00 sec)
mysql> drop database jinc;
Query OK, 0 rows affected (0.00 sec)