http://kakaodbe.blogspot.kr/2014/04/pt-online-schema-change-modified-pt.html

pt-online-schema-change_kakao.patch

Percona Toolkit에는 많은 훌륭한 도구들이 있지만, 그 중에서도 pt-online-schema-change는 아마도 대 부분의 사용자들이 경험해보았을 정도로 유용한 툴이다.
하지만 아무리 좋은 도구도 모든 사용자의 요건을 만족하기는 어려우며, pt-online-schema-change도 우리의 요건을 만족하기에 조금은 부족한 부분이 있었다.
우선 급하게 아래와 같이 필수적인 부분들 위주로, 조금 보완해서 pt-online-schema-change 도구를 개선해보았다. 

1. 프라이머리 키 변경과 테이블 파티션을 동시에 수행하는 경우
2. 이미 존재하는 이름의 컬럼의 기본(Default) 값을 변경하면서, 해당 컬럼의 값을 모두 새로운 기본값으로 채우기
3. pt-online-schema-change 도구가 서비스에 미치는 영향을 최소화하기 위해서 청크(Chunk)간 대기(Sleep) 시간 넣기

• --prompt-before-copy
  "--ask-pass"와 같이 별도의 파라미터 값을 필요로 하지 않으며, 이 파라미터가 정의되면 pt-online-schema-change 스크립트가 복사용으로 사용할 테이블을 생성하고 사용자의 확인을 기다리게 된다. 만약 새로운 테이블에 데이터가 복사되기 전에, 새로운 테이블의 구조를 확인한다거나 추가로 변경해야 할 사항이 있다면 이 옵션을 사용하면 된다. 대표적으로 파티션을 생성하는 작업을 위해서 프라이머리 키의 구조를 변경해야 할 때도 있다. 하지만 하나의 ALTER 구문으로는 프라이머리 키를 변경하는 작업과 파티셔닝을 수행하는 작업을 동시에 처리할 수 없다. pt-online-schema-change도구에서도 이 제약은 동일하게 적용된다. 이럴 때에는 --prompt-before-copy 옵션을 활성화하고, pt-online-schema-change가 실행을 잠깐 멈추었을 때 추가로 스키마 변경 작업을 해줄 수 있다.
• --skip-copy-columns
  pt-online-schema-change 도구가 서비스중인 테이블에서 새로운 테이블로 레코드를 복사할 때, 구조 변경 전의 테이블과 구조 변경후의 테이블에서 공통되는 이름의 컬럼은 자동으로 값을 복사하게 된다. 하지만 때로는 이름이 동일한 컬럼이라 하더라도, 기존 테이블의 컬럼 값을 새로운 테이블로 가져오지 말아야 할 때도 있다. 이런 경우에는 --skip-copy-columns 옵션에 무시할 컬럼들을 ","로 구분해서 나열하면 된다.
• --sleep-time-us
  pt-online-schema-change는 기본적으로 MySQL 서버의 시스템 부하 정도를 판단해서 스키마 변경 작업(레코드 복사)의 처리 속도를 조절할 수 있도록 제공하고 있다. 하지만 아주 가벼운 형태의 쿼리가 빈번하게 처리되는 MySQL 서버에서는 이 규칙은 별로 도움이 되지 않는다. 그래서 --sleep-time-us 옵션을 이용하면 pt-onine-schema-change 툴이 청크(chunk) 단위로 복사를 수행하고 나서 일정 시간동인 대기(Sleep)하도록 해서, 서비스용 쿼리들이 거의 지연되지 않고 처리될 수 있도록 할 수 있다. --sleep-time-us 는 마이크로 단위(1/1,000,000)의 초를 파라미터로 받기 때문에, 실제 pt-online-schema-change를 이용하는 경우에는 --sleep-time-us는 1000(1밀리 초) ~ 50000(50 밀리 초) 정도의 값이 적절해 보인다.
• --print-sql
  pt-onine-schema-change 툴은 내부적으로 기존의 테이블은 변경하지 않고, 변경하고자 하는 내용을 포함하는 새로운 테이블을 생성하고 트리거를 이용해서 기존 테이블의 레코드를 새로운 테이블로 복사하는 형태로 처리된다. 이때 pt-onine-schema-change가 생성하는 트리거나 신규 테이블의 구조가 궁금할 때도 있다. 이런 경우에는 --print-sql 옵션을 명시해주면, 생성되는 트리거와 신규 테이블의 구조를 화면에 출력한다. 물론 오리지널 버전의 pt-online-schema-change에도 디버깅 모드로 실행해서 모든 내용을 출력하도록 할 수 있다. 하지만 이는 너무 많은 불필요한 내용들이 출력되므로, 꼭 필요한 내용만 간략히 확인하고자 할 때에는 --print-sql 옵션이 더 도움이 될 것이다.

1) 프라이머리 키 변경과 테이블 파티션을 동시에 수행하는 경우

2) 이미 존재하는 이름의 컬럼의 기본(Default) 값을 변경하면서, 해당 컬럼의 값을 모두 새로운 기본값으로 채우기

3) pt-online-schema-change 도구가 서비스에 미치는 영향을 최소화하기 위해서 청크(Chunk)간 대기(Sleep) 시간 넣기

설치 및 패치

1. Download percona toolkit percona-toolkit-2.2.7-1.noarch.rpm from http://www.percona.com/downloads/percona-toolkit/LATEST/RPM/
2. Install percona toolkit 2.2.7-1
   $ rpm -Uvh percona-toolkit-2.2.7-1.noarch.rpm
3. Patch modified code to original pt-online-schema-change
   $ patch /usr/bin/pt-online-schema-change < pt-online-schema-change_kakao.patch

http://cafe.naver.com/mysqlpg

Consistency.txt

pt-table-checksum.txt

MySQL은 MasterDB / SlaveDB 간

최고의 유연성을 보장한다는 장점이 있는 반면,  이 때문에 다소의 정합성이 깨질 수 있다는 단점을 가지고 있습니다.

