MySQL database housekeeping

As part of regular ongoing database housekeeping, you should repair and optimise your MySQL tables on a regular basis.

To repair one or more tables:

repair table table1, table2, table3, table4, table5;

To optimise one or more tables:

optimize table table1, table2, table3, table4, table5;

Depending on how dynamic your database is, you should probably aim to do both of the above at least once a month. And always repair your tables before your optimise them.

MySQL query cache to the rescue

Dynamic database driven websites are great. You just add your content and the website software grabs that content from the database, applies all the necessary formatting and displays it nicely and consistently to the user.

Until you wind up with thousands of “articles” and a very popular website, as one user complained in a recent forum topic. Googling for “+php +memory_limit” produces over 400,000 results, which shows just how widespread this problem really is.

Read more >>

Finding duplicate records with SQL

Although I don’t spend a lot of time doing any type of relational database administration, occasionally I get asked to find duplicate records in tables. Here’s the easiest way to do it, assuming your database supports subqueries:

select f1, f2, f3 from t1 where f1 in (
   select f1 from t1 group by f1 having count(f1) > 1
)
order by f1;

I’ve surmised that you’re trying to find duplicates on the “f1″ field. Substitute “t1″ with your actual table name and “f1″, “f2″ and “f3″ with your actual field names and include or omit as many field names you want to output in the first part of the statement.

Schedule Organizer

I stumbled across this one while doing some research on a totally unrelated topic. Having been sufficiently impressed with it’s simultaneous simplicity and affability, I thought I might share my findings.

Schedule Organizer is a PHP/MySQL online scheduling system which offers a flexible, easy to use and powerful web-based interface for your clients to request and manage appointments with your business or organisation.

Read more >>

Automatic MySQL Backup

If you need to archive your MySQL databases in a Linux environment, there’s no better tool than Harley’s AutoMySQLBackup.

The main script features include:

• Backup multiple MySQL databases with one script.
• Backup all databases to a single backup file or to a separate directory and file for each database.
• Automatically compress the backup files to save disk space using either gzip or bzip2 compression.
• Can backup remote MySQL servers to a central server.
• Runs automatically using cron or can be run manually.
• Can email the backup log to any specified email address.
• Can email the compressed database backup files to the specified email address.
• Can specify maximum size backup to email.
• Can be set to run PRE and POST backup commands.
• Choose which day of the week to run weekly backups.

Easy to setup, configure and, combined with cron, really useful too.