Install HAProxy on Percona XtraDB custers

I have posted how to install Percona XtraDB cluster previously. And I want to show how to use the DB clusters from web server.

To use the multiple nodes from one or multiple web servers, it need load balancer. There’s several LB but HAProxy is one of most popular and easy to install.
Here’s documentation from Percona how to set up HAProxy on Percona XtraDB clusters.

Let’s say there are Percona DB nodes synced each other and one web server.

10.7.13.81 web
10.7.13.91 node1
10.7.13.92 node2

And I would like to make web server connected to 2 db nodes.

Config and install HAProxy on web server

Download and Install HAProxy on web server.

sudo add-apt-repository ppa:vbernat/haproxy-1.8
sudo apt-get update
sudo apt-get install haproxy

Then config HAProxy.

sudo vi /etc/haproxy/haproxy.cfg

Then add this below. We will use port 3307 and localhost to connect Mysql nodes. And HAProxy will routes the traffic to db nodes using port 3306.

frontend pxc-front
  bind *:3307
  mode tcp
  default_backend pxc-back

frontend stats-front
  bind *:80
  mode http
  default_backend stats-back

frontend pxc-onenode-front
  bind *:3306
  mode tcp
  default_backend pxc-onenode-back

backend pxc-back
  mode tcp
  balance leastconn
  option httpchk
  server node1 10.7.13.91:3306 check port 9200 inter 12000 rise 3 fall 3
  server node2 10.7.13.92:3306 check port 9200 inter 12000 rise 3 fall 3

backend stats-back
  mode http
  balance roundrobin
  stats uri /haproxy/stats
  stats auth pxcstats:secret

backend pxc-onenode-back
  mode tcp
  balance leastconn
  option httpchk
  server node1 10.7.13.91:3306 check port 9200 inter 12000 rise 3 fall 3
  server node2 10.7.13.92:3306 check port 9200 inter 12000 rise 3 fall 3 backup

Install clustercheck on nodes

Install Clustercheck on each db nodes. Clustercheck is checking mysql health and display the status on web port 80. So that HAProxy knows which node is live and available.

First create clustercheckuser on mysql.

GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!'
FLUSH PRIVILEGES;

Then download clustercheck from git repository and place into /usr/bin/clustercheck on node server.

git clone git@github.com:olafz/percona-clustercheck.git
mv  /root/clustercheck /usr/bin/clustercheck

The edit the downloaded file.

vi /usr/bin/clustercheck

Here’s important part, there’s typo in the programming where the mysql username and password recorded. Fix it like below, you can change the user name and password but it should be matched with the mysql user information created above. For me, this took an hour to find out this bug. No body reported this bug on the git repository although this is at least 3 years old.

MYSQL_USERNAME="${MYSQL_USERNAME:-clustercheckuser}"
MYSQL_PASSWORD="${MYSQL_PASSWORD:-clustercheckpassword!}"

Add clustercheck in mysqlchk

Configure mysqlchk file and designate where is clustercheck file located. (/usr/bin/clustercheck)

vi /etc/xinetd.d/mysqlchk
# default: on
# description: mysqlchk
service mysqlchk
{
        disable = no
        flags = REUSE
        socket_type = stream
        port = 9200
        wait = no
        user = nobody
        server = /usr/bin/clustercheck
        log_on_failure += USERID
        only_from = 0.0.0.0/0
        per_source = UNLIMITED
        
}

Install xinetd

xinetd is service where we can monitor using port 80. Add mysqlchk service in the xinetd.

vi /etc/services

Then searching for xinetd and add after below.

mysqlchk    9200/tcp    # MySQL check

We need to install xinetd if it is not installed.

sudo apt-get update -y
sudo apt-get install -y xinetd

Start xinetd using below command.

sudo service xinetd start

You can check health status of nodes from web browser, port 9200.
http://10.7.13.91:9200/
http://10.7.13.92:9200/

Make sure the message saying: Percona XtraDB Cluster Node is synced.
If it says Percona XtraDB Cluster Node is not synced. then check if the clusteruser login information matched with mysql user and credential on the file (/usr/bin/clustercheck)

You can also check through terminal.

