Install Percona XtraDB Cluster on Centos 7
Percona XtraDB Cluster
This tutorial is written for a developer wanting to install a MySQL database on a Centos 7 linux Server. There are many choices in both the type of Linux server you are running as well as the database you choose to use. MySQL is a very common opensource solution for Database needs. Centos is basically RedHat which is a very popular enterprise distribution of Linux. So this tutorial will be written with our personal preference of Centos 7 and Percona’s version of MySQL call Percona XtraDB.
Percona XtraDB is an opensource high availability solution for MySQL.
MySQL itself comes in a few different versions depending on your needs.
- Community version which if freely downloadable and one of the most popular database solutions
- Standard version has an annual subscription of around $2000
- Enterprise version has an annual subscription of around $5000
- Cluster Carrier Grade version has an annual subscription of around $10,000
We have included the links to each of the versions so you can read more about the features themselves but we exclusively use Percona for our MySQL needs. It allows us to have a lot of the “Paid” features for NO COST and allows us to cluster our servers for solutions that need this type of functionality.
To get started you will need to have a Centos 7 server up and running and root access to it. You can find our tutorial on initial set up here.
So assuming you are at that point we like to write shell scripts for needs like this so it is easily repeatable.
Add the following lines to the new file:
# Run update on Server
yum -y update
# Change to /tmp directory
# Install software
yum -y install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum -y install socat
yum -y install Percona-XtraDB-Cluster-full-56
Save and exit out of the file and make file executable
press [Esc] key
chmod +x build.sh
Now run the script
Start the MySQL service
systemctl start [email protected]
Now that the software is installed and up and running we need to set a few things up with the MySQL database
UPDATE mysql.user SET password=PASSWORD("[your_password]") where user="root";
CREATE USER 'sstuser'@'localhost' IDENTIFIED BY '[your_password]';
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
Now let’s set up the my.cnf file to work. This configuration will be the basics needed for setting up a cluster environment. The specific variables for buffer pool size, log file sizes etc. depend on how your server is built. Percona has a great tool that helps you configure your my.cnf file and you can replace the appropriate variables with the results of the tool.
# Template my.cnf for PXC
# Edit to your requirements.
# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1
binlog_format = ROW
innodb_buffer_pool_size = 1G
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_file_per_table = 1
wsrep_cluster_address = gcomm://
wsrep_provider = /usr/lib64/galera3/libgalera_smm.so
wsrep_slave_threads = 8
wsrep_cluster_name = Cluster [can be what you define as name]
wsrep_node_name = Node1 [can be what you define as name]
wsrep_node_address = [your ip address]
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = “sstuser:[your_password]”
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 1024
table-open-cache = 2048
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
pid-file = /run/mysqld/mysql.pid
Restart the service and then you should be good to go.
systemctl restart [email protected]
There are a few different tools out there for managing you database through a GUI, you can always use the command line but we prefer the GUI as it is a little easier to look at things. The simplest choice is MySQL Workbench. It has a lot of features and will meet most of your needs.