5 Key Steps to Ensuring Database Security

Databases often contain extremely sensitive information that must be protected from security vulnerabilities and exploits. All organizations need to work on a continual basis to identify and remediate those vulnerabilities using a variety of tools that are available. In addition to doing monitoring and security assessments constantly, it is vital that the results are analyzed and properly audited so that an organization can not only ensure that its database security posture is sound, but also demonstrate compliance with regulations that demand high levels of security be applied to sensitive data.

THREATS TO DATABASE SECURITY

Almost all organizations use databases in some form for tracking information such as customer and transaction records, financial information, and human resources records. Much of the information contained in databases is sensitive and can be sold for cash or, such as in cases of theft by a disgruntled employee or by a hacker with political motivations, to cause the organization loss of business or reputation, especially if the organization is found to be in breach of regulations or industry standards that demand high levels of data security.

According to the 2012 data breach investigations report published by Verizon Business, 96% of records breached in 2011 were taken from database servers. Of these, 55% exploited default or guessable credentials and 40% the use of stolen login credentials. And, according to another study among data professionals conducted by Unisphere Research, a division of Information Today, Inc., for the IOUG little more than one-third of the 430 respondents install 37% critical patch updates within three months of their release.

According to technology vendor Application Security, Inc., the following are the top 10 threats related to databases:

  1. Default or weak passwords
  2. SQL injection
  3. Excessive user and group privileges
  4. Unnecessary DBMS features enabled
  5. Broken configuration management
  6. Buffer overflows
  7. Privilege escalation
  8. Denial of service
  9. Un-patched RDBMS
  10. Unencrypted data

5 DATABASE SECURITY ESSENTIALS

There are 5 key steps to ensuring database security, according to Applications Security, Inc.

  1. Isolate sensitive databases—maintain an accurate inventory of all databases deployed across the enterprise and identify all sensitive data residing on those databases.
  2. Eliminate vulnerabilities—continually assess, identify and remediate vulnerabilities that expose the database.
  3. Enforce least privileges—identify user entitlements and enforce user access controls and privileges to limit access to only the minimum data required for employees to do their jobs.
  4. Monitor for deviations—implement appropriate policies and monitor any vulnerabilities that cannot be remediated for any and all activity the deviates from authorized activity.
  5. Respond to suspicious behavior—alert and respond to any abnormal or suspicious behavior in real time to minimize risk of attack.

DATABASE SECURITY BEST PRACTICES

The first step for ensuring database security is to develop a database security plan, taking into account regulations such as Sarbanes-Oxley and industry standards such as the Payment Card Industry Data Security Standards with which the organization must comply. The use of a standard checklist is to be advised, rather than trying to develop a security plan from scratch. Examples of such as checklist include those available from the information assurance support environment website, sponsored by the U.S. Defense Information Systems Agency or the Center for Internet Security.

As part of the development of this plan, the organization should take an inventory of all databases within the organization’s network environment, which can be done more efficiently through use of vulnerability management technology that can automatically discover all databases and run scans to identify which contain sensitive information, such as financial information and customer data. The scans performed by such technology will also be able to assess database vulnerabilities and misconfigurations, identifying issues such as default or weak passwords, missing patches and poor access controls, and will look to identify which vulnerabilities can be exploited so that remediation can be prioritized. Most tools available will include built-in templates that incorporate the requirements of many best practice frameworks and regulatory compliance initiatives. Such tools should be used not only when developing a database security program, but on a continuous basis to identify new threats and vulnerabilities.

Database activity monitoring (DAM) tools will also aid in the process of reducing vulnerabilities by providing visibility in real time into all database activity. Such tools collect data, aggregate it and analyze the data to look for activities that are in violation of security policy or that indicate anomalies have occurred. According to the Gartner Group, the primary reason for deploying DAM technologies is to monitor the activity of privileged users such as database and system administrators, developers, and help desk and outsourced personnel, many of whom typically have unfettered access to corporate databases. To ensure that threats are minimized and the requirements of regulations are being complied with, DAM tools should be used to identify anomalous activities such as privileged users viewing sensitive data, altering log records, making unauthorized configuration changes or creating new accounts with super user privileges. They can compare activities performed with those authorized by change requests. In general, it is considered to be best practice to implement access controls based on the principle of least privilege to ensure that no one user has excessive access rights and those rights should be regularly audited.

