Posted on by Robin Jupp

Setting up MariaDB for WordPress 4.9.4 on OpenBSD 6.2 part3

The Bitter DB …

Welcome back to installing WordPress 4.9.4 on OpenBSD 6.2.  In this installment we are going to install MariaDB for WordPress to use as a data store.  To be successful we will need to configure our Mariadb web access user to run as a socket in our chrooted environment, and secure the database against malicious users. To that end our goals are too:

  1. Install the MariaDB -10.0.32v1 required by WordPress
  2. Configure PHP to use the MariaDB libraries 
  3. Configure MariaDB to run correctly in a Chroot’d environment that httpd uses.

To test our MariaDB and httpds ability to talk to it through the php socket we will need to set up our testing environment.  We will be using two terminal sessions in two separate windows today:

  1. A “VPS” in which we will be starting httpd sessions in debug mode 
  2. A “Maintenance” widow to be used to make any changes to your configuration files etc.

Open a ssh connection using you Client window to your VPS as our beloved ”bob” and su to root.  We will need root privileges to make our changes.

Now we will need to make some test files.  The first test file will make sure curl is functioning through the php socket by making a connection to WordPress.org.  In your Maintenance window using your favorite editor create  file in /var/www/htdocs called test_curl.php.

Vi /var/www/htdocs/test_curl.php

Paste the following into the file.

