在mysql安装后,清理无用的权限,使用如下语句:
delete from user where (user,host) not in (select 'root','localhost');
由于上述语句写错为:
delete from user where (user,host) not in (select 'root','local');导致root用户被删除。
补救措施:
1、由于没有root用户,无法使用mysqladmin关闭mysql,所以在操作系统层直接杀掉mysql进程:
[root@linfytest3 ~]# ps -ef|grep mysql
root 29356 1 0 15:55 pts/2 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql3307/my.cnf
mysql 30506 29356 8 17:00 pts/2 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/var/lib/mysql3307/my.cnf --basedir=/usr/local/mysql --datadir=/var/lib/mysql3307 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/lib/mysql3307/linfytest3.err --pid-file=/var/lib/mysql3307/linfytest3.pid --socket=/tmp/mysql3307.sock --port=3307
root 30531 29955 0 17:00 pts/3 00:00:00 grep mysql
[root@linfytest3 ~]# kill -9 29356
[root@linfytest3 ~]# ps -ef|grep mysql
mysql 30506 1 2 17:00 pts/2 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/var/lib/mysql3307/my.cnf --basedir=/usr/local/mysql --datadir=/var/lib/mysql3307 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/lib/mysql3307/linfytest3.err --pid-file=/var/lib/mysql3307/linfytest3.pid --socket=/tmp/mysql3307.sock --port=3307
root 30534 29955 0 17:00 pts/3 00:00:00 grep mysql
[root@linfytest3 ~]# kill -9 30506
[root@linfytest3 ~]# ps -ef|grep mysql
root 30555 29955 0 17:01 pts/3 00:00:00 grep mysql
注意,先杀主进程29356,再杀子进程30506,如果先杀子进程30506,那么mysql进程会自动重启。
2、使用参数--skip-grant-tables启动数据库,启动数据库时不进行权限验证
[root@linfytest3 ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql3307/my.cnf --skip-grant-tables &
[1] 30677
[root@linfytest3 ~]# 150928 17:04:09 mysqld_safe Logging to '/var/lib/mysql3307/linfytest3.err'.
150928 17:04:09 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql3307
3、不用root用户也可以登录mysql服务器了
[root@linfytest3 ~]# /usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock
4、使用SQL语句添加root用户
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.02 sec)
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> INSERT INTO user SET User='root',Host='localhost',ssl_cipher='',x509_issuer='',x509_subject='';
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE user SET 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='Y',authentication_string='' WHERE User='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,password from users;
ERROR 1146 (42S02): Table 'mysql.users' doesn't exist
mysql> select host,user,password from user;
+-----------+------+----------+
| host | user | password |
+-----------+------+----------+
| localhost | root | |
+-----------+------+----------+
1 row in set (0.00 sec)
5、修改root密码:
[root@linfytest3 ~]# mysqladmin -S /tmp/mysql3307.sock password 111111
Warning: Using a password on the command line interface can be insecure.
mysqladmin:
You cannot use 'password' command as mysqld runs
with grant tables disabled (was started with --skip-grant-tables).
Use: "mysqladmin flush-privileges password '*'" instead
提示:使用--skip-grant-tables参数启动数据库,不允许修改root密码
6、重启mysql
[root@linfytest3 ~]# /usr/local/mysql/bin/mysqladmin -uroot -S /tmp/mysql3307.sock shutdown
150928 17:14:39 mysqld_safe mysqld from pid file /var/lib/mysql3307/linfytest3.pid ended
[1]+ Done /usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql3307/my.cnf --skip-grant-tables
[root@linfytest3 ~]# ps -ef|grep mysql
root 30931 29955 0 17:14 pts/3 00:00:00 grep mysql
[root@linfytest3 ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql3307/my.cnf &
[1] 30934
[root@linfytest3 ~]# 150928 17:15:08 mysqld_safe Logging to '/var/lib/mysql3307/linfytest3.err'.
150928 17:15:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql3307
7、再次修改root密码:
[root@linfytest3 ~]# mysqladmin -S /tmp/mysql3307.sock password 111111
Warning: Using a password on the command line interface can be insecure.
修改成功!
修改密码的第二种方法:
使用SQL语句修改:
mysql> update user set password=password('111111') where user='root';
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)
delete from user where (user,host) not in (select 'root','localhost');
由于上述语句写错为:
delete from user where (user,host) not in (select 'root','local');导致root用户被删除。
补救措施:
1、由于没有root用户,无法使用mysqladmin关闭mysql,所以在操作系统层直接杀掉mysql进程:
[root@linfytest3 ~]# ps -ef|grep mysql
root 29356 1 0 15:55 pts/2 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql3307/my.cnf
mysql 30506 29356 8 17:00 pts/2 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/var/lib/mysql3307/my.cnf --basedir=/usr/local/mysql --datadir=/var/lib/mysql3307 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/lib/mysql3307/linfytest3.err --pid-file=/var/lib/mysql3307/linfytest3.pid --socket=/tmp/mysql3307.sock --port=3307
root 30531 29955 0 17:00 pts/3 00:00:00 grep mysql
[root@linfytest3 ~]# kill -9 29356
[root@linfytest3 ~]# ps -ef|grep mysql
mysql 30506 1 2 17:00 pts/2 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/var/lib/mysql3307/my.cnf --basedir=/usr/local/mysql --datadir=/var/lib/mysql3307 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/lib/mysql3307/linfytest3.err --pid-file=/var/lib/mysql3307/linfytest3.pid --socket=/tmp/mysql3307.sock --port=3307
root 30534 29955 0 17:00 pts/3 00:00:00 grep mysql
[root@linfytest3 ~]# kill -9 30506
[root@linfytest3 ~]# ps -ef|grep mysql
root 30555 29955 0 17:01 pts/3 00:00:00 grep mysql
注意,先杀主进程29356,再杀子进程30506,如果先杀子进程30506,那么mysql进程会自动重启。
2、使用参数--skip-grant-tables启动数据库,启动数据库时不进行权限验证
[root@linfytest3 ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql3307/my.cnf --skip-grant-tables &
[1] 30677
[root@linfytest3 ~]# 150928 17:04:09 mysqld_safe Logging to '/var/lib/mysql3307/linfytest3.err'.
150928 17:04:09 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql3307
3、不用root用户也可以登录mysql服务器了
[root@linfytest3 ~]# /usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock
4、使用SQL语句添加root用户
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.02 sec)
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> INSERT INTO user SET User='root',Host='localhost',ssl_cipher='',x509_issuer='',x509_subject='';
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE user SET 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='Y',authentication_string='' WHERE User='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,password from users;
ERROR 1146 (42S02): Table 'mysql.users' doesn't exist
mysql> select host,user,password from user;
+-----------+------+----------+
| host | user | password |
+-----------+------+----------+
| localhost | root | |
+-----------+------+----------+
1 row in set (0.00 sec)
5、修改root密码:
[root@linfytest3 ~]# mysqladmin -S /tmp/mysql3307.sock password 111111
Warning: Using a password on the command line interface can be insecure.
mysqladmin:
You cannot use 'password' command as mysqld runs
with grant tables disabled (was started with --skip-grant-tables).
Use: "mysqladmin flush-privileges password '*'" instead
提示:使用--skip-grant-tables参数启动数据库,不允许修改root密码
6、重启mysql
[root@linfytest3 ~]# /usr/local/mysql/bin/mysqladmin -uroot -S /tmp/mysql3307.sock shutdown
150928 17:14:39 mysqld_safe mysqld from pid file /var/lib/mysql3307/linfytest3.pid ended
[1]+ Done /usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql3307/my.cnf --skip-grant-tables
[root@linfytest3 ~]# ps -ef|grep mysql
root 30931 29955 0 17:14 pts/3 00:00:00 grep mysql
[root@linfytest3 ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql3307/my.cnf &
[1] 30934
[root@linfytest3 ~]# 150928 17:15:08 mysqld_safe Logging to '/var/lib/mysql3307/linfytest3.err'.
150928 17:15:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql3307
7、再次修改root密码:
[root@linfytest3 ~]# mysqladmin -S /tmp/mysql3307.sock password 111111
Warning: Using a password on the command line interface can be insecure.
修改成功!
修改密码的第二种方法:
使用SQL语句修改:
mysql> update user set password=password('111111') where user='root';
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/543979/viewspace-1811280/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/543979/viewspace-1811280/