mysql 4.0 버전 5.6.x 업그레이드 와 euckr utf8 변경 방법

기본 APM 이 설치된 서버를 운영 관리하다보면 오래된 버전을 최신버전등으로 업그레이드 해야되는 경우가 종종 발생하게 된다. 이유는 간단하다. 최신 트랜드에 맞춰 좀더 좋은 퍼포먼스를 보여주기 위해서다. 성능 또한 향상되는 이점이 있다. 최근 관리하는 서버의 mysql (DB) 버전이 너무 오래된 버전을 사용하고있었는데, 고객사에서 향상된 퍼포먼스를 요구하여 버전 업그레이드를 시도해보았다. 특히 mysql 의 경우 앞 메이저 번호가 바뀌면 큰 변화가 일어난 상태이기 때문에 작업에 준비를 철저히 해야된다. [mysql 4.0.27 -> 5.6.15 업그레이드 순서] [euckr -> utf8 변환] 진행하기 전에 my.cnf 에서 설정값을 변경해야된다. mysqldump -uroot -p --default-character-set=utf8 --all-databases > all_utf8.sql [sql 파일 엔진 변경 및 언어셋 변경방법] sed -i 's/TYPE=MyISAM/ENGINE=MyISAM/g' hojufirst.sql [에러 발생 수정] Invalid default value for 'school_no' 4.0 to 5.0 이상으로 업그레이드 할경우에, sql 파일을 db 에 넣는 와중에 위와 같은 에러가 발생할 경우가 생긴다. 이런 경우가 발생할때에는 해당 sql 파일을 열어서 문제가 생기는 테이블 생성 위치로 이동하여, `school_ielts` char(0) NOT NULL default 'n', 이런식으로 되어있을텐데, default 'n' 에서 n 을 지워준다 '' 이것만.. 이유는 5.0이상부터는 auto_increment 랑 default 값을 같이 사용하지 못하기 때문!! [이 아래부터는 예전방식으로 백업방법] 4.0.27 -> 4.1.22 -> 5.6.15 순서대로 업그레이드를 진행해야됨. (언어셋은 euckr 로 포멧이 된 DB임) [mysql 4.0.27] 서비스 중지 /usr/local/mysql/bin/mysqladmin -uroot -p shutdown [root@localhost db]# ls -al /usr/local 합계 124 drwxr-x--x. 19 root root 4096 2014-03-28 10:54 . drwx--x--x. 13 root root 4096 2014-03-25 23:57 .. lrwxrwxrwx 1 root root 23 2014-03-27 15:25 apache -> /usr/local/apache_2.4.7 drwxr-xr-x 15 root root 4096 2013-12-12 17:21 apache_2.4.7 -rw-r--r-- 1 root root 47065 2014-03-27 15:28 apmtools.install.log drwxr-xr-x 4 root root 4096 2014-03-27 20:02 backup drwxr-x--x. 2 root root 4096 2011-09-23 20:50 bin drwxr-xr-x. 2 root root 4096 2011-09-23 20:50 etc drwxr-xr-x. 2 root root 4096 2011-09-23 20:50 games drwxr-xr-x. 2 root root 4096 2011-09-23 20:50 include drwxr-xr-x. 2 root root 4096 2014-03-27 15:28 lib drwxr-xr-x. 2 root root 4096 2011-09-23 20:50 lib64 drwxr-xr-x. 2 root root 4096 2011-09-23 20:50 libexec drwxr-xr-x. 9 root root 4096 2014-03-26 00:01 lscp lrwxrwxrwx 1 root root 23 2014-03-28 10:54 mysql -> /usr/local/mysql_5.6.15 drwxr-xr-x 11 root root 4096 2008-11-10 17:32 mysql_4.0.27 drwxr-xr-x 10 root root 4096 2013-09-16 14:34 mysql_4.1.22 drwxr-xr-x 13 root root 4096 2013-12-12 14:16 mysql_5.6.15 lrwxrwxrwx 1 root root 20 2014-03-27 15:28 php -> /usr/local/php_5.5.6 drwxr-xr-x 7 root root 4096 2014-03-25 17:53 php_5.5.6 drwxr-xr-x. 2 root root 4096 2011-09-23 20:50 sbin drwxr-xr-x. 5 root root 4096 2014-03-25 23:57 share drwxr-xr-x. 5 root root 4096 2014-03-27 15:30 src [root@localhost db]# 링크 삭제 rm mysql ln -s /usr/local/mysql_4.1.22 /usr/local/mysql mysql 4.0.27 -> 4.1.22 동기화 rsync -av --delete /usr/local/mysql_4.0.27/data/ /usr/local/mysql_4.1.22/data/ my.cnf 환경설정 [root@localhost local]# cat /etc/my.cnf__ [client] #password = your_password port = 3306 socket = /tmp/mysql.sock default-character-set=utf8 # The MySQL server [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 log-bin # but will not function as a master if omitted server-id = 1 character-set-server=utf8 #collation-server=utf8_general_ci init_connect=SET #collation_connection=utf8_general_ci init_connect=SET NAMES utf8 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash default-character-set=utf8 # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout ---------------------------------------------------- mysql 4.0.x 버전에서 4.1.x 버전으로 업그레이드 시. 에러 문제점은 아래와 같이 해결하면 됨. /usr/local/mysql/bin/mysqld_safe --skip-grant-tables --user=mysql & /usr/local/mysql/bin/mysql_fix_privilege_tables --password=MySQL_ROOT_PASSWORD /usr/local/mysql/bin/mysqladmin -uroot -p shutdown /usr/local/mysql/bin/mysqld_safe & /usr/local/mysql/bin/mysqlcheck -uroot -p --all-databases --auto-repair mysql -uroot -p 언어셋 확인 show variables like 'c%'; 그럼 4.1.22 버전 업그레이드가 완료가 된 상태다 .이 상태에서 다시 5.6.15 버전으로 업그레이드를 진행!! 컴파일은 모두 진행하고 준비가 완료된 상태에서 설명을 함. mysql 중지 /usr/local/mysql/bin/mysqladmin -uroot -p shutdown rm mysql 링크걸어주고 ln -s /usr/local/mysql_5.6.15 /usr/local/mysql 동기화 rsync -av --delete /usr/local/mysql_4.1.22/data/ /usr/local/mysql_5.6.15/data/ my.cnf 설정 변경 [root@localhost local]# cat /etc/my.cnf [mysqld] datadir=/usr/local/mysql/data old_passwords=0 skip-secure-auth character_set_server = euckr #character_set_server = utf8 init_connect='SET collation_connection = euckr_general_ci' #init_connect='SET collation_connection = utf8_general_ci' default-storage-engine = myisam sql_mode='' log-bin expire_logs_days = 5 server-id = 10 #read_only slave-skip-errors = all explicit_defaults_for_timestamp = TRUE innodb_data_home_dir = /usr/local/mysql/data innodb_data_file_path = ibdata1:10M;ibdata2:10M:autoextend innodb_log_group_home_dir=/usr/local/mysql/data #innodb_log_arch_dir=/usr/local/mysql/data innodb_buffer_pool_size = 20G innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 50 innodb_thread_concurrency=12 key_buffer_size = 256M max_allowed_packet = 100M table_open_cache = 2048 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache = 2 query_cache_size = 32M thread_concurrency = 24 slow_query_log = 1 slow_query_log_file = /usr/local/mysql/data/slow_query.log long_query_time = 5 connect_timeout = 10 max_connections = 1024 skip-name-resolve binlog_cache_size = 1M max_binlog_size = 512M max_heap_table_size=32M query_cache_type=1 tmp_table_size=32M query_cache_size = 128M query_cache_limit = 7M [client] port=3306 socket = /tmp/mysql.sock #character-set-client = euckr default-character-set=euckr [mysqld_safe] pid-file=/usr/local/mysql/data/mysqld.pid [mysqldump] quick max_allowed_packet = 256M [mysql] no-auto-rehash [ndbd] # If you are running a MySQL Cluster storage daemon (ndbd) on this machine, # adjust its connection to the management daemon here. # Note: ndbd init script requires this to include nodeid! connect-string="nodeid=2;host=localhost:1186" [ndb_mgm] # connection string for MySQL Cluster management tool connect-string="host=localhost:1186" [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout --------------------------------------------------- 패스워드 무시하고 db 접근하도록 프로세스 실행. /usr/local/mysql/bin/mysqld_safe --skip-grant & 에러가 확인된것이다. 간단하게 처리할수있다. 찾아보다가 . ib_data 관련 에러 로그가 나타나게 되면. 해당 파일들을 지우고. 다시 시작 하면 됨. 성공적으로 에러 코드를 해결한 상태에서 mysql 서비스가 정상적으로 동작한다면 아래 진행상황을 따라가도록. db접속후 4.0 때에는 old_password 를 사용했지만, 5.5.x 이상부터는 사용하지 않음. 그래서 패스워드를 별도로 변경을 해줘야 됨. mysql -uroot -p use mysql update user set password=password(‘pass’) where User = ‘root’ and Host = ‘localhost’ flush privileges; 각 db 테이블 검사를 통해 적용 시킨다 /usr/local/mysql/bin/mysqlcheck -uroot -p --check-upgrade --all-databases --auto-repair /usr/local/mysql/bin/mysql_upgrade -uroot -p /usr/local/mysql/bin/mysqladmin -uroot -p shutdown service mysqld start 정상적으로 서비스 올라오면 성공!!! [tip] 작업을 진행할때마다 mysql 디렉토리를 통째로 백업을 해두는 습관을 기르자. 물론 백업할때에는 서비스를 모두 중지 한 후에 백업 진행