PXC (Percona xtradb Cluster) Installation and problem solving methods

PXC Installation

1, PXC installation environment:

Cluster Name: Pxc-sky

node1:192.168.246.18

node2:192.168.246.19

node3:192.168.246.20

2, shut down the firewall and SELinux

#chkconfig iptables off

#vi/etc/selinux/config

Selinux=disabled

#reboot

3. Create MySQL users and Groups

All 3 nodes to do

[Root@localhost ~]# Groupadd MySQL

[Root@localhost ~]# useradd–g mysql–s/sbin/nologin MySQL

4, the main software installation

All 3 nodes to do

The storage location of the software under/opt/mysql/

First, configure the Yum source

4.1 percona-xtradb-cluster-5.6.26-rel74.0-25.12.1.linux.x86_64.tar.gz

Using TAR-ZXVF percona-xtradb-cluster-5.6.26-rel74.0-25.12.1.linux.x86_64.tar.gz Decompression

Then the soft connection to the/usr/local/mysql

cd/usr/local/

Ln-s/opt/mysql/percona-xtradb-cluster-5.6.26-rel74.0-25.12.1.linux.x86_64 MySQL

Grant permissions to the MySQL user group

Chown–r Mysql:mysql MySQL

Chown–r Mysql:mysql mysql/

4.2 percona-xtrabackup-2.3.2-1.el6.x86_64.rpm

libev-4.15-1.el6.rf.x86_64.rpm

Install the dependency pack first libev-4.15-1.el6.rf.x86_64.rpm

RPM–IVH libev-4.15-1.el6.rf.x86_64.rpm

Then use the Yum–y install percona-xtrabackup-2.3.2-1.el6.x86_64.rpm installation

4.3 socat-1.7.2.3-1.el6.src.rpm

Cd/opt/mysql

RPM–IVH socat-1.7.2.3-1.el6.src.rpm

Generate/root/rpmbuild/sources/socat-1.7.2.3.tar.gz

cd/root/rpmbuild/sources/

TAR-ZXVF socat-1.7.2.3.tar.gz

cd/root/rpmbuild/sources/socat-1.7.2.3

./configure

# # # # #如果出现下面错误, then install the GCC dependency pack

Using Yum–y install GCC

And then in the execution

./configure

Make

Make install

Cd/usr/bin

Ln-s/usr/local/bin/socat Socat

5. Configure MySQL parameter file

All 3 nodes to do

stored in/ETC/MY.CNF

#my. cnf

[Client]

Port = 3306

Socket =/tmp/mysql3306.sock

[MySQL]

prompt= "\\u@\\h:\\p [\\d]>

#pager = "Less-i-n-s"

#tee =/home/mysql/query.log

No-auto-rehash

[Mysqld]

#misc

user = MySQL

Basedir =/usr/local/mysql

DataDir =/data/mysql/mysql3306/data

Port = 3306

Socket =/tmp/mysql3306.sock

Event_scheduler = 0

Tmpdir=/data/mysql/mysql3306/tmp

#timeout

Interactive_timeout = 300

Wait_timeout = 300

#character Set

Character-set-server = UTF8

Open_files_limit = 65535

max_connections = 100

Max_connect_errors = 100000

#

Explicit_defaults_for_timestamp

#logs

Log-output=file

Slow_query_log = 1

Slow_query_log_file = Slow.log

Log-error = Error.log

Log_warnings = 2

Pid-file = Mysql.pid

Long_query_time = 1

#log-slow-admin-statements = 1

#log-queries-not-using-indexes = 1

Log-slow-slave-statements = 1

#binlog

Binlog_format = row

Server-id = 193306 # # #ip最后一位 + port number

Log-bin =/data/mysql/mysql3306/logs/mysql-bin

Binlog_cache_size = 1M

Max_binlog_size = 200M

Max_binlog_cache_size = 2G

Sync_binlog = 0

Expire_logs_days = 10

#relay Log

Skip_slave_start = 1

Max_relay_log_size = 500M

Relay_log_purge = 1

Relay_log_recovery = 1

Log_slave_updates

#slave-skip-errors=1032,1053,1062

#buffers & Cache

Table_open_cache = 2048

Table_definition_cache = 2048

Table_open_cache = 2048

Max_heap_table_size = 96M

Sort_buffer_size = 2M

Join_buffer_size = 2M

Thread_cache_size = 256

Query_cache_size = 0

Query_cache_type = 0

Query_cache_limit = 256K

