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으로 설치
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으로 설치
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..
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!
자, 다시 설치 시도.
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 생성
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 수정
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 스크립트 실행
Master]# mysql_install_db --user=mysql Installing MySQL system tables... OK Filling help tables... OK ...
Master Mysql 구동
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 의 상태 확인
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에서 계정생성
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave-ip' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.00 sec)
Slave my.cnf 수정
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 구동
Slave]# /usr/bin/mysqld_safe &
Replication 설정을 하기전에 Master에서 Table Lock
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 구동
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을 풀어줌
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을 생성
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 확인
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 확인
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 구성