SEARCH
TOOLBOX
LANGUAGES
Mysql

Mysql

From Chaehyun

(Difference between revisions)
Jump to: navigation, search
(→csv file 바로 import 하기)
(→mysql 사용자 계정 설정)
 
(28 intermediate revisions not shown)
Line 37: Line 37:
== disk 상태 보기 ==
== disk 상태 보기 ==
* iostat -k 1
* iostat -k 1
 +
* 설치하려면
 +
** yum install sysstat
== 다중 출력 ==
== 다중 출력 ==
Line 76: Line 78:
* 한글이 깨질 경우,
* 한글이 깨질 경우,
** load data infile 'item_meta.txt' into table item '''character set utf8''' fields terminated by '\|' lines terminated by '\n' (item_id, name, cat1, cat2, author, download, keywords);
** load data infile 'item_meta.txt' into table item '''character set utf8''' fields terminated by '\|' lines terminated by '\n' (item_id, name, cat1, cat2, author, download, keywords);
 +
* ERROR 13 (HY000): Can't get stat of '/home/skplanet/users/chaehyun/data/input.txt' (Errcode: 13) 에러 발생한 경우
 +
** load data '''local''' infile '/home/skplanet/users/chaehyun/data/input.txt' into table path fields terminated by ',' lines terminated by '\n' (src,dst,time);
 +
* ERROR 1148 (42000): The used command is not allowed with this MySQL version 에러 발생한 경우
 +
** mysql -u user -p '''--local-infile''' dbname
 +
** 위와 같이 mysql을 켜면 됨
 +
 +
== csv file 로 바로 export 하기 ==
 +
* mysql -h fancy-secondary-east.thingcloud.info -u recengine -p django_fancy_db -e "select * from fancy_categoryitem order by id" --skip-column-names | tr '[[:blank:] ]' ','
 +
* mysql -husershard0-secondary-east.thingcloud.info -urecengine -p -e "select thing_id,id from fancy_shard0.fancy_newthing" --skip-column-names | tr '[[:blank:] ]' ',' > newthing
 +
 +
== Table './codesprint/path' is marked as crashed and should be repaired  오류 발생 ==
 +
* repair path
 +
* http://forcecore.tistory.com/428
 +
 +
== hash 로 indexing 하기 ==
 +
* CREATE TABLE lookup (id INT, INDEX USING HASH (id)) ENGINE = MEMORY;
 +
 +
== memory table 최대 크기 설정 ==
 +
* max_heap_table_size 시스템 변수는 MEMORY 테이블이 얼마나 커질 수 있는지를 나타낸다. 이 변수는 CREATE TABLE로 생성된 것을 포함해서 모든 MEMORY 테이블에 적용된다. 하지만, 내부 MEMORY 테이블의 경우에는 실제 최대 크기가 tmp_table_size와 연결된 max_heap_table_size로 결정된다: 두 변수 중에 작은 값이 적용된다. 내부 MEMORY 테이블의 크기가 제한치를 초과하게 되면, MySQL은 자동으로 디스크 기반 MyISAM 테이블로 변환 시킨다.
 +
 +
== python에서 사용할 때 ==
 +
* easy_install mysql-python
 +
* http://stackoverflow.com/questions/454854/no-module-named-mysqldb
 +
 +
= innodb replication 설정 =
 +
* http://wiki.kldp.org/wiki.php/MySQL%B8%AE%C7%C3%B8%AE%C4%C9%C0%CC%BC%C7#s-1.3
 +
* http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=01&m_no=21425&cat1=6&cat2=201&cat3=0&lang=k
 +
* http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
 +
== 순서대대로 봅시다 ==
 +
# master 서버에서 /etc/my.cnf 에 추가
 +
#* [mysqld]
 +
#* log-bin=mysql-bin
 +
#* server-id=1
 +
#* sudo /etc/init.d/mysqld restart
 +
