搭建高可用mysql系列(2)-- Percona XtraDB Cluster 安装

本文主要介绍在 centos 下 Percona XtraDB Cluster(下文简称PXC) 的安装, 个人的系统版本信息如下:

[root@c2-d09 worker]# more /etc/redhat-release
CentOS Linux release 7.3.1611 (Core)

一  安装前的准备工作

(1) 关闭 SELinux

  这是因为SELinux 可能会限制 PXC 的数据访问,关闭的方法为命令行下执行: 

setenforce 0

   为了防止系统重启后上述操作实效,还需要修改  /etc/selinux/config 文件,将 SELINUX 设置为:permissive 即:

SELINUX=permissive

(2)确保 PXC 默认使用的的4个端口没有被防火墙阻止:

  3306 mysql 实例端口

  4444:用于SST传送的端口 ,可以在配置中进行修改,比如:wsrep_sst_receive_address=10.11.12.205:5555 

  4567:pxc cluster相互通讯的端口,可以在配置中进行修改,比如:wsrep_provider_options ="gmcast.listen_addr=tcp://0.0.0.0:4010; "

  4568:用于IST传送的端口,可以在配置中进行修改,比如:wsrep_provider_options = "ist.recv_addr=10.11.12.206:7777; "

二  安装的具体步骤

(1)下载安装文件并解压到安装目录,本文的目录为:/home/worker/XtraDB_Cluster/XtraDB_3306 

wget  https://www.percona.com/downloads/Percona-XtraDB-Cluster-57/Percona-XtraDB- ,!Cluster-5.7.14-26.17/binary/tarball/Percona-XtraDB-Cluster-5.7.14-rel8-26.17.1. ,!Linux.x86_64.ssl101.tar.gz 

 (2)初始化第一个节点

  (a)切换到安装目录,生成日志所需文件夹    

cd /home/worker/XtraDB_Cluster/XtraDB_3306
mkdir -p logs/bin_log logs/innodb_log logs/relay_log var

  (b)编辑 my.cnf 文件,本文用到的第一个节点 my.cnf 文件如下:

[client]
port            = 3306
socket          = /home/worker/XtraDB_Cluster/XtraDB_3306/var/mysql.sock

[mysqld]
bind_address    = 0.0.0.0
port            = 3306
socket          = /home/worker/XtraDB_Cluster/XtraDB_3306/var/mysql.sock
user            = worker
datadir         = /home/worker/XtraDB_Cluster/XtraDB_3306/data
pid_file        = /home/worker/XtraDB_Cluster/XtraDB_3306/var/mysqld.pid
skip_name_resolve

gtid_mode = ON
enforce_gtid_consistency = ON

tmpdir          = /tmp
slave_load_tmpdir = /tmp


back_log                = 1024
max_connections         = 4096
max_connect_errors      = 4096
table_open_cache        = 1024
max_allowed_packet      = 1M

tmp_table_size          = 128M
max_heap_table_size     = 64M
read_buffer_size        = 16M
read_rnd_buffer_size    = 16M
sort_buffer_size        = 32M
join_buffer_size        = 8M
query_cache_size        = 128M
query_cache_limit       = 32K
open_files_limit        = 65535

table_open_cache        = 1024
table_definition_cache  = 4096

thread_cache_size       = 256
thread_stack            = 192K
transaction_isolation   = REPEATABLE-READ

ft_min_word_len         = 4
# Set if mysql only or small db
#memlock

# Common logs
log_error               = /home/worker/XtraDB_Cluster/XtraDB_3306/logs/error.log
log_warnings
slow_query_log          = 1
slow_query_log_file     = /home/worker/XtraDB_Cluster/XtraDB_3306/logs/slow.log
long_query_time         = 2

general_log             = 0
general_log_file        = /home/worker/XtraDB_Cluster/XtraDB_3306/logs/general.log