Click here for full Story

How to Enable and Grant Remote Access to MySQL Database Server

For reasons of security, remote access to MySQL database server is disabled by default because they are considered potential security threats. However, due to some reason, it is necessary to allow access from a remote location or web server. Let assume that we are making connection from remote web server IP called 192.168.0.3 for database called db1 for user user1 at remote MySQL server, 192.168.0.2, then we need to grant access to this IP address.

If the remote access is not enable you will get this error :

ERROR 1130 (HY000): Host ‘192.168.0.3’ is not allowed to connect to this MySQL server

IP Adress 1 : 192.168.0.2 – MySQL Server
IP Adress 2 : 192.168.0.3 – Web Server (Nginx or Apache)

Steps to Enable and Grant Remote Access to MySQL Database Server

1. Edit the my.cnf file :

# vim /etc/mysql/my.cnf

Comment out or remove below line :

#bind-address           = 127.0.0.1

2. The following command will allow access to the MySQL database(192.168.0.2) from a remote web server IP address(192.168.0.3):

mysql> create user 'user1'@'192.168.0.3' identified by 'PASSWORD';
mysql> grant all on db1.* to 'user1'@'192.168.0.3';

3. Test the connection from the remote web server :

# mysql -u user1 -pPASSWORD -h 192.168.0.2

4. Verify the user privileges for user1 :

mysql> select * from information_schema.user_privileges where grantee like "'user1'%";

5. In case you want to revoke all options the access from all machine or web server(192.168.0.3) only :

mysql> revoke all privileges, grant option from 'user1'@'%';
mysql> revoke all privileges, grant option from 'user1'@'192.168.0.3';

database

How to Import and Export MySQL Database Command line in Linux

import and export MySQL database command line in LinuxIn this article, I will show you how to import and export MySQL database command line in Linux. In this case, export is to backup while import is to restore. The syntax are very simple and easy to understand and suitable for all levels of users including beginners. This MySQL command line is useful for those who want to migrate their WordPress blog from shared hosting to virtual private server (VPS) hosting or from current VPS hosting to new VPS hosting. This MySQL Database Import and Export command has been tested on CentOS 6 and CentOS 7 and Oracle Linux either on MySQL or MariaDB database.

How to Export MySQL Database Command line in Linux

1. Syntax to Export:

mysqldump -u USERNAME -p DATABASE_NAME > filename.sql

How to Export MySQL Database Command line in Linux

2. Syntax to Import:

mysql -u USERNAME -p DATABASE_NAME < filename.sql

Example :

Export WordPress_DB :

[root@vps ~]# mysqldump -u WordPress_User -p WordPress_DB > WordPress_DB.sql

Import WordPress_DB :

[root@vps ~]# mysql -u WordPress_User -p WordPress_DB < WordPress_DB.sql

I hope this article gives you some ideas and essential guidance on how to import and export MySQL database command line in Linux.

 

How to Secure MySQL Server on CentOS 6.5 / CentOS 6.6

MySQL is the world’s most popular open source database and its the world’s second most widely used open-source relational database management system (RDBMS). MySQL default installation is not securely configured. For the sake of security, we need to run mysql_secure_installation wizard manually in order to perform basic MYSQL hardening on Virtual private server (VPS). The following steps has been tested on MySQL Community Server 5.5.39 that was running on CentOS 6.5 and CentOS 6.6.

1. Run mysql_secure_installation wizard :

[root@vps ]# mysql_secure_installation




NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


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

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 MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL 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 them 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 at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, 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 MySQL
installation should now be secure.

Thanks for using MySQL!

2. Set “bind-address” parameter within the “[mysqld]” section in /etc/my.conf. Configure this to your VPS local loopback network device, which is “127.0.0.1”. please make sure that you only perform this step if you confirm no other server will need to access the database on your VPS.

[root@vps ~]# vi /etc/my.cnf
[mysqld]
..
bind-address = 127.0.0.1
..

3. Restart your mysqld server :

[root@vps ~]# service mysqld restart

4. Verify the mysqld port listen to 127.0.0.1 only :

[root@vps ~]# netstat -plunt | grep 3306
tcp        0      0 127.0.0.1:3306              0.0.0.0:*                   LISTEN      8224/mysqld

