ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Linux服务器---使用mysql

Linux服务器---使用mysql

原创 Linux操作系统 作者:一生有你llx 时间:2018-12-01 16:47:33 0 删除 编辑

使用mysql

1 、登录,可以用密码登录,也可以不用密码登录。命令格式“mysql –u 用户名 –p 密码”

[root@localhost src]#  mysql -u root      // 有密码登录

Enter password: 

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

Your MySQL connection id is 3

[root@localhost src]#  mysql -u root          // 无密码登录

 

2 、退出,命令“quit” 

[root@localhost bin]#  quit

 

3 、创建数据库,命令“create database 数据库名称;”,注意这个命令后面有分号

mysql>  create database test1;

Query OK, 1 row affected (0.00 sec)

 

4 、查看数据库,命令“show databases;”

mysql>  show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| test               |

| test1              |

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

4 rows in set (0.00 sec)

 

5 、删除数据库,命令“drop database 数据库名称;”

mysql>  drop database test1;

Query OK, 0 rows affected (0.01 sec)

 

6 、设置权限 

mysql 允许给某个特定的用户赋予指定的权利,而且可以指定在某台机器上使用。Mysql的权限如下

权限

数据库

Table

Column

说明

all privileges



所有权利

alter


增减、删除、修改列

create


创建数据库、表

delete


删除行

drop


删除表、数据库

file



操作文件

index


索引

insert

插入

process



查看线程、连接

reference



创建外键

reload



重新加载,拥有此权限可以刷新表

select

选择

shutdown



关闭

update

更新

usage



无权限,只能连接

 

1 )授权用户权限,命令格式“grant 权限on 数据库文件to 用户名@ip identified by ‘密码’;”。在使用grant的时候,如果用户不存在,那么久创建用户。

// david 在本机授权插入功能,密码 123456 ,只能对 test01 操作

mysql>  grant insert on test01.* to david@localhost identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> 

// david 所有权限,在所有的主机都可以操作,而且可以操作任意数据库

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

Query OK, 0 rows affected (0.00 sec)

mysql>

2 )查看当前数据库所有授权情况,命令“select host,user from mysql.user” 

mysql>  select host,user from mysql.user;

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

| host                  | user  |

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

| %                     | david |

| 127.0.0.1             | root  |

| localhost             |       |

| localhost             | david |

| localhost             | root  |

| localhost.localdomain |       |

| localhost.localdomain | root  |

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

7 rows in set (0.00 sec)

mysql> 

3 )查看当前登录用户的权利,命令“show grants” 

mysql>  show grants;

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

| Grants for root@localhost                                                                                                             |

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

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*1256939B1977AFF6C3D114C5594EE354EF363A8B' WITH GRANT OPTION |

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

1 row in set (0.00 sec)

mysql> 

4 )查看某个用户在某台机器的权限,命令“show grants for user@ip” 

mysql>  show grants for david@localhost;

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

| Grants for david@localhost                                                                                  |

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

| GRANT USAGE ON *.* TO 'david'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

| GRANT INSERT ON `test01`.* TO 'david'@'localhost'                                                            |

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

2 rows in set (0.00 sec)

mysql> 

5 )删除用户的权限,命令“revoke 权限on  数据库文件  from  user@ip”

mysql>  revoke all privileges on *.* from david@'%'; 

Query OK, 0 rows affected (0.00 sec)

 

mysql>  show grants for david@localhost;        // 删除之后查看一下

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

| Grants for david@localhost                                                                                  |

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

| GRANT USAGE ON *.* TO 'david'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

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

2 rows in set (0.00 sec)

mysql>         

6 )删除用户,命令“delete from user where user=‘username’”

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>  delete from user where user='david';    // 删除用户

Query OK, 2 rows affected (0.00 sec)

 

mysql>  select host,user from mysql.user;     // 查看用户

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

| host                  | user |

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

| 127.0.0.1             | root |

| localhost             |      |

| localhost             | root |

| localhost.localdomain |      |

| localhost.localdomain | root |

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

5 rows in set (0.00 sec)

mysql> 


做了一个Linux学习的平台,目前出来一个雏形,各位可以参考使用
链接:   密码:n7bk



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29270124/viewspace-2222357/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
多年物联网讲师经验,曾任职于达内、华清等公司。擅长Linux、c、java、Android、单片机、ZigBee、BLE等领域

注册时间:2018-10-21

  • 博文量
    256
  • 访问量
    258707