猫猫1 发表于 2018-10-7 13:51:11

Mysql命令汇总

  flush privileges;
  1.修改ROOT密码:

  alter user 'root'@'localhost'>  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@'%'>
  CREATE USER 'username'@'host'>  9.创建主从账号(在主数据库创建)

  GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'>  9.1.主库查询当前master的bin-log
  showmatser 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@"%">  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.删除用户
  DROP USER 'username'@'host';
  15.删除库:
  drop database dbname;

页: [1]
查看完整版本: Mysql命令汇总