MySQL/필드값중 특정 문자열 치환,대체,변환 명령문(예;카테고리값변경

phpMyAdmin에서 sql query 실행 클릭해서 아래 사용

필드내 특정 문자열 치환하는 mysql 명령문;

update 테이블명 set 필드명=REPLACE(필드명,’찾는문자열’,’수정할 문자열’)

예시) g4_write_free 테이블의 ca_name 필드값중에 “추천”이라고 들어간 단어를 “강추”로 일괄치환하고자할때

update g4_write_free set ca_name=REPLACE(ca_name,’추천’,’강추’)

제목 일괄 변경시,
update g4_write_hsuam set wr_subject=REPLACE(wr_subject,’수암 몸짱 만들기’,’수암’)

delete나 truncate 같은 명령으로 데이터를 지웠을때 복구하는 방법입니다.

일단 DB 디렉토리로 이동한다.
# cd /usr/local/mysql/data

바이너리 파일을 소스파일로 변환한다.
# ../mysqlbinlog leopit-bin.002 > rescue.sql

필요한 구문만 가져온다.
# grep “insert” rescue.sql > rescue1.sql

이렇게 하시면 rescue1.sql은 지금까지의 인서트구문이 다 들어옵니다.
여기서 필요없는 부분은 지우세요

이제 최종적으로 DB에 삽입만 하시면 됩니다.
# mysql -u 계정아이디 -p DB이름 < rescue1.sql

출처 http://blog.naver.com/saltdream/70001590232

[mysqld]
datadir = /data/mysql
socket = /var/lib/mysql/mysql.sock
user=mysql

init_connect=SET collation_connection = utf8_general_ci
init_connect=SET NAMES utf8
character-set-server = utf8
collation-server = utf8_general_ci

big-tables
default-storage-engine = InnoDB       #
기본엔진설정

skip-host-cache
skip-name-resolve                   #
DNS 검색비활성
skip-external-locking              # 외부(TCP/IP) 잠금비활성

max_connections = 500
table_cache = 256
wait_timeout = 50

## General 로그를 사용하려면 아래 설정은 그대로 유지하고
## MySQL 서버에 로그인한  "SET GLOBALgeneral_log=1″ 명령으로 활성화
general_log = 1        # 0=제네럴로그 비활성
general_log_file = /var/log/mysql/general_query.log  # 제네럴로그 파일경로

log_slow_admin_statements         # DDL쿼리도 슬로우 쿼리에 기록

slow-query-log = 1                # 슬로우 쿼리로그 활성화
long_query_time = 1               # 이 변수값보다 쿼리처리가 길게 걸린다면 에러로그에 기록
slow_query_log_file = /var/log/mysql/slow_query.log  # 슬로우 쿼리 로그파일 경로

## MySQL 스케줄러를 사용하려면 아래 event-scheduler 옵션을 ON으로 변경

#event-scheduler = OFF            # 이벤트 비활성
sysdate-is-now                    # 함수 sysdate() now() 동일하게 처리

back_log = 100                    # 동시접속시 대기시킬수있는 커넥션 갯수
max_connections = 300             # 최대 클라이언트 연결 갯수
max_connect_errors = 999999
thread_cache_size = 50            #
다쓴 쓰레드를 스레드풀에 저장할 갯수
table_open_cache = 400            # 각 쓰레드별 오픈할 테이블수

wait_timeout = 28800              # 커넥션 최대 대기시간()

max_allowed_packet = 32M          # 요청된 쿼리의 최대길이의 
max_heap_table_size = 32M         # MEMORY 테이블의 최대크기
tmp_table_size = 512K             # 메모리 내의 임시테이블 크기  초과시 디스크에 저장

# 2012.09.13, xCode, ref http://dev.kthcorp.com/2011/07/01/mysql-innodb-storage-engine-benchmark/
#sort_buffer_size = 128K          #
정렬에 필요한 버퍼의 크기 ORDER BY 또는 GROUP BY 연산 속도와관련
#join_buffer_size = 128K          # 조인이 테이블을 풀스캔 하기위해 사용하는 버퍼크기
#read_buffer_size = 128K          # 테이블 스캔에 필요한 버퍼크기
#read_rnd_buffer_size = 128K      # 디스크 검색을 피하기위한 랜덤 읽기 버퍼크기
sort_buffer_size = 512K           # 정렬에 필요한 버퍼의 크기 ORDER BY 또는 GROUP BY 연산 속도와 관련
join_buffer_size = 6K             # 조인이 테이블을 풀스캔 하기위해 사용하는버퍼크기
read_buffer_size = 64K            # 테이블 스캔에 필요한 버퍼크기
read_rnd_buffer_size = 256K       # 디스크 검색을 피하기위한 랜덤 읽기 버퍼크기

query_cache_size = 32M            # 쿼리 결과를 캐싱라기 위해 할당된 메모리크기
query_cache_limit = 2M            # 이  변수 값보다 큰 값은 캐싱이 안됨

group_concat_max_len = 1024       # GROUP_CONCAT()함수  사용시 컬럼값 최대크기

# 2012.09.13, xCode, 1 chip x 8 core
thread_concurrency = 2            #
쓰레스 갯수

## 마스터 MySQL 서버에서 "레코드 기반 복제"를 사용할 대는 READ-COMMIT 사용가능
## 복제에 참여하지 않는 MySQL 서버에서는 READ-COMMIT 사용 가능 

## 그 외에는 반드시 REPEATABLE-READ로 사용 

transaction-isolation = REPEATABLE-READ   # isolation 레벨 [READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE]

 

