This is the next chapter in the series – Scalable, Highly Available, Secure WordPress on Azure. 

This exercise will be divided into two tasks.

1. Copying the install files on-to the database server.
2. Installation and configuration of MySQL databse on the database server

Copying Installation Files

It is important that the batch file ‘scopy.bat‘ and ‘sqlCommands.sql‘ file are properly configured with IP address of the database server and password of remoteuser before carrying out the next step. How to configure these two files was described in detail in the previous chapter.

To copy the files across to the database server, I open a Command promt, navigate to the local ‘dbserver‘ folder and execute the script ‘scopy.bat‘.

The script copies the installation files to the default folder of the user ‘webuser‘ on the database server.

Q: Why not execute scripts remotely on the database server?

A: The reason for remote login on-to the database server is the interactive nature of MySQL database. During the installation of MySQL database, it prompts couple of screens asking for confirmation of user passwords. I’ve not managed to replicate the installation in an non-interactive fashion. This is definitely one of the items that will be place in the future to-do/investigate tasks list.

Installation and Configuration of MySQL DB

After the successful run of ‘scopy.bat‘ script locally, I’ll login on-to the Linux database server machine. You can use any terminal program like ‘putty’, ‘ssh’to login to the Linux server. On my machine, I am using vanilla ssh to remotely login to the server and execute the following command . The IP address used was the last public IP address of the database server. This is displayed as the last output when the previous script was executed. The IP address for your configuration would be different.

>ssh webuser@<<public IP address of database server>> -p 22
...

Login to the database server with the user ‘webuser‘ that was specified during the VM creation and using the passcode that was specified when the secure keys were generated using the ssh-keygen application – see this chapter to recap on those details.

Once you’ve logged in, execute the script ‘. /install.sh‘ from the terminal prompt.

webuser@backend-databaseserver-v2:~$ ./install.sh 

In essence the ‘install.sh‘ script does the following

1. Updates and upgrades the server (apt-get update) & (apt-get upgrade -y)
2. Installes MySQL server (apt-get install -y mysql-server-5.7) and hardens the installation using ‘mysql_secure_installation‘. It will ask for various confirmation during this process of installation.
3. Restarts MySQL server (systemctl start mysql) and configures it to start on reboot (systemctl enable mysql).
4. Configures MySQL server by executing the commands present in the file SqlCommands.SQL
   • Script creates a database ‘wordpress‘
   • Creates a remote user ‘remotewpuser‘ and allows it to access the database from front-end server that we’d create later
   • Creates a local user ‘wpuser‘ and grants permissions for it to access the WordPress database.
5. By default MySQL only allows access from localhost (127.0.0.1). Change the configuration file (/etc/mysql/mysql.conf.d/mysqld.cnf) by replacing keyword ‘127.0.0.1’ to the IP address of the database server. This replacement is done by the python script ‘py_file_replace_str.py‘
6. Restart the MySQL
7. Executes the ‘ufw‘ command to open firewall for MySQL to access traffic.

#!/bin/bash
# This script should be copied to webuser, chmod + x 
# and then executed to check sudo permissions grant
echo '=========== default update and upgrade ======================='
sudo apt-get update 
sudo apt-get upgrade -y
#sudo apt-get install debconf-utils
echo '===========   mysqlinstallation        ======================='
sudo apt-get install -y mysql-server-5.7
mysql_secure_installation
echo '===========   mysqlinstallation        ======================='
sudo systemctl start mysql
sudo systemctl enable mysql
echo '===========   creating databasee       ======================='
mysql -u root -p < sqlCommands.sql
echo '===========  test local user access   ========================'
echo '===========  substituting local access   ====================='
sudo python py_file_replace_str.py /etc/mysql/mysql.conf.d/mysqld.cnf "127.0.0.1" "10.0.2.4"
echo '===========  restart mysql access   =========================='
sudo systemctl restart mysql
echo '===========  Allow right ports      =========================='
sudo ufw allow mysql
echo '===========  Check some answers      ========================='
sudo netstat -plunt | grep mysqld
echo '....done'

After the successful installation of both the scripts, you should now have MySQL database server ready to accept and serve request. Close the remote connection by executing ‘exit’ at the terminal prompt.

Next Steps

In the next section we’ll configure the two front-end webservers and establish end-2-end connectivity. Can’t wait. 🙂