登录：
mysql -u username -h ip地址 -P 端口 -p 密码
mysql -u username -S /path/mysql.sock -P -p
用户管理：
select user,host from mysql.user;//查看数据库中所用用户信息
create user username@'%';//创建用户
create user username@'%' identified by 'password';//创建用户，并改密
alter user username@'%' identified by 'password';//改密或者延长密码过期时间
alter user username@'%' account lock;//锁用户
alter user username@'%' account unlock;//解锁用户
drop user username@'%';//删除用户
rename user username@'%' to new_username@'%';
alter user username@'%' password expire never;//设置用户密码永不过期
alter user username@'%' password expire x interval day;//设置用户密码X天后过期
alter user username@'%' password expire;//设置密码立即过期，强制下次登录必须改密
权限管理：
show grants for username@'%';//查看用户权限
grant privileges on database.table to username@'%' ;//赋予用户权限
flush privileges;//修改权限后，刷新用户权限表
调整数据库和表的字符集和排序规则
alter database dbname CHARACTER SET utf8mb4 collate utf8mb4_general_ci;
alter table t1 convert to CHARSET utf8mb4 collate utf8mb4_general_ci; //将排序方式调整为大小写不敏感的utf8mb4_general_ci
调整数据库索引
alter table xxx drop index ix_xx; //删除索引
alter table xxx add unique index uk_xx (dataid(128),group_id(128)); //创建唯一索引
MySQL主从管理
show slave status \G
show master status \G
show variales like '%read%';
show global variables where variable_name in('read_only','super_read_only');
set global rpl_semi_sync_master_enabled=1/0;
set global rpl_semi_sync_slave_enabled=1/0;
连接会话管理
show processlist;
select concat('kill ',id,';') from information_schema.processlist where user not in ('system user','root','repl','mysql_monitor','mha','patrol');
事务状态检查
select * from information_schema.innodb_trx \G;
