DB 이중화 작업(Replication 설정)

• server-id , bin 파일 추가

# vi /etc/my.cnf

[mysqld]

server-id = 1
log-bin = mysql-bin

• mysql 재시작

service mysqld restart

 

DB 사용자 계정 생성

mysql > CREATE USER 'test'@'%' IDENTIFIED BY 'test1234!!';  (계정 생성)
mysql > GRANT REPLICATION SLAVE ON *.* to 'test'@'%' IDENTIFIED BY 'test1234!!'; (권한 부여)
mysql > SELECT user, host FROM mysql.user WHERE user = 'test'; (계정 확인)
mysql > GRANT REPLICATION SLAVE ON *.* TO 'test'@'%'; (권한 확인)
mysql> show grants for 'test'@'%';
+-----------------------------------------------+
| Grants for test@%                            |
+-----------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `test`@`%` |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> flush privileges;

 

• test DB 생성 및 테이블 생성

mysql> create database test;
mysql> use test;
mysql> CREATE TABLE testTable( 
    ->   id INT(11) NOT NULL AUTO_INCREMENT,
    ->   name VARCHAR(20) NOT NULL,
    ->   ouccupation VARCHAR(20) NULL,
    ->   height SMALLINT,
    ->   profile TEXT NULL,
    ->   date  DATETIME,
    ->   CONSTRAINT testTable_PK PRIMARY KEY(id)
    -> );

mysql> DESC testTable;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int         | NO   | PRI | NULL    | auto_increment |
| name        | varchar(20) | NO   |     | NULL    |                |
| ouccupation | varchar(20) | YES  |     | NULL    |                |
| height      | smallint    | YES  |     | NULL    |                |
| profile     | text        | YES  |     | NULL    |                |
| date        | datetime    | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

 

• 읽기 전용 테이블로 Lock

mysql> FLUSH TABLES WITH READ LOCK;

 

• Binary log file, position 조회/기록

mysql> SHOW MASTER STATUS\G;

 

 

---

 

DB 백업 파일 생성 -> Slave로 전송

• 특정 DB 백업 파일 생성

mysqldump -u 'root' -p test > test.sql

 

• 백업 파일 확인

[root@localhost bin]# cat test.sql 
-- MySQL dump 10.13  Distrib 8.0.40, for Linux (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	8.0.40

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `testTable`
--

DROP TABLE IF EXISTS `testTable`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `testTable` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `ouccupation` varchar(20) DEFAULT NULL,
  `height` smallint DEFAULT NULL,
  `profile` text,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
<span style="color:rgb(235, 0, 0)">-- Dumping data for table `testTable`</span>
--

<span style="color:rgb(235, 0, 0)">LOCK TABLES `testTable` WRITE;</span>
/*!40000 ALTER TABLE `testTable` DISABLE KEYS */;
/*!40000 ALTER TABLE `testTable` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-12-05  9:32:34

 

• Slave 서버로 백업 파일 전송

rsync -avzh ./test.sql root@192.168.19.32:/home/test/

포트 지정해야 할 경우
rsync -avzh -e "ssh -p 7722" ./test.sql test@192.168.19.33:/home/test/

 

• 테이블 Lock 해제

mysql> use test;
mysql> unlock tables;

 

Slave 서버 setup 실습

• 복제할 DB 생성

mysql> CREATE database test;

 

• 데이터 복제

mysql -u root -p test < test.sql 
Enter password: 
ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci'

버전이 달라서 오류 발생 시 수정하기
vi test.sql 

33 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; (수정 전)
33 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; (수정 후)

mysql -u root -p test < test.sql 
Enter password:

 

• vi /etc/my.cnf

[mysqld]
server-id = 2

추가하기

 

• mysql 재시작

service mysqld restart

 

• master 서버와 연결

mysql -u root -p 
Enter password: 

mysql> change master to
    -> master_host='192.168.19.33',
    -> master_user='test',
    -> master_password='test1234!!',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=1434;
Query OK, 0 rows affected, 2 warnings (0.19 sec)

 

• Slave 실행

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\g;

 

• 연동 테스트

mysql> create database test3;

Slave 서버 접속 후 test3 생성되었는지 확인
mysql > show databases;

 

참고 : 

• Slave 리셋

mysql> stop slave;
mysql> reset slave all;
mysql> show slave status\g;