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

mchdba

-- ======================================
-- MySQL 游标遍历
-- ======================================

delimiter $$
use `test`$$
drop procedure if exists `proc_cursor_t`$$
use `test`$$

CREATE  PROCEDURE `proc_cursor_t`(p_code varchar(30), p_cumu int)
    SQL SECURITY INVOKER
begin
    declare _done int default 0;
    declare v_flag tinyint default 0;   
    declare v_item_id int default 0;
    declare v_item_code varchar(20) default '';
    declare cur_code  cursor for      
    select item_id, item_code
    from csf_maec.report_data
    where rpt_code = p_code and cumu = p_cumu         
        and data_scale_code in('UN','HU','TH','TT','MI','TM','HM','BI','TB','HB','TR') limit 5 ;  
      
    declare continue handler for not found
    begin
        set _done=1;
        select 'not found';
        
        commit;         
    end;
   
    -- handler exception
    declare exit handler for sqlexception
    begin
        -- 异常处理
        select 'exception';
    end;   
   
    -- 打开游标
    open cur_code;
    allcodes:loop
        -- 开始遍历游标
        fetch cur_code into v_item_id, v_item_code;

        if  not _done then   
             -- 开始业务操作
             if p_cumu = 2 then                    
                 select 'cumu 2', v_item_id, v_item_code,now();                 
             else
                 select 'cumu 1', v_item_id, v_item_code,now();
            end if;
        else
            leave allcodes;
        end if;
    end loop allcodes;
    -- 关闭游标
    close cur_code;
   
end

mchdba

-- ===============================
-- mysql 导出表结构、导出存储过程、导出 存储函数
-- ===============================
/usr/local/mysql/bin/mysqldump -h192.168.0.39 -uroot -p123456 -ntd -R csf > /root/p0702.sql
/usr/local/mysql/bin/mysqldump -h192.168.0.39 -uroot -p123456 -ntd -R csf > /root/p0709.sql
/usr/local/mysql/bin/mysqldump -hlocalhost -uroot -p123456 -ntd -R csf_maec > /tmp/pcm0916.sql

mchdba

-- ==================================
-- 查看某张表的索引信息
-- ==================================
在mysql workbeacn查询窗口里面执行
   show indexes from  csf.tmp_excel_data;


强制使用索引
explain SELECT ted.cmp_id, ted.rpt_cd, ted.row_num FROM csf.tmp_excel_data ted
        force index(idx_tmp_excel_data_row_num,idx_tmp_excel_data_col_num)
        WHERE  ted.col_num >3
        and ted.row_num >13
        AND trim(ted.data_value) not in('Top 10 shareholders','Name', 'Shareholders by type', 'Type')

mchdba

-- 使用定界符 备份表数据
-- 导出 1000W数据，大约700M 时间 40S
select * into outfile '/tmp/rpt_style1_3.txt'
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\n'
from csf_pub.rpt_style1 limit 100000000000;

-- 可以在客户端工具里面执行
-- 导入  时间 70S
load data infile '/tmp/rpt_style.txt'
into table csf_pub.rpt_style1
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\n';

mchdba

-- =====================================
-- mysql建立定时任务
-- =====================================

1 开启定时任务参数
mysql> SET GLOBAL event_scheduler = ON;

2 建立定时任务 event
DELIMITER $$
use `test `$$

create EVENT `insert_itemized_hist_fail_blocked` ON SCHEDULE EVERY 1 MINUTE STARTS '2010-09-27 17:12:00' ON COMPLETION

NOT PRESERVE ENABLE
DO
  BEGIN         
         insert into test.t(n) select 1;
     commit;
  END$$
DELIMITER ;

3 验证
select * from test.t;

mchdba

-- =================================================================================
-- error 1
-- =================================================================================
错误日志代码：
/usr/local/mysql/libexec/mysqld: Table './csf_pub/rpt_style1' is marked as crashed and last (automatic?) repair failed

-- deal step

1, service mysqld stop
2, back all datafile
3, [root@mysql csf_pub]# /usr/local/mysql/bin/myisamchk -c -r  /usr/local/mysql/var/csf_pub/rpt_style1.MYI
4, service mysqld start

mchdba

