How to Install MySQL on CentOS 7 / RHEL 7 / Oracle Linux 7

how to install mysql
In this quick guide, i will show you how to install MYSQL on CentOS 7 / RHEL 7 / Oracle Linux 7 instead of MariaDB.

MariaDB is the default implementation of MySQL in Red Hat Enterprise Linux 7 (RHEL 7) or CentOS 7.

MariaDB is a community-developed fork of the MySQL database project, and provides a replacement for MySQL.

However, in some cases, you still need to install MySQL as your deployment database on you linux server.

How to Install MySQL on CentOS 7 / RHEL 7 / Oracle Linux 7

1. Remove MariaDB installation :
If you server already have MariaDB database server installed, i would suggest you remove it first to avoid conflict.

# sudo yum remove mariadb-server -y

2. Download MySQL 5.7 repo file :

# wget https://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

3. Install MySQL 5.7 repo file :

# sudo rpm -ivh mysql57-community-release-el7-8.noarch.rpm

4. Install MySQL 5.7 database server :

# sudo yum install mysql-server -y

5. How to Start MySQL server in linux :

# sudo systemctl start mysqld

6. Enable auto start at boot :

# sudo systemctl enable mysqld

7. At the initial start up of the MySQL database server, the following happens, given that the data directory of the server is empty:

a) The server is initialized.
b) An SSL certificate and key files are generated in the data directory.
c) The validate_password plugin is installed and enabled.
d) A superuser account ‘root’@’localhost is created. The initial root password created can be found in the error log file. You can get the password by issue the following command :

# sudo grep 'temporary password' /var/log/mysqld.log
2016-06-19T23:08:09.439963Z 1 [Note] A temporary password is generated for root@localhost: sj-mMM;o%6Ll

8. Harden MySQL Server

Run the mysql_secure_installation script to address several security concerns in a default MySQL installation.

You will be given the choice to change the MySQL root password, remove anonymous user accounts, disable root logins outside of localhost, and remove test databases. It is recommended that you answer yes to these options.

