Author: ServerOk – ServerOK

Apache Show Real IP Address when using CloudFlare

When using Apache web server behind cloudflare, apache logs show cloudflare IP address instead of real visitor IP address. To show actual visitor IP address, you need to install mod_cloudflare apache module.

Before you can install the module, you need to install following requirments.

On Debian/Ubuntu server,

apt-get install apache2-dev libtool git

1	apt-get install apache2-dev libtool git

Now install mod_cloudflare with

cd /usr/local/src
git clone https://github.com/cloudflare/mod_cloudflare.git; cd mod_cloudflare
apxs -a -i -c mod_cloudflare.c

cd /usr/local/src

git clone https://github.com/cloudflare/mod_cloudflare.git; cd mod_cloudflare

apxs -a -i -c mod_cloudflare.c

Restart apache web server with

service apache2 restart

1	service apache2 restart

Verify mod_cloudflare apache module is loaded with

apachectl -M | grep cloudflare

1	apachectl -M \| grep cloudflare

MySQL ERROR 1193 Unknown system variable GTID_PURGED

When restoring MySQL backup taken on Amazon RDS, i get error

root@PRD-50ml:~# mysql sok_db2 < db.sql
ERROR 1193 (HY000) at line 24: Unknown system variable 'GTID_PURGED'
root@PRD-50ml:~#

root@PRD-50ml:~# mysql sok_db2 < db.sql

ERROR 1193 (HY000) at line 24: Unknown system variable 'GTID_PURGED'

root@PRD-50ml:~#

To fix this, open db.sql in text editor, remove the line

SET @@GLOBAL.GTID_PURGED='';

1	SET @@GLOBAL.GTID_PURGED='';

Another solution is take a new MySQL backup with –set-gtid-purged=OFF option and restore it.

mysqldump -u DB_USER -p --set-gtid-purged=OFF --triggers --routines --events DB_NAME > DB_NAME.sql

1	mysqldump -u DB_USER -p --set-gtid-purged=OFF --triggers --routines --events DB_NAME > DB_NAME.sql

MySQL create database

To create a database, you can use

create database DB_NAME;

1	create database DB_NAME;

Example

To create a database with specific charset use

create database DB_NAME character set utf8mb4 collate utf8mb4_bin

1	create database DB_NAME character set utf8mb4 collate utf8mb4_bin

You can use whatever character set you wish instead of utf8mb4.

Example

create database serverok_db2 character set utf8mb4 collate utf8mb4_unicode_ci;

1	create database serverok_db2 character set utf8mb4 collate utf8mb4_unicode_ci;

mysql create database

To delete a database, use

drop database DB_NAME;

1	drop database DB_NAME;

Install PHP 7 on CentOS VestaCP

To install PHP 7. you need to first enable epel and remi repo.

yum install -y epel-release
rpm -Uvh http://rpms.remirepo.net/enterprise/remi-release-7.rpm

1 2	yum install -y epel-release rpm -Uvh http://rpms.remirepo.net/enterprise/remi-release-7.rpm

Remove existing PHP

yum -y remove php

1	yum -y remove php

Install PHP 7.3

yum --enablerepo=remi-php73 install php73-php php73-php-mbstring php73-php-mysqlnd php73-php-gd php73-php-fpm php73-php-intl php73-php-cli php73-php-xml php73-php-opcache php73-php-pdo php73-php-gmp php73-php-process php73-php-pecl-imagick php73-php-devel

1	yum --enablerepo=remi-php73 install php73-php php73-php-mbstring php73-php-mysqlnd php73-php-gd php73-php-fpm php73-php-intl php73-php-cli php73-php-xml php73-php-opcache php73-php-pdo php73-php-gmp php73-php-process php73-php-pecl-imagick php73-php-devel

start php-fpm

service php-fpm stop
service php73-php-fpm start

1 2	service php-fpm stop service php73-php-fpm start

Set php 7.3 as default PHP for cli

rm /usr/bin/php
ln -s /usr/bin/php73 /usr/bin/php