이에 정합성이 맞는지 수시로 확인을 해야하는데요, 이와 관련된 내용이 있어 공유드립니다.

출처 : Oracle Press - Effective MySQL Backup and Recovery (p.68~)

=========================================================
Data Consistency : pt-data-checksum
=========================================================

[각 노드에서 percona-toolkit 설치]

sudo su -

yum install -y percona-toolkit

[MasterDB]

sudo su - mysql

cd /usr1/mysql/dba/tools

pt-table-checksum --host localhost --user root --ask-pass --replicate=zzdba.pt_checksum --databases=globaldb,gamedb,logdb --create-replicate-table --no-check-binlog-format --nocheck-replication-filters

------------------------------------------------------------------------------------------------------------
Waiting for the --replicate table to replicate to slavedb-s...
Waiting for the --replicate table to replicate to slavedb-s...
Waiting for the --replicate table to replicate to slavedb-s...

Checksumming globaldb.achievements:  55% 00:24 remain

            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
02-07T18:24:06      0      0  1032199      26       0  37.582 globaldb.achievements
02-07T18:24:06      0      0      670       1       0   0.022 globaldb.achievementsdata
02-07T18:24:20      0      0  1032184      25       0  13.456 globaldb.achievementsreward
02-07T18:24:20      0      0        1       1       0   0.261 globaldb.boss
02-07T18:24:20      0      0       15       1       0   0.009 globaldb.bossattack
02-07T18:24:26      0      0  1582272      10       0   6.235 globaldb.chattingdfield
02-07T18:24:32      0      0  1539837      10       0   6.172 globaldb.chattingnotify
...
# ------------------------------------------------------------------------------------------------------------
# --ask-pass                        : 암호를 입력받을 수 있게 (ask password)
# --replicate=zzdba.pt_checksum     : 체크섬 데이터를 저장할 테이블명
# --create-replicate-table          : 체크섬 테이블이 없다면 만들어서
# --databases=globaldb,gamedb,logdb : 체크할 데이터베이스명
# --no-check-binlog-format          : binlog-format='STATEMENT' 가 아니면 에러가 나는데, 이를 무시하라 (우리는 MIXED 이기 때문에 사용해야함)
# --nocheck-replication-filters     : 복제되지않는 DB들 체크하지 않아도 됨
# ------------------------------------------------------------------------------------------------------------
# Waiting for the --replicate table to replicate to ld-db-s... 
#   : Slave DB 에 체크섬 테이블(zzdba.pt_checksum) 이 만들어지지 않으면 위의 오류 발생
#     만일 2~3번까지도 waiting 된다면 slave node 에 replication 에 문제가 없는지 확인 필요 (slavedb : show slave status\G)
# ------------------------------------------------------------------------------------------------------------

[SlaveDB]

sudo su - mysql

cd /usr1/mysql/dba/tools

mysql -u root -p

SELECT db, tbl, chunk
     , IF(this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 'YES', 'NO') AS diff
     , this_cnt-master_cnt AS rowcount_diff
  FROM zzdba.pt_checksum
 WHERE master_cnt <> this_cnt
    OR master_crc <> this_crc
    OR ISNULL(master_crc ) <> ISNULL(this_crc);

+-----------+-------------------+-------+------+---------------+
| db        | tbl               | chunk | diff | rowcount_diff |
+-----------+-------------------+-------+------+---------------+
| globaldb  | log_ranking_cache |     1 | YES  |            -1 |
+-----------+-------------------+-------+------+---------------+

=========================================================
Object Consistency : md5(), length(), diff -u
=========================================================

sudo su - mysql

mysql -u root -p

use zzdba;

create view dba_checksum_proc
as
select routine_schema,routine_name, routine_type

     , length (routine_definition) as size, md5(routine_definition) as checksum
  from information_schema.routines
 where ROUTINE_SCHEMA not in ('bmt_schema2a','common_schema','ps_helper','test','mysql','information_schema')
 order by routine_schema,routine_name, routine_type;

cd /usr1/mysql/dba/monitor

mysql -uroot -p -h master_host -e "set sql_log_bin=0; select * from zzdba.dba_checksum_proc" > zztmp_object_master.log

mysql -uroot -p -h slave_host  -e "set sql_log_bin=0; select * from zzdba.dba_checksum_proc" > zztmp_object_slave.log

diff -u zztmp_object_master.log zztmp_object_slave.log

=========================================================
Schema Consistency : mysqldump, diff -u
=========================================================

The layman's approach to detecting schema differences is to use the mysqldump utility

and to generate the schema only and compare.

cd /usr1/mysql/dba/monitor

mysqldump --all-databases --no-data --skip-dump-date -uroot -p -h ld-db-m   | sed -e "s/AUTO_INCREMENT=[^\ ] //" > zztmp_schema_master.log

mysqldump --all-databases --no-data --skip-dump-date -uroot -p -h localhost | sed -e "s/AUTO_INCREMENT=[^\ ] //" > zztmp_schema_slave.log

diff -u zztmp_schema_master.log zztmp_schema_slave.log

This process is not ideal as there is no guarantee the output is ordered, and the format does differ between MySQL versions; however,this process can be used to confirm that no schema differences exist.  ^^;

=======================================================================
참고. pt-table-checksum 소스버젼으로 실행하면서 에러 발생시!
=======================================================================
erroe : 02-07T08:43:57 Cannot read response; is Term::ReadKey installed?
        Can't locate Term/ReadKey.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl
        /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at ./pt-table-checksum line 2629.
=======================================================================

yum install perl-CPAN yaml

cpan YAML

perl -MCPAN -e shell

install Term::ReadKey

zzdba_objects_v1.1_20140125.sql

monitoring script 짤 때 사용하면 될 듯.

env.ini 파일 내용

export MySQL_VER=5.6.12

export MySQL_BASE=/usr1/mysql

