MariaDB Galera Cluster 10.0.14

티스토리 뷰

MariaDB Galera Cluster 10.0.14 - GTID Replication

Posted by 해적하록 on 2014. 11. 28. 15:50 in MARIADB /

https://mariadb.com/blog/mariadb-10-gtid-explained

선행작업으로 총 3대의 노드로 MariaDB Galera Cluster 를 구축해 놓아야 합니다.
이 후 특정 MASTER에 SLAVE 를 복제로 연결하는 작업을 아래에서 진행 하겠습니다.

참고로 SLAVE 서버역시 부모노드들과 동일한 MariaDB Galera 10.0.14 버전을 설치해야 하며
WSREP 옵션은 my.cnf 파일에서 모두 주석처리 합니다.

-- STEP1. SLAVE 서버 설치 및 환경설정 (설치작업 정보 생략)

[my.cnf - SLAVE 정보]

###############################################################

# my.cnf #

###############################################################

[client]

port = 3306

socket = /tmp/mysql.sock

default-character-set = utf8

#password = your_password

[mysqld]

server-id = 123004

user = galera

port = 3306

socket = /tmp/mysql.sock

basedir = /usr/mariadb/mariadb-galera-10.0.14

datadir = /data/mariadb/DBSpace_Galera

tmpdir = /tmp

pid-file = /tmp/mysqld.pid

init_connect = "SET NAMES utf8;"

character-set-server = utf8

collation-server = utf8_general_ci

skip-character-set-client-handshake

skip-host-cache

skip-name-resolve

skip-external-locking

skip-slave-start = 1

#bind-address = 0.0.0.0

bind-address = 192.168.53.236

#event_scheduler = ON

general-log = OFF

general-log-file = /data/mariadb/mariadb-galera-10.0.14/admin/log/general.log

sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

log_output = FILE

slow-query-log = ON

slow-query-log-file = /data/mariadb/mariadb-galera-10.0.14/admin/log/slow.log

long_query_time = 0.2

log-queries-not-using-indexes = OFF

#log-bin = /data/mariadb/DBSpace_Galera/BINLOG/OracleLinux-004

binlog_cache_size = 2M

binlog_format = ROW

expire_logs_days = 3

log-warnings = 2

log-error = /data/mariadb/mariadb-galera-10.0.14/admin/log/error.log

sync_binlog = 0

back_log = 1024

max_connections = 1000

max_connect_errors = 99999

table_open_cache = 15000

max_allowed_packet = 16M

max_heap_table_size = 512M

read_buffer_size = 1M

read_rnd_buffer_size = 1M

sort_buffer_size = 1M

join_buffer_size = 1M

thread_cache_size = 2048

thread_concurrency = 8

#query_cache_type = DEMAND

#query_cache_size = 64M

#query_cache_limit = 1M

query_cache_size = 0

query_cache_type = 0

thread_stack = 512K

tmp_table_size = 16M

ft_min_word_len = 4

net_buffer_length = 2K

wait_timeout = 30

interactive_timeout = 28800

connect_timeout = 300

default-storage-engine = INNODB

lower_case_table_names = 1

transaction_isolation = READ-COMMITTED

max_binlog_size = 250M

log_bin_trust_function_creators = 1

open-files-limit = 65535

#explicit_defaults_for_timestamp = TRUE

########################################

# INNODB Specific options

########################################

innodb_data_home_dir = /data/mariadb/DBSpace_Galera/DATA

innodb_log_group_home_dir = /data/mariadb/DBSpace_Galera/IBLOG

innodb_buffer_pool_size = 200M

#innodb_buffer_pool_instance = 8

innodb_additional_mem_pool_size = 50M

innodb-buffer-pool-instances = 4

innodb_data_file_path = ibdata1:500M:autoextend

innodb_file_per_table = 1

innodb_write_io_threads = 8

innodb_read_io_threads = 8

innodb_force_recovery = 0

innodb_thread_concurrency = 12

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 16M

innodb_log_file_size = 300M

innodb_log_files_in_group = 5

innodb_max_dirty_pages_pct = 80

innodb_flush_method = O_DIRECT

innodb_lock_wait_timeout = 30

innodb_locks_unsafe_for_binlog = 1

innodb_autoinc_lock_mode = 2

innodb_doublewrite = ON

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