1 2	rm /usr/bin/php ln -s /usr/bin/php73 /usr/bin/php

Restart apache

service httpd restart

1	service httpd restart

Install ImunifyAV on Cpanel Server

ImunifyAV is Free version of malware scanner for web sites. To install ImunifyAV on Cpanel or DirectAdmin server, run

wget https://repo.imunify360.cloudlinux.com/defence360/imav-deploy.sh
bash imav-deploy.sh

1 2	wget https://repo.imunify360.cloudlinux.com/defence360/imav-deploy.sh bash imav-deploy.sh

MySQL root password in VestaCP

In VestaCP server, MySQL root password is stored in file /usr/local/vesta/conf/mysql.conf, to find MySQL root password, run

cat /usr/local/vesta/conf/mysql.conf

1	cat /usr/local/vesta/conf/mysql.conf

Configure Nginx Reverse Proxy behind Cloudflare

On reverse proxy server, lets install some basic utilities.

apt install -y wget
wget https://raw.githubusercontent.com/serverok/server-setup/master/debian/1-basic-tools.sh
bash 1-basic-tools.sh

apt install -y wget

wget https://raw.githubusercontent.com/serverok/server-setup/master/debian/1-basic-tools.sh

bash 1-basic-tools.sh

Install nginx

apt install nginx -y

1	apt install nginx -y

Now create a config file

vi /etc/nginx/sites-enabled/proxy.conf

1	vi /etc/nginx/sites-enabled/proxy.conf

Add following to the file and save

server {
    server_name  YOUR-DOMAIN.COM www.YOUR-DOMAIN.COM;
    listen *:80;
    client_max_body_size 100M;
    proxy_read_timeout 600s;
    proxy_buffers 16 4k;
    proxy_buffer_size 2k;
    location / {
        proxy_set_header X-Real-IP  $remote_addr;
        proxy_set_header X-Forwarded-For $remote_addr;
        proxy_set_header Host $host;
        proxy_pass http://BACKEND-SERVER-IP:80;
    }
}

server {

    server_name  YOUR-DOMAIN.COM www.YOUR-DOMAIN.COM;

    set_real_ip_from 103.21.244.0/22;
    set_real_ip_from 103.22.200.0/22;
    set_real_ip_from 103.31.4.0/22;
    set_real_ip_from 104.16.0.0/12;
    set_real_ip_from 108.162.192.0/18;
    set_real_ip_from 131.0.72.0/22;
    set_real_ip_from 141.101.64.0/18;
    set_real_ip_from 162.158.0.0/15;
    set_real_ip_from 172.64.0.0/13;
    set_real_ip_from 173.245.48.0/20;
    set_real_ip_from 188.114.96.0/20;
    set_real_ip_from 190.93.240.0/20;
    set_real_ip_from 197.234.240.0/22;
    set_real_ip_from 198.41.128.0/17;
    set_real_ip_from 2400:cb00::/32;
    set_real_ip_from 2606:4700::/32;
    set_real_ip_from 2803:f800::/32;
    set_real_ip_from 2405:b500::/32;
    set_real_ip_from 2405:8100::/32;
    set_real_ip_from 2c0f:f248::/32;
    set_real_ip_from 2a06:98c0::/29;

    # use any of the following two
    real_ip_header CF-Connecting-IP;
    #real_ip_header X-Forwarded-For;


    listen 443 ssl http2;
    ssl on;
    ssl_certificate /etc/nginx/ssl/YOUR-DOMAIN.COM.crt;
    ssl_certificate_key /etc/nginx/ssl/YOUR-DOMAIN.COM.key;

    client_max_body_size 100M;
    proxy_read_timeout 600s;
    proxy_buffers 16 4k;
    proxy_buffer_size 2k;
    location / {
        proxy_set_header X-Real-IP  $remote_addr;
        proxy_set_header X-Forwarded-For $remote_addr;
        proxy_set_header Host $host;
        proxy_pass https://BACKEND-SERVER-IP:443;
    }
}

