Monday, July 25, 2011

MySQL "Push" backup strategy

A "pull" backup strategy can be achieved by a MySQL replication.

Alternatively, a "push" backup strategy can also be achieved through cron or task scheduler with the following 2-step strategy:

1. take a backup of the database

> mysqldump [options] database > backup.sql

2. send or dump the backup to the remote database

> mysql -u<user> -p<password> -h<remotehost> [options] < backup.sql

Note: the second step should have an explicit password, it should not be blank; to avoid prompt.

You can then add this to your cronjob or Task Scheduler (on Windows) to execute at a certain interval and frequency.

Friday, July 22, 2011

Duplicating MySQL table to another table

Method #1: [the better option]

  1. get the creation table information of the table to be duplicated
  2. change the table name
  3. execute the script

Method #2: this will remove the index definitions of the table though. Use:

CREATE TABLE <newtable>  SELECT * FROM <oldtable> WHERE 1 = 0;

the where clause just says you are ONLY copying the structure and not the data.

Method #3: Copy to and from another database

  1. Copy the table to another database[#2]
  2. Rename the table on the other database[#2]
  3. Copy back to the source database[#1]


Tuesday, July 19, 2011

Requirement Management in four notes

  • Planning good requirements: "What the heck are we building?"
  • Collaboration and buy-in: "Just approve the scope, already!"
  • Traceability and change management: "Wait, does the dev team know that changed?"
  • Quality assurance: "Hello, did anyone test this thing?"

Friday, July 15, 2011

Scheduled Remote MySQL backup using PHP

There's a nice 10 ways to backup mysql database from here.

There's also the use of MySQL Administrator or MySQL Workbench if both machines are Windows-based.

However, if you have the MySQL remotely and can be on Linux; some are on Windows; on different machines; and you want to grab backup from all of them, this one can be helpful.

On my development desktop, I have PHP and MySQL installed. Though it is not necessary to have these installed, it's rather handy on a development workstation.

1. Copy, paste it to your favorite text editor and save it somewhere as backup.php (or any filename you wish, just make sure it's recognized by PHP). For the sake of this tutorial, I saved it at D:\DBbackup\

* This should be run on command prompt
* @params
* host
* user
* password
* [db] defaults as {host}
* [port] defaults as 3306
$DATABASES = array(
'user' => 'user1'
,'host' => ''
,'password' => 'mypassword'
'user' => 'user2'
,'host' => ''
,'password' => 'anotherpassword'
,'port' => '6609'
'user' => 'user2'
,'host' => ''
,'password' => 'itspassword'
,'db' => 'mydatabase'
,'port' => '3308'
# add as many database connection as needed using the structure above
if (defined('STDIN')){ # make sure it's only run on command line interface (CLI)
$start = date('H:i:s');
$date = date('Ymd');
foreach($DATABASES as $instance){
$port = (isset($instance['port']) ? $instance['port'] : '3306');
$db = (isset($instance['db']) ? $instance['db'] : $instance['user']);
exec("mysqldump "
." -h".$instance['host'] #host
." -u".$instance['user'] #user
." -p".$instance['password'] #password
." -P".$port
." --compress" #you may customize the options appropriate to your servers
." --create-options"
." --extended-insert"
." --no-create-db"
." --quote-names"
." --verbose"
." ".$db
." --result-file=".dirname(__FILE__).DIRECTORY_SEPARATOR.$date."-".$db."@".$instance['host'].".sql"
# you may add other routines here like:
# compress using 7z
exec("echo 'STARTED: ".$start." DONE: ".date('H:i:s')."' > ".$date.".txt"); #keep log
} else{
echo 'This should be run on a command line';

2. Test it on your local
a. open a command prompt on d:\dbbackup\ directory
> d:
> cd dbbackup
b. test php [PHPpath]php --info
> C:\PHP\php.exe D:\dbbackup\backup.php
c. it will create *.sql files on d:\dbbackup\ directory for every database you want to backup

3. Add as task to automate execution
a. Open Task Scheduler
b. Add new task that starts a program.
Program to execute: C:\PHP\php.exe
Parameters (or add it to Program to execute depending on your Windows version)
: D:\dbbackup\backup.php
This will basically execute as
C:\PHP\php.exe D:\dbbackup\backup.php
Put the desired frequency and schedule to backup.

The length of time to execute the script will depend on the amount of data and speed of your connection to the server.