[innoDB]
innodb_file_per_table = 1      #
테이블 단위로 테이블스페이스 할당, 활성시 확장자 .ibd 파일이생성됨
innodb_data_home_dir = /data/mysql    # innodb 홈디렉터리 경로
innodb_data_file_path = ibdata1:256M:autoextend      # 파일명 : 초기용량 : 자동증가 : 최대사이즈
innodb_autoextend_increment = 100                    #테이블 스페이스 자동 확장시 크기
innodb_log_group_home_dir = /data/mysql
innodb_log_arch_dir = /data/mysql         #
로그 디렉터리 정보
innodb_buffer_pool_size = 10G             # 데이터와 인덱스를 캐시하기 위해 사용하는 메모리버퍼크기
# innodb에서 사용할 메모리 양으로 전체 메모리의 50~80% 정도로 설정
innodb_additional_mem_pool_size = 16M     # 데이터 디렉토리 정보와 내부 데이타 구조를 저장하는 메모리 풀의 크기
innodb_log_buffer_size = 16M              # Redo 로그 버퍼크기

로그 버퍼 사이즈로 성능에 맞춰 로그를 기록하는 경우 크게 설정
innodb_log_file_size = 64M                #로그  파일 사이즈로 버퍼풀 사이즈의 25% 정도로 설정
innodb_flush_log_at_trx_commit = 2        # 커밋 로그 옵션으로 성능 최적화로 1분마다 저장되도록 2설정
# 1=트랜젝션 실행할때마다 로그 파일에 기록되고 디스크 플러시가 실행
innodb_support_xa = OFF                   # 트렌젝션 two-phase commit 지원, 디스크  플러시 횟수를 줄여 성능항상
# 분산데이터 기능 (2-PhaseCommit) 

# 2012.09.13, xCode, 1 chip x 8 core
#innodb_thread_concurrency = 0            # InooDB
내에 쓰레드 갯수, 변수 0은 쓰레드간 동시성 비활성화
innodb_lock_wait_timeout = 20             # 롤백이 진행되기 전에 LOCK을 대기하는 시간()
innodb_force_recovery = 0                 #
크래시 복구 모드 설정
innodb_flush_method = O_DSYNC             # 성능을 위해 메모리에서 직접 액세스 하도록 설정

innodb_purge_threads = 1
innodb-read-io-threads = 2
innodb-write-io-threads = 2
innodb_thread_concurrency = 6
innodb-buffer-pool-instance = 3
# O_DIRECT=
운영체제의 버퍼를 사용 않고 IO 실행, RAID 컨트롤러(캐시메모리 장착된)가 없거나 SAN 사용시 O_DIRECT를 사용 하지 않음
innodb_doublewrite = 0                    # 이중 쓰기 버퍼 비활성
innodb_sync_spin_loops = 20               # 쓰레드가 지연되기 전에 (suspended) 풀어 주기 위해 InnoDB 뮤텍스 (mutex)를 기다리는 쓰레드의  대기 시간
innodb_table_locks = 1                    # LOCK TABLES AUTOCOMMIT=0경우에, InnoDB로 하여금 내부적으로 테이블을 잠금
innodb_thread_sleep_delay = 1000          # InnoDB 큐를 조이닝 (joining)하기 전에 InnoDB 쓰레드가 일시 정지 (sleep)하는 시간
innodb_max_purge_lag = 0                  # 퍼지 연산 (purge operation)이 래깅 (lagging)될  INSERT, UPDATE DELETE 연산을 지연 시키는 방법을 제어, 디폴트값 0일시 지연
innodb_commit_concurrency = 0             # 동시에 실행되는 쓰레드의 숫자. 이 값이 0이 되면 동시성 제어 (concurrency control)가 비활성화
innodb_concurrency_tickets = 500          

# InnoDB에 동시에 들어갈 수 있는 쓰레드의 숫자는 innodb_thread_concurrency 변수로 알아볼 수가 있다. 여러 개의 쓰레드가 이미  컨커런시 한계에 도달하였다면, 하나의 쓰레드만이 큐에 들어갈 수 있다. 하나이 쓰레드가 InnoDB에 들어가게 되면, innodb_concurrency_tickets의 값과 일치하는 "자유 티켓"의 숫자가 주어지고, 쓰레드가 자신의 티켓을 사용하기 전 까지는 자유 게 InnoDB에 들어가고 나올 수가 있다. 이런 후에는, 쓰레드는 다시금 일관성 검사를 하고 InnoDB에 다시 들어가려고 시도 게 된다

[mysql]
default-character-set=utf8
show-warnings                             #
경고 발생시 메세지 자동 출력
prompt=\u@\h:\d\_\R:\m:\\s>               # SQL 프롬프트 설정
#pager="less -n -i -F -X -E"              # 데이터  출력시 페이징처리
#no-auto-rehash                           # 자동 완성 기능 비활성화
#safe-updates                             # 데이터 변경 또는 삭제시 wherer구문 필수입력

[myisam]
## InnoDB
사용하지 않고 MyISAM만 사용한다면 key_buffer_size 4GB까지 설정
key_buffer_size = 32M                     # 인텍스 캐시 크기
bulk_insert_buffer_size = 32M             # 대량 삽입에 사용된 트리 캐시의 크기, 쓰레드당 각각 할당됨
myisam_sort_buffer_size = 1M              # 인덱스 정렬시 할당되는 버퍼의 크기

myisam_max_sort_file_size = 2G            # 인덱스 재 생성시 사용할 임시 파일의 최대 크기

myisam_repair_threads = 1                 # 정렬 복구시 사용될 쓰레드 갯수
myisam_recover                            # MyISAM 테이블을 열었을시 테이블 자동복구
ft_min_word_len = 3                       # 하나의 FULLTEXT 인덱스에 포함 되는 단어의 최소 길이 ,
#
변경후 REPAIR TABLE tbl_name QUICK 으로 재 구축해야함 

[mysqld_safe]

