Ten MySQL Best Practices by George Reese, coauthor of Managing & Using MySQL, 2nd Edition 07/11/2002

MySQL is a complex piece of software that may seem overwhelming when you're first trying to learn it. This article describes a set of best practices for MySQL administrators, architects, and developers that should help in the security, maintenance, and performance of a MySQL installation.

1. Set a password for the "root" user and then rename the user.

The first thing you should do with a clean MySQL install is set a password for the root user:

[01:19:00] george@firenze$ mysqladmin -u root password
'somepassword'

Once you've set the password, change the name of the "root" user to something else. A hack attempt on a MySQL server might target the one user that exists on most systems, "root", both because it has superuser powers and because it is a known user. By changing the name of the "root" user, you make it more difficult for would-be hackers to try a brute-force attack. The following sequence of commands will rename the "root" user:

[01:25:29] george@firenze$ mysql -u root -p mysql
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 72 to server version: 4.0.0-alpha-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> UPDATE user set user = 'admin' where user = 'root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> quit;
Bye
[01:25:51] george@firenze$ mysqladmin -u root -p reload
Enter password:

Of course, you may want to select a more creative alternative name than "admin".

Related Reading Managing & Using MySQL Open Source SQL Databases for Managing Information & Web Sites By George Reese, Randy Jay Yarger, Tim King Table of Contents Index Sample Chapter Read Online--Safari Search this book on Safari:   Only This Book All of Safari Code Fragments only

2. Hide MySQL from the Internet.

MySQL has a pretty solid track record for security of a network service. Nevertheless, there simply is no good reason to expose MySQL directly to the Internet-- so don't do it. When you hide MySQL behind a firewall and enable communication to the server for only hosts running application servers and Web servers, you constrain the path of attack a would-be hacker might take.

3. Protect the MySQL installation directory from access by other users.

First, MySQL should be installed under a special user ID such as "mysql". Second, no user on the system but MySQL should be able to access MySQL's data directories. If you grant access to those data directories to other users, you begin down a path of compromising the internal security built into MySQL. Even a database administrator should not be put into the "mysql" group. Instead, most DBA functions should pass through MySQL itself. For those few operations that need to happen at the file-system level, the DBA should login as the MySQL user.

4. Don't store binary data in MySQL.

It is true that MySQL supports binary data types. Just because you can, however, does not imply that you should. MySQL sends results to a client all at once. Consequently, any application parsing a result set with binary data needs to wait for each row to arrive before it can be processed. Furthermore, there is no real benefit to storing the binary data in MySQL.

A better approach to binary data is to store that data on the file system and store pointers to those files in MySQL. With this approach, you can actually stream the binary data in background threads while you process the result set.

This tip does not apply only to binary data; it applies to any kind of large data objects. The performance issues that plague binary data also plague character data. In other words, the reading of any part of a result set is done serially. You will take more notice with binary data since it is usually large. You will notice the problems equally with large character data. You do need to weigh the performance benefits of storing large character data on the file system against the ability to search against that data in the database.

5. Stick to ANSI SQL.

MySQL provides many convenient additions to the ANSI standard that are very tempting for programmers. These additions include timesaving tools like multitable deletes and multirow inserts. When you rely on these features in a MySQL application, you limit the ability to adapt the application to any other database engine. In fact, you may make it impossible to port the application to another database without a significant rewrite. For maximal portability, you should therefore stick to ANSI SQL for your applications.

On the other hand, I do not want to give the impression that you should ignore these cool tools. They definitely have their place in MySQL maintenance. When you are working at the MySQL command line or you're building scripts specifically for the maintenance of a MySQL installation, it would be folly to ignore the advantages of these tools.