MySQL用戶創建和授權 環境 www.2cto.com MySQL 5.1 + 命令行工具 問題 MySQL用戶創建和授權 解決 1.以root用戶登錄創建新用戶 [sql] C:\Users\Wentasy>mysql -uroot -p mysql> CREATE USER music IDENTIFIED BY 'music'; 2.以root用戶登錄創建數據庫 [sql] 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用戶登錄為用戶授權 [sql] mysql> GRANT ALL PRIVILEGES ON music.* TO music@localhost IDENTIFIED BY 'music'; 4.以新創建的用戶登錄查看數據庫 [sql] 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>
