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 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:-
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?
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?
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?
There are two backup types: physical and logical. Physical (Percona XtraBackup, RDS/LVM Snapshots, MySQL Enterprise Backup), and also you can use cp or rsync command lines to copy the datadir as long as mysql is down/stopped.
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.
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.
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 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.
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With