반응형
## /etc/mysql/my.cnf 설정 파일
[mysqld]
# base directory
datadir=/var/lib/mysql
tmpdir=/tmp
# Charset
character_set_server=utf8
collation_server=utf8_unicode_ci
skip-character-set-client-handshake
# Logging
slow_query_log=on
slow_query_log_file=/var/log/mysql/mariadb-slow.log
log_warnings=2
# General logging has huge performance penalty therefore is disabled by default
general_log=off
general_log_file=/var/log/mysql/mariadb-error.log
long_query_time=3
log_queries_not_using_indexes=on
# Networking
bind_address=0.0.0.0
port=3306
# When a client connects, the server will perform hostname resolution,
# and when DNS is slow, establishing the connection will become slow as well.
# It is therefore recommended to start the server with skip-name-resolve to
# disable all DNS lookups. The only limitation is that the GRANT statements
# must then use IP addresses only.
skip_name_resolve
# Tuning
user=mysql
max_allowed_packet=256M
open_files_limit=10240
max_connections=8192
max-connect-errors=1000000
join_buffer_size = 192K
tmp_table_size = 16M
max_heap_table_size = 16M
## Generally, it is unwise to set the query cache to be larger than 64-128M
## as the costs associated with maintaining the cache outweigh the performance
## gains.
## The query cache is a well known bottleneck that can be seen even when
## concurrency is moderate. The best option is to disable it from day 1
## by setting query_cache_size=0 (now the default on MySQL 5.6)
## and to use other ways to speed up read queries: good indexing, adding
## replicas to spread the read load or using an external cache.
query_cache_size=0
query_cache_type=0
sync_binlog=0
thread_cache_size=16
table_open_cache=2048
table_definition_cache=1024
#
# InnoDB
#
# The buffer pool is where data and indexes are cached: having it as large as possible
# will ensure you use memory and not disks for most read operations.
# Typical values are 50..75% of available RAM.
# TODO(tomasz.paszkowski): This needs to by dynamic based on available RAM.
innodb_buffer_pool_size=10G
innodb_log_file_size=128M
innodb_file_per_table=1
innodb_doublewrite=0
innodb_file_format=Barracuda
innodb_flush_method=O_DIRECT
innodb_io_capacity=500
innodb_old_blocks_time=1000
innodb_read_io_threads=8
innodb_write_io_threads=8
# Clustering
binlog_format=ROW
default-storage-engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=2
innodb_locks_unsafe_for_binlog=1
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name=mariadb
wsrep_node_name=mariadb-0.mariadb-discovery
wsrep_node_address=172.16.10.252
wsrep_cluster_address="gcomm://mariadb-1.mariadb-discovery:4567,mariadb-2.mariadb-discovery:4567"
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=root:password
wsrep_provider_options="gcache.size=512M; gcache.name=/tmp/galera.cache; gcache.page_size=128M; gmcast.listen_addr=tcp://0.0.0.0:4567"
wsrep_slave_threads=16
wsrep_on=1
[mysqldump]
max-allowed-packet=16M
[client]
default_character_set=utf8
protocol=tcp
port=3306
connect_timeout=10
## sync 상태 보기 (각 mariadb 서버에서)
MariaDB [(none)]> show status like 'wsrep_%';
+------------------------------+-------------------------------------------------------------+
| Variable_name | Value |
+------------------------------+-------------------------------------------------------------+
| wsrep_apply_oooe | 0.007694 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.014445 |
| wsrep_causal_reads | 0 |
| wsrep_cert_deps_distance | 25.653383 |
| wsrep_cert_index_size | 67 |
| wsrep_cert_interval | 0.007814 |
| wsrep_cluster_conf_id | 3 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 86cf6166-d00b-11e7-92f6-bf254b8a19b3 |
| wsrep_cluster_status | Primary |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.006594 |
| wsrep_connected | ON |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0.000495221/0.000788639/0.00164732/0.00034555/19 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_sent | 0 |
| wsrep_gcomm_uuid | f14761b1-d026-11e7-a9c6-43e9a12bd841 |
| wsrep_incoming_addresses | 192.168.236.97:3306,192.168.28.197:3306,192.168.105.20:3306 |
| wsrep_last_committed | 12525 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_cached_downto | 6127 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_commits | 970 |
| wsrep_local_index | 2 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.143574 |
| wsrep_local_recv_queue_max | 40 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000978 |
| wsrep_local_send_queue_max | 2 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_local_state_uuid | 86cf6166-d00b-11e7-92f6-bf254b8a19b3 |
| wsrep_protocol_version | 7 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 25.3.20(r3703) |
| wsrep_ready | ON |
| wsrep_received | 5579 |
| wsrep_received_bytes | 4241688 |
| wsrep_repl_data_bytes | 441822 |
| wsrep_repl_keys | 4283 |
| wsrep_repl_keys_bytes | 56574 |
| wsrep_repl_other_bytes | 0 |
| wsrep_replicated | 970 |
| wsrep_replicated_bytes | 560476 |
| wsrep_thread_count | 13 |
+------------------------------+-------------------------------------------------------------+
wsrep_cluster_conf_id : 모든 db 에 똑같은 id 로 설정되어야 함
wsrep_cluster_size : 클러스터의 노드 갯수
wsrep_cluster_state_uuid : 고유 클러스터 ID 로 클러스터의 노드는 같은 값을 가져야 함
wsrep_cluster_status : 노드의 역할
wsrep_incoming_addresses : 클러스터 멤버의 ip
wsrep_local_state_comment : 해당 노드가 클러스터의 멤버인지를 확인
wsrep_ready : 노드가 준비된 상태
## docker 내에서 클러스터 시작 (첫번째 클러스터)
$ sed -i 's/^safe_to_bootstrap:.*/safe_to_bootstrap: 1/' /var/lib/mysql/grastate.dat || :
$ docker-entrypoint.sh mysqld --wsrep-new-cluster
## docker 내에서 클러스터 시작 (두번째, 세번째 클러스터)
$ sed -i 's/^safe_to_bootstrap:.*/safe_to_bootstrap: 1/' /var/lib/mysql/grastate.dat || :
$ docker-entrypoint.sh mysqld
반응형
