General purpose remote data backup and download - including InnoDb support

Question

I want a PHP based solution to backup database (only data and not code) of a remote server and download the file. I know that Shell based solutions are better for doing such things (running a shell script on local system and connecting through SSH to remote system) but it is a requirement to have a PHP based solution where knowing a URL and having database credentials is enough for a non-techie to take backups. The PHP script can be uploaded to the remote server and executed.

Following are the features I want:-

• Should have support for InnoDb engine at least - foreign key constraints should be exported. No harm if it supports all other engines.

• Should work on all servers, in the presence of the maximum possible amount of restrictions (I know about a few restrictions like safe_mode enabled, exec(), system() functions disabled etc.). I want a very general purpose solution which is guaranteed to work anywhere.

• Process should be password authenticated (asks for database credentials).

Now, I am breaking down things and starting from the very basics. Following are my assumptions of things so far and some questions:-

1. I am not sure if system functions like exec, system etc. can be completely disabled in shared hosting servers or not. If they are disabled such that they cannot be overridden, then the mysqldump based solution given here will not work universally.
   Question - If however only safe_mode is on such that system functions can execute on files present inside safe_mode_exec_dir, will the solution work securely?

2. I asked a question regarding security risks of doing this using PHP and understood that the backup file should never be created (I assume, in case of a mysqldump based solution, backup file needs to be created first before downloading) in the webspace. So, the solution should not need the backup files to be created there (no problem if creates in other locations).
   Question - But, will shared hosting providers allow this?

3. I checked out various general-purpose user contributed PHP classes like phpmysqldump etc. and did not find the usage of mysqldump based solution using system commands there to take backup. They do things like SHOW CREATE TABLE etc. to get all the table creation, data insertion queries and then download those things without actually saving it as a file (so no security risk).
   Question - Am I correct to conclude that they do all these things without doing a simple mysqldump as given in the solution in the first point because this cannot be a general purpose and secure solution?
   Question - Also, I read that there aren't any good ones which work well. I personally used only this phpmysqldump and it gives me mysql errors when I try to restore a database with the backup created. The queries in the dump file also look somewhat different from those created by PhpMyAdmin's export module. I also checked a few other free user contributed PHP classes. It looks like most of them do not support InnoDb support and so foriegn key constraints, if present in the database are not present in the export.
   Question - The export functionality of PhpMyAdmin itself, if present separately could be the solution for me, I guess. Does anybody know of any stable library like this one?

Answer 1

What I think you should do:

I Think you should install phpmyadmin on your server, this will allow you to access your database from work/school/cafe/etc, MySQL-workbench is more advanced and gives you more features so you can deal with changing the structure and editing any rows/columns, relations, and much more, look at phpmyadmin's features it has most if not all.

phpmyadmin it works in any web-browser:

I really recommended phpMyAdmin it has many SQL features to help you deal with everything when it comes to the MySQL database if you are using innoDB then you get even more features such as relation-ships between tables.

phpMyAdmin has the following features:

• Intuitive web interface
• Support for most MySQL features:
• browse and drop databases, tables, views, fields and indexes
• create, copy, drop, rename and alter databases, tables, fields and indexes
• maintenance server, databases and tables, with proposals on server configuration
• execute, edit and bookmark any SQL-statement, even batch-queries
• manage MySQL users and privileges
• manage stored procedures and triggers
• Import data from CSV and SQL
• Export data to various formats: CSV, SQL, XML, PDF, ISO/IEC 26300 - OpenDocument Text and Spreadsheet, Word, Excel, LATEX and others
• Administering multiple servers
• Creating PDF graphics of your database layout
• Creating complex queries using Query-by-example (QBE)
• Searching globally in a database or a subset of it
• Transforming stored data into any format using a set of predefined functions, like displaying BLOB-data as image or download-link
• And much more...

Everything you listed above is included in phpMyAdmin, if you are running debian or Debian-based system simply run:

root@debian:~ # aptitude install phpmyadmin
root@arch:~ # pacman -S phpmyadmin

BTW: if you are not using Apache or lighttpd for the http-server you will need to read through the conf files for phpmyadmin and then write up the required conf script for phpmyadmin to work with your http-server.

MySQL workbench. Its cross-platform and works great.

MySQL workbench visurally see what you are doing with your database. http://diariolinux.com/wp-content/uploads/2008/08/wb51linuxpreview2a.png

BTW: Use <ctrl>+<G> to forward engineer a database, It took me a while to find out for to do this.

Stand alone perl file that works just after you configure it: (untested)

use DBI;
my $user = "username";   # MySQL Username
my $pass = "xxxx";       # MySQL Password
my $host = "localhost";  # MySQL Host
my $mydb = "zzzz";       # MySQL Database
my $file = "test.sql";   # Import file
my $sqlServer = "mysql"; # What sql-server are we using, oracle/mysql/etc
    # I would use the following method to configure it, though the above works fine too.
($user,$pass,$host,$mydb,$file,sqlServer) = (
    "username",  # MySQL Username
    "password",  # MySQL Password
    "localhost", # MySQL Host
    "myDB",      # MySQL Database
    "test.sql",  # Imported file
    "mysql"      # What sql-server are we using, oracle/mysql/etc
);
    # Now lets connect to the MySQL server.
my $dbh  = DBI->connect("DBI:$sqlServer:$mydb:$host",$user,$pass)or die DBI->errstr();
    # Lets now open the .sql file.
open(INPUT,$file);
    # Now lets run each sql-statement.
while ($line = <INPUT>){
    print $line;
    $dbh->do($line);
    print "Query failed (run manually):\n$line\n\n ". $dbh->errstr()."\n" if $dbh->errstr();
}
    # Now close the file.
close(INPUT);

Answer 2

PHPMyAdmin is a great solution in general but may be overkill if you only ever want to do backups. If you want a universal solution then absolutely don't rely on exec being available. PHP combined with the right SQL queries (as you have noted in your point 3.) can provide you with all the information you need and will always work.

Answer 3

In my own experience, relying on PHP based backup solution is VERY bad idea. If you are dealing with large backups, PHP can easily fail (due to timeouts, memory consumption or something undefined).

We have been successfully using Bacula (http://www.bacula.org/en/) for years now on our un*x servers. Did our own backup scripts which do whatever we want to do. You need to have shell access to server off course.

If you need simple backup php based solution, you are best with writing your own. :)