변군이글루

[mysql] MySQL Replication 설정 본문

및___APM

[mysql] MySQL Replication 설정

변군 변군이글루 2014.04.12 00:25

MySQL Replication 설정

 

Master Server

 

1.환경설정

[root@svm101 ~]# vi /etc/my.cnf

[mysqld]

...

#log setting

log-bin = mysql-bin #로그파일명

max_binlog_size = 100M #로그파일크기

expire_logs_days = 7 # 로그보존주기

 

#Replication for master server

server-id = 3 #서버 식별자(유니크)

binlog_do_db = racktables #리플리케이션DB(생략시엔 전체DB를 리플리케이션함)

#binlog_do_db = test1 #여러 개의 DB일경우, 계속 추가

#binlog_ignore_db = test2 #Replication 제외 DB 지정

 

2.mysql 재가동(MasterServer)

[root@svm101 ~]# service mysqld restart

 

[root@svm101 ~]# mysql -u root -p

Enter password:

 

3.유저추가(MasterServer)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';

Query OK, 0 rows affected (0.00 sec)

 

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

 

// DB Write 금지

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 |   873110 | zabbix,racktables_db |                  |

+------------------+----------+----------------------+------------------+

1 row in set (0.00 sec)

 

4.데이터 백업(MasterServer)

[root@svm101 ~]# mysqldump -u root -p zabbix > Backup_zabbix.sql

Enter password:

[root@svm101 ~]# mysqldump -u root -p racktables_db > Backup_racktables_db.sql

Enter password:

 

[root@svm101 ~]# ls -l | grep sql

-rw-r--r--  1 root root  262824 2014-04-09 00:43 Backup_racktables_db.sql

-rw-r--r--  1 root root 2027131 2014-04-09 00:43 Backup_zabbix.sql

 

// DB Write 금지해제

mysql> UNLOCK TABLES;

Query OK, 0 rows affected (0.00 sec)

 

mysql> SHOW MASTER STATUS\G

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

            File: mysql-bin.000001

        Position: 895793

    Binlog_Do_DB: zabbix,racktables_db

Binlog_Ignore_DB:

1 row in set (0.00 sec)

 

Slave Server

 

1.환경설정

[root@cvm121 ~]# vi /etc/my.cnf

[mysqld]

...

#Replication for master server

server-id = 4 #서버 식별자(유니크)

replicate-do-db = zabbix #리플리케이션DB(생략시엔 전체DB를 리플리케이션함)

replicate-do-db = racktables_db #여러 개의 DB일경우, 계속 추가

 

[root@cvm121 ~]# mysql -u root -p

Enter password:

 

mysql> create database zabbix;

Query OK, 1 row affected (0.00 sec)

 

mysql> create database racktables_db;

Query OK, 1 row affected (0.00 sec)

 

[root@cvm121 ~]# mysql -u root -p racktables < Backup_racktables.sql

Enter password:

 

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=846712;

Query OK, 0 rows affected (0.01 sec)

 

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> flush privileges;

Query OK, 0 rows affected (0.00 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.0.100

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 871719

               Relay_Log_File: mysqld-relay-bin.000002

                Relay_Log_Pos: 25258

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: zabbix,racktables_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: 871719

              Relay_Log_Space: 25414

              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>

 

 

원본 URL :

http://juhyunsik.blogspot.kr/2012/05/mysql-replication.html

http://www.zosel.net/entry/MySQL-Replication-%EC%84%A4%EC%A0%95Master-Slave-Master-Master

 

 

0 Comments
댓글쓰기 폼