[선행 작업]

- 마스터 서버의 mysql 에서 복제 전용 계정을 새로 만들던가 기존 계정에 복제 관련 권한을 추가해 줘야한다.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'id'@'ip' IDENTIFIED BY 'password';


- 마스터, 슬레이브에서 replication 할 db 의 데이터를 맞춰둔다. (덤프 이용)

* DB 데이터 백업

# mysqldump -u계정명 -p비밀번호 db > db.sql


* 함수, 프로시저, 트리거 를 함께 백업해야 할 경우

# mysqldump --routines --trigger -u계정명 -p비밀번호 db > db.sql


* 함수, 프로시저, 트리거 만 백업해야 할 경우 (테이블 제외)

# mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt db > db_no_tables.sql


* 백업 데이터 복원

# mysql -u계정명 -p비밀번호 db < db.sql




[설정]

1) 마스터 설정파일 수정

/etc/my.cnf.d/server.cnf

----

[mysqld]

log-bin=mysql-bin

server-id=1

----

위 항목 추가


2) 마스터 mysql 실행 & 마스터 설정 확인

# service mysql start

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 |      906 |              |                  |

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

* file, position 기록해놓기.


3) 슬레이브 설정파일 수정

/etc/my.cnf.d/server.cnf

----

[mysqld]

server-id=2

# replicate-do-db='repl' 

# 위 설정을 통해 하나의 db 만 replication 가능. 없으면 모든 db 를 replication 함.

# 몇몇개의 db 만 replication 할 경우 replicate-do-db 항목을 여러개 추가

----

위 항목 추가


4) 슬레이브 mysql 실행 & 슬레이브 마스터 연결 설정

# service mysql start

mysql> change master to

master_host='192.168.0.1',

master_user='id',

master_password='password',

master_log_file='mysql-bin.000001',   # 마스터 설정에서 확인한 file 이름

master_log_pos=906;                   # 마스터 설정에서 확인한 position 번호


5) 슬레이브 replication start

mysql> start slave;



[참고 링크]

http://we-minarida.tistory.com/entry/%EC%8B%A4%EC%82%AC%EC%9A%A9-db-%EC%97%90%EC%84%9C-mysql-replication

http://server-talk.tistory.com/241

https://blurblah.net/1490


[오류 발생]


The user specified as a definer ('user'@'ip') does not exist different ip address


- 해당 오류는 계정 정보의 불일치로 여러가지 경우에 발생하며 일반적인 경우에는 권한 추가 등과 같은

  널리 알려진 방법으로 해결 가능하다



[원인]


- 업무상의 이유로 서버가 변경되면서 IP 도 바뀌게 됨.

- 서버 변경 후 procedure 의 definer (정의자) 변경을 하지 않아서 발생함


(Heidisql 사용)


정의자를 알맞는 계정으로 수정하면 됨.


'Programming > DB' 카테고리의 다른 글

[Mysql & MariaDB] Master Slave Replication 설정  (0) 2019.01.03
[Mysql & MariaDB] 백업 & 복원  (0) 2018.10.04
[MariaDB] Clustering  (0) 2018.09.18
[MariaDB] Character Set 변경  (0) 2018.09.17

<MariaDB 10.1 기준>


* DB 데이터 백업

# mysqldump -u계정명 -p비밀번호 DB이름 > db.sql


* 특정 테이블만 백업

# mysqldump -u계정명 -p비밀번호 DB이름 테이블명1 테이블명2 테이블명3 > tables.sql

 

* 함수, 프로시저, 트리거 를 함께 백업해야 할 경우

mysqldump --routines --trigger -u계정명 -p비밀번호 DB이름 db.sql


함수, 프로시저, 트리거 만 백업해야 할 경우 (테이블 제외)

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt DB이름> db_no_tables.sql


* 백업 데이터 복원

mysql -u계정명 -p비밀번호 DB이름 < db.sql


 

[참고사항]

- 복원 시

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, 
or READS SQL DATA in its declaration and binary logging is enabled 
(you *might* want to use the less safe log_bin_trust_function_creators 
variable) 

