Corey's Projects: Database Scripts


	Professional Software Consulting

MySQL Database Scripts

A database lies at the heart of most applications today. Oftentimes, the data is critical; its loss causing a severe impact on your business. Backing up this data, and being able to subsequently restore it, are critical for the long term success of an application.

Hosting companies frequently have their own solution to allow you to manage your data, but having an automated solution can be a real time saver. One way to do this is to write an interface into your application that enables you to click a button and have the data backed up, compressed, and copied to multiple off-site locations. Then another button push to restore from one of these backups.

MySQL Command Line Client
# mysql -u root -p Enter password: ********** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 35 Server version: 5.0.51b-community-nt MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

However, as simple as this solution is, it still requires manual intervention. Luckily, MySQL comes with a command line interface (CLI) client that can be used as the basis for an automted solution. Most people who have used the CLI client use it to log in remotely to the database: use SSH to get into the machine where the database is housed, use the CLI client to startup the interactive program, and then execute SQL commands.

The CLI client, however, is a very powerful tool for automating database operations. It has a number of parameters that can be used to execute queries, create backup files, restore data, add users, grant priviledges, even insert entire sets of data into the database.

While these commands can be put directly into cron, because databases typically have passwords and other sensitive information, it is best to wrap them in a script of some sort - and this is what I did.

The original scripts I wrote were part of a larger system, and so got certain information from the application's global configuration file. In addition, I needed to support multiple environments on the same database server, which meant that I couldn't just call my database X, I had to call it X_test, X_prod, and X_qc (to name a few), and the scripts had to be smart enough to figure out which environment they were supposed to operate on. Unfortunately, this makes the original scripts all but useless outside of their intended application.

I've modified the scripts so that you can run them directly, without any dependencies or any of the sophistication that was required for the original application. This has had a two-fold effect. The first is that the scripts have become rather simplistic, and don't seem much more useful than calling the MySQL client by itself. The second is that the number of arguments required has increased substantially.

For example, the restore.ksh script, as originally written, required only two parameters: the name of the file containing the data, and the name of the database the data should be placed into. The database host, user name, password, and port came from a configuration file, and the environment was used to deduce whether it was running in QC, test, production, and so on. With the version of the script here, everything is required is passed on the command line.

You can always hardcode the scripts so that these database parameters are defined in the scripts themselves. Another option is to put them in a simple name-value pair configuration file and use a combination of grep and cut to access them in your script, like so (note these are back-ticks, not single quotes):

# VALUE=`grep key config.ini | cut -f2 -d=`

Some might find these scripts useful as-is, but I would imagine most will use them as a starting point.

These scripts ended up being time savers for me because when I needed to do something infrequently, like printing the schema, I didn't have to waste time researching the exact CLI client syntax again.

View the code
backupDb.ksh	Backs up a specific database to an SQL file
backupTable.ksh	Backs up a specific table to an SQL file
restore.ksh	Restores whatever data is in the SQL file
getSchema.ksh	Stores the database schema
showUsers.ksh	Shows all MySQL users
showDatabases.ksh	Shows all MySQL databases

Download the code
db_scripts.tar.gz	2.02 KB	db_scripts.zip	7.69 KB
Note: Scripts are designed with a unix-like usage statement; invoke the script with no parameters, and it will tell you what parameters are available as well as which are required.
Note: Scripts return 0 upon success, and 1 upon failure.

Something wrong with this page or this site? Let the webmaster know by clicking HERE