mysql replication 구성
1. mysql rpm 설치
mysql 홈페이지에서 최신버전 다운로드
http://www.mysql.com/downloads/mysql/
MySQL Community Server 5.5.20 – Linux – Generic
우리가 필요한 Client Utilities와 MySQL Server를 받는다.
(MySQL-client-5.5.20-1.linux2.6.x86_64.rpm, MySQL-server-5.5.20-1.linux2.6.x86_64.rpm)
Master에 Mysql rpm으로 설치
|
1 2 3 4 5 6 7 |
Master]# rpm -ivh MySQL-server-5.5.20-1.linux2.6.x86_64.rpm Preparing... ########################################### [100%] 1:MySQL-server ########################################### [100%] ... Master]# rpm -ivh MySQL-client-5.5.20-1.linux2.6.x86_64.rpm Preparing... ########################################### [100%] 1:MySQL-client ########################################### [100%] |
Slave에 Mysql rpm으로 설치
|
1 2 3 4 |
Slave]# rpm -ivh MySQL-server-5.5.20-1.linux2.6.x86_64.rpm error: Failed dependencies: MySQL conflicts with mysql-5.0.95-1.el5_7.1.x86_64 MySQL conflicts with mysql-5.0.95-1.el5_7.1.i386 |
흐미.. dependencies 때문에 설치가 안됨,,ㅠㅍㅠ
이미 설치된 mysql 확인하고 remove..
|
1 2 3 4 5 6 7 8 9 10 11 |
Slave]# yum list installed |grep mysql mysql.i386 5.0.95-1.el5_7.1 installed mysql.x86_64 5.0.95-1.el5_7.1 installed mysql-devel.i386 5.0.95-1.el5_7.1 installed mysql-devel.x86_64 5.0.95-1.el5_7.1 installed Slave]# yum remove mysql* ... Removed: mysql.i386 0:5.0.95-1.el5_7.1 mysql.x86_64 0:5.0.95-1.el5_7.1 mysql-devel.i386 0:5.0.95-1.el5_7.1 mysql-devel.x86_64 0:5.0.95-1.el5_7.1 Complete! |
자, 다시 설치 시도.
|
1 2 3 4 5 6 7 |
Slave]# rpm -ivh MySQL-server-5.5.20-1.linux2.6.x86_64.rpm Preparing... ########################################### [100%] 1:MySQL-server ########################################### [100%] ... Slave]# rpm -ivh MySQL-client-5.5.20-1.linux2.6.x86_64.rpm Preparing... ########################################### [100%] 1:MySQL-client ########################################### [100%] |
my.conf 생성
|
1 2 |
Master]# cp /usr/share/mysql/my-innodb-heavy-4G.cnf /etc/my.cnf Slave]# cp /usr/share/mysql/my-innodb-heavy-4G.cnf /etc/my.cnf |
mysql replication을 위한 설정
참고로 A —> B 은 A설정을 B으로 변경
—> B 는 B라인 추가
Master my.cnf 수정
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Master]# vi /etc/my.cnf ... ---> datadir = /data1/mysql max_connections = 100 ---> max_connections = 300 table_open_cache = 2048 ---> table_open_cache = 2048 binlog_cache_size = 1M ---> binlog_cache_size = 2M default-storage-engine = MYISAM ---> default-storage-engine = INNODB bulk_insert_buffer_size = 64M ---> bulk_insert_buffer_size = 16M myisam_sort_buffer_size = 128M ---> myisam_sort_buffer_size = 64M innodb_buffer_pool_size = 2G ---> innodb_buffer_pool_size = 3G #innodb_data_home_dir = <directory> ---> innodb_data_home_dir = /data1/mysql/ innodb_write_io_threads = 8 ---> innodb_write_io_threads = 4 innodb_read_io_threads = 8 ---> innodb_read_io_threads = 4 innodb_thread_concurrency = 16 ---> innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 1 ---> innodb_flush_log_at_trx_commit = 2 #innodb_log_group_home_dir ---> innodb_log_group_home_dir = /data1/mysql/ ## 추가부분 ---> binlog_cache_size = 2M # binlog cache 사이즈 ---> max_binlog_size = 500M # bin로그 파일 사이즈 ---> expire_logs_days = 20 # 보관기간 ---> init_connect=SET collation_connection = utf8_general_ci ---> init_connect=SET NAMES utf8 ---> character-set-server=utf8 ---> collation-server=utf8_general_ci ---> default-character-set=utf8 ... Master]# mkdir /data1/mysql Master]# chown -R mysql.mysql /data1/mysql |
mysql_install_db 스크립트 실행
|
1 2 3 4 5 6 |
Master]# mysql_install_db --user=mysql Installing MySQL system tables... OK Filling help tables... OK ... |
Master Mysql 구동
|
1 2 3 4 5 |
Master]# /usr/bin/mysqld_safe & Master]# ps -ef |grep mysql root 13019 9307 0 16:10 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe mysql 13812 13019 4 16:10 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/data1/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/data1/mysql/master.err --open-files-limit=8192 --pid-file=/data1/mysql/master.pid --socket=/var/lib/mysql/mysql.sock --port=3306 root 13833 9307 0 16:10 pts/0 00:00:00 grep mysql |
Mysql에 접속해서 Master 의 상태 확인
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Master]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.20-log MySQL Community Server (GPL) Copyright (c) 2000, 2011, 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> mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 | 107 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> |
Mysql Slave 가 replication 수행을 위해 접속할 계정 생성
master에서 계정생성
|
1 2 |
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave-ip' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.00 sec) |
Slave my.cnf 수정
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
Slave]# vi /etc/my.cnf ... ---> slave-skip-errors = 1062 ---> binlog-ignore-db = mysql ---> binlog-ignore-db = test ---> binlog-ignore-db = davidoff ---> datadir = /data1/mysql table_open_cache = 2048 ---> table_open_cache = 256 query_cache_size = 64M ---> query_cache_size = 16M default-storage-engine = MYISAM ---> default-storage-engine = INNODB tmp_table_size = 64M ---> tmp_table_size = 16M server-id = 1 ---> server-id = 2 #master-host = <hostname> ---> # master-host = #master-user = <username> ---> # master-user = repluser #master-password = <password> ---> # master-password = key_buffer_size = 32M ---> key_buffer_size = 16M bulk_insert_buffer_size = 64M ---> bulk_insert_buffer_size = 16M myisam_sort_buffer_size = 128M ---> myisam_sort_buffer_size = 64M innodb_buffer_pool_size = 2G ---> innodb_buffer_pool_size = 500M #innodb_data_home_dir = <directory> ---> innodb_data_home_dir = /data1/mysql/ innodb_write_io_threads = 8 ---> #innodb_write_io_threads = 8 innodb_read_io_threads = 8 ---> #innodb_read_io_threads = 8 --- > innodb_file_io_threads = 8 ## 추가부분 ---> binlog_cache_size = 2M # binlog cache 사이즈 ---> max_binlog_size = 500M # bin로그 파일 사이즈 ---> init_connect=SET collation_connection = utf8_general_ci ---> init_connect=SET NAMES utf8 ---> character-set-server=utf8 ---> collation-server=utf8_general_ci max_allowed_packet = 16M ---> max_allowed_packet = 64M ---> default-character-set=utf8 key_buffer_size = 512M ---> key_buffer_size = 128M sort_buffer_size = 512M ---> sort_buffer_size = 128M ... Slave]# mkdir /data1/mysql Slave]# chown -R mysql.mysql /data1/mysql |
Slave Mysql 구동
|
1 |
Slave]# /usr/bin/mysqld_safe & |
Replication 설정을 하기전에 Master에서 Table Lock
|
1 2 3 4 5 6 7 8 9 10 |
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.000004 | 264 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) |
Slave장비에서 Replication을 위한 Slave 구동
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
Slave]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.20-log MySQL Community Server (GPL) Copyright (c) 2000, 2011, 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='master-ip', master_user='repl', master_port=3306, master_password='', master_log_file='mysql-bin.000004', master_log_pos=630; Query OK, 0 rows affected (0.01 sec) mysql> START SLAVE; Query OK, 0 rows affected (0.00 sec) mysql> mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: master-ip Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 630 Relay_Log_File: master-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Connecting 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: 630 Relay_Log_Space: 107 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1045 Last_IO_Error: error connecting to master 'repl@master-ip:3306' - retry-time: 60 retries: 86400 Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 1 row in set (0.00 sec) mysql> |
Master의 lock을 풀어줌
|
1 2 |
mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) |
* Replication 체크 사항
동기화
– Master Server의 Position 과 Slave Server의 Read_Master_Log_Pos 값이 동일해야 한다.
(Slave Server의 Read_Master_Log_Pos 값과 Exec_Master_Log_Pos 값도 동일 해야한다)
– Slave Server의 Slave_IO_Running 과 Slave_SQL_Running 값이 모두 Yes 이어야 한다. (버전에 따라 컬럼이 다른듯)
– Slave Server에서 Pos 값이 변하는지 확인.
– Master Server에 테이블을 만들어보고 insert 해보고 동기화가 되는지 확인.
대기
– Slave Server의 Seconds_Behind_Master 값이 크면 클수록 지연 되고 있다는 뜻.
replication이 정상적으로 동작하는지 확인
Master에서 새로운 DB와 table을 생성
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> create database apollo89; Query OK, 1 row affected (0.00 sec) mysql> CREATE TABLE IF NOT EXISTS `apollo89`.`TBL_test` ( `ID` INT NOT NULL AUTO_INCREMENT COMMENT '', `DIVISION` VARCHAR(30) NOT NULL COMMENT '', `NAME` VARCHAR(30) NOT NULL COMMENT '', PRIMARY KEY (`ID`) ); mysql> insert into TBL_test values ('','1','a'); Query OK, 1 row affected, 1 warning (0.00 sec) |
Master에서 position 확인
|
1 2 3 4 5 6 7 |
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 | 1835 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) |
slave 에서 Read_Master_Log_Pos 확인
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master-ip Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 1835 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 1458 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: 1835 Relay_Log_Space: 1616 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 1 row in set (0.00 sec) mysql> |
Master의 postion과 Slave의 Read_Master_Log_Pos이 동일하다!!
참고
http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=01_1&m_no=22382&cat1=753&cat2=0&cat3=0&lang=k
http://shonm.tistory.com/435
http://ksewookk.blog.me/100169519921
http://gr8.kr/120066131409?Redirect=Log&from=postView
http://yupmin.com/entry/mysql_replication
2 Responses to mysql replication 구성