1. mysqldump —routines

While I used mysqldump to backup Database, MySQL Stored Procedures and Functions are lost by default. I spent much time to debug since the webpages couldn’t run normally without their supports. The following article from web is very helpful which fix the problem by adding them when backup.

Dumping MySQL Stored Procedures, Functions and Triggers

By default mysqldump backups all the triggers but NOT the stored procedures/functions. There are 2 mysqldump parameters that control this behavior:

• –routines – FALSE by default
• –triggers – TRUE by default

This means that if you want to include in an existing backup script also the triggers and stored procedures you only need to add the –routines command line parameter:

mysqldump <other mysqldump options> --routines > outputfile.sql

Let’s assume we want to backup ONLY the stored procedures and triggers and not the mysql tables and data (this can be useful to import these in another db/server that has already the data but not the stored procedures and/or triggers), then we should run something like:

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt <database> > outputfile.sql

and this will save only the procedures/functions/triggers. If you need to import them to another db/server you will have to run something like:

mysql <database> < outputfile.sql

2. unique $_POSE keys

Sometimes when we post form, the post keys might be duplicated, such as re-enter passwords form. Before update/insert into DB, we need to unique them and extract useful info. The following function is suitable to apply for such case:

function array_unique_key($array) {
 $result = array();
 foreach (array_unique(array_keys($array)) as $tvalue) {
  $result[$tvalue] = $array[$tvalue];
 }
 return $result;
}

It does 3 things:

• using array_keys() to get $_POST keys which is a array.
• using array_unique() to unique the keys which removes the duplicates.
• generate a new array which holds the unique keys and their values.

3.mysql: get unique max record with same ids

I want to extract the latest updated record from table, which may be updated many times for the same primary key. e.g., there are 6 records (under same id) updated during this week, I prefer to fetch the latest record instead of all. The following is the simpleset way:

SELECT data.* FROM data INNER JOIN (SELECT MAX(id) AS id FROM data group by url) ids ON data.id = ids.id

group by get the records grouped, and MAX() select the latest.