# sudo mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 50
Change the password for root ? ((Press y|Y for Yes, any other key for No) : yes

New password:

Re-enter new password:

Estimated strength of the password: 50
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
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? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : no

 ... 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? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

Resource : http://dev.mysql.com/

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 Calculate and List Sizes of MySQL / MariaDB Databases

Question :
I am finding the best way to calculate and list sizes of MySQL or MariaDB Databases ?

Answer :

In order to achive this, please do the following.

1. Enter to mysql root console:

# mysql -u root -p

2. Run the following SQL select query to calculate and list the sizes of all available databases in MySQL or MariaDB.

MariaDB [(none)]> SELECT table_schema AS "DB Name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;

Output :

+--------------------+-------------+
| DB Name            | Size (MB)   |
+--------------------+-------------+
| wordpressdb        | 36.79408455 |
| information_schema |  0.14062500 |
| mysql              |  0.62723351 |
| oscommercedb       |  1.42187500 |
| performance_schema |  0.00000000 |
+--------------------+-------------+
5 rows in set (0.02 sec)

MariaDB [(none)]>

linux-banner

How to Change the WordPress URLs in MySQL Database

Before this I have experienced issues in wordpress migration of servers moving from the test server with an unregistered domain URL (www.ehowstuff.local) to the new virtual private server (VPS) with a registered domain (www.ehowstuff.com). After struggling to do research on google, I found the steps below that save a lot of time.

Step 1 : Update ‘siteurl’ and ‘home’ :

Select the WordPress database :

mysql> use wordpressdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Check current value for ‘siteurl’ and ‘home’ :

mysql> SELECT option_name, option_value FROM wp_options WHERE option_name IN ('siteurl', 'home');
+-------------+----------------------------+
| option_name | option_value               |
+-------------+----------------------------+
| home        | http://www.ehowstuff.local |
| siteurl     | http://www.ehowstuff.local |
+-------------+----------------------------+
2 rows in set (0.00 sec)

Update the value for ‘siteurl’ and ‘home’ :

mysql> UPDATE wp_options SET option_value = 'https://webhostinggeeks.com/howto' WHERE option_name IN ('siteurl', 'home');
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Check the updated value for ‘siteurl’ and ‘home’ :

mysql> SELECT option_name, option_value FROM wp_options WHERE option_name IN ('siteurl', 'home');

+-------------+--------------------------+
| option_name | option_value             |
+-------------+--------------------------+
| home        | https://webhostinggeeks.com/howto |
| siteurl     | https://webhostinggeeks.com/howto |
+-------------+--------------------------+
2 rows in set (0.00 sec)

Optionally you can use below query to achieve step 1:

mysql> UPDATE wp_options SET option_value = replace(option_value, 'http://www.ehowstuff.local', 'https://webhostinggeeks.com/howto') WHERE option_name = 'home' OR option_name = 'siteurl';

Step 2 : Update the guid value in wp_posts table :

mysql> UPDATE wp_posts SET guid = replace(guid, 'http://www.ehowstuff.local','https://webhostinggeeks.com/howto');

Step 3 : Update the post_content value in wp_posts table :

mysql> UPDATE wp_posts SET post_content = replace(post_content, 'http://www.ehowstuff.local', 'https://webhostinggeeks.com/howto');

Step 4 : Update the meta_value value in wp_postmeta table :

mysql> UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://www.ehowstuff.local', 'https://webhostinggeeks.com/howto');

wp-wall

How to Install LAMP on CentOS 7 / RHEL 7

LAMP stack is a group of open source software that installed together to let you run a web server to host dynamic websites. “L” stand for Linux, “A” stand for Apache (to host Web server), “M” stand for MySQL(to store database) but in RHEL 7, MariaDB will in place of MySQL by default. “P” stand for PHP(to process dynamic content). This brief tutorial will explain how to install LAMP on CentOS 7 / RHEL 7 / Oracle Linux 7.

How to Install LAMP on CentOS 7 / RHEL 7 / Oracle Linux 7

The LAMP stack is just short reference for Linux, Apache, MySQL/MariaDB and PHP :

L = Linux
A = Apache
M = MariaDB /MySQL
P = PHP

If you’re a newbie, these steps can help to install web server and database server on linux operating system. It is very simple to install install LAMP on CentOS 7 / RHEL 7 / Oracle Linux 7. You just need to follow these steps one by one.

1. Install Apache web server, Mariadb database and PHP packages :

When come to the question, how to install web server i ? The answer is very easy,

[root@centos7 ~]# sudo yum install mariadb mariadb-server httpd php php-mysql php-gd php-ldap php-odbc php-pear php-xml php-xmlrpc php-mbstring php-snmp php-soap -y

2. Start Apache web server and Mariadb database. Make it auto start at boot :

[root@centos7 ~]# sudo systemctl start httpd.service
[root@centos7 ~]# sudo systemctl enable httpd.service
[root@centos7 ~]# sudo systemctl start mariadb
[root@centos7 ~]# sudo systemctl enable mariadb.service

3. Settings for MariaDB.

[root@centos7 ~]# sudo mysql_secure_installation
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

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

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

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] n
 ... skipping.

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 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, MariaDB 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...
ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist
 ... Failed!  Not critical, keep moving...
 - 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!

4. Browse to your server ip http://IPaddress.

Install LAMP on CentOS 7
Follow the instruction in welcome.conf before proceed to configure the virtual host :

[root@centos7 ~]# vi /etc/httpd/conf.d/welcome.conf

Install LAMP on CentOS 7

5. Configure Apache Virtual host :

a. Create DocumentRoot directory for new domain :

[root@centos7 ~]# mkdir -p /var/www/html/ehowstuff

b. Create vhost.conf to place Name-based virtual host. For example, the default domain is centos7.ehowstuff.local and you wish to add the new virtual host www.ehowstuff.local, which points at the same IP address.

[root@centos7 ~]# vi /etc/httpd/conf.d/vhost.conf

