LoginSignup
5
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@gamisan9999(Kentaro Kishigami)

深淵の闇から逃れる為のMariaDB Galera Cluster 5.5 入門(2 node編)

Posted at

昔つかってたHatena Blog より転載

MariaDB Galera Cluster 5.5を試す

oujiにMySQLの深淵の闇(マスタ昇格とかマスタ昇格とか)から逃れたいと相談したところ MariaDB Galera Clusterが良いと教えてもらったのでやっとこさ試した記録を残します。

RDS最高なのですが、選定できないと思われるケースもあるのでこっちが利用できたらいいな。

MariaDB Galera Clusterって何

デュアルマスタ構成が可能なデータベースクラスタらしいです。最高。

今回はMySQL 5.5からMariaDBへの移行を考え MariaDB Galera Clusterは5.5で試しています。

MariaDB 5.5はMySQL 5.5と互換性があるので、アプリケーションやデータベースを変更せずmysqldumpで移行できると思います。

詳しい資料は

こことか

日本語で詳しいのはさくらインターネット研究所さんの記事だと思います。

資料を熟読してないのですが今回はインストールとMaster破損時の挙動確認をしてみたいとおもいます。

Galera Cluster 5.5 ServerとClientのインストール

今回はEC2(t2.micro)*2をVagrantで準備し、それぞれの内部IPを以下にして検証しています。OSはAmazon Linuxです。

役割 IPアドレス
Master 10.0.0.245
Secondary 10.0.0.67

参考資料

  • リポジトリの準備

Amazon Linux用のリポジトリは提供されていませんが、動RHEL向けのリポジトリを使いました

$ sudo cat /etc/yum.repos.d/mariadb.repo << EOF
# MariaDB 5.5 RedHat repository list - created 2014-09-20 02:09 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/rhel6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF
  • Galera Clusterのインストール
$ sudo yum install --enablerepo=mariadb MariaDB-Galera-server galera MariaDB-Client

Galera Clusterの事前準備

  • Master Nodeの設定
