MariaDB Galera集群搭建

1、概述

1.1、目的

1.2、环境信息

• 操作系统版本CentOS Linux release 7.2.1511 (Core)
• MariaDB:10.1.14-MariaDB MariaDB Server
• 物理机1：node1：10.10.90.167
• 物理机2：node2：10.10.90.174
• 物理机3：node3：10.10.90.181

2、Galera集群搭建步骤

2.1、MariaDB安装步骤

MariaDB参数

MariaDB-10.1.14安装步骤与MySQL-5.6一致，只是一些在MySQL中的参数，在MarIaDB中不能使用或者被忽略。在QData的参数模板中，以下参数会导致数据库初始化话失败：

gtid-mode=on # GTID only
enforce-gtid-consistency=true # GTID only
slave_parallel_type=LOGICAL_CLOCK
innodb_undo_log_truncate=ON
log_timestamps=SYSTEM
slave_preserve_commit_order=ON

以下参数会使数据库初始化的过程中出现警告:

slave_pending_jobs_size_max=128M
table_open_cache = 4096
binlog-rows-query-log-events
master-info-repository
relay-log-info-repository
slave-parallel-workers

使用的参数模板如下:

[client]
loose_default-character-set = utf8
port=3306
socket=/home/mysql/data/mysqldata1/sock/mysql.sock
user=admin

[mysqldump]
quick
max_allowed_packet = 2G
default-character-set = utf8

[mysql]
no-auto-rehash
show-warnings
prompt="\\u@\\h : \\d \\r:\\m:\\s> "
default-character-set = utf8

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
user=mysql
open-files-limit = 65535

[mysqld]
#large-pages
#***********************************common parameters******************************
read_only=on
#memlock
default-storage-engine = INNODB
character-set-server=utf8
collation_server = utf8_bin

user=mysql
port=3306
socket=/home/mysql/data/mysqldata1/sock/mysql.sock
pid-file=/home/mysql/data/mysqldata1/sock/mysql.pid
datadir=/home/mysql/data/mysqldata1/mydata
tmpdir=/home/mysql/data/mysqldata1/tmpdir

skip-name-resolve
skip_external_locking

lower_case_table_names=1
event_scheduler=0
back_log=512
default-time-zone='+8:00'

max_connections = 3000
max_connect_errors=99999
max_allowed_packet = 64M
#slave_pending_jobs_size_max=128M
max_heap_table_size = 8M
max_length_for_sort_data = 16k

wait_timeout=172800
interactive_timeout=172800

net_buffer_length = 8K
read_buffer_size = 2M
read_rnd_buffer_size = 2M
sort_buffer_size = 2M
join_buffer_size = 4M
binlog_cache_size = 2M

#table_open_cache = 4096
table_open_cache_instances = 2
table_definition_cache = 4096
thread_cache_size = 512
tmp_table_size = 8M

query_cache_size=0
query_cache_type=OFF
explicit_defaults_for_timestamp=ON

#*******************************  Logs related settings ***************************
log-error=/home/mysql/data/mysqldata1/log/error.log
long_query_time = 1
slow_query_log
slow_query_log_file=/home/mysql/data/mysqldata1/slowlog/slow-query.log
log_slow_slave_statements
#log_queries_not_using_indexes

#*******************************  Replication related settings **********************

#### For Master
server-id=3306191
log-bin=/home/mysql/data/mysqldata1/binlog/mysql-bin
binlog-format=ROW
binlog-checksum=CRC32
#binlog-rows-query-log-events=1
max_binlog_size = 512M
expire_logs_days=15
sync_binlog=1
master-verify-checksum=1
#master-info-repository=TABLE
auto_increment_increment=2
auto_increment_offset=2

#### For Slave
relay-log=/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin
#relay-log-info-repository=TABLE
relay-log-recovery=1
#slave-skip-errors=1022,1032,1062,1236
slave-sql-verify-checksum=1
log_bin_trust_function_creators=1
log_slave_updates=1
slave-net-timeout=10
skip-slave-start

#******************************* MyISAM Specific options ****************************
key_buffer_size = 8M
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover_options=force

# ***************************** INNODB Specific options ****************************
#### Data options
#innodb_data_home_dir = /home/mysql/data/mysqldata1/innodb_ts
innodb_data_file_path = ibdata1:2048M:autoextend
innodb_file_per_table
innodb_file_format = barracuda
innodb_file_format_max = barracuda
innodb_file_format_check = ON
innodb_strict_mode = 1
innodb_flush_method = O_DIRECT
innodb_checksum_algorithm=crc32
innodb_autoinc_lock_mode=2

#### Buffer Pool options
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 2
innodb_max_dirty_pages_pct = 75
innodb_adaptive_flushing = ON
innodb_flush_neighbors = 0
innodb_lru_scan_depth = 4096
#innodb_change_buffering = inserts
innodb_old_blocks_time = 1000

#### Redo options
innodb_log_group_home_dir = /home/mysql/data/mysqldata1/innodb_log
innodb_log_buffer_size = 64M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_fast_shutdown = 2
innodb_support_xa = ON

#### Transaction options
innodb_thread_concurrency = 64
innodb_lock_wait_timeout = 120
innodb_rollback_on_timeout = 1
transaction_isolation = READ-COMMITTED

#### IO options
innodb_read_io_threads = 8
inn