Sunday, September 27, 2015

First learning into mariadb galera cluster

We will learn a cluster known as galera cluster today. There are 3 Galera versions (Codership, Percona XtraDB Cluster and MariaDB Galera Cluster). We will pick MariaDB galera cluster.  But what is MariaDB Galera Cluster?

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.

No comments:

Post a Comment