PHP Stack with Percona XtraDB Cluster on CentOS 7

Recently I often faced problems with MariaDB Galera Cluster, so I decided to give Percona XtraDB Cluster a try. I write here necessary steps to setup a PHP Stack with Percona XtraDB Cluster on CentOS 7.

1. To avoid problems, be sure that SELinux is not enabled in the server. Guide can be found HERE.
2. Open INCOMING and OUTGOING ports for MySQL Cluster: 3306, 4444, 4567, 4568
3. First, install Epel, Remi, and Percona repo for CentOS 7:

   yum install -y epel-release
   rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-7.rpm
   yum install -y https://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-4.noarch.rpm
   yum install -y yum-utils

4. Switch to PHP7 repo:

   yum-config-manager --enable remi-php71

5. Next, install Nginx and PHP softwares:

   yum install -y nginx
   yum install -y php-fpm php-opcache php-mysqlnd php-mcrypt php-mbstring php-cli php-opcache php-xml

6. Then, install Percona XtraDB Cluster:

   yum install -y Percona-XtraDB-Cluster-57

7. Edit /etc/my.cnf to add information about cluster under [mysqld] section. Sample configuration is as follows:

   binlog_format                  = ROW
   innodb_buffer_pool_size        = 100M
   innodb_flush_log_at_trx_commit = 0
   innodb_flush_method            = O_DIRECT
   innodb_log_files_in_group      = 2
   innodb_log_file_size           = 20M
   innodb_file_per_table          = 1
   datadir                        = /var/lib/mysql
   
   wsrep_cluster_address          = gcomm://IP_NODE_1,IP_NODE_2,IP_NODE_3
   wsrep_provider                 = /usr/lib64/galera3/libgalera_smm.so
   
   wsrep_slave_threads            = 8
   wsrep_cluster_name             = ClusterName
   wsrep_node_name                = NODE_NAME
   wsrep_node_address	       = IP_NODE_1
   
   innodb_locks_unsafe_for_binlog = 1
   innodb_autoinc_lock_mode       = 2
   
   wsrep_sst_method		= xtrabackup-v2
   wsrep_sst_auth			= "AUTHUSER:AUTHPASSWORD"

8. Bootstrap it in the first node

   systemctl start [email protected]

   or

   service mysql bootstrap-pxc

   . Remember that for other nodes, we only need to start the service without bootstrap.service param.

9. Remember to Secure MySQL after installing:

   mysql_secure_installation

10. Grant permission for the above AUTHUSER for replication (or grant all permission as me):

    # mysql -u root -p
    GRANT USAGE ON *.* to AUTHUSER@'%' IDENTIFIED BY 'AUTHPASSWORD';
    GRANT ALL PRIVILEGES on *.* to AUTHUSER@'%';
    FLUSH PRIVILEGES;

11. Done, re-peat the config for NODE_2 and NODE_3. Remember to update wsrep_node_name and wsrep_node_address for these node also. As all user / password / mysql configuration will be copied from existing nodes to the new nodes, so we do not need to do many repeated tasks over and over.

WARNING: If you are enabling SELinux and cannot join a cluster, consider to DISABLE it. I found myself be difficult in activating a new joiner node when I enable SELinux, but all goes well after disabling it.