# Replication
server_id = 12616010
#log-slave-updates = 1
log_bin                 = /home/worker/XtraDB_Cluster/XtraDB_3306/logs/bin_log/mysql-bin
log_bin_index           = /home/worker/XtraDB_Cluster/XtraDB_3306/logs/bin_log/mysql-bin.index
binlog_format           = ROW
binlog_cache_size       = 32M
expire_logs_days        = 31
sync_binlog             = 1
log_slave_updates       = 1

relay_log               = /home/worker/XtraDB_Cluster/XtraDB_3306/logs/relay_log/mysql-relay-bin
relay_log_index         = /home/worker/XtraDB_Cluster/XtraDB_3306/logs/relay_log/mysql-relay-bin.index
relay_log_info_file     = relay-log.info
master-info-file        = master.info


skip_slave_start        = 1


#*** MyISAM Specific options
key_buffer_size         = 128M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 32M
myisam_max_sort_file_size = 128M
myisam_repair_threads = 1
myisam-recover-options

# *** INNODB Specific options ***
innodb_file_per_table   = 1
innodb_buffer_pool_size = 4G
innodb_data_home_dir   = /home/worker/XtraDB_Cluster/XtraDB_3306/data
innodb_data_file_path   = ibdata1:256M:autoextend
innodb_max_dirty_pages_pct      = 90

innodb_log_buffer_size  = 16M
innodb_log_file_size    = 256M
innodb_log_files_in_group  = 3
innodb_log_group_home_dir  = /home/worker/XtraDB_Cluster/XtraDB_3306/logs/innodb_log

innodb_flush_method     = O_DIRECT

innodb_write_io_threads = 8
innodb_read_io_threads  = 8

innodb_flush_log_at_trx_commit  = 1

# Speed up InnoDB shutdown. This will disable InnoDB to do a full purge
# and insert buffer merge on shutdown. It may increase shutdown time a
# lot, but InnoDB will have to do it on the next startup instead.
#innodb_fast_shutdown

innodb_lock_wait_timeout   = 120

#pxc
default_storage_engine=Innodb
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
pxc_strict_mode=ENFORCING

wsrep_cluster_address=gcomm://10.126.160.1:33060,10.126.160.2:33070,10.126.160.3:33080
wsrep_provider_options="gmcast.listen_addr=tcp://0.0.0.0:33060;ist.recv_addr=10.126.160.1:33061"
wsrep_sst_receive_address=10.126.160.1:33062

wsrep_cluster_name=pxc_default_channel
wsrep_node_name=defaultchannel_126_160_1_3306
wsrep_provider=/home/worker/XtraDB_Cluster/XtraDB_3306/lib/libgalera_smm.so
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sst:defaultchannel"
wsrep_node_address=10.126.160.1
wsrep_slave_threads=8


[mysqldump]
quick

max_allowed_packet = 1M

[mysql]
no_auto_rehash

# Only allow UPDATEs and DELETEs that use keys.
#safe_updates

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

[mysqlhotcopy]
interactive_timeout

[mysqld_safe]
open_files_limit = 65535

  (c)初始化mysql    

sudo ./bin/mysqld --defaults-file=/home/worker/XtraDB_Cluster/XtraDB_3306/my.cnf  --basedir=/home/worker/XtraDB_Cluster/XtraDB_3306 --initialize-insecure --user=worker

   (d)加载数据并启动mysql

如果有使用 mysql dump 等方式导出的数据,可以将数据copy 到 /home/worker/XtraDB_Cluster/XtraDB_3306/data 目录下
./bin/mysqld_safe --defaults-file=/home/worker/XtraDB_Cluster/XtraDB_3306/my.cnf

   (e)启动PXC第一个节点

nohup ./bin/mysqld_safe --defaults-file=/home/worker/XtraDB_Cluster/XtraDB_3306/my.cnf --wsrep-new-cluster &

   (f)验证节点是否正常启动 

使用 socket 进入mysql:

./bin/mysql --socket=./var/mysql.sock -uroot

