반응형

1. Master DB --> Slave DB  백업 방벙

VM Primary DB Clone해서 VM Standby 생성

아니면 디렉토리를 tar 묶거나, zip 명령을 이용하여 압축

tar cf /tmp/db.tar ./data

zip -r /tmp/db.zip ./data

아니면 데이터를 백업 받은 Restore

mysql> FLUSH TABLES WITH READ LOCK;

shell> mysqldump --all-databases --lock-all-tables >dbdump.db

shell> mysqldump --all-databases --master-data >dbdump.db

mysql> UNLOCK TABLES;

 

2. 특정 DB 또는 특정 테이블만 replication 가능하다.

 

3. /etc/my.cnf 환경변수 설정  DB 단위의 Replication 설정

[root@Mmysql ~]# cat /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1                localhost.localdomain localhost

::1                localhost6.localdomain6 localhost6

192.168.10.15   Mmysql

192.168.10.16   Smysql

 

4. Replication 설정

 

Master

Slave

[root@Mmysql ~]# service mysqld stop

Stopping mysqld:                                           [  OK  ]

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

#Replication for Master server

server-id=1

log-bin=mysql-bin

max_binlog_size=100M

max_allowed_packet=128M

expire_logs_days=3

 

위와 같이 하면 모든 DB replication 하는 것이고, 특정 DB 테이블만 동기화 하고자 한다면 binlog_do_db 파라미터를 사용한다.

 

binlog_do_db = tpch 이렇게 특정 DB 나열해 주면 된다.

binlog_do_db = mysql 이렇게 특정 DB 나열해 주면 된다.

binlog_do_db = tpch.customer 이렇게 하면 해당 테이블만 동기화 된다.

[root@Smysql ~]# service mysqld stop

Stopping mysqld:                                           [  OK  ]

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

#Replication for Slave server

server-id=2

log-bin=mysql-bin

max_binlog_size=100M

max_allowed_packet=128M

expire_logs_days=3

[root@Mmysql ~]# service mysqld start

Starting mysqld:                                           [  OK  ]

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

Enter password:

mysql> create user 'repl'@'%' identified by 'repl1122';

Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'repl'@'%' ;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges ;

Query OK, 0 rows affected (0.00 sec)

 

상태에서 backup -- 백업 방법은 위에 나열

 

mysql> show master status ;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000019 |      762 |              |                  |                   |

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

1 row in set (0.00 sec)

 

여기에 있는 file값과 position 값을 slave 설정해 준다.

 

 

[root@Smysql ~]# service mysqld start

Starting mysqld:                                           [  OK  ]

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

Enter password:

mysql> change master to

    -> master_host='Mmysql',

    -> master_user='repl' ,

    -> master_password='repl1122' ,

    -> master_log_file='mysql-bin.000019' ,

    -> master_log_pos=762 ;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave ;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status ;+----------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+

| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File          | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error                                                                                                                                          | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File           | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State                                | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |

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

|                | Mmysql      | repl        |        3306 |            60 | mysql-bin.000019 |                 762 | Smysql-relay-bin.000001 |             4 | mysql-bin.000019      | No               | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |                 762 |             154 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL | No                            |          1593 | Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. |              0 |                |                             |                1 |             | /var/lib/mysql/master.info |         0 |                NULL | Slave has read all relay log; waiting for more updates |              86400 |             | 160621 16:31:27         |                          |                |                    |                    |                   |             0 |                      |              |                    |

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

1 row in set (0.00 sec)

mysql> stop slave ;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

 

VM 이미지를  Copy해서 만들었더니 UUID 똑같아서 안된다고 에러가 . UUID auto.cnf 파일 안에 저장되는데, 해당 파일은 data 디렉토리 밑에 필요에 의해서 생성 시키는 파일이다.

그러므로 그냥 삭제해 주면 된다.

설치된 위치를 파악하기 위하여 디렉토리를 확인

 

[root@Smysql mysql]# cat /etc/passwd | grep mysql

mysql:x:27:501:MySQL Server:/var/lib/mysql:/bin/false

[root@Smysql mysql]# cd /var/lib/mysql

[root@Smysql mysql]# ls

[root@Smysql mysql]# cat auto.cnf

[auto]

server-uuid=5123053e-369d-11e6-9615-080027794699

[root@Smysql mysql]# service mysqld stop

Stopping mysqld:                                           [  OK  ]

[root@Smysql mysql]# rm -rf auto.cnf

[root@Smysql mysql]# cat auto.cnf

cat: auto.cnf: No such file or directory

[root@Smysql mysql]# service mysqld start

Starting mysqld:                                           [  OK  ]

[root@Smysql mysql]# cat auto.cnf

[auto]

server-uuid=6aec2310-3783-11e6-b400-0800274b5271

[root@Smysql mysql]# mysql -u root -p mysql

Enter password:

mysql> start slave ;

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

mysql> show slave status;

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

| Slave_IO_State                   | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File          | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID                          | Master_Info_File           | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State                                | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |

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

