MySQL Post Install Setup

After MySQL is installed, either as part of Ubuntu Server installation sequence or separately through an appropriate apt-get command, it needs to be properly initialized for use by a client.

One key concept of any standalone database engine is that they have their own user namespace and associated access rights. MySQL is no different and therefore this needs to be appropriately setup before any client can create/update/delete entries into any table.

Essentially this involves the following steps, all to be undertaken as the Administrator (or the root account, by default):

1. Create a user account with an associated password. Password is optional, but recommended. Note that only the Administrator can create accounts with the default access rights setup as part of MySQL installation. So you need to connect to MySQL as root.

   {.brush: .shell; .gutter: .false} $ mysql -uroot -p<password> # connect as root ... mysql> CREATE USER '<user>'@'localhost' IDENTIFIED BY '<password>'; # inside MySQL console

   The key point here is that when logging into MySQL as root, the -p option has to be explicitly specified for it to prompt for password. Without it, you will get an access denied error. Of course you can supply the password inline in the command line right after the -p option as well.

Another thing that I kept getting wrong is that there is no space between the -u option and the value supplied to it. This is a little different from a lot of tools that accept various command line parameters. The same goes for the -p option as well.

1. Create the database(s) that will be used by the client

   {.brush: .shell; .gutter: .false} mysql> CREATE DATABASE <dbname>;

2. Grant appropriate privileges (or all privileges, as here) to the new client user account on the database(s) just created

   {.brush: .shell; .gutter: .false} mysql> GRANT ALL on <dbname>.* TO '<user>'@'localhost';

Now you should be able to connect to MySQL as the newly created user and manipulate the just created database any way you need to.