然后执行:

show status like 'wsrep%';

得到如下的结果,代表第一个节点已经正常启动:
+------------------------------+-------------------------------------------------------+
| Variable_name                | Value                                                 |
+------------------------------+-------------------------------------------------------+
| wsrep_local_state_uuid       | 3afab882-07ef-11e8-b57a-ce630de1af49                  |
| ...                          | ...                           
| wsrep_local_state            | 4                                                     |
| wsrep_local_state_comment    | Synced                                                |
| ...                          |                              
| wsrep_cluster_size           | 1                                                     |
| wsrep_cluster_status         | Primary                                               |
| wsrep_connected              | ON                                                    |
| ...               | ...
| wsrep_ready                  | ON                                                    |
+------------------------------+-------------------------------------------------------+
59 rows in set (0.01 sec)

   (g)创建sst用户,用于数据同步

在第一个节点 mysql下执行以下命令:

 CREATE USER 'sst' IDENTIFIED BY 'defaultchannel';

 GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sst';

 FLUSH PRIVILEGES;

   至此,第一个节点创建完毕。

(3) 初始化其它节点

  (a) 切换到安装目录,生成日志所需文件夹

  跟步骤(2)中 (a)步骤一致

  (b)编辑 my.cnf 文件,本文用到的第二,第三节点的 my.cnf 文件如下:

  第二节点:

[client]
port            = 3307
socket          = /home/worker/XtraDB_Cluster/XtraDB_3307/var/mysql.sock

[mysqld]
bind_address    = 0.0.0.0
port            = 3307
socket          = /home/worker/XtraDB_Cluster/XtraDB_3307/var/mysql.sock
user            = worker
datadir         = /home/worker/XtraDB_Cluster/XtraDB_3307/data
pid_file        = /home/worker/XtraDB_Cluster/XtraDB_3307/var/mysqld.pid
skip_name_resolve

gtid_mode = ON
enforce_gtid_consistency = ON

tmpdir          = /tmp
slave_load_tmpdir = /tmp


back_log                = 1024
max_connections         = 4096
max_connect_errors      = 4096
table_open_cache        = 1024
max_allowed_packet      = 1M

tmp_table_size          = 128M
max_heap_table_size     = 64M
read_buffer_size        = 16M
read_rnd_buffer_size    = 16M
sort_buffer_size        = 32M
join_buffer_size        = 8M
query_cache_size        = 128M
query_cache_limit       = 32K
open_files_limit        = 65535

table_open_cache        = 1024
table_definition_cache  = 4096

thread_cache_size       = 256
thread_stack            = 192K
transaction_isolation   = REPEATABLE-READ

ft_min_word_len         = 4
# Set if mysql only or small db
#memlock

# Common logs
log_error               = /home/worker/XtraDB_Cluster/XtraDB_3307/logs/error.log
log_warnings
slow_query_log          = 1
slow_query_log_file     = /home/worker/XtraDB_Cluster/XtraDB_3307/logs/slow.log
long_query_time         = 2

general_log             = 0
general_log_file        = /home/worker/XtraDB_Cluster/XtraDB_3307/logs/general.log

# Replication
server_id = 12616021
#log-slave-updates = 1

log_bin                 = /home/worker/XtraDB_Cluster/XtraDB_3307/logs/bin_log/mysql-bin
log_bin_index           = /home/worker/XtraDB_Cluster/XtraDB_3307/logs/bin_log/mysql-bin.index
binlog_format           = ROW
binlog_cache_size       = 32M
expire_logs_days        = 31
sync_binlog             = 1
log_slave_updates       = 1

relay_log               = /home/worker/XtraDB_Cluster/XtraDB_3307/logs/relay_log/mysql-relay-bin
relay_log_index         = /home/worker/XtraDB_Cluster/XtraDB_3307/logs/relay_log/mysql-relay-bin.index
relay_log_info_file     = relay-log.info
master-info-file        = master.info