How to Display MySQL root Password in Zimbra

In the situation we need to utilize the zimbra MySQL database server in order to host other databases, we may need to know what is the root password for MySQL. The following command will help you to find and display MySQL root password. These command has been tested on Zimbra 8.0.7 thas was running on CentOS 6.5 operating system.

To view system operating system :

[root@mail-server ~]# cat /etc/redhat-release
CentOS release 6.5 (Final)

To view zimbra version :

[root@mail-server ~]# su - zimbra
[zimbra@mail-server ~]$ zmcontrol -v
Release 8.0.7_GA_6021.RHEL6_64_20140408123911 RHEL6_64 FOSS edition.

To view MySQL root password :

[root@mail-server ~]# su - zimbra
[zimbra@mail-server ~]$ zmlocalconfig -s | grep mysql_root_password
antispam_mysql_root_password =
mysql_root_password = ipXlRAJ7654321FDXHb4nMUFr9Uf

To display zimbra MySQL pasword :

[root@mail-server ~]# su - zimbra
[zimbra@mail-server ~]$ zmlocalconfig -s | grep mysql | grep password
antispam_mysql_password =
antispam_mysql_root_password =
mysql_root_password = ipXlRAJ7654321FDXHb4nMUFr9Uf
zimbra_mysql_password = c7dr5Tj7654321qcHCP6qJMVRVw

To view more options and help :

[root@mail-server ~]# su - zimbra
[zimbra@mail-server ~]$ zmlocalconfig --help
usage: zmlocalconfig [options] [args]
where [options] are:
 -c,--config    File in which configuration is stored.
 -d,--default        Show default values for keys listed in [args].
 -e,--edit           Edit configuration file, changing keys and values
                     specified. [args] is in key=value form.
 -f,--force          Allow editing of keys whose change is known to be
                     potentially dangerous.
 -h,--help           Show this usage information.
 -i,--info           Show documentation for keys listed in [args].
 -l,--reload         Send a SOAP request to the server to reload its local
                     config.
 -m,--format    Show values in one of these formats: plain (default),
                     xml, shell, export, nokey.
 -n,--changed        Show values for only those keys listed in [args] that
                     have been changed from their defaults.
 -p,--path           Show which configuration file will be used.
 -q,--quiet          Suppress logging.
 -r,--random         Used with the edit option, sets specified key to
                     random password string
 -s,--show           Force display of password strings.
 -u,--unset          Remove a configuration key.  If this is a key with
                     compiled in defaults, set its value to the empty
                     string.
 -x,--expand         Expand values.

How to Install phpMyAdmin on Ubuntu 14.04

phpMyAdmin is a open source software that intended to manage and administer MySQL over the Web browsers and it was written in PHP. It is one of the most popular tools for managing the MySQL database and you’ll need to install and configure Apache, PHP, and the PHP MySQL in order to make it run perfectly. Follow the following steps to install phpMyAdmin on Ubuntu 14.04 virtual private server (VPS)and dedicated server.

1. Assumed that Apache web server and MySQL database server has been prepared.

2. Install phpmyadmin :

ehowstuff@ubuntu14:~$ sudo apt-get install phpmyadmin -y

phpmyadmin-ubuntu14-1
phpmyadmin-ubuntu14-2

3. Configure Apache :

ehowstuff@ubuntu14:~$ sudo vi /etc/phpmyadmin/apache.conf

Add IP as below :

# phpMyAdmin default Apache configuration

Alias /phpmyadmin /usr/share/phpmyadmin

<Directory /usr/share/phpmyadmin>
        Options FollowSymLinks
        DirectoryIndex index.php
        Require ip 127.0.0.1 192.168.0.0/24
        <IfModule mod_php5.c>
                AddType application/x-httpd-php .php

                php_flag magic_quotes_gpc Off
                php_flag track_vars On
                php_flag register_globals Off
                php_admin_flag allow_url_fopen Off
                php_value include_path .
                php_admin_value upload_tmp_dir /var/lib/phpmyadmin/tmp
                php_admin_value open_basedir /usr/share/phpmyadmin/:/etc/phpmyadmin/:/var/lib/phpmyadmin/:/usr/share/php/php-gettext/:/usr/share/javascript/
        </IfModule>

</Directory>

4. Restart Apache :

