태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

달력

32021  이전 다음

  •  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  •  
  •  
  •  

'3-4. DATABASE/MySQL'에 해당되는 글 7건

  1. 2009.05.26 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)
Posted by coriahn coriahn

댓글을 달아 주세요