skip_slave_start        = 1


#*** MyISAM Specific options
key_buffer_size         = 128M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 32M
myisam_max_sort_file_size = 128M
myisam_repair_threads = 1
myisam-recover-options

# *** INNODB Specific options ***
innodb_file_per_table   = 1
innodb_buffer_pool_size = 4G
innodb_data_home_dir   = /home/worker/XtraDB_Cluster/XtraDB_3307/data
innodb_data_file_path   = ibdata1:256M:autoextend
innodb_max_dirty_pages_pct      = 90

innodb_log_buffer_size  = 16M
innodb_log_file_size    = 256M
innodb_log_files_in_group  = 3
innodb_log_group_home_dir  = /home/worker/XtraDB_Cluster/XtraDB_3307/logs/innodb_log

innodb_flush_method     = O_DIRECT

innodb_write_io_threads = 8
innodb_read_io_threads  = 8

innodb_flush_log_at_trx_commit  = 1

# Speed up InnoDB shutdown. This will disable InnoDB to do a full purge
# and insert buffer merge on shutdown. It may increase shutdown time a
# lot, but InnoDB will have to do it on the next startup instead.
#innodb_fast_shutdown

innodb_lock_wait_timeout   = 120

#pxc
default_storage_engine=Innodb
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
pxc_strict_mode=ENFORCING

wsrep_cluster_address=gcomm://10.126.160.1:33060,10.126.160.2:33070,10.126.160.3:33080
wsrep_provider_options="gmcast.listen_addr=tcp://0.0.0.0:33070;ist.recv_addr=10.126.160.2:33071"
wsrep_sst_receive_address=10.126.160.2:33072
wsrep_cluster_name=pxc_default_channel
wsrep_node_name=defaultchannel_126_160_2_3307
wsrep_provider=/home/worker/XtraDB_Cluster/XtraDB_3307/lib/libgalera_smm.so
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sst:defaultchannel"
wsrep_node_address=10.126.160.2
wsrep_slave_threads=8


[mysqldump]
quick

max_allowed_packet = 1M

[mysql]
no_auto_rehash

# Only allow UPDATEs and DELETEs that use keys.
#safe_updates

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

[mysqlhotcopy]
interactive_timeout

[mysqld_safe]
open_files_limit = 65535

   第三个节点:

[client]
port            = 3308
socket          = /home/worker/XtraDB_Cluster/XtraDB_3308/var/mysql.sock

[mysqld]
bind_address    = 0.0.0.0
port            = 3308
socket          = /home/worker/XtraDB_Cluster/XtraDB_3308/var/mysql.sock
user            = worker
datadir         = /home/worker/XtraDB_Cluster/XtraDB_3308/data
pid_file        = /home/worker/XtraDB_Cluster/XtraDB_3308/var/mysqld.pid
skip_name_resolve

gtid_mode = ON
enforce_gtid_consistency = ON

tmpdir          = /tmp
slave_load_tmpdir = /tmp


back_log                = 1024
max_connections         = 4096
max_connect_errors      = 4096
table_open_cache        = 1024
max_allowed_packet      = 1M

tmp_table_size          = 128M
max_heap_table_size     = 64M
read_buffer_size        = 16M
read_rnd_buffer_size    = 16M
sort_buffer_size        = 32M
join_buffer_size        = 8M
query_cache_size        = 128M
query_cache_limit       = 32K
open_files_limit        = 65535

table_open_cache        = 1024
table_definition_cache  = 4096

thread_cache_size       = 256
thread_stack            = 192K
transaction_isolation   = REPEATABLE-READ

ft_min_word_len         = 4
# Set if mysql only or small db
#memlock

# Common logs
log_error               = /home/worker/XtraDB_Cluster/XtraDB_3308/logs/error.log
log_warnings
slow_query_log          = 1
slow_query_log_file     = /home/worker/XtraDB_Cluster/XtraDB_3308/logs/slow.log
long_query_time         = 2