log-warnings = 1                          # 에러로그에 경고메세지 기록
log-error=/var/log/mysqld.log             # 에러로그 파일경로\

pid-file=/var/run/mysqld/mysqld.pid       # 프로세스 ID 파일경로 

# 2012.09.13, xCode

tcmalloc on

[ndbd]

connect-string="nodeid=2;host=localhost:1186"

[ndb_mgm]

connect-string="host=localhost:1186"

출처: <http://bluebreeze.co.kr/608>

 

 

현재 사용 중인 옵션 mysql 5.6

 

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]

# generic configuration options
port = 3306
socket = /var/lib/mysql/mysql.sock

back_log = 50

max_connections = 1000
max_connect_errors = 1000000
max_allowed_packet = 16M
binlog_cache_size = 1M

# HEAP TABLE
max_heap_table_size = 128M
tmp_table_size = 128M

sort_buffer_size = 8M
join_buffer_size = 8M

thread_cache_size = 8

thread_concurrency = 4

#Query Cache 상태와 유지관리
#FLUSH QUERY CACHE
#FLUSH QUERY CACHE는 Query Cache의 메모리를 조각모음해서 메모리 사용 효율을 높일때 사용 한다.
#FLUSH TABLES
#FLUSH TABLES는 Query Cache를 비운다.
#RESET QUERY CACHE
#RESET QUERY CACHE는 Query Cache의 캐시된 모든 쿼리결과를 제거 한다.
#
# 0(OFF) : results를 캐쉬하거나 retrieve하지 않음.
# 1(ON) : select sql_no_cache.. 쿼리를 제외한 모든 results를 캐쉬
# 2(DEMAND): select sql_cache… 쿼리만 캐쉬
query_cache_type = 1
# DB 실제 사용량에 비례해서 운영하면서 조절하면 됨
query_cache_size = 64M
# 설정저장가능한 최대 results크기(디폴트:1M) 보통 query_cache_size의 1~10%정도 설정
query_cache_limit = 6M

ft_min_word_len = 4

#default_table_type = MYISAM

thread_stack = 192K

transaction_isolation = REPEATABLE-READ

log-bin=mysql-bin

slow_query_log_file = /var/log/mysql/slow_query.log
long_query_time = 2

#log_long_format

server-id = 1
#*** MyISAM Specific options
key_buffer_size = 32M
#read_buffer_size = 2M
#read_rnd_buffer_size = 16M
#bulk_insert_buffer_size = 64M
#myisam_sort_buffer_size = 128M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
#myisam_repair_threads = 1
#
#myisam_recover

#skip-bdb

# *** INNODB Specific options ***
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 321M
# Innodb 사용시 5.x 부터 사용 가능한 테이블당 파일 용량 조절
#innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_per_table
innodb_file_io_threads = 4
innodb_thread_concurrency = 4
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
#innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 30
explicit_defaults_for_timestamp
[mysqldump]
# Do not buffer the whole result set in memory before writing it to
# file. Required for dumping very large tables
quick

max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Only allow UPDATEs and DELETEs that use keys.
#safe-updates

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
# Increase the amount of open files allowed per process. Warning: Make
# sure you have set the global system limit high enough! The high value
# is required for a large number of opened tables
open-files-limit = 8192

Mysql 4.0.x

  • not null 형태에 null이 저장될 수 있다.
  • Innodb 안정적이지 못함.
  • 패스워드를 hash code 형태로 저장

     
     

Mysql 5.0.x

  • 지정된 케릭터셋, 길이 체크 기능이 강화
  • Innodb의 완벽한 지원
  • Date type 캐스트 형식 변경
    • 필드가 datatime이 아닌 date 형태로 지정되어 있을 경우 시간 오차 발생
  • 패스워드 저장 방식이 hash code X

     
     

참고 페이지 : http://blog.naver.com/tykim00/90037058194


 
 

PHP 버전별 차이

Php 3 – 4

  • 확장된 API 모듈
  • 멀티쓰레드 웹 서버를 지원하는 통일된 웹서버 인터페이스
  • 고유의 HTTP 세션 지원

     
     

PHP 4 – 5

  • Zend 엔진 기반의 OOP 모델

 
 

 
 

 
 

MySQL Replication

By Joseph JangVieweron May 20, 2008 8:31 AM | PermalinkViewer | Comments (0)Viewer | TrackBacks (0)Viewer

MySQL ReplicationViewer은 MySQL이 기본적으로 지원하는 데이터 복제 방식입니다.

MySQL Replication은 read-write가 가능한 Master와 read-only인 Slave로 구성되는데, Master는 쿼리 로그에 해당하는 binary log를 남기고, Slave는 asynchronous하게 이를 가져가서 반영하는 역할을 합니다. 설정도 간단해, 1-3줄 정도의 설정만 해주면 됩니다. 간단한 동작 방식에 기초해서 Multi-slave, Multi-master, Replication chaining 등의 구성 등이 가능합니다.

MySQL Replication의 문제점들

