Tutorials

Install WordPress with MariaDB in SSL replication in VirtualBox.

In this guide I am going to setup a WordPress installation that is running on Nginx with php-fpm, this will connect to a MariaDB database that is running with SSL replication. I have also configured Nginx in a way that “wp-login” and “wp-admin” will go over SSL.

I choose to use Fedora for this guide but with some minor tweaks it should be possible to do the same with any Linux version.! 🙂

To start create 3 virtual machines in VirtualBox with a bridged network or use my “How to SSH into a VirtualBox Linux guest from your host machine” guide to setup so you can ssh to each machine.

I created the following servers in VirtualBox:
db01 = 10.0.0.1 (40gb disk, 2gb ram, 2 core)
db02 = 10.0.0.2 (40gb disk, 2gb ram, 2 core)
blog01 = 10.0.0.3 (40gb disk, 2gb ram, 2 core)

Please be aware that you can run WordPress itself can easily run on a 1 core server with 512mb of ram with a small MariaDB installation, the purpose of using three VirtualBox instances is to have everything separated and to show how to make MariaDB have a SSL replication between different servers.

Lets get started! 🙂

Step 1: Create the following directory on DB01 and DB02
$ sudo mkdir -p /etc/mariadb/ssl
WHEN YOU CREATE THE CERTIFICATES MARK MY CHANGES IN THE INFORMATION YOU GET REQUESTED! DO NOT USE THE SAME COMMON NAME THREE TIMES!

Step 2: Create CA certificate on DB01 in your home directory (make sure to check the fill in example)

$ sudo openssl genrsa 4096 > ca-key.pem
$ sudo openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem

What did I fill in for Step 2:

Country Name (2 letter code) [XX]:SE
State or Province Name (full name) []:Stockholm
Locality Name (eg, city) [Default City]:Stockholm
Organization Name (eg, company) [Default Company Ltd]:Ulyaoth
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:ulyaoth.net
Email Address []:[email protected]

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

Step 3: Create server certificate on DB01 in your home directory (make sure to check the fill in example)

$ sudo openssl req -newkey rsa:4096 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
$ sudo openssl rsa -in server-key.pem -out server-key.pem
$ sudo openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

What did I fill in for Step 3:

Country Name (2 letter code) [XX]:SE
State or Province Name (full name) []:Stockholm
Locality Name (eg, city) [Default City]:Stockholm
Organization Name (eg, company) [Default Company Ltd]:Ulyaoth
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []: MariaDB Server
Email Address []:[email protected]

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

Step 4: Create client certificate on DB01 in your home directory (make sure to check the fill in example)

$ sudo openssl req -newkey rsa:4096 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
$ sudo openssl rsa -in client-key.pem -out client-key.pem
$ sudo openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

What did I fill in for Step 4:

Country Name (2 letter code) [XX]:SE
State or Province Name (full name) []:Stockholm
Locality Name (eg, city) [Default City]:Stockholm
Organization Name (eg, company) [Default Company Ltd]:Ulyaoth
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:u MariaDB Client
Email Address []:[email protected]

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

Step 5: Verify the certificates on DB01 in your home directory
$ sudo openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

Result should be:

server-cert.pem: OK
client-cert.pem: OK

Step 6: Copy the certificates from your home dir to the ssl dir on DB01
$ sudo cp *.pem /etc/mariadb/ssl/

