[ Mysql ] 전체 DB 데이터베이스 테이블 최적화

출처:http://www.lxg.de/code/shell-script-to-optimize-all-tables-in-all-databases-mysql

#!/bin/bash
 
MYSQL_LOGIN='-u root --password=YOURPASSWORD'
 
for db in $(echo "SHOW DATABASES;" | mysql $MYSQL_LOGIN | grep -v -e "Database" -e "information_schema")
do
        TABLES=$(echo "USE $db; SHOW TABLES;" | mysql $MYSQL_LOGIN |  grep -v Tables_in_)
        echo "Switching to database $db"
        for table in $TABLES
        do
                echo -n " * Optimizing table $table ... "
                echo "USE $db; OPTIMIZE TABLE $table" | mysql $MYSQL_LOGIN  >/dev/null
                echo "done."
        done
done

[ Mysql ][ innodb ] 데이터 베이스 별 ibdata

데이터 베이스별로 ibdata 생성시키기

[mysqld]
innodb_file_per_table

참고사이트
http://dev.mysql.com/doc/refman/5.0/en/innodb-multiple-tablespaces.html

[ Mysql ] 관리자 root 패스워드 리셋

만일 MySQL의 root 암호를 잊어 버려도 다음과 같이 재설정 할 수 있다.

1. MySQL 데몬 프로세스를 중지
2. - skip-grant-tables 옵션을 붙여 mysqld를 다시 시작
3. mysql-u root에서 MySQL 클라이언트 시작
4. UPDATE mysql.user SET password = PASSWORD ( 'password') WHERE user = 'root';
5. FLUSH PRIVILEGES; 실행

MySQL을 권한 무시 모드로 시작한 후 정상적인 비밀번호 업데이트와 같은 방식으로 처리하면 비밀번호를 재설정 할 수 있다.

[MySql] left join right join inner join 조인 정리

MySQL의 LEFT JOIN, RIGHT JOIN, INNER JOIN

테스트 테이블을 준비

CREATE TABLE test1 (

    my_id VARCHAR (255) PRIMARY KEY,

    data1 VARCHAR (255)

);

CREATE TABLE test2 (

    id INT PRIMARY KEY AUTO_INCREMENT,

    my_id VARCHAR (255) NOT NULL,

    data2 VARCHAR (255)

);

테이블 결과

SELECT * FROM test1;

| my_id | data1 |

------------------

| aaaaa | aa1 |

| bbbbb | aa2 |

| ccccc | aa3 |

| ddddd | aa3 |

SELECT * FROM test2;

| id | my_id | data2 |

-----------------------

| 1 | aaaaa | bb1 |

| 2 | aaaaa | bb2 |

| 3 | bbbbb | cc1 |

| 4 | ccccc | dd3 |

| 5 | eeeee | NULL |

1) LEFT JOIN

2) RIGHT JOIN

3) INNER JOIN

1) JOIN 왼쪽 테이블의 데이터는 기본적으로 모두 표시합니다 (WHERE 절에서 제외하지 않는 한). 그리고 거기에 해당하는 오른쪽 테이블의 데이터를 추가합니다. 오른쪽 테이블에 해당하는 데이터가 존재하지 않는 경우는 NULL 대신에 사용됩니다.

SELECT

    test1.my_id AS id_1,

    test2.my_id AS id_2,

    data1,

    data2

FROM

    test1 LEFT JOIN test2

ON

    test1.my_id = test2.my_id;

| id_1 | id_2 | data1 | data2 |

-------------------------------

| aaaaa | aaaaa | aa1 | bb1 |

| aaaaa | aaaaa | aa1 | bb2 |

| bbbbb | bbbbb | aa2 | cc1 |

| ccccc | ccccc | aa3 | dd3 |

| ddddd | NULL | aa3 | NULL |

2) 1)의역으로 JOIN 오른쪽 테이블의 데이터는 기본적으로 모든 표시됩니다. 그리고 거기에 해당하는 왼쪽 테이블의 데이터를 추가합니다. 왼쪽 테이블에 해당하는 데이터가 존재하지 않는 경우는 NULL 대신에 사용됩니다.

SELECT

    test1.my_id AS id_1,

    test2.my_id AS id_2,

    data1,

    data2

FROM

    test1 RIGHT JOIN test2

ON

    test1.my_id = test2.my_id;

| id_1 | id_2 | data1 | data2 |

-------------------------------

| aaaaa | aaaaa | aa1 | bb1 |

| aaaaa | aaaaa | aa1 | bb2 |

| bbbbb | bbbbb | aa2 | cc1 |

| ccccc | ccccc | aa3 | dd3 |

| NULL | eeeee | NULL | NULL |

3) 좌우 두 테이블에서 해당 데이터가 존재하는 것 밖에 표시하지 않습니다

SELECT

    test1.my_id AS id_1,

    test2.my_id AS id_2,

    data1,

    data2

FROM

    test1 INNER JOIN test2

ON

    test1.my_id = test2.my_id;

| id_1 | id_2 | data1 | data2 |

-------------------------------

| aaaaa | aaaaa | aa1 | bb1 |

| aaaaa | aaaaa | aa1 | bb2 |

| bbbbb | bbbbb | aa2 | cc1 |

| ccccc | ccccc | aa3 | dd3 |

[Mysql] insert 속도 개선

다중으로 insert쓸때에는
밑에 처럼 나열하는게 빠르다 .

insert into tablename (field1,field2) values(data1-1,data2-1),(data1-1,data2-1)....

[MySql] 데이터 무한 삽입 테스트

test.sql에 데이터 삽입 sql문 작성해 놓고
(인서트문 100개 정도 넣어 놨다)

아래를 test.sh로 저장

#!/bin/bash
mysql -u root -p"12345" dbname < test.sql;
mysql -u root -p"12345" dbname -e "select count(*) from testrun;" >> count.text;
mysql -u root -p"12345" dbname -e "show table status like 'testrun'" >> count.text;
mysql -u root -p"12345" dbname -e "select table_schema, sum(round((data_length+index_length)/1024/1024,0)) as total_size_mb from information_schema.tables where table_schema like 'dbname' and engine like 'innodb' group by table_schema;" >> count.text;
date '+%F  %r' >> count.text
echo >> count.text

1분단위로 위 파일을 실행
crontab -e에 등록
*/1 * * * * /root/test.sh

count.text에서 확인

[MySql] ERROR 1005 (HY000):Cant create table (errno: 121)

테이블 생성할 때
CONSTRAINT  testrun_ibfk_1 ~~~
 testrun_ibfk_1 라고 선언된게 어딘가에 있기때문에 발생
CONSTRAINT testrun_babo_ibfk_1 ~~~라는 식으로 변경하믄 OK

[MySql] 테이블 생성 sql 뽑기

mysql>show create table TBLNAME;