'MariaDB'에 해당되는 글 1건

  1. 2017.11.24 mariadb with galera setting

mariadb with galera setting

MySQL 2017. 11. 24. 16:41
반응형
## /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







반응형
Posted by seungkyua@gmail.com
,