MariaDB: Installation, Optimization and Tuning

MariaDB is forked from MySQL, MariaDB has been known to be a drop-in replacement that brings enhancements and performance optimizations over MySQL. This tutorial will show you how to install, optimize and tune on your server-side.

Step 1: Download/Install MariaDB

This is straightforward, if you don’t have installed yet simply run the following. If you already have installed then good, move to the next step directly.

wget https://raw.githubusercontent.com/sayem314/MariaDB-Installer-for-Linux/master/install-mariadb.sh && bash install-mariadb.sh && rm install-mariadb.sh

On Debian/Ubuntu it would prompt for entering a root password. I would recommend choosing a strong/complex password.

Step 2: Securing MariaDB

Finish the installation by running mysql_secure_installation script to address several security concerns in a default MariaDB installation:

/usr/bin/mysql_secure_installation

In order to log into MariaDB to secure it, we’ll need the current password for the root user. If you’ve just installed MariaDB, and you haven’t set the root password yet, then the default password will be blank, so you should just press enter to continue.

Enter current password for root (enter for none):

OK, successfully used password, moving on…

Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation.

Set root password? [Y/n] y

New password: MyPassword

Re-enter new password: MyPassword

Password updated successfully!

Reloading privilege tables..

… Success!

By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove the anonymous login before moving into a production environment.

Remove anonymous users? [Y/n] y

… Success!

Normally, root should only be allowed to connect from ‘localhost’. This ensures that someone cannot guess the root password over the network.

Disallow root login remotely? [Y/n] y

… Success!

By default, MariaDB comes with a database named ‘test’ that anyone can access. This is intended for testing only and should be removed before moving into a production environment.

Remove test database and access to it? [Y/n] y

– Dropping test database…

… Success!

– Removing privileges on test database…

… Success!

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.

Reload privilege tables now? [Y/n] y

… Success!

Cleaning up…

All done! If you’ve completed all of the above steps, your MariaDB installation should now be secure.

Thanks for using MariaDB!

Now, you have to restart MariaDB.

service mysql restart

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!

That’s it. To log in to MariaDB as the root user:

mysql -u root -p

When prompted, enter the root password you assigned when the mysql_secure_installation script was run.

Step 3: Tuning/Optimization

Tuning/Optimization are about utilizing as many resources as possible, not reducing resource utilization. No one buys a Ferrari and modifies it to go slower! A well-tuned MySQL server can perform 2-3x better than a poorly tuned MySQL server. Optimizing MySQL/MariaDB is very important to get right the first time. There are really only few settings that need to be tuned/changed. This post will walk you through the steps for determining if MySQL/MariaDB needs to be tuned, and if it does how to tune the settings.

This is how MySQL/MariaDB works on server-side:

Server A: Lots of CPU power and good storage system.

E5-2650v3

8GB RAM

SATA RAID 10 with 4 disks

10GB of MySQL data

Server B: Decent CPU, not a ton of cores, lots of RAM, basic storage system.

E3-1270v3

16GB RAM

SATA RAID 1 with 2 disks

10GB of MySQL data

The question is which server will perform better? You might think that because the E5 box has a lot of cores, costs a lot of money and has RAID 10 it’s better. However, if you run some Sysbench OLTP read tests on these boxes Server B will more than likely to crush server A. Why? RAM, that’s why.

Here is what happens if I send a SELECT query to the server A:

Step 1) Server gets my request, goes to find my data, there’s a 4/10 chance that it’s in RAM. Just so happens my data is not in RAM.

Step 2) Server sends my disk a request for my data, CPU sends the request really quickly, and then waits for the array to return my data.

Step 3) My disks took their time getting my data, let’s say they took 20ms (really long).

Step 4) My CPU fiddles it’s thumbs waiting on my data to be returned from the disks (into RAM). And finally gets the results and sends to me.

Here is what happens if I send a SELECT query to the server B:

Step 1) Server gets my request, goes to find my data, 100% chance it’s in RAM. Great!

Step 2) It takes less than 1ms for the RAM to find my data and return it to the CPU

Step 3) CPU sends the data to me.

Basically the server B could theoretically serve more than 10 requests in the amount of time the server A takes to return one request. There are four main areas to focus on in terms of server performance, RAM, CPU, DISK and NETWORK. RAM is the most important factor for MySQL performance. The fastest CPU will not help to speed up your database if it does not have correct amount of RAM. There has always been a severe bottleneck between the CPU and Harddrive, the solution to that is RAM. Reading data out of RAM is significantly faster than reading data from an SATA harddrive, or even an SSD. Because of this we want to try and fit as much data into RAM as possible to speed up response times.

