MySQL 关于用户操作详解

一、添加用户：

①登陆mysql root用户：

　@>mysql -u root -p

　@>密码

②创建用户：

　mysql> insert into mysql.user(Host,User,Password) values("localhost","test",password("test1234"));

   mysql> create user 'username'@'%' identified by 'password';

   这样就创建了一个名为：test 密码为：test1234 的用户。

   注意：此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录。如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录，也可以指定某台机器可以远程登录。

③然后登录一下：

　mysql>exit;

　@>mysql -u test -p

　@>test1234

　mysql>登录成功

二、为用户授权

授权格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";

①登录MYSQL（有ROOT权限），这里以ROOT身份登录：

　@>mysql -u root -p
　@>密码

②首先为用户创建一个数据库(testDB)：

　mysql>create database testDB;

③授权test用户拥有testDB数据库的所有权限（某个数据库的所有权限）：

　mysql>grant all privileges on testDB.* to test@localhost identified by 'test1234';
　mysql>flush privileges; //刷新系统权限表

格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";

④如果想指定部分权限给一用户，可以这样来写:

　mysql>grant select,update on testDB.* to xu@localhost identified by '1234';
　mysql>flush privileges; //刷新系统权限表

⑤授权test用户拥有所有数据库的某些权限：

　mysql>grant select,delete,update,create,drop on *.* to xu@"%" identified by "test1234";

　//test用户对所有数据库都有select,delete,update,create,drop 权限。

　//@"%" 表示对所有非本地主机授权，不包括localhost。（localhost地址设为127.0.0.1，如果设为真实的本地地址，不知道是否可以，没有验证。）

　//对localhost授权：加上一句grant all privileges on testDB.* to test@localhost identified by 'test1234';即可。

三、远程登录授权

Mysql为了安全性，在默认情况下用户只允许在本地登录，可是在有此情况下，还是需要使用用户进行远程连接，因此为了使其可以远程需要进行如下操作：

3.1、允许root用户在任何地方进行远程登录，并具有所有库任何操作权限，具体操作如下：

   在本机先使用root用户登录mysql： mysql -u root -p"youpassword" 进行授权操作：

   mysql>GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'youpassword' WITH GRANT OPTION;

   mysql>UPDATE  mysql.`user` SET Host = '%' WHERE User = 'root' AND Host = 'localhost';

   重载授权表：FLUSH PRIVILEGES;

   退出mysql数据库：exit;

3.2、允许root用户在一个特定的IP进行远程登录，并具有所有库任何操作权限，具体操作如下：

   在本机先使用root用户登录：mysql： mysql -u root -p"youpassword"

   进行授权操作：

   GRANT ALL PRIVILEGES ON *.* TO root@'117.128.60.21' IDENTIFIED BY "youpassword" WITH GRANT OPTION;

   重载授权表： FLUSH PRIVILEGES;

   退出mysql数据库： exit;

3.3、允许root用户在一个特定的IP进行远程登录，并具有所有库特定操作权限，具体操作如下：

   在本机先使用root用户登录：mysql： mysql -u root -p"youpassword"

   进行授权操作：

   GRANT select，insert，update，delete ON *.* TO root@'117.128.60.21' IDENTIFIED BY "youpassword";

   重载授权表： FLUSH PRIVILEGES;

   退出mysql数据库： exit;

3.4、删除用户授权，需要使用REVOKE命令，具体命令格式为：

   REVOKE privileges ON 数据库[.表名] FROM user-name;

   具体实例，先在本机登录：mysql: mysql -u root -p"youpassword"

   进行授权操作：

   GRANT select，insert，update，delete ON TEST-DB TO test@'117.128.60.21' IDENTIFIED BY "youpassword";

   再进行删除授权操作：

   REVOKE all on TEST-DB from test;

   注：该操作只是清除了用户对于TEST-DB的相关授权权限，但是这个“test-user”这个用户还是存在。

   最后从用户表内清除用户：

   DELETE FROM user WHERE user="test";

   重载授权表： FLUSH PRIVILEGES;

   退出mysql数据库： exit;

