Change WordPress url from mysql

I made a mistake when configuring a WordPress url and could no longer access the site to rectify the issue!

If you are as dumb as I was and need to change the url when you can’t access the WordPress admin panel you can follow these instructions if you have access to the database.

Login over ssh or access the Terminal and login to mysql using your username and password.

mysql -u dbuser -p

Enter password when prompted then select your wordpress database by name. (Yours may be named differently)

mysql> use wordpress;

You can then show all the tables if you wish.

mysql> show tables;

We need to select the 2 url entries from the database and change them to what we want.

if you know the value that is currently being used you can search for that value. (Change the url for yours)

mysql> select * from wp_options where option_value = 'http://wrongvalue.co.uk';

If not you can search by LIKE

mysql> select * from wp_options where option_value LIKE 'http://wrongvalue.co.uk';

Here you can make sure this is the correct entry you wish to change.

Then use the option id of the column you need to change like this.

mysql> update wp_options set option_value = 'http://correctvalue.co.uk' where option_id = 1;

Make sure you do this for both entries and you’re all done.

New HTTP Header “Referrer Policy”

Another blog by @Scott_Helme here on a new http header we need to be setting so our users info is not leaked to third parties when using a link to another site from our own.

It’s pretty simple, and we are using WordPress on nginx for this example.

First we go to our config file.

sudo nano /etc/nginx/sites-available/default

Then below our existing headers we simply add

add_header Referrer-Policy "strict-origin-when-cross-origin";

then restart nginx and we’re done.

sudo systemctl restart nginx

There are several different options and if you are unsure you should check out the previously mentioned article for a thorough explanation.

How to generate and install SSL certificate in nginx running WordPress

You’ve got your site running on https with a self-signed certificate but it’s time to get one signed by a Certificate authority. If you haven’t already configured your server for https then follow the guide here.

The first thing we need to do is create the private key and csr file. The private key stays on the server, and the csr file is sent to the certificate authority for signing.

First let’s make our directory for the certificates.

sudo mkdir /etc/nginx/certificates/{ssl.key,ssl.crt}

And cd to the new certificates dir

cd /etc/nginx/certificates

Then we run (install openssl if you haven’t already got it)

sudo openssl req -nodes -newkey rsa:2048 -keyout yoursite.key -out yoursite.csr

You will need to answer the questions, and for most of them the answer doesn’t matter. The real important one is “Common Name”. This has to be the name of your website. For example: yoursite.com

If this doesn’t match the name of your site then when anyone goes to your site they will receive a warning that the certificate name doesn’t match the site name.

Now you need to go to where are getting the Certificate from and request a certificate. You will then need to cut and paste all the text from the .csr file into the request. This includes –BEGIN CERTIFICATE– and –END CERTIFICATE–

Once the request is complete you need to download your new certificate as a .crt file and you will probably also have to download the intermediate certificate also as a .crt or .pem.

If there is an intermediate cert you will need to merge them with the following command, or simply copy and paste the text from the intermediate certificate into your site cert.

sudo cat intermediate.pem >> yoursite.crt

Then you need to copy yoursite.key to /etc/nginx/certificates/ssl.crt and yoursite.crt /etc/nginx/certificates/ssl.crt by going to the directory you have the files saved and running

sudo cp yoursite.crt /etc/nginx/certificates/ssl.crt

sudo cp yoursite.key /etc/nginx/certificates/ssl.key

Then in your config file you reference the se locations so the server knows which key and crt file to use.

For example

sudo nano /etc/nginx/sites-available/default

Then add the locations in the SSL Server tags as below

ssl on;

ssl_certificate /etc/nginx/certificates/ssl.crt/yoursite.crt; ssl_certificate_key /etc/nginx/certificates/ssl.key/yoursite.key;

Save the changes then retsrat the webserver service

sudo systemctl restart nginx

Now browse to your site over https.

Moving WordPress to https

It’s time to move your sites over to https. Again for this example we will be using a LEMP stack on Ubuntu 16.04.2.

First thing is to open the firewall to allow traffic on port 443. (Here I’m using ufw)

sudo ufw allow https

Then we can check to ensure the port is open with the following.

sudo ufw status

Once we’ve done that we add the following to our config file.

For nginx this is /etc/nginx/sites-available

To open the file

sudo nano yoursite

SSL configuration
server {
access_log off;
log_not_found off;
error_log  logs/yoursite.com-error_log warn;

        listen 443 ssl;
        server_name  yoursite.com; 

 ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
 ssl_prefer_server_ciphers On;
 ssl_ciphers ECDH+AESGCM:DH+AESGCM:ECDH+AES256:
DH+AES256:
ECDH+AES128:DH+AES:ECDH+3DES:DH+3DES:RSA+AESGCM:
RSA+AES:
RSA+3DES:!aNULL:!MD5:!DSS;
ssl_certificate /etc/nginx/cert/crt/yoursite.crt;
ssl_certificate_key /etc/nginx/cert/key/yoursite.key;

The “ssl_certificate” and “ssl_certificate_key” entries are the key and certificate that you have generated on the server and had signed by a certificate authority. If you don’t have these then you can check out our guide on how to do this here.

Now you will be able to access the site over both http and https.

How to update WordPress over SSH

You should definitely be updating over an encrypted connection, so with that in mind, let’s proceed.

Run the following command, and answer all the questions.

sudo adduser wp-admin

Next we need to change ownership of the directory, so we run

cd /var/www/html

sudo chown -R wp-admin:wp-admin /var/www/html

Now we create the SSH keys, while logged in as the new user

sudo su - wp-admin

ssh-keygen -t rsa -b 4096

Save them on the server, for example

sudo /home/wp-admin/wp_rsa

Then go back to your normal account with the following

exit

Then we lock down the permissions

sudo chown wp-user:www-data /home/wp-user/wp_rsa*

sudo chmod 0640 /home/wp-user/wp_rsa*

Now we create a new directory, and set permissions for storing the keys to allow remote login

sudo mkdir /home/wp-user/.ssh

sudo chown wp-user:wp-user /home/wp-user/.ssh/

sudo chmod 0700 /home/wp-user/.ssh/

Then we copy the keys over to the new directory

sudo cp /home/wp-user/wp_rsa.pub /home/wp-user/.ssh/authorized_keys

Then we lock down the new files

sudo chown wp-user:wp-user /home/wp-user/.ssh/authorized_keys
sudo chmod 0644 /home/wp-user/.ssh/authorized_keys

Then we lock down to local login only

sudo nano /home/wp-user/.ssh/authorized_keys

Put the following at the top of the file

 from="127.0.0.1"

Now we update the wp-config file, open with the following

sudo nano /var/www/html/wp-config.php

Then add these lines to the file

define('FTP_PUBKEY','/home/wp-user/wp_rsa.pub');

define('FTP_PRIKEY','/home/wp-user/wp_rsa');

define('FTP_USER','wp-user');

define('FTP_PASS','');

define('FTP_HOST','127.0.0.1:22');

Then restart nginx

sudo systemctl restart nginx

Then next time you need to update select ssh enter your new wp-admin user details and you should be good to go.

Before we finish more thing.

As we have locked down the permissions quite tightly you may need to change ownership of the html folder temporarily while running updates, and then reinstate the locked down permissions and you do this by running the following.

Before updating run.

sudo chown -R www-data /var/www/html

Then once your done

sudo chown -R wp-admin /var/www/html