# slave 서버에서
 +
#* [mysqld]
 +
#* server-id=2
 +
#* sudo /etc/init.d/mysqld restart
 +
# master 서버에서 계정 생성
 +
#* mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
 +
#* mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
 +
# master 서버에서 기존 data dump
 +
#* mysql> FLUSH TABLES WITH READ LOCK; //잠시 커밋을 막고
 +
#* mysql > SHOW MASTER STATUS;  //여기 나온 숫자들을 기록
 +
#** File이랑 Position
 +
#** 혹시 기존에 binary logging을 한적이 없다면, filename : 빈줄\'\' position : 4로 설정
 +
#* mysqldump --all-databases --master-data >dbdump.db
 +
#* mysql> UNLOCK TABLES;
 +
#* dump file을 slave로 복사
 +
# slave 서버에서
 +
#* 한 번만 설정해두면 됨..
 +
<pre>
 +
mysql> CHANGE MASTER TO
 +
    ->    MASTER_HOST='master_host_name',
 +
    ->    MASTER_USER='replication_user_name',
 +
    ->    MASTER_PASSWORD='replication_password',
 +
    ->    MASTER_LOG_FILE='recorded_log_file_name',
 +
    ->    MASTER_LOG_POS=recorded_log_position;
 +
</pre>
 +
#* mysq> START SLAVE;
 +
 +
== binary export ==
 +
<pre>
 +
#!/bin/bash
 +
for i in {0..93}
 +
do
 +
    mysql -uroot -p'*****' --default-character-set=utf8 -E -e "select source from codesprint.submit where id=$i into dumpfile '/tmp/answer/$i'"
 +
    #!/bin/bash
 +
    echo $i
 +
done
 +
</pre>
 +
 +
== 설정 관련 ==
 +
* max_binlog_size=1073741824
 +
** binary log file이 이 크기보다 커지면, 이 로그 파일을 닫고, 다음 로그 파일을 연다
 +
** transaction이 큰 경우, 로그 파일이 이 크기보다 커질 수 있다.
 +
** 바이너리 로그는 갱신쿼리를 기록한 것으로 리커버리 및 replication에 사용되는 중요한 로그파일이다.
 +
* innodb_log_file_size=5242880
 +
** 이 값이 클수록, buffer pool에서 checkpoint flush를 덜하게 되고, disk I/O를 덜하게 된다. 대신 crash가 났을 때 recovery가 더 오래 걸리게 된다
 +
** 이 로그 파일의 사이즈를 buffer_pool의 15% 정도로 설정하는 것이 바람직하다
 +
 +
== 루트 비번 분실 ==
 +
* mysqladmin -u root -p shutdown
 +
 +
== 사용자 비번 변경 ==
 +
* use  mysql;
 +
* update  user  set  password=password('123456') where user='root';
 +
* flush  privileges; <--- 적용
 +
 +
== 외부에서 접근 가능하도록 변경 ==
 +
* use mysql;
 +
* insert into user (Host, User, Password) values ('%', 'root', password('pass'));
 +
* flush  privileges;
 +
* grant all privileges on *.* to root@'%' identified by 'password' with grant option;
 +
 +
== inno db log size error ==
 +
* InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
 +
* InnoDB: than specified in the .cnf file 0 104857600 bytes!
 +
* 위와 같은 에러가 발생하면,
 +
** sudo rm /var/lib/mysql/ib_logfile*
 +
** sudo /etc/init.d/mysqd restart
 +
 +
= mysql 사용자 계정 설정 =
 +
* grant all privileges on `dbuser_%`.* to dbuser@localhost identified by 'password';
 +
* grant all privileges on movie.* to dbuser@localhost identified by 'password';
 +
* FLUSH PRIVILEGES;
 +
** grant인 경우 따로 해 줄 필요 없음
 +
 +
= 특정 쿼리 취소 (cancel slow query) =
 +
* show processlist;
 +
