Skip to content

Instantly share code, notes, and snippets.

@denji

denji/ Galera.md

Last active December 31, 2015 13:58
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save denji/7996168 to your computer and use it in GitHub Desktop.
Save denji/7996168 to your computer and use it in GitHub Desktop.
Download ZIP
Ubuntu 13.10: 3 Node MariaDB Cluster + XtraBackup + GLB Load Balancer ( aka MySQL + Galera )
Raw
Galera.md

Links

MariaDB Cluster provides the following features

Synchronous replication Active/active multi-master topology Read and write to any cluster node Automatic membership control, failed nodes drop from the cluster Automatic node joining True parallel row level replication Direct client connections Drop-in replacement for native MySQL All the MariaDB benefits

Benefits using Galera Replication

High Availability No slave lag No lost transactions No more data inconsistency Smaller client latencies Read scalability and write throughput improvement (3 times and more, depending on your workload

#Hostnames
dbnode1 11.11.11.11
dbnode2 22.22.22.22
dbnode3 33.33.33.33

#Ports
TCP 22 : SSH

#MariaDB Cluster Ports
TCP 3306 : Regular MySQL
TCP 4567 : Group Communication (wsrep_provider gmcast.listen_addr)
TCP 4568 : Incremental State Transfer (wsrep_provider ist.recv_addr)
TCP 4444 : State Transfer (wsrep_sst_receive_address)

Set the timezone

Set the system timezone to UTC

$ ln -sf /usr/share/zoneinfo/UTC /etc/localtime

Remove conflicting solutions and configs

all nodes

$ apt-get update
$ apt-get purge -y mysql* 
$ apt-get purge -y mariadb* 
$ apt-get purge -y percona*
$ apt-get autoremove -y
$ rm -rf /etc/mysql
$ rm -rf /var/lib/mysql
$ rm -rf /etc/rc*.d/*mysql
$ rm -rf /var/lib/update-rc.d/mysql

Setup Hostnames and SSH Passwordless logins between the nodes

Generate the rsa key all nodes

$ ssh-keygen -t rsa

Enter file in which to save the key (/root/.ssh/id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Optional: Add hosts to /etc/hosts.allow all nodes

$ echo -e "\nALL: dbnode1\nALL: dbnode2\nALL: dbnode3" >> /etc/hosts.allow
Node1
$ echo -e "\n#MariaDB Cluster Node Names\n127.0.0.1 dbnode1\n22.22.22.22 dbnode2\n33.33.33.33 dbnode3" >> /etc/hosts
$ ssh-copy-id root@dbnode2
$ ssh-copy-id root@dbnode3
Node2
$ echo -e "\n#MariaDB Cluster Node Names\n11.11.11.11 dbnode1\n127.0.0.1 dbnode2\n33.33.33.33 dbnode3" >> /etc/hosts
$ ssh-copy-id root@dbnode1
$ ssh-copy-id root@dbnode3
Node3
$ echo -e "\n#MariaDB Cluster Node Names\n11.11.11.11 dbnode1\n22.22.22.22 dbnode2\n127.0.0.1 dbnode3" >> /etc/hosts
$ ssh-copy-id root@dbnode1
$ ssh-copy-id root@dbnode2

Install the latest MariaDB Cluster

Install the latest mariadb ( mysql )

all nodes add the repository

$ echo -e "# MariaDB 5.5 repository list - created 2014-01-10 22:00 UTC\n# http://mariadb.org/mariadb/repositories/\ndeb http://mirror.i3d.net/pub/mariadb/repo/5.5/ubuntu saucy main\ndeb-src http://mirror.i3d.net/pub/mariadb/repo/5.5/ubuntu saucy main" > /etc/apt/sources.list.d/MariaDB.list

Import the GP key all nodes

$ apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db

Apt-Pinning the packages all nodes

$ echo -e "# MariaDB repository\nPackage: *\nPin: origin i3d.net\nPin-Priority: 1000\n\nPackage: *\nPin: origin mirror.i3d.net\nPin-Priority: 1000\n\nPackage: libmysqlclient18\nPin: origin mirror.i3d.net\nPin-Priority: 1000" > /etc/apt/preferences.d/00MariaDB.pref

Install MariaDB all nodes

$ apt-get update && apt-get install -y galera mariadb-galera-server-5.5 mariadb-client-5.5 libmariadbclient18 mariadb-client-core-5.5 rsync netcat-openbsd

SET A MARIADB ROOT PASSWORD, SAME ON ALL NODES

Initial MariaDB Configuration

Shutdown all instances of mariadb all nodes

$ service mysql stop

Node1

Replace dbclusterroot with a random username Replace securepassword with a random password

$ service mysql start
$ mysql -u root -e "CREATE USER 'root'@'localhost' IDENTIFIED BY 'securepassword'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;"
$ mysql -u root -e "CREATE USER 'root'@'dbnode1' IDENTIFIED BY 'securepassword'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'dbnode1' WITH GRANT OPTION;"
$ mysql -u root -e "CREATE USER 'root'@'dbnode2' IDENTIFIED BY 'securepassword'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'dbnode2' WITH GRANT OPTION;"
$ mysql -u root -e "CREATE USER 'root'@'dbnode3' IDENTIFIED BY 'securepassword'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'dbnode3' WITH GRANT OPTION;"
$ mysql -u root -e "GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'localhost' WITH GRANT OPTION;"

Configure MariaDB

Add missing my.cnf options all nodes

$ sed -i 's|query_cache_size|#query_cache_size|g'  /etc/mysql/my.cnf
$ sed -i 's|default_storage_engine|#default_storage_engine|g'  /etc/mysql/my.cnf
$ sed -i 's|127.0.0.1|0.0.0.0|g'  /etc/mysql/my.cnf
 
 
$ sed -i -e 's|\[mysqld\]|\[mysqld\]\n## Powered by eXtremeSHOK.com ##\n# * START * Mandatory settings REQUIRED for proper cluster operations#\n# Mandatory settings: DO NOT MODIFY\nquery_cache_size=0\nbinlog_format=ROW\ndefault_storage_engine=innodb\ninnodb_autoinc_lock_mode=2\ninnodb_locks_unsafe_for_binlog=1\ninnodb_doublewrite=1\n# wsrep provider configuration: basic wsrep options\nwsrep_provider=/usr/lib/galera/libgalera_smm.so\nwsrep_provider_options="gcache.size=256M; gcache.page_size=128M"\nwsrep_cluster_address=gcomm://{hosts_list}\n#wsrep_cluster_address=gcomm://\nwsrep_cluster_name="{cluster_name}"\nwsrep_node_address="{node_name}"\nwsrep_node_name="{node_name}"\nwsrep_sst_method=rsync\nwsrep_sst_auth="{root_username}:{root_password}"\n# additional wsrep settings\nwsrep_node_incoming_address={node_address}\nwsrep_sst_receive_address={node_address}\nwsrep_slave_threads=16\n#wsrep_sst_donor={donor}\n# * END * Mandatory settings REQUIRED for proper cluster operations#\n## Powered by eXtremeSHOK.com ##\n|' /etc/mysql/my.cnf
 
$ sed -i -e 's|innodb_log_buffer_size|innodb_flush_log_at_trx_commit = 2\ninnodb_log_buffer_size|' /etc/mysql/my.cnf
 
$ sed -i 's|{cluster_name}|"MariaDB_Cluster"|'  /etc/mysql/my.cnf
$ sed -i 's|{hosts_list}|"dbnode1,dbnode2,dbnode3"|'  /etc/mysql/my.cnf

Create the cluster root sql user all nodes

Replace securepassword with a random password

avoid all special characters

$ sed -i 's|{root_username}:{root_password}|"root:securepassword"|'  /etc/mysql/my.cnf

Default is 4GB ram **optional: gcache for 512MB ram all nodes

$ sed -i 's|gcache.size=2G; gcache.page_size=1G|gcache.size=256M; gcache.page_size=128M|g'  /etc/mysql/my.cnf

**optional: gcache for 1024MB ram all nodes

$ sed -i 's|gcache.size=2G; gcache.page_size=1G|gcache.size=512M; gcache.page_size=256M|g'  /etc/mysql/my.cnf

**optional: gcache for 2GB ram all nodes

$ sed -i 's|gcache.size=2G; gcache.page_size=1G|gcache.size=1G; gcache.page_size=512M|g'  /etc/mysql/my.cnf

**optional: Disable slow query logging all nodes

$ sed -i -e 's|slow_query_log_file|slow_query_log = 0\nslow_query_log_file|g'  /etc/mysql/my.cnf
Node1
$ sed -i 's|{node_name}|dbnode1|'  /etc/mysql/my.cnf
$ sed -i 's|{node_address}|11.11.11.11|'  /etc/mysql/my.cnf
Node2
$ sed -i 's|{node_name}|dbnode2|'  /etc/mysql/my.cnf
$ sed -i 's|{node_address}|22.22.22.22|'  /etc/mysql/my.cnf
Node3
$ sed -i 's|{node_name}|dbnode3|'  /etc/mysql/my.cnf
$ sed -i 's|{node_address}|33.33.33.33|'  /etc/mysql/my.cnf

Bugfix: Access denied for user ‘debian-sys-maint’@'localhost’

[Warning] Access denied for user ‘debian-sys-maint’@'localhost’ (using password: YES) Node1

$ scp /etc/mysql/debian.cnf dbnode2:/etc/mysql/debian.cnf
$ scp /etc/mysql/debian.cnf dbnode3:/etc/mysql/debian.cnf

Optional: Install the latest xtrabackup ( wsrep_sst_method=xtrabackup ) to avoid read-only Donor during SST

The main advantage of using xtrabackup to synchronize the nodes, is that the Donor is writeable during the synchronization process. add the repository all nodes

$ echo -e "# Percona repository list - created 2012-06-17 16:25 UTC\n# http://www.percona.com/doc/percona-server/5.5/installation/apt_repo.html\ndeb http://repo.percona.com/apt saucy  main\ndeb-src http://repo.percona.com/apt saucy main" > /etc/apt/sources.list.d/Percona.list
Import the GP key all nodes
$ gpg --keyserver  hkp://keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A && gpg -a --export CD2EFD2A | apt-key add -

Install Xtrabackup and Percona-toolkit all nodes Note: install netcat-openbsd fixes the error: nc: invalid option — ‘d’

$ apt-get update && apt-get -y install percona-toolkit percona-xtrabackup netcat-openbsd

Set the wsrep_sst_method to xtrabackup all nodes

$ sed -i 's|wsrep_sst_method=rsync|wsrep_sst_method=xtrabackup|'  /etc/mysql/my.cnf

Bootstrap the cluster

Stop mysql** all nodes **

$ service mysql stop
$ service rsync stop
# added to prevent issues from non terminated processes
$ pkill -9 -f maria
$ pkill -9 -f percona
$ pkill -9 -f mysql
$ pkill -9 -f rsync
$ pkill -9 -f innobackupex

Bootstrap node1** node 1 ** Set the wsrep_cluster_address to gcom:// to enable the cluster to be bootstrapped

$ sed -i -e 's|wsrep_cluster_address=gcom://|wsrep_cluster_address=gcom://\n#|' /etc/mysql/my.cnf

Start MySQL Server

$ service mysql start

Check that mysql is online, functioning and to monitor real-time node status

$ watch -d -n1 -x mysql -B --password=securepassword -e "SHOW STATUS WHERE variable_name ='wsrep_local_state_comment' OR variable_name ='wsrep_cluster_size' OR variable_name ='wsrep_incoming_addresses' OR variable_name ='wsrep_cluster_status' OR variable_name ='wsrep_connected' OR variable_name ='wsrep_ready' OR variable_name ='wsrep_local_state_uuid' OR variable_name ='wsrep_cluster_state_uuid';"

Variable_name Value
wsrep_local_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_local_state_comment Synced
wsrep_incoming_addresses 33.33.33.33:3306
wsrep_cluster_size 1
wsrep_cluster_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_cluster_status Primary
wsrep_connected ON
wsrep_ready ON

Restore the wsrep_cluster_address to the cluster, to enable node1 to connect to the cluster

$ sed -i -e ':a;N;$!ba;s|wsrep_cluster_address=gcom://\n#|wsrep_cluster_address=gcom://|' /etc/mysql/my.cnf

Bootstrap node2**node 2 **

$ service mysql start

Check that mysql is online, functioning and to monitor real-time node status

$ watch -d -n1 -x mysql -B --password=securepassword -e "SHOW STATUS WHERE variable_name ='wsrep_local_state_comment' OR variable_name ='wsrep_cluster_size' OR variable_name ='wsrep_incoming_addresses' OR variable_name ='wsrep_cluster_status' OR variable_name ='wsrep_connected' OR variable_name ='wsrep_ready' OR variable_name ='wsrep_local_state_uuid' OR variable_name ='wsrep_cluster_state_uuid';"

Variable_name Value
wsrep_local_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_local_state_comment Synced
wsrep_incoming_addresses 33.33.33.33:3306,11.11.11.11:3306
wsrep_cluster_size 2
wsrep_cluster_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_cluster_status Primary
wsrep_connected ON
wsrep_ready ON

Bootstrap node2**node 3 **

service mysql start

Check that mysql is online, functioning and to monitor real-time node status

$ watch -d -n1 -x mysql -B --password=securepassword -e "SHOW STATUS WHERE variable_name ='wsrep_local_state_comment' OR variable_name ='wsrep_cluster_size' OR variable_name ='wsrep_incoming_addresses' OR variable_name ='wsrep_cluster_status' OR variable_name ='wsrep_connected' OR variable_name ='wsrep_ready' OR variable_name ='wsrep_local_state_uuid' OR variable_name ='wsrep_cluster_state_uuid';"

Variable_name Value
wsrep_local_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_local_state_comment Synced
wsrep_incoming_addresses 33.33.33.33:3306,22.22.22.22:3306,11.11.11.11:3306
wsrep_cluster_size 3
wsrep_cluster_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_cluster_status Primary
wsrep_connected ON
wsrep_ready ON

Notes / Bug Fixes: * Starting MariaDB database server mysqld [fail]

I have had issues with large databases and mysql failing to start. This is caused by the start script timing out before rsync is able to complete. A simple solution to this is to start the failed node manually with the following: Make sure mysql is not running and force kill any processes

service mysql stop
service rsync stop
#added to prevent issues from non terminated processes
pkill -9 -f maria
pkill -9 -f mysql
pkill -9 -f rsync

Start mysql manually

mysqld --service-startup-timeout=-1 > /dev/null 2>&1 &
Test

Monitor the MariaDB Cluster Status

watch -d -n1 -x mysql -B --password="securepassword" -e "SHOW STATUS LIKE 'wsrep_%';"

Variable_name Value
wsrep_local_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_protocol_version 4
wsrep_last_committed 44150
wsrep_replicated 20000
wsrep_replicated_bytes 22623578
wsrep_received 4163
wsrep_received_bytes 4863775
wsrep_local_commits 20000
wsrep_local_cert_failures 0
wsrep_local_bf_aborts 0
wsrep_local_replays 0
wsrep_local_send_queue 0
wsrep_local_send_queue_avg 0.000000
wsrep_local_recv_queue 0
wsrep_local_recv_queue_avg 0.000000
wsrep_flow_control_paused 0.000000
wsrep_flow_control_sent 0
wsrep_flow_control_recv 0
wsrep_cert_deps_distance 0.000000
wsrep_apply_oooe 0.000000
wsrep_apply_oool 0.000000
wsrep_apply_window 0.000000
wsrep_commit_oooe 0.000000
wsrep_commit_oool 0.000000
wsrep_commit_window 0.000000
wsrep_local_state 4
wsrep_local_state_comment Synced
wsrep_cert_index_size 0
wsrep_causal_reads 0
wsrep_incoming_addresses 33.33.33.33:3306,22.22.22.22:3306,11.11.11.11:3306
wsrep_cluster_conf_id 22
wsrep_cluster_size 3
wsrep_cluster_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_cluster_status Primary
wsrep_connected ON
wsrep_local_index 0
wsrep_provider_name Galera
wsrep_provider_vendor Codership Oy i...@codership.com
wsrep_provider_version 23.2.4(r147)
wsrep_ready ON

$ watch -d -n1 -x mysql -B --password=securepassword -e "SHOW STATUS WHERE variable_name ='wsrep_local_state_comment' OR variable_name ='wsrep_cluster_size' OR variable_name ='wsrep_incoming_addresses' OR variable_name ='wsrep_cluster_status' OR variable_name ='wsrep_connected' OR variable_name ='wsrep_ready' OR variable_name ='wsrep_local_state_uuid' OR variable_name ='wsrep_cluster_state_uuid';"

Variable_name Value
wsrep_local_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_local_state_comment Synced
wsrep_incoming_addresses 33.33.33.33:3306,22.22.22.22:3306,11.11.11.11:3306
wsrep_cluster_size 3
wsrep_cluster_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_cluster_status Primary
wsrep_connected ON
wsrep_ready ON

Performance Testing

Create the sbtest database with 1 000 000 tables** Node1 **

Replace securepassword with a random password

$ mysql -u root --password="securepassword" -e "CREATE SCHEMA 'sbtest';" 
$ sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=root --mysql-password=securepassword --mysql-host=localhost --mysql-port=3306 prepare

Run the test on all nodes** All Nodes **

Replace securepassword with a random password

$ sysbench --num-threads=16 --max-time=300 --max-requests=500000 --test=oltp --mysql-user=root --mysql-password=securepassword --mysql-host=localhost --mysql-port=3306 --oltp-test-mode=complex --oltp-table-size=1000000 run

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using “BEGIN” for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 500000
Threads started!
Time limit exceeded, exiting…
(last message repeated 15 times)
Done.

OLTP test statistics:
queries performed:
read: 856198
write: 305785
other: 122314
total: 1284297
transactions: 61157 (203.81 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1161983 (3872.47 per sec.)
other operations: 122314 (407.63 per sec.)

Test execution summary:
total time: 300.0627s
total number of events: 61157
total time taken by event execution: 4799.9976
per-request statistics:
min: 55.43ms
avg: 78.49ms
max: 689.76ms
approx. 95 percentile: 98.52ms

Threads fairness:
events (avg/stddev): 3822.3125/6.61
execution time (avg/stddev): 299.9999/0.02

Test Cluster load with Pen Load Balancer

This is just to test load balance performance, I suggest you use either GLB or HAProxy in a production environment Install Pen** Node 1 **

$ apt-get install -y pen

Run Pen and listen to port 4406** Node 1 **

$ pen -d 4406 dbnode1:3306 dbnode2:3306 dbnode3:3306

Execute the Sysbench, using the Pen Load Balancer

sysbench --num-threads=8 --max-time=300 --max-requests=500000 --test=oltp --mysql-user=root --mysql-password=securepassword --mysql-host=localhost --mysql-port=4406 --oltp-test-mode=complex --oltp-table-size=1000000 run

Number of threads: 16

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using “BEGIN” for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 500000
Threads started!
Time limit exceeded, exiting…
(last message repeated 15 times)
Done.

OLTP test statistics:
queries performed:
read: 855974
write: 305705
other: 122282
total: 1283961
transactions: 61141 (203.76 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1161679 (3871.43 per sec.)
other operations: 122282 (407.52 per sec.)

Test execution summary:
total time: 300.0643s
total number of events: 61141
total time taken by event execution: 4799.9052
per-request statistics:
min: 55.38ms
avg: 78.51ms
max: 605.87ms
approx. 95 percentile: 94.22ms

Threads fairness:
events (avg/stddev): 3821.3125/11.27
execution time (avg/stddev): 299.9941/0.02

Terminate Pen
$ pkill -9 -f pen
Optional: Galera Load Balancer ( GLB )

Note: I will only write a guide once the 1.0.1 version is released.

http://www.codership.com/downloads/glb

http://www.codership.com/files/glb/README-1.0.1

Optional: CSF Firewall

Allow/add the dbnode IPs to csf** all nodes **

$ csf -a 11.11.11.11
$ csf -a 22.22.22.22
$ csf -a 33.33.33.33
$ csf -r
[THE ITEMS BELOW ARE IN PROGRESS :: DO NOT USE]!!!!
Optional: UFW Firewall

Install UFW

Secure SSH for galera/wsrep Secure MariaDB

$ mysql_secure_installation

Enter current password for root (enter for none): yoursecurepassword
Change the root password? N
Remove anonymous users? Y
Disallow root login remotely? Y
Remove test database and access to it? Y
Reload privilege tables now? Y

Restart MariaDB (mysql server)

$ service mysql restart

Confirm MariaDB is running

$ netstat -tap | grep mysql

tcp 0 0 localhost:mysql : LISTEN 11878/mysqld

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment