I found a way on how to code the backup for mysql database table. but I was wondering on how PMA(PHPMyAdmin) does this scenario on backing up the sql file. the one that has AUTOMATIC download to your machine.
this is the simple syntax I made myself

See Also:
 Cron Job Tutorial: Crontab Scheduling Syntax and Script Example
 Constant Contact Chooses SkySQL for MySQL Support
 Manage Backups for WordPress Files and Databases
This application will help you in the following ways:

It will allow you to do backups automatically, instead of manually with a hosting control panel or phpMyAdmin.
It will let you use your hosting cron feature to specify that backups run weekly, monthly, etc.
It will place the MySQL database backup in a secure folder. This secure folder is not accessible to the public. The hosting cron will be the only one that can access this folder.
It will place the cron PHP script that will do the backup in the secure folder. This will prevent public users from accidentally executing your PHP script with a browser.
It will use SSH to safely download your MySQL database from your hosting server to your local computer.
Basic Requirements to Get Started

Not all hosting configurations can implement this PHP- MySQL database backup script. So make sure your system meets the following requirements:

Linux/Apache and MySQL database.
The following PHP functions should be enabled in your hosting account:
system - refer to PHP manual here: http://php.net/manual/en/function.system.php
escapeshellcmd - http://php.net/manual/en/function.escapeshellcmd.php
You cannot implement this application using a free web hosting account, because these PHP functions will be disabled for security reasons.

If you have a paid hosting account, you need to log in to your hosting panel and look for php.ini file. If you have trouble finding this file, ask your hosting support.

This application is tested to run on PHP 5, but it may have problems with other versions.

The screen shot below shows PHP 5.2.5 being used in the hosting account, and a link "here" to the php.ini file.

To enable the function above, make sure that disable_functions are commented. This means that they are enabled.

If they are not commented, they are disabled. For example:

disable_functions = exec, passthru, system, popen, fsockopen, pfsockopen

The above code means that the functions named exec, passthru, system, popen, fsockopen and pfsockopen are disabled. To enable them:

;disable_functions = exec, passthru, system, popen, fsockopen, pfsockopen

Just put a semi-colon before disable_functions.

The PHP Script to Back Up a MySQL Database

Define MySQL database connection parameters. This is the database that you would like to have a backup.

$username = "root";
$password = "naruto";
$hostname = "localhost";
$database = "seats";

Sanitized or escape those following variables. This uses the escapeshellcmd function and is done for security reasons. Do not remove these lines, particularly if you are accepting user inputs.

$username =escapeshellcmd($username);
$password =escapeshellcmd($password);
$hostname =escapeshellcmd($hostname);
$database =escapeshellcmd($database);

Define the full path to your restricted folder. This folder is where you will place the PHP-MySQL backup script, as well as the generated MySQL database backup by the cron.

Make sure to define this correctly. If this is not correct, it will not produce a backup. To make sure you have the correct, full path to your backup restricted folder, follow the steps below.

Step 1. Create a folder in your website root directory named backuprestricted. Actually, you can name it anything you like.

Step 2. Set the file permission of the created folder to 755.

Step 3. Upload this PHP script (name it fullserverpath.php) inside this folder.


Step 4. Now, using a web browser, open the PHP script. Paste the URL into the browser address bar.


The result looks somewhat like this: /home/www/example.com/backuprestricted/fullserverpath.php

Therefore the full path to the backup folder is this: /home/www/example.com/backuprestricted/

Use it to define $backupFile variable below:


Also you have noticed that the backup file variable above is concatenated with date, database name and the sql extension. The purpose is to generate a timestamp, which is appended to the backup file. In this way, you will know when the backup was created. 

Formulate the MySQL dump command. This command will do the actual work of exporting the MySQL database and place the generated SQL file into the backup path you have specified.

$command = "mysqldump -u$username -p$password -h$hostname $database > $backupFile";

Finally, execute the system function. This function will execute the command. It takes two arguments, the $command and the $result. The $result will be useful for troubleshooting the output of the system function. For example, if the error code returned to the browser is 127, this means that the file path to the backup might be wrong or incorrect, and PHP cannot find it.

system($command, $result);
echo $result;
5 Years
Discussion Span
Last Post by pritaeas
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.