curl http://10.7.13.91:9200/

Connectivity from web server

From web server, check if the connection to db node is working through port 3306.

mysql -uyourmysqluser -p -P 3306 -h 10.7.13.91 -e "show variables like 'wsrep_node_name';"

If there’s no problem, also check connection using port 3307, through HAProxy.

mysql -uyourmysqluser -p -P 3307 -h 127.0.0.1 -e "show variables like 'wsrep_node_name';"

If everything works fine, you will see below and now mysql is connected using host 127.0.0.1 and port 3307.

Install phpMyAdmin ubuntu via command line

phpMyAdmin is useful tool to check database content. And I would like to use it on my development server. It’s quite simple and straight forward steps.

You will need root privilege to install this and supposed to be mysql installed and configured already.

  1. First prepare apt-get up to date
    apt-get update && apt-get upgrade
  2. Install phpMyAdmin
    apt-get install phpmyadmin

    When you install and will see and option to choose a web server to choose. If you have apache2 installed, select the option. By default, it’s  blank and looks like chosen but actually need to press space key to select. Then press tab and click OK.

    phpmyadmin install choose apache2
    phpmyadmin install choose apache2

    Another option window is asking if I want to use dbconfig-common. If you already db configured, simply choose “No”.

    phpmyadmin install config

    It will ask password for phpmyadmin user if you choose dbconfig-common as Yes. Put desired password there.

  3. Now you can try access http://yourdomain.com/phpmyadmin

    phpmyadmin installed
    phpmyadmin installed

Use HipChat PHP API

Hipchat is good communication platform not just between people but also between machine and apps.

I am using HipChat to monitor deployment in Jenkins. There’s HipChat plugin for Jenkins.

Hipchat Jenkins plugin
Hipchat Jenkins plugin

And I would like to get this deployment result using API.

hipchat log
hipchat log

Here we go.

First we need to install composer if it is not installed on the server.

sudo apt-get update
sudo apt-get install curl php-cli php-mbstring git unzip
cd ~
curl -sS https://getcomposer.org/installer -o composer-setup.php

After get composer setup file, we can verify the installer. go to https://composer.github.io/pubkeys.html and get key.
Run this command after replace key.

php -r "if (hash_file('SHA384', 'composer-setup.php') === '544e09ee996cdf60ece3804abc52599c22b1f40f4323403c44d44fdfdd586475ca9813a858088ffbc1f233e9b180f061') { echo 'Installer verified'; } else { echo 'Installer corrupt'; unlink('composer-setup.php'); } echo PHP_EOL;"

You will see the message like “Installer verified”. Then turn this.

sudo php composer-setup.php --install-dir=/usr/local/bin --filename=composer

Next step is get HipChat PHP API and configure composer accordingly.

git clone https://github.com/vanilla/hipchat-api.git
curl -sS https://getcomposer.org/installer | php
php composer.phar install

When you git clone, the git repository has composer.json file and run curl -sS https://getcomposer.org/installer | php  will down load composer.phar file and all sub folders will be created after run php composer.phar install

Now we need to get API Token.  Login to HipChat and go to Profile > API access menu > create new token.

get hipchat api token
get hipchat api token

Finally, we can make example script.

<?php
// The library lives in the HipChat namespace.
use HipChat\v2\HipChatClient;

// Require composer's autoloader.
require_once 'vendor/autoload.php';
  
// Define the cli options.
$client = new HipChatClient();
$client->setAuth('{your api token put here}');

// Start making calls!
$options['max-results']=5;
$options['start-index']=0;
$message = $client->roomsAPI()->getHistory('{room number goes here}',$options);
print_r($message);

I got this chat history. Yay!

hipchat api result
hipchat api result

I put it on my Raspberry Pi LCD display to monitor quickly.

hipchat on my raspberry pi
hipchat on my raspberry pi

Real time user count from Google analytics API

I posted how to say hello to Google Analytics API.

Continuing the post, I would like to get Real-time user number using Google Analytics API.

1. Initialize google analytics API.

require '../composer/vendor/autoload.php';