* kill query 21445;
 +
** http://johnstanfield.com/?p=223

Latest revision as of 16:19, 26 February 2013

  • mysql innodb status
    • History list length 1616091 ??

Contents

innoDB purge 관련

  • innoDB 에서는 sql에 의해 row를 지우더라도, 실제 database에서 row가 즉시 물리적으로 삭제 되지 않음
  • 삭제를 위한 undo log record가 innodb에서 버려졌을 때, 그제서야 해당되는 row와 index rocord를 db에서 물리적으로 삭제함
  • 이러한 삭제 작업을 purge라고 칭함. 그리고 이 purge 작업은 굉장히 빠르고, 보통은 sql의 삭제와 동일한 time order를 가짐
  • my.cnf
  • innodb_max_purge_lag = 8192
    • http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html
    • purge 작업이 지체되었을 때, 얼마나 기다릴 것인지 조절하는 설정
    • 기본은 0임. (delay 없음)
    • 값이 있을 때는, ((purge_lag/innodb_max_purge_lag)×10)–5 milliseconds 만큼, insert, update, delete 작업을 delay 시킨다.
  • show innodb status 하면 innoDB monitor output을 볼 수 있음
    • History list length 값이 바로 purge_lag의 값임
  • http://mysqlha.blogspot.com/2008/07/how-do-you-know-when-innodb-gets-behind.html
  • History list length가 커지면, background thread에 의해 실행될 pending된 io가 많아진다는 것을 의미함. background thread가 이 io를 수행하는 속도는 서버의 현재 상태 (busy or idle)와 서버가 초당 100개의 IO를 처리할 수 있다는 가정에 따라 결정된다.
  • primary key 순서대로 스캔하는 thread와 지우는 thread가 동시에 있을 때, 스캔하는 thread는 delete thread에 의해 삭제 되었지만, 아직 purge되지 않은 row를 만날 수 있다. 만약 백그라운드에서 돌고 있는 purge가 delete 작업을 따라잡지 못하면, 스캔 작업은 생각했던 것 보다 굉장히 느릴 것이다. 그러므로 innodb_max_purge_lag을 설정하여, insert, update, delete 작업을 delay 시켜 버린다.
  • innodb_buffer_pool_size 를 키우자. 메모리의 70~80% 까지. (현재 우리는 1GB로 설정되어 있음)
  • SET GLOBALinnodb_max_dirty_pages_pct=0 를 통해 dirty page 비율을 조절할 수 있음
    • 기본값은 90
  • http://blogs.innodb.com/wp/2011/04/mysql-5-6-multi-threaded-purge/
  • 5.5 이전 버전에서는 innodb master thread가 purge와 flushing을 같이 수행한다. 그러므로 서버에 로드가 많이 걸리고 dirty page가 많이 나오면, master thread가 대부분의 시간을 flushing을 하는데 써 버리기 때문에 purging이 되지 않는 문제가 있었음

data 옮기기

  • data를 삭제하기 보다는 새로 insert를 하는 게 더 낫다
  • select ~ where~ into outfile "output"
  • loaddata infile "table" into table Document
    • insert 보다 30배 빠르다는데?
    • unique 확인을 끄는 설정을 켜면 더 빠름
  • mysqldump의 경우, sql로 만드므로, dump file이 커지고, data를 넣을 때, insert를 실행하기 때문에 느리다

설정 값 바꾸기

  • show global variable;
  • set global innodb_max_purge_lag = 0
    • 이러면 바뀜

disk 상태 보기

  • iostat -k 1
  • 설치하려면
    • yum install sysstat

다중 출력

  • mysql -E -e "select~" 라고 하면 수직으로 출력됨

