[펌] MySQL 최적화 c api 소스

---

출처 : http://flashcafe.org/mysql_study/3967/page/3

---

 

http://cafe.naver.com/q69/7240 컴파일 방법 ( GCC )

 

// Debug Mode
gcc -O6 -mpentiumpro -Wall -o optdb optdb.c -I/usr/local/mysql/include/mysql -L/usr/local/mysql/lib/mysql -lmysqlclient

// Complie Mode
gcc -O6 -mpentiumpro -o optdb optdb.c -I/usr/local/mysql/include/mysql -L/usr/local/mysql/lib/mysql -lmysqlclient 

--------------------------------------------------------------------------------------------------
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql.h>
#include <unistd.h>
#include <time.h>

struct dbmysql
{
        char *host;
        char *dbid;
        char *dbpw;
}a;

int filed, cnt, e, s_cnt, i, counter = 1;

MYSQL_RES* res;
MYSQL_ROW row;
MYSQL mysql;

FILE *inf;

void DBopt(char *dbname[],int sel);
void ErrorMsg(char *msg1);
void closeDB(void);
char *get_date(void);
char *get_time(void);
char *time_to_string(int);
void connectDB(void);

int main()
{  
        int sel;
        
        char *dblist[40];

        mysql_init(&mysql);
        
        system("clear"); 
        
        reloadquestion: ;
        
        // MySQL DB연결
        connectDB();
        
        // 현재 연결된 디비의 모든 테이블 쿼리
        if(mysql_query(&mysql, "show databases"))  
        {  
                ErrorMsg("디비 리스트를 뽑아 오지 못함");
                
        }  

        res = mysql_store_result(&mysql);  
        filed = mysql_num_fields(res);  

        // 설명문 추가
        printf("n");
        printf("/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/n");
        printf("/                                                                         /n");
        printf("/                      MySQL Database Optimizer&check                     /n");
        printf("/                                                                         /n");
        printf("/    제작자   : 조성준 (Cho Sung Jun)                                     /n");
        printf("/    연락처   : eggtech@orgio.net                                                /n");
        printf("/    연락처   : openphp@openphp.com                                              /n");
        printf("/    홈페이지 : http://www.openphp.com                                           /n");
        printf("/    버전     : 0.1pre10                                                         /n");
        printf("/    수정일   : 2001-06-29                                                 /n");        
        printf("/                                                                              /n");
        printf("/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/n");
        printf("n");        
        printf("n");
        printf("0. All Tables Optimize [Testing]n");
        
        while(row = mysql_fetch_row(res))
        {  
                for(i = 0 ; i < filed ; ++i) 
                {                        
                        dblist[counter] = row[0];
                        printf("%d. %s Tables n",counter,row[0]);
                        counter++;
                }

        }
        printf("n최적화할 데이타 베이스를 선택 하십시요 ? : ");
        
        scanf("%d",&sel);
        
        // 버그 수정용
        //printf("nn%snn",dblist[cnt]);
        
        if(sel > counter)
        {
                system("clear");
                counter = 1;
                
                goto reloadquestion;
        }
        
        DBopt(dblist,sel);        
        
        // 디비를 닫는다.
        closeDB();
        
         
}

