mysql安全篇

在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、直接登录