Master와 Slave가 최소한의 정보만 공유하는 단순한 방식이 장점인 동시에 단점으로 작용합니다.

  1. Master-Slave pair 관리:서버들이 많아질 경우, Master와 Slave의 짝을 관리하는 것이 쉽지는 않습니다. Master와 Slave의 짝을 다른 어디선가 관리해주어야 합니다.
  2. 실패 상황에서의 복구:당연히 자동으로 복구한다든가 하는 기능을 MySQL이 제공하지는 않습니다. Master가 실패했을 경우, Slave를 Master를 바꾼다든가, Slave의 데이터를 Master로 복사해준다든가 하는 작업은 순전히 수작업이 됩니다. Slave가 실패했을 때도, 마찬가지 입니다. Slave의 일시적인 오류라면, Slave는 자신이 반영한 로그의 위치를 기억하고 있긴 하지만, 이에 따른 복구도 사람 손이 갑니다.
  3. binary log의 관리: Master는 Slave에 대해서 don’t care이므로, 언제 binary log를 삭제할 지는 주기 등을 사용합니다. 물론 삭제하는 command가 있지만, Slave들의 정보를 반영해서 자동으로 삭제하는 기능은 없습니다.
  4. asynchronicity: Slave는 Master와 같은 데이터를 가지고 있다고 보장할 수 없으므로, Slave가 Master의 쿼리 처리량을 따라가지 못하게 되면, consistency 문제가 발생할 소지가 있습니다. 따라서, 애플리케이션에서 이러한 점을 신경써주어야 합니다. 이러한 점들 때문에, MySQL Replication을 백업으로 생각하지 말라는 조언들이 등장합니다.

    1, 2, 3번의 경우에는 도구를 이용해서 극복할 수 있습니다. 1번, 2번에 대해서는 어느 정도 도구를 만들어 쓰고 있는데, 인터페이스를 웹 인터페이스로 개선하고 좀 더 자동화할 필요가 있습니다. MaakitViewer이라든가 MySQL Replication ManagerViewer 등을 참고해볼만 합니다.

    4번의 문제에 대해서는 MySQL Replication이 아닌 대안을 찾는 것이 옳을지도 모르겠습니다. 구글에서는 semi-sync 모드를 위한 패치를Viewer 내놓기도 했습니다.

    MySQL Replication의 대안들

    일반적으로 MySQL Replication을 얘기할 때는 다음과 같은 대안들을 함께 얘기합니다.

  • MySQL Cluster: MySQL이 직접 지원하는 것 중에는 MySQL ClusterViewer가 있지만, 아직은 메모리 기반 엔진인 NDB만 지원하기 때문에 메모리의 크기에 제약을 받습니다.
  • DRDB: 디바이스 수준에서 디스크를 분산하는 DRDB를 MySQL과 함께 사용합니다. MySQL Replication보다 failure에 안전한 것은 아니지만, sync가 어긋난 상태는 최소화Viewer됩니다. 하지만, failover node (i.e. slave)는 MySQL 쿼리를 처리할 수 없다는 크나큰 제약이 있습니다. 백업의 용도로는 적절하리라고 생각합니다.

    예전부터 관심을 두고 있던 SequoiaViewer와 같은 데이터베이스 클러스터링 솔루션도 한번 써보고 싶고, 최근에 화제가 되고 있는 MySQL ProxyViewer 와 같은 Proxy들(SQL RelayViewer, dpmViewer, Spock ProxyViewer)을 사용해서 Replication을 잘할 수 없을까 생각이 들기도 하는데, 당장은 시간이 없다는 변명을 하렵니다.

●?Who’s 라미아

 
 

원본 위치 <http://www.lamia.kr/entertainment/1673>

 
 

 
 

 
 

mysql replication에서 특정한 position으로 건너뛰고 싶을때
┗ DB

replication서비스를 진행하다가 보면

replication이 아래와 같은 이유등으로 오류가 날 경우가 있습니다.

이때의 데이터가 필요없는 부분이라면 그냥 건너뛰게 하면 되는데요

건너뛰게 하는 방법은 아래와 같습니다.

 
 

slave서버에서 멈춘 위치 확인(Exec_master_log_pos: 1033756136)

mysql> show slave status\G

 
 

master서버에서 멈춘 위치근처에 어떤 쿼리들이 있는지 확인

mysql> show binlog events in ‘MASTERSERVER-bin.012’ from 1033756136 limit 3\G

 
 

오류가 발생한 쿼리는 필요없으므로 다음 쿼리로 이동

mysql> slave stop;

mysql> change master to master_log_file=’MASTERSERVER-bin.012′, master_log_pos=1033756434;

mysql> slave start;

mysql> show slave status\G

 
 

 
 

———————상세한 내용————————————–

 
 

mysql> show slave status\G

*************************** 1. row ***************************

Master_Host: 192.168.*.*

Master_User: userid

Master_Port: 1111

Connect_retry: 60

Master_Log_File: MASTERSERVER-bin.012

Read_Master_Log_Pos: 1049058337

Relay_Log_File: MEMOINFOS2-relay-bin.011

Relay_Log_Pos: 1033731265

Relay_Master_Log_File: MASTERSERVER-bin.012

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_do_db:

Replicate_ignore_db:

Last_errno: 1062

Last_error: Error ‘Duplicate entry ‘18462’ for key 1′ on query ‘INSERT INTO dbaaa.TBLEVENT_0818_RECEIVE (EVENTKEY, seq, USERID, SENDERseq, CREATEDATE, UPDATEDATE) VALUES (xxx, 111, ‘222@aaa.com’, ‘4337665’, NOW(), NOW())’. Default database: ‘INFODB’

Skip_counter: 0

Exec_master_log_pos: 1033756136

Relay_log_space: 1049033466

1 row in set (0.00 sec)

 
 

master에서 Exec_master_log_pos에 나타난 숫자 뒤에 어떤 이벤트 로그가 있는지 확인

 
 

mysql> show binlog events in ‘MASTERSERVER-bin.012’ from 1033756136 limit 3\G

*************************** 1. row ***************************

Log_name: MASTERSERVER-bin.012

Pos: 1033756136

Event_type: Intvar

Server_id: 1

Orig_log_pos: 1033756136

Info: INSERT_ID=18462

*************************** 2. row ***************************

Log_name: MASTERSERVER-bin.012

Pos: 1033756164

Event_type: Query

Server_id: 1

Orig_log_pos: 1033756164