ehowstuff@ubuntu14:~$ sudo /etc/init.d/apache2 restart
 * Restarting web server apache2                                                             [ OK ]
ehowstuff@ubuntu14:~$

5. Access to “http://IP_address/phpmyadmin/” and login to MySQL.
phpmyadmin-ubuntu14-3

How to Install Lighttpd With PHP5 (PHP-FPM) and MySQL on CentOS 6.5

Lighttpd (pronounced “lighty”) is an open-source web server as an alternative to Apache and Nginx. It is a secure, flexible, fast and designed for speed-critical environments. It has a low memory footprint and can handle large number of connections in one server especially for busier sites.

PHP is an acronym for “PHP Hypertext Preprocessor”; PHP is a widely-used server-side scripting language executed on the server.

PHP-FPM (FastCGI Process Manager) is an alternative PHP FastCGI implementation with some additional features useful for sites of any size, especially busier sites.

MySQL is a popular database solution for use in web applications.

This post will show you how to install Lighttpd With PHP5 (PHP-FPM) and MySQL on CentOS 6.5 VPS or dedicated server.

1. EPEL repository is another extra repository that creates, maintains, and manages a high quality set of additional packages for Enterprise Linux, including, but not limited to, Red Hat Enterprise Linux (RHEL) and CentOS server. How to Configure EPEL Repository on CentOS.

2. Install Lighttpd, MySQL and PHP5 work in Lighttpd through PHP-FPM :

[root@centos6-05 ~]# yum install lighttpd php php-fpm lighttpd-fastcgi php-mysql mysql mysql-server -y

3. Configure Lighttpd :

[root@centos6-05 ~]# vi /etc/lighttpd/lighttpd.conf
server.use-ipv6 = "enable"

Change to :

server.use-ipv6 = "disable"

4. Make lighttpd start at boot and also start lighttpd service:

[root@centos6-05 ~]# chkconfig --levels 235 lighttpd on
[root@centos6-05 ~]# /etc/init.d/lighttpd start

Browse your web server and Lighttpd welcome page should be displayed :
lighttpd-centos6.5-1

5. Configure PHP to work in Lighttpd through PHP-FPM :

[root@centos6-05 ~]# vi /etc/php-fpm.d/www.conf

Enable PHP-FPM use a TCP connection instead of unix socket :

;   '/path/to/unix/socket' - to listen on a unix socket.
; Note: This value is mandatory.
listen = 127.0.0.1:9000

Configure user and group to lighttpd :

..
..
; RPM: apache Choosed to be able to access some dir as httpd
user = lighttpd
; RPM: Keep a group allowed to write in log dir.
group = lighttpd
..
..

6. Make php-fpm start at boot and also start the php-fpm service :

[root@centos6-05 ~]# chkconfig --levels 235 php-fpm on
[root@centos6-05 ~]# /etc/init.d/php-fpm start

7. Open and modify /etc/php.ini :

[root@centos6-05 ~]# vi /etc/php.ini

Uncomment the line cgi.fix_pathinfo=1

..
; cgi.fix_pathinfo provides *real* PATH_INFO/PATH_TRANSLATED support for CGI.  PHP's
; previous behaviour was to set PATH_TRANSLATED to SCRIPT_FILENAME, and to not grok
; what PATH_INFO is.  For more information on PATH_INFO, see the cgi specs.  Setting
; this to 1 will cause PHP CGI to fix its paths to conform to the spec.  A setting
; of zero causes PHP to behave as before.  Default is 1.  You should fix your scripts
; to use SCRIPT_FILENAME rather than PATH_TRANSLATED.
; http://www.php.net/manual/en/ini.core.php#ini.cgi.fix-pathinfo
cgi.fix_pathinfo=1
..

8. Open and modify /etc/lighttpd/modules.conf :

[root@centos6-05 ~]# vi /etc/lighttpd/modules.conf

Uncomment the line include “conf.d/fastcgi.conf”:

..
## FastCGI (mod_fastcgi)
##
include "conf.d/fastcgi.conf"
..

9. Open and modify /etc/lighttpd/conf.d/fastcgi.conf :

Add below fastcgi.server at bottom of the file :

..
fastcgi.server += ( ".php" =>
        ((
                "host" => "127.0.0.1",
                "port" => "9000",
                "broken-scriptfilename" => "enable"
        ))
)
..