CPU performance is ONLY a factor if you have very low IO WAIT and you are correctly utilizing RAM. If you notice that your CPU is constantly around 90%-100% then you may want to look into a CPU upgrade. In general, MySQL/MariaDB prefers faster cores instead of more cores.

How much RAM do I need?

Let’s say you have one database that totals 6GB in size. You would want at least 6GB of RAM for this database. You also need RAM for the OS and things like that so 8GB of RAM should be sufficient.

If you have 30GB of databases then, you will want at least 30GB of RAM. Obviously this can sometimes be cost prohibitive, but the fact is that you will want to put as much of your data into RAM as possible. This is the most important thing to improve MySQL/MariaDB performance. If you don’t have sufficient RAM on your server MySQL/MariaDB will perform poorly regardless of the my.cnf settings you try to change.

By utilizing as much RAM as possible you are not only making response time faster, you are also freeing up the servers DISK so that it can attend to other things, like writing data. This also makes your CPU much more efficient since it does not have to wait for the slow disk to process its request for data.

You can use below script to get MySQL/MariaDB memory usage, or memory usage for any other running process. Create a file named mem.sh, paste in the contents below.

#!/bin/bash

ps -C $1 -O rss | awk ‘{ count ++; sum += $2 }; END {count –; print “Number of processes =”,count; print “Memory usage per process =”,sum/1024/count, “MB”; print “Total memory usage =”, sum/1024, “MB” ;};’

Run chmod +x mem.sh then run ./mem.sh mysqld to get MySQL/MariaDB memory usage. You can also use this for other apps like apache, php or anything else.

Changes you should make on MySQL/MariaDB configuration file described below. Your configuration file (my.cnf) is located on /etc/my.cnf or /etc/mysql/my.cnf. If you have 512MB server then I would recommend you to keep default settings.

innodb_buffer_pool_size

InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory. Knowing how the InnoDB buffer pool works, and taking advantage of it to keep frequently accessed data in memory, is an important aspect of MySQL/MariaDB tuning.

key_buffer_size

To minimize disk I/O, the MyISAM storage engine exploits a strategy that is used by many database management systems. It employs a cache mechanism to keep the most frequently accessed table blocks in memory.

innodb_log_file_size

For servers with SSDs you can raise this to 128MB, 256MB, or even 2GB. Keep in mind that the larger the logs are the longer a recovery might take. For spinning HDDs this is a problem since they are already slow, so recovery could take a long time. If you have SSDs, which is much faster, recovery would take only few more seconds or minutes if you set this really high.

## 160M for 1GB RAM, 450M for 2GB RAM 2G for 4GB RAM and 5G for 8GB RAM

innodb_buffer_pool_size = 64M

## 96M for 2GB RAM, 256M for 4GB RAM and 768M for 8GB RAM

key_buffer = 64M

## Changing this setting requires you to stop MySQL

innodb_log_file_size = 128M

If you have 256MB VPS then, you can lower default value slightly, this might decrease performance, but it would keep your VPS stable. I would not recommend you to install MariaDB on a system with less than 256MB RAM. You can use Percona Tools to get an optimized configuration files for your server.

How to remove CryptoPHP malware – Scan Now

What is CryptoPHP?
CryptoPHP is a threat that uses backdoored Joomla, WordPress and Drupal themes and plug-ins to compromise webservers on a large scale. By publishing pirated themes and plug-ins free for anyone to use instead of having to pay for them, the CryptoPHP actor is social engineering site administrators into installing the included backdoor on their server.

This malware can be controled via a remote server or email. This is a well written piece of code, it can have ,

Auto integrate into most of the CMS like joomla, wordpress , drupal ,etc,.
It is encrypted key based communication between the affected server and control server
Backup and failover mechanisam incase of shut down
Remote manual management , auto update ,etc,.
Thousands of servers and websites affected by this malware. Our clients servers with proactive management are already scanned and protected from this threat . It looks like the inspection limit is increasing.

If you have some shell experience , please use the following methods for identifying the malware
1) Quick check for social*.png files ,

find /home/ -type f -iname "social*.png" -exec grep -E -o 'php.{0,80}' {} \; -print

