mysql replication

0. Mysql Replication
    0.1. CentOs 4.7, mysql 4.1.22를 기준으로 작성하였습니다.

[root@DB_master ~]# uname -a Linux DB_master 2.6.9-78.EL #1 Thu Jul 24 23:46:01 EDT 2008 i686 i686 i386 GNU/Linux [root@DB_master ~]# mysql --version mysql  Ver 14.7 Distrib 4.1.22, for redhat-linux-gnu (i686) using readline 4.3

     0.2. 동일 버전에서 2회 성공하였고, 급조해서 만든 메뉴얼이니.. 안되면 당신탓.. ㅋㅋ

리플은 남겨주셈.. 안되는 경우라도 알게..

1. DB_master에서의 설정
    1.1. my.cnf 의 [mysqld] 섹션에 굵은 글씨로 된 2줄을 추가.

[root@DB_master ~]# vi /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql log-bin server-id=1 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

 
    1.2. 변경된 설정파일 적용을 위해 mysqld 재시작

[root@DB_master ~]# /etc/init.d/mysqld restart MySQL 를 정지함:  [  확인  ] MySQL (을)를 시작함:  [  확인  ]

 
    1.3. DB에 접속하여 마스터 설정(연결 계정 추가, 상태 확인)

[root@DB_master ~]# mysql -uroot -p Enter password: mysql> GRANT SELECT , RELOAD , LOCK TABLES , REPLICATION SLAVE , REPLICATION CLIENT ON *.* TO ReplicationID@"%" IDENTIFIED by 'ReplicationPW'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +----------------------+----------+--------------+------------------+ | File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | +----------------------+----------+--------------+------------------+ | DB_master-bin.000001 |       79 |              |                  | +----------------------+----------+--------------+------------------+ 1 row in set (0.01 sec)

 
    1.4. DB_master 백업을 위해 해당 폴더 압축

[root@DB_master ~]# cd /var/lib [root@DB_master lib]# tar cvf DB_master.tar mysql/ mysql/ mysql/ib_logfile1 mysql/ibdata1 mysql/mysql/ mysql/mysql/help_relation.frm …

2. DB_slave에서의 설정
    2.1. DB_master에서 압축한 DB 복사

[root@DB_salve ~]# cd /var/lib/ [root@DB_salve lib]# scp 192.168.0.175:/var/lib/DB_master.tar . The authenticity of host '192.168.0.175 (192.168.0.175)' can't be established. RSA key fingerprint is 5f:88:50:f1:62:a0:3a:d2:a2:89:d7:a7:fd:60:fc:3b. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.0.175' (RSA) to the list of known hosts. root@192.168.0.175's password: DB_master.tar                                           100%   21MB   1.6MB/s   00:13    [root@DB_salve lib]# tar xvf DB_master.tar mysql/ mysql/ib_logfile1 mysql/ibdata1 mysql/mysql/ mysql/mysql/help_relation.frm …

 
    2.2. my.cnf 의 [mysqld] 섹션에 굵은 글씨로 된 5줄을 추가.

[root@DB_salve lib]# vi /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql log-bin server-id=2 log-slave-updates log-warning replicate-ignore-db=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

    2.3. 변경된 설정파일 적용을 위해 mysqld 재시작

[root@DB_salve lib]# /etc/init.d/mysqld restart MySQL 를 정지함:  [  확인  ] MySQL (을)를 시작함:  [  확인  ]

    2.4. DB에 접속하여 슬레이브 설정(마스터 연결정보, 슬레이브 시작)

[root@DB_salve lib]# mysql -u root -p Enter password: mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.175', MASTER_USER='ReplicationID', MASTER_PASSWORD='ReplicationPW'; Query OK, 0 rows affected (0.05 sec) mysql> slave start; Query OK, 0 rows affected (0.00 sec) mysql> SHOW SLAVE STATUS; +----------------------------------+---------------+-------------+-------------+----------- | Slave_IO_State                   | Master_Host   | Master_User | Master_Port | Connect_Re +----------------------------------+---------------+-------------+-------------+----------- | Waiting for master to send event | 192.168.0.175 | ReplicationID|        3306 |           +----------------------------------+---------------+-------------+-------------+----------- 1 row in set (0.00 sec)

3. 설정 확인
    3.1. DB_master에서 값입력

[root@DB_master ~]# mysql -uroot -p Enter password: mysql> use radius Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> insert into radcheck (username,attribute,op,value) values ('test','User-Password',':=','tpw1'); Query OK, 1 row affected (0.06 sec) mysql> insert into radcheck (username,attribute,op,value) values ('test1','User-Password',':=','tpw1'); Query OK, 1 row affected (0.00 sec) mysql> select * from radcheck; +----+----------+---------------+----+-------+ | id | UserName | Attribute     | op | Value | +----+----------+---------------+----+-------+ |  1 | test     | User-Password | := | tpw1  | |  2 | test1    | User-Password | := | tpw1  | +----+----------+---------------+----+-------+ 2 rows in set (0.00 sec)

    3.2. DB_slave에서 변경된 값 확인

[root@DB_slave ~]# mysql -uroot -p Enter password: mysql> use radius Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from radcheck; +----+----------+---------------+----+-------+ | id | UserName | Attribute     | op | Value | +----+----------+---------------+----+-------+ |  1 | test     | User-Password | := | tpw1  | |  2 | test1    | User-Password | := | tpw1  | +----+----------+---------------+----+-------+ 2 rows in set (0.00 sec)