If you’re involved in creating or maintaining a website that uses a Content Management System, or CMS, as its backbone, it’s likely that the CMS uses a database. mySQL is an open-source database that is very robust and is the most widely used database on the web. It’s robust enough to be forgotten and that’s not good.
The saying goes, “only back up data that you want to keep.” This snarky bit of advice is usually passed on to the panicked user whose every document, picture and spreadsheet has just disappeared into the data abyss of a failed drive or viral infection. It’s pretty easy to back up a set of local files on a regular basis and for web developers, backing up the source files for a particular site is as easy as copying the entire root folder to a local drive, but how does a mySQL database get saved?
- If you’re using a hosting provider, log in to the control panel for your site(s).
- Login to phpMyAdmin using your database user name and password.
- Click on the database name you want to back up.
- Click on the tab labeled EXPORT.
- There will be a file window with all of the files to be exported highlighted, otherwise, select all the files in that window (place you cursor in the window and press Ctrl-A or CMD-A on the Mac.
- SQL should be checked.
- Check the “SAVE FILE AS”
- Click “GO”
Your database will begin to download to your local machine, to the directory specified in your browser software.
If you don’t feel comfortable following these steps, chat with your hosting provider about your mySQL database backup options. The backup process can be easily automated and your provider may already backup your web work. On the other hand, if you’re hosting on a local server, consider creating a CRON job to regularly backup all directory contents, the server and IIS or Apache configs and mySQL to more than one remote volume.
Another important consideration for mySQL databases when used with a CMS is security. First thing is to never use the same login and password for your hosting account, your CMS and your mySQL db. Yes, this means you need to manage more passwords, and if you’re running many sites, this can be a challenge. LastPass.com offers a free and secure password management system that will help.
If you’re using Drupal or Joomla, create a specific user with a different password for the CMSes to use and restrict the privileges. The only mySQL privileges needed are:
create
drop
alter
delete
index
insert
select
update
Your master user should have all privileges enabled, but not the CMS user. This helps to prevent mySQL exploits that can potentially allow hackers to gain access to your database.