export MySQL_HOME=$MySQL_BASE/$MySQL_VER/bin

export MySQL_SID=vm21

export MySQL_PORT=10614

export MySQL_SOCKET=/tmp/mysql_sandbox10614.sock

export _DBDBA_UID=msandbox

export _DBDBA_PWD='msandbox'

export _DBBACKUP_UID=msandbox

export _DBBACKUP_PWD='msandbox'

export _DBMON_UID=msandbox

export _DBMON_PWD='msandbox'

.env.ini

chk_all.sh

chk_lock.sh

chk_process.sh

chk_qps.sh

chk_session.sh

chk_slowquery_cnt.sh

파일 권한은 chmod +x *.sh

파일 업로드 후 bin/sbinM^ 뭐..이딴 에러나면

vi 파일명

:set ff=unix

:wq

전체 파일

mysql command monitoring script v1.1.txt

/* CentOS 설치

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

nmon Install

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

sudo su -

mkdir -p /usr1/program

cd /usr1/program

wget http://sourceforge.net/projects/nmon/files/nmon_linux_14i.tar.gz

tar xvfp nmon_linux_14i.tar.gz

ls -l /usr/bin/nmon

chmod +x nmon*

ls -l 

mv nmon_x86_64_centos6 /usr/bin/nmon

ls -l /usr/bin/nmon

nmon

rm -rf nmon*

*/

http://cafe.naver.com/mysqlpg.cafe?iframe_url=/ArticleList.nhn%3Fsearch.clubid=26608564%26search.menuid=28%26search.boardtype=L

extreme linux performance monitoring part ii (io tuning).pdf

nmon_analyser_34a.zip

nmon_linux_14i.tar.gz

nmon_linux_14i_ubuntu134

MySQL, Oracle 가 설치된 Linux 환경에서 OS 성능분석툴로 유용한 nmon 공유드립니다.

IBM 에서 OS 관련 문제해결시 사용하는 툴로 원래 AIX 용으로 개발되었지만,

최근에는 Linux 버전도 제공하고 있으며, 수집된 Log 는 nmon analyzer 를 통해 엑셀로 이쁘게 출력도 됩니다.

그래서 저희는, nmon을 default로 설치하여 모니터링 하고 있답니다. ㅎㅎ

사용법 : nmon 실행 후, c 클릭 (CPU), m 클릭 (Memory), n 클릭 (Network), d 클릭 (Disk)

[nmon wiki]

http://nmon.sourceforge.net/pmwiki.php

http://nmon.sourceforge.net/pmwiki.php?n=Site.Download

[nmon tool 다운로드]

: http://www.ibm.com/developerworks/wikis/display/WikiPtype/nmon
: 압축풀고 /usr/bin 으로 nmon 파일을 복사해서 사용하면 됨.

http://cafe.naver.com/mysqlpg.cafe?iframe_url=/ArticleList.nhn%3Fsearch.clubid=26608564%26search.menuid=28%26search.boardtype=L

env.ini

mon_qps.sh.txt

mon_script.sh.txt

서비스 오픈초기 모니터링시 사용하는 "실시간 모니터링" 스크립트 공유해 봅니다.

mon_qps.sh

mon_script.sh

@ 유용한 Script 들 많이 공유되면 좋겠네요. ^^;

시간하고 같이 찍는 스크립트 입니다. 

mysqladmin ext -i1 -u root --password=$_PASSWD | gawk '
/Queries/{q=$4-qp;qp=$4}
/Questions/{qs=$4-qsp;qsp=$4}
/Slow_queries/{sq=$4-sqp;sqp=$4}
/Com_select/{cs=$4-csp;csp=$4}
/Com_insert/ && $2 !~ /Com_insert_select/{ci=$4-cip;cip=$4}
/Com_update/ && $2 !~ /Com_update_multi/{cu=$4-cup;cup=$4}
/Com_delete/ && $2 !~ /Com_delete_multi/{cd=$4-cdp;cdp=$4}
/Threads_connected/{tc=$4}
/Threads_running/{printf "Time: %20s Queries: %5d Questions: %5d Slow_queries: %5d Com_select: %5d Com_insert: %5d Com_update: %5d Com_delete: %5d Threads_connected: %5d Threads_running: %5d\n", strftime("%m-%d-%Y %H:%M:%S"),q,qs,sq,cs,ci,cu,cd,tc, $4}' > $LOG_DIR/thread_mon.log

mysqlbinlog --database=sakila /gamevil/database/binlog/mysql-master-bin.000001 | grep -B 3 -i 'drop table sakila.payment'

# at 352

# 070919 16:11:23 server id 1 end_log_pos 429 Query thread_id=16 exec_time=0 error_code=0

SET TIMESTAMP=1190232683/*!*/;

DROP TABLE sakila.payment/*!*/;

server1# mysqlbinlog --database=sakila /gamevil/database/binlog/mysql-master.bin.000001 --stop-position=352 | mysql -uroot -p

server1# mysqlbinlog --database=sakila /gamevil/database/binlog/mysql-master.bin.000001 --start-position=429 | mysql -uroot -p

출처 : http://www.s-arcana.co.jp/tech/2011/11/insert-on-duplicate-key-update.html

프라이머리 키, 유니크 키가 중복된 경우에 UPDATE를 하는 MySQL의 편리한 구문 INSERT ON DUPLICATE KEY UPDATE
그럼 사용법을 알아보자

테스트용 테이블 준비

먼저 테이블을 작성

그리고 적당히 데이타를 insert한다.

한행씩 처리하는 경우는 문제가 없는데...

ON DUPLICATE KEY UPDATE를 사용, 한행씩 insert하는 경우라면 이런 식으로 UPDATE하고 싶은 컬럼이랑 값을 지정한다.

하지만 이것으로 복수행을 insert할경우에 어떻게 값을 설정해야 좋을지 고민하게 된다.

해결방법을 발견!