if you see any files from the above result , then you must delete those files immediately,

2) Check all png file ,

find /home -type f -iname '*.png' -print0 | xargs -0 file | grep "PHP script" > /root/cryptoinfected.txt

Now check all the files listed in /root/cryptoinfected.txt and remove it
3) Check all other files,

You must need to check all other files too , because it is not only infected by png fines and jpeg files,

4) Use clamav or maldetect

You may please update your clamav database and maldetect database . After that run a scan , this will detect the mallware

freshclam
maldetect -U

EDIT : Further investigation found that this malware seems to be attached via email attachments too, so you may need to scan the server email accounts too.

By syslint.com

How to install odoo a.k.a openerp

What is Odoo ?

Odoo is the fastest evolving business software in the world. Odoo has a complete suite of business applications covering all business needs, from Website/Ecommerce down to manufacturing, inventory and accounting, all seamlessly integrated. It is the first time ever a software editor managed to reach such a functional coverage.

System Requirement ?

An Ordinery server with Ubuntu 14.04 LTS installed. You can install it on any platform, but here the documentations are based on the Ubuntu/debian server

Step 1: Create the Odoo user that will own and run the application

# sudo adduser --system --home=/home/odoo --group odoo

Step 2 : Install postgresql database .

# sudo apt-get install postgresql

Step 3 : Create a database user with password

You must remember the password that you are giving

# sudo su - postgres
#createuser --createdb --username postgres --no-createrole --no-superuser --pwprompt odoo
Enter password for new role: ********
Enter it again: ********


# exit

Step 4. Install the necessary Python libraries for the server

# sudo apt-get install python-dateutil python-decorator python-docutils python-feedparser \
python-gdata python-gevent python-imaging python-jinja2 python-ldap python-libxslt1 python-lxml \
python-mako python-mock python-openid python-passlib python-psutil python-psycopg2 python-pybabel \
python-pychart python-pydot python-pyparsing python-pypdf python-reportlab python-requests \
python-simplejson python-tz python-unittest2 python-vatnumber python-vobject python-werkzeug \
python-xlwt python-yaml

Now install wkhtmltox

# sudo wget http://jaist.dl.sourceforge.net/project/wkhtmltopdf/0.12.1/wkhtmltox-0.12.1_linux-trusty-amd64.deb
# sudo dpkg -i wkhtmltox-0.12.1_linux-trusty-amd64.deb
# ln -s /usr/local/bin/wkhtmltopdf /usr/bin/wkhtmltopdf
# ln -s  /usr/local/bin/wkhtmltoimage /usr/bin/wkhtmltoimage

Step 5 : Install git client

# sudo apt-get install git

Step 6 : Install Odoo server

# sudo su - odoo -s /bin/bash
# git clone https://www.github.com/odoo/odoo --depth 1 --branch 8.0 --single-branch .
# exit 

Step 7 : Configuring the  Odoo / OpenERP application

Now you may need to setup the odoo-server.conf and the startup script as follows,

#sudo cp /home/odoo/debian/openerp-server.conf /etc/odoo-server.conf
#sudo chown odoo: /etc/odoo-server.conf
#sudo chmod 640 /etc/odoo-server.conf

Now edit the file /etc/odoo-server.conf and modify or add the lines as follows,

db_password = NEWPASSWORD   ( Change it to the password that you used on step 3 )
addons_path = /home/odoo/addons
logfile = /var/log/odoo/odoo-server.log

Now create the log folder and set permissions

# mkdir -pv /var/log/odoo/
# touch /var/log/odoo/odoo-server.log
# chown -R odoo: /var/log/odoo/

Step 8 : Installing the init scripts

You can download an init script from http://files.syslint.com/odoo/odoo-server.txt

# wget  http://files.syslint.com/odoo/odoo-server.txt
# mv odoo-server.txt /etc/init.d/odoo-server
# chmod 750 /etc/init.d/odoo-server
# chown root:root /etc/init.d/odoo-server

Step 9. Testing the server

Start the server

# /etc/init.d/odoo-server start

You check the log file,

# tailf /var/log/odoo/odoo-server.log

Now you can login to the Odoo server  from the following link,

# http://IP_or_domain.com:8069

You will see a database initialization wizard . You need to give the master password , by default it will be “admin” . I recommend to change the password to a complex one.

Step 10 : Add the init scripts to the startup scripts

