Transition Technology: Ticket #124: Live database backups

chris — Tue, 20 Jul 2010 13:42:07 GMT

From Benjamin at Gaia:

The backups are dropped in /var/backups daily and are named for the day
of the week, resulting in 7 days' worth of rotating backups. The script
which generates the backups is at /root/sbin/mysql_backup_daily.sh
If you want to copy the backup file out to another server after it is
generated, you can alter that script (or have us update the script) to
perform the remote copy. I've included an example line in the file which
uses scp to copy the file to an (example) remote server.

chris — Sat, 31 Jul 2010 11:34:56 GMT

The automatic syncing of Mysql backups and a copy of all files has been sorted out, see:

https://tech.transitionnetwork.org/trac/wiki/LiveServer#Backups

The script to update the dev and test sites is still to be done.

The time for sorting this out as been added to ticket #97

description changed

jim — Mon, 09 Aug 2010 22:06:05 GMT

description modified (diff)

Stripping the emails is pointless since Reroute Email is already installed and configured in DEV, TEST and LIVE (http://drupal.org/project/reroute_email). Updating description.

hours, totalhours changed; cc set

chris — Fri, 01 Oct 2010 16:27:28 GMT

cc ed, john added
hours changed from 0.0 to 5.0
totalhours changed from 0.0 to 5.0

I have written a script to update either the http://dev.transitionnetwork.org.webarch.net/ or the http://test.transitionnetwork.org.webarch.net/ sites with the last MySQL dump from the live site and also copy across backups of the uploaded files and I have documented it at DevelopmentServer#live2dev and the script can be viewed at attachment:wiki:DevelopmentServer:live2dev.txt

There is one problem with it though, and I guess some additional SQL commands are needed to fix this, the link to the login page redirects users to the live server:

http://dev.transitionnetwork.org.webarch.net/user/login

Has anyone got any idea why this is?

jim — Fri, 01 Oct 2010 16:50:26 GMT

This is because a few things need tweaking before what works in LIVE can work on a different domain.

Secure Pages: wants a secure and non-secure base URL. These MUST match the domain, and by extension the cookie domain. A simple way is to add...
```
or `name` like '%secure%'
```

...to the SQL I sent last night which then disables SSL all together. A more complex way is to correct these fields to match the correct base domain for DEV/TEST as part of the import script.

A lesser issue is the files/ folder location: For the new Workspaces subdomain, the sites use workspaces.[base domain] and hence Drupal multisite stores these sub-domains' details in /sites/workspaces.[base domain]. In here is that domain's own settings.php, plus tmp/ and files/ folders. Hence the database has a reference to the wrong path since the base domains are different. Not a biggie but worth noting.

I would imagine these are both somewhere in the variables table, stored as serialised data against the variable name. I'll have a look.

Chris: This is all very good, but it'll need to be a manual process, at least for DEV since a developer could lose their work if an automatic sync happened whilst they were working. I assume this is manual and run with a ./live2dev command or something? TEST could be more automatic (weekly etc), but again often things are being pushed the other way (from DEV). Thoughts?

jim — Fri, 01 Oct 2010 16:55:12 GMT

OK... the "variable" table is the place... Data from my system, which has SSL disabled.

Secure pages stuff:

mysql> select * from variable where name like '%securepages_base%';
+--------------------------+---------+
| name                     | value   |
+--------------------------+---------+
| securepages_basepath     | s:0:""; |
| securepages_basepath_ssl | s:0:""; |
+--------------------------+---------+
2 rows in set (0.00 sec)

File system paths:

mysql> select * from variable where name like '%file_dir%';
+---------------------+-----------------------------+
| name                | value                       |
+---------------------+-----------------------------+
| file_directory_temp | s:4:"/tmp";                 |
| file_directory_path | s:19:"sites/default/files"; |
+---------------------+-----------------------------+
2 rows in set (0.00 sec)

hours, totalhours changed

chris — Fri, 01 Oct 2010 17:41:45 GMT

hours changed from 0.0 to 0.15
totalhours changed from 5.0 to 5.15

Thanks for the suggestions, it is a manual process, the live2dev script needs to be run on the command line to update a site.

Do the numbers in these variables matter, eg the 32 and 33?

+--------------------------+------------------------------------------------------+
| name                     | value                                                |
+--------------------------+------------------------------------------------------+
| securepages_basepath     | s:32:"http://dev.transitionnetwork.org.webarch.net/" |
| securepages_basepath_ssl | s:33:"https://www.transitionnetwork.org";            |
+--------------------------+------------------------------------------------------+

If they do and are potentially going to change I can read them from the database and then update the fields with the same number and the correct URL.

jim — Fri, 01 Oct 2010 18:12:11 GMT

It's serialised array data and in this case 's' means String, 32 is the number of characters (or bytes).

More here: http://php.net/manual/en/function.serialize.php

jim — Fri, 01 Oct 2010 18:13:14 GMT

In fact, perhaps it might be a good idea to call PHP's serialise() method here for safety?

hours, totalhours changed

chris — Sat, 02 Oct 2010 11:21:42 GMT

hours changed from 0.0 to 30.0
totalhours changed from 5.15 to 35.15

In fact, perhaps it might be a good idea to call PHP's serialise() method here for safety?

Good thinking, I've written a php script that we can use, it takes a string on the command line and serialises it, eg:

./serialize.php http://dev.transitionnetwork.org.webarch.net/
s:45:"http://dev.transitionnetwork.org.webarch.net/";

This is the script

#!/usr/bin/php
<?php
if ($argc != 2 ) {
?>
This is a command line PHP script which requires a string to be passed to it on the command line.
  Usage:
  <?php echo $argv[0]; ?> <option>
  <option> the string you want to serialize
<?php
} else {
$serialized = serialize($argv[1]);
echo $serialized;
}
?>

So, unless anyone can think of any problems with this I'll use it to serialise the strings we want to update for the dev and test sites.

hours, totalhours changed

chris — Sat, 02 Oct 2010 11:37:26 GMT

hours changed from 0.0 to -29.5
totalhours changed from 35.15 to 5.65

Opps I added 30 hours to this ticket rather than 30mins!

Hopefully we can add negative hours to tickets to correct mistakes like this...

hours, totalhours changed

chris — Mon, 04 Oct 2010 14:19:02 GMT

hours changed from 0.0 to 1.0
totalhours changed from 5.65 to 6.65

The latest version of the script, attachment:wiki:DevelopmentServer:live2dev calls a bash script, attachment:wiki:DevelopmentServer:serialize.sh which uses attachment:wiki:DevelopmentServer:serialize.php to serialise the data that needs it.

I think this script is now done, unless anything can think of any other things it should do?

jim — Wed, 13 Oct 2010 19:00:05 GMT

This is very good but I've noticed a couple of snags on DEV...

1) The site can't handle the files any more due to a file ownership issue: /sites/default/files is owned by 'live', not root on Kiwi... Indeed all files are owned by 'root' or 'live'

