MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB. It is available on Linux only, and only supports the XtraDB/InnoDB storage engines (although there is experimental support for MyISAM - see the wsrep_replicate_myisam system variable).
For this, I have setup 3 virtual machines in the host ubuntu. The virtual machines are using ubuntu 1404. The network settings for this devices are bridge to my host network card and so I can access these vms remotely via ssh. This tutorial primarily is reference this and this. If you want explanation, please read the links mentioned before. I will quickly demonstrate what actually is galera cluster in few minutes.
Below shown are apt update on node 2. We then install package python-software-properties and add the mariadb galera package repository. You should repeat these steps on all 3 nodes.
galera2@galera2-VirtualBox:~$ sudo apt-get update
[sudo] password for galera2:
Ign http://my.archive.ubuntu.com trusty InRelease
Ign http://security.ubuntu.com trusty-security InRelease
Ign http://my.archive.ubuntu.com trusty-updates InRelease
Hit http://security.ubuntu.com trusty-security Release.gpg
Ign http://extras.ubuntu.com trusty InRelease
Ign http://my.archive.ubuntu.com trusty-backports InRelease
Hit http://security.ubuntu.com trusty-security Release
Hit http://extras.ubuntu.com trusty Release.gpg
Hit http://my.archive.ubuntu.com trusty Release.gpg
Hit http://extras.ubuntu.com trusty Release
Hit http://my.archive.ubuntu.com trusty-updates Release.gpg
Hit http://my.archive.ubuntu.com trusty-backports Release.gpg
Hit http://security.ubuntu.com trusty-security/main Sources
Hit http://my.archive.ubuntu.com trusty Release
Hit http://extras.ubuntu.com trusty/main Sources
Hit http://my.archive.ubuntu.com trusty-updates Release
Hit http://security.ubuntu.com trusty-security/restricted Sources
Hit http://extras.ubuntu.com trusty/main i386 Packages
Hit http://my.archive.ubuntu.com trusty-backports Release
Hit http://security.ubuntu.com trusty-security/universe Sources
Hit http://security.ubuntu.com trusty-security/multiverse Sources
Hit http://security.ubuntu.com trusty-security/main i386 Packages
Hit http://my.archive.ubuntu.com trusty/main Sources
Hit http://security.ubuntu.com trusty-security/restricted i386 Packages
Hit http://my.archive.ubuntu.com trusty/restricted Sources
Hit http://security.ubuntu.com trusty-security/universe i386 Packages
Hit http://my.archive.ubuntu.com trusty/universe Sources
Hit http://security.ubuntu.com trusty-security/multiverse i386 Packages
Hit http://my.archive.ubuntu.com trusty/multiverse Sources
Hit http://security.ubuntu.com trusty-security/main Translation-en
Hit http://my.archive.ubuntu.com trusty/main i386 Packages
Hit http://security.ubuntu.com trusty-security/multiverse Translation-en
Hit http://my.archive.ubuntu.com trusty/restricted i386 Packages
Hit http://security.ubuntu.com trusty-security/restricted Translation-en
Hit http://security.ubuntu.com trusty-security/universe Translation-en
Hit http://my.archive.ubuntu.com trusty/universe i386 Packages
Ign http://extras.ubuntu.com trusty/main Translation-en_US
Hit http://my.archive.ubuntu.com trusty/multiverse i386 Packages
Ign http://extras.ubuntu.com trusty/main Translation-en
Hit http://my.archive.ubuntu.com trusty/main Translation-en
Hit http://my.archive.ubuntu.com trusty/multiverse Translation-en
Hit http://my.archive.ubuntu.com trusty/restricted Translation-en
Hit http://my.archive.ubuntu.com trusty/universe Translation-en
Hit http://my.archive.ubuntu.com trusty-updates/main Sources
Hit http://my.archive.ubuntu.com trusty-updates/restricted Sources
Hit http://my.archive.ubuntu.com trusty-updates/universe Sources
Hit http://my.archive.ubuntu.com trusty-updates/multiverse Sources
Hit http://my.archive.ubuntu.com trusty-updates/main i386 Packages
Hit http://my.archive.ubuntu.com trusty-updates/restricted i386 Packages
Hit http://my.archive.ubuntu.com trusty-updates/universe i386 Packages
Hit http://my.archive.ubuntu.com trusty-updates/multiverse i386 Packages
Hit http://my.archive.ubuntu.com trusty-updates/main Translation-en
Hit http://my.archive.ubuntu.com trusty-updates/multiverse Translation-en
Hit http://my.archive.ubuntu.com trusty-updates/restricted Translation-en
Hit http://my.archive.ubuntu.com trusty-updates/universe Translation-en
Hit http://my.archive.ubuntu.com trusty-backports/main Sources
Hit http://my.archive.ubuntu.com trusty-backports/restricted Sources
Hit http://my.archive.ubuntu.com trusty-backports/universe Sources
Hit http://my.archive.ubuntu.com trusty-backports/multiverse Sources
Hit http://my.archive.ubuntu.com trusty-backports/main i386 Packages
Hit http://my.archive.ubuntu.com trusty-backports/restricted i386 Packages
Hit http://my.archive.ubuntu.com trusty-backports/universe i386 Packages
Hit http://my.archive.ubuntu.com trusty-backports/multiverse i386 Packages
Hit http://my.archive.ubuntu.com trusty-backports/main Translation-en
Hit http://my.archive.ubuntu.com trusty-backports/multiverse Translation-en
Hit http://my.archive.ubuntu.com trusty-backports/restricted Translation-en
Hit http://my.archive.ubuntu.com trusty-backports/universe Translation-en
Ign http://my.archive.ubuntu.com trusty/main Translation-en_US
Ign http://my.archive.ubuntu.com trusty/multiverse Translation-en_US
Ign http://my.archive.ubuntu.com trusty/restricted Translation-en_US
Ign http://my.archive.ubuntu.com trusty/universe Translation-en_US
Reading package lists... Done
galera2@galera2-VirtualBox:~$ sudo apt-get install python-software-properties
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
python-pycurl
Suggested packages:
libcurl4-gnutls-dev python-pycurl-dbg
The following NEW packages will be installed:
python-pycurl python-software-properties
0 upgraded, 2 newly installed, 0 to remove and 48 not upgraded.
Need to get 66.5 kB of archives.
After this operation, 352 kB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 http://my.archive.ubuntu.com/ubuntu/ trusty/main python-pycurl i386 7.19.3-0ubuntu3 [46.9 kB]
Get:2 http://my.archive.ubuntu.com/ubuntu/ trusty-updates/universe python-software-properties all 0.92.37.3 [19.6 kB]
Fetched 66.5 kB in 2s (26.8 kB/s)
Selecting previously unselected package python-pycurl.
(Reading database ... 167815 files and directories currently installed.)
Preparing to unpack .../python-pycurl_7.19.3-0ubuntu3_i386.deb ...
Unpacking python-pycurl (7.19.3-0ubuntu3) ...
Selecting previously unselected package python-software-properties.
Preparing to unpack .../python-software-properties_0.92.37.3_all.deb ...
Unpacking python-software-properties (0.92.37.3) ...
Processing triggers for doc-base (0.10.5) ...
Processing 32 changed doc-base files, 1 added doc-base file...
Setting up python-pycurl (7.19.3-0ubuntu3) ...
Setting up python-software-properties (0.92.37.3) ...
galera2@galera2-VirtualBox:~$
galera2@galera2-VirtualBox:~$ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
[sudo] password for galera2:
Executing: gpg --ignore-time-conflict --no-options --no-default-keyring --homedir /tmp/tmp.reQL8fqGDv --no-auto-check-trustdb --trust-model always --keyring /etc/apt/trusted.gpg --primary-keyring /etc/apt/trusted.gpg --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
gpg: requesting key 1BB943DB from hkp server keyserver.ubuntu.com
gpg: key 1BB943DB: public key "MariaDB Package Signing Key <package-signing-key@mariadb.org>" imported
gpg: Total number processed: 1
gpg: imported: 1
galera2@galera2-VirtualBox:~$ sudo add-apt-repository 'deb http://mariadb.nethub.com.hk//repo/5.5/ubuntu trusty main'
galera2@galera2-VirtualBox:~$ sudo apt-get update
Get:1 http://mariadb.nethub.com.hk trusty InRelease [2,488 B]
Ign http://security.ubuntu.com trusty-security InRelease
Ign http://my.archive.ubuntu.com trusty InRelease
Hit http://security.ubuntu.com trusty-security Release.gpg
Ign http://extras.ubuntu.com trusty InRelease
Ign http://my.archive.ubuntu.com trusty-updates InRelease
Ign http://my.archive.ubuntu.com trusty-backports InRelease
Hit http://security.ubuntu.com trusty-security Release
Hit http://extras.ubuntu.com trusty Release.gpg
Hit http://my.archive.ubuntu.com trusty Release.gpg
Hit http://extras.ubuntu.com trusty Release
Hit http://my.archive.ubuntu.com trusty-updates Release.gpg
Get:2 http://mariadb.nethub.com.hk trusty/main i386 Packages [5,041 B]
Hit http://my.archive.ubuntu.com trusty-backports Release.gpg
Hit http://my.archive.ubuntu.com trusty Release
Hit http://security.ubuntu.com trusty-security/main Sources
Hit http://my.archive.ubuntu.com trusty-updates Release
Hit http://my.archive.ubuntu.com trusty-backports Release
Hit http://extras.ubuntu.com trusty/main Sources
Hit http://my.archive.ubuntu.com trusty/main Sources
Hit http://security.ubuntu.com trusty-security/restricted Sources
Hit http://extras.ubuntu.com trusty/main i386 Packages
Hit http://my.archive.ubuntu.com trusty/restricted Sources
Hit http://security.ubuntu.com trusty-security/universe Sources
Hit http://security.ubuntu.com trusty-security/multiverse Sources
Hit http://my.archive.ubuntu.com trusty/universe Sources
Hit http://my.archive.ubuntu.com trusty/multiverse Sources
Hit http://security.ubuntu.com trusty-security/main i386 Packages
Hit http://security.ubuntu.com trusty-security/restricted i386 Packages
Hit http://my.archive.ubuntu.com trusty/main i386 Packages
Hit http://security.ubuntu.com trusty-security/universe i386 Packages
Hit http://security.ubuntu.com trusty-security/multiverse i386 Packages
Hit http://my.archive.ubuntu.com trusty/restricted i386 Packages
Hit http://security.ubuntu.com trusty-security/main Translation-en
Hit http://my.archive.ubuntu.com trusty/universe i386 Packages
Hit http://security.ubuntu.com trusty-security/multiverse Translation-en
Hit http://my.archive.ubuntu.com trusty/multiverse i386 Packages
Ign http://mariadb.nethub.com.hk trusty/main Translation-en_US
Hit http://security.ubuntu.com trusty-security/restricted Translation-en
Ign http://mariadb.nethub.com.hk trusty/main Translation-en
Hit http://my.archive.ubuntu.com trusty/main Translation-en
Ign http://extras.ubuntu.com trusty/main Translation-en_US
Hit http://security.ubuntu.com trusty-security/universe Translation-en
Ign http://extras.ubuntu.com trusty/main Translation-en
Hit http://my.archive.ubuntu.com trusty/multiverse Translation-en
Hit http://my.archive.ubuntu.com trusty/restricted Translation-en
Hit http://my.archive.ubuntu.com trusty/universe Translation-en
Hit http://my.archive.ubuntu.com trusty-updates/main Sources
Hit http://my.archive.ubuntu.com trusty-updates/restricted Sources
Hit http://my.archive.ubuntu.com trusty-updates/universe Sources
Hit http://my.archive.ubuntu.com trusty-updates/multiverse Sources
Hit http://my.archive.ubuntu.com trusty-updates/main i386 Packages
Hit http://my.archive.ubuntu.com trusty-updates/restricted i386 Packages
Hit http://my.archive.ubuntu.com trusty-updates/universe i386 Packages
Hit http://my.archive.ubuntu.com trusty-updates/multiverse i386 Packages
Hit http://my.archive.ubuntu.com trusty-updates/main Translation-en
Hit http://my.archive.ubuntu.com trusty-updates/multiverse Translation-en
Hit http://my.archive.ubuntu.com trusty-updates/restricted Translation-en
Hit http://my.archive.ubuntu.com trusty-updates/universe Translation-en
Hit http://my.archive.ubuntu.com trusty-backports/main Sources
Hit http://my.archive.ubuntu.com trusty-backports/restricted Sources
Hit http://my.archive.ubuntu.com trusty-backports/universe Sources
Hit http://my.archive.ubuntu.com trusty-backports/multiverse Sources
Hit http://my.archive.ubuntu.com trusty-backports/main i386 Packages
Hit http://my.archive.ubuntu.com trusty-backports/restricted i386 Packages
Hit http://my.archive.ubuntu.com trusty-backports/universe i386 Packages
Hit http://my.archive.ubuntu.com trusty-backports/multiverse i386 Packages
Hit http://my.archive.ubuntu.com trusty-backports/main Translation-en
Hit http://my.archive.ubuntu.com trusty-backports/multiverse Translation-en
Hit http://my.archive.ubuntu.com trusty-backports/restricted Translation-en
Hit http://my.archive.ubuntu.com trusty-backports/universe Translation-en
Ign http://my.archive.ubuntu.com trusty/main Translation-en_US
Ign http://my.archive.ubuntu.com trusty/multiverse Translation-en_US
Ign http://my.archive.ubuntu.com trusty/restricted Translation-en_US
Ign http://my.archive.ubuntu.com trusty/universe Translation-en_US
Fetched 7,529 B in 35s (212 B/s)
Reading package lists... Done
galera2@galera2-VirtualBox:~$ sudo apt-get install mariadb-galera-server galera rsync
Reading package lists... Done
Building dependency tree
Reading state information... Done
Note, selecting 'galera-3' instead of 'galera'
rsync is already the newest version.
The following extra packages will be installed:
gawk libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl
libmariadbclient18 libmysqlclient18 libsigsegv2 mariadb-client-5.5
mariadb-client-core-5.5 mariadb-common mariadb-galera-server-5.5
mysql-common
Suggested packages:
gawk-doc libmldbm-perl libnet-daemon-perl libplrpc-perl
libsql-statement-perl libipc-sharedcache-perl libterm-readkey-perl tinyca
mailx mariadb-galera-test socat
The following NEW packages will be installed:
galera-3 gawk libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl
libmariadbclient18 libmysqlclient18 libsigsegv2 mariadb-client-5.5
mariadb-client-core-5.5 mariadb-common mariadb-galera-server
mariadb-galera-server-5.5 mysql-common
0 upgraded, 15 newly installed, 0 to remove and 48 not upgraded.
Need to get 12.0 MB of archives.
After this operation, 115 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 http://mariadb.nethub.com.hk//repo/5.5/ubuntu/ trusty/main mysql-common all 5.5.45+maria-1~trusty [8,366 B]
Get:2 http://mariadb.nethub.com.hk//repo/5.5/ubuntu/ trusty/main mariadb-common all 5.5.45+maria-1~trusty [3,178 B]
Get:3 http://my.archive.ubuntu.com/ubuntu/ trusty/main libsigsegv2 i386 2.10-2 [14.8 kB]
Get:4 http://mariadb.nethub.com.hk//repo/5.5/ubuntu/ trusty/main libmariadbclient18 i386 5.5.45+maria-1~trusty [511 kB]
Get:5 http://my.archive.ubuntu.com/ubuntu/ trusty/main gawk i386 1:4.0.1+dfsg-2.1ubuntu2 [730 kB]
Get:6 http://mariadb.nethub.com.hk//repo/5.5/ubuntu/ trusty/main libmysqlclient18 i386 5.5.45+maria-1~trusty [2,850 B]
Get:7 http://mariadb.nethub.com.hk//repo/5.5/ubuntu/ trusty/main mariadb-client-core-5.5 i386 5.5.45+maria-1~trusty [607 kB]
Get:8 http://my.archive.ubuntu.com/ubuntu/ trusty/main libaio1 i386 0.3.109-4 [6,578 B]
Get:9 http://my.archive.ubuntu.com/ubuntu/ trusty/main libdbi-perl i386 1.630-1 [881 kB]
Get:10 http://mariadb.nethub.com.hk//repo/5.5/ubuntu/ trusty/main mariadb-client-5.5 i386 5.5.45+maria-1~trusty [928 kB]
Get:11 http://my.archive.ubuntu.com/ubuntu/ trusty/main libdbd-mysql-perl i386 4.025-1 [99.6 kB]
Get:12 http://my.archive.ubuntu.com/ubuntu/ trusty/main libhtml-template-perl all 2.95-1 [65.5 kB]
Get:13 http://mariadb.nethub.com.hk//repo/5.5/ubuntu/ trusty/main galera-3 i386 25.3.9-trusty [797 kB]
Get:14 http://mariadb.nethub.com.hk//repo/5.5/ubuntu/ trusty/main mariadb-galera-server-5.5 i386 5.5.45+maria-1~trusty [7,329 kB]
Get:15 http://mariadb.nethub.com.hk//repo/5.5/ubuntu/ trusty/main mariadb-galera-server all 5.5.45+maria-1~trusty [2,930 B]
Fetched 12.0 MB in 2min 19s (85.8 kB/s)
Preconfiguring packages ...
Selecting previously unselected package libsigsegv2:i386.
(Reading database ... 167858 files and directories currently installed.)
Preparing to unpack .../libsigsegv2_2.10-2_i386.deb ...
Unpacking libsigsegv2:i386 (2.10-2) ...
Setting up libsigsegv2:i386 (2.10-2) ...
Processing triggers for libc-bin (2.19-0ubuntu6.6) ...
Selecting previously unselected package gawk.
(Reading database ... 167866 files and directories currently installed.)
Preparing to unpack .../gawk_1%3a4.0.1+dfsg-2.1ubuntu2_i386.deb ...
Unpacking gawk (1:4.0.1+dfsg-2.1ubuntu2) ...
Selecting previously unselected package libaio1:i386.
Preparing to unpack .../libaio1_0.3.109-4_i386.deb ...
Unpacking libaio1:i386 (0.3.109-4) ...
Selecting previously unselected package mysql-common.
Preparing to unpack .../mysql-common_5.5.45+maria-1~trusty_all.deb ...
Unpacking mysql-common (5.5.45+maria-1~trusty) ...
Selecting previously unselected package mariadb-common.
Preparing to unpack .../mariadb-common_5.5.45+maria-1~trusty_all.deb ...
Unpacking mariadb-common (5.5.45+maria-1~trusty) ...
Selecting previously unselected package libdbi-perl.
Preparing to unpack .../libdbi-perl_1.630-1_i386.deb ...
Unpacking libdbi-perl (1.630-1) ...
Selecting previously unselected package libmariadbclient18.
Preparing to unpack .../libmariadbclient18_5.5.45+maria-1~trusty_i386.deb ...
Unpacking libmariadbclient18 (5.5.45+maria-1~trusty) ...
Selecting previously unselected package libmysqlclient18.
Preparing to unpack .../libmysqlclient18_5.5.45+maria-1~trusty_i386.deb ...
Unpacking libmysqlclient18 (5.5.45+maria-1~trusty) ...
Selecting previously unselected package libdbd-mysql-perl.
Preparing to unpack .../libdbd-mysql-perl_4.025-1_i386.deb ...
Unpacking libdbd-mysql-perl (4.025-1) ...
Selecting previously unselected package mariadb-client-core-5.5.
Preparing to unpack .../mariadb-client-core-5.5_5.5.45+maria-1~trusty_i386.deb ...
Unpacking mariadb-client-core-5.5 (5.5.45+maria-1~trusty) ...
Selecting previously unselected package mariadb-client-5.5.
Preparing to unpack .../mariadb-client-5.5_5.5.45+maria-1~trusty_i386.deb ...
Unpacking mariadb-client-5.5 (5.5.45+maria-1~trusty) ...
Selecting previously unselected package galera-3.
Preparing to unpack .../galera-3_25.3.9-trusty_i386.deb ...
Unpacking galera-3 (25.3.9-trusty) ...
Processing triggers for man-db (2.6.7.1-1ubuntu1) ...
Setting up mysql-common (5.5.45+maria-1~trusty) ...
Setting up mariadb-common (5.5.45+maria-1~trusty) ...
Selecting previously unselected package mariadb-galera-server-5.5.
(Reading database ... 168240 files and directories currently installed.)
Preparing to unpack .../mariadb-galera-server-5.5_5.5.45+maria-1~trusty_i386.deb ...
Unpacking mariadb-galera-server-5.5 (5.5.45+maria-1~trusty) ...
Selecting previously unselected package libhtml-template-perl.
Preparing to unpack .../libhtml-template-perl_2.95-1_all.deb ...
Unpacking libhtml-template-perl (2.95-1) ...
Selecting previously unselected package mariadb-galera-server.
Preparing to unpack .../mariadb-galera-server_5.5.45+maria-1~trusty_all.deb ...
Unpacking mariadb-galera-server (5.5.45+maria-1~trusty) ...
Processing triggers for man-db (2.6.7.1-1ubuntu1) ...
Processing triggers for ureadahead (0.100.0-16) ...
ureadahead will be reprofiled on next reboot
Setting up gawk (1:4.0.1+dfsg-2.1ubuntu2) ...
Setting up libaio1:i386 (0.3.109-4) ...
Setting up libdbi-perl (1.630-1) ...
Setting up galera-3 (25.3.9-trusty) ...
Setting up libhtml-template-perl (2.95-1) ...
Setting up libmysqlclient18 (5.5.45+maria-1~trusty) ...
Setting up libdbd-mysql-perl (4.025-1) ...
Setting up libmariadbclient18 (5.5.45+maria-1~trusty) ...
Setting up mariadb-client-core-5.5 (5.5.45+maria-1~trusty) ...
Setting up mariadb-client-5.5 (5.5.45+maria-1~trusty) ...
Setting up mariadb-galera-server-5.5 (5.5.45+maria-1~trusty) ...
* Stopping MariaDB database server mysqld [ OK ]
150907 23:31:54 [Note] /usr/sbin/mysqld (mysqld 5.5.45-MariaDB-1~trusty-wsrep-log) starting as process 5897 ...
150907 23:31:54 [Note] WSREP: Read nil XID from storage engines, skipping position init
150907 23:31:54 [Note] WSREP: wsrep_load(): loading provider library 'none'
150907 23:31:58 [Note] Plugin 'InnoDB' is disabled.
150907 23:31:58 [Note] Plugin 'FEEDBACK' is disabled.
150907 23:32:00 [Note] WSREP: Service disconnected.
150907 23:32:01 [Note] WSREP: Some threads may fail to exit.
* Starting MariaDB database server mysqld [fail]
invoke-rc.d: initscript mysql, action "start" failed.
dpkg: error processing package mariadb-galera-server-5.5 (--configure):
subprocess installed post-installation script returned error exit status 1
dpkg: dependency problems prevent configuration of mariadb-galera-server:
mariadb-galera-server depends on mariadb-galera-server-5.5 (= 5.5.45+maria-1~trusty); however:
Package mariadb-galera-server-5.5 is not configured yet.
dpkg: error processing package mariadb-galera-server (--configure):
dependency problems - leaving unconfigured
Processing triggers for libc-bin (2.19-0ubuntu6.6) ...
No apport report written because the error message indicates its a followup error from a previous failure.
Processing triggers for ureadahead (0.100.0-16) ...
Errors were encountered while processing:
mariadb-galera-server-5.5
mariadb-galera-server
E: Sub-process /usr/bin/dpkg returned an error code (1)
At this stage, it is okay if it fail as we have yet to configure the cluster configuration for each node. Just ignore the error message and stop the mysql instance in all the nodes if it started. Next , we will configure in each node for the configuration files.
galera1@galera1-VirtualBox:~$ sudo nano /etc/mysql/conf.d/cluster.cnf
galera1@galera1-VirtualBox:~$ hostname
galera1-VirtualBox
galera1@galera1-VirtualBox:~$ sudo nano /etc/mysql/conf.d/cluster.cnf
[sudo] password for galera1:
galera1@galera1-VirtualBox:~$ sudo cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = IIM5sEkzTo4rCBRa
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = IIM5sEkzTo4rCBRa
socket = /var/run/mysqld/mysqld.sock
basedir = /usr
galera1@galera1-VirtualBox:~$ sudo su -
root@galera1-VirtualBox:~# cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = IIM5sEkzTo4rCBRa
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = IIM5sEkzTo4rCBRa
socket = /var/run/mysqld/mysqld.sock
basedir = /usr
root@galera1-VirtualBox:~# ps aux | grep mysql
root 8128 0.1 0.5 5508 2976 ? S Sep07 0:07 /bin/bash /usr/bin/mysqld_safe
mysql 9186 2.8 14.6 838928 74456 ? Sl Sep07 1:40 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
root 9187 0.0 0.3 4236 1808 ? S Sep07 0:00 logger -t mysqld -p daemon.error
root 9472 0.0 0.4 4692 2060 pts/14 S+ 00:05 0:00 grep --color=auto mysql
root@galera1-VirtualBox:~# service mysql stop
* Stopping MariaDB database server mysqld [ OK ]
root@galera1-VirtualBox:~# cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = IIM5sEkzTo4rCBRa
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = IIM5sEkzTo4rCBRa
socket = /var/run/mysqld/mysqld.sock
basedir = /usr
root@galera1-VirtualBox:~# ps aux | grep mysql
root 9524 0.0 0.4 4692 2100 pts/14 S+ 00:06 0:00 grep --color=auto mysql
root@galera1-VirtualBox:~# service mysql start --wsrep-new-cluster
* Starting MariaDB database server mysqld
-----------------------------------------
galera2@galera2-VirtualBox:~$ sudo ls /etc/mysql/conf.d/
mariadb.cnf mysqld_safe_syslog.cnf
galera2@galera2-VirtualBox:~$ hostname
galera2-VirtualBox
galera2@galera2-VirtualBox:~$ sudo nano /etc/mysql/conf.d/cluster.cnf
[sudo] password for galera2:
galera2@galera2-VirtualBox:~$ sudo su -
root@galera2-VirtualBox:~# cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = 0LhDWkMUHLeXWYyv
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = 0LhDWkMUHLeXWYyv
socket = /var/run/mysqld/mysqld.sock
basedir = /usr
root@galera2-VirtualBox:~# service mysql stop
* Stopping MariaDB database server mysqld [fail]
root@galera2-VirtualBox:~# ps aux | grep mysql
root 5980 0.1 0.6 5508 3072 ? S Sep07 0:07 /bin/bash /usr/bin/mysqld_safe
mysql 7019 2.1 14.7 839220 74532 ? Sl Sep07 1:55 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
root 7020 0.0 0.3 4236 1840 ? S Sep07 0:00 logger -t mysqld -p daemon.error
root 7390 0.0 0.4 4692 2060 pts/1 S+ 01:07 0:00 grep --color=auto mysql
root@galera2-VirtualBox:~# kill 7019
root@galera2-VirtualBox:~# ps aux | grep mysql
root 7402 0.0 0.4 4692 2064 pts/1 S+ 01:08 0:00 grep --color=auto mysql
root@galera2-VirtualBox:~# service mysql start
* Starting MariaDB database server mysqld [fail]
root@galera2-VirtualBox:~#
---------------------
galera3@galera3-VirtualBox:~$ sudo vi /etc/mysql/conf.d/cluster.cnf
[sudo] password for galera3:
galera3@galera3-VirtualBox:~$ hostname
galera3-VirtualBox
galera3@galera3-VirtualBox:~$ ll /usr/lib/galera/libgalera_smm.so
-rw-r--r-- 1 root root 2520000 Feb 27 2015 /usr/lib/galera/libgalera_smm.so
galera3@galera3-VirtualBox:~$ sudo nano /etc/mysql/conf.d/cluster.cnf
[sudo] password for galera3:
galera3@galera3-VirtualBox:~$ sudo su -
root@galera3-VirtualBox:~# nano /etc/mysql/debian.cnf
root@galera3-VirtualBox:~# service mysql stop
* Stopping MariaDB database server mysqld [fail]
root@galera3-VirtualBox:~# ps aux | grep mysql
root 9415 0.1 0.5 5508 2996 ? S Sep07 0:05 /bin/bash /usr/bin/mysqld_safe
mysql 10466 2.8 14.6 839220 74408 ? Sl Sep07 2:13 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
root 10467 0.0 0.3 4236 1756 ? S Sep07 0:00 logger -t mysqld -p daemon.error
root 10813 2.0 0.3 4692 2016 pts/6 S+ 01:08 0:00 grep --color=auto mysql
root@galera3-VirtualBox:~# kill 10466
root@galera3-VirtualBox:~#
root@galera3-VirtualBox:~# ps aux | grep mysql
root 10823 0.0 0.3 4692 2004 pts/6 S+ 01:09 0:00 grep --color=auto mysql
root@galera3-VirtualBox:~# service mysql start
* Starting MariaDB database server mysqld [fail]
root@galera3-VirtualBox:~# service mysql restart
* Stopping MariaDB database server mysqld [ OK ]
* Starting MariaDB database server mysqld [fail]
root@galera3-VirtualBox:~#
There are two files to edit for all the nodes as of following. The content of the files can be found here.
- /etc/mysql/conf.d/cluster.cnf
- /etc/mysql/debian.cnf
Once all three nodes configuration files edited, you start a node with this parameter --wsrep-new-cluster . You can see above I started on the first node. Then start the remaining nodes just like normal mysql start process.
As you might noticed, mysql cannot stop in my virtual machine. I have no idea why is that as on my good old host machine, with three vms running make these vms crawling.. I don't bother to investigate further and just want to quickly get into the database crud. But eventually all my three nodes are started and see below.
galera1@galera1-VirtualBox:~$ mysql -uroot -pgalera123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.45-MariaDB-1~trusty-wsrep-log
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CREATE DATABASE playground;
Query OK, 1 row affected (3.50 sec)
MariaDB [(none)]> CREATE TABLE playground.equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));
Query OK, 0 rows affected (10.96 sec)
MariaDB [(none)]>
-----------------------
galera2@galera2-VirtualBox:~$ mysql -uroot -pgalera123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.45-MariaDB-1~trusty-wsrep-log
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| playground |
+--------------------+
4 rows in set (0.04 sec)
MariaDB [(none)]> use playground;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [playground]> show tables;
+----------------------+
| Tables_in_playground |
+----------------------+
| equipment |
+----------------------+
1 row in set (0.01 sec)
MariaDB [playground]> INSERT INTO playground.equipment (type, quant, color) VALUES ("slide", 2, "blue");
Query OK, 1 row affected (0.67 sec)
MariaDB [playground]> SELECT * FROM playground.equipment;
+----+-------+-------+--------+
| id | type | quant | color |
+----+-------+-------+--------+
| 2 | slide | 2 | blue |
| 3 | swing | 10 | yellow |
+----+-------+-------+--------+
2 rows in set (0.00 sec)
MariaDB [playground]>
-----------------------
galera3@galera3-VirtualBox:~$ mysql -uroot -pgalera123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.45-MariaDB-1~trusty-wsrep-log mariadb.org binary distribution, wsrep_25.11.r4026
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use playground;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [playground]> SELECT * FROM playground.equipment;
+----+-------+-------+-------+
| id | type | quant | color |
+----+-------+-------+-------+
| 2 | slide | 2 | blue |
+----+-------+-------+-------+
1 row in set (0.03 sec)
MariaDB [playground]> INSERT INTO playground.equipment (type, quant, color) VALUES ("swing", 10, "yellow");
Query OK, 1 row affected (0.43 sec)
MariaDB [playground]>
If this excite you, then you should probably spend more time to learn how to administer and how to best use mariadb galera cluster. Last but not least, if you would like to sponsor/donate for me or this site, please do so as this this fund will channel into server maintenance and buy better hardware for my learning and share to you in future.
