MySQL Installation Cleanup

Okay so you have just installed your MySQL database onto your server. It's now an ideal time to perform a cleanup on it to delete the un-necessary users, privileges and databases. This BLOG details serveral steps that I recommend you perform on you newly onstalled MySQL sever. All of which I have performed on my own installation to check that they work correctly.

Here the simple list of tasks that this blog entry will aid you in performing:

• Task 1 - Set the 'root' user password
• Task 2 - Delete anonymous users that are installed
• Task 3 - Delete the remote 'root' user
• Task 4 - Delete the test database
• Task 5 - Remove unwanted privileges
• Task 6 - Flush the servers privileges

Task 1 - Set the 'root' user password

The first task that I would recommend you do is create a root password, as by default the installation of MySQL leaves this all important user account unprotected as the password is left blank.

So who do you create/set the password for the root user? Well we use the MySQL monitor, so open a command prompt window and navigate to your MySQL directory. This will depend on where you installed MySQL to and also on your operating system. For the purpose of this BLOG I will assume you have installed your system the same as I have done locally using a Windows XP Pro system. I installed MySQL to C:\mysql so when I open a command prompt I enter the following syntax to access the mysqladmin utility:

cd c:\mysql\bin    

Now enter the following command at the command prompt:

mysql -u root -p

If no password has been set, which should be the case for you if this is performed directly after a fresh installation of MySQL, you can just press the ENTER key. The following command will allow you to set a password for your super-duper root user:

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

Now that you have set the new password its advised to flush your privileges, this is performed using the following syntax:

FLUSH PRIVILEGES;

Task 2 - Delete anonymous users

This step will delete any anonymous users that are installed Note that the following command uses two single ' characters and not a "

DELETE FROM mysql.user WHERE User=''

Task 3 - Delete the remote root user

Now its time to removes the remote root user using the following command syntax:

DELETE FROM mysql.user
WHERE User='root'
AND Host !='localhost';

Task 4 - Delete the test database

Now we are going to delete the test database that the installation process creates. Use the following command syntax:

DROP DATABASE test;

Task 5 - Remove un-necessary privileges

Now its time to remove any un-necessary privileges from the mysql database using the following command syntax:

DELETE FROM mysql.db
WHERE Db ='test'
or Db = 'test\\_%';

Task 6 - Flush the privileges on the Server

Let's now flush the server's privileges and we are then ready to quit the MySQL monitor and start to use our lovely new MySQL server. Flushing is performed using the following command syntax:

FLUSH PRIVILEGES;

My Conclusion

Well that now concludes this BLOG and hopefully has helped you cleanup your newly installed MySQL server. If you have any comments please feel free to post them and I will try and answer them to the best of my ability.

All the best Jayson