mysqldump를 이용한 데이터 이관시 속도 개선 방법

1. mysql 에서 아래 옵션을 변경

myisam_sort_buffer_size = 1000M
myisam_max_sort_file_size = 12G

스토레지 엔진을 myisam으로 변경한 다음 insert 완료후

alter database **** engine=innodb

2. innodb_flush_log_at_trx_commit 설정 값 변경

innodb_flush_log_at_trx_commit 값이 1인경우 insert 할때 로그파일을 기록한다. 이로인해 초당 몇천건 정도 insert할 내용을 몇십건 정도 insert한다.

show variables like 'innodb_flush_log_at_trx_commit';

DB dump파일을 import 할때는 로그가 요 없으므로 0 으로 설정하고 변경 값 확인후 insert한 다음 추후 설정값을 다시 복구한다.

set global innodb_flush_log_at_trx_commit=0;

3. table 별로 백업 sql 만들기

db_root_pw='비밀번호'

db_list=`echo "show databases;" | mysql -N -uroot -p"$db_root_pw"`
 for db in $db_list ;do
  table_list=`echo "show tables" | mysql -N -uroot -p"$db_root_pw" $db`
  for table in $table_list ; do
    mysqldump -uroot -p"$db_root_pw" $db $table > $db.${table}.sql
  done
 done

4. 옵션을 통해 시간 줄이기

--no-autocommit=1 : autocommit을 끄고 개의 테이블 입력 완료후 commit 수행, 오류 발생시 다시 처음부터 시작함

--single-transaction=1 : 작업 후에 변경 된 데이터의 내역을 다시 적용하지 않는다.

--extended-insert=1 :  INSERT 구문이 늘어나는 것을 막아준다.

- export 할때

mysqldump -hHOST_NAME -uMY_ID -pMY_PASSWORD --databases DB_NAME --tables TABLES_OF_DB_NAME --no-autocommit=1 --single-transaction=1 --extended-insert=1 > DUMP.sql

- import 할때

mysql -hHOST_NAME -uMY_ID -pMY_PASSWORD --database DB_NAME < DUMP.sql 

5. txt 파일이용하기

- export 할때

mysqldump DATABASE_NAME > DUMP.txt

- import 할때

mysql -u username -p –database=DATABASE_NAME < DUMP.txt

6. large dump

mysql -h localhost -uroot -pPASSWORD
set global net_buffer_length=1000000;
set global max_allowed_packet=1000000000;
SET foreign_key_checks = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
source /media/dbdump.sql

완료후

SET foreign_key_checks = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;

7. 기타

my.cnf 위치찾기

mysql --verbose --help | grep -A 1 'Default options'

/etc/my.cnf 파일에서

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0

설정 후 와 데이터 입력후 2번 실행

service mysql restart 

키워드 : mysql big data fast import

*reference

• https://cmanios.wordpress.com/2013/03/19/import-a-large-sql-dump-file-to-a-mysql-database-from-command-line/
• https://dba.stackexchange.com/questions/83125/mysql-any-way-to-import-a-huge-32-gb-sql-dump-faster