MySQL UTF-8 database the proper way in 3 easy steps

 

I keep comming accross this quite often but not as frequesntly to memorise the whole thing by heart, so I keep looking everything up in my notes. So I have decided to post this article to make life easier for everybody - this all is a well-know stuff, but I just wanted to make it as simple and as easy and all in one place as possible.

So here are the 3 steps:

 

Step 1: Create UTF-8 datbase

 

mysql> CREATE DATABASE mydbname CHARACTER SET utf8 COLLATE utf8_general_ci;

 

do not forget to put your desired datbase name where it is marked red.

 

Step 2: Create a user

 

mysql> CREATE USER myusername@localhost IDENTIFIED BY 'password';

 

put the desired username and password where they are marked in red. Please note that keyword identified by actualy will encrypt the password you are typing in between quotes. Also the @ character identifies the host name for this user and the special thing about it is that MySQL treats localhost (and everything else) as totally different things. So if you have your db on a different server you need to type in IP or fully qualified domain name for the server. (and if you require both to connect from localhost and externally you need to create two users: one with @localhost, and one with @1.1.1.1 (your IP instead of the ones))

 

Step 3: Give grants

 

mysql> GRANT ALL ON mydbname.* TO myusername@localhost IDENTIFIED BY 'password';

 

Here we are specifying three things:

what we are granting - in this case it is ALL (for full reference of grant please refer to documentation)

where we are granting - in this case all assets of the mydbname denoted by asterisk, but it is possible to specify specific tabels

who we are granting to - this should be self explanatory from the step 2 (it is actually possible to leave the 'identified by' part from step two, since it will be overriden by step 3.

 

Hope this will make your life a little easier...

This page was last updated on: 11/06/2009 06:06