You may please add it as follows,

# sudo update-rc.d odoo-server defaults

How to Install node.js on a cPanel Server

nstallation of node.js is  not yet officially supported by cPanel.However It’s still a feature request .

(Discussion at Official cPanel Forum too )

What is Node.Js?

Node.js is an application development software written in and for javascript for real-time event-driven applications,more at http://en.wikipedia.org/wiki/Node.js

The Installation Procedure:

Installing node.js on a cPanel VPS  doesn’t involve any complex tasks.  Prior beginning the installation make sure following requirements are met:

GCC 4.2 or newer
Python 2.6 or 2.7
GNU Make 3.81 or newer

Run and Update for any software in the above list (If not up to date).

Initially download latest version from nodejs.org .

wget http://nodejs.org/dist/v0.11.9/node-v0.11.9.tar.gz

Next, extract the node.js tarball and install it:

tar -xzvf  node-v0.11.9.tar.gz
cd node-v0.11.9
./configure
make && make install

The installation will take a while to complete . Now upon completion we could test it working.Check the version

[root@sh csf]# node -v
v0.11.9

[root@sh csf]# which node
/usr/local/bin/node
[root@sh csf]# /usr/local/bin/node –help

Usage: node [options] [ -e script | script.js ] [arguments]
node debug script.js [arguments]

Options:
-v, –version print node’s version
-e, –eval script evaluate script
-p, –print evaluate script and print result
-i, –interactive always enter the REPL even if stdin
does not appear to be a terminal
–no-deprecation silence deprecation warnings
–trace-deprecation show stack traces on deprecations
–v8-options print v8 command line options
–max-stack-size=val set max v8 stack size (bytes)

Environment variables:
NODE_PATH ‘:’-separated list of directories
prefixed to the module search path.
NODE_MODULE_CONTEXTS Set to 1 to load modules in their own
global contexts.
NODE_DISABLE_COLORS Set to 1 to disable colors in the REPL

Documentation can be found at http://nodejs.org/

Testing the  Installation:

Just scroll to  cd /usr/local/cpanel/htdocs

vi server.js

var http = require(“http”);
http.createServer(function(request, response) {
response.writeHead(200, {“Content-Type”: “text/plain”});
response.write(“Hello Test”);
response.end();
}).listen(8080);

save and quit

Open the port 8080 in your firewall via csf conf .

vi /etc/csf/csf.conf

TCP IN, OUT ===>8080
save and quit

csf -r

(from the dir/usr/local/cpanel/htdocs execute below )
/usr/local/bin/node server.js

Now access http://ip.add.re.ss:8080

You will get a confirmation message in your browser that says “Hello World”.

Extra:
If you need to direct traffic for our domain to the node.js build, you could implement it using via the .htaccess file by following rules.

RewriteEngine on
RewriteCond %{HTTP_HOST} ^domain\.com$ [OR]
RewriteCond %{HTTP_HOST} ^www\.domain\.com$
RewriteRule ^(.*) “http\:\/\/127\.0\.0\.1\:8080\/$1” [P,L]

Your domain name replaces “domain” in the above example. Just visit your domain again!

The above installation is tested under latest cpanel .

[root@sh]# cat /usr/local/cpanel/version
11.44.1.19
[root@sh]#

Let us know whether any sort of change or modification  is needed for  above installation, suggestions are invited!

By syslint.com

Managing Multiple Hard Drives with cPanel

f you have a server with single standalone drives, you may have considered the possibility of adding additional hard drives to provide more disk capacity to your system. Luckily, this is very easy to set up and use with cPanel.

First things first, I’ll assume that you already have the hard drive physically installed in your system. This guide will show you how to partition, format, and configure cPanel to use an additional hard drive, in the simplest way possible.

1. Partitioning the Disk

We’ll assume that the additional disk is supplementary to a system that already has an existing drive and OS installation. Therefore, the new disk will usually only need to be one single partition, unless you have different planned uses for it. If this is the second disk in your server, it’s going to be named either /dev/sdb or /dev/hdb, depending on what kind of drive it is. The last letter in the drive name will depend on how many other disks you have in your server, so see this article for a simple explanation on Linux drive mappings.

To find the new disk you added, use fdisk -l, which will list the disks active on your server. The additional disk should show up in the order that it’s physically configured inside the server:

Disk /dev/sdb: 250.0 GB, 250000000000 bytes
255 heads, 63 sectors/track, 30394 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

