CentOS 5 SQL database server [MySQL]

This how-to will show you how to configure:

  • MySQL as a secure and performant database server
  • Create new databases
  • Additional MySQL users with restricted privileges

Installing MySQL

yum install mysql mysql-server
chkconfig mysqld on
service mysqld start
iptables -I RH-Firewall-1-INPUT 4 -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
service iptables save

Adding a new database+user

Execute in MySQL:

CREATE DATABASE `username_purpose`
GRANT ALL ON `username_purpose` TO 'username'@'localhost' IDENTIFIED BY 'new_password'

The new user will only have privileges on the newly created database.

You can choose anything you'd like for username_purpose, username and new_password but to make maintenance easier, name your database users after the corresponding system user for that site. Database names have a relatively small maximum length, so often you can use a shorted version of the system username followed by the purpose of that database. For example, with a system user of foobar_baz then you could create the database foobar_websites to host the tables of user foobar_baz's CMS. At a glance, it's clear who's database it is and what it is used for.

As well, remember that most of your users will simply need to enter the password once at installation time of the CMS or other software. It's in your best interest to set a long, random alphanumerical password.