Top 5 best practices for MySQL users

MySQL is very complex and vast piece of software, so it is quite difficult to know every thing about it. But as a MySQL user you should be aware about the best practices that can help in the security, performance and maintenance of database. Here I am describing the top 5 practices that every user should know.

1. Set Password: At the time of installation of MySQL you need to set the username for the user as 'root'. Most users don't change the username even after installation of MySQL and this creates security threat. So the first thing that you should do to prevent your data from hackers is to rename the username and give a complex password.
2. Security guidelines: Keep in mind following basic security guidelines:

• Since the passwords of all the users are stored in mysql user table, so never grant its access to any administrative account.
• PROCESS or SUPER privilege should be given only to administrative users.
• Similarly FILE privilege should also be given only to administrative users. With FILE privilege user can read any file that is readable from the database and this is a big security threat.
• Lower the permission given to other mysql users like developer, tester, sysadmin etc.
• Make sure that only the 'root' and 'mysql' user can access the /var/lib/mysql directory.

3. Server Security: Anybody can hack your server if given physical access of system. Therefore it is recommended that your application and database server should be on different machine. Consider following security measures:

• Antivirus and antispam should be installed on your system.
• Protect your OS with firewall.
• Server physical location must also be safe.
• Disable the services that are not required.

4. Protect MySQL installation directory: MySQL data directory should be accessible only to MySQL. Even DBA is not allowed in a 'mysql' group.
5. Disable remote access: If your database is accessed remotely then make sure that only defined hosts are able to access your server. To restrict remote access you can TCP wrappers, firewall or other software’s available in market.