mysql管理用户
# mysql -uroot -pEnter password:
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection> Server version: 5.6.30-log Source distribution
Copyright (c) 2000, 2013, 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> select user,host,password from mysql.user;
+---------+------------+-------------------------------------------+
| user | host | password |
+---------+------------+-------------------------------------------+
| root | localhost| *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| root | zampdmp-02 | |
| root | 127.0.0.1| |
| root | ::1 | |
| | localhost| |
| | zampdmp-02 | |
| hivedb| localhost| *1F4A2E1E0471EC60934CC4896B8B03BC64E80B58 |
| hivedb| % | *1F4A2E1E0471EC60934CC4896B8B03BC64E80B58 |
| root | % | *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| etler | % | *8C377BBE4E13B4FA9AB2727937F3FEF047DB0AAF |
| exposer | % | *1B5955820D31F633B073D0A326801133A15DE967 |
+---------+------------+-------------------------------------------+
11 rows in set (0.00 sec)
mysql> select user,host,password from mysql.user where password='';
+------+------------+----------+
| user | host | password |
+------+------------+----------+
| root | zampdmp-02 | |
| root | 127.0.0.1| |
| root | ::1 | |
| | localhost| |
| | zampdmp-02 | |
+------+------------+----------+
5 rows in set (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'zampdmp-02'IDENTIFIED BY PASSWORD '*225AF6FA78393CB66A2CF30F85001B643546ED44' WITH GRANT OPTION
-> ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1'IDENTIFIED BY PASSWORD '*225AF6FA78393CB66A2CF30F85001B643546ED44' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user where password='';
+------+------------+----------+
| user | host | password |
+------+------------+----------+
| root | ::1 | |
| | localhost| |
| | zampdmp-02 | |
+------+------------+----------+
3 rows in set (0.00 sec)
mysql> select user,host,password from mysql.user;
+---------+------------+-------------------------------------------+
| user | host | password |
+---------+------------+-------------------------------------------+
| root | localhost| *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| root | zampdmp-02 | *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| root | 127.0.0.1| *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| root | ::1 | |
| | localhost| |
| | zampdmp-02 | |
| hivedb| localhost| *1F4A2E1E0471EC60934CC4896B8B03BC64E80B58 |
| hivedb| % | *1F4A2E1E0471EC60934CC4896B8B03BC64E80B58 |
| root | % | *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| etler | % | *8C377BBE4E13B4FA9AB2727937F3FEF047DB0AAF |
| exposer | % | *1B5955820D31F633B073D0A326801133A15DE967 |
+---------+------------+-------------------------------------------+
11 rows in set (0.00 sec)
mysql> select user,host,password from mysql.user where password = '';
+------+------------+----------+
| user | host | password |
+------+------------+----------+
| root | ::1 | |
| | localhost| |
| | zampdmp-02 | |
+------+------------+----------+
3 rows in set (0.00 sec)
mysql> delete from mysql.user where password='';
Query OK, 3 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user where password = '';
Empty set (0.00 sec)
mysql> select user,host,password from mysql.user;
+---------+------------+-------------------------------------------+
| user | host | password |
+---------+------------+-------------------------------------------+
| root | localhost| *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| root | zampdmp-02 | *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| root | 127.0.0.1| *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| hivedb| localhost| *1F4A2E1E0471EC60934CC4896B8B03BC64E80B58 |
| hivedb| % | *1F4A2E1E0471EC60934CC4896B8B03BC64E80B58 |
| root | % | *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| etler | % | *8C377BBE4E13B4FA9AB2727937F3FEF047DB0AAF |
| exposer | % | *1B5955820D31F633B073D0A326801133A15DE967 |
+---------+------------+-------------------------------------------+
8 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to 'hivedb'@'zampdmp-02'> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'options' at line 1
mysql> grant all privileges on *.* to 'hivedb'@'zampdmp-02'> Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select user,host,password from mysql.user;
+---------+------------+-------------------------------------------+
| user | host | password |
+---------+------------+-------------------------------------------+
| root | localhost| *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| root | zampdmp-02 | *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| root | 127.0.0.1| *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| hivedb| zampdmp-02 | *9AE5587FACBA2EE66D40CE18BAC51361FD134B42 |
| hivedb| localhost| *1F4A2E1E0471EC60934CC4896B8B03BC64E80B58 |
| hivedb| % | *1F4A2E1E0471EC60934CC4896B8B03BC64E80B58 |
| root | % | *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| etler | % | *8C377BBE4E13B4FA9AB2727937F3FEF047DB0AAF |
| exposer | % | *1B5955820D31F633B073D0A326801133A15DE967 |
+---------+------------+-------------------------------------------+
9 rows in set (0.00 sec)
mysql> grant all privileges on *.* to 'hivedb'@'127.0.0.1'> Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user;
+---------+------------+-------------------------------------------+
| user | host | password |
+---------+------------+-------------------------------------------+
| root | localhost| *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| root | zampdmp-02 | *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| root | 127.0.0.1| *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| hivedb| 127.0.0.1| *9AE5587FACBA2EE66D40CE18BAC51361FD134B42 |
| hivedb| zampdmp-02 | *9AE5587FACBA2EE66D40CE18BAC51361FD134B42 |
| hivedb| localhost| *1F4A2E1E0471EC60934CC4896B8B03BC64E80B58 |
| hivedb| % | *1F4A2E1E0471EC60934CC4896B8B03BC64E80B58 |
| root | % | *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| etler | % | *8C377BBE4E13B4FA9AB2727937F3FEF047DB0AAF |
| exposer | % | *1B5955820D31F633B073D0A326801133A15DE967 |
+---------+------------+-------------------------------------------+
10 rows in set (0.00 sec)
mysql> delete from mysql.user where user=root and host=zampdmp-02;
ERROR 1054 (42S22): Unknown column 'root' in 'where clause'
mysql> delete from mysql.user where user='root' and host='zampdmp-02';
Query OK, 1 row affected (0.00 sec)
mysql> delete from mysql.user where user='hivedb' and host='zampdmp-02';
Query OK, 1 row affected (0.00 sec)
mysql> select user,host,password from mysql.user;
+---------+-----------+-------------------------------------------+
| user | host | password |
+---------+-----------+-------------------------------------------+
| root | localhost | *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| root | 127.0.0.1 | *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| hivedb| 127.0.0.1 | *9AE5587FACBA2EE66D40CE18BAC51361FD134B42 |
| hivedb| localhost | *1F4A2E1E0471EC60934CC4896B8B03BC64E80B58 |
| hivedb| % | *1F4A2E1E0471EC60934CC4896B8B03BC64E80B58 |
| root | % | *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| etler | % | *8C377BBE4E13B4FA9AB2727937F3FEF047DB0AAF |
| exposer | % | *1B5955820D31F633B073D0A326801133A15DE967 |
+---------+-----------+-------------------------------------------+
8 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
# mysql -uroot -p -hzampdmp-02
Enter password:
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection> Server version: 5.6.30-log Source distribution
Copyright (c) 2000, 2013, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| david |
| elita |
| exp |
| hivedb |
| mysql |
| olap |
| performance_schema |
| pub |
| test |
| tmp |
+--------------------+
11 rows in set (0.00 sec)
mysql> use tmp
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> use exp
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> show tables;
+--------------------+
| Tables_in_exp |
+--------------------+
| export_segment_day |
| segment_status |
| segment_total |
| segment_trait |
+--------------------+
4 rows in set (0.00 sec)
注意：允许localhost登录，则主机名和主机ip都可以登录，但是127.0.0.1不能登录，需要单独添加。
mysql> select user,host,password from mysql.user;
+------+-------------+-------------------------------------------+
| user | host | password |
+------+-------------+-------------------------------------------+
| root | localhost | *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| root | 172.22.64.% | *225AF6FA78393CB66A2CF30F85001B643546ED44 |
+------+-------------+-------------------------------------------+
2 rows in set (0.00 sec)
# mysql -uroot -p -hlocalhost
Enter password:
# mysql -uroot -p -hzampdmp-01
Enter password:
# mysql -uroot -p -h172.22.64.01
Enter password:
# mysql -uroot -p -h127.0.0.1
Enter password:
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server
mysql> grant all privileges on *.* to 'root'@'127.0.0.1'> Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user;
+------+-------------+-------------------------------------------+
| user | host | password |
+------+-------------+-------------------------------------------+
| root | localhost | *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| root | 127.0.0.1 | *225AF6FA78393CB66A2CF30F85001B643546ED44 |
| root | 172.22.64.% | *225AF6FA78393CB66A2CF30F85001B643546ED44 |
+------+-------------+-------------------------------------------+
3 rows in set (0.00 sec)
# mysql -uroot -p -h127.0.0.1 #登录成功
Enter password:
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection> Server version: 5.6.30-log Source distribution
Copyright (c) 2000, 2013, 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>
页:
[1]