Ticket #124 (closed enhancement: fixed)

Opened 6 years ago

Last modified 6 years ago

Live database backups

Reported by: chris Owned by: chris
Priority: major Milestone:
Component: Live server Keywords: mysql
Cc: ed, john, jim Estimated Number of Hours: 3.0
Add Hours to Ticket: 0 Billable?: yes
Total Hours: 9.65

Description (last modified by jim) (diff)

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.

Change History

comment:1 Changed 6 years ago by 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.

comment:2 Changed 6 years ago by 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

comment:3 Changed 6 years ago by jim

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

comment:4 Changed 6 years ago by chris

  • Cc ed, john added
  • Add Hours to Ticket changed from 0.0 to 5.0
  • Total Hours 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?

comment:5 Changed 6 years ago by 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.

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

comment:6 Changed 6 years ago by 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)

comment:7 Changed 6 years ago by chris

  • Add Hours to Ticket changed from 0.0 to 0.15
  • Total Hours 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.

comment:8 Changed 6 years ago by 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

comment:9 Changed 6 years ago by jim

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

comment:10 Changed 6 years ago by chris

  • Add Hours to Ticket changed from 0.0 to 30.0
  • Total Hours 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.

comment:11 Changed 6 years ago by chris

  • Add Hours to Ticket changed from 0.0 to -29.5
  • Total Hours 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...

comment:12 Changed 6 years ago by chris

  • Add Hours to Ticket changed from 0.0 to 1.0
  • Total Hours 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?

comment:13 Changed 6 years ago by 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!

comment:14 Changed 6 years ago by chris

  • Add Hours to Ticket changed from 0.0 to 2.0
  • Total Hours 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!

comment:15 Changed 6 years ago by chris

  • Cc jim added
  • Add Hours to Ticket changed from 0.0 to 1.0
  • Status changed from new to closed
  • Resolution set to fixed
  • Total Hours 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.

comment:16 Changed 6 years ago by 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.

Note: See TracTickets for help on using tickets.