Query_cache_min_res_unit = 512

Thread_stack = 192K

Tmp_table_size = 96M

Key_buffer_size = 8M

Read_buffer_size = 2M

Read_rnd_buffer_size = 16M

Bulk_insert_buffer_size = 32M

#myisam

Myisam_sort_buffer_size = 128M

Myisam_max_sort_file_size = 10G

Myisam_repair_threads = 1

#innodb

Innodb_buffer_pool_size = 100M

Innodb_buffer_pool_instances = 1

Innodb_data_file_path =ibdata1:100m:autoextend

Innodb_flush_log_at_trx_commit = 2

Innodb_log_buffer_size = 64M

Innodb_log_file_size = 256M

Innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_file_per_table = 1

Innodb_rollback_on_timeout

Innodb_status_file = 1

innodb_io_capacity = 2000

Transaction_isolation = read-committed

Innodb_flush_method = O_direct

#pxc

Default_storage_engine=innodb

Innodb_locks_unsafe_for_binlog=1

innodb_autoinc_lock_mode=2

Wsrep_cluster_name=pxc_sky

wsrep_cluster_address=gcomm://192.168.246.20,192.168.246.19,192.168.246.18 # #3个节点的ip

wsrep_node_address=192.168.246.19 # # #本机的ip

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

# wsrep_sst_method=rsync # #sst的模式

WSREP_SST_METHOD=XTRABACKUP-V2 # #sst的模式

Wsrep_sst_auth=sst:sky # #sst的用户名和密码

6, initialization of MySQL

All 3 nodes to do

Set up a MySQL storage directory

Mkdir–p/data/mysql/mysql3306/{data,tmp,logs}

Chown–r Mysql:mysql/data

Cd/usr/local/mysql

In the initialization of the installation error, prompted to find libss.so.6 this package, and then I locate a bit, found that the system has this package, but the version is different, and then I will libssl.so.10 this package soft connection for libssl.so.6

[Root@node7/]# Locate Libssl
/usr/lib64/.libssl.so.1.0.1e.hmac
/usr/lib64/.libssl.so.10.hmac
/usr/lib64/libssl.so.1.0.1e
/usr/lib64/libssl.so.10
/usr/lib64/libssl3.so
[Root@node7/]# ln-s/usr/lib64/libssl.so.10/usr/lib64/libssl.so.6

Then continue to install initialization, and then prompted to find libcrypto.so.6 this package, and then continue to locate way to find out if the local system has no this version of the package

Can be found inside the system also has this package, continue to use the same method, soft connection

And then continue initialization.

There are two ok, so my initialization has been successful.

7, the PXC start

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

Then you start the first node, and the first node starts with the BOOTSTRAP-PXC.

You can also use/usr/local/mysql/bin/mysqld--defaults-file=/etc/my.cnf--wsrep_new_cluster &

To start the first node

The remaining nodes are started using/etc/init.d/mysql start directly

Then is connected to MySQL, found that there is still no package problem, with locate + soft connection to solve

After continuing to connect MySQL, it was successful

Connect to MySQL for security initialization

Delete from Mysql.user where user!= ' root ' or host!= ' localhost ';

TRUNCATE TABLE mysql.db;

drop database test;

Flush privileges;

User authorization

Grant all privileges on *.* to sky@ '% ' identified by ' sky '; Grant replication Slave on *.* to sst@ ' localhost ' identified by ' repl ';

8, the PXC closed

/etc/init.d/mysql Stop shutdown

Ps: Last Stop node, requires the first boot, starting mode for BOOTSTRAP-PXC boot

9. Errors encountered and Solutions

9.1

The second node starts an error

Workaround:

This is the machine abnormal stop, lock file did not delete the sample, restart the machine can be normal startup

If this error occurs after restarting, update the Percona-xtrabackup version and update to the latest version

9.2

PXC started successfully, you can connect to the database, do any operation is reported below this error, and close the PXC not off

Workaround:

This is PXC when starting,--wsrep_start_position=00000000-0000-0000-0000-000000000000:-1 This synchronization error, restart the machine, start PXC can

9.3

Workaround:

This is caused by wsrep_sst_method=xtrabackup-v2 in the PXC configuration file that is not written in V2

9.4

Workaround:

This is in the PXC configuration file.

wsrep_cluster_address=gcomm://192.168.246.18,192.168.246.19,192.168.246.20

This parameter does not write will pxc all node's IP writes the result, will PXC all node's IP to add up to be possible