function initializeAnalytics(){
  // Creates and returns the Analytics Reporting service object.

  // Use the developers console and download your service account
  // credentials in JSON format. Place them in this directory or
  // change the key file location if necessary.
  $KEY_FILE_LOCATION = __DIR__ . '/credentials.json';

  // Create and configure a new client object.
  $client = new Google_Client();
  $client->setApplicationName("Hello Analytics Reporting");
  $client->setAuthConfig($KEY_FILE_LOCATION);
  $client->setScopes(['https://www.googleapis.com/auth/analytics.readonly']);
  $analytics = new Google_Service_Analytics($client);

  return $analytics;
}

$analytics = initializeAnalytics();

2. Function for Real-time user number.

function get_realtime_active_user($analytics, $ga_internal_id){
  $optParams = array(
      'dimensions' => 'rt:medium');
  try {
    $results = $analytics->data_realtime->get(
        'ga:'.$ga_internal_id,
        'rt:activeUsers',
        $optParams);
    // Success. 
    $return = $results->totalsForAllResults['rt:activeUsers'];
    return $return;
  } catch (apiServiceException $e) {
    // Handle API service exceptions.
    $error = $e->getMessage();
  }
  
}

3. Get multiple site’s data, change $ga_id_array variable according to your GA account number.
You can get GA account number just like the below.

ga account number
ga account number copy from here

$data = array();
$ga_id_array = array('site1'=>'1234567','site2'=>'12345678','site3'=>'123456789');
foreach($ga_id_array as $name => $ga_id){
  $each_data = array();
  $each_data['name'] = $name;
  $each_data['num'] = get_realtime_active_user($analytics, $ga_id);
  $data[] = $each_data;
}
echo json_encode($data);

4. All together.

<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
  
require '../composer/vendor/autoload.php';

function initializeAnalytics(){
  // Creates and returns the Analytics Reporting service object.

  // Use the developers console and download your service account
  // credentials in JSON format. Place them in this directory or
  // change the key file location if necessary.
  $KEY_FILE_LOCATION = __DIR__ . '/credentials.json';

  // Create and configure a new client object.
  $client = new Google_Client();
  $client->setApplicationName("Hello Analytics Reporting");
  $client->setAuthConfig($KEY_FILE_LOCATION);
  $client->setScopes(['https://www.googleapis.com/auth/analytics.readonly']);
  $analytics = new Google_Service_Analytics($client);

  return $analytics;
}

$analytics = initializeAnalytics();

function get_realtime_active_user($analytics, $ga_internal_id){
  $optParams = array(
      'dimensions' => 'rt:medium');
  try {
    $results = $analytics->data_realtime->get(
        'ga:'.$ga_internal_id,
        'rt:activeUsers',
        $optParams);
    // Success. 
    $return = $results->totalsForAllResults['rt:activeUsers'];
    return $return;
  } catch (apiServiceException $e) {
    // Handle API service exceptions.
    $error = $e->getMessage();
  }
  
}

$data = array();
$ga_id_array = array('site1'=>'1234567','site2'=>'12345678','site3'=>'123456789');
foreach($ga_id_array as $name => $ga_id){
  $each_data = array();
  $each_data['name'] = $name;
  $each_data['num'] = get_realtime_active_user($analytics, $ga_id);
  $data[] = $each_data;
}
echo json_encode($data);

That’s all, this will return with JSON format like this.

[{"name":"site1","num":"722"},{"name":"site2","num":"100"},{"name":"site3","num":"20"}]

 

Get started with Google analytics API

Making something new is always challenging.
Google API is well made, but documentation is always confusing me.
So let’s say “Hello” to Google Analytics. It’s good start for this kind of complicate API work.

I have checked this Hello analytics page.

1. Download composer

$ curl -sS https://getcomposer.org/installer | php

2. creat composer.json

{
  "require": {
    "google/apiclient": "^2.0"
  }
}

3. Initialize composer with google analytics. This will create vendor folder with google folder inside

$ php composer.phar install

4. Get credential JSON file from Google credential page.
Go to Credentials > Create credentials > Service account key > Click key type as JSON
Download and save it in to proper folder.

5. Enable Google Analytics API
Go to Library on Google API page. And click Analytics API under Other popular APIs