<?php
$_h = curl_init();
curl_setopt($_h, CURLOPT_HEADER, 1);
curl_setopt($_h, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($_h, CURLOPT_HTTPGET, 1);
//curl_setopt($_h, CURLOPT_URL,'https://jetpack.wordpress.com' );
curl_setopt($_h, CURLOPT_URL,'https://planet.wordpress.org' );
curl_setopt($_h, CURLOPT_DNS_USE_GLOBAL_CACHE, false );
curl_setopt($_h, CURLOPT_DNS_CACHE_TIMEOUT, 2 );
var_dump(curl_exec($_h));
var_dump(curl_getinfo($_h));
var_dump(curl_error($_h)); 
?>

And save the file.  As you can see, it instantiates a curl object, loads some basic properties, executes the connection and dumps the results of the connection, the configuration details and any errors.

The second test file we will create test the MariaDB connection through the PHP socket.   In your Maintenance window using your favorite editor create  file in /var/www/htdocs called test_mysql.php.

Vi /var/www/htdocs/test_mysql.php

Paste the following into the file.

<?php
$user = “’your mysql user’”;
$pwd = “your password”;
$host = "127.0.0.1";
$db = “your database name“;
$link = mysqli_connect("$host", "$user", "$pwd" , "$db");
if (!$link) {
   echo "Error: Unable to connect to MySQL using user as $user and password as $pwd ." . PHP_EOL;
   echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
   echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
   $time = time();
   $hash = md5(“your email”  . time() . mt_rand() );
   echo "hash = $hash" . PHP_EOL;
   echo "time = $time" . PHP_EOL;
   exit;
}

echo "Success: A proper connection to MySQL was made using user as $user and password as $pwd! The my_db database is great." . PHP_EOL;
echo "Host information: " . mysqli_get_host_info($link) . PHP_EOL;
$time = time();
$hash = md5( $value . time() . mt_rand() );
echo "hash = $hash" . PHP_EOL;
echo "time = $time" . PHP_EOL;
mysqli_close($link);
?>

You will need to update the “your mysql user”, “your password”, “your database name”, and “your email” with valid values once you have that information before we can test with this file.

Lets install the MariaDB.  Now that we have the PKG system setup, we just need to query our Mirror to see if they have the MariaDB we want to install.  But first lets refresh our memory on what we have installed from Setting up WordPress on OpenBSD 6.2 part2 by using pkg_info

pkg_info

Returns:

colorls-6.0         ls(1) that can use color to display file attributes

curl-7.55.1         get files from FTP, Gopher, HTTP or HTTPS servers

femail-1.0p1        simple SMTP client

femail-chroot-1.0p2 simple SMTP client for chrooted web servers

gettext-0.19.8.1p1  GNU gettext runtime libraries and programs

jpeg-1.5.1p0v0      SIMD-accelerated JPEG codec replacement of libjpeg

libiconv-1.14p3     character set conversion library

libxml-2.9.5        XML parsing library

mariadb-client-10.0.32v1 multithreaded SQL database (client)

nghttp2-1.26.0      library for HTTP/2

php-7.0.23          server-side HTML-embedded scripting language

php-curl-7.0.23     curl URL library extensions for php5

php-gd-7.0.23       image manipulation extensions for php5

php-mysqli-7.0.23   mysql database access extensions for php5

png-1.6.31          library for manipulating PNG images

quirks-2.367        exceptions to pkg_add rules

xz-5.2.3p0          LZMA compression and decompression tools

But what’s this?  The MariaDB is already installed?  Well, not the part we need.  What your seeing is the mariadb-client-10.0.32v1 multithreaded SQL database (client) which was an install requirement for “php-mysqli-7.0.23   mysql database access extensions for php5.“(See pkg_info section in the Setting up WordPress on OpenBSD 6.2 Part 2 on install requirements)  We need the “server” component for the mariadb as well. Lets check for that on the mirror:

pkg_info -Q mariadb

Returns:

mariadb-client-10.0.32v1 (installed)

mariadb-server-10.0.32v1

mariadb-tests-10.0.32v1

Lets install the matching server component

pkg_add mariadb-server-10.0.32v1

As you saw, there are a lot of components to the mariadb install.  You are also encouraged to read the /usr/local/share/doc/pkg-readmes/ for the mariadb-server-10.0.32v1 install.  Do so.  One of the first things you are asked to do in the /usr/local/share/doc/pkg-readmes/mariadb-server-10.0.32v1 is to run the /usr/local/bin/mysql_install_db in order to create the default database.

/usr/local/bin/mysql_install_db

Unfortunately, “/usr/local/share/doc/pkg-readmes/mariadb-server-10.0.32v1” makes no mention of /usr/local/bin/mysql_secure_installation which runs you through some basic security setup needed for mariadb to run on a production server.  Good thing the /usr/local/bin/mysql_install_db does.  But before we can do that process we need to enable the mysql daemon.

rcctl enable mysqld

Now start the mysqld daemon

rcctl start mysqld

Now execute the mysql_secure_installation 

/usr/local/bin/mysql_secure_installation

You are now presented with a series of questions:

  1. “Enter the current password for root (enter for none):” Press Enter.
  2. “Set root password?” [Y/n]” Use your password vault to generate and store a password for the mysql root login, then enter it here and hit enter.
  3. “Remove anonymous users? [Y/n]”  Enter “Y”, we do not want anonymous users on our system.
  4. “Disallow root login remotely? [Y,n]” Enter “Y”, never let root have direct access to anything. Always login in with a low level user and then elevate your privileges.
  5. “Remove test database and access to it? [Y/n]” Enter “Y”, test databases are for a test environment.
  6. “Reload privilege tables now? [Y,n]” Enter “Y”, this activates all of our changes immediately.

You should now be at the command prompt.  Lets test your “root” login by using the mysql client

mysql -u root -p <your root password>

You should have a mysql prompt if everything went as planned.  Now lets exit back out to the terminal by typing quit.

quit

Now let tell php all about mysql by editing the /etc/php-7.0.ini…

vi /etc/php-7.0.ini

…and scroll down to the extension section, it looks a lot like this:

;;;;;;;;;;;;;;;;;;;;;;

; Dynamic Extensions ;

;;;;;;;;;;;;;;;;;;;;;;

; If you wish to have an extension loaded automatically, use the following

; syntax:

;

;   extension=modulename.extension

;

; For example, on Windows:

;

;   extension=msql.dll

;

; … or under UNIX:

;

;   extension=msql.so

;

; … or with a path:

;

;   extension=/path/to/extension/msql.so

;

; If you only provide the name of the extension, PHP will look for it in its

; default extension directory.

;

; Windows Extensions

; Note that ODBC support is built in, so no dll is needed for it.

; Note that many DLL files are located in the extensions/ (PHP 4) ext/ (PHP 5+)

; extension folders as well as the separate PECL DLL download (PHP 5+).

; Be sure to appropriately set the extension_dir directive.

;

;extension=php_bz2.dll

;extension=php_curl.dll

;extension=php_fileinfo.dll

;extension=php_ftp.dll

;extension=php_gd2.dll

…

Verify the path to the mysqli.so using find.

find / -name mysqli.so

Add the path returned by Find at the end of the extension section as in this example:

extension=/usr/local/lib/php-7.0/modules/mysqli.so

Save the file.  Now lets do a little testing.  In your VPS window make sure your running as root.  Now lets start or httpd in debug (-d) mode with verbosity turned to medium (-vv) using our httpd_config.test (-f /etc/httd_config.test)

httpd -d -vv -f /etc/httpd_config.test

Open up your web browser and point it to http://<IP address of your server>.   You should see the out put of the phpinfo() function.  Notice that there is now a section called mysqli curtesy of mysqli.so.

Type ctrl+c to shutdown the httpd daemon.

Lets add a database and a user to mysql for WordPress to use.  At you terminal command prompt login as root to mysql

mysql -u root -p <your root password>

Lets create a database called wp_website1.  Type the following:

Create database wp_website1;

Now lets create a user that can access wp_website1 for WordPress to use.  First generate a pass word in your password vault and save it then use it in the following query.  Type the following:

Create user ‘wp_user’@‘127.0.0.1’ identified by ‘your_password_from_vault’;

Do not use ‘localhost’, use ‘127.0.0.1’ to force mysql to use the database socket for this user.  The /var/run/mysql/mysql.sock is the only way for our chroot’d environment to talk to mysql.

Lets grant wp_user rights to do things on wp_website1

Grant all on wp_website1.* to ‘wp_user’@‘127.0.0.1’;

Now exit mysql by typing quit.

Lets edit the /var/www/htdocs/test_mysql.php file and enter in your wp_user, password, database name and email information; being mindful that the user has both double and single quotes around the name and that some meta-characters like “$” need to be escaped with a leading “\” if they are used in the password.

Great, lets test our database connection.  In your VPS window start up you httpd daemon.  

httpd -d -vv -f /etc/httpd_config.test

In Your Maintenance window restart php and mysql

rcctl restart mysqld
rcctl restart php70_fpm

Now in you web browser enter the following URL http://“your IP address”/test_mysql.php.  You should see something like the following:

Success: A proper connection to MySQL was made using user as ‘wp_user’ and password as <your password> The my_db database is great. Host information: 127.0.0.1 via TCP/IP hash = f133bb3bf8278ec4eb48ae1fdff56382 time = 1521508293

While we are at it lets finish up the curl component.  Curl is installed, but since it operates in a chroot it needs a few pieces moved into /var/www in order to be able to grab external url’s. Those pieces are the following:

/etc/resolv.conf

/etc/services

/etc/ssl

Remember we are in a chroot environment and the root for that environment is /var/www.  We need to keep the same directory relationship here so we need to add /var/www to each destination. We will do that using mkdir and cp:

mkdir -p /var/www/etc
mkdir -p /var/www/etc/ssl/lib
mkdir -p /var/www/etc/ssl/private
cp /etc/resolv.conf /var/www/etc/resolv.conf
cp /etc/services /var/www/etc/services
cp -R /etc/ssl /var/www/etc/

Now we can test curl.  In your VPS window start up you httpd daemon.  

httpd -d -vv -f /etc/httpd_config.test

In Your Maintenance window restart php.

rcctl restart php70_fpm

Now in you web browser enter the following URL http://“your IP address”/test_curl.php.  You should see something like the following:

string(130999) “HTTP/1.1 200 OK Server: nginx Date: Tue, 20 Mar 2018 18:32:01 GMT Content-Type: text/html Content-Length: 130714 Connection: keep-alive Vary: Accept-Encoding Last-Modified: Tue, 20 Mar 2018 18:30:27 GMT X-Frame-Options: SAMEORIGIN X-nc: EXPIRED ord 1 Accept-Ranges: bytes

And the web page for WordPress.org should display blow that.

Ok lets shut down our services to secure our server until next time:

ctrl+c in your VPS window to stop our debug httpd.
rcctl stop php70_fpm
rcctl stop mysql

Not bad!  Lets summarize our achievements today…

  1. We created test scripts to verify php-curl and MariaDB functionality through php and httpd
  2. We installed MariaDB, and setup a user for WordPress to use with the appropriate permissions and socket access through php
  3. Finished setting up the curl component for php
  4. And verified base MariaDB connectivity and curl functionality with our scripts.

Another good days work.  Stay tuned, much more to come.

Leave a Reply