10. Reload the PHP-FPM and Lighttpd service :

[root@centos6-05 ~]# /etc/init.d/php-fpm reload
[root@centos6-05 ~]# /etc/init.d/lighttpd reload

11. Since MySQl has been install, dont forget to make MySQL start at boot and start the MySQL service :

[root@centos6-05 ~]# chkconfig --levels 235 mysqld on
[root@centos6-05 ~]# /etc/init.d/mysqld start

12. Create info.php under ligghttpd document root :

[root@centos6-05 ~]# vi /var/www/lighttpd/info.php
<?php
phpinfo();
?>

Browse your page http://IP-Adress/info.php. Thats all.

How to Install and Configure Apache2, PHP and MySQL 5.6 on Ubuntu 14.04

LAMP stack is a group of open source software that installed together to let you run a server to host dynamic websites. “L” stand for Linux, “A” stand for Apache (to host Web server), “M” stand for MySQL(to store database) and “P” stand for PHP(to process dynamic content). With the release of Ubuntu 14.04 on April 17 2014, i would share the steps to setup Apache2, PHP and MySQL on Ubuntu 14.04 in order to run a dynamic websites. This may useful for those who plan to run their websites on Virtual private server (VPS) or dedicated server.

1. Install Apache2, MySQL and PHP :

ehowstuff@ubuntu14:~$ sudo apt-get install apache2 php5 php5-cgi libapache2-mod-php5 php5-common php-pear mysql-server-5.6 -y

During this installation, you will require to set MySQL’s root password :
1

2

2. Backup the original Apache2 configuration file :

ehowstuff@ubuntu14:~$ sudo cp -p /etc/apache2/conf-enabled/security.conf /etc/apache2/conf-enabled/security.conf.bak

3. Open security.conf and modify the OS to become Prod. For security reason, Prod will tells apache to only return Apache in the Server header, returned on every page request.

ehowstuff@ubuntu14:~$ sudo vi /etc/apache2/conf-enabled/security.conf
..
..
ServerTokens Prod
..
..
ServerSignature Off
..
..

4. Add file extension that can be access :

ehowstuff@ubuntu14:~$ sudo vi /etc/apache2/mods-enabled/dir.conf
<IfModule mod_dir.c>
        DirectoryIndex index.html index.cgi index.pl index.php index.xhtml index.htm
</IfModule>

5. Specify server name :

ehowstuff@ubuntu14:~$ sudo vi /etc/apache2/apache2.conf
# Do NOT add a slash at the end of the directory path.
#
#ServerRoot "/etc/apache2"
ServerName ubuntu14.ehowstuff.local
#
# The accept se

6. Specify webmaster’s email :

ehowstuff@ubuntu14:~$ sudo vi /etc/apache2/sites-enabled/000-default.conf

        ServerAdmin webmaster@ubuntu14.ehowstuff.local
        DocumentRoot /var/www/html

7. Restart web server apache2 :

ehowstuff@ubuntu14:~$ sudo /etc/init.d/apache2 restart
 * Restarting web server apache2                                                             [ OK ]

8. Near line 220: add extension for PHP :

ehowstuff@ubuntu14:~$ sudo vi /etc/apache2/mods-enabled/mime.conf
..
..
AddHandler php5-script .php
..
..

9. Comment and add your timezone :

ehowstuff@ubuntu14:~$ sudo vi /etc/php5/apache2/php.ini
..
..
date.timezone = "Asia/Kuala Lumpur"
..
..

After change php.ini, restart the apache :

ehowstuff@ubuntu14:~$ sudo /etc/init.d/apache2 restart
 * Restarting web server apache2                                                             [ OK ]

10. Connect to MySQL :

ehowstuff@ubuntu14:~$ sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.6.17-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

11. Show user info :

mysql> select user,host,password from mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root             | ubuntu14  | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root             | 127.0.0.1 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root             | ::1       | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| debian-sys-maint | localhost | *9C063813F4CC3C2E09995B0D043C7375C5E5538A |
+------------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

12. Show databases :

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.01 sec)

Done!!

How to Display processlist in MySQL

