For this instructional tutorial we'll be exploring the installation and setup of both WordPress v4.0 and the required MySQL database used to support it. However, sometimes we find ourselves in interesting situations which either necessitate or involve remote instances of a MySQL server or database. Quite simply, most of the time remote databases or servers are used for security purposes--specifically security through obscurity and not having all of your eggs in a single basket.
Important: Please ensure that you are correctly modifying usernames, passwords, hostnames and IP addresses to suit your needs. If you do not, this setup will not work correctly for you.
LEMP (Linux, Nginx, MySQL, PHP) is one of the most stable and widely used production/development environments available to developers. Anything Apache can do, Nginx can do much faster. This includes PHP processing via php5-fpm. Setup is simple and only takes a few minutes. In this guide, we'll be working with two separate virtual machines. One with our Nginx and php5-fpm installation and another as our remote MySQL database. Respectively, we'll refer to them as web.dev at 192.168.254.129 and database.dev at 192.168.254.130. With that out of the way, let's get started:
Moving forward, this guide assumes that you've successfully setup Ubuntu 14.04 with an active network connection on both servers (!) as well as installed and configured SSH. So we'll start with our web.dev server which will contain all of our web files to be served to the end user. So we'll remotely connect via ssh and begin:
root@local:~$ ssh zqueal@192.168.254.129
The authenticity of host '192.168.254.129 (192.168.254.129)' can't be established.
ECDSA key fingerprint is d2:66:0f:61:9a:d2:e9:f5:77:95:01:8f:9d:c2:c4:31.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.254.129' (ECDSA) to the list of known hosts.
zqueal@192.168.254.129's password:
zqueal@ubuntu:~$
When on a very fresh installation of any Linux based operating system, the very first thing you should do is update the package list, and upgrade the packages. Most of the time these package updates come with valuable security updates to help keep out malicious third parties. To do so, we execute the following commands in order (please note, that the -y update/install operator is entirely optional and is only to bypass the 'are you sure?' dialogs):
zqueal@ubuntu:~$ sudo apt-get update
[sudo] password for zqueal:
Ign http://security.ubuntu.com trusty-security InRelease
[...]
Fetched 2,212 kB in 2s (769 kB/s)
Reading package lists... Done
and
zqueal@ubuntu:~$ sudo apt-get upgrade -y
Reading package lists... Done
Building dependency tree
Reading state information... Done
Calculating upgrade... Done
The following packages have been kept back:
linux-generic linux-headers-generic linux-image-generic
The following packages will be upgraded:
irqbalance
1 upgraded, 0 newly installed, 0 to remove and 3 not upgraded.
Need to get 30.6 kB of archives.
After this operation, 0 B of additional disk space will be used.
Get:1 http://us.archive.ubuntu.com/ubuntu/ trusty-updates/main irqbalance amd64 1.0.6-2ubuntu0.14.04.1 [30.6 kB]
Fetched 30.6 kB in 0s (304 kB/s)
Preconfiguring packages ...
(Reading database ... 54557 files and directories currently installed.)
Preparing to unpack .../irqbalance_1.0.6-2ubuntu0.14.04.1_amd64.deb ...
irqbalance stop/waiting
Unpacking irqbalance (1.0.6-2ubuntu0.14.04.1) over (1.0.6-2) ...
Processing triggers for man-db (2.6.7.1-1) ...
Processing triggers for ureadahead (0.100.0-16) ...
ureadahead will be reprofiled on next reboot
Setting up irqbalance (1.0.6-2ubuntu0.14.04.1) ...
Installing new version of config file /etc/init/irqbalance.conf ...
irqbalance start/running, process 1583
Processing triggers for ureadahead (0.100.0-16) ...
After our package list and applications are all updated we can proceed to install nginx and php5-fpm which should look a little like this:
zqueal@ubuntu:~$ sudo apt-get install nginx php5-fpm php5-mysql -y
Reading package lists... Done
Building dependency tree
Reading state information... Done
Suggested packages:
php-pear
The following NEW packages will be installed:
nginx php5-fpm php5-mysql
0 upgraded, 2 newly installed, 0 to remove and 3 not upgraded.
Need to get 0 B/2,197 kB of archives.
After this operation, 9,338 kB of additional disk space will be used.
[...]
Awesome! No errors. We'll first--before continuing to setup php5-fpm--ensure that nginx is working correctly. We can do this by actually visiting the IP address of our server:
zqueal@ubuntu:~$ ifconfig | grep "inet addr:"
inet addr:192.168.254.129 Bcast:192.168.254.255 Mask:255.255.255.0
inet addr:127.0.0.1 Mask:255.0.0.0
Enter the web.dev servers public IP address into your local web browser and you should see something very close to:
The last thing we should do before we continue is to make the public web directory much easier to access. Normally the web directory for nginx is located at /usr/share/nginx/html what we want to do is system link it directly to /www or /var/www whichever you prefer. To do this we simply execute the following command:
zqueal@ubuntu:~$ sudo ln -s /usr/share/nginx/html /www
or
zqueal@ubuntu:~$ sudo ln -s /usr/share/nginx/html /var/www
We can confirm that the system link works by viewing the root level directory / and using ls -l:
zqueal@ubuntu:/$ ls -l | grep www
lrwxrwxrwx 1 root root 21 Sep 4 01:03 www -> /usr/share/nginx/html
Very cool, we're just about half way done with our web.dev server. Next we need to enable php5-fpm support for nginx to enable it to process php files--but first we'll a common security measure by disabling cgi.fix_pathinfo in our /etc/php5/fpm/php.ini file. This setting is not only enabled by default, however, it's extremely insecure--instructing PHP to execute the closest common file it's able to find given a request, even if it doesn't match exactly. The simple thought itself sends chills down developers backs.
So lets edit the file:
zqueal@ubuntu:/$ sudo nano /etc/php5/fpm/php.ini
Within nano we can quickly find the line we're looking for by pressing ctrl+w and typing in cgi.fix_pathinfo.
Press enter to search for the term and we are brought right to the correct place in the configuration file. Simply remote the prefixed ; before the cgi.fix_pathinfo=1 property and change the 1 to a 0. Once changed ctrl-x to save. When prompted, select y to save our modified buffer and enter to save the file as php.ini.
Radical! Let's keep up the momentum and continue by finishing up our web.dev setup by configuring nginx to use php5-fpm. To do this we need to edit the default nginx website configuration by copying and pasting the following configuration into the default configuration file and replacing the current contents. The easiest way to do this is to empty the file, and paste our configuration.
zqueal@ubuntu:/www$ cd /etc/nginx/sites-available/
zqueal@ubuntu:/www$ sudo cat /dev/null > default
zqueal@ubuntu:/www$ sudo nano default
During this step, the file should be completely empty. Please note, if you don't feel comfortable removing the contents of the default configuration file, then please make a backup first by copying the default configuration file.
zqueal@ubuntu:$ sudo cp /etc/nginx/sites-available/default /etc/nginx/sites-available/default.bak
If not, simply paste the following contents into your blank configuration file--and modifying the server_name with your servers fully qualified domain name (example.com)--then save by ctrl-x, y to save the buffer, and hitting enter to save as the original default file name.
server {
listen 80 default_server;
root /usr/share/nginx/html;
index index.php index.html index.htm;
server_name web.dev; # FQDN
location / {
try_files $uri $uri/ =404;
}
location ~ \.php$ {
try_files $uri =404;
fastcgi_split_path_info ^(.+\.php)(/.+)$;
fastcgi_pass unix:/var/run/php5-fpm.sock;
fastcgi_index index.php;
include fastcgi_params;
}
location ~ /\.ht {
deny all;
}
}
Restart nginx and php5-fpm.
zqueal@ubuntu:/www$ sudo service nginx restart
* Restarting nginx nginx [ OK ]
zqueal@ubuntu:/www$ sudo service php5-fpm restart
php5-fpm stop/waiting
php5-fpm start/running, process 2720
We now need to change the owner of our /www directory so we have write permissions without having to use sudo.
zqueal@ubuntu:~$ cd -P /www
zqueal@ubuntu:/usr/share/nginx/html$ cd ..
zqueal@ubuntu:/usr/share/nginx$ sudo chown -R zqueal:zqueal html/
We're not ready to test nginx and php5-fpm! Enter your web facing directory, move the current index.html file to index.php and empty it's contents. Then we simply add a very simple phpinfo() function to view our php5-fpm system variables.
zqueal@ubuntu:~$ cd /www
zqueal@ubuntu:/www$ mv index.html index.php
zqueal@ubuntu:/www$ sudo cat /dev/null > index.php
zqueal@ubuntu:/www$ nano index.php
Ensure index.php looks like the following image, and save the file as we've done before. ctrl-x, y to save the buffer, and enter to save as index.php.
If everything went according to plan you should see php5-fpm setup information, and it will look like the following:
Groovy! Now that our server is setup, we'll do one last thing before moving on--downloading WordPress to our web directory so we can run back to web.dev and finish the installation and configuration once we've setup MySQL on our database.dev server.
zqueal@ubuntu:~$cd /tmp
zqueal@ubuntu:/tmp$ wget https://wordpress.org/latest.tar.gz
[...]
zqueal@ubuntu:/tmp$ tar xvf latest.tar.gz
[...]
zqueal@ubuntu:/tmp$ cd wordpress/
zqueal@ubuntu:/tmp$ sudo cp -R * /www
zqueal@ubuntu:/tmp$ cd /www
zqueal@ubuntu:/tmp$ cp wp-config-example.php wp-config.php
zqueal@ubuntu:/tmp$ chmod 0777 wp-config.php
Respectively, these commands open up a temporary space (cleaned and removed after reboot) and download the newest version of WordPress to extract, then we move the appropriate files into our /www web facing directory. Additionally we need to rename the example configuration file and make it so the WordPress configuration will be able to edit it later on. If we visit the IP address of our server now, we're greeted with the WordPress installation page.
Finally it's time to setup our MySQL server. Let's begin by connecting:
root@local:~$ ssh zqueal@192.168.254.130
The authenticity of host '192.168.254.130 (192.168.254.130)' can't be established.
ECDSA key fingerprint is 37:e1:ae:d3:39:c1:34:b0:40:94:3b:44:22:3f:38:cc.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.254.130' (ECDSA) to the list of known hosts.
zqueal@192.168.254.130's password:
zqueal@ubuntu:~$
Just the same as our previous web server we need to update the package list and upgrade them and then install mysql-server:
zqueal@ubuntu:~$ sudo apt-get update
[...]
zqueal@ubuntu:~$ sudo apt-get upgrade -y
[...]
zqueal@ubuntu:~$ sudo apt-get install mysql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following NEW packages will be installed:
mysql-server
0 upgraded, 1 newly installed, 0 to remove and 3 not upgraded.
Need to get 0 B/12.4 kB of archives.
After this operation, 129 kB of additional disk space will be used.
Selecting previously unselected package mysql-server.
(Reading database ... 54703 files and directories currently installed.)
Preparing to unpack .../mysql-server_5.5.38-0ubuntu0.14.04.1_all.deb ...
Unpacking mysql-server (5.5.38-0ubuntu0.14.04.1) ...
Setting up mysql-server (5.5.38-0ubuntu0.14.04.1) ...
The installation will ask us what we'd like the MySQL root password to be:
Enter your password, then again to confirm. Moving right along, we need to instruct MySQL to set its own directory tree and then enter the mysql_secure_installation which will guide us through the rest of the MySQL setup process.
zqueal@ubuntu:~$ sudo mysql_install_db
Installing MySQL system tables...
140904 18:51:28 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
OK
Filling help tables...
140904 18:51:28 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h ubuntu password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysql.com/
The mysql_secure_installation script will ask us a bunch of questions such as the current password for root (MySQL root, not Ubuntu root), if you'd like to change the root password, disallow root remote connections, etc.
zqueal@ubuntu:~$ sudo 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.
You already have a root password set, so you can safely answer 'n'.
Change the root password? [Y/n] n
... skipping.
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...
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 MySQL
installation should now be secure.
Thanks for using MySQL!
Condensed for easy reading:
Change the root password? [Y/n] n
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y
The final step before actually installing WordPress is to create a new user, aside from root, that we can use to remotely connect. We first need to connect to MySQL via command line to be able to create a new user:
zqueal@ubuntu:~$ mysql -u root -p
[...]
mysql>
And we'll execute some commands to create our database and privilege our new user:
mysql> CREATE DATABASE IF NOT EXISTS wordpress;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE USER 'wordpress'@'localhost' IDENTIFIED BY 'wordpress';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpress'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER 'wordpress'@'%' IDENTIFIED BY 'wordpress';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpress'@'%';
Query OK, 0 rows affected (0.00 sec)
To be able to connect from a remote server, however, we need to edit our my.cnf configuration file that comes with MySQL.
zqueal@ubuntu:~$ sudo nano /etc/mysql/my.cnf
Use the ctrl-w search command to search for bind-address and comment it out. With that property still in our configuration we will be unable to connect to MySQL remotely. Save the file, ctrl-x, y to save the buffer, and enter to save as my.cnf. Quickly restart the MySQL Daemon:
zqueal@ubuntu:~$ sudo service mysql restart
mysql stop/waiting
mysql start/running, process 17168
Switching back to web.dev execute the following command to ensure that everything is setup correctly (don't forget to change the IP address for your working environment):
zqueal@ubuntu:~$ mysql -h 192.168.254.130 -u wordpress -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.5.38-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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| wordpress |
+--------------------+
2 rows in set (0.00 sec)
At this point, the last thing to do is to navigate to our web.dev address in our browser and quickly finish the WordPress installation! Your page should look relatively similar to the following image, with the exception of usernames and passwords (hopefully), and the IP address of the database host. It's always a good idea to use table prefixes just encase you'd like to install another WordPress and still only use one database.
Click "Submit" to continue. If you run into a small error (following image) don't panic! It's an easy fix and only means the wp-config.php file was not correctly permissed. Simply copy the contents from the browser and paste it into the /www/wp-config.php file and click "Run the install."
Oh boy! Once the MySQL database is populated successfully you should see a configuration page. Blow through it with your desired values and you're done. See how simple that was?
Fully completed installation:
During this guide we've successfully installed nginx, php5-fpm and a remote MySQL server, database, and user which is used to connect from a remote server for added security. If you have any questions or errors throughout the installation please start over and remember to substitute usernames, passwords, hostnames, and IP addresses for those that are congruent with your own setup.