Installing & Configuring MySQL Server

This Howto will show you how to install MySQL 5.x, start the service, make sure the server starts on reboot, login via terminal, change the root database admin password, change the name of the root user, add a new user with specific privileges to a specific database, add a new DBA, add a new database, remove all anonymous logins, remove all non-root users, added file security steps, disable remote access (via port 3306), purge the scrollback history, and finally the installation of the gui tool mysql-administrator.

Applicable to Centos Versions:

  • Centos 5.x
  • Centos 6.x

Requirements

  1. Login to a terminal as root using one of these options: (su –login | su -l | or: su –)
  2. Yum and rpm must also be installed and functional (something is seriously wrong if they aren’t)

Doing the Work

  1. Install mysql mysql-server:
  2. Start MySQL server daemon (mysqld):
  3. Login as root database admin to MySQL server:
  4. Delete ALL users who are not root:
  5. Change root database admin password: (note: once this step is complete you’ll need to login with: mysql -p -u root)
  6. Change root username to something less guessable for higher security.
  7. Remove anonymous access to the database(s):
  8. Add a new user with database admin privs for all databases:
  9. Add a new user with database admin privs for a specific database, in this case the database is called “bugzilla”: (note: The ‘bugzilla’ database must first be added, see below.)
  10. Add a MySQL database:
  11. Installing mysql-administrator:
  12. Improving local file security (after saving and exiting remember to: service mysqld restart for changes to take effect):
  13. Disabling remote access to the MySQL server (after saving and exiting remember to: service mysqld restart for changes to take effect).

Troubleshooting

How to test

  1. Make sure mysql and mysql server are indeed installed and that they are the correct versions:
  2. Starting mysqld on boot:
  3. Clear MySQL scrollback history (so sensitive data such as passwords cannot be seen by others with access):
  4. Show all users in the MySQL Server database:
  5. Delete a user from the MySQL Server database:
  6. Delete a null user (user without a username) from the MySQL Server database:

Common problems and fixes

Problem: User has not properly logged in with roots environment.

Fix: (switch to root with one of the following methods):
su –login
su -l
su –

More Information

Disclaimer

We test this stuff on our own machines, really we do. But you may run into problems, if you do, come to #centoshelp on irc.freenode.net

This has been tested on Centos 5.x and 6.x

Added Reading