다큐멘트를 잘 보면 이렇게 적혀진것을 발견할수 있다.

실제로 사용해 보면 이렇다.

한번에 갱신이 되었다.

REPLACE INTO를 사용하면 동일하게 동작하지만 REPLACE INTO는 새롭게 insert를 해버리므로
ON UPDATE CURRENT_TIMESTAMP를 사용할수 없다.

CentOS_mysql_xtrabackup_설치패키지

package_1.1.zip

Ubuntu_mysql_xtrabackup_설치패키지

package.zip

http://mysql-mmm.org/mmm2:guide

Installation Guide

Basic configuration of master 1

aptitude install mysql-server

server_id           = 1
log_bin             = /var/log/mysql/mysql-bin.log 
log_bin_index       = /var/log/mysql/mysql-bin.log.index 
relay_log           = /var/log/mysql/mysql-relay-bin 
relay_log_index     = /var/log/mysql/mysql-relay-bin.index 
expire_logs_days    = 10 
max_binlog_size     = 100M 
log_slave_updates   = 1

bind-address = 127.0.0.1

auto_increment_increment = 2

auto_increment_offset = 1

bind-address = 0.0.0.0

/etc/init.d/mysql restart

Create users

GRANT REPLICATION CLIENT                 ON *.* TO 'mmm_monitor'@'192.168.0.%' IDENTIFIED BY 'monitor_password';
GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.0.%'   IDENTIFIED BY 'agent_password';
GRANT REPLICATION SLAVE                  ON *.* TO 'replication'@'192.168.0.%' IDENTIFIED BY 'replication_password';

Synchronisation of data between both databases

(db1) mysql> FLUSH TABLES WITH READ LOCK;

(db1) mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+ 
| mysql-bin.000002 |      374 |              |                  | 
+------------------+----------+--------------+------------------+ 
1 row in set (0.00 sec) 

db1$ mysqldump -u root -p --all-databases > /tmp/database-backup.sql

(db1) mysql> UNLOCK TABLES;

db1$ scp /tmp/database-backup.sql <user>@192.168.0.12:/tmp
db1$ scp /tmp/database-backup.sql <user>@192.168.0.13:/tmp
db1$ scp /tmp/database-backup.sql <user>@192.168.0.14:/tmp

db2$ mysql -u root -p < /tmp/database-backup.sql
db3$ mysql -u root -p < /tmp/database-backup.sql
db4$ mysql -u root -p < /tmp/database-backup.sql

(db2) mysql> FLUSH PRIVILEGES;
(db3) mysql> FLUSH PRIVILEGES;
(db4) mysql> FLUSH PRIVILEGES;

Setup replication

(db2) mysql> CHANGE MASTER TO master_host='192.168.0.11', master_port=3306, master_user='replication', 
              master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;
(db3) mysql> CHANGE MASTER TO master_host='192.168.0.11', master_port=3306, master_user='replication', 
              master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;
(db4) mysql> CHANGE MASTER TO master_host='192.168.0.11', master_port=3306, master_user='replication', 
              master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;

(db2) mysql> START SLAVE;
(db3) mysql> START SLAVE;
(db4) mysql> START SLAVE;

(db2) mysql> SHOW SLAVE STATUS\G
*************************** 1. row *************************** 
             Slave_IO_State: Waiting for master to send event 
                Master_Host: 192.168.0.11
                Master_User: replication
                Master_Port: 3306 
              Connect_Retry: 60 
…
(db3) mysql> SHOW SLAVE STATUS\G
*************************** 1. row *************************** 
             Slave_IO_State: Waiting for master to send event 
                Master_Host: 192.168.0.11
                Master_User: replication
                Master_Port: 3306 
              Connect_Retry: 60 
…
(db4) mysql> SHOW SLAVE STATUS\G
*************************** 1. row *************************** 
             Slave_IO_State: Waiting for master to send event 
                Master_Host: 192.168.0.11
                Master_User: replication
                Master_Port: 3306 
              Connect_Retry: 60 
…

(db2) mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+ 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+--------------+------------------+ 
| mysql-bin.000001 |       98 |              |                  |
+------------------+----------+--------------+------------------+ 
1 row in set (0.00 sec) 

(db1) mysql> CHANGE MASTER TO master_host = '192.168.0.12', master_port=3306, master_user='replication',
              master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;

(db1) mysql> START SLAVE;

(db1) mysql> SHOW SLAVE STATUS\G
*************************** 1. row *************************** 
             Slave_IO_State: Waiting for master to send event 
                Master_Host: 192.168.0.12
                Master_User: <replication>
                Master_Port: 3306 
              Connect_Retry: 60 
…

Install MMM

Create user

useradd --comment "MMM Script owner" --shell /sbin/nologin mmmd

Monitoring host

aptitude install liblog-log4perl-perl libmailtools-perl liblog-dispatch-perl libclass-singleton-perl libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl

dpkg -i mysql-mmm-common_*.deb mysql-mmm-monitor*.deb

Database hosts

aptitude install liblog-log4perl-perl libmailtools-perl liblog-dispatch-perl iproute libnet-arp-perl libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl

dpkg -i mysql-mmm-common_*.deb mysql-mmm-agent_*.deb

yum install -y mysql-mmm-agent

mysql-mmm-agent.noarch 0:2.2.1-1.el5                                          