MySQL databases are great database in internet. It’s commonly used in WordPress and Drupal blog. When you are monitoring the performance of a WordPress or Drupal blog, do not forget to monitor the MySQL queries using existing tools including mytop, mtop and also running a SHOW PROCESSLIST from the mysql client command line. It will show all the queries are running and how long they take. The command SHOW PROCESSLIST actually shows you which threads are running in realtime. You can also can get this information from the mysqladmin processlist command and from the INFORMATION_SCHEMA PROCESSLIST table. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads. However, if you really need to investigate or monitor overall website performance issue, please also look at other aspects of the system such memory and swapping as well as CPU utilization.

Here is an example of SHOW PROCESSLIST output:

show processlist;
mysql> show processlist;
+----+---------------+-------------------+-------------+---------+------+-------+------------------+
| Id | User          | Host              | db          | Command | Time | State | Info             |
+----+---------------+-------------------+-------------+---------+------+-------+------------------+
| 23 | root          | localhost         | NULL        | Query   |    0 | NULL  | show processlist |
| 46 | wordpressuser | 192.168.0.5:38876 | wordpressdb | Sleep   |   69 |       | NULL             |
| 51 | root          | localhost         | wordpressdb | Sleep   |   34 |       | NULL             |
+----+---------------+-------------------+-------------+---------+------+-------+------------------+
3 rows in set (0.00 sec)

Alternately you can run as below :

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 23
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: SHOW PROCESSLIST
*************************** 2. row ***************************
     Id: 46
   User: wordpressuser
   Host: 192.168.0.5:38876
     db: wordpressdb
Command: Sleep
   Time: 73
  State:
   Info: NULL
*************************** 3. row ***************************
     Id: 51
   User: root
   Host: localhost
     db: wordpressdb
Command: Sleep
   Time: 38
  State:
   Info: NULL
3 rows in set (0.00 sec)

You have an option to run processlist in mysqladmin command. Below example show processlist every two second :

mysqladmin -u root -p -i 2 processlist

Example :

[root@mysql-server ~]# mysqladmin -u root -p -i 2 processlist
Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 6  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 6  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 6  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 6  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

How to Secure your MySQL On VPS or Dedicated Server

Running a WordPress on a Virtual private Server or dedicated server is not an easy as running a WordPress on shared hosting server. There are a few things need to install and configure. Basically you will need web server(Apache, Nginx or Lighttpd) and database server(MySQL). The most popular database for WordPress platform is MySQL. Installation of the MySQL is very easy, but most of the webmaster will facing difficulties on the configuration part. Therefore i have prepared the article that will cover configuring and securing your MySQL on Virtual private Server(VPS) or on dedicated server. MySQL database is actually the brain of your website or blog. It will store all the configuration information, the posts, comments, login information, user information and etc. This article assumed that you already installed the MySQL server on your VPS or dedicated server and then you may proceed to configure and harden it as below :

1. Run pre-install mysql script, mysql_secure_installation. This will do the following :

a) Set the root password ensures that nobody can log into the MySQL root user without the proper authorization.
b) Remove anonymous users
c) Remove test database and access to it
d) Normally, root should only be allowed to connect from ‘localhost’. Disallow root login remotely if you want. However i prefer to disallow it later

[root@mysql-server ~]# /usr/bin/mysql_secure_installation




NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


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

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 MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL 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 them 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 at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, 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 MySQL
installation should now be secure.

Thanks for using MySQL!

2. List of MySQL users, make sure all users have password :

mysql> SELECT User,Host,Password FROM mysql.user;
+---------------+-------------+-------------------------------------------+
| User          | Host        | Password                                  |
+---------------+-------------+-------------------------------------------+
| root          | localhost   | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root          | mysql       | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root          | 127.0.0.1   | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| wordpressuser | 192.168.0.5 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+---------------+-------------+-------------------------------------------+
4 rows in set (0.00 sec)

3. Set a strong password for the MySQL root account and also existing user account :

Existing user account :

mysql> select Host,User,Password from user;
+-------------+---------------+-------------------------------------------+
| Host        | User          | Password                                  |
+-------------+---------------+-------------------------------------------+
| localhost   | root          | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| mysql       | root          | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| 127.0.0.1   | root          | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| 192.168.0.5 | wordpressuser | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-------------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)

Set new strong password :