Add the following. Please note that “NameVirtualHost *:80” no longer valid in Apache 2.4.x :

#Create new default domain
<VirtualHost *:80>
   DocumentRoot /var/www/html
   ServerName centos7.ehowstuff.local
</VirtualHost>
# for virtual domain
<VirtualHost *:80>
   DocumentRoot /var/www/html/ehowstuff
   ServerName www.ehowstuff.local
   ServerAdmin webmaster@ehowstuff.local
   ErrorLog logs/www.ehowstuff.local-error_log
   CustomLog logs/www.ehowstuff.local-access_log combined
</VirtualHost>

6. To debug apache configuration :

[root@centos7 ~]# httpd -S
VirtualHost configuration:
*:80                   is a NameVirtualHost
         default server centos7.ehowstuff.local (/etc/httpd/conf.d/vhost.conf:2)
         port 80 namevhost centos7.ehowstuff.local (/etc/httpd/conf.d/vhost.conf:2)
         port 80 namevhost centos7.ehowstuff.local (/etc/httpd/conf.d/vhost.conf:2)
         port 80 namevhost www.ehowstuff.local (/etc/httpd/conf.d/vhost.conf:7)
         port 80 namevhost www.ehowstuff.local (/etc/httpd/conf.d/vhost.conf:7)
ServerRoot: "/etc/httpd"
Main DocumentRoot: "/var/www/html"
Main ErrorLog: "/etc/httpd/logs/error_log"
Mutex proxy: using_defaults
Mutex authn-socache: using_defaults
Mutex default: dir="/run/httpd/" mechanism=default
Mutex mpm-accept: using_defaults
Mutex authdigest-opaque: using_defaults
Mutex proxy-balancer-shm: using_defaults
Mutex rewrite-map: using_defaults
Mutex authdigest-client: using_defaults
PidFile: "/run/httpd/httpd.pid"
Define: DUMP_VHOSTS
Define: DUMP_RUN_CFG
User: name="apache" id=48
Group: name="apache" id=48

7. To get more information about apache :

[root@centos7 ~]# httpd -V
Server version: Apache/2.4.6 (CentOS)
Server built:   Jan 12 2015 13:22:31
Server's Module Magic Number: 20120211:23
Server loaded:  APR 1.4.8, APR-UTIL 1.5.2
Compiled using: APR 1.4.8, APR-UTIL 1.5.2
Architecture:   64-bit
Server MPM:     prefork
  threaded:     no
    forked:     yes (variable process count)
Server compiled with....
 -D APR_HAS_SENDFILE
 -D APR_HAS_MMAP
 -D APR_HAVE_IPV6 (IPv4-mapped addresses enabled)
 -D APR_USE_SYSVSEM_SERIALIZE
 -D APR_USE_PTHREAD_SERIALIZE
 -D SINGLE_LISTEN_UNSERIALIZED_ACCEPT
 -D APR_HAS_OTHER_CHILD
 -D AP_HAVE_RELIABLE_PIPED_LOGS
 -D DYNAMIC_MODULE_LIMIT=256
 -D HTTPD_ROOT="/etc/httpd"
 -D SUEXEC_BIN="/usr/sbin/suexec"
 -D DEFAULT_PIDLOG="/run/httpd/httpd.pid"
 -D DEFAULT_SCOREBOARD="logs/apache_runtime_status"
 -D DEFAULT_ERRORLOG="logs/error_log"
 -D AP_TYPES_CONFIG_FILE="conf/mime.types"
 -D SERVER_CONFIG_FILE="conf/httpd.conf"

8. Check php version :

[root@centos7 ~]# php -v
PHP 5.4.16 (cli) (built: Oct 31 2014 12:59:36)
Copyright (c) 1997-2013 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2013 Zend Technologies

Install LAMP on CentOS 7

I hope this article gives you some ideas and essential guidance on how to install LAMP on CentOS 7 / RHEL 7 / Oracle Linux 7.

 

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 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 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/