Three Tips for MySQL Database Organization and Security

Databases are everywhere. In fact, they’re so widely used in web software that nearly everyone with a website will have to deal with them at some point. At DreamHost, we use MySQL for databases. With the unlimited number of databases included with shared, VPS and dedicated hosting, there’s a good amount of flexibility in how you could manage them all. Flexibility can be both a benefit and a hindrance as it can sometimes result in important items falling through the cracks. Here are some easy tips to keep your databases organized, secure, and backed up.

1. Database Naming:

Avoid creating tangled databases. One of the primary ways you’ll keep your databases organized is by naming them. Databases in the DreamHost system need to have unique names. Because of this, common names such as “blog,” “WordPress” or “wiki” aren’t available.To create unique names, and to keep your databases organized, include the domain name, and/or the subdirectory and subdomain in your database name.

For Example:
For a website named: justin.dreamhosters.com/myblog
Consider naming the associated database: “justin_dreamhosters_com_myblog.”

If you use this convention, then  the database name will provide a clue about the website you’re looking for (and vice versa) when you need to refer to a less used database,

If you have database names you want to reorganize, it will require adding a new database with the new name.  One method to move the data is to make a backup of the old database, and restore that backup to the new database.  Another method is to use phpMyAdmin (best for small databases), for this make sure the two databases both have the same mysql user so they both show up in phpMyAdmin.  Once logged in, select the source database, click the operations tab on the top right, and from there you can copy the structure and data to the new database.  When you are done, update your config files to the new database name, and remove the old database with the “Delete DB” button in the control panel.

apoorlynameddatabase

 

2. User Permissions:

Setting specific user permissions for your database is one of the best ways to keep them secure. User permissions can be set to grant MySQL users specific permissions, allowing you to set flexible security measures.

Most user permissions options can be found in the control panel in: Goodies -> MySQL Databases. Once there, you can change individual settings by clicking on a MySQL username in the “Users with Access” column. From here, you can set permissions on a per user basis. (Note: A user’s permissions will apply to all of the databases they have access to.) From this screen, you can remove users from databases completely, or set up specific user rights using a series of check boxes. The default settings allow a user to do most everything with the database. Depending upon the purpose of a specific user account, you may choose to give a user fewer permissions by unchecking the boxes in this area. (For a list of user permissions and what they do, click here.)

In addition to setting specific user permissions, you can also set “Allowable Hosts” from this screen. By defining your “Allowable Hosts,” you’re choosing where a user can connect to the MySQL server. By default, a MySQL connection can only be established when it’s initiated from one of our webservers (the %.dreamhost.com default). However, this can be customized for other incoming connections. The most common reason to use this would be if you plan to connect to your database from another host, a home or work computer, or a database driven mobile app.

apoorlynameduser

 

3.  Backups:

You probably hear how important backups are, and at Dreamhost we agree! Our own MySQL servers are backed up daily, and are maintained for five days. This provides an important safety net, however, we recommend keeping a copy of your own backups as well. A best practice is to backup your data offsite often enough to cover your needs.

The easiest method to download a backup of all of your databases is via our “Backup Your Account” feature, which is located in the panel under DreamHost -> Backup Your Account. This service can be run once a month. It will backup your website data uploaded to your ftp users and your databases. The service will then email you with information you need to download the backup.

Other backup methods include using “phpmyadmin” to backup smaller databases (less than 10MB generally), “mysqldump” while logged into your website via ssh, or using a graphical user interface to connect remotely to your databases and back them up. Information on these additional backup methods can be found here.