MYSQL Commands

If you are using phpmyadmin, or have access to bash, here are some useful commands.
Remember always do a mysqldump first so you have a back up of your database.

mysqldump

When you have access to your database it is always a good ide to have a current backup before you do any work.
In bash you can run

mysqldump -uuser dbname -ppassword >todaysdate.sql

Now you can play and even make mistakes. But until you have done this some and feel confident only work on your own databases, not a production database.

update

Had an issue, I had just updated a website to my server, set up security and was moving them to https. Only to find out that the theme they were using was rather out of date and the ssl plugin could not edit the ‘meta_value’ in real time and replace http with https. This mean that the site was seen as insecure.

select * from tinkerwp_postmeta where meta_value like "%http:%";

Gave me a list of all the IDs with http: in the meta_value field, about 15 of them, so instead of indifivuallt going to each record and manually replacing http with https, I ran this command.

UPDATE tinkerwp_postmeta SET meta_value = REPLACE(meta_value, 'http:', 'https:');
Query OK, 15 rows affected (0.012 sec)
Rows matched: 3390 Changed: 8 Warnings: 0

Success.

Working with WordPress and MySQL

select option_id,option_name from wp_options where option_value like "%http://mokuaikaua.com%";

This gives us all the lines that use http://mokuaikaua.com

select option_id,option_name from wp_options where option_value like "%https://mokuaikaua.com%";
select * from wp_options where option_id = 1;
+-----------+-------------+------------------------+----------+
| option_id | option_name | option_value           | autoload |
+-----------+-------------+------------------------+----------+
|         1 | siteurl     | https://mokuaikaua.com | yes      |
+-----------+-------------+------------------------+----------+

This is what https://mokuaikaua.com may look like.
Now we want to replace https://mokuaikaua.com with https://temp.mokuaikaua.com

UPDATE wp_options SET option_value = REPLACE(option_value, 'https://mokuaikaua.com', 'https://temp.mokuaikaua.com');

Useful Commands

show tables;

Let’s you see all the tables in the current database.

describe tablename;

This gives a description of all the fields and their type from the table selected.