검색 엔진의 방문이 늘어나고 있군...

Posted
Filed under 프로그램과 명령어/서버와 서비스
참조 원문 : Setup Master-Slave Replication in MySQL Server
참조 원문 : MySQL replication - Master/Slave

  MySQL 리플리케이션이란 마스터와 슬레이브 DB 서버를 설정하여 마스터의 DB 데이터를 슬레이브의 DB에 자동으로 복사하게 하는 일종의 DB 고가용성(High Availablility) 솔루션입니다. 마스터 서버가 죽는 일이 발생해도 슬레이브 서버를 통해 데이터를 이용할 수 있습니다.

  리플리케이션을 활용하는 방법은 여러가지가 있는데 이 글에서는 가장 간단한 방식인 1마스터/1슬레이브 설정을 소개합니다. 이를 위해 아래의 두 서버로 작업한다고 가정하겠습니다. 또한 이 글에서 사용하는 방법은 MySQL 5.5 이상에서 사용하는 방법으로 그보다 더 낮은 버전은 슬레이브 설정 방법이 약간 다르므로 참고하시기 바랍니다.
MySQL Master system : CentOS 6.4
Master IP Address : 192.168.1.250/24
MySQL Slave system : CentOS 6.4
Slave IP Address: 192.168.1.150/24

MySQL 설치
  iptables이 3306 포트를 허용하도록 /etc/sysconfig/iptables 파일의 적절한 위치에 아래 내용을 추가합니다. 이 작업은 마스터 서버에서만 하면 됩니다.
-A INPUT -p udp -m state --state NEW --dport 3306 -j ACCEPT
-A INPUT -p tcp -m state --state NEW --dport 3306 -j ACCEPT
  iptables를 재시작합니다.
# service iptables restart
  마스터와 슬레이브 서버에 MySQL을 설치하고 서비스를 시작합니다. 아래의 설치 작업은 두 서버에서 모두 실시합니다.
# yum install mysql-server mysql -y
# service mysqld start
# chkconfig mysqld on
  MySQL의 기본 보안 및 Root 패스워드를 설정합니다.
# /usr/bin/mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current password for the root user.  If you've just installed MySQL, and you haven't set the root password yet, the password will be blank, so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!

By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them.  This is intended only for testing, and to make the installation go a bit smoother.  You should remove them before moving into a production environment.

Remove anonymous users? [Y/n]
... Success!
Normally, root should only be allowed to connect from 'localhost'.  This ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
... Success!

By default, MySQL comes with a database named 'test' that anyone can access.  This is also intended only for testing, and should be removed before moving into a production environment.

Remove test database and access to it? [Y/n]
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.

Reload privilege tables now? [Y/n]
... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MySQL installation should now be secure.

Thanks for using MySQL!

MySQL 마스터 설정
  /etc/my.cnf 파일을 열어 [mysqld] 섹션에 아래 내용을 추가합니다.
log-bin = mysql-bin
server-id = 1
  추가가 끝나면 MySQL을 재시작합니다.
# service mysqld restart
  MySQL에 로그인해서 슬레이브 서버가 사용할 계정과 패스워드를 생성합니다. 여기선 슬레이브 계정명으로 mirashi, 패스워드로 passpass를 사용하겠습니다.
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.69-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'mirashi'@'%' IDENTIFIED BY 'passpass';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
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 |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 | myservlab    |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
  파일명(위에선 mysql-bin.000001)과 위치 번호(위에선 106)는 나중에 필요하기 때문에 별도로 적어놓습니다.

MySQL 슬레이브 설정
  /etc/my.cnf 파일을 열어 [mysqld] 섹션에 아래 내용을 추가합니다. 아래 내용을 추가합니다.
server-id = 2
  추가가 끝나면 MySQL을 재시작합니다.
# service mysqld restart
  MySQL에 root 계정으로 로그인해서 아까 마스터 서버에서 SHOW MASTER STATUS; 명령어로 확인했던 마스터 로그 파일에 대한 정보를 슬레이브 서버에 입력합니다.
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.69-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.250', MASTER_USER='mirashi', MASTER_PASSWORD='passpass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;
Query OK, 0 rows affected (0.03 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.1.250
                Master_User: mirashi
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 314
             Relay_Log_File: mysqld-relay-bin.000001
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_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: 314
            Relay_Log_Space: 235
            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
  Slave_IO_State가 'Waiting for master to send event'이며 Seconds_Behind_Master 가 'NULL'이 아니면 리플리케이션이 작동하고 있는 것입니다.

리플리케이션 테스트
마스터 서버
# mysql -u root -p
Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.69-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database myservlab;
Query OK, 1 row affected (0.04 sec)

mysql> use myservlab;
Database changed

mysql> create table sample (c int);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into sample (c) values (1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from sample;
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql>
슬레이브 서버
# mysql -u root -p
Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.69-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use myservlab;
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 sample;
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql>
2013/07/16 13:05 2013/07/16 13:05