Regular expression examples

Remove style in html tag

$text = preg_replace(“/style=\”[^\”>]+\”/i”, ”, $text);

e.g.

<span style=”font-family: Georgia, serif;” style=”font-family: Georgia, ‘Times New Roman’, Times, serif; font-size: 16px; line-height: 26px;”>some text</span>

to

<span >some text</span>

Increase timeout on apache server on ubuntu 18

Sometimes we need to have more time for executing heavy script on apache server. In this case, it shows time out error like this.

504 Gateway Time-out

To increase limit, we need to check 2 things. Apache and PHP configuration.

Increase in PHP

Open the php.ini file and modify below.

vi /etc/php/7.0/apache2/php.ini<br>

Below is just example, change the value according to your needs.

max_execution_time = 120
max_input_time = 120
memory_limit = 256M

Increase in Apache

Open Conf file and add below.

cd /etc/apache2/sites-available/
vi yourfile.conf

Set timeout

TimeOut 600

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.

Installing Percona XtraDB Cluster on Ubuntu 18.04

Mysql DB is great, it’s free and has good performance. Also has good combination with Apache and PHP.

But we need to use multiple DB servers for High availability. And the each db clusters need to be synced and keep the same data for each server while it delivers or updates records.

Percona XtraDB could be great solution for it. I would like to present how to install percona XtraDB on 3 servers.

We need to have 3 DB servers with Ubuntu 18.04. The original documentation can be found on Percona website.

Open Ports

I set it up with 3 servers, with these IPs.

Percona1 - 10.0.21.1
Percona2 - 10.0.21.2
Percona3 - 10.0.21.3

Before starting, some of server ports need to be opened for communicate each other.

Open 3306, 4444, 4567, 4568 ports for each servers. And open the ports using below commands.

iptables -A INPUT -i eth0 -p tcp -m iprange --src-range 10.0.21.1-10.0.21.3 --dport 3306 -j ACCEPT
iptables -A INPUT -i eth0 -p tcp -m iprange --src-range 10.0.21.1-10.0.21.3 --dport 4444 -j ACCEPT
iptables -A INPUT -i eth0 -p tcp -m iprange --src-range 10.0.21.1-10.0.21.3 --dport 4567 -j ACCEPT
iptables -A INPUT -i eth0 -p tcp -m iprange --src-range 10.0.21.1-10.0.21.3 --dport 4568 -j ACCEPT

If you are using AWS, you will need to open the ports as well on security groups. Just like this.

Remove apparmor or Mysql

I strongly recommend do not install mysql before installing Percona DB. It will conflict and may not working properly.
Also remove apparmor before installing.

sudo apt-get remove apparmor

Install Percona XtraDB package

Using below command, install package. During installation, you will need to set up root password.

wget https://repo.percona.com/apt/percona-release_0.1-6.$(lsb_release -sc)_all.deb 
sudo dpkg -i percona-release_0.1-6.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-xtradb-cluster-full-57

Prepare same for all 3 servers. If you are using cloud service, like AWS, you may want to take snapshot and duplicate the Percona servers.

Login into all servers and stop mysql service.

sudo service mysql stop

Config mysql on master

I have prepared these 3 nodes.
Percona1 – 10.0.21.1
Percona2 – 10.0.21.2
Percona3 – 10.0.21.3

And config mysql on master server(10.0.21.1). All servers will be set up as master, but at the beginning, we need to designate one server for master and others could be synced into it.

vi /etc/mysql/my.cnf
[mysqld]

wsrep_provider=/usr/lib/libgalera_smm.so

wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://10.0.21.1,10.0.21.2,10.0.21.3

wsrep_node_name=pxc1
wsrep_node_address=10.0.21.1

wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:sstuser_password

pxc_strict_mode=ENFORCING

binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/percona-xtradb-cluster.conf.d/

Set all node with same configuration but you will need to change wsrep_cluster_address for all your nodes and configure below line.

wsrep_node_name=pxc1
wsrep_node_address=10.0.21.1

Also set your sstuser and password for this line:

wsrep_sst_auth=sstuser:sstuser_password

Create SST user

Login to each nodes and login to mysql. Then create SST users.

CREATE USER 'sstuser'@'%' IDENTIFIED BY 'sstuser_password';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'%';

CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'sstuser_password';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
FLUSH PRIVILEGES;

Open port from terminal

Double check if port is opened and it communicate each other.

iptables --append INPUT --in-interface eth0 --protocol tcp --match tcp --dport 3306 --source 10.0.21.0/24 --jump ACCEPT
iptables --append INPUT --in-interface eth0 --protocol tcp --match tcp --dport 4567 --source 10.0.21.0/24 --jump ACCEPT
iptables --append INPUT --in-interface eth0 --protocol tcp --match tcp --dport 4568 --source 10.0.21.0/24 --jump ACCEPT
iptables --append INPUT --in-interface eth0 --protocol tcp --match tcp --dport 4444 --source 10.0.21.0/24 --jump ACCEPT

Run first node

For the first node, this need to be executed.

/etc/init.d/mysql bootstrap-pxc