Info: use INFODB; INSERT INTO INFODB.TBLEVENT_0818_RECEIVE (EVENTKEY, seq, USERID, SENDERCMN, CREATEDATE, UPDATEDATE) VALUES (xxx, 111, ‘222@aaa.com’, ‘4337665’, NOW(), NOW())

*************************** 3. row ***************************

Log_name: MASTERSERVER-bin.012

Pos: 1033756434

Event_type: Query

Server_id: 1

Orig_log_pos: 1033756434

Info: use INFODB; UPDATE dbaaa.aaa SET LOGOUTDATE = NOW(), UPDATEDATE = NOW() WHERE seq = ‘1’

3 rows in set (0.00 sec)

 
 

slave stop;

change master to master_log_file=’MASTERSERVER-bin.012′, master_log_pos=1033756434;

slave start;

show slave status\G

 
 

출처 : Tong – 항상 열심히~님의 MySQL통

 
 

 
 

=========================================================================================

요즘은 MySQL Replication 기능을 이용해서 DB System을 구축하는 경우가 많다. 또한 MySQL DB를 사용하면서 백업은 보통 Replication Slave 단에 있는 백업 Server에서 별도로 받게 된다.

백업 서버에서 Dump로 받게 된 Data는 DML 쿼리로 된 text data이며, 이 데이터를 이용해서 신규 장비를 설치하던가, 아니면 기존 장비에 문제가 생겼을 시 복구를 하게 된다.

 
 


보통의 MySQL Replication 구성

그런데 복구를 하더라도 Replication이란 문제로 인해 Master DB와 싱크를 맞추는 문제가 발생하게 된다.

따라서 백업 서버에서 백업을 받게 되는 경우, Master DB의 binary log의 포지션을 알아야지만 된다.

이럴 경우에는 다음과 같은 절차를 따라서 Dump를 받으면 된다.

 
 

/usr/local/mysql/bin/mysqldump -uroot -p –master-data=2 > /data/…/backup.sql

 
 

이 명령어 중에서 “–master-data=2” 옵션을 주게 되면 덤프 시점의 binary log의 포지션 정보가 같이 Dump 되게 된다.

Dump된 파일을 에디터로 열어보면 다음과 같은 내용이 나온다.

 
 

CHANGE MASTER TO MASTER_LOG_FILE=’binary파일’, MASTER_LOG_POS=포지션번호;

위 말은 binary파일의 포지션 번호 시점에서 백입이 이루어 졌다는 의미이다.

물론 binary 파일과 포지션은 복구하기 위한 중요한 내용이 되겠지만, 위와 같은 구성에서 복구를 하기 위해서는 조금 애매 보호한 감이 있다.

가령, Backup DB에서 Dump받은 파일을 Slave DB를 복구하기 위해 사용한다던가, 아니면 신규 Slave 장비가 와서 새로운 시스템을 구축할 시에는 Backup DB의 binary 파일명과 포지션은 별로 소용이 없기 때문이다.

따라서 이럴 때에는 다음과 같은 절차로 Master DB의 binary 파일과 포지션을 알아낼 수 있다.

 
 

1. Backup DB에서 포지션이 속한 바이너리 로그를 sql 파일로 export 한다. 사용 툴은 mysqlbinlog이다.

 
 

/usr/local/mysql/bin/mysqlbinlog –start-datetime=”2008-12-15 02:00:00″–stop-datetime=’2008-12-15 02:30:00′ /data/mysql_data/binary파일 > binlog.sql

2. Master DB에서도 위 방법대로 바이너리 로그를 sql 파일로 export 한다.

 
 

/usr/local/mysql/bin/mysqlbinlog –start-datetime=”2008-12-15 02:00:00″–stop-datetime=’2008-12-15 02:30:00′ /data/mysql_data/binary파일 > binlog.sql

3. Backup DB에서 export한 sql 파일을 에디터로 열어서 확인해 본다.

 
 

# at 576856551

#060824 2:18:30 server id 1 log_pos 576856551 Intvar

SET INSERT_ID=136;

# at 576856579

#060824 2:18:30 server id 1 log_pos 576856579 Query thread_id=16587969 exec_time=16157 error_code=0

SET TIMESTAMP=1156353510;

 
 


 
 

# at 576856919

 
 

최초에 Backup DB에서 Dump받은 파일에 기록된 포지션 정보를 찾는다.

 
 

4. Master DB에서 export 한 sql 파일을 에디터로 열어서 동일한 포지션을 찾는다.

 
 

# at 576375776

#060824 2:18:30 server id 1 log_pos 576375776 Intvar

SET INSERT_ID=136;

# at 576375804

#060824 2:18:30 server id 1 log_pos 576375804 Query thread_id=16587969 exec_time=0 error_code=0

SET TIMESTAMP=1156353510;

 
 

 
 

Backup DB에서 해당 포지션에 기록된 TIMESTAMP값과 동일한 정보를 Master DB에서 export한 sql 파일에서 찾는다.

위의 경우에는 576375776이 포지션 번호가 된다.

 
 

5. 최초에 Dump 받은 Dump Data의 바이너리 파일과 포지션을 수정해 준다.

 
 

CHANGE MASTER TO MASTER_LOG_FILE=’마스터DB의 binary파일’, MASTER_LOG_POS=576375776;

위와 같이 되었다면, Dump 받은 파일은 언제라도 Master DB에 붙여서 replication을 동기화 시킬 수 있는 준비가 된 상태이다.

만일 새로운 장비를 들여와서 Dump Data로 복구를 했다면, 위 명령어를 내린 후 slave를 start 시키면, Master DB의 해당 포지션 부터 sync를 맞춰가기 시작한다.

Master DB의 포지션을 모두 다 따라갔다고 판단되면 Slave DB를 서비스에 투입시키면 끝이다.

 
 

원본 위치 <http://www.4te.co.kr/532>

 
 

 
 

 
 

 
 

 
 

