-
MySQL/Galera Configuration Tips - Parallel Applying (wsrep_slave_threads)
-
Avoiding Deadlocks in Galera - HAProxy single-node writes and multi-node reads
-
Online Schema Upgrade in MySQL Galera Cluster using RSU Method
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
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 system timezone to UTC
$ ln -sf /usr/share/zoneinfo/UTC /etc/localtime
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
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
$ 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
$ 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
$ 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
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
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;"
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
$ sed -i 's|{node_name}|dbnode1|' /etc/mysql/my.cnf
$ sed -i 's|{node_address}|11.11.11.11|' /etc/mysql/my.cnf
$ sed -i 's|{node_name}|dbnode2|' /etc/mysql/my.cnf
$ sed -i 's|{node_address}|22.22.22.22|' /etc/mysql/my.cnf
$ sed -i 's|{node_name}|dbnode3|' /etc/mysql/my.cnf
$ sed -i 's|{node_address}|33.33.33.33|' /etc/mysql/my.cnf
[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
$ 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
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
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 &
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
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
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
$ pkill -9 -f pen
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
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
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