【MYSQL】DBA 常用語法

查詢processlist 排除sleep後排序時間大到小

select id, db, user, host, command, time, state, info from information_schema.processlist  where  command <> 'Sleep' order by time desc; 主從設定，從更新主位置進行同步 change master to  master_host='192.XX.XX.XX', master_user='replication_userName', master_password='replication_Passwrod', master_log_file='log-bin.000001', master_log_pos=10111; 只導出表結構-d mysqldump -d DB_name -u'用戶名' -p'密碼' > data.sql 只導數據沒有結構-t mysqldump -t DB_name -u'用戶名' -p'密碼' > data.sql 給予權限及密碼1.授予slave的權限 2.授予select的權限 grant replication slave on *.* to '用戶名'@'IP' identified by '密碼'; grant select on xxxtable.* to '用戶名'@'IP' identified by '密碼'; 查看MYSQL所有使用者 select user,host from mysql.user; 更改使用者密碼 set password for '用戶名'@'IP'=password('新密碼'); grant usage on *.* '用戶名'@'IP' identified by '新密碼'; update mysql.user set password=password('新密碼') where user='用戶名'; 查詢某用戶的權限show grants from '用戶名'@'IP';  mysql> show grants for 'root'@'localhost'; +---------------------------------------------------------------------+ | Grants for root@localhost                                           | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec) 移除多個權限 revoke crcate,index,select,event on `DB_NAME`.* from '用戶名'@'IP'; 移除帳號 DROP USER '用戶名'@'IP'; 查看slave狀態 show slave status \G 查看master狀態 show master status;