libart_lgpl.x86_64 0:2.3.17-4                                                 
mysql-mmm.noarch 0:2.2.1-1.el5                                                
perl-Algorithm-Diff.noarch 0:1.1902-2.el5                                     
perl-DBD-mysql.x86_64 0:4.008-1.rf                                            
perl-DateManip.noarch 0:5.44-1.2.1                                            
perl-IPC-Shareable.noarch 0:0.60-3.el5                                        
perl-Log-Dispatch.noarch 0:2.20-1.el5                                         
perl-Log-Dispatch-FileRotate.noarch 0:1.16-1.el5                              
perl-Log-Log4perl.noarch 0:1.13-2.el5                                         
perl-MIME-Lite.noarch 0:3.01-5.el5                                            
perl-Mail-Sender.noarch 0:0.8.13-2.el5.1                                      
perl-Mail-Sendmail.noarch 0:0.79-9.el5.1                                      
perl-MailTools.noarch 0:1.77-1.el5                                            
perl-Net-ARP.x86_64 0:1.0.6-2.1.el5                                           
perl-Params-Validate.x86_64 0:0.88-3.el5                                      
perl-Proc-Daemon.noarch 0:0.03-1.el5                                          
perl-TimeDate.noarch 1:1.16-5.el5                                             
perl-XML-DOM.noarch 0:1.44-2.el5                                              
perl-XML-Parser.x86_64 0:2.34-6.1.2.2.1                                       
perl-XML-RegExp.noarch 0:0.03-2.el5                                           
rrdtool.x86_64 0:1.2.27-3.el5                                                 
rrdtool-perl.x86_64 0:1.2.27-3.el5 

Configure MMM

active_master_role          writer


<host default>
    cluster_interface       eth0

    pid_path                /var/run/mmmd_agent.pid
    bin_path                /usr/lib/mysql-mmm/

    replication_user        replication
    replication_password    replication_password

    agent_user              mmm_agent
    agent_password          agent_password
</host>

<host db1>
    ip                      192.168.0.11
    mode                    master
    peer                    db2
</host>

<host db2>
    ip                      192.168.0.12
    mode                    master
    peer                    db1
</host>

<host db3>
    ip                      192.168.0.13
    mode                    slave
</host>

<host db4>
    ip                      192.168.0.14
    mode                    slave
</host>


<role writer>
    hosts                   db1, db2
    ips                     192.168.0.100
    mode                    exclusive
</role>

<role reader>
    hosts                   db1, db2, db3, db4
    ips                     192.168.0.101, 192.168.0.102, 192.168.0.103, 192.168.0.104
    mode                    balanced
</role>

include mmm_common.conf
this db1

include mmm_common.conf

<monitor>
    ip                      127.0.0.1
    pid_path                /var/run/mmmd_mon.pid
    bin_path                /usr/lib/mysql-mmm/
    status_path             /var/lib/misc/mmmd_mon.status
    ping_ips                192.168.0.1, 192.168.0.11, 192.168.0.12, 192.168.0.13, 192.168.0.14
</monitor>

<host default>
    monitor_user            mmm_monitor
    monitor_password        monitor_password
</host>

debug 0

Start MMM

Start the agents

Debian/Ubuntu

ENABLED=1

Red Hat

chkconfig mysql-mmm-agent on

/etc/init.d/mysql-mmm-agent start

Start the monitor

ENABLED=1

/etc/init.d/mysql-mmm-monitor start

mon$ mmm_control show
  db1(192.168.0.11) master/AWAITING_RECOVERY. Roles: 
  db2(192.168.0.12) master/AWAITING_RECOVERY. Roles: 
  db3(192.168.0.13) slave/AWAITING_RECOVERY. Roles: 
  db4(192.168.0.14) slave/AWAITING_RECOVERY. Roles: 

mon$ tail /var/log/mysql-mmm/mmm_mon.warn
…
2009/10/28 23:15:28  WARN Detected new host 'db1': Setting its initial state to 'AWAITING_RECOVERY'. Use 'mmm_control set_online db1' to switch it online.
2009/10/28 23:15:28  WARN Detected new host 'db2': Setting its initial state to 'AWAITING_RECOVERY'. Use 'mmm_control set_online db2' to switch it online.
2009/10/28 23:15:28  WARN Detected new host 'db3': Setting its initial state to 'AWAITING_RECOVERY'. Use 'mmm_control set_online db3' to switch it online.
2009/10/28 23:15:28  WARN Detected new host 'db4': Setting its initial state to 'AWAITING_RECOVERY'. Use 'mmm_control set_online db4' to switch it online.

mon$ mmm_control set_online db1
OK: State of 'db1' changed to ONLINE. Now you can wait some time and check its new roles!
mon$ mmm_control set_online db2
OK: State of 'db2' changed to ONLINE. Now you can wait some time and check its new roles!
mon$ mmm_control set_online db3
OK: State of 'db3' changed to ONLINE. Now you can wait some time and check its new roles!
mon$ mmm_control set_online db4
OK: State of 'db4' changed to ONLINE. Now you can wait some time and check its new roles!

http://foxrain93.blog.me/100192636540

자동 백업 스크립트 만들기

# vi mysqlbackup

---------------------------------------

#!/bin/sh

DATE=`date +"%Y%m%d%H%M"`

BACKUP_DIR=/mysqlbackup 

FILENAME=mysqlback 

/usr/bin/mysqldump -uroot -ppassword dbname > ${BACKUP_DIR}/${FILENAME}_${DATE}.sql 

# (or dbname -> --all-databases) 

cd ${BACKUP_DIR} 

tar cfpz ${FILENAME}_${DATE}.tar.gz ${FILENAME}_${DATE}.sql 

rm -Rf ${FILENAME}_${DATE}.sql  

chown root.root ${BACKUP_DIR}/${FILENAME}_${DATE}.tar.gz 

chmod 755 ${BACKUP_DIR}/${FILENAME}_${DATE}.tar.gz 

find ${BACKUP_DIR}/ -mtime +5 -exec rm -f {} \; 

cp ${BACKUP_DIR}/${FILENAME}_${DATE}.tar.gz /nfs 

-------------------------------------------------------------- 

# chmod 755 mysqlbackup 

매일 3시 1분에 실행

# crontab -e

----------------------------------

01 03 * * * sh /mysqlbackup/mysqlbackup > /dev/null 2>&1

------------------------------------

/etc/rc.d/init.d/crond stop

/etc/rc.d/init.d/crond start

