目標
情境
db1: 192.168.48.129
db2: 192.168.48.130
db3: 192.168.48.131
db4: 192.168.48.132 (for replication)
db2: 192.168.48.130
db3: 192.168.48.131
db4: 192.168.48.132 (for replication)
安裝
安裝基本程式 (DB1 - DB4)
cat >> init.sh << EOF
#!/bin/bash
# if needed
#yum install open-vm-tools -y
# Install essential package & update
yum install epel-release -y
yum install mailx postfix openssh-clients rsync xinetd ftp man bash-completion bash-completion-extras setuptool vim-enhanced screen telnet net-tools wget mlocate bind-utils glances iptables-services lsof tmux ntpdate zip unzip p7zip bmon nload -y
yum update -y
chmod +x /etc/rc.d/rc.local
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/sysconfig/selinux
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
systemctl disable firewalld
reboot
EOF
sh init.sh
安裝 MariaDB (DB1 - DB4)
cat >> /etc/yum.repos.d/mariadb.repo << EOF
# MariaDB 10.3 CentOS repository list - created 2019-03-16 10:14 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF
yum install MariaDB-server MariaDB-client -y
設定
DB1 - DB3 建立設定腳本
vi db.sh三台之
THIS_HOST_IP 與 THIS_CLUSTER_NODE_NAME 兩者參數必不相同，其餘依實際狀況修改即可。innodb_buffer_pool_size 依照實際記憶體調整，通常為總記憶體之 70%
#!/bin/sh
# vars
DB_ROOT_PASSWORD=root
CLUSTER_NAME=smartrobotcluster
CLUSTER_USERNAME=smartrobotdb
CLUSTER_USER_PASSWORD=smartrobotdb123
CLUSTER_HOSTS_IP="192.168.48.129,192.168.48.130,192.168.48.131"
THIS_HOST_IP=192.168.48.129
THIS_CLUSTER_NODE_NAME=smartrobotdb1
# delete unuse User
mysql -uroot -e "DROP USER 'root'@'localhost.localdomain';"
mysql -uroot -e "DROP USER ''@'localhost.localdomain';"
mysql -uroot -e "DROP USER ''@'localhost';"
mysql -uroot -e "DROP USER 'root'@'::1';"
# create remote user
mysql -uroot -e "CREATE USER 'root'@'%' IDENTIFIED BY '${DB_ROOT_PASSWORD}';"
mysql -uroot -e "GRANT ALL privileges ON *.* TO 'root'@'%';"
# create password for cluster user
mysql -uroot -e "CREATE USER '${CLUSTER_USERNAME}'@'%' IDENTIFIED BY '${CLUSTER_USER_PASSWORD}';"
mysql -uroot -e "GRANT ALL privileges ON *.* TO '${CLUSTER_USERNAME}'@'%';"
# create password for root
mysql -uroot -e "FLUSH PRIVILEGES;"
mysql -uroot -e "SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('${DB_ROOT_PASSWORD}');"
mysql -uroot -e "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('${DB_ROOT_PASSWORD}');"
cat > /etc/my.cnf.d/cluster.cnf << EOF
[server]
# this is only for the mysqld standalone daemon
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
character-set-client-handshake = false
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
binlog_format=ROW
wsrep_sst_method=rsync
# CHANGE: Username and password you created for the SST cluster MySQL user
wsrep_sst_auth=${CLUSTER_USERNAME}:${CLUSTER_USER_PASSWORD}
# CHANGE: Uncomment and set your desired cluster name
wsrep_cluster_name='${CLUSTER_NAME}'
# CHANGE: Uncomment and Add all your servers
wsrep_cluster_address="gcomm://${CLUSTER_HOSTS_IP}"
# CHANGE: Uncomment and set IP address of this server
wsrep_node_address='${THIS_HOST_IP}'
# CHANGE: Uncomment and set the node name of this server
wsrep_node_name='${THIS_CLUSTER_NODE_NAME}'
[embedded]
[mariadb]
# When set to 0.0.0.0, the server listens to remote connections
bind-address=0.0.0.0
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
# The buffer pool contains buffered data and the index. This is usually set to 70% of physical memory.
innodb_buffer_pool_size = 1024M
# Redo logs ensure that write operations are fast, reliable, and recoverable after a crash
innodb_log_file_size = 512M
# A larger value will give the server more time to recycle idled connections
max_connections = 5000
# Speed up the table space transmission and optimize the debris management performance
innodb_file_per_table = 1
# The log buffer allows transactions to run without having to flush the log to disk before the transactions commit
innodb_log_buffer_size = 128M
# The setting of 2 enables the most data integrity and is suitable for Master in MySQL cluster
innodb_flush_log_at_trx_commit = 2
query_cache_size = 0
max_allowed_packet=64M
[mariadb-10.3]
EOF
DB01(第一台初始化) 執行腳本並建立 cluster
為避免啟動有問題，不讓 MariaDB 開機自動啟動
systemctl start mariadb
systemctl disable mariadb
sh db.sh
systemctl stop mariadb
galera_new_cluster
mysql -uroot -proot -e 'SHOW STATUS LIKE"wsrep_cluster_size";'
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 1 |
+--------------------+-------+
mysql -uroot -proot -e 'SHOW STATUS LIKE"wsrep_cluster_status";'
+----------------------+---------+
| Variable_name | Value |
+----------------------+---------+
| wsrep_cluster_status | Primary |
+----------------------+---------+
mysql -uroot -proot -e 'SHOW STATUS LIKE"wsrep_local_state_comment";'
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| wsrep_local_state_comment | Synced |
+---------------------------+--------+
檢查 DB01 grastate.dat 狀態
cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid: 1397c88b-47f2-11e9-bdad-4609fe134b3c
seqno: -1
safe_to_bootstrap: 1
關閉 DB01 再回去檢查 grastate.dat 狀態
systemctl stop mariadb
cat /var/lib/mysql/grastate.dat
可以發現 seqno 歸 0
# GALERA saved state
version: 2.1
uuid: 1397c88b-47f2-11e9-bdad-4609fe134b3c
seqno: 0
safe_to_bootstrap: 1
再次啟動 DB01
cluster 無 node 起動，即此 node 為第一個起動，則輸入以下指令起動(不侷限於哪一台 DB)
galera_new_cluster
如 cluster 已有 node 起動中，則使用以下指令起動
systemctl start mariadb
可以發現
grastate.dat 的 uuid 不會改變cat /var/lib/mysql/grastate.dat# GALERA saved state
version: 2.1
uuid: 1397c88b-47f2-11e9-bdad-4609fe134b3c
seqno: -1
safe_to_bootstrap: 0
DB2 與 DB3 起動
修改完設定腳本參數如下
DB2
# vars
DB_ROOT_PASSWORD=root
CLUSTER_NAME=smartrobotcluster
CLUSTER_USERNAME=smartrobotdb
CLUSTER_USER_PASSWORD=smartrobotdb123
CLUSTER_HOSTS_IP="192.168.48.129,192.168.48.130,192.168.48.131"
THIS_HOST_IP=192.168.48.130
THIS_CLUSTER_NODE_NAME=smartrobotdb2
DB3
# vars
DB_ROOT_PASSWORD=root
CLUSTER_NAME=smartrobotcluster
CLUSTER_USERNAME=smartrobotdb
CLUSTER_USER_PASSWORD=smartrobotdb123
CLUSTER_HOSTS_IP="192.168.48.129,192.168.48.130,192.168.48.131"
THIS_HOST_IP=192.168.48.131
THIS_CLUSTER_NODE_NAME=smartrobotdb3
確定 DB1 與 DB2 之 MariaDB 服務開啟，於 DB2 與 DB3 主機上執行腳本，停止服務後，再重啟動 MariaDB
systemctl start mariadb
systemctl disable mariadb
sh db.sh
systemctl stop mariadb
systemctl start mariadb
於任一台 DB 直行以下指令查詢狀態，應確認黃底部分是否正常
mysql -uroot -proot -e 'SHOW STATUS LIKE"%wsrep%";'
+------------------------------+-------------------------------------------------------------+
| Variable_name | Value |
+------------------------------+-------------------------------------------------------------+
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.000000 |
| wsrep_causal_reads | 0 |
| wsrep_cert_deps_distance | 1.000000 |
| wsrep_cert_index_size | 1 |
| wsrep_cert_interval | 0.000000 |
| wsrep_cluster_conf_id | 5 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 1397c88b-47f2-11e9-bdad-4609fe134b3c |
| wsrep_cluster_status | Primary |
| wsrep_cluster_weight | 3 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.000000 |
| wsrep_connected | ON |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| 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 | bc9bd1a8-4878-11e9-8929-b3047f02c3f8 |
| wsrep_incoming_addresses | 192.168.48.130:3306,192.168.48.129:3306,192.168.48.131:3306 |
| wsrep_last_committed | 1 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_cached_downto | 1 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_commits | 0 |
| wsrep_local_index | 1 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.166667 |
| 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 | 1397c88b-47f2-11e9-bdad-4609fe134b3c |
| wsrep_open_connections | 0 |
| wsrep_open_transactions | 0 |
| wsrep_protocol_version | 9 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 25.3.25(r3836) |
| wsrep_ready | ON |
| wsrep_received | 6 |
| wsrep_received_bytes | 1197 |
| wsrep_repl_data_bytes | 362 |
| wsrep_repl_keys | 1 |
| wsrep_repl_keys_bytes | 32 |
| wsrep_repl_other_bytes | 0 |
| wsrep_replicated | 1 |
| wsrep_replicated_bytes | 464 |
| wsrep_thread_count | 2 |
+------------------------------+-------------------------------------------------------------+
測試
建立資料
於 cluster 中 DB3 建立新資料庫與表，並加入資料
mysql -uroot -proot -e "CREATE DATABASE test_db;"
mysql -uroot -proot -e 'USE test_db; CREATE TABLE users (id INT AUTO_INCREMENT, name VARCHAR(30), datum TIMESTAMP DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY(id));'
mysql -uroot -proot -e "USE test_db; INSERT INTO users(name) VALUES ('Marko');"
於 DB1 中查詢
mysql -uroot -proot -e "USE test_db; select * from users;"
+----+-------+---------------------+
| id | name | datum |
+----+-------+---------------------+
| 5 | Marko | 2019-03-17 15:22:11 |
+----+-------+---------------------+
關閉測試
關閉 DB2
systemctl stop mariadb
觀察 DB2 的
/var/lib/mysql/grastate.dat 中的 seqno 出現 5cat /var/lib/mysql/grastate.dat# GALERA saved state
version: 2.1
uuid: 1397c88b-47f2-11e9-bdad-4609fe134b3c
seqno: 5
safe_to_bootstrap: 0
於 DB1 加入新資料
mysql -uroot -proot -e "USE test_db; INSERT INTO users(name) VALUES ('Mmao');"
於 DB1 與 DB3 檢查
mysql -uroot -proot -e "USE test_db; select * from users;"
+----+-------+---------------------+
| id | name | datum |
+----+-------+---------------------+
| 5 | Marko | 2019-03-17 15:22:11 |
| 8 | Mmao | 2019-03-17 15:45:17 |
+----+-------+---------------------+
再將 DB2 起動檢查，可以發現資料均有同步
systemctl start mariadbmysql -uroot -proot -e "USE test_db; select * from users;"
+----+-------+---------------------+
| id | name | datum |
+----+-------+---------------------+
| 5 | Marko | 2019-03-17 15:22:11 |
| 8 | Mmao | 2019-03-17 15:45:17 |
+----+-------+---------------------+
啟動與關閉
關閉順序
輸入
systemctl start mariadb 關閉，務必等待每台正常關閉後再關閉下一台開啟順序
觀察每台 DB 之
grastate.dat 中 seqno 數值cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid: 1397c88b-47f2-11e9-bdad-4609fe134b3c
seqno: 6
safe_to_bootstrap: 0
如果 safe_to_bootstrap 為 1 亦可作為第一台啟動
galera_new_cluster- 數值最大的使用以下指令起動
galera_new_cluster
餘下則以
systemctl start mariadb 起動，如每台數值一樣大，則查看 safe_to_bootstrap 數值是否為 1，，如都為 0 則可任選一台將 safe_to_bootstrap 改為 1 後，再以 galera_new_cluster 起動即可。safe_to_bootstrap 都為 0 的情況多半發生在 cluster 同步完成後，再停止所有 node 時發生。
REF
https://dba.stackexchange.com/questions/151941/how-to-restart-mariadb-galera-cluster
https://www.itread01.com/content/1533470189.html
https://dba.stackexchange.com/questions/151941/how-to-restart-mariadb-galera-cluster
https://www.itread01.com/content/1533470189.html
其他應用
MariaDB Cluster Replicate to Other Slave Node
用途
將 Cluster 其中一臺 node 作為 master replication node 複寫資料至其他單獨 node 中
修改其中一臺 cluster node 設定檔並重啟
此以 DB2 作為 master 為例
cat > /etc/my.cnf.d/rep.cnf << EOF
[mysqld]
server-id = 1
binlog-ignore-db = "mysql"
binlog-format = row
log-bin=mysql-bin
datadir=/var/lib/mysql
innodb_flush_log_at_trx_commit=1
sync_binlog=1
EOF
systemctl restart mariadb
確認 DB2 有正確起用 master
mysql -uroot -proot -e 'SHOW MASTER STATUS;'
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| binlog.000004 | 328 | | mysql |
+------------------+----------+--------------+------------------+
於 DB2 建立複寫使用者並給與權限
mysql -uroot -proot -e "create user 'replusr'@'%' identified by 'replusr';"
mysql -uroot -proot -e "grant replication slave on *.* to 'replusr'@'%';"
mysql -uroot -proot -e "flush privileges;"
於 DB1, DB2, DB3 建立以下設定
server-id 每台不同
cat > /etc/my.cnf.d/rep.cnf << EOF
[mysqld]
server-id = 1 # 1 for master1, 2 for master2, 3 for master3
binlog-ignore-db = "mysql"
log_bin=binlog
binlog_format=ROW
datadir=/var/lib/mysql
innodb_flush_log_at_trx_commit=1
sync_binlog=1
log_slave_updates=1
expire_logs_days=7
EOF
設定 DB4 的複寫設定
cat > /etc/my.cnf.d/rep.cnf << EOF
[mysqld]
server_id=4
log_bin=binlog
log_slave_updates=1
expire_logs_days=7
binlog_format=ROW
slave_net_timeout=60
EOF
systemctl restart mariadb設定 DB4 之 hosts 與 root 密碼
echo "192.168.48.130 db2" >> /etc/hosts
mysql -uroot -e "SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('${DB_ROOT_PASSWORD}');"
mysql -uroot -e "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('${DB_ROOT_PASSWORD}');"
於 DB4 建立複寫資料庫
mysql -uroot -proot -e "CREATE DATABASE test_db;"
# dump 原資料庫至 db4 上並還原
mysqldump -uroot -proot -h192.168.48.129 test_db > testdb.sql
mysql -uroot -proot test_db < testdb.sql
mysql -uroot -proot -e "CHANGE MASTER TO MASTER_HOST='db2', MASTER_PORT=3306, MASTER_USER='replusr', MASTER_PASSWORD='replusr', MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=328;"
# 需設定 GTIDs 否則 cluster 中其他 node 寫入不會同步到 slave node
mysql -uroot -proot -e "CHANGE MASTER TO MASTER_HOST = 'db2', MASTER_PORT = 3306, MASTER_USER = 'replusr', MASTER_PASSWORD = 'replusr', MASTER_USE_GTID=current_pos;"
# 確認沒問題後，再將 slave 起動
mysql -uroot -proot -e "START SLAVE;"
確認同步狀態
mysql -uroot -proot -e "SHOW SLAVE STATUS \G;"
Slave_IO_State: Waiting for master to send event
Master_Host: db2
Master_User: replusr
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 1563
Relay_Log_File: db4-relay-bin.000002
Relay_Log_Pos: 792
Relay_Master_Log_File: binlog.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1563
Relay_Log_Space: 1099
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos: 0-4-42
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 1
疑難排解
資料回寫錯誤
如果出現某臺 cluster 無法正常啟動類似如下錯誤
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 9120034833
150125 16:12:51 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 150125 16:12:51 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
To report this bug, see http://kb.askmonty.org/en/reporting-bugs
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Server version: 5.5.37-MariaDB-log
key_buffer_size=268435456
read_buffer_size=1048576
max_used_connections=0
max_threads=1002
thread_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2332093 K bytes of memory
41 Hope that.
且
/var/lib/mysql/grastate.dat 中出現version: 2.1
uuid: 00000000-0000-0000-0000-000000000000
seqno: -1
於
server.cnf 中的 [mariadb] 加入innodb_force_recovery=6
innodb_purge_thread=0
再次啟動 MariaDB 看看
或
service mysql start或
systemctl start mariadb
如果依舊無法正常啟動
則
則
- 修改
/etc/my.cnf.d/server.cnf或有放置 galera 設定之設定檔，將 galera cluster 相關設定內容暫時註解，包含innodb_force_recovery = 6與innodb_purge_thread = 0。 - 將
/var/lib/mysql資料夾內容清空，再重新建立資料庫
cd /var/lib/mysql/
rm -fr *
mysql_install_db
chown mysql.mysql -R /var/lib/mysql/
- 起動資料庫後便會自動同步寫回資料
# for MariaDB 10.0
service mysql start
# for MariaDB 10.3
systemctl start mariadb
REF
https://mariadb.com/kb/en/library/getting-started-with-mariadb-galera-cluster/
https://severalnines.com/blog/updated-how-bootstrap-mysql-or-mariadb-galera-cluster
https://blog.51cto.com/tryingstuff/1922986
https://dba.stackexchange.com/questions/157500/how-to-recover-mariadb-galera-cluster-after-full-crash
http://hank20.blogspot.com/2018/04/centos7mariadb103galera-cluster.html
https://www.cnblogs.com/nulige/p/8087918.html
https://mariadb.com/kb/en/library/getting-started-with-mariadb-galera-cluster/
https://severalnines.com/blog/updated-how-bootstrap-mysql-or-mariadb-galera-cluster
https://blog.51cto.com/tryingstuff/1922986
https://dba.stackexchange.com/questions/157500/how-to-recover-mariadb-galera-cluster-after-full-crash
http://hank20.blogspot.com/2018/04/centos7mariadb103galera-cluster.html
https://www.cnblogs.com/nulige/p/8087918.html
沒有留言:
張貼留言