If you notice, the already-partitioned volumes also list the partition info. For example, this is what the output for the primary hard drive in my test server looks like, which is already set up with two partitions on the primary drive:

Device Boot Start End Blocks Id System
/dev/sda1 * 1 29884 240043198+ 83 Linux
/dev/sda2 29885 30394 4096575 82 Linux swap / Solaris

The partition information is blank for the new disk, indicating that it has not been partitioned yet. If yours has already been partitioned and you want to keep that structure, you can skip this section and go straight to formatting.

From here, I want to create one partition which will be /home2 on this server, to provide additional capacity for users on this system. GNU parted is a simple command line utility to manage disk partitions, and I’ll use this to create a partition on my new hard drive:

parted /dev/sdb

Once in parted, type print free to show how much space is available to be partitioned:

Number  Start   End    Size   Type  File system  Flags
        0.00kB  250GB  250GB        Free Space

Since I’m only creating one partition, I use the parted mkpart command to specify the start and end space to occupy the whole disk:

(parted) mkpart
Partition type? primary/extended? primary
File system type? [ext2]? ext3
Start? 0kB
End? 250GB

Then when I type in print, I see my new partition listed:

Number Start End Size Type File system Flags
1 0.51kB 250GB 250GB primary

Now, if you’re creating multiple partitions on the disk, do the same thing, but modify the start and end parameters to sequentially create your partitions. In the above example, that partition would have a device name as /dev/sdb1, since it’s the partition numbered 1 on disk /dev/sdb. If I had a second and third partition, they would be named /dev/sdb2 and /dev/sdb3, respectively.

Type quit to close the parted session.

2. Formatting and Configuring the Disk

Once you’ve created the partitions, you can format them easily with mkfs.ext[2|3], using the device name as the argument:

• For ext2: mkfs.ext2 /dev/sdb1
• For ext3: mkfs.ext3 /dev/sdb1
• For ext4: mkfs.ext4 /dev/sdb1

Repeat this for all the partitions you created, which will be numbered started as /dev/<device><partition>. My example is using the first partition of the second SCSI hard drive, so the partition device name is /dev/sdb1 . You can also find the new partition names by running fdisk -l again to see the names of the partitions for the new disk.

I also prefer to set the reserved disk space for the root user, as by default this will be 5% of the total partition size. This means for my 250GB disk, about 12GB is being reserved, which is a bit excessive. I instead usually set this to 2500 blocks:

root@server [~]# tune2fs -r 2500 /dev/sdb1
tune2fs 1.39 (29-May-2006)
Setting reserved blocks count to 2500

TIP: You can also pass -m <%> to the mkfs.ext[2|3] commands to set the percentage for reserved disk space during formatting, which can of course be changed later with tune2fs

3. Labeling and Mounting the Disk

Now, you need to label the disk and add it to fstab. In my example, I want to label the disk as /home2, so I’d use thee2label command and pass the partition’s device name and my desired label as arguments:

e2label /dev/sdb1 /home2

This can be confirmed by typing e2label /dev/sdb1:

root@server [~]# e2label /dev/sdb1
/home2

In order to mount the partition, it needs a mount point, and to be added to the file system table. Since the partition will be mounted as /home2, I created a folder called /home2, and added the following to /etc/fstab:

LABEL=/home2 /home2 ext4 defaults,usrquota,noatime 0 0

The LABEL value would be set to the label you used in e2label.  Replace ‘ext4’ with the partition type you created in step 2. To find out more about the fstab file, see this article. Again, you need to repeat these steps for each partition you created.

After you do this, you can mount the new partitions normally:

mount /home2

4. Setting up cPanel

As far as setting up cPanel, there’s only one file you have to edit – /etc/wwwacct.conf.

• HOMEDIR : The location where all new user home folders will be created (/home by default)
• HOMEMATCH: Additional home directories that will also be used for new home directory creations – only takesone value, leaving blank disables.

So, if you want to specify the partition/folder that all users are set up on, edit the HOMEDIR value and leave HOMEMATCH blank. If you specify a value for HOMEMATCH, cPanel will pick the partition based on which one has the most free space available.

For example, if you specify “home” for HOMEMATCH, it will configure users in the following locations:

• /home
• /home* (/home2, /home3, etc)
• /anythingwith/home
• /usr/home

By thecpaneladmin.com