CentOS 5 SQL database server [MySQL]

  • 2 min read

    • 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
    mysql_secure_installation

    Adding a new database+user

    Execute in MySQL:

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

    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.