1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Tutorial: Install Wordpress with MariaDB in SSL replication.

Install Wordpress with MariaDB in SSL replication.

  1. sbagmeijer
    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 on any Linux version. If you spot any mistakes then please let me know and I will improve my guide and of course learn from it! :)

    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
    Code:
    $ 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)
    Code:
    $ 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:
    Code:
    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 []:info@ulyaoth.net
    
    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)
    Code:
    $ 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:
    Code:
    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 []:info@ulyaoth.net
    
    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)
    Code:
    $ 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:
    Code:
    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 []:info@ulyaoth.net
    
    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
    Code:
    $ sudo openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
    Result should be:
    Code:
    server-cert.pem: OK
    client-cert.pem: OK
    Step 6: Copy the certificates from your home dir to the ssl dir on DB01
    Code:
    $ sudo cp *.pem /etc/mariadb/ssl/
    Step 7: Copy the certificates from db01 to db02
    Code:
    $ sudo scp /etc/mariadb/ssl/*.pem root@10.0.0.2:/etc/mariadb/ssl/
    Step 8:Install the MariaDB database on DB01 and DB02.
    Code:
    $ 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
    Code:
    $ 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
    Code:
    $ 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 10: Open the MariaDB config file and perform the below changes on DB02
    Code:
    $ 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 11: Start the MariaDB database on DB01 and DB02
    Code:
    $ sudo systemctl start mariadb.service
    Step 12: Place the MariaDB database service as autostart on boot on DB01 and DB02
    Code:
    $ sudo systemctl enable mariadb.service
    Step 13: run the MariaDB secure installation script on DB01 and DB02
    Code:
    $ 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 14: Login to the MariaDB database on DB01:
    Code:
    $ sudo mysql -uroot -p
    Step 15: Create the replication user from within the MariaDB client
    Code:
    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;
    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.

    Step 16: Check that MariaDB correctly has accepted the ssl on DB01
    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 17:Get the bin log information from MariaDB on DB01 (Make sure to write this down you need it later on)
    Code:
    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 18: Login to the MariaDB database on DB02
    Code:
    $ sudo mysql -uroot -p
    Step 19: Check that MariaDB correctly has accepted the ssl on DB02 from within the MariaDB client.
    Code:
    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 20: Give MariaDB the master information it knows this server will be a slave on DB02
    Code:
    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 21: Start MariaDB as a slave on DB02 from within the MariaDB client.
    Code:
    start slave;
    Step 22: Check that you have a successful replication on DB02 from within the MariaDB client.
    Code:
    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 'repl@10.0.0.1: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:
    Code:
    $ 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 23: In the MariaDB client that should be still open on DB01 create the following user and database
    Code:
    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 24: Close the MariaDB client on DB01 and DB02
    Code:
    exit
    Step 25:Check that the replication worked and that SSL works correctly on DB01 and DB02
    Code:
    $ 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 26: Install the following packages on BLOG01
    Code:
    $ 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 27: Create the following directories on BLOG01
    Code:
    $ 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 28: Create the Nginx CA ssl certificates on BLOG01 in your home dir make sure to check the fill in example)
    Code:
    $ 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 []:info@ulyaoth.net

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


    Step 29: Create a Nginx server certificate on BLOG01 in your home directory (make sure to check the fill in example)
    Code:
    $ 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 29:
    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 []:info@ulyaoth.net

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


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

    Step 31: Copy the certificates from your home dir to the SSL dir on DB01
    Code:
    $ sudo cp *.pem /etc/nginx/ssl/
    Step 32: Delete the current nginx.conf on BLOG01
    Code:
    $ sudo rm -rf /etc/nginx/nginx.conf
    Step 33: Go to the nginx directory on BLOG01
    Code:
    $ cd /etc/nginx/
    Step 34: wget a new nginx.conf on BLOG01
    Code:
    $ sudo wget http://trash.ulyaoth.net/trash/nginx/conf/nginx.conf
    Step 35: Open the new nginx.conf and change appropriatly on BLOG01
    Code:
    $ 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″ I feel personally there is not much point going above 4. 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 36: Go to the Nginx vhost directory
    Code:
    $ cd /etc/nginx/sites-available/
    Step 37: wget the Wordpress vhost file on BLOG01
    Code:
    $ sudo wget http://trash.ulyaoth.net/trash/nginx/vhost/wordpress
    Step 38: Open the Wordpress file
    Code:
    $ sudo vim /etc/nginx/sites-available/wordpress
    Step 39: 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 40: Symbolic link the vhost file so Nginx will load it
    Code:
    $ sudo ln -s /etc/nginx/sites-available/wordpress /etc/nginx/sites-enabled/wordpress</code>
    
    [B]Step 41:[/B] Delete the current php-fpm.conf and default php-fpm pool on BLOG01
    [code]$ sudo rm -rf /etc/php-fpm.conf
    $ sudo rm -rf /etc/php-fpm.d/www.conf
    Step 42: Go to the etc directory on BLOG01
    Code:
    $ cd /etc/
    Step 43: wget a new php-fpm.conf on BLOG01
    Code:
    $ sudo wget http://trash.ulyaoth.net/trash/php-fpm/conf/php-fpm.conf</code>
    
    [B]Step 44:[/B] Go to the php-fpm.d directory on BLOG01
    [code]$ cd /etc/php-fpm.d
    Step 45: wget a Wordpress php-fpm pool config file on BLOG01
    Code:
    $ 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 46: go to the Wordpress website folder BLOG01
    Code:
    $ cd /usr/share/nginx/wordpress/public
    Step 47: Download the latest Wordpress version BLOG01
    Code:
    $ sudo wget http://wordpress.org/latest.tar.gz
    Step 48: Untar Wordpress and fix directory structure BLOG01
    Code:
    $ sudo tar xzfv latest.tar.gz
    $ sudo mv wordpress/* .
    $ sudo rm -rf latest.tar.gz
    $ sudo rm -rf wordpress
    Step 49: Create a nologin user called Wordpress on BLOG01
    Code:
    sudo useradd -s /sbin/nologin wordpress
    Step 50: Chown the web directory to wordpress:nginx
    Code:
    $ sudo chown -R wordpress:nginx /usr/share/nginx/wordpress/
    $ sudo chown -R wordpress:nginx /var/lib/php/wordpresssession
    Step 51: Fix SELinux and the firewall on DB01
    Code:
    $ 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 52: Start php-fpm and Nginx and place them as autostart during boot
    Code:
    $ 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.

    [​IMG]