在mysql安装的时候默认有一个root用户,该用户是管理员用户,因此可以查看和操作所有的数据库实例,这样对于数据库服务器而言是个灾难的事情。
添加用户:
grant select,insert,update,delete,create,drop,on day01.* to 'huoniao'@'localhost' identified by 'huoniao' flush privileges;
grant select,insert,update,delete,create,drop,on day01.* to 'huoniao'@'%' identified by 'huoniao'
%代表创建的该用户可以被局域网使用。
实例:
mysql> grant select,insert,update,delete,create,drop on day01.* to 'huoniao'@'localhost' identified by 'huoniao';
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
C:\Users\Administrator>mysql -u huoniao -p
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, 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 |
| day01 |
+--------------------+
2 rows in set (0.00 sec)
mysql>
查看用户:
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 |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
mysql> select * from user;
+-----------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host | User | Password | 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 | authentication_string |
+-----------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | 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 | | |
| localhost | huoniao | *95787E1FB8F0CC3A73789D5F401212633777B679 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | | NULL |
| % | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | 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 | N | N | N | | | | | 0 | 0 | 0 | 0 | | NULL |
+-----------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
3 rows in set (0.00 sec)
发现mysql只要一安装就会内置一个mysql数据库,该数据库中维护了一个user表,该表中维护的所有的用户信息,那么以后如果用户需要修改密码,可以直接使用修改语句。
mysql> select user,password from user;
+---------+-------------------------------------------+
| user | password |
+---------+-------------------------------------------+
| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| huoniao | *95787E1FB8F0CC3A73789D5F401212633777B679 |
| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+---------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> update user set password=password('cainiao') where user='huoniao';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> quit
mysql> flush privilege;
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 'privilege' at line 1
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
mysql> quit
一定要记得plush,不然的话。要重启数据库实例才行。
如果忘记了mysql管理员的密码。那么可以重装mysql,但是对于专业人士不需要重装。
1、停止服务
2、创建一个文本文件 e:\\mysql-init.txt
update mysql.user set password=password('mysql') where user='root';
flush privileges;
3、在运行中输入如下指令
mysqld --defaults-file="C:\\Program Files\\MySQL\\MySQL Server 5.5\\my.ini" --init-file=E:\\mysql-init.txt
4、直接登录