昔つかってた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| | 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| | 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 Oyloaded 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のデュアルマスタ機能を試すことが出来ました。
引き続き検証を続けたいと思います。
最高。