After installing LAMP on new servers (similar to as described here for CentOS), I’ve recently had two errors after the creation of MySQL users.
These user accounts required additional steps after creation – flushing permissions, and re-running the MySQL secure installation.
Creating a new User and assigning permissions
The first user was created in shell, via the MySQL prompt and assigned access to a new database.
Login to MySQL via SSH
mysql -u root -p
Create the new user “newuser” on localhost. We could also use % (any host), or any other specific hosts defined in MySQL.
mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
And assign privileges to a database called “mydatabase” – the format is database.table, so for access to all use “*.*”.
mysql> GRANT ALL PRIVILEGES ON mydatabase . * TO 'newuser'@'localhost';
And now – crucially, flush permissions. Often it works just fine, but on some setups this is required.
mysql> FLUSH PRIVILEGES;
The anonymous user issue
The second issue I’ve had recently was with a new user being unable to use their account. After some trial and error it turned out that there was an issue with anonymous access, which was easily solved by re-running the MySQL install helper.
Remove all anonymous access!