MySQL常用命令

1、登录

mysql -u root -p密码
mysql -u root -p
mysql -hlocalhost -uroot -p

2、清屏

cls

3、操作用户

# 查看现有用户
select host,user,authentication_string from mysql.user;
# 新建用户
# create user "username"@"host" identified by "password";
# host="localhost"为本地登录用户，host="ip"为ip地址登录，host="%"，为外网ip登录
create user 'test'@'localhost' identified by '123';
create user 'test'@'192.168.46.120' identified by '123';
create user 'test'@'%' identified by '123';
# 删除用户
drop user 'username'@'host';
# 更改用户名
rename user 'test'@'%' to 'dev'@'%';
# 修改密码
# 1、
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
# 2、格式：mysqladmin -u用户名 -p旧密码 password 新密码
mysqladmin -uroot -p123456 password 1234abcd
# 3、用update直接编辑user表
# 4、
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';

4、授权

# grant privileges on databasename.tablename to 'username'@'host' IDENTIFIED BY 'PASSWORD';

# 授予用户通过外网IP对于该数据库的全部权限
grant all privileges on `test`.* to 'test'@'%' ;
# 授予用户在本地服务器对该数据库的全部权限
grant all privileges on `test`.* to 'test'@'localhost';   
# 给予查询权限
grant select on test.* to 'user1'@'localhost';
# 添加插入权限
grant insert on test.* to 'user1'@'localhost';
# 添加删除权限
grant delete on test.* to 'user1'@'localhost';
# 添加更新权限
grant update on test.* to 'user1'@'localhost';
# 刷新权限
flush privileges;
# 查看权限
show grants;
# 查看某个用户的权限
show grants for 'test'@'%';
# 删除权限
revoke delete on test.* from 'test'@'localhost';
# 刷新权限
flush privileges;

5、查看默认数据库密码

cat /var/log/mysqld.log | grep password