After that check status with mysql command below.

show status like 'wsrep%';
wsrep_local_state_comment should show as synced and wsrep_cluster_size will be 1

Run all other nodes

Run 2nd nodes using mysql command and check the status

/etc/init.d/mysql start
show status like 'wsrep%';
wsrep_cluster_size should be 2 and wsrep_local_state_comment should show as Synced

Run all other nodes with same way. wsrep_cluster_size should be same node number as all node servers at the end.

3D printed soldering iron holder

I recently bought 3D printer and I designed extra helping hand for soldering iron. 

I used tinkercad.com for design 3D modeling. 

Here is what I have designed. 

And printed this out using Anet A8 printer. 

And here are the parts printed out.

Assemble these together. 

It can hold solder rill and anything.

Like this.

It will be useful helper when I am making DIY projects.

Install HTTPS using Let’s Encrypt Certificates on multiple websites

Introduction

SSL certificates are used within server and client to encrypt the traffic. This gives extra security for users accessing the application. Let’s Encrypt is one of free certificates that easily installed on your web servers.
Here’s how to install multiple domains on single apache web server.

Step 1 – Configure vhost file.

We need to prepare apache vhost configuration for SSL.
Create new vhost file with different name.
For example, save to /etc/apache2/site-available/test.com-ssl.conf

<IfModule mod_ssl.c>
<VirtualHost *:443>
    ServerAdmin webmaster@test.com
    DocumentRoot /websites/com_test_www/www
    ServerName test.com
    ErrorLog ${APACHE_LOG_DIR}/error-test.log
    CustomLog ${APACHE_LOG_DIR}/access-test.log combined
    Include /etc/letsencrypt/options-ssl-apache.conf
    ServerAlias test.com
    ServerAlias www.test.com
    SSLCertificateFile /etc/letsencrypt/live/test.com-0001/fullchain.pem
    SSLCertificateKeyFile /etc/letsencrypt/live/test.com-0001/privkey.pem
</VirtualHost>
</IfModule>
Step 2 – Install  Let’s Encrypt
sudo add-apt-repository ppa:certbot/certbot

sudo apt-get update

sudo apt-get install python-certbot-apache
Step 3 – Configure the Certificates
sudo certbot --apache -d test.com -d www.test.com

Here’s how to verify after installed new certificate.

https://www.ssllabs.com/ssltest/analyze.html?d=example.com&latest

To generate multiple certificates

sudo certbot --apache -d test2.com -d www.test2.com
Step 4 – Auto renewal using crontab

The new certificate will last 3 month but it’s good to renew automatically using cron job.

sudo crontab -e
30 1 * * * /usr/bin/certbot renew --quiet
Step 5 – Redirect http to https

Check if your websites opens fine with https:// , let’s make redirect http:// to https:// using vhost.

Open vhost file used for http.
For example, vi /etc/apache2/site-available/test.com.conf
And put below line:

Redirect permanent / https://test.com

Below is example:

<VirtualHost *:80>
    ServerAdmin webmaster@test.com
    DocumentRoot /websites/com_test_www/www
    ServerName test.com
    Redirect permanent / https://test.com
    ErrorLog ${APACHE_LOG_DIR}/error-test.log
    CustomLog ${APACHE_LOG_DIR}/access-test.log combined
</VirtualHost>

Test if your websites redirect http:// to https://

You may need to delete cookies on your browser for testing correctly.

Conclusion

It’s good to check the official Let’s Encrypt blog time to time for important updates.

Change content text on multiple files using shell script

When I want to change text in several files, it’s hard to open each single file and replace.
So I made one shell script to do the job.
Here’s one example using sed command.

find /home/user/directory -name \*.* -exec sed -i “s/search_text/replace_text/g” {} \;

BTW, it’s hard to remember. Also if I want to change including special character, it’s more harder. like this one. <?php echo $text;?> 

I searched for easy way and found good command. rpl – replace string in files

It’s really easy to use. If you don’t have rpl installed, install it using brew.

$ brew install rpl
rpl "<?php echo $search_text;?>" "<?php echo $replace_text;?>" /home/user/directory

Amazing! I don’t need to remember complicate command line.
However, I want to change in multiple files at the same time, moreover I would like to make easier input without change core programming.

#!/bin/sh
echo "Please enter search: "
read search_variable
echo "Search: $search_variable"
echo "Please enter replace: "
read replace_variable
echo "Replace: $replace_variable"

This bash script will get user input, both find and replace text.

for file in `find . -type f`
do
    rpl "$search_variable" "$replace_variable" $file
done

And this will find all files and replace text. Here’s final code. Save to replace.sh file in good place.

#!/bin/sh
echo "Please enter search: "
read search_variable
echo "Search: $search_variable"
echo "Please enter replace: "
read replace_variable
echo "Replace: $replace_variable"


for file in `find . -type f`
do
    rpl "$search_variable" "$replace_variable" $file
done

One more, I would like to use this command from every where.

alias replace="~/home/shell/replace.sh"

Put this in the .bash_profile, so the comman is available from any where.

 

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"}]