此次是在CENTOS7.2上搭建的，與之前的6.5上稍有不同。
1、設定SELinux
在/etc/sysconfig/selinux檔案，修改SELINUX=disabled。
2、設定防火牆，或者關閉防火牆，安裝完成後再開啟。
wsrep(Write-Set Replication)
#mkdir -p /app/mysql/3306
#mkdir -p /app/mysql/binlog
1、安裝RPM包
# rpm -e mariadb-libs-1:5.5.52-1.el7.x86_64 --nodeps
# rpm -e mariadb-libs-5.5.44-2.el7.centos.x86_64 --nodeps
# rpm -ivh mysql-wsrep-common-5.7-5.7.21-25.14.el7.x86_64.rpm
warning: mysql-wsrep-common-5.7-5.7.21-25.14.el7.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID bc19ddba: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-wsrep-common-5.7-5.7.21-25.################################# [100%]
[root@mysql01 soft]# rpm -ivh mysql-wsrep-libs-5.7-5.7.21-25.14.el7.x86_64.rpm
warning: mysql-wsrep-libs-5.7-5.7.21-25.14.el7.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID bc19ddba: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-wsrep-libs-5.7-5.7.21-25.14################################# [100%]
[root@mysql01 soft]# rpm -ivh galera-3-25.3.23-2.el7.x86_64.rpm
warning: galera-3-25.3.23-2.el7.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID bc19ddba: NOKEY
error: Failed dependencies:
        libboost_program_options.so.1.53.0()(64bit) is needed by galera-3-25.3.23-2.el7.x86_64
# yum install boost-devel.x86_64
# rpm -ivh galera-3-25.3.23-2.el7.x86_64.rpm
warning: galera-3-25.3.23-2.el7.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID bc19ddba: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:galera-3-25.3.23-2.el7           ################################# [100%]
[root@mysql01 soft]# rpm -ivh mysql-wsrep-client-5.7-5.7.21-25.14.el7.x86_64.rpm
warning: mysql-wsrep-client-5.7-5.7.21-25.14.el7.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID bc19ddba: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-wsrep-client-5.7-5.7.21-25.################################# [100%]
# rpm -ivh mysql-wsrep-server-5.7-5.7.21-25.14.el7.x86_64.rpm
warning: mysql-wsrep-server-5.7-5.7.21-25.14.el7.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID bc19ddba: NOKEY
error: Failed dependencies:
        lsof is needed by mysql-wsrep-server-5.7-5.7.21-25.14.el7.x86_64
        socat is needed by mysql-wsrep-server-5.7-5.7.21-25.14.el7.x86_64
# yum install lsof -y
# yum install socat -y
# rpm -ivh mysql-wsrep-server-5.7-5.7.21-25.14.el7.x86_64.rpm
warning: mysql-wsrep-server-5.7-5.7.21-25.14.el7.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID bc19ddba: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-wsrep-server-5.7-5.7.21-25.################################# [100%]
# rpm -ivh mysql-wsrep-libs-compat-5.7-5.7.21-25.14.el7.x86_64.rpm
warning: mysql-wsrep-libs-compat-5.7-5.7.21-25.14.el7.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID bc19ddba: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-wsrep-libs-compat-5.7-5.7.2################################# [100%]
# yum install rsync -y

# rpm -ivh jemalloc-3.6.0-8.el7.centos.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:jemalloc-3.6.0-8.el7.centos ################################# [100%]
# ls -l /usr/lib64/libjemalloc.so.1
-rwxr-xr-x 1 root root 232808 Sep 14 2014 /usr/lib64/libjemalloc.so.1

2、建立並配置引數檔案
---建立並配置my.cnf檔案
# cat /etc/my.cnf
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
[mysqld]
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
explicit_defaults_for_timestamp=true
log_timestamps=SYSTEM
character_set_server = utf8
server-id=31
basedir=/usr
user=mysql
skip-name-resolve
lower_case_table_names=1
max_connections=1000
max_connect_errors=1000
datadir=/app/mysql/3306
default-storage-engine = InnoDB
socket=/app/mysql/3306/mysql.sock
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=6G
thread_cache_size=64
max_allowed_packet=128M
#pid_file=/app/mysql/mysqld.pid
port=3306
log-error=/app/mysql/mysql.err
#add for bin-log
log-bin=ON
log-bin=/app/mysql/binlog/mysql-bin
log-bin-index=/app/mysql/binlog/mysql-bin.index
expire-logs-days=32
max-binlog-size=512M
####binlog_format=mixed
binlog-format=ROW
log-slave-updates=on
##add for cluster
wsrep_cluster_name='uni_cluster'
wsrep-provider=/usr/lib64/galera-3/libgalera_smm.so
wsrep_node_name = uni01
#wsrep_cluster_address=gcomm://
wsrep_cluster_address=gcomm://10.70.67.31,10.70.161.115,10.70.161.91
wsrep_node_address='10.70.67.31'
####wsrep_node_incoming_address='10.70.161.197:4567'
wsrep_provider_options ="gmcast.listen_addr=tcp://10.70.67.31:4567"
wsrep_sst_donor='uni01,uni02,uni03'
wsrep_sst_method=rsync
####wsrep_slave_threads=16
wsrep_sst_auth=tyuni:TyunI#2018
slow_query_log=on
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[client]
socket=/app/mysql/3306/mysql.sock
#####default-character-set=utf8

