설치 환경 : CentOS7 (64Bit)
설치 DB : MariaDB 10.1
MariaDB.org 에서 권장하는 다음 URL을 통해서 해당 Linux Repository를 다운 받은 후 Yum 명령어를 이용하여 설치 한다.
https://downloads.mariadb.org/mariadb/repositories/#mirror=kaist
위 사이트에서 설치 하고자 하는 서버 및 MariaDB를 선택하게 되면 Repository파일을 만들 수 있는 정보를 준다.
해당 정보를 이용하여 /etc/yum.repos.d/MariaDB.repo 파일을 생성하고 다음의 내용을 추가 한다.
필요한 패키지 인스톨
[root@localhost ~]# yum install rsync nmap lsof perl-DBI nc
1. MariaDB 설치
0. SELinux Disable
[root@MariaCluster1 ~]# vi /etc/selinux/config
SELINUX=disabled
[root@MariaCluster1 ~]# reboot
[root@MariaCluster1 ~]# sestatus
SELinux status: disabled
1. FireWall Disable & Stop
[root@MariaCluster1 ~]# systemctl status firewalld
[root@MariaCluster1 ~]# systemctl disable firewalld
[root@MariaCluster1 ~]# systemctl stop firewalld
[root@MariaCluster1 ~]# systemctl status firewalld
2. MariaDB Install
root 유저로 다음 명령어 수행 (참조 URL : https://mariadb.com/kb/en/mariadb/yum/ )
[root@MariaCluster1 yum.repos.d]# yum install MariaDB-server MariaDB-client MariaDB-compat galera socat jemalloc
Installed:
MariaDB-client.x86_64 0:10.1.16-1.el7.centos
MariaDB-compat.x86_64 0:10.1.16-1.el7.centos
MariaDB-server.x86_64 0:10.1.16-1.el7.centos
MariaDB-shared.x86_64 0:10.1.16-1.el7.centos
galera.x86_64 0:25.3.15-1.rhel7.el7.centos
jemalloc.x86_64 0:3.6.0-1.el7
socat.x86_64 0:1.7.2.2-5.el7
Dependency Installed:
MariaDB-common.x86_64 0:10.1.16-1.el7.centos
boost-program-options.x86_64 0:1.53.0-25.el7
Replaced:
mariadb-libs.x86_64 1:5.5.44-2.el7.centos
Complete!
3. Start MariaDB & Setup Root password
[root@localhost ~]# service mysql start
Starting mysql (via systemctl): [ OK ]
[root@localhost ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y
New password: <--- root1122
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] n
... skipping.
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
여기까지 설치 하면 기본 MariaDB 설치가 완료 된 것임. 이후 부터는 Galera Cluster를 사용하기 위한 설정 임.
2. Galera Cluster 환경 설정
노드가 짝수 일 경우 Split Brain 문제가 발생 할 수 있다고 하는데, 그래서 홀수로 노드를 구성하는 것을 추천한다는 글이 있음. (Galera Cluster에서 권장하는 최소 홀수 개수는 3개 임.)
Galera가 사용하는 포트는 3306,4567, 4568, 4444 포트를 사용하는데 이중 4444 포트가 Warm 바이러스에서 사용하는 것이 있으므로 4444 포트는 변경해서 사용하는 것이 좋다.
(wsrep_sst_receive_address 부분에서 변경 하면 됨)
[root@MariaCluster1 ~]# vi /etc/my.cnf.d/server.cnf (Cluster Node1) 파일에 다음 내용을 추가한다.
[root@localhost ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.10.11 ma1 192.168.10.12 ma2 192.168.10.13 ma3 [root@localhost ~]# vi /etc/my.cnf.d/server.cnf # # These groups are read by MariaDB server. # Use it for options that only the server (but not clients) should see # # See the examples of server my.cnf files in /usr/share/mysql/ # # this is read by the standalone daemon and embedded servers [server] # this is only for the mysqld standalone daemon [mysqld] # # * Galera-related settings # [galera] # Mandatory settings wsrep_on=ON wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_address='gcomm://' <-- 초기 설치 시 Node1은 아이피 입력 안 해준다 wsrep_cluster_name='galera' <-- 클러스터 이름 wsrep_node_address='192.168.10.11' <-- 서버 아이피 wsrep_node_name='ma1' <-- 서버 호스트 이름 wsrep_sst_method=rsync binlog_format=row default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 bind-address=0.0.0.0 # # Allow server to accept connections on all interfaces. # #bind-address=0.0.0.0 # # Optional setting #wsrep_slave_threads=1 #innodb_flush_log_at_trx_commit=0 # this is only for embedded server [embedded] # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] # This group is only read by MariaDB-10.1 servers. # If you use the same .cnf file for MariaDB of different versions, # use this group for options that older servers don't understand [mariadb-10.1] |
3. Galera Cluster 최초 실행
노드1이 최초로 기동되는 MariaDB Doner이기에 --wsrep-new-cluster 옵션으로 시작해 주어야 함. (cluster 초기화 작업)
[root@localhost ~]# service mysql stop Stopping mysql (via systemctl): [ OK ] [root@localhost ~]# /etc/init.d/mysql start --wsrep-new-cluster Starting mysql (via systemctl): [ OK ] [root@localhost ~]# mysql -uroot -proot1122 -e "show status like 'wsrep%'" +------------------------------+---------------------------------------------+ | Variable_name | Value | +------------------------------+---------------------------------------------+ | wsrep_apply_oooe | 0.000000 | | wsrep_apply_oool | 0.000000 | | wsrep_apply_window | 0.000000 | | wsrep_causal_reads | 0 | | wsrep_cert_deps_distance | 0.000000 | | wsrep_cert_index_size | 0 | | wsrep_cert_interval | 0.000000 | | wsrep_cluster_conf_id | 1 | | wsrep_cluster_size | 1 | | wsrep_cluster_state_uuid | 64dea1cf-5562-11e6-895b-9ea4811cc370 | | wsrep_cluster_status | Primary | | wsrep_commit_oooe | 0.000000 | | wsrep_commit_oool | 0.000000 | | wsrep_commit_window | 0.000000 | | wsrep_connected | ON | | wsrep_evs_delayed | | | wsrep_evs_evict_list | | | wsrep_evs_repl_latency | 1.27e-06/4.0364e-06/7.404e-06/2.25523e-06/5 | | 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 | 64dd9778-5562-11e6-9c7a-96000eb646f4 | | wsrep_incoming_addresses | 192.168.10.11:3306 | | wsrep_last_committed | 0 | | wsrep_local_bf_aborts | 0 | | wsrep_local_cached_downto | 18446744073709551615 | | wsrep_local_cert_failures | 0 | | wsrep_local_commits | 0 | | wsrep_local_index | 0 | | wsrep_local_recv_queue | 0 | | wsrep_local_recv_queue_avg | 0.500000 | | wsrep_local_recv_queue_max | 2 | | wsrep_local_recv_queue_min | 0 | | wsrep_local_replays | 0 | | wsrep_local_send_queue | 0 | | wsrep_local_send_queue_avg | 0.000000 | | wsrep_local_send_queue_max | 1 | | wsrep_local_send_queue_min | 0 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_local_state_uuid | 64dea1cf-5562-11e6-895b-9ea4811cc370 | | wsrep_protocol_version | 7 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy <info@codership.com> | | wsrep_provider_version | 25.3.15(r3578) | | wsrep_ready | ON | | wsrep_received | 2 | | wsrep_received_bytes | 140 | | wsrep_repl_data_bytes | 0 | | wsrep_repl_keys | 0 | | wsrep_repl_keys_bytes | 0 | | wsrep_repl_other_bytes | 0 | | wsrep_replicated | 0 | | wsrep_replicated_bytes | 0 | | wsrep_thread_count | 2 | +------------------------------+---------------------------------------------+ [root@localhost ~]# service mysql stop Stopping mysql (via systemctl): [ OK ] [root@localhost ~]# ps -ef | grep mysql root 2806 1961 0 17:05 pts/0 00:00:00 grep --color=auto mysql |
4. VM 이미지 복제 및 설정 파일들 변경
VM 이미지 복제 후 아이피, hostname, /etc/my.cnf.d/server.cnf 파일에서 아이피, 이름 부분 수정
각각의 서버간의 통신에 문제가 있는지 ping check
[root@ma2 ~]# vi /etc/my.cnf.d/server.cnf [galera] # Mandatory settings wsrep_on=ON wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_address='gcomm://192.168.10.11' wsrep_cluster_name='galera' wsrep_node_address='192.168.10.12' wsrep_node_name='ma2' wsrep_sst_method=rsync binlog_format=row default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 bind-address=0.0.0.0 |
[root@ma3 ~]# vi /etc/my.cnf.d/server.cnf [galera] # Mandatory settings wsrep_on=ON wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_address='gcomm://192.168.10.11' wsrep_cluster_name='galera' wsrep_node_address='192.168.10.13' wsrep_node_name='ma3' wsrep_sst_method=rsync binlog_format=row default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 bind-address=0.0.0.0 |
5. 3개 노드 Mysql 기동
Cluster Node1 [root@ma1 ~]# service mysql start Starting mysql (via systemctl): [ OK ] [root@ma1 ~]# ps -ef | grep mysql mysql 2241 1 2 17:51 ? 00:00:00 /usr/sbin/mysqld --wsrep_start_position=64dea1cf-5562-11e6-895b-9ea4811cc370:0 root 2277 2080 0 17:51 pts/0 00:00:00 grep --color=auto mysql [root@ma1 ~]# mysql -uroot -proot1122 -e "show status like 'wsrep%'" +------------------------------+-------------------------------------------------------------+ | Variable_name | Value | +------------------------------+-------------------------------------------------------------+ | wsrep_apply_oooe | 0.000000 | | wsrep_apply_oool | 0.000000 | | wsrep_apply_window | 0.000000 | | wsrep_causal_reads | 0 | | wsrep_cert_deps_distance | 0.000000 | | wsrep_cert_index_size | 0 | | wsrep_cert_interval | 0.000000 | | wsrep_cluster_conf_id | 2 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | 64dea1cf-5562-11e6-895b-9ea4811cc370 | | wsrep_cluster_status | Primary | | wsrep_commit_oooe | 0.000000 | | wsrep_commit_oool | 0.000000 | | wsrep_commit_window | 0.000000 | | wsrep_connected | ON | | wsrep_evs_delayed | | | wsrep_evs_evict_list | | | wsrep_evs_repl_latency | 0.00161089/0.00182341/0.00203593/0.000212524/2 | | 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 | a7629206-5569-11e6-94e1-afdd9f9afb23 | | wsrep_incoming_addresses | 192.168.10.12:3306,192.168.10.13:3306,192.168.10.11:3306 | | wsrep_last_committed | 0 | | wsrep_local_bf_aborts | 0 | | wsrep_local_cached_downto | 18446744073709551615 | | wsrep_local_cert_failures | 0 | | wsrep_local_commits | 0 | | wsrep_local_index | 2 | | wsrep_local_recv_queue | 0 | | wsrep_local_recv_queue_avg | 0.333333 | | wsrep_local_recv_queue_max | 2 | | wsrep_local_recv_queue_min | 0 | | wsrep_local_replays | 0 | | wsrep_local_send_queue | 0 | | wsrep_local_send_queue_avg | 0.000000 | | wsrep_local_send_queue_max | 1 | | wsrep_local_send_queue_min | 0 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_local_state_uuid | 64dea1cf-5562-11e6-895b-9ea4811cc370 | | wsrep_protocol_version | 7 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy <info@codership.com> | | wsrep_provider_version | 25.3.15(r3578) | | wsrep_ready | ON | | wsrep_received | 3 | | wsrep_received_bytes | 412 | | wsrep_repl_data_bytes | 0 | | wsrep_repl_keys | 0 | | wsrep_repl_keys_bytes | 0 | | wsrep_repl_other_bytes | 0 | | wsrep_replicated | 0 | | wsrep_replicated_bytes | 0 | | wsrep_thread_count | 2 | +------------------------------+-------------------------------------------------------------+ |
Cluster Node#2 [root@ma2 ~]# service mysql start Starting mysql (via systemctl): [ OK ] [root@ma2 ~]# ps -ef | grep mysql mysql 2237 1 2 17:51 ? 00:00:00 /usr/sbin/mysqld --wsrep_start_position=64dea1cf-5562-11e6-895b-9ea4811cc370:0 root 2272 2079 0 17:51 pts/0 00:00:00 grep --color=auto mysql [root@ma2 ~]# mysql -uroot -proot1122 -e "show status like 'wsrep%'" +------------------------------+-------------------------------------------------------------+ | Variable_name | Value | +------------------------------+-------------------------------------------------------------+ | wsrep_apply_oooe | 0.000000 | | wsrep_apply_oool | 0.000000 | | wsrep_apply_window | 0.000000 | | wsrep_causal_reads | 0 | | wsrep_cert_deps_distance | 0.000000 | | wsrep_cert_index_size | 0 | | wsrep_cert_interval | 0.000000 | | wsrep_cluster_conf_id | 2 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | 64dea1cf-5562-11e6-895b-9ea4811cc370 | | wsrep_cluster_status | Primary | | wsrep_commit_oooe | 0.000000 | | wsrep_commit_oool | 0.000000 | | wsrep_commit_window | 0.000000 | | wsrep_connected | ON | | wsrep_evs_delayed | | | wsrep_evs_evict_list | | | wsrep_evs_repl_latency | 0.00188361/0.00507755/0.016603/0.00577088/5 | | 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 | a6fe8bc1-5569-11e6-81dc-d3f0acf3a5d7 | | wsrep_incoming_addresses | 192.168.10.12:3306,192.168.10.13:3306,192.168.10.11:3306 | | wsrep_last_committed | 0 | | wsrep_local_bf_aborts | 0 | | wsrep_local_cached_downto | 18446744073709551615 | | wsrep_local_cert_failures | 0 | | wsrep_local_commits | 0 | | wsrep_local_index | 0 | | wsrep_local_recv_queue | 0 | | wsrep_local_recv_queue_avg | 0.000000 | | wsrep_local_recv_queue_max | 1 | | wsrep_local_recv_queue_min | 0 | | wsrep_local_replays | 0 | | wsrep_local_send_queue | 0 | | wsrep_local_send_queue_avg | 0.000000 | | wsrep_local_send_queue_max | 1 | | wsrep_local_send_queue_min | 0 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_local_state_uuid | 64dea1cf-5562-11e6-895b-9ea4811cc370 | | wsrep_protocol_version | 7 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy <info@codership.com> | | wsrep_provider_version | 25.3.15(r3578) | | wsrep_ready | ON | | wsrep_received | 2 | | wsrep_received_bytes | 279 | | wsrep_repl_data_bytes | 0 | | wsrep_repl_keys | 0 | | wsrep_repl_keys_bytes | 0 | | wsrep_repl_other_bytes | 0 | | wsrep_replicated | 0 | | wsrep_replicated_bytes | 0 | | wsrep_thread_count | 2 | +------------------------------+-------------------------------------------------------------+ |
Cluster Node3 [root@ma3 ~]# service mysql start Starting mysql (via systemctl): [ OK ] [root@ma3 ~]# ps -ef | grep mysql mysql 2238 1 2 17:51 ? 00:00:00 /usr/sbin/mysqld --wsrep_start_position=64dea1cf-5562-11e6-895b-9ea4811cc370:0 root 2274 2080 0 17:51 pts/0 00:00:00 grep --color=auto mysql [root@ma3 ~]# mysql -uroot -proot1122 -e "show status like 'wsrep%'" +------------------------------+-------------------------------------------------------------+ | Variable_name | Value | +------------------------------+-------------------------------------------------------------+ | wsrep_apply_oooe | 0.000000 | | wsrep_apply_oool | 0.000000 | | wsrep_apply_window | 0.000000 | | wsrep_causal_reads | 0 | | wsrep_cert_deps_distance | 0.000000 | | wsrep_cert_index_size | 0 | | wsrep_cert_interval | 0.000000 | | wsrep_cluster_conf_id | 2 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | 64dea1cf-5562-11e6-895b-9ea4811cc370 | | wsrep_cluster_status | Primary | | wsrep_commit_oooe | 0.000000 | | wsrep_commit_oool | 0.000000 | | wsrep_commit_window | 0.000000 | | wsrep_connected | ON | | wsrep_evs_delayed | | | wsrep_evs_evict_list | | | wsrep_evs_repl_latency | 0.00204262/0.00700795/0.0159775/0.00635458/3 | | 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 | a7235184-5569-11e6-bdf1-378df73b1c93 | | wsrep_incoming_addresses | 192.168.10.12:3306,192.168.10.13:3306,192.168.10.11:3306 | | wsrep_last_committed | 0 | | wsrep_local_bf_aborts | 0 | | wsrep_local_cached_downto | 18446744073709551615 | | wsrep_local_cert_failures | 0 | | wsrep_local_commits | 0 | | wsrep_local_index | 1 | | wsrep_local_recv_queue | 0 | | wsrep_local_recv_queue_avg | 0.000000 | | wsrep_local_recv_queue_max | 1 | | wsrep_local_recv_queue_min | 0 | | wsrep_local_replays | 0 | | wsrep_local_send_queue | 0 | | wsrep_local_send_queue_avg | 0.000000 | | wsrep_local_send_queue_max | 1 | | wsrep_local_send_queue_min | 0 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_local_state_uuid | 64dea1cf-5562-11e6-895b-9ea4811cc370 | | wsrep_protocol_version | 7 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy <info@codership.com> | | wsrep_provider_version | 25.3.15(r3578) | | wsrep_ready | ON | | wsrep_received | 2 | | wsrep_received_bytes | 279 | | wsrep_repl_data_bytes | 0 | | wsrep_repl_keys | 0 | | wsrep_repl_keys_bytes | 0 | | wsrep_repl_other_bytes | 0 | | wsrep_replicated | 0 | | wsrep_replicated_bytes | 0 | | wsrep_thread_count | 2 | +------------------------------+-------------------------------------------------------------+ |
이제 3개의 DB에서 동일 데이터를 가지고 작업이 가능하다.
