登录root账户
mysql -u用户名 -p密码
$ mysql -uroot -p********
创建新用户
CREATE USER ‘username’@‘host’ IDENTIFIED BY ‘password’;
$ CREATE USER 'testuser'@'%' IDENTIFIED BY '12345678';
注意:如果提示ERROR 1819 (HY000): Your password does not satisfy the current policy requirements,可查看当前密码策略
mysql> SHOW VARIABLES LIKE 'validate_password%'; +--------------------------------------+--------+| Variable_name | Value |+--------------------------------------+--------+| validate_password_check_user_name | OFF || validate_password_dictionary_file | || validate_password_length | 8|| validate_password_mixed_case_count | 1|| validate_password_number_count | 1|| validate_password_policy | MEDIUM || validate_password_special_char_count | 1|+--------------------------------------+--------+7 rows in set (0.01 sec)
一般是密码长度,必须有大小写字母,有数字,改法如下:
mysql> set global validate_password_mixed_case_count=0;Query OK, 0 rows affected (0.00 sec)
赋予用户相关权限
grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址;
权限1,权限2,…权限n代表select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等权限
例:赋予全部权限
mysql> grant all privileges on *.* to 'testuser'@'%';mysql> flush privileges;Query OK, 0 rows affected (0.00 sec
查看当前的用户
mysql> select host,user from user;+-----------+------------------+| host| user |+-----------+------------------+| % | ding || % | root || localhost | debian-sys-maint || localhost | mysql.session || localhost | mysql.sys |+-----------+------------------+5 rows in set (0.01 sec)
删除用户
mysql> DELETE FROM user Where User='ding' and Host='%';Query OK, 1 row affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)mysql> select host,user from user;+-----------+------------------+| host| user |+-----------+------------------+| % | root || localhost | debian-sys-maint || localhost | mysql.session || localhost | mysql.sys |+-----------+------------------+4 rows in set (0.00 sec)
设置远程访问
vi /etc/mysql/mysql.conf.d/f
将bind-address = 127.0.0.1,修改为:0.0.0.0或者注释掉
root@root:~#service mysql restartroot@root:~#netstat -an | grep 3306tcp 00 0.0.0.0:3306 0.0.0.0:*LISTEN
添加远程访问用户
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;mysql> FLUSH PRIVILEGES;
重启mysql服务
root@root:/# service mysql restart