[mysql]
#default-character-set=utf8
socket=/app/mysql/3306/mysql.sock

[mysqldump]
max_allowed_packet = 512M

[mysqld_safe]
malloc-lib=/usr/lib64/libjemalloc.so.1

# id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)
# chmod -R 775 /app/mysql
# chown -R mysql.mysql /app/mysql

mysqld --initialize --user=mysql
4eVsE:i0okKy

# mysql_ssl_rsa_setup
cd /usr/lib/systemd/system
vi mysqld.service

# cat mysqld.service
# Copyright (c) 2015, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# systemd service file for MySQL forking server
# Modified for wsrep (Galera): Recovery
#

[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

Type=forking

#PIDFile=/var/run/mysqld/mysqld.pid
PIDFile=/app/mysql/mysqld.pid

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Execute pre and post scripts as root
PermissionsStartOnly=true

# Needed to create system tables and to check for cluster crash recovery
#ExecStartPre=/usr/bin/mysqld_pre_systemd --pre

# Start main service
#ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/app/mysql/mysqld.pid $MYSQLD_OPTS $MYSQLD_RECOVER_START
ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/app/mysql/mysqld.pid $MYSQLD_OPTS

# Needed to reset cluster crash recovery
#ExecStartPost=/usr/bin/mysqld_pre_systemd --post

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 65535

Restart=on-failure

# Dirty hack to prevent fast restart in case of configuration problem.
# Longer-term fix will be to ensure exit status 1 for "RestartPreventExitStatus=1" below.
RestartSec=1

RestartPreventExitStatus=1

PrivateTmp=false

systemctl daemon-reload
systemctl enable mysqld.service
systemctl is-enabled mysqld

systemctl start mysqld.service

mysql -uroot -p

set password=password("My#Sql2018");
grant usage on *.* to identified by 'TyunI#2018';
grant all privileges on *.* to ;
flush privileges;

systemctl stop mysqld.service
systemctl start mysqld.service
tyuni:TyunI#2018

mysqld start --wsrep-cluster-address="gcomm://"

節點2：
# yum install rsync -y
# cat /etc/my.cnf.d/wsrep.cnf
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
explicit_defaults_for_timestamp=true
log_timestamps=SYSTEM
character_set_server = utf8
server-id=198
basedir=/usr
user=mysql
skip-name-resolve
lower_case_table_names=1
max_connections=1000
max_connect_errors=1000
datadir=/app/mysql/3306
default-storage-engine = InnoDB
socket=/app/mysql/3306/mysql.sock
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=6G
#pid_file=/app/mysql/mysqld.pid
port=3306
log-error=/app/mysql/mysql.err
#add for bin-log
log-bin=ON
log-bin=/app/mysql/binlog/mysql-bin
log-bin-index=/app/mysql/binlog/mysql-bin.index
expire-logs-days=15
max-binlog-size=512M
####binlog_format=mixed
binlog-format=ROW
log-slave-updates=on
##add for cluster
wsrep_cluster_name='uni_cluster'
wsrep-provider=/usr/lib64/galera-3/libgalera_smm.so
wsrep_node_name = uni02
wsrep_cluster_address=gcomm://10.70.161.197,10.70.161.198,10.70.161.91
wsrep_node_address='10.70.161.198'
####wsrep_node_incoming_address='10.70.161.197:4567'
wsrep_provider_options ="gmcast.listen_addr=tcp://10.70.161.198:4567"
####wsrep_cluster_address=gcomm://192.168.56.111:4567,192.168.56.112:4567,192.168.56.113:4567
wsrep_sst_donor='uni01,uni02,uni03'
wsrep_sst_method=rsync
####wsrep_slave_threads=16
wsrep_sst_auth=tyuni:TyunI#2018
slow_query_log=on
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[client]
socket=/app/mysql/3306/mysql.sock
#####default-character-set=utf8

[mysql]
#default-character-set=utf8
socket=/app/mysql/3306/mysql.sock

[mysqldump]
max_allowed_packet = 512M

[mysqld_safe]
malloc-lib=/usr/lib64/libjemalloc.so.1

mysql> show global status like 'wsrep%';
+------------------------------+---------------------------------------+
| Variable_name                | Value                                 |
+------------------------------+---------------------------------------+
| wsrep_local_state_uuid       | 41c1fd6a-485a-11e8-bbca-17d012bd2e14 |
| wsrep_protocol_version       | 8                                     |
| 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         | 220                                   |
| wsrep_local_commits          | 0                                     |
| wsrep_local_cert_failures    | 0                                     |
| wsrep_local_replays          | 0                                     |
| wsrep_local_send_queue       | 0                                     |
| wsrep_local_send_queue_max   | 1                                     |
| wsrep_local_send_queue_min   | 0                                     |
| wsrep_local_send_queue_avg   | 0.000000                              |
| wsrep_local_recv_queue       | 0                                     |
| wsrep_local_recv_queue_max   | 1                                     |
| wsrep_local_recv_queue_min   | 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.70.161.198:3306,10.70.161.197:3306 |
| 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_gcomm_uuid             | 8b09545b-485e-11e8-a794-4e977f6db8c0 |
| wsrep_cluster_conf_id        | 46                                    |
| wsrep_cluster_size           | 2                                     |
| wsrep_cluster_state_uuid     | 41c1fd6a-485a-11e8-bbca-17d012bd2e14 |
| 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       | 3.23(rac090bc)                        |
| wsrep_ready                  | ON                                    |
+------------------------------+---------------------------------------+
57 rows in set (0.01 sec)

節點3：
# yum install rsync -y
# cat /etc/my.cnf.d/wsrep.cnf
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

[mysqld]

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
explicit_defaults_for_timestamp=true
log_timestamps=SYSTEM
character_set_server = utf8
server-id=91
basedir=/usr
user=mysql
skip-name-resolve
lower_case_table_names=1
max_connections=1000
max_connect_errors=1000
datadir=/app/mysql/3306
default-storage-engine = InnoDB
socket=/app/mysql/3306/mysql.sock
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=6G
#pid_file=/app/mysql/mysqld.pid
port=3306
log-error=/app/mysql/mysql.err
#add for bin-log
log-bin=ON
log-bin=/app/mysql/binlog/mysql-bin
log-bin-index=/app/mysql/binlog/mysql-bin.index
expire-logs-days=15
max-binlog-size=512M
####binlog_format=mixed
binlog-format=ROW
log-slave-updates=on
##add for cluster
wsrep_cluster_name='uni_cluster'
wsrep-provider=/usr/lib64/galera-3/libgalera_smm.so
wsrep_node_name = uni03
wsrep_cluster_address=gcomm://10.70.161.197,10.70.161.198,10.70.161.91
wsrep_node_address='10.70.161.91'
####wsrep_node_incoming_address='10.70.161.197:4567'
wsrep_provider_options ="gmcast.listen_addr=tcp://10.70.161.91:4567"
####wsrep_cluster_address=gcomm://192.168.56.111:4567,192.168.56.112:4567,192.168.56.113:4567
wsrep_sst_donor='uni01,uni02,uni03'
wsrep_sst_method=rsync
####wsrep_slave_threads=16
wsrep_sst_auth=tyuni:TyunI#2018
slow_query_log=on
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[client]
socket=/app/mysql/3306/mysql.sock
#####default-character-set=utf8

[mysql]
#default-character-set=utf8
socket=/app/mysql/3306/mysql.sock

[mysqldump]
max_allowed_packet = 512M

[mysqld_safe]
malloc-lib=/usr/lib64/libjemalloc.so.1

mysql> show global status like 'wsrep%';
+------------------------------+---------------------------------------------------------+
| Variable_name                | Value                                                   |
+------------------------------+---------------------------------------------------------+
| wsrep_local_state_uuid       | 41c1fd6a-485a-11e8-bbca-17d012bd2e14                    |
| wsrep_protocol_version       | 8                                                       |
| wsrep_last_committed         | 1                                                       |
| 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         | 289                                                     |
| wsrep_local_commits          | 0                                                       |
| wsrep_local_cert_failures    | 0                                                       |
| wsrep_local_replays          | 0                                                       |
| wsrep_local_send_queue       | 0                                                       |
| wsrep_local_send_queue_max   | 1                                                       |
| wsrep_local_send_queue_min   | 0                                                       |
| wsrep_local_send_queue_avg   | 0.000000                                                |
| wsrep_local_recv_queue       | 0                                                       |
| wsrep_local_recv_queue_max   | 1                                                       |
| wsrep_local_recv_queue_min   | 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.70.161.91:3306,10.70.161.198:3306,10.70.161.197:3306 |
| wsrep_desync_count           | 0                                                       |
| wsrep_evs_delayed            |                                                         |
| wsrep_evs_evict_list         |                                                         |
| wsrep_evs_repl_latency       | 0.00046872/0.000656263/0.000845957/0.000152154/7        |
| wsrep_evs_state              | OPERATIONAL                                             |
| wsrep_gcomm_uuid             | 68a3d1f0-4863-11e8-bb94-96ef6746cd06                    |
| wsrep_cluster_conf_id        | 47                                                      |
| wsrep_cluster_size           | 3                                                       |
| wsrep_cluster_state_uuid     | 41c1fd6a-485a-11e8-bbca-17d012bd2e14                    |
| 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       | 3.23(rac090bc)                                          |
| wsrep_ready                  | ON                                                      |
+------------------------------+---------------------------------------------------------+
57 rows in set (0.00 sec)

----------------------End-By:TangYun-2018.04.25---------------------------------------------

mysql5.7GeleraCluster安裝部署(二)

相關文章