| Waiting for master to send event | Mmysql      | repl        |        3306 |            60 | mysql-bin.000019 |                 762 | Smysql-relay-bin.000003 |           320 | mysql-bin.000019      | Yes              | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |                 762 |             528 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |                1 | 5123053e-369d-11e6-9615-080027794699 | /var/lib/mysql/master.info |         0 |                NULL | Slave has read all relay log; waiting for more updates |              86400 |             |                         |                          |                |                    |                    |                   |             0 |                      |              |                    |

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

1 row in set (0.00 sec)

mysql> show master status ;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000019 |      762 |              |                  |                   |

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

1 row in set (0.00 sec)

 

mysql> show databases ;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| sys                |

| tpch               |

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

5 rows in set (0.00 sec)

 

mysql> use tpch

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> show tables ;

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

| Tables_in_tpch |

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

| CUSTOMER       |

| LINEITEM       |

| NATION         |

| ORDERS         |

| PART           |

| PARTSUPP       |

| REGION         |

| SUPPLIER       |

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

8 rows in set (0.00 sec)

 

mysql> create table TEST ( id int ) ;

Query OK, 0 rows affected (0.01 sec)

mysql> insert into TEST values (1) ;

Query OK, 1 row affected (0.01 sec)

mysql> insert into TEST values (2) ;

Query OK, 1 row affected (0.02 sec)

mysql> insert into TEST values (3) ;

Query OK, 1 row affected (0.01 sec)

mysql> commit ;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show master status\G ;

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

             File: mysql-bin.000019

         Position: 1694

     Binlog_Do_DB:

 Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

1 row in set (0.00 sec)

 

 

mysql> show slave status\G ;

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: Mmysql

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000019

          Read_Master_Log_Pos: 1694

               Relay_Log_File: Smysql-relay-bin.000003

                Relay_Log_Pos: 1252

        Relay_Master_Log_File: mysql-bin.000019

             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: 1694

              Relay_Log_Space: 1460

              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:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 1

                  Master_UUID: 5123053e-369d-11e6-9615-080027794699

             Master_Info_File: /var/lib/mysql/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set:

                Auto_Position: 0

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)

 

mysql> show databases ;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| sys                |

| tpch               |

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

5 rows in set (0.00 sec)

 

mysql> use tpch

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> show tables ;

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

| Tables_in_tpch |

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

| CUSTOMER       |

| LINEITEM       |

| NATION         |

| ORDERS         |

| PART           |

| PARTSUPP       |

| REGION         |

| SUPPLIER       |

| TEST           |

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

9 rows in set (0.00 sec)

 

mysql> SELECT * FROM TEST ;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

+------+

3 rows in set (0.00 sec)

 

동기화가 되는 것을 확인 있다.

mysql> show slave hosts\G ;

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

 Server_id: 2

      Host:

      Port: 3306

 Master_id: 1

Slave_UUID: 6aec2310-3783-11e6-b400-0800274b5271

1 row in set (0.00 sec)

 

mysql> show processlist\G ;

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

     Id: 5

   User: root

   Host: localhost

     db: tpch

Command: Query

   Time: 0

  State: starting

   Info: show processlist

*************************** 2. row ***************************

     Id: 7

   User: repl

   Host: 192.168.10.16:56532

     db: NULL

Command: Binlog Dump

   Time: 1839

  State: Master has sent all binlog to slave; waiting for more updates

   Info: NULL

2 rows in set (0.00 sec)

 

 

 

 

5. Slave 사용하던 것을 Master 사용하고자 한다면 다음 명령을 수행해 준다.

 

mysql> stop slave;

mysql> CHANGE MASTER TO MASTER_HOST='';

mysql> RESET SLAVE;

 

6.  모니터링

Slave 서버에서 show slave status 해서 나온 정보들 중에서 Last_Errno, Last_Error, Last_IO_Errno, Last_IO_Error, Last_SQL_Errno, Last_SQL_Error값 들이 있는지 확인하고,

Seconds_Behind_Master 값으로 Master와 log 차이 값(초)를 확인해서 얼마만큼의 갭이 있는지 확인한다.


Slave에서 다음 쿼리 수행

mysql> SELECT  IFNULL(MAX(TIME), 0) as 'Replication_delay(sec)'

          FROM information_schema.PROCESSLIST

          WHERE USER = 'system user'

          AND NOT ( STATE LIKE '%waiting for the slave I/O thread to update it%'

                          OR STATE LIKE '%Waiting for master to send event%'

                          OR STATE LIKE '%Queueing%'

                          OR STATE LIKE '%Slave has read all relay log%'

                          OR STATE LIKE '%Reading event from the relay log%') ;

 

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

| Replication_delay(sec) | 

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

|                      0 |

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

1 row in set (0.00 sec)


 

mysql> show slave status\G ;

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: Mmysql

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000067

          Read_Master_Log_Pos: 36650787

               Relay_Log_File: smysql-relay-bin.000140

                Relay_Log_Pos: 36651000

        Relay_Master_Log_File: mysql-bin.000067

             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: 36650787

              Relay_Log_Space: 36651255

              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:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 1

                  Master_UUID: 5123053e-369d-11e6-9615-080027794699

             Master_Info_File: /var/lib/mysql/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set:

                Auto_Position: 0

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)

반응형

+ Recent posts