jim@kiwi:/web/dev.transitionnetwork.org.webarch.net/www/sites/default$ ls -al
total 108
drwxr-xr-x  4 root root  4096 2010-09-29 18:41 .
drwxr-xr-x  6 root root  4096 2010-09-16 17:49 ..
-rw-r--r--  1 root root  9724 2010-03-06 14:09 default.settings.php
drwxr-xr-x 24 live live 69632 2010-10-13 19:06 files
-rwxr-xr-x  1 root root 10269 2010-09-29 18:27 settings.php
drwxr-xr-x  6 root root  4096 2010-10-13 19:37 .svn

Hence the server cannot access this directory and Drupal freaks out. I've chown'ed recursively to fix for now but this needs adding to the scripts ideally

jim@kiwi:/web/dev.transitionnetwork.org.webarch.net/www$ sudo chown -R www-data:www-data .

2) (maybe) All the tables starting "cache" tables need truncating/emptying so that weird caching issues don't happen.

Apart from the above, all looks good!

hours, totalhours changed

chris — Fri, 05 Nov 2010 15:36:54 GMT

hours changed from 0.0 to 2.0
totalhours changed from 6.65 to 8.65

I'm having to rewrite a lot of the script to make it work with a different format of DB dumps we have now (compared to the FreeBSD ones from gaia) and also it wasn't updating the workspaces databases.

I'm working on it now so the dev site is probably unusable till I'm done!

cc, hours, status, totalhours changed; resolution set

chris — Thu, 11 Nov 2010 14:50:13 GMT

cc jim added
hours changed from 0.0 to 1.0
status changed from new to closed
resolution set to fixed
totalhours changed from 8.65 to 9.65

The scripts and docs are now all up to date following the server move trac:ticket/147, see wiki:NewLiveServer#backup2kiwiwiki, wiki/DevelopmentServer#live2dev and wiki/DevelopmentServer#wiki-live2dev.

chris — Thu, 11 Nov 2010 14:57:14 GMT

Oops, 3 of the links were wrong, that'll teach me not to preview posts! I should have posted:

The scripts and docs are now all up to date following the server move ticket:147, see wiki:NewLiveServer#backup2kiwiwiki, wiki:DevelopmentServer#live2dev and wiki:DevelopmentServer#wiki-live2dev.