server {

server_name YOUR-DOMAIN.COM www.YOUR-DOMAIN.COM;

listen *:80;

client_max_body_size 100M;

proxy_read_timeout 600s;

proxy_buffers 16 4k;

proxy_buffer_size 2k;

location / {

proxy_set_header X-Real-IP $remote_addr;

proxy_set_header X-Forwarded-For $remote_addr;

proxy_set_header Host $host;

proxy_pass http://BACKEND-SERVER-IP:80;

}

server {

server_name YOUR-DOMAIN.COM www.YOUR-DOMAIN.COM;

set_real_ip_from 103.21.244.0/22;

set_real_ip_from 103.22.200.0/22;

set_real_ip_from 103.31.4.0/22;

set_real_ip_from 104.16.0.0/12;

set_real_ip_from 108.162.192.0/18;

set_real_ip_from 131.0.72.0/22;

set_real_ip_from 141.101.64.0/18;

set_real_ip_from 162.158.0.0/15;

set_real_ip_from 172.64.0.0/13;

set_real_ip_from 173.245.48.0/20;

set_real_ip_from 188.114.96.0/20;

set_real_ip_from 190.93.240.0/20;

set_real_ip_from 197.234.240.0/22;

set_real_ip_from 198.41.128.0/17;

set_real_ip_from 2400:cb00::/32;

set_real_ip_from 2606:4700::/32;

set_real_ip_from 2803:f800::/32;

set_real_ip_from 2405:b500::/32;

set_real_ip_from 2405:8100::/32;

set_real_ip_from 2c0f:f248::/32;

set_real_ip_from 2a06:98c0::/29;

# use any of the following two

real_ip_header CF-Connecting-IP;

#real_ip_header X-Forwarded-For;

listen 443 ssl http2;

ssl on;

ssl_certificate /etc/nginx/ssl/YOUR-DOMAIN.COM.crt;

ssl_certificate_key /etc/nginx/ssl/YOUR-DOMAIN.COM.key;

client_max_body_size 100M;

proxy_read_timeout 600s;

proxy_buffers 16 4k;

proxy_buffer_size 2k;

location / {

proxy_set_header X-Real-IP $remote_addr;

proxy_set_header X-Forwarded-For $remote_addr;

proxy_set_header Host $host;

proxy_pass https://BACKEND-SERVER-IP:443;

}

In above replce following

YOUR-DOMAIN.COM = replace with your actual domain name
BACKEND-SERVER-IP = replace with IP of the web server where your web site is running.

1 2	YOUR-DOMAIN.COM = replace with your actual domain name BACKEND-SERVER-IP = replace with IP of the web server where your web site is running.

Next create a self signed SSL certificate for the web site

mkdir /etc/nginx/ssl
cd /etc/nginx/ssl
openssl genrsa -out YOUR-DOMAIN.COM.key 2048
openssl req -new -x509 -key YOUR-DOMAIN.COM.key -out YOUR-DOMAIN.COM.crt -days 3650 -subj /CN="YOUR-DOMAIN.COM www.YOUR-DOMAIN.COM"

mkdir /etc/nginx/ssl

cd /etc/nginx/ssl

openssl genrsa -out YOUR-DOMAIN.COM.key 2048

openssl req -new -x509 -key YOUR-DOMAIN.COM.key -out YOUR-DOMAIN.COM.crt -days 3650 -subj /CN="YOUR-DOMAIN.COM www.YOUR-DOMAIN.COM"

Restart nginx

nginx -s reload

1	nginx -s reload

At this stage, you can login to cloudflare, point IP of the web site to reverse proxy server IP address.

Show real IP address

When running a site behind reverse proxy, by default, web server shows IP of the revese proxy server instead of real visitor IP. To fix this, you need to configure remoteip module.

On Cpanel server, edit file

vi /etc/apache2/conf.modules.d/370_mod_remoteip.conf

1	vi /etc/apache2/conf.modules.d/370_mod_remoteip.conf

Find

RemoteIPTrustedProxy 127.0.0.1

1	RemoteIPTrustedProxy 127.0.0.1

Add your proxy server IP after.

Example

root@lh34134 [~]# cat /etc/apache2/conf.modules.d/370_mod_remoteip.conf
# Enable mod_remoteip
LoadModule remoteip_module modules/mod_remoteip.so

RemoteIPHeader X-Forwarded-For
RemoteIPTrustedProxy 127.0.0.1 94.242.55.132 104.238.213.205 185.193.126.66 207.246.98.251

root@lh34134 [~]#

root@lh34134 [~]# cat /etc/apache2/conf.modules.d/370_mod_remoteip.conf

# Enable mod_remoteip

LoadModule remoteip_module modules/mod_remoteip.so

RemoteIPHeader X-Forwarded-For

RemoteIPTrustedProxy 127.0.0.1 94.242.55.132 104.238.213.205 185.193.126.66 207.246.98.251

root@lh34134 [~]#

VestaCP Update

To update VestaCP server, run

v-list-sys-vesta-updates
v-update-sys-vesta-all

1 2	v-list-sys-vesta-updates v-update-sys-vesta-all

Example

[root@backendz ~]# v-list-sys-vesta-updates
PKG          VER    REL  ARCH    UPDT  DATE
---          ---    ---  ----    ----  ----
vesta        0.9.8  26   x86_64  yes   2019-09-30
vesta-php    0.9.8  26   x86_64  yes   2019-09-30
vesta-nginx  0.9.8  26   x86_64  yes   2019-09-30
[root@backendz ~]# v-update-sys-vesta-all
[root@backendz ~]#

[root@backendz ~]# v-list-sys-vesta-updates

PKG VER REL ARCH UPDT DATE

--- --- --- ---- ---- ----

vesta 0.9.8 26 x86_64 yes 2019-09-30

vesta-php 0.9.8 26 x86_64 yes 2019-09-30

vesta-nginx 0.9.8 26 x86_64 yes 2019-09-30

[root@backendz ~]# v-update-sys-vesta-all

[root@backendz ~]#

Redirect site to HTTPS excluding a folder

On a web site, customer need to redirect all pages to HTTPS, but want to keep files in one of the folder on HTTP.

For this, i used following in .htaccess file.

RewriteEngine On
RewriteCond %{HTTPS} off
RewriteCond %{REQUEST_URI} !^/auth/.*
RewriteRule ^ https://%{HTTP_HOST}%{REQUEST_URI} [L,R=301]

RewriteEngine On

RewriteCond %{HTTPS} off

RewriteCond %{REQUEST_URI} !^/auth/.*

RewriteRule ^ https://%{HTTP_HOST}%{REQUEST_URI} [L,R=301]

Here any url like yourdomain.extn/auth/ will not get redirected to HTTPS.

See Redirect

rpmdb DB_RUNRECOVERY: Fatal error, run database recovery

When running yum update on a CentOS server, i get following error.

[root@ip-172-30-0-39 ~]# yum update
error: rpmdb: BDB0113 Thread/process 12797/46913889995840 failed: BDB1507 Thread died in Berkeley DB library
error: db5 error(-30973) from dbenv->failchk: BDB0087 DB_RUNRECOVERY: Fatal error, run database recovery
error: cannot open Packages index using db5 -  (-30973)
error: cannot open Packages database in /var/lib/rpm
CRITICAL:yum.main:

Error: rpmdb open failed
[root@ip-172-30-0-39 ~]#

[root@ip-172-30-0-39 ~]# yum update

error: rpmdb: BDB0113 Thread/process 12797/46913889995840 failed: BDB1507 Thread died in Berkeley DB library

error: db5 error(-30973) from dbenv->failchk: BDB0087 DB_RUNRECOVERY: Fatal error, run database recovery

error: cannot open Packages index using db5 - (-30973)

error: cannot open Packages database in /var/lib/rpm

CRITICAL:yum.main:

Error: rpmdb open failed

[root@ip-172-30-0-39 ~]#

To fix it, run

rm -f /var/lib/rpm/__db.00*

1	rm -f /var/lib/rpm/__db.00*