http://foxrain93.blog.me/100192636540

자동 백업 스크립트 만들기

# vi mysqlbackup

---------------------------------------

#!/bin/sh

DATE=`date +"%Y%m%d"`

OLD_DATE=`date --date '5 days ago'+"%Y%m%d"` 

BACKUP_DIR=/mysqlbackup 

/usr/bin/mysqldump -uroot -ppassword dbname > ${BACKUP_DIR}/dbname_${DATE}.sql 

(or dbname -> --all-databases) 

chown root.root ${BACKUP_DIR}/dbname_${DATE}.sql 

chmod 755 ${BACKUP_DIR}/dbname_${DATE}.sql 

rm -Rf ${BACKUP_DIR}/dbname_${OLD_DATE}.sql 

-------------------------------------------------------------- 

# chmod 755 mysqlbackup 

매일 3시 1분에 실행

# crontab -e

----------------------------------

1 3 * * * sh /mysqlbackup/mysqlbackup > /dev/null 2>&1

------------------------------------

/etc/rc.d/init.d/crond stop

/etc/rc.d/init.d/crond start

한 번에 모든 테이블 Optimizing table 하는 스크립트

#!/bin/bash 
MYSQL_LOGIN='-u<user> -p<password>'
for db in $(echo "SHOW DATABASES;" | mysql $MYSQL_LOGIN | grep -v -e "Database" -e "information_schema")
do
        TABLES=$(echo "USE $db; SHOW TABLES;" | mysql $MYSQL_LOGIN |  grep -v Tables_in_)
        echo "Switching to database $db"
        for table in $TABLES
        do
                echo -n " * Optimizing table $table ... "
                echo "USE $db; OPTIMIZE TABLE $table" | mysql $MYSQL_LOGIN >/dev/null
                echo "done."
        done
done 

http://foxrain93.blog.me/100192636540

i <= RAND < j 를

FLOOR(i+RAND()*(j-i)) 로 나타낸다.

5자릿수라면,

FLOOR(10000+RAND()*(99999-10000))

즉,

FLOOR(10000+RAND()*89999)

http://foxrain93.blog.me/100192636540

1.

mysql> SHOW SLAVE STATUS\G 상에서 오류가 나타나지 않았는데 로그에선 다음과 같은 오류가 발생했다면,

# vi /var/log/mysqld.log

-----------------------------------------------

[Note] Error reading relay log event: slave SQL thread was killed 

~

------------------------------------------------

또, mmm monitor에서 mmm_control checks 명령으로 replication broken 상태가 보인다면,

마스터를 설정하는 과정 change master 에서 master가 될 서버의 로그 파일과 pos을 세팅할때 master 서버에서 상태값을 받아올때,

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;

mysql> UNLOCK TABLES;

위 처럼 락 상태에서 상태값을 읽고 slave에서 change master 를 실행해준다.

명령을 실행할때마다 position 이 바뀌기 때문에 잘못된 replication 연결로 발생한것이다.

2. 아래같은 상태일때, port가 방화벽에 열려있는지 확인한다.

# mmm_control show 
# Warning: agent on host db1 is not reachable 
# Warning: agent on host db2 is not reachable 
  db1() master/ONLINE. Roles: 
  db2() master/ONLINE. Roles:

mmm 에 필요한 port는

mysql_port : 3306 (복제는 실행되지만 모니터링이 안된다, agent, monitor, tools)

agent_port : 9989 (agent, monitor)

ssh_port : 22 (tools)

http://foxrain93.blog.me/100192636540

최소 2개의 db server 와 monistoring server가 필요하다.

master 1 환경 설정

# vi /etc/my.cnf

-------------------------------------------

server_id = 1

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

log_bin_index = /var/log/mysql/mysql-bin.log.index

relay_log = /var/log/mysql/mysql-relay-bin

relay_log_index = /var/log/mysql/mysql-relay-bin.index

expire_logs_days = 10

max_binlog_size = 100M

log_slave_updates = 1

# bind-address = 127.0.0.1

삭제 하고

bind-address = 0.0.0.0

auto_increment_increment = 2 // master 서버의 수 설정

auto_increment_offset = 1 // 각 서버에서 auto_increment_increment 보다 적은(less than) 수로 unique 하게 설정

----------------------------------------------------------------

# /etc/init.d/mysqld restart

error  : ~ file '/var/log/mysql/mysql-bin.log.index' not found 로 재구동이 안될경우

->

# mkdir /var/log/mysql

# chown -R mysql.mysql /var/log/mysql

# chmod -R 755 /var/log/mysql

mysql 사용자 생성

mysql> GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.0.%' IDENTIFIED BY 'monitor_password';
mysql> GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.0.%' IDENTIFIED BY 'agent_password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.0.%' IDENTIFIED BY 'replication_password';

백업을 위해 데이터 변경을 금지한다.

mysql> FLUSH TABLES WITH READ LOCK;

상태값을 기억한다.

mysql> SHOW MASTER STATUS;

백업을 하는동안 mysql-shell 을 닫으면 안된다. 위에서한 lock이 해제된다.

# mysqldump -u root -p --all-databases > /tmp/database-backup.sql

mysql> UNLOCK TABLES;

다른 db 서버로 옮긴후 import 한다,

# mysql -u root -p < /tmp/database-backup.sql

권한을 새로이 적용한다

mysql> FLUSH PRIVILEGES;

mysql> CHANGE MASTER TO master_host='192.168.0.11', master_port=3306, master_user='replication', master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;

(error)

// log를 보면 mysql slave failed to open the relay log 이렇게 나온다. 이럴 경우,

mysql> FLUSH LOGS;

mysql> RESET SLAVE;

mysql> CHANGE MASTER TO master_host='192.168.0.11', master_port=3306, master_user='replication', master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;

// 오류가 없으면,

mysql> SLAVE START;

mysql> SHOW SLAVE STATUS\G

이제 반대로 replication 을 실행해야 한다.

