使用skip-grant-tables选项启动数据库时,
不能执行 grant语句,可以对mysql.user表进行,update,delete,insert操作:
[
mysql@localhost ~]$ mysqld_safe --skip-grant-tables
[ mysql@localhost ~]$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.23-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
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 user,host from user;
+-----------+-----------------------+
| user | host |
+-----------+-----------------------+
| root | % |
| song | % |
| song_test | % |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
| root | localhost.localdomain |
+-----------+-----------------------+
7 rows in set (0.00 sec)
mysql> grant all on *.* to 'test'@'%' identified by 'test';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> insert into user (user,host) values ('test','%');
ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value
mysql> UPDATE user SET Password=PASSWORD('123456') where USER='root';
Query OK, 3 rows affected (0.03 sec)
Rows matched: 5 Changed: 3 Warnings: 0
mysql> delete from user where user='root' and host='::1';
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO user VALUES('%','test',PASSWORD('test'),'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','','','Y');
Query OK, 1 row affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+-----------+-----------------------+
| user | host |
+-----------+-----------------------+
| root | % |
| song | % |
| song_test | % |
| test | % |
| root | 127.0.0.1 |
| root | localhost |
| root | localhost.localdomain |
+-----------+-----------------------+
7 rows in set (0.00 sec)
mysql> exit
Bye
[ mysql@localhost ~]$ mysqladmin shutdown -u root
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
[ mysql@localhost ~]$ mysqladmin shutdown
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'mysql'@'localhost' (using password: NO)'
[ mysql@localhost ~]$ mysqladmin shutdown -u root -p
Enter password:
[ mysql@localhost ~]$
[ mysql@localhost ~]$ service mysql start
Starting MySQL. SUCCESS!
[ mysql@localhost ~]$
[ mysql@localhost ~]$ mysql -u test -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.23-enterprise-commercial-advanced-log
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> desc mysql.user;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> SET PASSWORD=PASSWORD('123456');
Query OK, 0 rows affected (0.00 sec)
[ mysql@localhost ~]$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.23-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
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 user,host from user;
+-----------+-----------------------+
| user | host |
+-----------+-----------------------+
| root | % |
| song | % |
| song_test | % |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
| root | localhost.localdomain |
+-----------+-----------------------+
7 rows in set (0.00 sec)
mysql> grant all on *.* to 'test'@'%' identified by 'test';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> insert into user (user,host) values ('test','%');
ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value
mysql> UPDATE user SET Password=PASSWORD('123456') where USER='root';
Query OK, 3 rows affected (0.03 sec)
Rows matched: 5 Changed: 3 Warnings: 0
mysql> delete from user where user='root' and host='::1';
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO user VALUES('%','test',PASSWORD('test'),'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','','','Y');
Query OK, 1 row affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+-----------+-----------------------+
| user | host |
+-----------+-----------------------+
| root | % |
| song | % |
| song_test | % |
| test | % |
| root | 127.0.0.1 |
| root | localhost |
| root | localhost.localdomain |
+-----------+-----------------------+
7 rows in set (0.00 sec)
mysql> exit
Bye
[ mysql@localhost ~]$ mysqladmin shutdown -u root
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
[ mysql@localhost ~]$ mysqladmin shutdown
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'mysql'@'localhost' (using password: NO)'
[ mysql@localhost ~]$ mysqladmin shutdown -u root -p
Enter password:
[ mysql@localhost ~]$
[ mysql@localhost ~]$ service mysql start
Starting MySQL. SUCCESS!
[ mysql@localhost ~]$
[ mysql@localhost ~]$ mysql -u test -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.23-enterprise-commercial-advanced-log
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> desc mysql.user;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> SET PASSWORD=PASSWORD('123456');
Query OK, 0 rows affected (0.00 sec)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25099483/viewspace-1481800/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25099483/viewspace-1481800/