登錄：

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;

?

?