Mysql

* mysql innodb status
** History list length 1616091 ??
== 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  오류 발생 ==
* 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인 경우 따로 해 줄 필요 없음

=