Mysql主从同步以及紧急处理慢查询管理

主从配置

1. 建立主从(配置从服务器)

CHANGE MASTER TO
MASTER_HOST='xxx.xxx.xxx.xxx', //主服务器
MASTER_PORT=xxxxx,
MASTER_USER='xxxxx',
MASTER_PASSWORD='xxxxxxxxxxxx',
MASTER_LOG_FILE='master-bin.xxxx', // master
MASTER_LOG_POS=xxxxx;

2. 重置主从

STOP SLAVE;
RESET SLAVE;

慢查询处理

select CONCAT('KILL ',ID, ';') from information_schema.processlist WHERE  INFO IS NOT NULL AND Command = 'Query' ORDER BY Time

select * from information_schema.processlist WHERE  INFO IS NOT NULL AND Command = 'Query' AND Time > 10 ORDER BY Time

查询碎片情况并且进行碎片整理

select table_schema, table_name, data_free, engine from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0 AND table_schema = 'fad' ORDER BY data_free;

select CONCAT('OPTIMIZE TABLE ', table_name,';') engine from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0 AND table_schema = 'fad' ORDER BY data_free;

SELECT * FROM information_schema.`TABLES` WHERE table_schema not in ('information_schema', 'mysql')

select concat(round(sum(data_length/1024/1024),2),'MB') as data from information_schema.tables  where table_schema='fad'