mysql> SHOW MASTER STATUS;

// 새 파일과 위치를 기억한다.

db1으로 가서 명령을 실행한다. db2 에서 기억한 SHOW MASTER STATUS의 <file>, <position> 을 입력한다.

mysql> CHANGE MASTER TO master_host = '192.168.0.12', master_port=3306, master_user='replication', master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G

# vi /etc/sysconfig/iptables

--------------------------------------

// agent, monitor, tools

-A INPUT -s 192.168.10.0/24 -m state --state NEW -p tcp --dport 3306 -j ACCEPT

// agent, monitor

-A INPUT -s 192.168.10.0/24 -m state --state NEW -p tcp --dport 9989 -j ACCEPT

// tools

-A INPUT -s 192.168.10.0/24 -m state --state NEW -p tcp --dport 22 -j ACCEPT

---------------------------------------

MMM 설치

모든 host 에서,

# useradd --comment "MMM Script owner" --shell /sbin/nologin mmmd

Monitoring host 에서,

# yum install mysql-mmm mysql-mmm-monitor

Database hosts 에서,

# yum install mysql-mmm mysql-mmm-agent

MMM 환경 설정

모든 host에서,

# vi /etc/mysql-mmm/mmm_common.conf

--------------------------------------------------

active_master_role          writer

<host default>

# cluster에서 사용할 장비 설정
    cluster_interface       eth0

    pid_path                /var/run/mysql-mmm/mmm_agentd.pid
    bin_path                /usr/libexec/mysql-mmm/

    replication_user        replication
    replication_password    replication_password

    agent_user              mmm_agent
    agent_password          agent_password
</host>

<host db1>
    ip                      192.168.0.11
    mode                    master
    peer                    db2
</host>

<host db2>
    ip                      192.168.0.12
    mode                    master
    peer                    db1
</host>

# slave 및 추가 장비들

#<host db3>
#    ip                      192.168.0.13
#    mode                    slave
#</host>

#<host db4>
#   ip                      192.168.0.14
#    mode                    slave
#</host>

<role writer>
    hosts                   db1, db2
    ips                     192.168.0.250
    mode                    exclusive
</role>

<role reader>
    hosts                   db1, db2
    ips                     192.168.0.251, 192.168.0.252
    mode                    balanced
</role>

--------------------------------------------------

Database hosts 에서, (db1 일 경우)

# vi /etc/mysql-mmm/mmm_agent.conf

---------------------------------------------------

include mmm_common.conf

this db1

---------------------------------------------------

Monitor host 에서

# vi /etc/mysql-mmm/mmm_mon.conf

-------------------------------------------------------

include mmm_common.conf

<monitor>
    ip                      127.0.0.1
    pid_path             /var/run/mysql-mmm/mmm_mond.pid
    bin_path             /usr/libexec/mysql-mmm/
    status_path        /var/lib/mysql-mmm/mmm_mond.status

# ping_ips 는 monitor 가 network connection이 ok 상태인지 ping해보는 ip 이다. ping 이 가능한 서버들을 리스트한다.
    ping_ips              192.168.0.1, 192.168.0.11, 192.168.0.12

    auto_set_online    60
</monitor>

<host default>
    monitor_user        mmm_monitor
    monitor_password monitor_password
</host>

debug 0

-------------------------------------------------------

MMM 구동

Database hosts 에서,

(Debian/Ubuntu)

# vi /etc/default/mysql-mmm-agent

-----------------------------------

ENABLED=1

-----------------------------------

(Red Hat)

# chkconfig mysql-mmm-agent on

# /etc/init.d/mysql-mmm-agent start

Monitor host 에서,

# vi /etc/default/mysql-mmm-monitor

------------------------------------

ENABLED=1

-------------------------------------

# chkconfig mysql-mmm-monitor on

# /etc/init.d/mysql-mmm-monitor start

위의 함수 외에 이전 시간을 구할 필요가 있을 경우도 있을 것입니다~!!

예를 들면 몇분전, 몇시간 전을 말하는 것입니다!

해당 경우를 대비해 추가로 참고 자료를 넣습니다~!^-^/

xtrabackup 

DB가 10GB가 넘어가면서 mysqldump로 백업하면 그 시간동안 서버가 기어다님ㅠㅠ

xtrabackup innobackupex 를 쓰고싶었지만 perl DBI mysql 없어서 실행안됨ㅠㅠ

결국 xtrabackup 을 써봤는데 속도도 빠르고 괜찮아서 걍 쓰기로마음먹고 

백업<->복구 

백업->증분백업->복구

테스트해본결과 속도도 괜찮고 몇가지 사항만 숙지한다면 복구도 확실해서 쓰기로 마음먹었으나 사용법이 OTZ

1. 백업 = 전체 백업 -> 증분 백업 

2. 복구 = 증분+전체백업 합치기 -> 복구

백업(전체 백업)

frm 파일은 백업되지 않음으로

mysql 데이터 폴더 (/usr/local/mysql/data)에 database 폴더를 수동으로 백업한다

cp -r /usr/local/mysql/data/mysql /backup/mysql/xtrabackup_mysql
cp -r /usr/local/mysql/data/mydatabase /backup/mysql/xtrabackup_mydatabase

xtrabackup --backup --target-dir=/backup/mysql/xtrabackup_basedir

xtrabackup --prepare --target-dir=/backup/mysql/xtrabackup_basedir

백업(증분 백업)

xtrabackup --backup --target-dir=/backup/mysql/incremental/ --incremental-basedir=/backup/mysql/xtrabackup_basedir/

복구

0. 전체 백업에 증분백업을 합친다

1. mysql 종료

2. mysql 데이터 폴더 (/usr/local/mysql/data) 삭제

3. mysql 데이터 폴더 (/usr/local/mysql/data)에 database 폴더를 수동으로 백업한다 <- 이걸복사

4. 백업데이터를 복사

