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 [email protected]'%' IDENTIFIED BY 'AUTHPASSWORD';
    GRANT ALL PRIVILEGES on *.* to [email protected]'%';
    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.

Leave a Reply