阿里云 mysql命令_Mysql命令汇总

flush privileges;

1.修改ROOT密码：

alter user 'root'@'localhost' identified by '123';

2.取消密码复杂要求：

set global validate_password_policy=0;

3.查看密码长度：

select @@validate_password_length;

4.查看mysql当前有哪些用户:

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

4.1查看用户权限.

show grants from user;

5.锁表:

flush tables with read lock;

6.解表:

unlock tables;

7.查看锁表时间

show variables like '%timeout%';

8.创建用户方式:

grant all privileges on 库名.* to admin@'%' identified by '123456'; (授权库)

CREATE USER 'username'@'host' IDENTIFIED BY 'password'; (单创建用户)

9.创建主从账号(在主数据库创建)

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'password';

9.1.主库查询当前master的bin-log

show  matser status;

10.从库连接主库方式:

CHANGE MASTER TO

MASTER_HOST='主库IP',

MASTER_PORT=3306,

MASTER_USER='repl',

MASTER_PASSWORD='主库创建的密码',

MASTER_LOG_FILE='mysql-bin.000002',

MASTER_LOG_POS=154;

11.启用mysql账号远程连接

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "youpassword" WITH GRANT OPTION;

12.收回账号权限

revoke select on dbname.* from username;

revoke all privileges on dbname[.tbname] from username;

privileges包括：

alter：修改数据库的表

create：创建新的数据库或表

delete：删除表数据

drop：删除数据库/表

index：创建/删除索引

insert：添加表数据

select：查询表数据

update：更新表数据

all：允许任何操作

usage：只允许登录

13. 创建mysql库

create database mobile DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

14.删除用户

15.删除库:

drop database dbname;

本文转自  一招拜师  51CTO博客，原文链接:http://blog.51cto.com/sadoc/1965227