MySQL知识点技能点总结贴--个人版！

每天进步一点点

1,2011-09-08
-- ===============================================================
-- mysql root 管理软件
-- ===============================================================
监控并发软件：Mysqlslap


-- ===============================================================
-- 查询数据库的字符集合
-- ===============================================================
select distinct table_schema,table_collation from information_schema.TABLES
where table_schema not in
('performance_schema','sys','test','mysql','information_schema');

select distinct table_schema,table_collation, table_name from information_schema.TABLES
where table_schema not in
('performance_schema','sys','test','mysql','information_schema')   ;

-- 修改表的字符集
alter table cam.account charset utf8;



-- 建库
delimiter $$
CREATE DATABASE `csf_fdp` /*!40100 DEFAULT CHARACTER SET utf8 */$$



2， 2011-09-08
-- ===============================================================
-- 设置mysql slow log 开启select语句检测
-- ===============================================================
long_query_time = 1
log-slow-queries = /usr/local/mysql/data/slow.log
log-queries-not-using-indexes
在线关闭slow log
set global slow_query_log='off';
show variables like '%slow%';
SELECT * FROM `test`.`report_data` order by rpt_post_time desc limit 10,100000;
-- slow log 没有写入记录
在线开启slow log
set global slow_query_log='on';
show variables like '%slow%';
SELECT * FROM `test`.`report_data` order by rpt_post_time desc limit 20,100000;
-- slow log 有写入记录
5.1.40以上肯定支持的，set global slow_query_log='off'; set global slow_query_log='on';

[ 本帖最后由 mchdba 于 2011-9-8 11:17 编辑 ]

-- ===============================================================
-- mysql root 管理软件
-- ===============================================================
监控并发软件：Mysqlslap


-- ===============================================================
-- 查询数据库的字符集合
-- ===============================================================
select distinct table_schema,table_collation from information_schema.TABLES
where table_schema not in
('performance_schema','sys','test','mysql','information_schema');

select distinct table_schema,table_collation, table_name from information_schema.TABLES
where table_schema not in
('performance_schema','sys','test','mysql','information_schema')   ;

-- 修改表的字符集
alter table cam.account charset utf8;



-- 建库
delimiter $$
CREATE DATABASE `csf_fdp` /*!40100 DEFAULT CHARACTER SET utf8 */$$

-- ===============================================================
-- 设置mysql slow log 开启select语句检测
-- ===============================================================
long_query_time = 1
log-slow-queries = /usr/local/mysql/data/slow.log
log-queries-not-using-indexes

在线关闭slow log
set global slow_query_log='off';
show variables like '%slow%';
SELECT * FROM `test`.`report_data` order by rpt_post_time desc limit 10,100000;
-- slow log 没有写入记录

在线开启slow log
set global slow_query_log='on';
show variables like '%slow%';
SELECT * FROM `test`.`report_data` order by rpt_post_time desc limit 20,100000;
-- slow log 有写入记录

5.1.40以上肯定支持的，set global slow_query_log='off'; set global slow_query_log='on';