위와 같은 오류가 발생 할 경우

1. mysql 서버를 시작할 때 다음 옵션을 추가 한다.

--log-bin-trust-function-creators=1

2. 계정에 접속해서 다음을 실행한다. (또는 접속툴을 이용해서)

mysql>SET GLOBAL log_bin_trust_function_creators = 1; 

(권한이 없어 실패할 경우 권한 있는 계정 또는 root 로 실행)



설치 환경 : 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  파일을 생성하고 다음의 내용을 추가 한다.

 

# MariaDB 10.1 CentOS repository list - created 2016-07-29 06:58 UTC

http://downloads.mariadb.org/mariadb/repositories/

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.1/centos7-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

 

필요한 패키지 인스톨

[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에서 동일 데이터를 가지고 작업이 가능하다.



출처: http://bstar36.tistory.com/298 [멋지게 놀아라]

우분투에 MariaDB를 처음 설치하면 캐릭터셋이 기본적으로 Latin-1 으로 되어 있는것을 볼 수 있습니다. (요즘 시대가 어떤 시대인데 Latin-1을 디폴트로...)


한글을 사용하기 위해서는 인코딩을 euc-kr이나 UTF8로 바꾸는 것이 필요한데, 요즘은 대부분 UTF8을 사용하므로 UTF8로 설정해 봅니다.


MariaDB의 캐릭터셋을 UTF8로 설정하는 것은 MySQL과 유사합니다.


먼저, 현재 MariaDB의 캐릭터셋이 무엇으로 되어 있는지 확인부터 해보죠. (이미 UTF8로 되어 있다면 바꿀 이유가 없으니까요.)


먼저 터미널로 접속합니다. 그리고 MariaDB에 연결합니다.


$ sudo mysql -u root -p                        <-- root는 MariaDB 유저명


비밀번호를 입력하고 접속 되면, MariaDB에 c로 시작하는 변수값이 어떻게 설정되었는지 확인합니다.


MariaDB [(none)]> show variables like 'c%';


제 경우 아래와 같이 나오네요.




utf8로 설정되어 있는 부분들도 있지만, latin1으로 설되어 있는 부분들도 보이는군요.


utf8로 바꾸어 봅시다.


(모두 utf8로 되어 있다면 바꿀 필요 없습니다.)


먼저 quit; 명령으로 MariaDB를 빠져 나옵니다.


MariaDB의 my.cnf 파일을 수정해야 하는데, 우분투의 경우, /etc/mysql/ 디렉터리 밑에 있습니다.


만약을 대비해 먼저 my.cnf 파일을 백업합니다.


$ sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.ori


이제 본인에게 편한 에디터로 my.cnf 파일을 수정합니다.


아래 내용이 my.cnf에 있는지 확인하고 없으면 추가하고, 있으면 수정합니다.


(각 섹션별로 추가나 수정을 해 줍니다.)


[client]

.

.

.

default-character-set=utf8

.

.

.

[mysqld]

.

.

.

init_connect="SET collation_connection=utf8_general_ci"

init_connect="SET NAMES utf8"

character-set-server=utf8

collation-server=utf8_general_ci


skip-character-set-client-handshake

.

.

.

[mysql]

.

.

.

default-character-set=utf8

.

.

.


수정을 마쳤으면, MariaDB를 재시작합니다.


$ sudo service mysql restart


다시한번 MariaDB에 접속해서, 아래 명령어로 캐릭터셋이 바뀌었는지 확인합니다.


MariaDB [(none)]> show variables like 'c%';


이제 아래와 같이 나오네요.




정상적으로 utf8로 바뀐것을 확인 할 수 있습니다.




끝.


---------------------------------------------------------------

centos


/etc/my.cnf 파일에 아래 내용을 추가한 후 서버를 재시작해준다

[mysqld]

character_set_server=utf8

collation_server=utf8_general_ci

init_connect=set collation_connection=utf8_general_ci

init_connect=set names utf8

character-set-server=utf8

character-set-client-handshake = TRUE


+ Recent posts