MySQL用户创建和授权

╰半夏微凉° 2022-03-21 13:42 303阅读 0赞

### 环境 ###

MySQL 5.1 + 命令行工具

### 问题 ###

MySQL用户创建和授权

### 解决 ###

1.以root用户登录创建新用户

C:\Users\Wentasy>mysql -uroot -p
    
    mysql> CREATE USER music IDENTIFIED BY 'music';

2.以root用户登录创建数据库

create database music default charset utf8;
    
    create table tb_user
    (
    	tb_user_id int(11) primary key auto_increment,
    	tb_user_account varchar(32) not null,
    	tb_user_password varchar(16) not null,
    	tb_user_nick varchar(16) not null,
    	tb_user_name varchar(32) not null,
    	tb_user_gender bit(1),
    	tb_user_phone varchar(16) ,
    	tb_user_email varchar(40),
    	check(tb_user_gender in('0', '1'))
    );
    
    create table tb_playlist
    (
    	tb_playlist_id int(11) primary key auto_increment,
    	tb_user_id int(11) not null,
    	tb_playlist_title varchar(255) not null,
    	constraint FK_TB_USER_ID foreign key(tb_user_id) references tb_user(tb_user_id)
    );
    
    create table tb_records
    (
    	tb_records_id int(11) primary key auto_increment,
    	tb_playlist_id int(11) not null,
    	tb_records_name varchar(40) not null,
    	constraint FK_TB_PLAYLIST_ID foreign key(tb_playlist_id) references tb_playlist(tb_playlist_id)
    );

3.以root用户登录为用户授权

mysql> GRANT ALL PRIVILEGES ON music.* TO music@localhost IDENTIFIED BY 'music';

4.以新创建的用户登录查看数据库

C:\Users\Wentasy>mysql -umusic -p
    
    C:\Users\Wentasy>mysql -umusic -p
    Enter password: *****
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 6
    Server version: 5.1.43-community MySQL Community Server (GPL)
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | music              |
    +--------------------+
    2 rows in set (0.00 sec)
    
    mysql> use music;
    Database changed
    mysql> show tables;
    +-----------------+
    | Tables_in_music |
    +-----------------+
    | tb_playlist     |
    | tb_records      |
    | tb_user         |
    +-----------------+
    3 rows in set (0.01 sec)
    
    mysql>

## ##

### 参考资料 ###

[http://qinlong.blog.51cto.com/1130504/591669][http_qinlong.blog.51cto.com_1130504_591669]  
[http://seawavecau.iteye.com/blog/282345][http_seawavecau.iteye.com_blog_282345]

<table style="border-bottom:#48c5db 5px double; border-left:#48c5db 5px double; padding-bottom:5px; background-color:black; padding-left:5px; border-spacing:0; width:100%; padding-right:30px; height:90px; border-top:#48c5db 5px double; border-right:#48c5db 5px double; padding-top:5px"> 
 <tbody> 
  <tr> 
   <td><img alt="katoon" src="https://img-my.csdn.net/uploads/201301/06/1357456262_5409.jpg" style="height:80px"></td> 
   <td style="text-align:right"><a title="新浪微博求粉 ^_^" href="http://weibo.com/wentasy" rel="nofollow"><img title="新浪微博求粉 ^_^" alt="Sina" src="https://img-my.csdn.net/uploads/201210/26/1351182273_5476.png.thumb.jpg" style="height:40px"></a> &nbsp;<a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow"><img title="我的CSDN博客——HelloWorld" alt="CSDN" src="https://img-my.csdn.net/uploads/201210/26/1351182268_8254.png.thumb.jpg" style="height:40px"></a></td> 
  </tr> 
  <tr> 
   <td style="text-align:right; font-family:microsoft yahei; color:white; font-size:12px"> <a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow">@Wentasy</a> 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [CSDN博客]</td> 
  </tr> 
 </tbody> 
</table>

[http_qinlong.blog.51cto.com_1130504_591669]: http://qinlong.blog.51cto.com/1130504/591669
[http_seawavecau.iteye.com_blog_282345]: http://seawavecau.iteye.com/blog/282345