general_log             = 0
general_log_file        = /home/worker/XtraDB_Cluster/XtraDB_3308/logs/general.log

# Replication
server_id = 12616032
#log-slave-updates = 1

log_bin                 = /home/worker/XtraDB_Cluster/XtraDB_3308/logs/bin_log/mysql-bin
log_bin_index           = /home/worker/XtraDB_Cluster/XtraDB_3308/logs/bin_log/mysql-bin.index
binlog_format           = ROW
binlog_cache_size       = 32M
expire_logs_days        = 31
sync_binlog             = 1
log_slave_updates       = 1

relay_log               = /home/worker/XtraDB_Cluster/XtraDB_3308/logs/relay_log/mysql-relay-bin
relay_log_index         = /home/worker/XtraDB_Cluster/XtraDB_3308/logs/relay_log/mysql-relay-bin.index
relay_log_info_file     = relay-log.info
master-info-file        = master.info


skip_slave_start        = 1


#*** MyISAM Specific options
key_buffer_size         = 128M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 32M
myisam_max_sort_file_size = 128M
myisam_repair_threads = 1
myisam-recover-options

# *** INNODB Specific options ***
innodb_file_per_table   = 1
innodb_buffer_pool_size = 4G
innodb_data_home_dir   = /home/worker/XtraDB_Cluster/XtraDB_3308/data
innodb_data_file_path   = ibdata1:256M:autoextend
innodb_max_dirty_pages_pct      = 90

innodb_log_buffer_size  = 16M
innodb_log_file_size    = 256M
innodb_log_files_in_group  = 3
innodb_log_group_home_dir  = /home/worker/XtraDB_Cluster/XtraDB_3308/logs/innodb_log

innodb_flush_method     = O_DIRECT

innodb_write_io_threads = 8
innodb_read_io_threads  = 8

innodb_flush_log_at_trx_commit  = 1

# Speed up InnoDB shutdown. This will disable InnoDB to do a full purge
# and insert buffer merge on shutdown. It may increase shutdown time a
# lot, but InnoDB will have to do it on the next startup instead.
#innodb_fast_shutdown

innodb_lock_wait_timeout   = 120

#pxc
default_storage_engine=Innodb
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
pxc_strict_mode=ENFORCING

wsrep_cluster_address=gcomm://10.126.160.1:33060,10.126.160.2:33070,10.126.160.3:33080
wsrep_provider_options="gmcast.listen_addr=tcp://0.0.0.0:33080;ist.recv_addr=10.126.160.3:33081"
wsrep_sst_receive_address=10.126.160.3:33082

wsrep_cluster_name=pxc_default_channel
wsrep_node_name=defaultchannel_126_160_3_3308
wsrep_provider=/home/worker/XtraDB_Cluster/XtraDB_3308/lib/libgalera_smm.so
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sst:defaultchannel"
wsrep_node_address=10.126.160.3
wsrep_slave_threads=8


[mysqldump]
quick

max_allowed_packet = 1M

[mysql]
no_auto_rehash

# Only allow UPDATEs and DELETEs that use keys.
#safe_updates

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

[mysqlhotcopy]
interactive_timeout

[mysqld_safe]
open_files_limit = 65535

   (c)初始化 mysql

sudo ./bin/mysqld --defaults-file=/home/worker/XtraDB_Cluster/XtraDB_3307/my.cnf  --basedir=/home/worker/XtraDB_Cluster/XtraDB_3307 --initialize-insecure --user=worker

   (d)启动mysql 并 加入cluster

nohup ./bin/mysqld --defaults-file=/home/worker/XtraDB_Cluster/XtraDB_3307/my.cnf &

   (f)检查mysql 的状态和cluster size

(4)按照普通节点启动的方式重启第一个节点

  至此,PXC 搭建完毕

 

 

 

 

  

 

posted @ 2018-02-07 19:18  talk_is_cheap  阅读(303)  评论(0编辑  收藏  举报