创建用户:
mysql> create user 'username'@'ipaddress' identified by 'password';
#注:如果ipaddress为'%',代表任何地址都可以用新建的用户名连接
mysql> create user 'myroot'@'123.123.12.123' identified by 'mypass';
mysql> create user 'myroot'@'%' identified by 'mypass';
mysql> create user 'myroot'@'%' identified by '';
mysql> create user 'myroot'@'%';
创建之后,查询用户权限:
mysql> use mysql;
mysql> show grants for myroot@123.123.12.123;
+------------------------------------------------+
| Grants for 'myroot'@'123.123.12.123' |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'myroot'@'123.123.12.123' |
+------------------------------------------------+
授权:
给用户myroot赋予所有数据库的所有权限
mysql> grant all privileges on *.* to 'myroot'@'123.123.12.123' identified by 'mypass' with grant option;
给用户myroot赋予数据库db_school的所有权限
mysql> grant all privileges on db_school.* to 'myroot'@'123.123.12.123' identified by 'mypass' with grant option;
指定IP段用户赋权
mysql> grant all privileges on db_school.* to 'myroot'@'123.123.%' identified by 'mypass' with grant option;
给用户myroot赋予数据库db_school中表stu1的查询和新增的权限
mysql> grant select,update on db_school.stu1 to 'myroot'@'123.123.12.123' identified by 'mypass' with grant option;
解除权限:
mysql> revoke select on db_school.stu1 from 'myroot'@'123.123.12.123' with grant option;
mysql> show grants for 'myroot'@'123.123.12.123';
+----------------------------------------------------------------------------------+
| Grants for 'myroot'@'123.123.12.123' |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'myroot'@'123.123.12.123' |
| GRANT UPDATE ON 'db_school'.'stu1' TO 'myroot'@'123.123.12.123' WITH GRANT OPTION|
+----------------------------------------------------------------------------------+
删除用户
mysql> drop user 'myroot'@'123.123.12.123';
修改密码
mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
mysql
use mysql;
update user set authentication_string=password('root123') where user='root';
flush privileges;
exit;
service mysqld restart;