-- =================================================================================
-- mysql 全库备份、恢复命令
-- =================================================================================
1, 备份命令
/usr/local/mysql/bin/mysqldump -uroot -pmysql --opt --default-character-set=utf8 --extended-insert=false --triggers -R

--hex-blob --all-databases --flush-logs --delete-master-logs --delete-master-logs -x >

/tmp/mysqlbackup/backup_20101215_adb_002.sql

/usr/local/mysql/bin/mysqldump -uroot -p123456 --opt --default-character-set=utf8 --extended-insert=false --triggers

-R --hex-blob --all-databases --flush-logs --delete-master-logs --delete-master-logs --lock-tables=false -x >

mysql_$(date +%Y%m%d_%H%M%S).sql


(3)        恢复单个库
Shell> /usr/local/mysql/bin/mysql -uroot -pmysql --database=csf < /tmp/mysqlbackup/backup_20101215_adb_003.sql

(4)        恢复单张表
Mysql所提供的工具目前无法做到这一步，有个方案是：先恢复整个库到一个测试数据服务器上，然后在此服务器上导出单张表的sql，

然后把这个sql导入进生产库上。
在测试服务器上导入库：
Shell> /usr/local/mysql/bin/mysql -uroot -pmysql --database=csf < /tmp/mysqlbackup/backup_20101215_adb_003.sql

从测试服务器导出单个表：
Shell> /usr/local/mysql/bin/mysqldump --user=root -pmysql --no-create-info=FALSE  --order-by-primary=FALSE --

force=FALSE --no-data=FALSE --tz-utc=TRUE --flush-privileges=FALSE --compress=FALSE --replace=FALSE --insert-

ignore=FALSE --extended-insert=TRUE --quote-names=TRUE --hex-blob=FALSE --complete-insert=FALSE --add-locks=TRUE --

port=3306 --disable-keys=TRUE --delayed-insert=FALSE --create-options=TRUE --delete-master-logs=FALSE --comments=TRUE

--default-character-set=utf8 --max_allowed_packet=1G --flush-logs=FALSE --dump-date=TRUE --lock-tables=TRUE --allow-

keywords=FALSE --single-transaction=TRUE "csf" "tmp_excel_data_000728" >

/tmp/mysqlbackup/backup_20101215_single_table_003.sql

在生产库上导入此表记录
Shell> /usr/local/mysql/bin/mysql -uroot -pmysql csf < /tmp/mysqlbackup/backup_20101215_single_table_003.sql

mchdba

-- ============================================
-- mysql分组排序取前N条记录的最简洁的单条sql。
-- ============================================
use test;
drop table if exists test;
create table test (
  id int primary key,
  cid int,
  author varchar(30)
) engine=myisam;
insert into test values  
(1,1,'test1'),
(2,1,'test1'),
(3,1,'test2'),
(4,1,'test2'),
(5,1,'test2'),
(6,1,'test3'),
(7,1,'test3'),
(8,1,'test3'),
(9,1,'test3'),
(10,2,'test11'),
(11,2,'test11'),
(12,2,'test22'),
(13,2,'test22'),
(14,2,'test22'),
(15,2,'test33'),
(16,2,'test33'),
(17,2,'test33'),
(18,2,'test33');

--
select * from (select cid,author,count(*) as number from test group by cid,author) a
where  
2>(
    select count(*)
    from (select cid,author,count(*) as number from test group by cid,author) b
    where a.cid=b.cid and a.number<b.number
)order by cid,number desc;


-- mysql 日期格式
select date_format(now(),"%Y-%m-%d %H:%m:%s");

mchdba

-- ==================================
-- 多服务器复制中的主键冲突问题
-- ==================================
多服务器复制中的Auto-Increment
show variables like '%auto_increment%';
SET @auto_increment_offset=5;
SET @auto_increment_increment=10;

mchdba

-- ==================================
-- 错误日志清理
-- ==================================
[root@ldap-mysql-svn-trac var]# cp ldap-mysql-svn-trac.err ldap-mysql-svn-trac.err_20110119.bak
[root@ldap-mysql-svn-trac var]# true ldap-mysql-svn-trac.err ls > ldap-mysql-svn-trac.err
不需要重启数据库，新的错误日志会继续记录到当前的错误日志中。