PXC(Percona XtraDB Cluster)单机多实例与多集群安装配置

来源：互联网发布：linux 上一条命令结果编辑：程序博客网时间：2024/06/06 08:41

pxc原理：

节点接收SQL请求后，在commit之前，由wsrepAPI调用galera库进行集群内广播，所有其他节点验证成功后事务在所有节点进行提交，反之rollback。pxc保证整个集群所有数据的强一致性，满足CAP理论中：consistency和availability

在一个事务提交的过程，Node1提交一个事物，必须要所有的节点通过了这个事务的请求，并且返回成功（ok）或者失败（conflict）信号之后，才真正的commit之后返回结果给用户，但是这里注意，最后在其它节点apply过程，并不会影响到给用户返回结果的过程。

大多数PXC集群都是单机单实例的，但是也有例外，为了充分利用机器性能，并且需要PXC集群架构，于是，多实例与多集群的部署需求就来了，以下便是安装步骤：

1．环境描述：

CentOS7.2 PXC5.7.14

cluster1

cluster2

cluster3

node1,192.168.252.227

3307,4567

3308,5567

3309,6567

node2,192.168.252.228

3307,4567

3308,5567

3309,6567

node3,192.168.252.229

3307,4567

3308,5567

3309,6567

2．安装配置cluster1

yum install socat nc -y

tar zxf Percona-XtraDB-Cluster-5.7.14-rel8-26.17.1.Linux.x86_64.ssl101.tar.gz -C/usr/local/

ln -s /usr/local/Percona-XtraDB-Cluster-5.7.14-rel8-26.17.1.Linux.x86_64.ssl101/usr/local/mysql

mkdir /export/mysql/mysql3307/{binlog,data,innodb,logs,tmp} -p

vim /export/mysql/mysql3307/my3307.cnf

[client]

port = 3307

socket = /export/mysql/mysql3307/tmp/mysql3307.sock

[mysql]

prompt = '<\U:\p [\d]>'

default_character_set = utf8

no_auto_rehash

[mysqld]

###GENERAL ###

user = mysql

port = 3307

socket = /export/mysql/mysql3307/tmp/mysql3307.sock

pid_file = /export/mysql/mysql3307/tmp/3307mysql.pid

datadir = /export/mysql/mysql3307/data

tmpdir = /export/mysql/mysql3307/tmp

character_set_server = utf8

lower_case_table_names = 1

default_storage_engine = InnoDB

#innodb = FORCE

###NETWORK AND LIMITS ###

back_log = 100

max_allowed_packet = 16M

max_connect_errors = 10000

max_connections = 1024

max_user_connections = 1000

interactive_timeout = 7200

wait_timeout = 7200

connect_timeout = 30

skip_external_locking

skip_name_resolve

###CACHES ###

thread_cache_size = 64

table_open_cache = 4096

table_definition_cache = 2048

query_cache_size = 0

query_cache_type = 0

tmp_table_size = 512M

max_heap_table_size = 512M

###PER-THREAD BUFFERS ###

sort_buffer_size = 4M

read_buffer_size = 1M

read_rnd_buffer_size = 1M

join_buffer_size = 1M

bulk_insert_buffer_size = 64M

###PERCONA SERVER ###

#extra_port = 13306

#gtid_mode = on

#enforce_gtid_consistency = 1

thread_handling = pool-of-threads

thread_pool_oversubscribe = 8

explicit_defaults_for_timestamp

###MyISAM ###

key_buffer_size = 128M

myisam_sort_buffer_size = 32M

###INNODB ###

transaction_isolation = READ-COMMITTED

innodb_buffer_pool_size = 32G

#innodb_buffer_pool_instances = 8

innodb_data_home_dir = /export/mysql/mysql3307/innodb

innodb_data_file_path = ibdata1:1G:autoextend

innodb_log_group_home_dir = /export/mysql/mysql3307/innodb

innodb_log_file_size = 1G

innodb_log_files_in_group = 2

innodb_log_buffer_size = 32M

innodb_file_per_table = 1

innodb_flush_method = O_DIRECT

innodb_flush_log_at_trx_commit = 2

innodb_max_dirty_pages_pct = 80

innodb_lock_wait_timeout = 10

innodb_read_io_threads = 16

innodb_write_io_threads = 16

innodb_open_files = 4096

#log_bin_trust_function_creators= 1

#innodb_flush_neighbors = 0

#innodb_io_capacity = 2000

#innodb_page_size = 8K

###REPLICATION ###

server_id = 2273307

sync_binlog = 0

log_bin = /export/mysql/mysql3307/binlog/mysql-bin

binlog_format = ROW

max_binlog_size = 256M

expire_logs_days = 10

binlog_cache_size = 1M

master_info_file = /export/mysql/mysql3307/binlog/master.info

relay_log = /export/mysql/mysql3307/binlog/relay-log

relay_log_info_file = /export/mysql/mysql3307/binlog/relay-log.info

max-relay-log-size = 256M

#relay_log_purge = 0

#read_only = 1

log_slave_updates = 1

slave_net_timeout = 60