3.5、MYSQL权限详细分类：

   全局管理权限：

     FILE: 在MySQL服务器上读写文件。

     PROCESS: 显示或杀死属于其它用户的服务线程。

     RELOAD: 重载访问控制表，刷新日志等。

     SHUTDOWN: 关闭MySQL服务。

   数据库/数据表/数据列权限：

     ALTER: 修改已存在的数据表(例如增加/删除列)和索引。

     CREATE: 建立新的数据库或数据表。

     DELETE: 删除表的记录。

     DROP: 删除数据表或数据库。

     INDEX: 建立或删除索引。

     INSERT: 增加表的记录。

     SELECT: 显示/搜索表的记录。

     UPDATE: 修改表中已存在的记录。

   特别的权限：

     ALL: 允许做任何事(和root一样)。

     USAGE: 只允许登录--其它什么也不允许做。

四、删除用户：

mysql>Delete FROM user Where User='test' and Host='localhost';

mysql>flush privileges;

mysql>drop database testDB; //删除用户的数据库

删除账户及权限：>drop user 用户名@'%';

　　　　　　　　>drop user 用户名@ localhost; 

五、修改指定用户密码：

方法1： 用SET PASSWORD命令
　mysql -u root
　mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('新密码');

方法2：用mysqladmin
　mysqladmin -u root password "新密码"
　如果root已经设置过密码，采用如下方法
　mysqladmin -u root password oldpass "新密码"

方法3： 用UPDATE直接编辑user表
　mysql -u root
　mysql> use mysql;
　mysql> UPDATE user SET Password = PASSWORD('新密码') WHERE user = 'root';
　mysql> FLUSH PRIVILEGES;

MySQL忘记root密码，而且不能使用操作系统认证直接进入数据库修改密码时，需要重置root密码：

报错：ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: YES)

Windows：
　1. 关闭正在运行的MySQL服务。 
    2. CMD 打开 DOS 窗口，转到mysql\bin目录。 
    3. 输入mysqld --skip-grant-tables 回车，--skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证（windows下修改的是my.ini） 找到 mysqld 添加 skip-grant-tables 一行）。

    4. 再开一个 CMD DOS窗口（因为刚才那个DOS窗口已经不能动了）。
    5. 通过“服务”（或者 mysqld restart），重新启动数据库。
    6. 转到mysql\bin目录，在打开的CMD DOS窗口输入mysql回车，如果成功，将出现MySQL提示符 >。 
    7. 连接权限数据库： use mysql; 。 
    8. 改密码：update user set password=password("123") where user="root";（别忘了最后加分号） 。 
    9. 刷新权限（必须步骤）：flush privileges; 。 
    10. 退出 quit，再使用用户名root和刚才设置的新密码123登录。

Linux：

    1、在配置文件 /etc/my.cnf（注：windows下修改的是my.ini） 添加skip-grant-tables一行，跳过密码验证。
    2、重启mysql数据库主进程 # /etc/init.d/mysqld restart（也可以直接先停掉MySQL进程后使用 skip-grant-tables 参数重启MySQL）
    3、登录数据库修改密码。

    mysql> update user set authentication_string=password('') where user='root' and host='localhost'; 
      Query OK, 1 row affected (0.00 sec)
      Rows matched: 1  Changed: 1  Warnings: 0

    mysql> flush privileges;
     Query OK, 0 rows affected (0.00 sec)

    mysql> 
    mysql> exit
    这里需要修改的字段是authentication_string，这点和之前的版本不同。

    4、这个时候，如果你设置的密码太简单，则在数据库执行任何命令都会报类似如下错误：
    mysql> show databases;
      ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
    mysql> show database;
      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 'database' at line 1
    mysql> update user set authentication_string=password('mysql') where user='root' and host='localhost'; 
      ERROR 1046 (3D000): No database selected
    mysql> use mysql;
      ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

    5、注意：如果只想设置简单密码需要修改两个全局参数：
    mysql> set global validate_password_policy=0;
    mysql> set global validate_password_length=1; 
    mysql> set global validate_password_policy=0;
        Query OK, 0 rows affected (0.00 sec)
    mysql> set global validate_password_length=1; 
        Query OK, 0 rows affected (0.00 sec)
    mysql> set password=password("mysql");
       Query OK, 0 rows affected, 1 warning (0.00 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

六、开启 general_log 跟踪数据执行过程

# 设置general log保存路径，需要root用户才有访问此文件的权限

注意：在Linux中只能设置到 /tmp 或 /var 文件夹下，设置其他路径出错

   mysql> set global general_log_file='/tmp/general.log'; 

# 开启general log模式

   mysql> set global general_log=on; 

# 关闭general log模式

   mysql>set global general_log=off; 

在general log模式开启过程中，所有对数据库的操作都将被记录 general.log 文件