Setting the MySQL root password

Setting a MySQL root password is considered by some to be a waste of time. But then some also believe having a firewall or wearing a seatbelt are a waste of time.

Setting a MySQL root password is very simple and is the first thing you should do on installing the MySQL database.

When you first install MySQL it won't have a password set for the root user.

Note: Don't confuse the MySQL root user with the system root user.

Although both users have the same philosophy behind them in that they are admin users with full permissions, the MySQL root user is only used when accessing databases and when using MySQL from the command line. It cannot do anything 'outside' of MySQL.

So to start this MySQL series, we will set the MySQL root user password. There are a couple of ways of doing this, but we are going to log into MySQL to set it.

This does a couple of things. Naturally, it will set the password (!) but it will also introduce you to the MySQL command line.

Just as with BASH, you should not be intimidated by using the command line. It is easy to learn and is as powerful as you want it to be. There are plenty of external gui faces to MySQL and indeed, I occasionally use them. But it is useful to learn at least the basics of the command line for there will be a time that entering a couple of commands is far easier than firing up another programme to complete a simple task.

Let's get stuck right in. Log onto your VPS and find out the hostname of your server. You will probably know this already but if not, you can enter the command:

hostname
#shallot

As you can see, my server's hostname is 'shallot'. I'll explain why we need this is a moment.

Now we get to use MySQL via the command line. Simply enter 'mysql:

mysql -u root -p

The -u option specifies the user. In this case the user is root. The -p option means a password will be required. Just hit enter/return when asked as there is no password (yet).

A little tip is to set an alias in your .bash_profile to shorten this command. For example, I have:

alias mysqll="mysql -u root -p"

So when I enter the command 'mysqll' it will execute the full 'mysql -u root -p' code.

You will be greeted with some information regarding version and some tips on getting help:

As this is our first foray into the MySQL command line, let's set the password and leave.

To set the password enter:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('yourpasswordhere');

Change 'yourpasswordhere' for a password of your choosing and note the semi-colon (;) at the end of the command.

The output is pretty straight forward:

Query OK, 0 rows affected (0.00 sec)

That tells us that the 'Query' (the command we entered) went OK.

That's almost it.

Remember we got the hostname of your VPS? Well, there is more than one way to connect to MySQL. One is from localhost and the other is via the hostname of your machine:

SET PASSWORD FOR 'root'@'shallot' = PASSWORD('yourpasswordhere');

Notice how I have my hostname ('shallot') in the command? As it's the same user, just with different ways of connecting, use the same password as before. We won't have to worry about that again.

Finally, you need to reload the privileges and then leave the MySQL command line:

FLUSH PRIVILEGES;
quit;

MySQL is very polite and will say 'Bye' before putting you back on the BASH command line.

As you can see, using MySQL is very simple and very quick. It would not have been worth starting a gui interface just for this.

The next articles will concentrate on creating databases, tables, columns, users and all the good stuff that databases are used for.

PickledOnion.

Digg it | del.icio.us | reddit | StumbleUpon