Step 7: Copy the certificates from db01 to db02
$ sudo scp /etc/mariadb/ssl/*.pem [email protected]:/etc/mariadb/ssl/

Step 8: Install the MariaDB database on DB01 and DB02.
$ sudo yum install mariadb mariadb-server -y

Step 9: Copy the example config for a small MariaDB database to real config location on DB01 and DB02
$ sudo cp /usr/share/mysql/my-small.cnf /etc/my.cnf

Step 10: Open the MariaDB config file and perform the below changes on DB01
$ sudo vi /etc/my.cnf

ADD under [client]:

# SSL
ssl-ca=/etc/mariadb/ssl/ca-cert.pem

ADD under [mysqld]:

# SSL
ssl-ca=/etc/mariadb/ssl/ca-cert.pem
ssl-cert=/etc/mariadb/ssl/server-cert.pem
ssl-key=/etc/mariadb/ssl/server-key.pem

CHANGE under [mysqld]:

#log-bin=mysql-bin
#binlog_format=mixed

to

log-bin=mysql-bin
binlog_format=mixed

Step 11: Open the MariaDB config file and perform the below changes on DB02
$ sudo vi /etc/my.cnf

ADD under [client]:

# SSL
ssl-ca=/etc/mariadb/ssl/ca-cert.pem

ADD under [mysqld]:

# SSL
ssl-ca=/etc/mariadb/ssl/ca-cert.pem
ssl-cert=/etc/mariadb/ssl/server-cert.pem
ssl-key=/etc/mariadb/ssl/server-key.pem

CHANGE under [mysqld]:

server-id = 1
#log-bin=mysql-bin
#binlog_format=mixed

to

server-id = 2
log-bin=mysql-bin
binlog_format=mixed

Step 12: Start the MariaDB database on DB01 and DB02
$ sudo systemctl start mariadb.service

Step 13: Place the MariaDB database service as autostart on boot on DB01 and DB02
$ sudo systemctl enable mariadb.service

Step 14: run the MariaDB secure installation script on DB01 and DB02
$ sudo /usr/bin/mysql_secure_installation

I would recommend to do the steps I did below:

Enter current password for root (enter for none): (ENTER)
Set root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
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

Step 15: Login to the MariaDB database on DB01:
$ sudo mysql -uroot -p

Step 16: Create the replication user from within the MariaDB client

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'pass' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.2' IDENTIFIED BY 'pass' REQUIRE SSL;
FLUSH PRIVILEGES;</code>
Make sure to adjust the ip number to the one of your DB02, for me the % option does not always work unfortunately so that is why I also added the ip number specifically.

<strong>Step 17:</strong> Check that MariaDB correctly has accepted the ssl on DB01
<code>show global variables like '%ssl%';

Result should be:

+---------------+----------------------------------+
| Variable_name | Value |
+---------------+----------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/mariadb/ssl/ca-cert.pem |
| ssl_capath | |
| ssl_cert | /etc/mariadb/ssl/server-cert.pem |
| ssl_cipher | |
| ssl_key | /etc/mariadb/ssl/server-key.pem |
+---------------+----------------------------------+
7 rows in set (0.00 sec)

Step 18: Get the bin log information from MariaDB on DB01 (Make sure to write this down you need it later on)

SHOW MASTER STATUS;
For me this shows:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 245 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Step 19: Login to the MariaDB database on DB02
$ sudo mysql -uroot -p

Step 20: Check that MariaDB correctly has accepted the ssl on DB02 from within the MariaDB client.

show global variables like '%ssl%';
Result should be again:
+---------------+----------------------------------+
| Variable_name | Value |
+---------------+----------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/mariadb/ssl/ca-cert.pem |
| ssl_capath | |
| ssl_cert | /etc/mariadb/ssl/server-cert.pem |
| ssl_cipher | |
| ssl_key | /etc/mariadb/ssl/server-key.pem |
+---------------+----------------------------------+
7 rows in set (0.00 sec)

Step 21: Give MariaDB the master information it knows this server will be a slave on DB02

CHANGE MASTER TO MASTER_HOST='10.0.0.1', MASTER_USER='repl', MASTER_PASSWORD='pass', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245, MASTER_SSL=1, MASTER_SSL_CA = '/etc/mariadb/ssl/ca-cert.pem', MASTER_SSL_CERT = '/etc/mariadb/ssl/client-cert.pem', MASTER_SSL_KEY = '/etc/mariadb/ssl/client-key.pem';

Make sure to change the MASTER_HOST ip number to the host name or ip of your DB01 server, also be aware the above is one long line not separate.

Step 22: Start MariaDB as a slave on DB02 from within the MariaDB client.
start slave;

Step 23: Check that you have a successful replication on DB02 from within the MariaDB client.
SHOW SLAVE STATUS\G
The result should be somethings as below, just make sure you have no errors:

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 612
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 896
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 612
Relay_Log_Space: 1194
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/mariadb/ssl/ca-cert.pem
Master_SSL_CA_Path: /etc/mariadb/ssl
Master_SSL_Cert: /etc/mariadb/ssl/client-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /etc/mariadb/ssl/client-key.pem
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

If you happen to see a error like this:
Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 86400 message: Can't connect to MySQL server on '10.0.0.1' (113)

You might need to apply firewall rules on both DB01 and DB02, I had to do this:

$ sudo firewall-cmd --permanent --zone=public --add-service=mysql
$ sudo firewall-cmd --permanent --zone=external --add-service=mysql
$ sudo firewall-cmd --permanent --zone=public --add-port=3306/tcp
$ sudo firewall-cmd --permanent --zone=external --add-port=3306/tcp

Lets test now the replication just to double check by creating a user we will use later on for WordPress.

Step 24: In the MariaDB client that should be still open on DB01 create the following user and database

CREATE DATABASE wordpress;
GRANT ALL PRIVILEGES ON wordpress.* TO "blogninja"@"%" IDENTIFIED BY "password";
GRANT ALL PRIVILEGES ON wordpress.* TO "blogninja"@"localhost" IDENTIFIED BY "password";
GRANT ALL PRIVILEGES ON wordpress.* TO "blogninja"@"10.0.0.3" IDENTIFIED BY "password";
FLUSH PRIVILEGES;

If your client was closed you can login with “sudo mysql -uroot -p” again.

Step 25: Close the MariaDB client on DB01 and DB02
exit

Step 26: Check that the replication worked and that SSL works correctly on DB01 and DB02
$ sudo mysql -u wordpress -ppass -sss -e '\s' | grep SSL
I should show the following on DB01 and DB02:
SSL: Cipher in use is DHE-RSA-AES256-SHA

As you can see the user “wordpress” was successfully replicated to DB02.

Now that we have a successful MariaDB SSL replication and support for SSL login we will move forward to the WordPress, Nginx and php-fpm installation.

Step 27: Install the following packages on BLOG01
$ sudo yum install nginx tar wget vim policycoreutils-python zip php-fpm php-common php-pecl-apc php-cli php-pear php-pdo php-mysqlnd php-pgsql php-pecl-mongo php-pecl-memcache php-pecl-memcached php-gd php-mbstring php-mcrypt php-xml -y

Step 28: Create the following directories on BLOG01

$ sudo mkdir -p /var/log/nginx/wordpress
$ sudo mkdir -p /usr/share/nginx/wordpress/public
$ sudo mkdir -p /etc/nginx/sites-available
$ sudo mkdir -p /etc/nginx/sites-enabled
$ sudo mkdir -p /var/lib/php/wordpresssession
$ sudo mkdir -p /etc/nginx/ssl

step 29: Create the Nginx CA ssl certificates on BLOG01 in your home dir make sure to check the fill in example)

$ sudo openssl genrsa 4096 > ca-key.pem
$ sudo openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem

What I did fill on for Step 28:
Country Name (2 letter code) [XX]:SE
State or Province Name (full name) []:Stockholm
Locality Name (eg, city) [Default City]:Stockholm
Organization Name (eg, company) [Default Company Ltd]:Ulyaoth
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:ulyaoth.net
Email Address []:[email protected]

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

Step 30: Create a Nginx server certificate on BLOG01 in your home directory (make sure to check the fill in example)

$ sudo openssl req -newkey rsa:4096 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
$ sudo openssl rsa -in server-key.pem -out server-key.pem
$ sudo openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

What I did fill in for Step 30:
Country Name (2 letter code) [XX]:SE
State or Province Name (full name) []:Stockholm
Locality Name (eg, city) [Default City]:Stockholm
Organization Name (eg, company) [Default Company Ltd]:Ulyaoth
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []: Nginx
Email Address []:[email protected]

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

Step 31: Verify the certificates on BLOG01 in your home directory
$ sudo openssl verify -CAfile ca-cert.pem server-cert.pem
Result should be:
server-cert.pem: OK

Step 32: Copy the certificates from your home dir to the SSL dir on DB01
$ sudo cp *.pem /etc/nginx/ssl/

Step 33: Delete the current nginx.conf on BLOG01
$ sudo rm -rf /etc/nginx/nginx.conf

Step 34: Go to the nginx directory on BLOG01
$ cd /etc/nginx/

Step 35: wget a new nginx.conf on BLOG01
$ sudo wget http://trash.ulyaoth.net/trash/nginx/conf/nginx.conf

Step 36: Open the new nginx.conf and change appropriatly on BLOG01
$ sudo vim /etc/nginx/nginx.conf
Change the following line to fit your cpu amount
worker_processes 1;

Since I have two virtual CPUs I am using “2″ Just save the file after you added your changes. You might want to look at the other options also if you use a hardware server you probably would want to increase the allowance of open files.

Step 37: Go to the Nginx vhost directory
$ cd /etc/nginx/sites-available/

Step 38: wget the WordPress vhost file on BLOG01
$ sudo wget http://trash.ulyaoth.net/trash/nginx/vhost/wordpress

Step 39: Open the WordPress file
$ sudo vim /etc/nginx/sites-available/wordpress

Step 40: Change the site name
Simply change the “www.ulyaoth.net” to whatever your website url will be and save the file, remember to change it for both port 80 and port 443 so two times!

Step 41: Symbolic link the vhost file so Nginx will load it
$ sudo ln -s /etc/nginx/sites-available/wordpress /etc/nginx/sites-enabled/wordpress

Step 42: Delete the current php-fpm.conf and default php-fpm pool on BLOG01

$ sudo rm -rf /etc/php-fpm.conf
$ sudo rm -rf /etc/php-fpm.d/www.conf

Step 43: Go to the etc directory on BLOG01
$ cd /etc/

Step 44: wget a new php-fpm.conf on BLOG01
$ sudo wget http://trash.ulyaoth.net/trash/php-fpm/conf/php-fpm.conf

Step 45: Go to the php-fpm.d directory on BLOG01
$ cd /etc/php-fpm.d

Step 46: wget a WordPress php-fpm pool config file on BLOG01
$ sudo wget http://trash.ulyaoth.net/trash/php-fpm/conf/wordpress.conf
Make sure to change “wordpress.conf” and “php-fpm.conf” to settings you feel comfortable with or leave them as they are for now.

Step 47: go to the WordPress website folder BLOG01
$ cd /usr/share/nginx/wordpress/public

Step 48: Download the latest WordPress version BLOG01
$ sudo wget http://wordpress.org/latest.tar.gz

Step 49: Untar WordPress and fix directory structure BLOG01

$ sudo tar xzfv latest.tar.gz
$ sudo mv wordpress/* .
$ sudo rm -rf latest.tar.gz
$ sudo rm -rf wordpress

Step 50: Create a nologin user called WordPress on BLOG01
$ sudo useradd -s /sbin/nologin wordpress

Step 51: Chown the web directory to wordpress:nginx

$ sudo chown -R wordpress:nginx /usr/share/nginx/wordpress/
$ sudo chown -R wordpress:nginx /var/lib/php/wordpresssession

Step 52: Fix SELinux and the firewall on DB01

$ sudo setsebool -P httpd_can_network_connect on
$ sudo chcon -R -t httpd_sys_rw_content_t /usr/share/nginx/wordpress/public/
$ sudo semanage port -a -t http_port_t -p tcp 80
$ sudo semanage port -a -t http_port_t -p tcp 443

Depending on your setup you probably would need to run something similar as I show below here.
$ sudo firewall-cmd --permanent --zone=public --add-service=http
$ sudo firewall-cmd --permanent --zone=public --add-port=80/tcp
$ sudo firewall-cmd --permanent --zone=public --add-port=443/tcp
$ sudo firewall-cmd --permanent --zone=external --add-service=http
$ sudo firewall-cmd --permanent --zone=external --add-port=80/tcp
$ sudo firewall-cmd --permanent --zone=external --add-port=443/tcp

You might wane double check the firewall and SELinux settings that they are actually safe for your environment as currently I proved full “Read/Write” allowance with SELinux to the whole web directory, I would suggest to tweak this to only the directories that require it.

Step 53: Start php-fpm and Nginx and place them as autostart during boot

$ sudo systemctl start php-fpm.service
$ sudo systemctl start nginx.service
$ sudo systemctl enable php-fpm.service
$ sudo systemctl enable nginx.service

That is it 🙂 now simply go to your website name and follow the instructions to setup WordPress correctly with the database information of your DB01 server, remember do not use DB02 as this is simply a slave.

Related posts

How to create a high availability Grafana 5.3 environment in AWS OpsWorks.

Sjir Bagmeijer

How to install Elastic Stack 4 on Fedora with rsyslog.

Sjir Bagmeijer

Install the official driver from nvidia in Fedora

Sjir Bagmeijer