Page 1 of 1

table prefix contribution

Posted: Mon Jun 08, 2015 10:49 am
by davidgwalker
I have received a useful contribution from a developer using phpMysqlAutoBackup, and have posted here as it will be useful to others and maybe included in the next release.

Download here:
http://www.dwalker.co.uk/phpmysqlautoba ... bution.zip

Notes from the developer:

    The main change for me is caused due to I often run multiple systems (websites) off of a one physical database where there is a possibility that they may need to interact. For instance, for one of my systems they operate a website for each of their 4 branch offices, 2 additional websites for the head office and another system for their web-traffic analytics. So that is 7 logical websites all within 1 physcial MySQL database. Having 7 “databases” in 1 making things like advertising employment vacancies from across the group very simple. Of course the logical-versus-physical databases is achieved very simply through table prefixes “br1_” for branch1 etc.

    Backups need to be on a system by system basis (i.e. one backup per website (logical database) . So I use Phpmysqlautoback and want to use the $table_select but of course I do not want to maintain a list of table names in excess of 150 tables x 7 websites


    So my slightly customised version of Phpmysqlautobackup is installed in the filesystem for each of the 7 websites, and the main modification is in run.php I set a variable called $table_prefix to a value, e.g. “br1_”

    Then in schema-for-export.php I do a Show Tables Like and merge the result in to $tables_select

    This also means that I need a control table phpmysqlautoback in the database for each logical database , so I defined a constant for the control table name and prefix it with the same table prefix as being backed up if set. i.e. the control name is now “prefix_phpmysqlautobackup”

    So my version of Phpmysqlautoback is enclosed in the attached zip – if you think any of this is useful then I hope you’ll adopt the code and/or ideas in to your great product

    A few other tiny points to consider

    · When running in debug mode I was getting strict warning messages due to PDO::prepare ought to have an options array, so I fixed that throughout

    · Similarly, I like many other buy hosting from a 3rd party and they seriously limit the extent we can play with php.ini config and htaccess – with the end result that I was also getting strict warning messages because the default time zone is not set, so I fixed that too

    · Lastly – the way implemented the logic around $table_like should play nicely with $table_select – but:

    o It does NOT play nicely with $table_exclude, a few small changes ought to to cope with the scenario include tables like “br1_” AND select tables “HO1_.........” but exclude “br1_not_this_table”;

    o I have not tested all of the permutaions of $table_select / $table_exclude / $table_prefix being set/unset

    Also enclosed in the zip file are DIF reports on both run.php and schema-for-export.php that compares my changes to your verion 1.6.3 in case that helps you spot the changes