Live database backups

[chris]

Automate the nightly syncing of a database dump on the live server to the dev server and have an easy to import the live db, minus email addresses, into the dev and test sites.

[chris]

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]

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

[jim]

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.

[chris]

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]

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

1. 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.

2. 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]

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)

[chris]

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]

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]

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

[chris]

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.

[chris]

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

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

[chris]

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]

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!

[chris]

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!

[chris]

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]

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.