Percona XtraDB Cluster Ubuntu Configuration

-- Add Percona Keys on Ubuntu

gpg --keyserver  hkp://keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
gpg -a --export CD2EFD2A | sudo apt-key add -

-- Add repository

nano /etc/apt/sources.list

deb http://repo.percona.com/apt precise main
deb-src http://repo.percona.com/apt precise main

apt-get update

-- Install Percona Cluster

sudo apt-get install percona-xtradb-cluster-client-5.5 percona-xtradb-cluster-server-5.5 percona-xtrabackup

-- Edit /etc/mysql/my.cnf for the first node

[client]

port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
# * Basic Settings
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
# bind-address = 0.0.0.0
binlog_format = ROW

######## Galera ########
# Full path to wsrep provider library or ‘none’ to disable galera
wsrep_provider=/usr/lib/libgalera_smm.so
# set to “gcom://”" to reinitialise (reset) a node
wsrep_cluster_address=gcomm://
wsrep_cluster_name=Percona-XtraDB-Cluster
wsrep_node_name=Node1

#### BOF : State Snapshot Transfer method
wsrep_sst_method=rsync
# alternative methods to-do SST
# experimental, wait for RC release
#wsrep_sst_method=xtrabackup
# not recommended, transfers the ENTIRE database to re-sync nodes.
# Set to number of cpu cores.
wsrep_slave_threads=1

#### for MyISAM support
wsrep_replicate_myisam=1
####END of MyISAM

# to enable debug level logging, set this to 1
wsrep_debug=1
# how many times to retry deadlocked autocommits
wsrep_retry_autocommit=1
# convert locking sessions into transactions
wsrep_convert_LOCK_to_trx=1
# Generate fake primary keys for non-PK tables (required for multi-master and parallel applying operation)
wsrep_certify_nonPK=1

#### Required for Galera
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
default_storage_engine=InnoDB
query_cache_size=0
query_cache_type=0
######## EOF : Galera ########

# * Fine Tuning
key_buffer = 32M
max_allowed_packet = 32M
thread_stack = 192K
thread_cache_size = 8

# * Query Cache Configuration
query_cache_limit = 1M
query_cache_size = 32M

# * Logging and Replication
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file = /var/log/mysql/mysql.log
general_log = 1
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with ‘.cnf’, otherwise they’ll be ignored.
!includedir /etc/mysql/conf.d/

----------------------------------------------------------------------------

-- Edit /etc/mysql/my.cnf for the second node

# The MySQL database server configuration file.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
# * Basic Settings
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address = 0.0.0.0
binlog_format = ROW

######## Galera ########
# Full path to wsrep provider library or 'none' to disable galera
wsrep_provider=/usr/lib/libgalera_smm.so
# set to "gcom://"" to reinitialise (reset) a node
wsrep_cluster_address=gcomm://192.168.56.1
wsrep_cluster_name=Percona-XtraDB-Cluster
wsrep_node_name=Node2

#### State Snapshot Transfer method
wsrep_sst_method=rsync
# alternative methods to-do SST
# experimental, wait for RC release
#wsrep_sst_method=xtrabackup
# not recommended, transfers the ENTIRE database to re-sync nodes.
#wsrep_sst_method=mysqldump

#### Optimization
# Set to number of cpu cores.
wsrep_slave_threads=1

####MyISAM
wsrep_replicate_myisam=1
####END of MyISAM

#### Work Around
# to enable debug level logging, set this to 1
wsrep_debug=1
# how many times to retry deadlocked autocommits
wsrep_retry_autocommit=1
# convert locking sessions into transactions
wsrep_convert_LOCK_to_trx=1
# Generate fake primary keys for non-PK tables (required for multi-master and parallel applying operation)
wsrep_certify_nonPK=1

#### Required for Galera
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
default_storage_engine=InnoDB
query_cache_size=0
query_cache_type=0
######## EOF : Galera ########

# * Fine Tuning
key_buffer = 32M
max_allowed_packet = 32M
thread_stack = 192K
thread_cache_size = 8

# * Query Cache Configuration
query_cache_limit = 1M
query_cache_size = 32M
# * Logging and Replication
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file = /var/log/mysql/mysql.log
general_log = 1
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
!includedir /etc/mysql/conf.d/

----------------------------------------------------------------------------

-- Edit /etc/mysql/my.cnf for the third node

# The MySQL database server configuration file.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
# * Basic Settings
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address = 0.0.0.0
binlog_format = ROW

######## Galera ########
# Full path to wsrep provider library or 'none' to disable galera
wsrep_provider=/usr/lib/libgalera_smm.so
# set to "gcom://"" to reinitialise (reset) a node
wsrep_cluster_address=gcomm://192.168.56.2
wsrep_cluster_name=Percona-XtraDB-Cluster
wsrep_node_name=Node3
#### State Snapshot Transfer method
wsrep_sst_method=rsync
# alternative methods to-do SST
# experimental, wait for RC release
#wsrep_sst_method=xtrabackup
# not recommended, transfers the ENTIRE database to re-sync nodes.
#wsrep_sst_method=mysqldump

####MyISAM
wsrep_replicate_myisam=1
####END of MyISAM

#### Optimization
# Set to number of cpu cores.
wsrep_slave_threads=1

#### Work Around
# to enable debug level logging, set this to 1
wsrep_debug=1
# how many times to retry deadlocked autocommits
wsrep_retry_autocommit=1
# convert locking sessions into transactions
wsrep_convert_LOCK_to_trx=1
# Generate fake primary keys for non-PK tables (required for multi-master and parallel applying operation)
wsrep_certify_nonPK=1

#### Required for Galera
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
default_storage_engine=InnoDB
query_cache_size=0
query_cache_type=0

######## End Galera ########

# * Fine Tuning
key_buffer = 32M
max_allowed_packet = 32M
thread_stack = 192K
thread_cache_size = 8

# * Query Cache Configuration
query_cache_limit = 1M
query_cache_size = 32M
# * Logging and Replication
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file = /var/log/mysql/mysql.log
general_log = 1
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
!includedir /etc/mysql/conf.d/

-- In the last step, start mysql

/etc/init.d/mysql start