skip_slave_start = 1

slave_parallel_workers = 16

#replicate-wild-do-table = mysql.%

#replicate-wild-ignore-table = test.%

#auto-increment-offset = 1

#auto-increment-increment = 2

#rpl_semi_sync_master_enabled = 1

#rpl_semi_sync_master_timeout = 1000

#rpl_semi_sync_slave_enabled = 1

###LOG ###

slow_query_log = 1

slow_query_log_file = /export/mysql/mysql3307/logs/slow.log

log_queries_not_using_indexes = 0

long_query_time = 1

log_error = /export/mysql/mysql3307/logs/error.log

#general_log = 0

general_log_file = /export/mysql/mysql3307/logs/general.log

###PXC ###

default_storage_engine=Innodb

innodb_locks_unsafe_for_binlog = 1

innodb_autoinc_lock_mode = 2

wsrep_cluster_name = cluster1

wsrep_cluster_address = gcomm://192.168.252.227,192.168.252.228,192.168.252.229

wsrep_node_address = 192.168.252.227

wsrep_provider = /usr/local/mysql/lib/libgalera_smm.so

wsrep_sst_method = xtrabackup-v2

wsrep_sst_auth = thunder:thunder

[mysqld_safe]

open-files-limit = 65535

#malloc-lib = /usr/local/mysql/lib/mysql/libjemalloc.so

[mysqldump]

quick

max_allowed_packet = 128M

[myisamchk]

key_buffer_size = 512M

sort_buffer_size = 512M

read_buffer = 8M

write_buffer = 8M

[mysqlhotcopy]

interactive_timeout

#将配置文件传到其它服务器上，并修改里面对应的wsrep_node_address

scp -r /export/mysql/mysql3307 192.168.252.228:/export/mysql

scp -r /export/mysql/mysql3307 192.168.252.229:/export/mysql

#在node1上面初始化并启动pxc的第一个节点，并对配置文件中的用户授权

cd /usr/local/mysql

./bin/mysqld--defaults-file=/export/mysql/mysql3307/my3307.cnf --initialize-insecure

./bin/mysqld--defaults-file=/export/mysql/mysql3307/my3307.cnf --wsrep-new-cluster &

mysql>GRANT PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'thunder'@'localhost'IDENTIFIED BY 'thunder';FLUSH PRIVILEGES;

#在node2,node3上面启动pxc的其它节点，观察是否启动成功，并在node1上面进行写入，观察node2,node3上面是否有进行同步

./bin/mysqld--defaults-file=/export/mysql/mysql3307/my3307.cnf &

到此，cluster1就安装完毕了

3．安装cluster2

除配置文件外，其它与cluster1安装一样，cluster的端口为5567

###PXC ###

default_storage_engine=Innodb

innodb_locks_unsafe_for_binlog = 1

innodb_autoinc_lock_mode = 2

wsrep_cluster_name = cluster2

wsrep_cluster_address =gcomm://192.168.252.227:5567,192.168.252.228:5567,192.168.252.229:5567wsrep_node_address = 192.168.252.228:5567

wsrep_provider_options = "base_port=5567"

wsrep_provider =/usr/local/mysql/lib/libgalera_smm.so

wsrep_sst_method = xtrabackup-v2

wsrep_sst_auth = thunder:thunder

4．安装cluster3

除配置文件外，其它与cluster1安装一样，

###PXC ###

default_storage_engine=Innodb

innodb_locks_unsafe_for_binlog = 1

innodb_autoinc_lock_mode = 2

wsrep_cluster_name = cluster3

wsrep_cluster_address = gcomm://192.168.252.227:6567,192.168.252.228:6567,192.168.252.229:6567

wsrep_node_address = 192.168.252.228:6567

wsrep_provider_options = "base_port=6567"

wsrep_provider =/usr/local/mysql/lib/libgalera_smm.so

wsrep_sst_method = xtrabackup-v2

wsrep_sst_auth = thunder:thunder

5．说明：

1．PXC不支持myisam引擎，在node1上创建myisam表后，其它节点不会进行复制。

2．建议的最小集群节点数为3，虽然两个节点也能运行，但是无法保证数据的稳定，当两节点时，任何一个节点出故障，将导致集群无法访问。最大值<=8。

3．3台机器损失一台机器性能。

4．关于wsrep_sst_method，有3种可选:mysqldump,rsync,xtrabackup

mysqldump最慢

rsync最快，但是在同步期间会锁表

xtrabackup可以在线传输，只有在copy表结构文件时会锁表，和其备份原理一样

5．启动顺序，如果为单机单实例，则

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

第一个实例启动命令为：

/etc/init.d/mysql bootstarp-pxc

如果为多实例，则通过mysqld_safe的方式来启动：

./bin/mysqld --defaults-file=/export/mysql/mysql3307/my3307.cnf --wsrep-new-cluster &

6. 可在启动时指定从哪台机器进行同步

./bin/mysqld --defaults-file=/export/mysql/mysql3307/my3307.cnf --wsrep-sst-donor=’192.168.252.228’ &

0 0