创建用户：mysql> create user root@'%' identified by 'mysqlpass123';Query OK, 0 rows affected (0.01 sec)
授权：mysql> grant all on *.* to 'root'@'%';Query OK, 0 rows affected (0.00 sec)
刷新权限：mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)
删除用户：mysql> drop user 'root'@'127.0.0.1';Query OK, 0 rows affected (0.00 sec)
mysql> delete from user where host='::1';Query OK, 1 row affected (0.01 sec)
grant all privileges on *.* to 'root'@'%' IDENTIFIED by '123456';grant all privileges on slt_tppaml.* to 'slt_tppaml_w'@'%' IDENTIFIED by 'master_w';grant Select on slt_tppaml.* to 'slt_tppaml_r'@'%' IDENTIFIED by 'readonly';flush privileges;
mysql设置密码及修改密码的方法
1.设置新密码:(用户初始没有密码)# mysqladmin -uroot password '123456'修改密码:# mysqladmin -uroot -p123456 password '888888'
2.用root 进入mysql后mysql>set password for 'user'@'localhost' =password('mypass');mysql>flush privileges;
例：将root@localhost帐号密码设置为mysql!123。mysql>set password for 'root'@'localhost'=password('mysql!123');
3.使用GRANT语句mysql>grant all on *.* to 'root'@'localhost' IDENTIFIED BY '你的密码' with grant option ;mysql>flush privileges;
4.进入mysql库修改user表mysql>use mysql;mysql>update user set password=password('你的密码') where user='root';mysql>flush privileges;
通过命令行执行密码修改：# /usr/local/mysql/bin/mysql -uroot -p'mysqlpass' -e "update mysql.user set password=password('12345678') where host='127.0.0.1';"
mysql 5.7.17 修改用户密码：-------------------------------------mysql> update mysql.user set authentication_string=password('666666') where user='wk';Query OK, 1 row affected, 1 warning (0.06 sec)Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)
MySQL用户管理
查看用户权限：mysql> show grants for root@localhost; 授权：mysql权限分为：全局级别、数据库级别、表级别、列级别，根据需要指定相应级别。
赋予root超级用户权利权限：mysql> grant all privileges on *.* to root@'%' identified by '111111'; 创建test全局权限并有授予别人的权限：mysql> grant all privileges on *.* to test@'%' identified by '222222' with grant option; 创建test用户并赋予cacti库所有权限：mysql> grant all privileges on cacti.* to test@'%' identified by '222222'; 创建test用户并赋予cacti库某些权限：mysql> grant select,delete,update,create,drop on cacti.* to test@'%' identified by '222222'; 创建test用户并赋予cacti库host表权限：mysql> grant all privileges on cacti.host to test@'%' identified by '222222'; 撤销用户权限：mysql> revoke all on *.* from test; REVOKE语句只是取消用户的权限，没有彻底删除用户，如果需要，可以用delete语句删除用户。mysql> delete from mysql.user where user='test';mysql> flush privileges; 多条件匹配删除：mysql> delete from mysql.user where user='test' and host='localhost';
转载于:https://www.cnblogs.com/miclesvic/p/6179604.html
