mysql基础管理

1.创建用户

mysql> create user IF NOT EXISTS test@'10.0.0.%' identified by '123';

2.修改密码

mysql> alter user test@'10.0.0.%' identified by '123456';

3.查询用户

mysql> select user,host ,authentication_string ,plugin from mysql.user;

4.删除用户

mysql> drop user test@'10.0.0.%';

5.添加一个超级管理员

mysql> grant all on *.* to nobody@'10.0.0.%' identified by '123' with grant option;

6.给普通用户添加多个权限

mysql> grant select,update,delete,insert on test.*  to test@'10.0.0.%' identified by '123456';

7.查看授权

mysql> show grants for test@'10.0.0.%';

8.回收权限

revoke delete on test.* from 'test'@'10.0.0.%' ;

9.用户锁定与解锁

mysql> alter user test@'10.0.0.%' ACCOUNT LOCK;
mysql> alter user test@'10.0.0.%' ACCOUNT UNLOCK;

10.密码过期

alter user test@'10.0.0.%' PASSWORD EXPIRE;

11.本地登录

mysql -uxxx -p123456 -S /tmp/mysql.sock
mysql -uxxx -p123456 -hlocalhost
mysql -uxxx -p123456 -h127.0.0.1
mysql -uxxx -p123456 

12.远程登录

mysql -utest -p123456 -h 10.0.0.51  -P 3306

13.免交互执行mysql命令

mysql -uroot -p123 -e "select user,host from mysql.user;" 2>/dev/null

14.导入sql脚本

mysql -uroot -p123 < world.sql 

15.内置功能

help           :  获取客户端命令帮助
\c (Ctrl+C)    :  结束上条命令运行
Ctrl+l         :  清理屏幕
Ctrl+d（\q ）   :  退出会话
\G             : 格式化输出
source         :  导入SQL脚本
system         :  MySQL中调用操作系统命令
use            : 进入某个库