PXC (Percona xtradb Cluster) Single machine multi-instance and multi-cluster installation configuration

PXC (Percona xtradb Cluster) Single machine multi-instance and multi-cluster installation configuration

PXC principle:

After the node receives the SQL request, the WSREPAPI calls the Galera library for cluster broadcast before the commit, and all other nodes commit the transaction after the success, and vice versa rollback. PXC ensures strong consistency across all data in the cluster to meet the CAP theory: consistency and availability

In the process of committing a transaction, Node1 commits a thing, must have all the nodes pass the request of the transaction, and returns the result to the user after the successful (OK) or failure (conflict) signal, but it is noted here that Finally, the apply process at other nodes does not affect the process of returning results to the user.

Most PXC clusters are stand-alone singleton instances, but there are exceptions, in order to take full advantage of machine performance and the need to PXC cluster architectures, the deployment requirements for multiple instances and multiple clusters are coming, and the following are the installation steps:

1. Environment Description:

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. Installation Configuration 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

#将配置文件传到其它服务器上, and modify the corresponding wsrep_node_address inside

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

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

#在node1上面初始化并启动pxc的第一个节点, and user authorization in the configuration file

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 the other nodes on PXC to see if the boot was successful and write on Node1 to see if there was a sync on node2,node3

./BIN/MYSQLD--DEFAULTS-FILE=/EXPORT/MYSQL/MYSQL3307/MY3307.CNF &

By this, the Cluster1 is ready to install.

3. Install Cluster2

In addition to the configuration file, other than the Cluster1 installation, the cluster port is 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. Install Cluster3

In addition to the configuration file, other than the Cluster1 installation,

# # #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. Description

1. PXC does not support the MyISAM engine, the other nodes are not replicated after the MyISAM table is created on Node1.

2. The proposed minimum cluster node number is 3, although two nodes can also run, but cannot guarantee the data stability, when two nodes, any one node fails, will cause the cluster inaccessible. Maximum Value <=8.

3. 3 Machines lose a machine's performance.

4. About Wsrep_sst_method, there are 3 kinds of optional: mysqldump,rsync,xtrabackup

Mysqldump the slowest

Rsync is the fastest, but locks the table during synchronization

Xtrabackup can be transmitted online, only when the copy table structure file locks the table, and its backup principle

5. The start order, if it is a stand-alone single instance,

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

The first Instance startup command is:

/etc/init.d/mysql BOOTSTARP-PXC

If it is multiple instances, it is started by Mysqld_safe:

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

6. You can specify from which machine to sync at startup

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