// 디비 최적화만 따로 함수로 만듬.
void DBopt(char *dbname[],int sel)
{
        int failcounter = 0;
        
        char *opt = (char *) malloc(200);;
        
        s_cnt = sizeof(dbname);
        
        // 현재 디비로 연결한다        
        if(mysql_select_db(&mysql,dbname[sel]))
        {   
                ErrorMsg("데이타 베이스가 없습니다.");
        }
        
        // 현재 연결된 디비의 모든 테이블 쿼리
        if(mysql_query(&mysql, "show tables"))   
        {
                ErrorMsg("디비 리스트를 뽑아 오지 못함");       
        }
        
        res = mysql_store_result(&mysql);
        filed = mysql_num_fields(res);
                
        counter = 0;
        
        //printf("n Good Choise Database : %snn",dbname[sel+1]);
        printf("n");
        
        // 로그 파일들 이미 만들어 둔당..        
        inf = fopen("sungzun-mysql-opt-error.log","a");
        
        // 선택된 디비의 테이블을 읽어 와서 최적화 합니당.
        while(row = mysql_fetch_row(res))
        {
                for(i = 0 ; i < filed ; ++i)
                {
                        // MyISAM 타입의 디비인 경우
                        stpcpy(opt,"OPTIMIZE TABLE ");
                        
                        strcat(opt,row[0]);
                        
                        //printf("nQuery : %s",opt);
                        
                        // 먼저 최적화 한다...Index Sort 등등                        
                        if(!mysql_query(&mysql,opt))
                        {
                                printf("[최적화 실패] 테이블에 문제가 발생 하였습니다. %s n",row[0]);
                                
                                //로그 파일을 만든다.
                                fprintf(inf,"[%s %s] Error Table Name : %s / %sn",get_date(),get_time(),row[0]);
                                ++failcounter;
                        }
                        
                        // 메모리 활당한걸 푼다...
                        free(opt);
                        
                        ++counter;
                }
        }
        
        // 파일을 닫아 부러
        fclose(inf);
        
        //system("clear");        
        
        printf("nn");
        printf("/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/n");
        printf("                                                                           n");
        printf("                               x1b[36m최적화 정보x1b[37m                                 n");
        printf("                                                                           n");
        printf("  최적화한 데이타 베이스명         : %s                                    n",dbname[sel]);
        printf("  작업 대상 테이블 갯수            : %d                                    n",counter);
        printf("  최적화 되지 못한 데이블 갯수     : %d                                    n",failcounter);
        printf("  최적화된 테이블 갯수             : %d                                    n",counter-failcounter);
        printf("                                                                           n");
        printf("/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/n");
        printf("nn");
        mysql_free_result(res);
        exit(0);
}

// 날짜는 구한다.
char *get_date(void)
{ 
        char *temp = (char *) malloc(sizeof(char) * 20);
        time_t tm_t; 
        struct tm *t; 
    
        tm_t = time(NULL);
        t = localtime(&tm_t);

        sprintf(temp,"%d-%s-%s" , t->tm_year+1900,time_to_string(t->tm_mon+1),

                      time_to_string(t->tm_mday)  );
        return temp;
        free(temp);
}

// 시간 구하기
char *get_time(void)
{  

       char *temp = (char *) malloc(sizeof(char) * 20); 
       time_t tm_t;  
       struct tm *t;  
     
       tm_t = time(NULL);             
       t = localtime(&tm_t); 

       sprintf(temp,"%s:%s:%s" , time_to_string(t->tm_hour) ,time_to_string(t->tm_min),

                  time_to_string(t->tm_sec) );  
       

       return temp; 
       free(temp);
}

// int 한 자리를 char 두 자리로
char *time_to_string(int temp_int)
{
        char *temp_str = (char *) malloc(sizeof(char) * 2);

        if( temp_int < 10 )

            sprintf(temp_str,"0%d",temp_int);
        else 

            sprintf(temp_str,"%d",temp_int);

        return temp_str;
        free(temp_str);
}

// 디비 연결
void connectDB(void)
{
        // 디비 접속 설정 부분 입니다..
        a.host = "";  // 접속 호스트
        a.dbid = "";  // 접속 아이디 보통 Root로 하세요
        a.dbpw = ""; //접속 패스워드 root 패스로 하시면(Mysql DB의 Root) 다른 디비 모두 됩니당.
        
        if(!mysql_real_connect(&mysql, a.host, a.dbid, a.dbpw, NULL ,3306, (char *)NULL, 0)) 

             ErrorMsg("MySQL 연결이 되지 못했습니다");
        
}

// 에러 메세지 출력
void ErrorMsg(char *msg1)
{
        // 화면 이쁘게 보이기 위해서리....
        printf("/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/n");
        printf("t%s",msg1);
        printf("nntError Meassage : %sn", mysql_error(&mysql));
        printf("/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/n");
        exit(-1);
}

// 디비 연결 해제
void closeDB(void)
{
        mysql_free_result(res);
        mysql_close(&mysql);
}