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:
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)
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:
Here is a the story of setting up a Trellis server at Digital Ocean including SendGrid and Google Mail for a client. I took care of this recently to move him from a shared Dreamhost server (testing ground) to a Trellis LEMP with Digital Ocean using the Bedrock Modern WordPress Stack. This post also includes dealing with general email using Google and external site email using SendGrid. Took longer mainly due to SendGrid issues. Normally is should just take a few hours depending on the propagation of the domain name and the provisioning and deployment of your server.
I made a backup of the existing website using BackupBuddy (awesome backup plugin by iThemes) and files backup with SFTP. I also made a database backup using Sequel Pro. On top I exported all data using the WordPress Export Tool. If you are doing a bare bone installation or had been developing with Trellis from scratch and are going to production you can skip this step.
Digital Ocean Setup
I then did the initial set up a US Digital Ocean Ubuntu 16.0.4 LTS Server. To set up Trellis later you need this type of bare bone setup. The client wanted to have a US located server so I picked NYC. I also checked backups and added my SSH keys:
You need SSH to have smooth and secure access and to make installing Trellis a breeze using the Ansible Playbooks.
Gmail for Mail
I also started the process at Gmail to take care of email for the client as they have the best email service out there for business. Digital Ocean does not take care of this for you. So you need to another provider for this. Google Mail is the best and is around $5 per month for a basic package. You set this all up at Google Apps for Work. Create a general account with them, add email aliases (accounts) and send verifications to your clients. This so the accounts can be activated.
Gmail Domain Verification
Next step is to confirm domain ownership. To use your domain with Gmail you have to be able to confirm you own it adding a tag to the index page’s header section:
This was not possible yet due to the domain not pointing to the Digital Ocean server as discussed next.
Domain propagation to point the domain name to DO was in progress at so I needed to wait a bit for the new domain to load from the new server. For this the What’s My DNS site is your friend:
At Dreamhost I already had changed the DNS and had an A record pointing to the new ip address. See article on pointing hosting away from Dreamhost here. This in case you are also using Dreamhost for DNS. If you use another provider they should have documentation for you too.
NB Emptying local DNS might be useful too. Especially when most DNS Servers globally are showing the correct ip address for the domain already. For OSX use:
NNB You may also have to remove the SSH key when you tried to access the site with the old ip address. Remove the offending line at
Server Provisioning with Trellis
Once all pointed to the Digital Ocean Droplet’s ip address xxx.xx.x.xxx.xxx I could install the Trellis Server at Digital Ocean. You always first set all things up locally and then add all to the server. This means you have several files to edit. Eight when you set up the local server as well as production. A few more when you take care of staging as well. Check things locally. See if they work and do not forget to:
add commercial plugins to .gitignore
remove www as url if you are only using non www urls
activate build-before.yml deploy hook if you are working with Sage
You can read more about it at Roots.io and in this blog post. Better do it well or you will bump into Trellis errors.
When all is well and the server is provisioned you can deploy the web application to the server. You can do this using the following command:
./deploy.sh production domain.com
As you can see I am deploying to production. If you are deploying to staging then change accordingly. When all goes well the site should then load and you should be able to install WordPress. Once that is done you can log in and check if all plugins and needed theme have been added.
Content at the installed Trellis Server at Digital Ocean can be imported with WP CLI. This comes pre-installed with the Trellis setup and is an awesome CLI tool for WordPress developer. Importing can be done from the command line with the WordPress Import plugin active. Check that it is installed and active. When it is use the following command:
That should allow you to import all. You will see notices and or warnings, but the tool does the job most of the time without issues.
Gmail Domain Confirmation
Once the deployment was done well and I had included the Google Domain Verification email I could proceed with setting up Gmail for Work. In the Dreamhost Panel I indicated I would use Google Mail for email. Dreamhost can then with ease add the necessary MX records. With that Google Aps for Work had enough information and things setup to process all:
And as you can see this can take up to 50 minutes for Google Aps for Work to verify the domain and set up your email!
When all is done you will see this popup saying all is well and that you should enter payment details before the trial has ended:
Sendgrid – External Mail
For the external email server of the Digital Ocean server we use Sendgrid .
The service is for free up to 12,000 emails per month and 2,000 contacts. Just for getting emails when comments are left, users are registered and such. Better to do this once the domain is all setup. Otherwise Sendgrid may stop the provisioning.
SendGrid Provisioning Issues
That is the issue we ran into. The stated:
Unfortunately, your account did not pass provisioning. We’ve sent you an email outlining why your account was not activated. Learn More.
We opened a ticket with them. Took them about 5 hours to reply. They needed the email address to be changed to reflect the domain, a CNAME created to show ownership of the site:
and details on the nature of my client’s business. This as we used a different email address for initial setup as Google Mail was not set up. And as the domain hadn’t been properly propagated yet. Next time I will wait a little longer!
Somehow the initial ticket got lost so I opened a new ticket with them and added the last response with details I sent them. About 24 hrs after I set things up with them I still hadn’t had an answer from them. Then I did get a reply again to add a phone number and sent some screenshots of the web app we were building. Et voila, the day after so after about 48 hours our account got accepted!
Trellis would like you to use SMTP. SendGrid has the following information on that:
If you are integrating SendGrid with an existing application, setting up the application to use our SMTP relay is easiest, as it only requires modifying SMTP configuration.
Change your SMTP username and password to your SendGrid credentials
Set the server host name to smtp.sendgrid.net
Use ports 25 or 587 for plain/TLS connections and port 465 for SSL connections
For most users we suggest port 587 to avoid rate limits set by some hosting companies.
With SMTP, 100 messages can be sent with each connection.
So we used smtp.sendgrid.net:587 and then added all our details. Inside group_vars/all/vault.yml I already had a password added. But as SendGrid stated I had to change the user and password to the ones used by SendGrid.
As the Mail credentials were changed I had to first update my repository and then re provision the server.
Git repo update:
Jaspers-Mini:trellis jasper$ pwd
Jaspers-Mini:trellis jasper$ git status
On branch master
Your branch is up-to-date with 'origin/master'.
Changes not staged for commit:
(use "git add <file>..." to update what will be committed)
(use "git checkout -- <file>..." to discard changes in working directory)
no changes added to commit (use "git add" and/or "git commit -a")
Jaspers-Mini:trellis jasper$ git commit -a -m "changed email credentials"
[master 35672f8] changed email credentials
2 files changed, 5 insertions(+), 5 deletions(-)
Jaspers-Mini:trellis jasper$ git push
Counting objects: 7, done.
Delta compression using up to 8 threads.
Compressing objects: 100% (7/7), done.
Writing objects: 100% (7/7), 783 bytes | 0 bytes/s, done.
Total 7 (delta 4), reused 0 (delta 0)
remote: Resolving deltas: 100% (4/4), completed with 4 local objects.
abe93a8..35672f8 master -> master
Then I did the actual provisioning again:
ansible-playbook server.yml -e env=production
Provisioning can take another 15-20 minutes as it will check all needs to make sure all has been set up well and to make sure no changes are skipped. So get another coffee or do some other work in the meanwhile. If all went well you should now see:
but assumed it was already included in Trellis. When I checked the config I saw it was:
# Ansible managed: /Users/jasper/webdesign/domain.com/trellis/roles/ssmtp/templates/ssmtp.conf.j2 modified on 2016-09-19 07:47:36 by jasper on Jaspers-Mini
Then I thought. Perhaps the from sender in WordPress settings is still not OK. But it was. It was using the existing email account attached to the domain. Then I checked the Ssmtp config again and realized the mailhub was wrong. I had used our domain, not sendgrid. Silly me. So updated repo with corrected settings and provisioned again.
Email Test Successful
So with mail settings changed I tested SendGrid / Mail again by resetting the password for the test user and it worked!
And at SendGrid it showed as well:
I can only assume the bounce was due to the initial email with faulty settings.
Trellis Server at Digital Ocean all done
Well, we now are all done setting up a Trellis Server at Digital Ocean including SendGrid and Google Mail. The WordPress website is up and running at Digital Ocean using Trellis. The client can receive email with Google Apps Email although payment credentials still need to be added as well as some more aliases. We can also send emails from the servers for subscriptions and when payments are done using SendGrid. Great result!
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”
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.