linux mysql 用户管理_mysql基础-用户管理（转）

安装完MYSQL后,启动MYSQL服务器用:net start mysql或service mysqld start

停止MYSQL服务器用:net stop mysql或service mysqld stop

创建用户,

mysql> insert into mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_sub

ject) values("localhost","fred",password("love"),'','','');

创建后的用户名为：fred 密码为：love

#创建用户就原理很简单,就是在mysql数据库user表里面加入一个用户名

创建数据库(test)

mysql>create database test;

将test数据库的所有权限授权给用户fred

>grant all privileges on test.* to fred@localhost identified by 'love';

#将all privileges可以改为相应的其它权限,还可以可test.*里面的部分表给用户即可.

刷新系统权限表

mysql>flush privileges;

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

mysql>grant select,update on phplampDB.* to fred@localhost identified by 'love';

刷新系统权限表。

mysql>flush privileges;

修改指定用户密码。

>mysql -u root -p

>密码

mysql>update mysql.user set password=password('新密码') where User="fred" and Host="localhost";

mysql>flush privileges;

#本人在进入MYSQL.USER表中看到HOST和USER都属于关键字,所以由两者来确定用户身份.

删除用户。

>mysql -u root -p

>密码

mysql>DELETE FROM user WHERE User="fred" and Host="localhost";

mysql>flush privileges;

删除用户的数据库

mysql>drop database test;

其它一些有用的操作：

列出所有数据库

mysql>show database;

切换数据库

mysql>use '数据库名';

列出所有表

mysql>show tables;

显示数据表结构

mysql>describe 表名;

删除数据库和数据表

mysql>drop database 数据库名;

mysql>drop table 数据表名;

-------------------

[root@redhat5 ~]# service mysqld start

Starting MySQL:                                            [  OK  ]

[root@redhat5 ~]# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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> update user set password=password("zzvcomcharge") where user="root";

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3  Changed: 3  Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

[root@redhat5 ~]# mysql

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@redhat5 ~]# mysql -uroot -pzzvcomcharge mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant all privileges on manage.* to manage@'%' identified by 'manage';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on manage.* to manage@'localhost' identified by 'manage';

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

[root@redhat5 ~]# mysql -umanage -pmanage manage

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| manage             |

| test               |

+--------------------+

3 rows in set (0.00 sec)