InnoDB MVCC (Multi Version Concurrency Control)

  • http://intomysql.blogspot.com/2010/12/mvcc-multi-version-concurrency-control.html
  • 동시에 하나의 레코드에 대해서 여러개의 버전이 관리됨
  • 특정 레코드가 어떤 트랜잭션에 의해서 변경되고 있는 경우, 다른 트랜잭션은 이전 버전의 레코드만 읽을 수 있음
    • 특별한 잠금 없이 레코드를 읽을 수 있음 (Consistent non-locking read라고 표현)
  • 데이터 페이지에는 항상 가장 최근 버전의 레코드가 저장됨 (Commit되지 않은 경우라 하더라도)
  • 레코드의 변경이 가해지면, 이전 버전은 시스템 테이블 스페이스의 Undo 영역으로 복사됨
  • Undo log의 종류
    • INSERT : Insert 에 의해서 발생한 Undo log 레코드이며 트랜잭션이 완료되면 불필요하므로 삭제됨
    • UPDATE : Update 또는 Delete에 의해서 발생한 Undo log 레코드. 트랜잭션이 완료되어도, MVCC를 위해서 보관되어야 함.
  • 최종적으로 불필요한 Undo log 레코드는 Purge thread가 모두 제거하게 됨
  • Update 작업이 매우 빈번한 경우, Purge thread가 불필요한 Undo log 제거를 제때에 처리하지 못할 수 있음
    • 이런 경우 "History length" 가 계속 증가하게 됨 (SHOW ENGINE INNODB STATUS 명령으로 확인 가능)
  • Undo log 포맷
    • Primary key
    • 트랜잭션 아이디 (현재 레코드를 변경한 트랜잭션의 아이디)
    • 변경 이전 값

java에서 접근할 때

  • jdbc:mysql://reople.com/movie?useUnicode=true&characterEncoding=utf8&user=xlos&password=pass;
    • xml에서는 & 대신에 & amp; 라고 써야함
  • 혹은
    • db.addConnectionProperty("useUnicode", "true");
    • db.addConnectionProperty("characterEncoding", "UTF-8");

mysql이 안 뜰 때 (ubuntu 기준)

  • /var/log/syslog 확인
  • /etc/mysql/my.conf 확인

csv file 바로 import 하기

  • load data infile '/tmp/item.txt' into table item_to_item_batch fields terminated by ',' lines terminated by '\n' (item_id, recommended, score);
  • ERROR 29 (HY000): File '/mnt/users/chaehyun/temp/item.txt' not found (Errcode: 13) 발생 시
    • 해당 파일을 /var/lib/mysql/fancy/item.txt 로 옮김
  • 한글이 깨질 경우,
    • load data infile 'item_meta.txt' into table item character set utf8 fields terminated by '\|' lines terminated by '\n' (item_id, name, cat1, cat2, author, download, keywords);
  • ERROR 13 (HY000): Can't get stat of '/home/skplanet/users/chaehyun/data/input.txt' (Errcode: 13) 에러 발생한 경우
    • load data local infile '/home/skplanet/users/chaehyun/data/input.txt' into table path fields terminated by ',' lines terminated by '\n' (src,dst,time);
  • ERROR 1148 (42000): The used command is not allowed with this MySQL version 에러 발생한 경우
    • mysql -u user -p --local-infile dbname
    • 위와 같이 mysql을 켜면 됨

csv file 로 바로 export 하기

  • mysql -h fancy-secondary-east.thingcloud.info -u recengine -p django_fancy_db -e "select * from fancy_categoryitem order by id" --skip-column-names | tr '[[:blank:] ]' ','
  • mysql -husershard0-secondary-east.thingcloud.info -urecengine -p -e "select thing_id,id from fancy_shard0.fancy_newthing" --skip-column-names | tr '[[:blank:] ]' ',' > newthing

Table './codesprint/path' is marked as crashed and should be repaired 오류 발생

hash 로 indexing 하기

  • CREATE TABLE lookup (id INT, INDEX USING HASH (id)) ENGINE = MEMORY;