mysql replication 설정, master-slave 설정하기/장애대응

 
 

원본 위치 <http://blog.1day1.org/454>

 
 

 
 

mysql 리플리케이션 설정은 어렵지 않군.

장애대응이 어려운것 같다.

 
 

# 기본 mysql replication 설정.(마스터-슬레이브)

1. mysql config 설정

master , slave 각각 config 파일에 replication 설정을 해준다.

우분투의 경우 /etc/mysql/conf.d 에 replication_slave.cnf (파일명은 임의로 )

/etc/mysql/my.cnf 의 마지막줄에 이렇게 되어 있어서 불러오게 된다

!includedir /etc/mysql/conf.d/

my.cnf 의 [mysqld] 탭에 직접추가해줘도 된다.

 
 

내용은 이런식이다. (슬레이브쪽)

[mysqld]

server-id = 2

master-host = master

master-user = replication

master-password = slave

replicate-do-db = master_db

replicate-do-db = other_db

마스터쪽은 다음과 같다.

[mysqld]

server-id = 1

log_bin = /var/log/mysql/mysql-bin.log

binlog_do_db = master_db

binlog_do_db = other_db

binlog_ignore_db = mysql

binlog_ignore_db = information_schema

설정만 보면 이해할 수 있을 것이다.

binlog_do_db 등을 각자의 서버설정에 맞게 바꾸어 주면 된다.

 
 

2. replication 접근 권한설정

replication 은 slave 쪽에서 master 의 자료(bin_log)를 참조해서 데이터를 가져가는 것이다.

그래서 슬레이브쪽에서 마스터에 접근할 수 있어야 한다.

 
 

그 명령은 다음과 같다.(마스터쪽에서 mysql 명령을 내려준다)

mysql> grant replication slave on *.* to ‘replication‘@192.168.0.1 identified by ‘slave‘;

진한부분이 conf 에서 설정한 master-user / master-host / master-password 에 해당한다.

IP 주소대신 /etc/hosts 에 설정한 호스트명을 적어줘도 된다. ( master-host = master 같은..)

 
 

3. 사용 및 장애대응

작동 중지/실행

start slave ;

stop slave ;

슬레이브 상태 보기

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: master

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000018

Read_Master_Log_Pos: 958292

Relay_Log_File: slave-relay-bin.000271

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000018

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: master_db

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 958292

Relay_Log_Space: 556

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

마스터의 상태를 보려면

mysql> show master status\G

*************************** 1. row ***************************

File: mysql-bin.000018

Position: 958292

Binlog_Do_DB: master_db

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

진하게 표시한 position 이 일치하는지 확인하면 된다.

 
 

slave 의 position 값이 일치하지 않는경우.

다음처럼 해준다.

mysql > stop slave;

mysql > reset slave;

mysql > start slave;

cron 등으로 주기적으로 확인해주도록 한다.

 
 

 
 

 
 

Mysql Master-Master Replication

 
 

원본 위치 <http://blog.diainfo.co.kr/textyle/8273>

 
 

Replication 을 사용 함에 있어서 Master-Slave 구조로 쓰는 것이 일반적일 것이다. 그런데 때에 따라 이런 구조를 사용 하지 못하는 경우가 있다. master에는 Insert,update,create,delete 등 db에 변경이 일어나는 Query 는 master만 사용하고 Slave 에서는 select 와 같은 db 에 변경이 없는 것만 사용 해야 한다. 그런데 현재 사용 해야 하는 웹사이트 등이 그렇지 못하다고 가정하자. 이럴 경우 master-slave 는 사용 하지 못한다. 물론 개발자가 해당 소스를 수정 해주면 간단하지만 사정상 할수가 없는 경우도 있다. 그럴 때 master-master 구조를 사용하면 해결이 된다.

 
 

다음은 db가 설치 되어있다는 가정하에 M-M 방식으로 동작하도록 설정하는 방법이다. DB는 2대로 구성하며 IP는 다음과 같다.

 
 

DB-1 : 192.168.11.11

DB-2 : 192.168.11.12

 
 

# my.cnf 파일을 편집기로 열어 다음과 같이 수정

 
 

log-bin

binlog-do-db=TESTDB // Replication 할 DB명

binlog-ignore-db=mysql // Replication 에서 제외할 DB명

 
 

 
 

# mysql 로 접속하여 slave 권한 부여

 
 

mysql> grant replication slave on *.* to replication@192.168.11.12 identified by ‘SLAVE_PASSWORD’;

 
 

# mysql restart

DB-1 시작후 DB-2 의 my.cnf에 replication 설정을 하고 시작을 하면 ” mysqld : unknown variable ‘master-host= ” 와 같은 오류가 난다. 이는 mysql 5.5 이상에서 아래 옵션들이 동작 하지 않는다.

 
 

–master-host

–master-user

–master-password

–master-port

 
 

그리하여 위 옵션은 사용 할수가 없다. 해결책으로 아래 옵션을 사용 한다. 해당 명령어를 실행하고 나면 datadir 에 master.info 가 생성되고 이후로 mysql server를 stop/start 하여도 유지 되니 걱정 하지 말자.

 
 

[DB-1]

mysql > SHOW MASTER STATUS;

+————————+—————–+———————+————————-+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+————————+—————–+———————+————————-+

| mysql-bin.000001 | 107 | TESTDB | mysql |

+————————+—————–+———————+————————-+

 
 

file 이름과 position 을 확인

 
 

[DB-2]

mysql> CHANGE MASTER TO MASTER_HOST=’192.168.11.11′, MASTER_USER=’replication’, MASTER_PASSWORD=’MASTER_PASSWORD’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=107;

mysql > slave start;

 
 

아래 명령어로 올바르게 동작 하는지 확인

