Create WordPress Database using MySQL Client

Every now and then I need to create a MySQL or MariaDB database from the command line using the MySQL Client. And I tend to forget exactly what commands to run. Lots of things to remember as a web developer. That is why I created loads of gists at Github. Unfortunately their search is not that great and it is often hard to find stuff as you cannot write solid descriptions like you would do on a blog.

So here let me tell you what to do when you need to quickly create the database for a WordPress site from the command line. In my case I have Laravel Valet up and running on OSX with PHP7 and MariaDB running based on Homebrew. So all I need is to log into the MySQL Client and add the database. So how to proceed.

  • open the terminal
  • run the command mysql -u root -p
  • enter your password

That should show you the MySQL client prompt demonstrating you logged on. Then you need to run the following commands – with adjustments for the names and passwords:

CREATE DATABASE databasename;

And then you add privileges for a user:

GRANT ALL PRIVILEGES ON databasename.* TO "wordpressusername"@"hostname" IDENTIFIED BY "password";

Make sure you change the

  • database name to the one you picked,
  • choose a database user and
  • choose a password.

And then you flush all privileges:

FLUSH PRIVILEGES;

So you would see something like this:

mysql> CREATE DATABASE databasename;
Query OK, 1 row affected (0.00 sec)
 
mysql> GRANT ALL PRIVILEGES ON databasename.* TO "wordpressusername"@"hostname" IDENTIFIED BY "password";
Query OK, 0 rows affected (0.00 sec)
 
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> EXIT
Bye

As you can see not really hard to do, but nice to have handy to quickly modify, copy and paste is it not?

NB Do not forget to note down your used credentials for adding to wp-config.php

If you would like to check if the database is there run:

SHOW DATABASES;

 

Show Databases

Your database with your name should be there.

Question? Leave a comment here below!

Converting Japanese Character Sets

A client of mine – Kyoko Inatome – who writes both in English and Japanese, had an issue with the Japanese characters (letters). She had saved the database language differently earlier. Now things no longer worked as it should and all the Japanese had become gibberish. The Japanese Character Sets needed to be converted to be displayed properly.

This is a WordPress site that has been around for a long time. At the time all data was stored as Latin and only partly converted to UTF8. And what was converted or added was not done successfully as the database character set and collation had not been adjusted and nor had the necessary table columns been adjusted.

One Step Conversion

What I tried first was a one step conversion of the post content and post titles. This I did using the following commands. I ran these from PHPMyAdmin:

ALTER DATABASE database_name CHARACTER SET utf8;
ALTER TABLE wp_posts CHARACTER SET utf8

The two queries above were to convert the database to UTF8 and to set the character set of the table with the main issues. Then I ran:

alter table wp_posts change post_content post_content LONGTEXT CHARACTER SET utf8;

This did not work at all.

Three Step Conversion

Then I read the article on the possible need of a three way conversion and the reasons behind this. I quote:

This is necessary when the charset of the table/column does not match the charset of the data that was being saved to it. Specifically it is necessary when the WordPress site was sending data in latin1 format (because DB_CHARSET was not set to UTF8) but the database was in fact formatted as UTF8. This seems crazy but it can happen because of mysterious compatibility layers in MySQL/MariaDB.

So I ran the three way conversion on the following table columsn

 alter table wp_posts change post_content post_content LONGTEXT CHARACTER SET latin1;
 alter table wp_posts change post_content post_content LONGBLOB;
 alter table wp_posts change post_content post_content LONGTEXT CHARACTER SET utf8;

alter table wp_posts change post_title post_title LONGTEXT CHARACTER SET latin1;
 alter table wp_posts change post_title post_title LONGBLOB;
 alter table wp_posts change post_title post_title LONGTEXT CHARACTER SET utf8;

alter table wp_terms change name name LONGTEXT CHARACTER SET latin1;
 alter table wp_terms change name name LONGBLOB;
 alter table wp_terms change name name LONGTEXT CHARACTER SET utf8;

Database Character Set

As they stated the three way conversion was probably needed as the database character set was not set properly. It wasn’t as I saw that the data in the database was still gibberish while it now looked fine on the site. So I added”

define( 'DB_CHARSET', 'utf8' );
define( 'DB_COLLATE', '' );

to wp-config.php .

Issues with Latin Characters

But running it got some Latin Chars mangled. These I fixed using the WordPress WYSIWYG Editor. This I could do as there was not too much text to go round and most of it was fine.

Japanese Letters Gone and Back Again!

Then when I checked all the Japanese categories and posts as well as post titles I saw they were all being displayed as gibberish again. Oh my goodness what now? then I realized reading it all I just had to repeat the three way conversion now that the database char set was all good.

So I ran the above variation three again going from latin to blob to utf8.

After that the Japanese in the database was fine:

phpmyadmin - Japanese All OK

as well as it was on the website:

Japanese on the site

Yay!

Trellis and Sequel Pro for OSX

If you are working with Trellis LEMP Stack on a Mac to create beautiful and professional WordPress themes on blazing fast and secure servers you are bound to wind up needing to manipulate the database. Many developers are used to PHPMyAdmin. Well, you should not install it and it has not been installed on Trellis. Install Sequel Pro instead on your Mac instead. With Trellis and Sequel Pro you will manipulate databases with ease.

Sequel Pro

Sequel Pro is a great GUI program that you can use to connect to your local and remote server’s database server. It is very versatile. You can use it to connect to database servers anywhere instead of just locally on one box. And as Trellis is a smart LEMP stack they decided they did not need PHPMyAdmin as they could approach any box using Sequel Pro.

Let’s discuss here how you can make a connection locally.

Trellis and Sequel Pro

As you know Trellis runs on a custom Vagrant box, preferably on VirtualBox. And so you can access the box using ssh vagrant. It will then allow you to access the box with your own ssh key. When you want to connect to the database using SSH tunneling you need to add several details.

Click the ssh tab after you clicked to add another favorite or chose another connection from the menu. Then add the following details. You can leave open the others:

  • mysql host: 127.0.0.1
  • username: root
  • password: password as entered in vault.yml
  • ssh host: 127.0.0.1
  • ssh user: vagrant
  • ssh key: under yoursite.com > .vagrant > machines > default > virtualbox > private_key
  • ssh port: 2222

The port is here not 22, but 2222 and you should locate the key clicking the blue key at the path I indicated above. Other details are not to hard to understand though you may pick the local network ip on your box instead of the localhost 127.0.0.1 . And well, that would not work either as you SSH tunnel your way in in this case.

You should now be able to connect. See here screenshot below:

Sequel Pro

You can do a test or save and connect right away. It will not load the database right away. You will need to choose it from the top left dropdown menu stating choose “Choose Database..” I did test and then saved and then connected. That is the best way really.

And then you will be connected. Trellis and Sequel Pro are working hand it hand as they should be. You can then start adjusting database data, import, export and so on.

Known Hosts Issues

Sometimes your known hosts setup has changed and then you won’t be able to connect. Sequel Pro error message may not be clear in console as you need to scroll to see it. Do scroll. It will tell you if that is the case or not. Make sure to edit the known hosts file and to remove the offending line using nano or another editing tool. For OSX users:

nano /Users/your-user/.ssh/known_hosts

Once opened remove the line and then try again.

NBB For Windows users I refer to MySQL Workbench