mysql> set password for 'root'@'localhost'=password('newstrongpassword');
mysql> set password for 'root'@'127.0.0.1'=password('newstrongpassword');
mysql> set password for 'wordpressuser'@'192.168.0.5'=password('newstrongpassword');

4. Make sure logging such as general_log, slow_query_log and log-error has been enabled in mysql :

[root@mysql-server ~]# vim /etc/my.cnf
[mysqld]
..
..
..
general_log_file=/var/log/mysql/mysqld.log
general_log=1
slow_query_log_file=/var/log/mysql/mysqld.slow.log
slow_query_log=1

[mysqld_safe]
log-error=/var/log/mysql/mysqld.error.log
...
..

Create folder for mysql log and change the folder owner to mysql:

[root@mysql-server ~]# chown -R mysql:mysql /var/log/mysql

Verify the logs :

[root@mysql-server ~]# ll /var/log/mysql
total 12
-rw-r----- 1 mysql mysql 3547 Apr  7 16:57 mysqld.error.log
-rw-rw---- 1 mysql mysql  373 Apr  7 16:58 mysqld.log
-rw-rw---- 1 mysql mysql  174 Apr  7 16:57 mysqld.slow.log

This Will help administrators to monitor critical events and helps in troubleshooting.

Reference : http://dev.mysql.com/doc/refman/5.7/en/server-logs.html

Once you have done above configuration, make sure yo restart the mysqld service :

[root@mysql-server ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

Note : This configuration and hardening practice is very basic, you can fine tune your database based on your expected security level and also you can implement host iptables, physical firewall protection and operating system hardening in order to protect the MySQL server. You may refer to “Securing and Hardening Linux Dedicated Server

How to Install WordPress on Remote MySQL

WordPress is an open source content management system (CMS) and popular blogging platform in the world based on PHP and MySQL platform. WordPress can be install on multiple way, either using dedicated server, virtual private server(VPS) or the cheapest way is running on shared hosting. When come to decision to run the wordpress on dedicated server or on VPS, the next question would be whether to run a web service and database service on single or multiple server. We have an option to combine it or to split it. For high performance wordpress website, i would suggest you to run web server(Apache, NGINX, Lighttpd) and database server (MySQL) on different server. Below steps should provide the basic steps how you can setup the wordpress on remote MySQL.

server1 = 192.168.0.5 = Apache server
server2 = 192.168.0.6 = Remote MySQL server

1. Login as a root on server1 then download latest wordpress file and extract the file :

[root@server1 html]# cd /var/www/html
[root@server1 html]# wget http://wordpress.org/latest.tar.gz
[root@server1 html]# tar xzvf latest.tar.gz

2. Login to server2, create the database for the wordpress :

[root@server2 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database wordpressdb;
Query OK, 1 row affected (0.00 sec)

mysql> create user 'wordpressuser'@'192.168.0.5' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on wordpressdb.* to 'wordpressuser'@'192.168.0.5';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'wordpressuser'@'192.168.0.5';
+------------------------------------------------------------------------------------------------------------------------+
| Grants for wordpressuser@192.168.0.5                                                                                   |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wordpressuser'@'192.168.0.5' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT ALL PRIVILEGES ON `wordpressdb`.* TO 'wordpressuser'@'192.168.0.5'                                               |
+------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

3. Once step 2 above has been done, login again to server1, test the connectivity to database server :

[root@server1 html]# mysql -u wordpressuser -p -h 192.168.0.6
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
| worpressdb         |
+--------------------+
3 rows in set (0.00 sec)

4. Still on server1, copy the config.php file :

[root@server1 ~]# cp /var/www/html/wordpress/wp-config-sample.php /var/www/html/wordpress/wp-config.php

5. Modify the config.php file and enter the database informations and remote mysql server details :

[root@server1 ~]# vi /var/www/html/wordpress/wp-config.php
define('DB_NAME', 'wordpressdb');

/** MySQL database username */
define('DB_USER', 'wordpressuser');

/** MySQL database password */
define('DB_PASSWORD', 'password');

/** MySQL hostname */
define('DB_HOST', '192.168.0.6');

/** Database Charset to use in creating database tables. */
define('DB_CHARSET', 'utf8');

/** The Database Collate type. Don't change this if in doubt. */
define('DB_COLLATE', '');

6. To install, navigate the browser to http://servername/wordpress/.

http://192.168.0.5/wordpress/