[solved] Getting hold of remote hosted database frm old site

[paulbaker]

New client. Existing CMSMS running 1.9.4.1. Front end and back end working OK.

Need to grab a backup of the site and database and do an upgrade.

I have FTP access. I have downloaded all files, so we're OK there.

But having trouble getting a backup of the database.

Config file shows it connects to a remote server; db_hostname is an IP address (on a different host entirely). Client has no access to this server and has lost contact with the person that owned it/set it up.

Tried to set up phpmyadmin in a sub directory of the website and connect like that, but no joy, looks like the remote server ignores the connection or perhaps the host doesn't allow what phpmyadmin is trying to do - I just get a blank screen.

Set up phpmyadmin on another host to try to connect from there. Again no joy but got "phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection" which is probably the remote server rejecting connection attempts from an IP that is not in its whitelist.

So I'm now thinking install a module to grab the database.

MysqlDump looks promising http://dev.cmsmadesimple.org/projects/mysqldump

Anyone used it successfully on an old site like this?

Or any better ideas to get hold of the database?

Thanks

----------------------------------------------

Cms Version: 1.9.4.1

Installed Modules:

CMSMailer: 2.0.1
FileManager: 1.1.0
MenuManager: 1.7.6
ModuleManager: 1.4.2
News: 2.11.2
nuSOAP: 1.0.2
Printing: 1.1.2
Search: 1.6.10
ThemeManager: 1.1.4
TinyMCE: 2.8.4
CGExtensions: 1.24.4
FormBuilder: 0.7
CGEcommerceBase: 1.2.4
CGSimpleSmarty: 1.4.8
Products: 2.10.3
Cart: 1.7.5
CGPaymentGatewayBase: 1.0.9
PaypalGateway: 2.3.3
FrontEndUsers: 1.12.14
Orders: 1.11.5
Captcha: 0.4.3
Gallery: 1.4.4
FormBrowser: 0.4

Config Information:

php_memory_limit:
process_whole_template: false
output_compression: false
max_upload_size: 20000000
default_upload_permission: 664
url_rewriting: mod_rewrite
page_extension:
query_var: page
image_manipulation_prog: GD
auto_alias_content: true
locale:
default_encoding: utf-8
admin_encoding: utf-8
set_names: true

Php Information:

phpversion: 5.3.3-7+squeeze3
md5_function: On (True)
gd_version: 2
tempnam_function: On (True)
magic_quotes_runtime: Off (False)
E_STRICT: 0
E_DEPRECATED: 8192
memory_limit: 32M
max_execution_time: 30
output_buffering: On
safe_mode: Off (False)
file_uploads: On (True)
post_max_size: 20M
upload_max_filesize: 20M
session_save_path: No check because open basedir active
session_use_cookies: On (True)
xml_function: On (True)

Server Information:

Server Api: apache2handler
Server Db Type: MySQL (mysql)
Server Db Version: 5.5.35

----------------------------------------------

[Jo Morg]

Anyone used it successfully on an old site like this?

Or any better ideas to get hold of the database?

I tend to install and use http://dev.cmsmadesimple.org/projects/btadminer which, although is marked as stale, works perfectly for me.
It seems to have more and better features than MysqlDump.
But it's doable.

[paulbaker]

Thanks Jo.

I compared the 2 modules MySQLDump and btAdminer and the former looked simpler. Since I have no way of rebuilding the site if it goes pear shaped, I chose to install that one.

It installed OK but when I ran the job I got

Warning: system() has been disabled for security reasons in /var/www/ew7104740/public_html/modules/MysqlDump/action.dumpdatabase.php on line 82

Task completed
-- Filesize : 0 bytes
ERROR : backups/backup-2014-11-17_23-51-38.sql MISSING or TOO SMALL

So it seems the host disables system() function.

I then tried to install btAdminer (latest version); when attempting to upload the XML file I got

Fatal error: Access level to btAdminer::$config must be public (as in class CMSModule) in /var/www/ew7104740/public_html/modules/btAdminer/btAdminer.module.php on line 75

Any more ideas, anybody?

[Dr.CSS]

I just used httrack and ripped a site then I had to make it off of what I got, of course I didn't have any access to the site files/folders/etc. so it should work better for you...

[paulbaker]

Thanks Dr CSS yes I have done that so have a local copy of the HTML but was really hoping to avoid having to rebuild. That's madness considering the database is just sitting there.

[paulbaker]

This problem has now been "solved" because we have managed to get hold of a backup of the live database. I would still be interested in hearing other ideas though.

[rotezecke]

i havent used mysqldump in quite some time, but here's a snippet (BASH) that dumps multiple databases from one server, and restores them on another. this was meant as cron job and worked a treat (probably still does). it also archives the dump files.

Code: Select all

#!/bin/bash
DIR=/var/www/backup/
WEB_DIR=/var/www/cmsms/
DATESTAMP=$(date +%Y%m%d)

DB_USER=xxxx
DB_PASS='xxxx'
REMOTE_HOST=fleas

LOGFILE=/var/log/mysqldump.log
# databases to be backed up - add more databases to the array if needed
DB_LIST='nimfm_newdb cmsms_db cdlibrary_db'

# create backups securely
umask 027

#% we must exit on errors, ie fail to backup bad data, so one $rc will do
rc=0

for i in $DB_LIST; do
  DB=$i
  #m let's assume all files in $DIR are .gz files, except the last one of each database
  #m some error checking/warning mail if OLDFILE cannot be found? or more than one?
  #m this looks a bit dodgy
  OLDFILENAME=`find $DIR -name "${DB}.*.sql"` 
  FILENAME=${DIR}${DB}.${DATESTAMP}.sql

  mysqldump -u $DB_USER -p"$DB_PASS" --opt $DB > $FILENAME 
  rc=$? 
  if [ $rc -eq 0 ]; then
      # now that we know the dump was successful
      diff -I 'Dump completed on ' $OLDFILENAME $FILENAME 2> /dev/null
      rc=$?
      #0=didnt change ; 1=changed; >1 Error?
      if [ $rc -ne 0 ]; then
            mysql -u $DB_USER -p"$DB_PASS" -h $REMOTE_HOST $DB < $FILENAME
            rc=$?
            if [ $rc -eq 0 ]; then
                echo "`date` $DB update on $REMOTE_HOST - OK." >> $LOGFILE
            else
                echo "`date` ERROR $rc - failed to restore $DB on $REMOTE_HOST" >> $LOGFILE
                echo "`date` ERROR $rc - failed to restore $DB on $REMOTE_HOST" # -> mail
	        #m how is stout mailed to root?
		break
            fi
            gzip -f $OLDFILENAME && chown :adm ${OLDFILENAME}.gz
            rc=$?; [ $rc -ne 0 ] && echo "gzip $OLDFILENAME error $rc" && break	# unlikely w/ -f
	    #m where does the above echo show?
	    #m even though unlikely, an email would hurt as we now have 2 uncompressed files of one database
       else
            echo "`date` $DB did not change" >> $LOGFILE
            rm -f $FILENAME
       fi
  else
    echo "`date` ERROR $rc - failed to dump $DB to $DIR" >> $LOGFILE 
    echo "`date` ERROR $rc - failed to dump $DB to $DIR"  # stdout -> mail
    break
  fi
done
....

[paulbaker]

Thanks for the suggestion rotezecke.

[Jeff]

You could look at dumping the data INTO an OUTFILE via a sql call on $db in an UDT.

http://dev.mysql.com/doc/refman/5.1/en/select-into.html