William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

Tag Archives: mysqldump

3 tips: mysqldump -routines, unique php $_POST keys, mysql group by

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.

Advertisements

MySQL: 2 Things about Stored Procedure

1. How to check definition of stored procedure definition
  • In pma (PhpMyAdmin) GUI, it is quite simple:
    After the tables displayed,
    there is a routines link. Click it, the procedure details are displayed.
  • From command line:
    mysql> select name, db, type from mysql.proc;
    mysql> select name from mysql.proc 
      where db = 'db_name' and `type` = 'PROCEDURE'
    mysql> show procedure status;
    

    to get the list of stored procedures; then, to show individual procedure:

    mysql> show create procedure proc_name;
    mysql> show create function func_name; 
    

MySQL’s procedures and functions are not as powerful as PL/SQL or T-SQL. However, for a repeated or complex insert/update process which involve transaction, multi-statements, multi-tables, using procedure is a primary choice.

2. Extract/restore procedures/functions
To dump a database, we use mysqldump; to restore the database, we use mysql command:

$ mysqldump -u test -p <database> > database.sql
$ mysql --verbose --user=test 
  --password=test <database> < database.sql

The issue is: mysqldump will backup by default all the triggers but NOT the stored procedures/functions.
Suppose we have some of procedures/functions, the above mysqldump/mysql are not enough to dump all.

To solve it, there are 2 mysqldump parameters could control this behavior:

  • – routines – FALSE by default
    routines mean procedures/functions, it remarks that by default, all the procedures and functions do not dump.
  • – triggers – TRUE by default
    by defult, all triggers do dump with schema and data.

This means that if we want to dump ALL,
we only need to add the –routines command line parameter:

mysqldump --user=test --password=test 
  --routines > procedures.sql 

The above will dump all database structure/data as well as procedures/functions.

Let’s assume we ONLY want to backup the stored procedures/functions, not the mysql tables and data
(this can be useful to import these into 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 
--user=test --password=test 
--verbose <database> > procedures.sql

By using this, we can operate the stored procedures and functions alone or with whole database schema and data for the purpose of database transfer.