innodb_status_file = 1

innodb_adaptive_flushing = 1

innodb_use_native_aio = 1

#innodb_purge_thread = 1

innodb-purge-threads = 1

innodb_fast_shutdown = 0

#innodb_stats_auto_recalc = OFF

#innodb_stats_persistent = OFF

#innodb_sort_buffer_size = 4M

skip-innodb_adaptive_hash_index

performance_schema

#FEDERATED <- Error !!!

#federated

#################################################################

# Replication related settings

#################################################################

#replicate-ignore-db = perf_mon

#replicate-ignore-db = moniter

replicate-ignore-db = mysql

##replicate-do-db = test

#relay_log_purge = OFF

relay-log = /data/mariadb/DBSpace_Galera/RELAYLOG/mysql_relay

#################################################################

# MyISAM Specific options

#################################################################

key_buffer_size = 128M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 64M

myisam_max_sort_file_size = 64M

myisam_repair_threads = 1

#myisam_recover

myisam-recover-options

#################################################################

## WSREP options

#################################################################

##wsrep_cluster_address = 'gcomm://'

#wsrep_cluster_address = 'gcomm://10.10.0.193'

#wsrep_cluster_address = 'gcomm://10.10.0.193,10.10.0.194,10.10.0.196'

##wsrep_provider = /usr/lib64/galera/libgalera_smm.so

#wsrep_set_mothod = rsync

##wsrep_cluster_name = "GCSVC1"

##wsrep_slave_threads = 1

##wsrep_certify_nonPK = 1

##wsrep_max_ws_rows = 131072

##wsrep_max_ws_size = 1073741824

##wsrep_debug = 0

##wsrep_convert_LOCK_to_trx = 0

##wsrep_retry_autocommit = 1

##wsrep_auto_increment_control = 1

##wsrep_drupal_282555_workaround = 0

##wsrep_causal_reads = 0

#wsrep_notify_cmd =

# Galera Node Configuration

##wsrep_node_address = 192.168.53.236

##wsrep_node_name = OracleLinux-004

#wsrep_provider_options ="gcache.size=4G"

#################################################################

## WSREP State Transfer options

#################################################################

#wsrep_sst_method = xtrabackup

##wsrep_sst_method = mysqldump

##wsrep_sst_receive_address = 192.168.53.236

##wsrep_sst_auth = root:galera$321

#################################################################

[mysqldump]

quick

max_allowed_packet = 64M

[mysql]

no-auto-rehash

default-character-set = utf8

prompt = '[\h] (\d) \R:\m> '

[myisamchk]

key_buffer_size = 64M

sort_buffer_size = 16M

read_buffer = 16M

write_buffer = 16M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

open-files-limit = 65535

-- STEP2. MASTER NODE - gtid 설정 및 my.cnf 설정정보 변경

SET GLOBAL gtid_domain_id = 1;

my.cnf 파일 수정

# Domain = 1 for active master: server A

gtid-domain-id=1

작업 완료 후 각 Galera Node 서버를 재 시작 합니다.

종료순서는 시작과 반대로 Node3->Node2->Node1 순이며 하나가 완전히 완료 후 다음 Node 를 종료 합니다.

서버 상황에 따라 종료시간이 오래 소요될 수 도 있습니다. 각 노드 재시작 완료 후 show status like '%wsrep%' 명령어로 wsrep_local_index 값을 확인, 노드 순서를 확인할 수 있습니다.

-- STEP3. Galera Master Node. 1 에서 GTID 정보 확인 > "0-123001-8" 값이 GTID 입니다.

########################################

### Server Connection List ###

########################################

1. Galera Clust Node.1 [192.168.53.233]

2. Galera Clust Node.2 [192.168.53.234]

3. Galera Clust Node.3 [192.168.53.235]

9. Exit

########################################

[Input Number] : 1

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 86

Server version: 10.0.14-MariaDB-wsrep-log MariaDB Server, wsrep_25.10.r4144

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[192.168.53.233] ((none)) 15:05>

[192.168.53.233] ((none)) 15:05> show master status\G

*************************** 1. row ***************************

File: OracleLinux-001.000054

Position: 510

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

[192.168.53.233] ((none)) 15:05>

[192.168.53.233] ((none)) 15:05> select binlog_gtid_pos('OracleLinux-001.000054', 510);

