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 = '';

If not you can search by LIKE
mysql> select * from wp_options where option_value LIKE '';
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 = '' 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:
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/ warn;
        listen 443 ssl;
 ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
 ssl_prefer_server_ciphers On;
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
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/ /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
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






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