MySQL Security tips

Never store the MySQL ‘root’ user password in a ~root/.my.cnf file.

Do you need to store a MySQL password in a file? Yes. Connection management for your application is an example, however that use should never be ‘root’ user to connect to your application. You may also need to run scripts to backup your data with mysqldump. The solution is to create a dedicated user .e.g. ‘backup’, and then grant that user only the permissions necessary to do the specific task at hand. By default, a simple mysql command will never grant access without any authentication, the user will need to find the password.

$ mysql -uroot -p[password]
mysql> create user dba@localhost identified by '[newpassword]';
mysql> grant all on *.* to dba@localhost with grant option;
mysql> exit

# Check you can really login
$ mysql -udba -pnewpassword
mysql> select host,user from mysql.user;
# Drop 'root' users as listed, generally
mysql> drop user root@127.0.0.1;
mysql> drop user root@localhost;
mysql> drop user root@[hostname];
mysql> select host,user from mysql.user;
mysql> exit