Heim >Datenbank >MySQL-Tutorial >mysql运维之---每日一得01_MySQL
2015年7月1日-------------------
1、MHA修复宕机的机器
首先cat /var/log/manager.log|grep -i "All other slaves should start"确定change master命令,把宕掉的数据库给启动,登陆进去后,slave status为空,使用change master命令设置应用的主节点,启动slave进程
然后设置read_only=1,最后检查复制环境,必须启动mha manager的监控(ps aux|grep perl)并查看状态,删除app1.failover.complete,并把# mysql -e "set global relay_log_purge=0"
2、主从复制中,使用alter event把事件enable,不会影响从库的事件状态SLAVESIDE_DISABLED,进行切换后,现在的主库事件状态SLAVESIDE_DISABLED,需要手动进行enable,可以使用如下方式:
select concat('alter event ',EVENT_SCHEMA,'.',EVENT_NAME,' disable;') from information_schema.events;
2015年7月2日------------------
表结构:
CREATE TABLE `question_2` ( `qid` int(11) NOT NULL DEFAULT '0', `QuestionID` varchar(50) NOT NULL COMMENT '只做数据冗余,不做查询条件,不添加索引', `UserID` int(11) DEFAULT NULL, `QuestionTitle` varchar(500) NOT NULL, `Age` int(11) NOT NULL, `Month` int(11) NOT NULL, `CatalogID` int(11) NOT NULL, `Sex` int(11) NOT NULL, `QuestionDesc` longtext NOT NULL, `QuestionTag` varchar(400) DEFAULT NULL, `Score` int(11) DEFAULT NULL, `Anonym` int(11) DEFAULT '0', `CommentCount` int(11) NOT NULL DEFAULT '0', `Source` int(11) DEFAULT NULL, `IsAutoAdd` int(11) DEFAULT '0', `QuestionStatus` int(11) DEFAULT NULL, `OperateStatus` int(11) DEFAULT '0', `OperateTime` datetime DEFAULT NULL, `CreateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '展示时间', `UpdateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`qid`), KEY `idx_2_uid_ctime_qstatus` (`UserID`,`CreateTime`,`QuestionStatus`,`OperateStatus`), KEY `idx_2_qstatus_opstatus_sc_so` (`QuestionStatus`,`OperateStatus`,`Age`,`Score`,`Source`), KEY `idx_2_ctime_qstatus_opstatus` (`CreateTime`,`QuestionStatus`,`OperateStatus`,`CatalogID`,`Age`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; select count(*) from question_2;-- 4086112 explain select * from `question_2` where `questionstatus` >= 0 and `operatestatus` =2 and `age` in ('1','2') order by qid desc limit 60000,20; explain select qid from `question_2` where `questionstatus` >= 0 and `operatestatus` =2 and `age` in ('1','2') order by qid desc limit 60000,20; select count(*) from `question_2` where `questionstatus` >= 0;-- 4064825/4086112 select count(*) from `question_2` where `questionstatus` >= 0 and `operatestatus` =2;-- 3649271/4086112 ---------------优化后的sql explain select * from question_2 inner join (select qid from `question_2` where `questionstatus` >= 0 and `operatestatus` =2 and `age` in ('1','2') order by qid desc limit 60000,20) a using (qid);