$ sudo vi /etc/my.cnf.d/server.cnf
[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://
wsrep_node_address=10.0.0.245
wsrep_slave_threads=1
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
query_cache_size=0
  • Secondary Nodeの設定
$ sudo vi /etc/my.cnf.d/server.cnf
[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.245
wsrep_node_address=10.0.0.67
wsrep_slave_threads=1
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
query_cache_size=0

mysqldの起動

Master Node

  • Master Nodeのmysqld起動
$ sudo service mysql start
  • サンプルデータベースの作成
$ mysql -u root -e "create database orenomaria"
$ mysql -u root -e "show databases"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| orenomaria         |
| performance_schema |
| test               |
+--------------------+
$ mysql -u root -e "create table orenomaria.test( col varchar(10))"
$ mysql -u root -e "insert into orenomaria.test values('galera')"
$ mysql -u root -e "select * from orenomaria.test"
+--------+
| col    |
+--------+
| galera |
+--------+

Secondary Node

  • Secondary Nodeのmysqld起動

Master Nodeが起動済みでないと同期できず起動しない

$ sudo service mysql start
Starting MySQL.....SST in progress, setting sleep higher. SUCCESS!``
  • 同期確認
$ sudo mysql -u root -e "show databases"
$ mysql -u root -e "select * from orenomaria.test"
+--------+
| col    |
+--------+
| galera |
+--------+

Secondary Node 起動後の確認

Master Node

  • wsrep系パラメータの確認
$ mysql -u root -e "show status like  'wsrep_%'"
+------------------------------+--------------------------------------+
| Variable_name                | Value                                |
+------------------------------+--------------------------------------+
| wsrep_local_state_uuid       | 3f056461-4076-11e4-a2b8-6216b1337d22 |
| wsrep_protocol_version       | 5                                    |
| wsrep_last_committed         | 0                                    |
| wsrep_replicated             | 0                                    |
| wsrep_replicated_bytes       | 0                                    |
| wsrep_repl_keys              | 0                                    |
| wsrep_repl_keys_bytes        | 0                                    |
| wsrep_repl_data_bytes        | 0                                    |
| wsrep_repl_other_bytes       | 0                                    |
| wsrep_received               | 23                                   |
| wsrep_received_bytes         | 1821                                 |
| wsrep_local_commits          | 0                                    |
| wsrep_local_cert_failures    | 0                                    |
| wsrep_local_replays          | 0                                    |
| wsrep_local_send_queue       | 0                                    |
| wsrep_local_send_queue_avg   | 0.000000                             |
| wsrep_local_recv_queue       | 0                                    |
| wsrep_local_recv_queue_avg   | 0.043478                             |
| wsrep_local_cached_downto    | 18446744073709551615                 |
| wsrep_flow_control_paused_ns | 0                                    |
| wsrep_flow_control_paused    | 0.000000                             |
| wsrep_flow_control_sent      | 0                                    |
| wsrep_flow_control_recv      | 0                                    |
| wsrep_cert_deps_distance     | 0.000000                             |
| wsrep_apply_oooe             | 0.000000                             |
| wsrep_apply_oool             | 0.000000                             |
| wsrep_apply_window           | 0.000000                             |
| wsrep_commit_oooe            | 0.000000                             |
| wsrep_commit_oool            | 0.000000                             |
| wsrep_commit_window          | 0.000000                             |
| wsrep_local_state            | 4                                    |
| wsrep_local_state_comment    | Synced                               |
| wsrep_cert_index_size        | 0                                    |
| wsrep_causal_reads           | 0                                    |
| wsrep_cert_interval          | 0.000000                             |
| wsrep_incoming_addresses     | 10.0.0.245:3306,10.0.0.67:3306       |
| wsrep_cluster_conf_id        | 10                                   |
| wsrep_cluster_size           | 2                                    |
| wsrep_cluster_state_uuid     | 3f056461-4076-11e4-a2b8-6216b1337d22 |
| wsrep_cluster_status         | Primary                              |
| wsrep_connected              | ON                                   |
| wsrep_local_bf_aborts        | 0                                    |
| wsrep_local_index            | 0                                    |
| wsrep_provider_name          | Galera                               |
| wsrep_provider_vendor        | Codership Oy <info@codership.com>    |
| wsrep_provider_version       | 25.3.5(rXXXX)                        |
| wsrep_ready                  | ON                                   |
| wsrep_thread_count           | 2                                    |
+------------------------------+--------------------------------------+
  • Master Node に Anther Node が接続される時のログ

Cluster UUID [3f056461-4076-11e4-a2b8-6216b1337d22]に新しいメンバーがJoinしてwsrep_sst_rsyncが走っている

$ cat /dev/null > `hostname`.err
140920 12:47:18 [Note] WSREP: declaring d193920e-4078-11e4-9422-86a0381f90eb stable
140920 12:47:18 [Note] WSREP: Node 3f04f808-4076-11e4-a830-1bcc73dcf244 state prim
140920 12:47:18 [Note] WSREP: view(view_id(PRIM,3f04f808-4076-11e4-a830-1bcc73dcf244,10) memb {
        3f04f808-4076-11e4-a830-1bcc73dcf244,0
        d193920e-4078-11e4-9422-86a0381f90eb,0
} joined {
} left {
} partitioned {
})
140920 12:47:18 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 2
140920 12:47:18 [Note] WSREP: STATE_EXCHANGE: sent state UUID: d1e0b305-4078-11e4-a8ed-16954c252dca
140920 12:47:18 [Note] WSREP: STATE EXCHANGE: sent state msg: d1e0b305-4078-11e4-a8ed-16954c252dca
140920 12:47:18 [Note] WSREP: STATE EXCHANGE: got state msg: d1e0b305-4078-11e4-a8ed-16954c252dca from 0 ()
140920 12:47:19 [Note] WSREP: STATE EXCHANGE: got state msg: d1e0b305-4078-11e4-a8ed-16954c252dca from 1 ()
140920 12:47:19 [Note] WSREP: Quorum results:
        version    = 3,
        component  = PRIMARY,
        conf_id    = 9,
        members    = 1/2 (joined/total),
        act_id     = 0,
        last_appl. = 0,
        protocols  = 0/5/3 (gcs/repl/appl),
        group UUID = 3f056461-4076-11e4-a2b8-6216b1337d22
140920 12:47:19 [Note] WSREP: Flow-control interval: [23, 23]
140920 12:47:19 [Note] WSREP: New cluster view: global state: 3f056461-4076-11e4-a2b8-6216b1337d22:0, view# 10: Primary, number of nodes: 2, my index: 0, protocol version 3
140920 12:47:19 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
140920 12:47:19 [Note] WSREP: REPL Protocols: 5 (3, 1)
140920 12:47:19 [Note] WSREP: Service thread queue flushed.
140920 12:47:19 [Note] WSREP: Assign initial position for certification: 0, protocol version: 3
140920 12:47:19 [Note] WSREP: Service thread queue flushed.
140920 12:47:21 [Note] WSREP: Member 1.0 () requested state transfer from '*any*'. Selected 0.0 ()(SYNCED) as donor.
140920 12:47:21 [Note] WSREP: Shifting SYNCED -> DONOR/DESYNCED (TO: 0)
140920 12:47:21 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
140920 12:47:21 [Note] WSREP: Running: 'wsrep_sst_rsync --role 'donor' --address '10.0.0.67:4444/rsync_sst' --auth '(null)' --socket '/var/lib/mysql/mysql.sock' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --gtid '3f056461-4076-11e4-a2b8-6216b1337d22:0''
140920 12:47:21 [Note] WSREP: sst_donor_thread signaled with 0
140920 12:47:21 [Note] WSREP: Flushing tables for SST...
140920 12:47:21 [Note] WSREP: Provider paused at 3f056461-4076-11e4-a2b8-6216b1337d22:0 (25)
140920 12:47:21 [Note] WSREP: Tables flushed.
140920 12:47:22 [Note] WSREP: resuming provider at 25
140920 12:47:22 [Note] WSREP: Provider resumed.
140920 12:47:22 [Note] WSREP: 0.0 (): State transfer to 1.0 () complete.
140920 12:47:22 [Note] WSREP: Shifting DONOR/DESYNCED -> JOINED (TO: 0)
140920 12:47:22 [Note] WSREP: Member 0.0 () synced with group.
140920 12:47:22 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0)
140920 12:47:22 [Note] WSREP: Synchronized with group, ready for connections
140920 12:47:22 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
140920 12:47:25 [Note] WSREP: 1.0 (): State transfer from 0.0 () complete.
140920 12:47:25 [Note] WSREP: Member 1.0 () synced with group.

Secondary Node

  • Secondary Node起動後のwsrep系パラメータ
+------------------------------+--------------------------------------+
| Variable_name                | Value                                |
+------------------------------+--------------------------------------+
| wsrep_local_state_uuid       | 3f056461-4076-11e4-a2b8-6216b1337d22 |
| wsrep_protocol_version       | 5                                    |
| wsrep_last_committed         | 0                                    |
| wsrep_replicated             | 0                                    |
| wsrep_replicated_bytes       | 0                                    |
| wsrep_repl_keys              | 0                                    |
| wsrep_repl_keys_bytes        | 0                                    |
| wsrep_repl_data_bytes        | 0                                    |
| wsrep_repl_other_bytes       | 0                                    |
| wsrep_received               | 3                                    |
| wsrep_received_bytes         | 203                                  |
| wsrep_local_commits          | 0                                    |
| wsrep_local_cert_failures    | 0                                    |
| wsrep_local_replays          | 0                                    |
| wsrep_local_send_queue       | 0                                    |
| wsrep_local_send_queue_avg   | 0.000000                             |
| wsrep_local_recv_queue       | 0                                    |
| wsrep_local_recv_queue_avg   | 0.000000                             |
| wsrep_local_cached_downto    | 18446744073709551615                 |
| wsrep_flow_control_paused_ns | 0                                    |
| wsrep_flow_control_paused    | 0.000000                             |
| wsrep_flow_control_sent      | 0                                    |
| wsrep_flow_control_recv      | 0                                    |
| wsrep_cert_deps_distance     | 0.000000                             |
| wsrep_apply_oooe             | 0.000000                             |
| wsrep_apply_oool             | 0.000000                             |
| wsrep_apply_window           | 0.000000                             |
| wsrep_commit_oooe            | 0.000000                             |
| wsrep_commit_oool            | 0.000000                             |
| wsrep_commit_window          | 0.000000                             |
| wsrep_local_state            | 4                                    |
| wsrep_local_state_comment    | Synced                               |
| wsrep_cert_index_size        | 0                                    |
| wsrep_causal_reads           | 0                                    |
| wsrep_cert_interval          | 0.000000                             |
| wsrep_incoming_addresses     | 10.0.0.245:3306,10.0.0.67:3306       |
| wsrep_cluster_conf_id        | 10                                   |
| wsrep_cluster_size           | 2                                    |
| wsrep_cluster_state_uuid     | 3f056461-4076-11e4-a2b8-6216b1337d22 |
| wsrep_cluster_status         | Primary                              |
| wsrep_connected              | ON                                   |
| wsrep_local_bf_aborts        | 0                                    |
| wsrep_local_index            | 1                                    |
| wsrep_provider_name          | Galera                               |
| wsrep_provider_vendor        | Codership Oy <info@codership.com>    |
| wsrep_provider_version       | 25.3.5(rXXXX)                        |
| wsrep_ready                  | ON                                   |
| wsrep_thread_count           | 2                                    |
+------------------------------+--------------------------------------+
  • Secondary Node起動後のログ

wsrepによりMaster Nodeからデータを同期した後(今回の同期方式はデフォルトのwsrep_sst_rsync)

mysqldが起動している

$ cat /var/lib/mysql/`hostname`.err
140920 12:47:15 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140920 12:47:15 mysqld_safe WSREP: Running position recovery with --log_error='/var/lib/mysql/wsrep_recovery.pkbACS' --pid-file='/var/lib/mysql/ip-10-0-0-67-recover.pid'
140920 12:47:18 mysqld_safe WSREP: Recovered position 00000000-0000-0000-0000-000000000000:-1
140920 12:47:18 [Note] WSREP: wsrep_start_position var submitted: '00000000-0000-0000-0000-000000000000:-1'
140920 12:47:18 [Note] WSREP: Read nil XID from storage engines, skipping position init
140920 12:47:18 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/galera/libgalera_smm.so'
140920 12:47:18 [Note] WSREP: wsrep_load(): Galera 25.3.5(rXXXX) by Codership Oy <info@codership.com> loaded successfully.
140920 12:47:18 [Note] WSREP: CRC-32C: using hardware acceleration.
140920 12:47:18 [Warning] WSREP: Could not open saved state file for reading: /var/lib/mysql//grastate.dat
140920 12:47:18 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1
140920 12:47:18 [Note] WSREP: Passing config to GCS: base_host = 10.0.0.67; base_port = 4567; cert.log_conflicts = no; debug = no; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 1; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 2; evs.view_forget_timeout = PT24H; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.size = 128M; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.npvo = false; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = P30S; pc.weight = 1; protonet.b
140920 12:47:18 [Note] WSREP: Service thread queue flushed.
140920 12:47:18 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1
140920 12:47:18 [Note] WSREP: wsrep_sst_grab()
140920 12:47:18 [Note] WSREP: Start replication
140920 12:47:18 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1
140920 12:47:18 [Note] WSREP: protonet asio version 0
140920 12:47:18 [Note] WSREP: Using CRC-32C (optimized) for message checksums.
140920 12:47:18 [Note] WSREP: backend: asio
140920 12:47:18 [Note] WSREP: GMCast version 0
140920 12:47:18 [Note] WSREP: (d193920e-4078-11e4-9422-86a0381f90eb, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
140920 12:47:18 [Note] WSREP: (d193920e-4078-11e4-9422-86a0381f90eb, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
140920 12:47:18 [Note] WSREP: EVS version 0
140920 12:47:18 [Note] WSREP: PC version 0
140920 12:47:18 [Note] WSREP: gcomm: connecting to group 'my_wsrep_cluster', peer '10.0.0.245:'
140920 12:47:18 [Note] WSREP: declaring 3f04f808-4076-11e4-a830-1bcc73dcf244 stable
140920 12:47:18 [Note] WSREP: Node 3f04f808-4076-11e4-a830-1bcc73dcf244 state prim
140920 12:47:18 [Note] WSREP: view(view_id(PRIM,3f04f808-4076-11e4-a830-1bcc73dcf244,10) memb {
        3f04f808-4076-11e4-a830-1bcc73dcf244,0
        d193920e-4078-11e4-9422-86a0381f90eb,0
} joined {
} left {
} partitioned {
})
140920 12:47:19 [Note] WSREP: gcomm: connected
140920 12:47:19 [Note] WSREP: Changing maximum packet size to 64500, resulting msg size: 32636
140920 12:47:19 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
140920 12:47:19 [Note] WSREP: Opened channel 'my_wsrep_cluster'
140920 12:47:19 [Note] WSREP: Waiting for SST to complete.
140920 12:47:19 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 1, memb_num = 2
140920 12:47:19 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.
140920 12:47:19 [Note] WSREP: STATE EXCHANGE: sent state msg: d1e0b305-4078-11e4-a8ed-16954c252dca
140920 12:47:19 [Note] WSREP: STATE EXCHANGE: got state msg: d1e0b305-4078-11e4-a8ed-16954c252dca from 0 ()
140920 12:47:19 [Note] WSREP: STATE EXCHANGE: got state msg: d1e0b305-4078-11e4-a8ed-16954c252dca from 1 ()
140920 12:47:19 [Note] WSREP: Quorum results:
        version    = 3,
        component  = PRIMARY,
        conf_id    = 9,
        members    = 1/2 (joined/total),
        act_id     = 0,
        last_appl. = -1,
        protocols  = 0/5/3 (gcs/repl/appl),
        group UUID = 3f056461-4076-11e4-a2b8-6216b1337d22
140920 12:47:19 [Note] WSREP: Flow-control interval: [23, 23]
140920 12:47:19 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 0)
140920 12:47:19 [Note] WSREP: State transfer required:
        Group state: 3f056461-4076-11e4-a2b8-6216b1337d22:0
        Local state: 00000000-0000-0000-0000-000000000000:-1
140920 12:47:19 [Note] WSREP: New cluster view: global state: 3f056461-4076-11e4-a2b8-6216b1337d22:0, view# 10: Primary, number of nodes: 2, my index: 1, protocol version 3
140920 12:47:19 [Warning] WSREP: Gap in state sequence. Need state transfer.
140920 12:47:21 [Note] WSREP: Running: 'wsrep_sst_rsync --role 'joiner' --address '10.0.0.67' --auth '' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --parent '21327''
140920 12:47:21 [Note] WSREP: Prepared SST request: rsync|10.0.0.67:4444/rsync_sst
140920 12:47:21 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
140920 12:47:21 [Note] WSREP: REPL Protocols: 5 (3, 1)
140920 12:47:21 [Note] WSREP: Service thread queue flushed.
140920 12:47:21 [Note] WSREP: Assign initial position for certification: 0, protocol version: 3
140920 12:47:21 [Note] WSREP: Service thread queue flushed.
140920 12:47:21 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (3f056461-4076-11e4-a2b8-6216b1337d22): 1 (Operation not permitted)
         at galera/src/replicator_str.cpp:prepare_for_IST():447. IST will be unavailable.
140920 12:47:21 [Note] WSREP: Member 1.0 () requested state transfer from '*any*'. Selected 0.0 ()(SYNCED) as donor.
140920 12:47:21 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 0)
140920 12:47:21 [Note] WSREP: Requesting state transfer: success, donor: 0
140920 12:47:22 [Note] WSREP: 0.0 (): State transfer to 1.0 () complete.
140920 12:47:22 [Note] WSREP: Member 0.0 () synced with group.
WSREP_SST: [INFO] Joiner cleanup. (20140920 12:47:23.479)
WSREP_SST: [INFO] Joiner cleanup done. (20140920 12:47:23.987)
140920 12:47:23 [Note] WSREP: SST complete, seqno: 0
140920 12:47:23 InnoDB: The InnoDB memory heap is disabled
140920 12:47:23 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140920 12:47:23 InnoDB: Compressed tables use zlib 1.2.3
140920 12:47:23 InnoDB: Using Linux native AIO
140920 12:47:23 InnoDB: Initializing buffer pool, size = 128.0M
140920 12:47:24 InnoDB: Completed initialization of buffer pool
140920 12:47:24 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1598129
140920 12:47:24  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 1598283
140920 12:47:24  InnoDB: Waiting for the background threads to start
140920 12:47:25 Percona XtraDB (http://www.percona.com) 5.5.38-MariaDB-35.2 started; log sequence number 1598283
140920 12:47:25 [Note] Plugin 'FEEDBACK' is disabled.
140920 12:47:25 [Note] Server socket created on IP: '0.0.0.0'.
140920 12:47:25 [Note] Event Scheduler: Loaded 0 events
140920 12:47:25 [Note] WSREP: Signalling provider to continue.
140920 12:47:25 [Note] WSREP: SST received: 3f056461-4076-11e4-a2b8-6216b1337d22:0
140920 12:47:25 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.39-MariaDB-wsrep'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server, wsrep_25.10.r4014
140920 12:47:25 [Note] WSREP: 1.0 (): State transfer from 0.0 () complete.
140920 12:47:25 [Note] WSREP: Shifting JOINER -> JOINED (TO: 0)
140920 12:47:25 [Note] WSREP: Member 1.0 () synced with group.
140920 12:47:25 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0)
140920 12:47:25 [Note] WSREP: Synchronized with group, ready for connections
140920 12:47:25 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

Multi Masterの確認

Secondary Nodeに同期されたサンプルデータベースのテーブルにデータを挿入します

Secondary Node

$ mysql -u root -e "insert into orenomaria.test values('galera')"
$ mysql -u root -e "select * from orenomaria.test"
+--------+
| col    |
+--------+
| galera |
| galera |
+--------+

Master Node

確認

$ mysql -u root -e "select * from orenomaria.test"
+--------+
| col    |
+--------+
| galera |
| galera |←最高
+--------+

非常に簡単にMulti Masterを構成することが出来ました。

Master Nodeを壊す

Master Node

Master Nodeのmysqldatadirを削除してプロセスをkillします

$ ps -ef|grep mysql |grep -v grep
root     23107     1  0 14:57 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/ip-10-0-0-245.pid
mysql    23338 23107  0 14:57 pts/1    00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --wsrep-provider=/usr/lib64/galera/libgalera_smm.so --log-error=/var/lib/mysql/ip-10-0-0-245.err --pid-file=/var/lib/mysql/ip-10-0-0-245.pid --wsrep_start_position=3f056461-4076-11e4-a2b8-6216b1337d22:14
$ sudo rm -rf /var/lib/mysql
$ ll mysql
ls: mysql にアクセスできません: そのようなファイルやディレクトリはありません
$ sudo kill -9 23107 23338
$ ps -ef|grep mysql
root     23552 22176  0 15:06 pts/0    00:00:00 grep mysql

死にました

Secondary Node

  • Master破損時のログ
$ cat /var/lib/mysql/`hostname`.err
140920 15:06:50 [Note] WSREP: (b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://10.0.0.245:4567
140920 15:06:51 [Note] WSREP: (b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, 'tcp://0.0.0.0:4567') reconnecting to 11c5fdd2-408b-11e4-8712-53af37cbe2b9 (tcp://10.0.0.245:4567), attempt 0
140920 15:06:54 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, OPERATIONAL, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:06:55 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:06:55 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:06:56 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:06:56 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:06:57 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:06:57 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:06:58 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:06:58 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:06:59 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:06:59 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:07:00 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:07:00 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:07:01 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:07:01 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:07:02 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:07:02 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:07:03 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:07:03 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:07:04 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) suspecting node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:07:04 [Note] WSREP: evs::proto(b9f4ed9f-408b-11e4-b85d-53a5bbcd3048, GATHER, view_id(REG,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4)) detected inactive node: 11c5fdd2-408b-11e4-8712-53af37cbe2b9
140920 15:07:05 [Note] WSREP: view(view_id(NON_PRIM,11c5fdd2-408b-11e4-8712-53af37cbe2b9,4) memb {
        b9f4ed9f-408b-11e4-b85d-53a5bbcd3048,0
} joined {
} left {
} partitioned {
        11c5fdd2-408b-11e4-8712-53af37cbe2b9,0
})
140920 15:07:05 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
140920 15:07:05 [Note] WSREP: Flow-control interval: [16, 16]
140920 15:07:05 [Note] WSREP: Received NON-PRIMARY.
140920 15:07:05 [Note] WSREP: Shifting SYNCED -> OPEN (TO: 19)
140920 15:07:05 [Note] WSREP: New cluster view: global state: 3f056461-4076-11e4-a2b8-6216b1337d22:19, view# -1: non-Primary, number of nodes: 1, my index: 0, protocol version 3
140920 15:07:05 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
140920 15:07:05 [Note] WSREP: view(view_id(NON_PRIM,b9f4ed9f-408b-11e4-b85d-53a5bbcd3048,5) memb {
        b9f4ed9f-408b-11e4-b85d-53a5bbcd3048,0
} joined {
} left {
} partitioned {
        11c5fdd2-408b-11e4-8712-53af37cbe2b9,0
})
140920 15:07:05 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
140920 15:07:05 [Note] WSREP: Flow-control interval: [16, 16]
140920 15:07:05 [Note] WSREP: Received NON-PRIMARY.
140920 15:07:05 [Note] WSREP: New cluster view: global state: 3f056461-4076-11e4-a2b8-6216b1337d22:19, view# -1: non-Primary, number of nodes: 1, my index: 0, protocol version 3
140920 15:07:05 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
  • データ確認

Master Nodeが破損したためかクエリを受け付けなくなっています

$ mysql -u root -e "select count(*) from orenomaria.test"
ERROR 1047 (08S01) at line 1: WSREP has not yet prepared node for application use

復旧

Secondary Node

  • Secondary NodeのMaster化

Secondary Nodeのwsrep_cluster_addressをgcomm://に変更します

mysql -u root -e "set global wsrep_cluster_address='gcomm://'"

検索できるようになりました。データの挿入もOKっぽいです

$ mysql -u root -e "select count(*) from orenomaria.test"
+----------+
| count(*) |
+----------+
|       15 |
+----------+
$ mysql -u root -e "insert into orenomaria.test values('galera')"
$ mysql -u root -e "select count(*) from orenomaria.test"
+----------+
| count(*) |
+----------+
|       16 |
+----------+
  • ログの確認

Masterが停止しwsrep_cluster_addressを変更するまでのログを確認してみます。
WSREP: Quorum: No node with complete state: などとかでてますね。
AIX触っていた頃のVG Quorumを思い出します。

やつも確かQuorum50%以下になったらVGがinactiveになるので3本以上のDiskでVG構成すると良いよとかあったような(うるおぼえ)

Quorum型ってことは3node以上でGalera Clusterを作っておけば1台死んでもトランザクションは止まらないのかしら？

140920 15:10:00 [Note] WSREP: Stop replication
140920 15:10:00 [Note] WSREP: Closing send monitor...
140920 15:10:00 [Note] WSREP: Closed send monitor.
140920 15:10:00 [Note] WSREP: gcomm: terminating thread
140920 15:10:00 [Note] WSREP: gcomm: joining thread
140920 15:10:00 [Note] WSREP: gcomm: closing backend
140920 15:10:00 [Note] WSREP: view((empty))
140920 15:10:00 [Note] WSREP: Received self-leave message.
140920 15:10:00 [Note] WSREP: gcomm: closed
140920 15:10:00 [Note] WSREP: Flow-control interval: [0, 0]
140920 15:10:00 [Note] WSREP: Received SELF-LEAVE. Closing connection.
140920 15:10:00 [Note] WSREP: Shifting OPEN -> CLOSED (TO: 19)
140920 15:10:00 [Note] WSREP: RECV thread exiting 0: Success
140920 15:10:00 [Note] WSREP: New cluster view: global state: 3f056461-4076-11e4-a2b8-6216b1337d22:19, view# -1: non-Primary, number of nodes: 0, my index: -1, protocol version 3
140920 15:10:00 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
140920 15:10:00 [Note] WSREP: applier thread exiting (code:0)
140920 15:10:00 [Note] WSREP: recv_thread() joined.
140920 15:10:00 [Note] WSREP: Closing replication queue.
140920 15:10:00 [Note] WSREP: Closing slave action queue.
140920 15:10:02 [Note] WSREP: rollbacker thread exiting
140920 15:10:02 [Note] WSREP: Start replication
140920 15:10:02 [Note] WSREP: Setting initial position to 3f056461-4076-11e4-a2b8-6216b1337d22:19
140920 15:10:02 [Note] WSREP: protonet asio version 0
140920 15:10:02 [Note] WSREP: Using CRC-32C (optimized) for message checksums.
140920 15:10:02 [Note] WSREP: backend: asio
140920 15:10:02 [Note] WSREP: GMCast version 0
140920 15:10:02 [Note] WSREP: (c2657b9f-408c-11e4-9081-e700f8703e0a, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
140920 15:10:02 [Note] WSREP: (c2657b9f-408c-11e4-9081-e700f8703e0a, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
140920 15:10:02 [Note] WSREP: EVS version 0
140920 15:10:02 [Note] WSREP: PC version 0
140920 15:10:02 [Note] WSREP: gcomm: connecting to group 'my_wsrep_cluster', peer ''
140920 15:10:02 [Note] WSREP: Node c2657b9f-408c-11e4-9081-e700f8703e0a state prim
140920 15:10:02 [Note] WSREP: view(view_id(PRIM,c2657b9f-408c-11e4-9081-e700f8703e0a,1) memb {
        c2657b9f-408c-11e4-9081-e700f8703e0a,0
} joined {
} left {
} partitioned {
})
140920 15:10:02 [Note] WSREP: gcomm: connected
140920 15:10:02 [Note] WSREP: Changing maximum packet size to 64500, resulting msg size: 32636
140920 15:10:02 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 19)
140920 15:10:02 [Note] WSREP: Opened channel 'my_wsrep_cluster'
140920 15:10:02 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 1
140920 15:10:02 [Note] WSREP: STATE_EXCHANGE: sent state UUID: c265d73a-408c-11e4-a52c-2a7ddfe1c27a
140920 15:10:02 [Note] WSREP: STATE EXCHANGE: sent state msg: c265d73a-408c-11e4-a52c-2a7ddfe1c27a
140920 15:10:02 [Note] WSREP: STATE EXCHANGE: got state msg: c265d73a-408c-11e4-a52c-2a7ddfe1c27a from 0 ()
140920 15:10:02 [Warning] WSREP: Quorum: No node with complete state:


        Version      : 3
        Flags        : 0x1
        Protocols    : 0 / 5 / 3
        State        : NON-PRIMARY
        Prim state   : SYNCED
        Prim UUID    : ba41f234-408b-11e4-b1fa-d37de606186c
        Prim  seqno  : 4
        First seqno  : -1
        Last  seqno  : 19
        Prim JOINED  : 2
        State UUID   : c265d73a-408c-11e4-a52c-2a7ddfe1c27a
        Group UUID   : 3f056461-4076-11e4-a2b8-6216b1337d22
        Name         : ''
        Incoming addr: '10.0.0.67:3306'

140920 15:10:02 [Note] WSREP: Partial re-merge of primary ba41f234-408b-11e4-b1fa-d37de606186c found: 1 of 2.
140920 15:10:02 [Note] WSREP: Quorum results:
        version    = 3,
        component  = PRIMARY,
        conf_id    = 4,
        members    = 1/1 (joined/total),
        act_id     = 19,
        last_appl. = 0,
        protocols  = 0/5/3 (gcs/repl/appl),
        group UUID = 3f056461-4076-11e4-a2b8-6216b1337d22
140920 15:10:02 [Note] WSREP: Flow-control interval: [16, 16]
140920 15:10:02 [Note] WSREP: Restored state OPEN -> SYNCED (19)
140920 15:10:02 [Note] WSREP: New cluster view: global state: 3f056461-4076-11e4-a2b8-6216b1337d22:19, view# 5: Primary, number of nodes: 1, my index: 0, protocol version 3
140920 15:10:02 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
140920 15:10:02 [Note] WSREP: REPL Protocols: 5 (3, 1)
140920 15:10:02 [Note] WSREP: Service thread queue flushed.
140920 15:10:02 [Note] WSREP: Assign initial position for certification: 19, protocol version: 3
140920 15:10:02 [Note] WSREP: Service thread queue flushed.
140920 15:10:02 [Note] WSREP: Synchronized with group, ready for connections
140920 15:10:02 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

破損したMaster NodeをSecondaryとして起動

  • Master NodeのSecondary化

wsrep_cluster_addressを新Master(10.0.0.67)に変更します

$ vi /etc/my.cnf.d/server.conf
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.67
wsrep_node_address=10.0.0.245
wsrep_slave_threads=1
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
query_cache_size=0
  • 起動と確認

SSTにより新Master(10.0.0.67)からデータが転送されてSuccessが返ります。

データも同期され、新しいデータの挿入もOKです

$ sudo service mysql start
Starting MySQL.......SST in progress, setting sleep higher. SUCCESS!
mysql -u root -e "select count(*) from orenomaria.test"
+----------+
| count(*) |
+----------+
|       16 |
+----------+
$ mysql -u root -e "insert into orenomaria.test values('galera')"
$ mysql -u root -e "select count(*) from orenomaria.test"
+----------+
| count(*) |
+----------+
|       17 |
+----------+

新Master Nodeでの確認

  • データの確認

新Secondaryで挿入したデータは反映されてます。最高です

$ mysql -u root -e "select count(*) from orenomaria.test"
+----------+
| count(*) |
+----------+
|       17 |
+----------+
  • ログの確認

新しいメンバー通知が走りwsrep_sst_rsyncが行われています。

完了後にmemberとしてJOINしています。

140920 15:31:37 [Note] WSREP: REPL Protocols: 5 (3, 1)
140920 15:31:37 [Note] WSREP: Service thread queue flushed.
140920 15:31:37 [Note] WSREP: Assign initial position for certification: 20, protocol version: 3
140920 15:31:37 [Note] WSREP: Service thread queue flushed.
140920 15:31:37 [Warning] WSREP: Releasing seqno 20 before 21 was assigned.
140920 15:31:39 [Note] WSREP: Member 1.0 () requested state transfer from '*any*'. Selected 0.0 ()(SYNCED) as donor.
140920 15:31:39 [Note] WSREP: Shifting SYNCED -> DONOR/DESYNCED (TO: 20)
140920 15:31:39 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
140920 15:31:39 [Note] WSREP: Running: 'wsrep_sst_rsync --role 'donor' --address '10.0.0.245:4444/rsync_sst' --auth '(null)' --socket '/var/lib/mysql/mysql.sock' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --gtid '3f056461-4076-11e4-a2b8-6216b1337d22:20''
140920 15:31:39 [Note] WSREP: sst_donor_thread signaled with 0
140920 15:31:39 [Note] WSREP: Flushing tables for SST...
140920 15:31:39 [Note] WSREP: Provider paused at 3f056461-4076-11e4-a2b8-6216b1337d22:20 (16)
140920 15:31:39 [Note] WSREP: Tables flushed.
140920 15:31:41 [Note] WSREP: resuming provider at 16
140920 15:31:41 [Note] WSREP: Provider resumed.
140920 15:31:41 [Note] WSREP: 0.0 (): State transfer to 1.0 () complete.
140920 15:31:41 [Note] WSREP: Shifting DONOR/DESYNCED -> JOINED (TO: 20)
140920 15:31:41 [Note] WSREP: Member 0.0 () synced with group.
140920 15:31:41 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 20)
140920 15:31:41 [Note] WSREP: Synchronized with group, ready for connections
140920 15:31:41 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
140920 15:31:43 [Note] WSREP: 1.0 (): State transfer from 0.0 () complete.
140920 15:31:43 [Note] WSREP: Member 1.0 () synced with group.

以上でMariaDB Galera Clusterのデュアルマスタ機能を試すことが出来ました。

引き続き検証を続けたいと思います。

最高。

5
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?