memory table 최대 크기 설정

  • max_heap_table_size 시스템 변수는 MEMORY 테이블이 얼마나 커질 수 있는지를 나타낸다. 이 변수는 CREATE TABLE로 생성된 것을 포함해서 모든 MEMORY 테이블에 적용된다. 하지만, 내부 MEMORY 테이블의 경우에는 실제 최대 크기가 tmp_table_size와 연결된 max_heap_table_size로 결정된다: 두 변수 중에 작은 값이 적용된다. 내부 MEMORY 테이블의 크기가 제한치를 초과하게 되면, MySQL은 자동으로 디스크 기반 MyISAM 테이블로 변환 시킨다.

python에서 사용할 때

innodb replication 설정

순서대대로 봅시다

  1. master 서버에서 /etc/my.cnf 에 추가
    • [mysqld]
    • log-bin=mysql-bin
    • server-id=1
    • sudo /etc/init.d/mysqld restart
  2. slave 서버에서
    • [mysqld]
    • server-id=2
    • sudo /etc/init.d/mysqld restart
  3. master 서버에서 계정 생성
    • mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
    • mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
  4. master 서버에서 기존 data dump
    • mysql> FLUSH TABLES WITH READ LOCK; //잠시 커밋을 막고
    • mysql > SHOW MASTER STATUS; //여기 나온 숫자들을 기록
      • File이랑 Position
      • 혹시 기존에 binary logging을 한적이 없다면, filename : 빈줄\'\' position : 4로 설정
    • mysqldump --all-databases --master-data >dbdump.db
    • mysql> UNLOCK TABLES;
    • dump file을 slave로 복사
  5. slave 서버에서
    • 한 번만 설정해두면 됨.. 
mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;
    • mysq> START SLAVE;

binary export 

#!/bin/bash
for i in {0..93}
do
    mysql -uroot -p'*****' --default-character-set=utf8 -E -e "select source from codesprint.submit where id=$i into dumpfile '/tmp/answer/$i'"
    #!/bin/bash
    echo $i
done

설정 관련

  • max_binlog_size=1073741824
    • binary log file이 이 크기보다 커지면, 이 로그 파일을 닫고, 다음 로그 파일을 연다
    • transaction이 큰 경우, 로그 파일이 이 크기보다 커질 수 있다.
    • 바이너리 로그는 갱신쿼리를 기록한 것으로 리커버리 및 replication에 사용되는 중요한 로그파일이다.
  • innodb_log_file_size=5242880
    • 이 값이 클수록, buffer pool에서 checkpoint flush를 덜하게 되고, disk I/O를 덜하게 된다. 대신 crash가 났을 때 recovery가 더 오래 걸리게 된다
    • 이 로그 파일의 사이즈를 buffer_pool의 15% 정도로 설정하는 것이 바람직하다

루트 비번 분실

  • mysqladmin -u root -p shutdown

사용자 비번 변경

  • use mysql;
  • update user set password=password('123456') where user='root';
  • flush privileges; <--- 적용

외부에서 접근 가능하도록 변경

  • use mysql;
  • insert into user (Host, User, Password) values ('%', 'root', password('pass'));
  • flush privileges;
  • grant all privileges on *.* to root@'%' identified by 'password' with grant option;

inno db log size error

  • InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
  • InnoDB: than specified in the .cnf file 0 104857600 bytes!
  • 위와 같은 에러가 발생하면,
    • sudo rm /var/lib/mysql/ib_logfile*
    • sudo /etc/init.d/mysqd restart

mysql 사용자 계정 설정

  • grant all privileges on `dbuser_%`.* to dbuser@localhost identified by 'password';
  • grant all privileges on movie.* to dbuser@localhost identified by 'password';
  • FLUSH PRIVILEGES;
    • grant인 경우 따로 해 줄 필요 없음

특정 쿼리 취소 (cancel slow query)

Retrieved from "http://chaehyun.kr/w/Mysql"