mysql > show slave status \G

여기까지가 기본적인 Master-Slave 구조 이다.

Master-Master 구조 ( master1/slave2 – master2/slave1 ) 로 가기 위해서는 추가적인 설정을 더해 주면 된다.

 
 

 
 

[ DB-2 ]

 
 

DB-2 의 my.cnf 파일을 열고 다음을 추가 하도록 하자

 
 

log-bin

binlog-do-db=TESTDB

 
 

# mysql 접속 하여 권한 추가

mysql > grant replication slave on *.* to replication@192.168.11.11 identified by ‘SLAVE_PASSWORD’;

 
 

# mysql restart

 
 

mysql > show master status;

+————————+—————–+———————+————————-+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+————————+—————–+———————+————————-+

| mysql-bin.000006 | 107 | TESTDB | mysql |

+————————+—————–+———————+————————-+

 
 

file 이름과 position 확인

 
 

[ DB-1 ]

mysql > CHANGE MASTER TO MASTER_HOST=’192.168.11.12′, MASTER_USER=’replication’, MASTER_PASSWORD=’MASTER_PASSWORD’, MASTER_LOG_FILE=’mysql-bin.000006′, MASTER_LOG_POS=107;

mysql > slave start;

 
 

오류 없이 동작 하는지 확인.

mysql > show slave status \G

여기까지 하면 Master-Master 구성이 끝이난다. 설정을 하면서 꼭 Master-Slave 구성후에 show master status 와 show slae status 로 log_file 과 postion 이 일치하는지 확인하고 error 가 없는지도 확인하자.

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

Cent 5,6 버전에서 mysql 3.x 혹은 4.x 설치 하고자 한다면 make 과정 중 아래와 같은 에러가 발생 한다면..

mysql.cc error: expected primary-expression before ‘?’ token gcc

gcc 컴파일러의 버전이 높아서 에러가 발생하는 것임으로 다운그레이드 해줘야 한다.

yum remove gcc*

yum install compat-*

위와 같이 compat 패키지 설치시 gcc-34 가 설치 되는데 이를 링크 해줘서 gcc로 사용될 수 있게끔 해준다.
ln -s /usr/bin/gcc-34 /usr/bin/gcc
ln -s /usr/bin/g++34 /usr/bin/g++

다시 make 진행 해보면 정상 처리 된다.

제로보드의 mysql Database 이전 후 로그인이 이루어지지 않는 이런 황당한 일들이..

어찌 되었던 해결 해야 하지 않겠습니까..? 이대로 로그인 안되게 둘 순 없으니..

 

작업 대상 환경 : Cent 5.8 / apache 2.2.X  / php 5.3.x / mysql 5.2.x

기존 정보 : redhat 7.3 / apache 1.3.x / mysql 3.2.x / php 4.1.x / zero board 4

 

6년이 넘은 서버를 대대적인 업그레이드를 진행 해야 했다. 소스 수정없이..!!

 

이렇게 버전 업그레이드시에 가장 에러가 많이 발생하는 부분은 php다.

mysql 또한 속을 썩이는 경우도 간혹 있지만, 아래 포스팅 참고하면 어렵지 않게 마이그레이션이 가능 하다.

Mysql 3.x to 5.x migration

 

php는 버전이 달라지면서, 없어지거나 이름이 바뀌는 함수들이 존재 하기 때문에 소스 수정이 필요하거나,

php.ini 옵션들을 이용해 기존 그대로 사용할 수도 있다.

 

apache의 경우는 버전이 달라지더라도 설치할 버전이 맞게끔 설정만 해준다면 큰 이상 발생할 우려가 전혀~ 없다.

 

본론으로 들어가자면, 로그인이 이루어지지 않는 원인은?

아이디, 패스워드가 다르거나 member table 내에 회원 정보가 존재하지 않는 경우일 것이다.

 

이전에는 정상적으로 로그인 되던게 안된다면 후자는 아닐 것이고, 전자라는 것인데 패스워드는 바꾼적이 없다.

DB가 이상 동작 하는건가? ?

 

mysql 4.0 이하에서 4.1 이상으로 변경 되면서 패스워드 방식이 변경 되었다.

하여 해당 DB 그대로를 사용하면 패스워드를 다르게 인식하게 되는 문제가 생겨 로그인이 되지 않는 것.

 

서버호스팅, 혹은 단독의 슈퍼유저권한이 존재하는 서버의 경우는 /etc/my.cnf 파일에 옵션 하나 추가 하는 것으로 해결이 가능하다.

 

[rubi] / > # vi /etc/my.cnf

### 중략

[mysqld]

old_password = 1

 

하지만, 웹호스팅 혹은 쉐어드 호스팅의 경우에는 소스상에서 수정 해주어야 한다.

제로보드 홈디렉토리 아래에 login_check.php 파일이 존재 하는데, SQL Query 구문을 수정 해준다.

 

[rubi] / > #  vi login_check.php

$result = mysql_query(“…”)  아래의..  password=password(‘$password’) 부분

=>

password=old_password(‘$password’))

 

위 경우는 서버를 교체 해야 하는데 홈페이지 개발 인력이 없는 경우 제로보드의 큰 수정없이 진행 하는 방법일뿐..

버전 업그레이드를 진행한다면, 제로보드에서 제공하는 마이그레이션 툴을 통해 XE로 업그레이드 하는 것이 보안상 좋다.

 

제로보드 마이그레이션 툴에 대한 설명은 아래 URL 참고.

http://www.xeschool.com/xe/step1_53

EUC-KR로 encoding되어 있는 MySQL 3.x 데이타를 5.x으로 이전해야 할 일이 생겼다. ㅠㅠ 그래, 이왕 이전 하는 거 UTF-8으로 바꾸자 (사실 찾아 보니 이게 더 쉬울 것 같다. ㅋㅋ).
순서는 아래와 같다.

  1. MySQL 3.x로 부터 자료 dumping.
  2. Dump 된 파일 수정
  3. MySQL 5.x로 자료 올리기