6. Open Json credential file and copy client email and Add user profile in google analytics ( admin > user management > Add permissions for:)
e.g. some-username@my-api-123456.iam.gserviceaccount.com

7. Write this code and run it from browser.

<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
require '../composer/vendor/autoload.php';

function initializeAnalytics(){
  // Creates and returns the Analytics Reporting service object.
  // Use the developers console and download your service account
  // credentials in JSON format. Place them in this directory or
  // change the key file location if necessary.
  $KEY_FILE_LOCATION = __DIR__ . '/credentials.json';

  // Create and configure a new client object.
  $client = new Google_Client();
  $client->setApplicationName("Hello Analytics Reporting");
  $client->setAuthConfig($KEY_FILE_LOCATION);
  $client->setScopes(['https://www.googleapis.com/auth/analytics.readonly']);
  $analytics = new Google_Service_Analytics($client);

  return $analytics;
}

function getFirstProfileId($analytics) {
  // Get the user's first view (profile) ID.

  // Get the list of accounts for the authorized user.
  $accounts = $analytics->management_accounts->listManagementAccounts();

  if (count($accounts->getItems()) > 0) {
    $items = $accounts->getItems();
    $firstAccountId = $items[0]->getId();

    // Get the list of properties for the authorized user.
    $properties = $analytics->management_webproperties
        ->listManagementWebproperties($firstAccountId);

    if (count($properties->getItems()) > 0) {
      $items = $properties->getItems();
      $firstPropertyId = $items[0]->getId();

      // Get the list of views (profiles) for the authorized user.
      $profiles = $analytics->management_profiles
          ->listManagementProfiles($firstAccountId, $firstPropertyId);

      if (count($profiles->getItems()) > 0) {
        $items = $profiles->getItems();

        // Return the first view (profile) ID.
        return $items[0]->getId();

      } else {
        throw new Exception('No views (profiles) found for this user.');
      }
    } else {
      throw new Exception('No properties found for this user.');
    }
  } else {
    throw new Exception('No accounts found for this user.');
  }
}

function getResults($analytics, $profileId) {
  // Calls the Core Reporting API and queries for the number of sessions
  // for the last seven days.
   return $analytics->data_ga->get(
       'ga:' . $profileId,
       '7daysAgo',
       'today',
       'ga:sessions');
}

function printResults($results) {
  // Parses the response from the Core Reporting API and prints
  // the profile name and total sessions.
  if (count($results->getRows()) > 0) {

    // Get the profile name.
    $profileName = $results->getProfileInfo()->getProfileName();

    // Get the entry for the first entry in the first row.
    $rows = $results->getRows();
    $sessions = $rows[0][0];

    // Print the results.
    print "First view (profile) found: $profileName\n";
    print "Total sessions: $sessions\n";
  } else {
    print "No results found.\n";
  }
}

$analytics = initializeAnalytics();
$profile = getFirstProfileId($analytics);
$results = getResults($analytics, $profile);
printResults($results);

This looks easy but it need some effort to make it work.
Once you get Hello from analytics, next step is a lot easier.

 

Upgrade PHP5 to PHP7

I have older server to upgrade to PHP7.

PHP7 has better performance and cleaner library.
But some of functions are depricated like ereg_replace, ereg and also mysql is not supported anymore. Instead, preg_replace, preg_match and mysqli are available.

So some of php programming may not work and need to be updated.

To upgrade, use below commands:

sudo add-apt-repository ppa:ondrej/php
sudo apt-get install php7.0 libapache2-mod-php7.0
sudo a2dismod php5
sudo a2enmod php7.0
sudo apachectl restart

It’s quite simple, but I had to change those depricated functions.
Especially mysqli’s usage is slightly different than mysql.

To connect db:

$this->lid = mysqli_connect(DB_HOST,DB_USER,DB_PWD,DB_NAME);

Select DB:

mysqli_select_db($this->lid,DB_NAME)

Query:

$this->qid = mysqli_query($this->lid, $q);

Fetch Array:

$this->record = mysqli_fetch_array($this->qid);

Upgrading to PHP7 wasn’t night mare, it was quite simple and easy.