MySQL Users
MySQL users and System users
Posted 10.10.2005 | Updated 27.11.2008 | Contributed by Nick Fratangelo & Andy Mallett


When running MySQL on Unix, one potential point of perspiration is the user conundrum.

MySQL has its own set of users and it's easy to confuse these with the Unix system's own users.

Here I define the two types of user:

System Users - those with accounts in the Unix system's user database
MySQL Users - those with accounts in the MySQL user database

The above are two COMPLETELY SEPARATE sets of users with no connection at all.

The user 'root' is the default Unix administrator username and also (very confusingly) the default MySQL administrative user. Again, they are completely separate and unconnected accounts.

I like to refer to them as either 'system root' or 'mysql root' to clarify.

Running the MySQL Service (daemon)

The only relevance MySQL has to the Unix system users, is which system user actually runs the MySQL daemon.

The MySQL daemon itself (mysqd) is initialised and run by one of the Unix system's users, usually the system user called 'mysql' (hence the $adduser mysql when installing MySQL).

However it is possible to run some earlier versions of MySQL as the Unix system 'root' user. For various reasons, this is a HUGE security risk and is frowned upon by those in the know.

Always run the MySQL daemon as the Unix system user, 'mysql'.

Adding MySQL Users

It is good security policy to create a MySQL user account who will be able to use the database, preferably with the least number of privileges (in case the username/password is compromised). Allowing all access to a system through a single account with all abilities is risky.

To create a user andym with a password of Banana2008 and allow him to do anything with the database named secrets, connect to the database with mysql and issue the command: 

grant all on secrets.* to andym@localhost identified by 'Banana2008';


After changing any MySQL user details, it is important to flush all the user privileges afterwards, which will reload any user changes.. 

flush privileges;


Note that the above command only allows the user andym to use the MySQL database from the same machine which is hosting the MySQL system. Here are some variations..

You can specify the hostname or IP address that is allowed access, in place of localhost, in the following commands. To be able to connect from a host named 'workstation' (192.168.0.22), use one of these: 

grant all on accounts.* to andym@workstation identified by 'Banana2008';
grant all on accounts.* to andym@192.168.0.22 identified by 'Banana2008';


A slightly dodgy alternative, with severe security consequences, is to allow access to andym from anywhere: 

grant all on accounts.* to andym@'%' identified by 'Banana2008';


In this context, the % symbol is a wildcard permitting access from any host.

Note that it is necessary to create the localhost access control in addition to a specific host or global access control. If you do not, connections from localhost may not function properly.


Finally, log into MySQL using the new credentials.. 

mysql -u andym -p


The system will prompt for the password and away you go..



- N&A.