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)