척 보면 알겠지만 2번 작업이 이번 일의 핵심이다.
1번 dumping은 아래와 같이 쉽게 할 수 있다.

$ mysqldump -u USER -p DBNAME > DBNAME.dump.sql

 

사용자 계정 및 암호를 백업 하기 위해서는 mysql DB를 백업 해야 하는데, 이 경우 이미 Table이 생성되어 있을 것이다. 이때 아래와 같은 옵션을 줘서 백업 하도록 하자.

 

$mysqldump –add-drop-table -u USER -p mysql > mysql_root.sql

복구 하는 방법은 동일하다.

 

당연히 비밀번호를 입력해 줘야 한다. 이번일에는 이전해야 할 DB가 많아 아래와 같이 스크립트를 짜서 해결했다.

$ while read dbname do echo “Start dumping ${dbname}.” mysqldump -u ROOT_ID -p ${dbname} > ${dbname}.dump.sql done <<DBNAME accounts …… DBNAME

휴… 비밀번호 입력 하느라 힘들었다. 쩝…
다음으로 제일 중요한 dump 파일 수정이다.
일단 dump 파일 중 5.x에서 문제를 일으킬 수 있는 주석 부분을 제거 해 줘야 한다. SQL 문에서 주석은 C와 같이 ‘/* … */’로 쓰는 방법과 함께 주석 앞에 ‘–‘를 쓰는 방법이 있는데 아래 예의 1번과 같이 ‘———————————————————‘로 되어 있는 부분이 5.x에서는 문제를 일으킨다. 따라서, 1번 부분을 제거해 줘야한다. 그 다음에는 5.x에게 자료의 encoding이 EUC-KR임을 알려주기 위한 2번 부분, 또 새롭게 만드는 table들에 UTF-8 형태의 자료를 쓸 수 있도록 해주기 위한 3번 부분을 추가한다 (아래 예에서 제거되는 부분은 붉은 색으로, 추가되는 부분은 파란 색으로 표시하였다). 이때, 2번은 파일 맨 앞에서 한 번 만 추가해 주면 되지만 3번 부분은 매 create 문 마다 해 줘야 하기때문에 vim에서 정규식을 이용한 치환(:%s/\(TYPE=ISAM.*\);/\1 DEFAULT CHARSET=utf8;/)으로 한번에 해결 하였다. ㅋㅋ

SET NAMES euckr; <- 2 — MySQL dump 8.23 — — Host: localhost Database: accounts ——————————————————— <- 1 — Server version 3.23.58
— — Table structure for table a_00002
CREATE TABLE a_00002 ( number smallint(5) unsigned NOT NULL auto_increment, applicant varchar(16) NOT NULL default ”, …… timestamp timestamp(14) NOT NULL, PRIMARY KEY (number) ) TYPE=ISAM PACK_KEYS=1 DEFAULT CHARSET=utf8; <- 3
— — Dumping data for table a_00002 — ……

이제 파일을 수정했다면 3번 과정만이 남았다. 1번과 같이 아래의 명령으로 간단히 수행할 수 있다.

$ mysqladmin -u USER -p create DBNAME <- 1 $ mysql -u USER -p DBNAME < ${dbname}.dump.sql <- 2

위의 1번 명령은 해당 DB를 생성하도록 하는 것이고, 2번이 실제 자료를 MySQL에 넣도록 하는 명령이다.
이제 남은 일은 커피 한잔 마시며 자료가 잘 옮겨 졌는지 확인 하는 일… 음… 잘 됐군… ㅎㅎ

 

출처 : http://lthwkh.blogspot.kr/2009/09/mysql-3x-5x-migration.html

시스템 백업 과정
========================
시나리오: 장애가 발생한 CentOS의 MySQL InnoDB 파일을 이용해서(C 서버), Slackware 상에(S 서버)
복구하고 mysqldump 받기
Table Space: /mydb_db/mydb_sp/
Database: mydb
1) C 서버: 다음과 같은 파일들이 복구 시 필요하다. 아래 위치의 파일들을 백업받는다.
ib_logfile1, ib_logfile0, ibdata1, ibdata2
/mydb_db/mydb_sp/*
table관련 .frm 파일들
/usr/local/mysql/var/mydb/*
2) S 서버: my.cnf 파일의 로그 사이즈를 기존 시스템과 동일하게 한다.
# /etc/rc.d/rc.mysqld stop
# cd /var/lib/mysql/
# mkdir mydb
# cp ~/db/var/mydb/* /var/lib/mysql/mydb/
# chown mysql:root mydb
# chown mysql:mysql mydb/*
2단계까지 하고 재시작하면 에러가 나면서 mysql 을 시작할 수 없다.
3) S 서버: my.cnf 에 다음줄을 추가한다.
<<<
# Uncomment the following if you are using InnoDB tables
innodb_force_recovery = 4
>>>
4) S 서버: 재시작
# /etc/rc.d/rc.mysqld start
5) S 서버: mysqldump 받을 수 있다.
# mysqldump mydb > mydb.2007
시스템 복구 과정
========================
6) C 서버: mysqld 중지
# service mysqld stop
7) C 서버:
# vi /etc/my.cnf
innodb_force_recovery = 4 옵션을 제거한다.
8) C 서버: 기존 Table Space내의 파일들과 frm 관련 파일들을 삭제한다.
# rm /mydb_db/mydb_sp/*
# rm /usr/local/mysql/var/mydb/*
9) C 서버: mysqld 재시작
# service mysqld start
10) C 서버: data import
# mysql mydb < mydb.2007