+------------------------------------------------+

| binlog_gtid_pos('OracleLinux-001.000054', 510) |

+------------------------------------------------+

| 0-123001-8 |

+------------------------------------------------+

1 row in set (0.02 sec)

[192.168.53.233] ((none)) 15:13>

-- STEP4. MASTER 서버마다 (각 노드마다 설정) 복제용 계정을 생성 합니다.

GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO repjob_user @'%' IDENTIFIED BY ‘qwer!234’;

Flush Privileges;

-- STEP5. SLAVE 서버 설정 작업을 진행 합니다. (MASTER NODE 첫번째로 연결 합니다.)

[localhost] ((none)) 14:58> STOP SLAVE;

Query OK, 0 rows affected (0.01 sec)

[localhost] ((none)) 15:06> SET GLOBAL gtid_slave_pos = '0-123001-8'; Query OK, 0 rows affected (0.01 sec)

[localhost] ((none)) 15:07> CHANGE MASTER TO MASTER_HOST='192.168.53.233', MASTER_USER='repjob_user', MASTER_PASSWORD='qwer!234', MASTER_PORT=3306, master_use_gtid=slave_pos;

Query OK, 0 rows affected (0.01 sec)

[localhost] ((none)) 15:07> START SLAVE;

Query OK, 0 rows affected (0.01 sec)

[localhost] ((none)) 15:07> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.53.233

Master_User: repjob_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: OracleLinux-001.000056

Read_Master_Log_Pos: 1388

Relay_Log_File: mysql_relay.000002

Relay_Log_Pos: 1681

Relay_Master_Log_File: OracleLinux-001.000056

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: 1388

Relay_Log_Space: 1974

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: 123001

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: Slave_Pos

Gtid_IO_Pos: 1-123001-4,0-123001-8

1 row in set (0.00 sec)

MASTER 서버의 0-123001-8 GTID 로 복제 시 기존에 DB, TABLE 정보는 동기화가 되지 않기 때문에

과거에 생성한 정보는 덤프를 통해서 복원해 두어야 합니다.

복원 작업 시 GTID 를 지원받기 위해서는 rsync, mysqldump 를 이용해야 하며

xtrabackup 을 통해 복원은 지원여부를 확인해야 합니다.

xtrabackup-V2의 경우 Galera 5.5.37 과 10.0.10 부터 지원 합니다.

Last_SQL_Error: 정보가 없다면 정상적으로 복제 구성이 완료된 상황 입니다.

만약 연결된 MASTER NODE를 변경하고 할 경우 아래 커맨드 정보를 이용합니다.

-- Setting up a new slave (MASTER NODE 변경 시 gtid 오류가 발생 할 경우, 최초 구성처럼 gtid 값을 수동으로 지정)

> SET GLOBAL gtid_slave_pos='1-123001-10,0-123001-8';

> CHANGE MASTER TO MASTER_HOST='192.168.53.233', MASTER_USER='repjob_user', MASTER_PASSWORD='qwer!234', MASTER_PORT=3306, master_use_gtid=slave_pos;

> START SLAVE;

-- Switch a slave to use GTID

[localhost] ((none)) 14:29> STOP SLAVE;

[localhost] ((none)) 14:29> CHANGE MASTER TO master_use_gtid=current_pos;

[localhost] ((none)) 14:29> START SLAVE;

-- Change master

> STOP SLAVE;

> CHANGE MASTER TO MASTER_HOST='192.168.53.234', MASTER_USER='repjob_user', MASTER_PASSWORD='qwer!234', MASTER_PORT=3306;

> START SLAVE;

> SHOW SLAVE STATUS\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.53.234

Master_User: repjob_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: OracleLinux-002.000012

Read_Master_Log_Pos: 521

Relay_Log_File: mysql_relay.000002

Relay_Log_Pos: 814

Relay_Master_Log_File: OracleLinux-002.000012

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql

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: 521

Relay_Log_Space: 1107

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: 123002

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: Current_Pos

Gtid_IO_Pos: 1-123001-1,0-123002-1

1 row in set (0.00 sec)

Blog Content

티스토리 뷰

MariaDB Galera Cluster 10.0.14 - GTID Replication

Comments

티스토리툴바

« 2024/05 »
일	월	화	수	목	금	토
			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