常用命令
登陆数据库
登陆远程地址为xx端口为3307用户为root
mysql -u root -h xx -p -P 3307
用户权限相关
查看用户相关配置
ALTER USER 'root'@'%' IDENTIFIED BY 'new_password';
修改密码
set password for root@localhost = password('123');
set password for root@localhost = password('123');
新建用户远程连接mysql数据库
grant all on *.* to admin@'%' identified by '123456' with grant option;
flush privileges;
支持root用户允许远程连接mysql数据库
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
flush privileges;
创建一个支持远程登陆的用户
CREATE USER 'root'@'%' IDENTIFIED BY 'yourpassword';
修改用户密码
ALTER USER 'root'@'%' IDENTIFIED BY 'new_password';
赋予用户最大的权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
flush privileges;
为用户分配数据库权限
GRANT ALL PRIVILEGES ON `dbname`.* to 'newuser'@'localhost';
flush privileges;
查看系统用户
use mysql;
select user,host from user;
为某个用户设置最大连接数
ALTER USER 'testuser'@'%' WITH MAX_USER_CONNECTIONS 10;
查看某个哪个用户的连接数最多
use information_schema;
select count(*) as c ,user,host from PROCESSLIST group by user order by c desc limit 10