5. mysql 데이터 폴더 소유권 변경

6. mysql 시작

0. xtrabackup --prepare --apply-log-only --target-dir=/backup/xtrabackup/ --incremental-dir=/backup/mysql/incremental/

1. service mysqld stop

2. rm -rf /usr/local/mysql/data

3. mv /backup/mysql/xtrabackup_basedir /usr/local/mysql/data

4. 

    cp -r /backup/mysql/xtrabackup_mysql /usr/local/mysql/data/myssql

    cp -r /backup/mysql/xtrabackup_mydatabase /usr/local/mysql/data/mydatabase

5. chown -R mysql:mysql /usr/local/mysql/data

6. service mysqld start

crontab 에 등록해서 매월 1일에 전체백업을 하고 월 말까지 날마다 증분 백업하는 스크립트 예

[backup_month.sh]

#!/bin/bash
# xtrabackup
# 매월 첫 달에 실행된다
# DB xtrabackup base point
if [ ! -d /backup/mysql ]
then
mkdir /backup/mysql
mkdir /backup/mysql/$(date +%Y-%m)
fi
cp -r /usr/local/mysql/data/mysql /backup/mysql/$(date +%Y-%m)/xtrabackup_mysql
cp -r /usr/local/mysql/data/mydatabase/backup/mysql/$(date +%Y-%m)/xtrabackup_mydatabase
xtrabackup --backup --target-dir=/backup/mysql/$(date +%Y-%m)/xtrabackup_basedir
xtrabackup --prepare --target-dir=/backup/mysql/$(date +%Y-%m)/xtrabackup_basedir

[backup_day.sh]

#!/bin/bash
# xtrabackup 
# 매 일 실행된다
# DB xtrabackup base point
if [ ! -d /backup/mysql ]
then
mkdir /backup/mysql
mkdir /backup/mysql/$(date +%Y-%m)
fi
# DB xtrabackup increment point
if [ ! -d /backup/mysql/$(date +%Y-%m)/incremental ]
then
mkdir /backup/mysql/$(date +%Y-%m)/incremental
fi
xtrabackup --backup --target-dir=/backup/mysql/$(date +%Y-%m)/incremental/$(date +%d)/ --incremental-basedir=/backup/mysql/$(date +%Y-%m)/xtrabackup_basedir/

실행권한으로 바꾸고

chmod 0700 backup_month.sh

chmod 0700 backup_day.sh

crontab 등록

crontab -e

00 03 1 * * /root/contab/backup_month.sh
00 06 * * * /root/contab/backup_day.sh

Mysql과 MariaDB에 핫백업(Online Backup)을 지원하는 툴 중 무료에 쓸만한 툴은 Percona의 Xtrabackup이 제일 유명할 것이다.

하지만 한글로 정리된 자료 중 예와 함께 정리된 자료를 찾기 힘들어 이렇게 정리한다.

1) 백업 수행 계정

우선 백업하는 OS 계정은 root 혹은 mysql로 하는 것이 정신 건강상 편하다(보안을 위해 가능한 mysql계정으로)

설치 방법에 따라 다르지만 ubuntu에서 apt-get으로 설치했다고 하면 아마도 다음과 같이 소유권이 생성되어 있을 것이다.

여기서 소유권이 모두 접근 가능해야 하기 때문에 권한 설정을 잘 해 놓아야 하기 때문에 다른계정은 설정이 쉽지 않다.

(dba그룹을 만들고 참여하게 한다든지 방법이 없지는 않다.)

2) 모든 DB 백업

ex)

위와 같이 하면 백업경로 아래에 시간 폴더가 생성되며 백업이 저장된다

(os계정 로그인에 관리자 권한이 없다면 "--user <관리자계정> --password <암호>"를 추가하자)

3) 로그 백업 (로그를 기존 백업에 수정된 내용이 반영됨)

ex)

4) 모든 DB 복원

데이터 폴더 이름을 바꿔놓고(만약을 위해) 새로 생성후(빈 폴더) 권한을 바꿔주는 것이 포인트!

ex)

5) 특정 DB복원

Database파일이 손상되었다면 그대로, Drop 하였다면 같은 이름으로 생성한(show databases에서 보이도록) 상태에서

ex)

아마 여기까지 했다면 Database에 접속은 되지만 Table Select가 안될 것이다

찾아보면 서버에서 갖고 있는 테이블 정보와 매칭이 안되서라고 하니 다음을 수행시키자

확인해 보면 아마 복원 되어 있을 것이다

6) 특정 Table 복원

맹락은 특정 Database복원과 같다

show tables로 보이도록 만들어 두고

ex)

아마 여기까지 했다면 Database에 접속은 되지만 Table Select가 안될 것이다

확인해 보면 아마 복원 되어 있을 것이다

간만에 기술 블로깅질.. 
좀 하드코어 스럽긴 하지만... 일단 DB업그레이드 하기로 .. 결정.
이유는 뭐 빨라졌다고 해서?;;; 
장비빨이 안되는 환경에서 지푸라기라도 잡고 싶은 심정으로다가..  

일단 ... 개발 서버인 Ubuntu Jaunty 32bit 에다가 우선적으로다가.. 

 
MySQL 다운로드 사이트에서  Linux Generic 파일 받아서 압축을 풀면 RPM들 파일이 나온다.. 
이게 뭐밍?;; 뭐 그래서 위에 녀석이 필요하다.. 

and then 
일단 난 

해줬다.. 

그럼 mysql-server_5.5.10-1.linux2.6_i386.deb 파일과 mysql-client_5.5.10-1.linux2.6_i386.deb 파일이 생성된다. 

일단 설치 준비는 끝.. 
그다음에 .. 혹시 모를 상황을 대비해서 백업 실시 .. 

먼저 첨부된 파일 다운로드합니다.

#  yum install perl-DBD-mysql 
                     OR
# tar -zxvf DBD-mysql-4.018.tar.gz