Access MariaDB on Trellis LEMP using Sequel Pro

To access MariaDB on Trellis LEMP using Sequel Pro from you local box is easy once you know how. Like with most things in life really. But the main thing is that you need to know the proper way to access the database once you have set up SSH access properly with Sequel Pro (see this article on more on SSH access via Sequel Pro).

No root Access to Database

The issues is that MariaDB with standard setup does not allow root access unless you are root on the system and then you can login without a password as the standard MariaDB setup uses a plugin to check whether you are root and then automatically grants you access. And we normally log in as a non-root. So now what?

Database User & Password

If you are used to accessing your VPS with the root user for the database you will fail getting access. So instead of adding root and password you should add the database user for the database in question and the password there. That way you can avoid the need root ssh-ing into the box which you normally do not want nor should really want to.

Figuring out the Username

To figure out the username you normally just need the name or your project and whether it is development, production or staging. Normally it is example_com based on your chosen example.com project name.

Or – to be really certain – you ssh into your box, change to root, mysql -u root into the database server and then check which user is for the database.

So  you would do

ssh admin@domain.com
sudo su
mysql -u root
use mysql;

NB vagrant ssh for local access but we are accessing production in this example

Check Existing Users in Database

And then you would check for all users to figure the user for the database:

MariaDB [mysql]> select user,plugin FROM user;

+---------------------+-------------+

| user                | plugin      |

+---------------------+-------------+

| root                | unix_socket |

| root                |             |

| root                |             |

| root                |             |

| sub_domain_com |             |

+---------------------+-------------+

5 rows in set (0.01 sec)

As you can see the root has unix_socket access and the only non root user is sub_domain_com (name changed) . That is the user you should use together with the password you added in your vault.yml. So normally is is domain_com as the user.

Database Access Granted

Once you change the database user and add the correct password you can acces the database and make a backup for example. Or do other manipulations like you would normally do in the database with Sequel Pro.

Change Custom Post type Name and Taxonomy of existing Custom Post Type

Had a client ask me the other day if the existing name of the Custom Post Type in question could be changed as the url or taxonomy. So how do you Change Custom Post type Name and Taxonomy of existing Custom Post Type? Well there are a few steps you need to take to do this without blowing up your website.

Custom Post Type Setup

As with most if not all custom post type setups you have the option to create posts under that custom post type. The name of that post and slug can be adjusted, but the name for the group it is under cannot be changed. So in our case the name module or plural modules cannot be changed in the backend. To do that you have to change things in the database and in the code to load things properly.

Backup

Backup your site’s database and theme files before you get started. You will be making theme file changes as well as do a database update. So make sure you can revert when shit hits the fence. Always better to the safe than sorry, right?

Change Custom Post Type Name in Database

To update the Custom Post Type name we would have to run the following in the database:

UPDATE `wp_posts`
SET 
 # Update the post_type column
 `post_type` = REPLACE(`post_type`,'name_of_old_post_type','name_of_new_post_type'),
 # Update the urls
 `guid` = REPLACE(`guid`,'name_of_old_post_type','name_of_new_post_type')
WHERE `post_type` = 'name_of_old_post_type'

NB See SO thread on something similar here.

You might though just do a simple:

UPDATE `wp_posts` SET `post_type` = '' WHERE `post_type` = '';

without replacing the GUID might suffice as well. Then you just need to update the permalinks after that has been changed as well as the taxonomy here below. With this query you basically update the name of the custom post type without changed the GUID. It is simple and quick.

Change Taxonomy Name in Database

To update the url or taxonomy used by the Custom Post Type you will have to to do the following

UPDATE `wp_term_taxonomy` SET `taxonomy` = '' WHERE `taxonomy` = '';

See also WordPress Stack Exchange thread

Post Meta

If you have stored any references to this post type within wp_postmeta within serialized arrays you cannot simply do a search and replace as they replaced strings need to be of the same length or things won’t work anymore. There are scripts and plugins out there that can help with this though. Here is a Better Search and Replace plugin and there is of course wp-cli search and replace that does all this taking care of serialized data as well.

Permalink Update

Do not forget to update the permalinks after these changes under settings > permalinks. Otherwise your existing custom post type posts won’t load anymore and neither will the taxonomy they are under. But hey, this is not hard to take care of now is it?

Theme Changes

You’ll need to update any references to this post type in your code. So references to the old custom post type and taxonomy in template files, functions.php and so on. And this could take some time. Especially if it wasn’t you who created the theme in the first place. And that was the case in our case. In the end we postponed this big change as a new theme change was coming up. Once that is done we will get back to this possible upgrade.