MySql Server Cluster (Maria+galera)

Add MariaDB Repositories
========================
Create a mariadb repository /etc/yum.repos.d/mariadb.repo using following content in your system. Below repository will work on CentOS 6.x systems, For other system use repository generation tool and add to your system.

Disable Selinux in redhat sever's.

For CentOS 6 – 64bit

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
For CentOS 6 – 32bit

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-x86
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
Install MariaDB and Galera
==========================
Before installing MariaDB Galera cluster packages, remove any existing MySQL or MariaDB packages installed on system. After that use following command to install on all nodes.

# yum install MariaDB-Galera-server MariaDB-client galera

Initial MariaDB Configuration
=============================
After successfully installing packages in above steps do the some initial MariaDB configurations. Use following command and follow the instructions on all nodes of cluster. If will prompt to set root account password also.

# mysql_secure_installation
# service mysql start
After that create a user in MariaDB on all nodes, which can access database from your network in cluster.

# mysql -u root -p

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit
and stop MariaDB service before starting cluster configuration

# service mysql stop
Setup Cluster Configuration on database1
========================================
Lets start setup MariaDB Galera cluster from database1 server. Edit MariaDB server configuration file and add following values under [mariadb] section.

[root@database1 ~]# vim /etc/my.cnf.d/server.cnf
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.4,10.0.0.5
wsrep_cluster_name='cluster1'
wsrep_node_address='10.0.0.2'
wsrep_node_name='database1'
wsrep_sst_method=rsync
wsrep_sst_auth=root:password
Start cluster using following command.

[root@database1 ~]# /etc/init.d/mysql bootstrap
Bootstrapping the clusterStarting MySQL.... SUCCESS!
If you get any problem during startup check MariaDB error log file /var/lib/mysql/<hostname>.err

Add database2 in MariaDB Cluster
================================
After successfully starting cluster on database1. Start configuration on database2. Edit MariaDB server configuration file and add following values under [mariadb] section. All the settings are similar to database1 except wsrep_node_address, wsrep_cluster_address and wsrep_node_name.

[root@database2 ~]# vim /etc/my.cnf.d/server.cnf

query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.2,10.0.0.5
wsrep_cluster_name='cluster1'
wsrep_node_address='10.0.0.4'
wsrep_node_name='database2'
wsrep_sst_method=rsync
wsrep_sst_auth=root:password

Start cluster using following command.

[root@database2 ~]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!

Add database3 in MariaDB Cluster
================================
This server is optional, If you want only two server in cluster, you can ignore this step, but you need to remove third server ip from database1/database2 configuration files. To add this server make changes same as database2.

[root@database3 ~]# vim /etc/my.cnf.d/server.cnf
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.2,10.0.0.4
wsrep_cluster_name='cluster1'
wsrep_node_address='10.0.0.5'
wsrep_node_name='database3'
wsrep_sst_method=rsync
wsrep_sst_auth=root:password
Start cluster using following command.

[root@db3 ~]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!

Enable up/Down arrow in powershell

For this, you need PSReadline. First, install PsGet if you don’t have it:To install it just run the following URL in powershell.

(new-object Net.WebClient).DownloadString("http://psget.net/GetPsGet.ps1 ' ') | iex
Then, install PSReadline:

install-module PSReadline
Import PSReadline after loading the persistent history:

Import-Module PSReadLine
And you will be able to recall previous commands with up arrow key. Add the following to have partial history search with up/down arrow key:

Set-PSReadlineKeyHandler -Key UpArrow -Function HistorySearchBackward
Set-PSReadlineKeyHandler -Key DownArrow -Function HistorySearchForward
Lastly, to enable bash style completion:

Set-PSReadlineKeyHandler -Key Tab -Function Complete

History Command in PowerShell

To add the a command to a new or existing profile
On the computer that hosts the data warehouse management server,

click Start, point to Programs, point to Windows PowerShell 1.0, right-click Windows PowerShell, and then click Run as administrator.
At the Windows PowerShell prompt, type the following command, and then press ENTER:
>>Test-path $profile
If the results of the previous command are false, go to step 4. If the results are true, go to step 5.
Type the following command, and then press ENTER.
New-item –type file –force $profile
Type the following command, and then press ENTER.
Notepad $profile

Add the following Content into profile file.

===================

Set-Location C:\
$a = Get-Date
“Date: ” + $a.ToShortDateString()
“Time: ” + $a.ToShortTimeString()
$MaximumHistoryCount = 1KB

if (!(Test-Path ~\PowerShell -PathType Container))
{ New-Item ~\PowerShell -ItemType Directory
}

function bye
{ Get-History -Count 1KB |Export-CSV ~\PowerShell\history.csv
exit
}

if (Test-path ~\PowerShell\History.csv)
{ Import-CSV ~\PowerShell\History.csv |Add-History
}

====================

 

PS> Get-History |Get-Member -MemberType Property
TypeName: Microsoft.PowerShell.Commands.HistoryInfo

Name MemberType Definition
---- ---------- ----------
CommandLine Property System.String CommandLine {get;}
EndExecutionTime Property System.DateTime EndExecutionTime {get;}
ExecutionStatus Property System.Management.Automation.Runspaces.Pip...
Id Property System.Int64 Id {get;}
StartExecutionTime Property System.DateTime StartExecutionTime {get;}

This means that you can find out when something was executed (e.g. which session it happened in) using the following command:

PS> ghy |ft id,endexecutiontime,commandline -auto

Id EndExecutionTime CommandLine
-- ---------------- -----------
612 6/29/2006 5:39:34 AM gcm export-csv |fl *
613 6/30/2006 6:51:16 PM ipconfig
614 6/30/2006 8:51:38 PM cd C:\kits