MYSQL DB 동기화 방법이다.
-- [master] --
- 환경 변수에 추가 -
# vi /etc/my.cnf
[mysqld]
log-bin = mysql-bin
server-id = 1
- mysql Replication 계정 생성 -
# 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.71-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 'backup(계정)'@'%' IDENTIFIED BY '123qwe(암호)';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
- 추가 됐는지 확인 -
mysql> select user,host,password from user;
+--------+-----------+------------------+
| user | host | password |
+--------+-----------+------------------+
| root | localhost | |
| root | master | |
| root | 127.0.0.1 | |
| | localhost | |
| | master | |
| backup | % | 64f6539969715ad7 |
+--------+-----------+------------------+
6 rows in set (0.00 sec)
- master 상태 확인 -
mysql> use mysql;
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 master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 195 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
-- [slave] --
- slave 장비에 master 정보 입력 -
# 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.71-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.0.247', MASTER_USER='backup', MASTER_PORT=3306, MASTER_PASSWORD='123qwe', MASTER_LOG_FILE='mysql-bin.000004';
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 52.2.12.247
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 195
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000004
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: 195
Relay_Log_Space: 407
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)
빨간 글씨처럼 Waiting for master to send event 라고뜨면 동기화 준비 중이다.
정상적으로 작동 되고 있다.
테스트 해보면
-- [master] --
mysql> CREATE DATABASE test_db99;
Query OK, 1 row affected (0.00 sec)
mysql>
db 생성해보면
-- [slave] --
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| test_db99 |
+--------------------+
8 rows in set (0.00 sec)
mysql>
위와 같이 slave 에도 db가 생성한것을 확인 할수 있다.
모든 설정 초기화
server
mysql> reset master
slave
mysql> reset slave