Page tree
Skip to end of metadata
Go to start of metadata

Newscoop Replication: Article Synchronizing between Newscoop Servers

This document is a first approach to the Radio Package requirement regarding Trac ticket:2330 "Offline editing/Synchronizing Articles with Another Server".

Facts

Since Internet connectivity is not that good in some places, radio stations need to be able to create stories on their local intranet and then send them to the main web server (outside of the intranet) whenever connectivity is available. All media from the article must be uploaded to the web server.

The following must be synchronized:

  • Database (using MySQL replication)
  • images folder
  • files folder (attachments)
  • look folder (templates)
  • And also any Airtime storage server files that have been added as attachments to Newscoop articles.

Newscoop Installation

All what you need to do is run a normal Newscoop installation on both the master and slave servers. The most important thing at this point is to set the same instance name (newscoop is the default) for both installations.

Follow the installation guide from the Newscoop manual to do all of this in a right way.

Newscoop MySQL database replication

MySQL server has built-in support for replication, meaning that you can have on-the-fly copies of data stored in a main database server -known in this context as a Master server- in anothers servers -the Slave servers. Latest releases of MySQL server --5.x- allow you to create really complex replication scenarios, but this is not our case. All what you need to synchronize two newscoop servers is to use a simple MySQL replication scheme, known as Master Master replication.

When using replication, all updates to the tables that are replicated should be performed on the master server.

This way and to be clear, Local Newscoop server will be our 'Master 1' and 'Slave 2', and the Online Newscoop server will be the 'Slave 1' and 'Master 2'.

We really need to implement this scheme because of the article comments management. More details about this in the section Handling Article Comments at the end of this document.

Requirements

MySQL >= 4.1.0

Make sure MySQL versions installed on master and slave are compatibles to do replication. Take a look at http://dev.mysql.com/doc/refman/4.1/en/replication-compatibility.html.

Setting up

In general, what we need to do is:

  • Ignore ArticleComments, all Phorum tables ( phorum*_ ) and SystemPreferences when replicating from Local to Online.
  • Replicate all the rest newscoop tables from Local to Online.
  • Replicate ArticleComments and all the Phorum tables ( phorum*_ ) from Online to Local.

So, here we go.

1- Create an account on the 'Master 1' server that the 'Slave 1' can use to connect.

   mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
       -> TO 'replication_user'@'slave1server.yourdomain.com'
       -> IDENTIFIED BY 'a_strong_password';

Change 'replication_user', 'slave1server.yourdomain.com' and 'a_strong_password' to whatever you want to use and suit your installation.

2- Create an account on the 'Master 2' server that the 'Slave 2' can use to connect.

   mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
       -> TO 'replication_user'@'slave2server.yourdomain.com'
       -> IDENTIFIED BY 'a_strong_password';

Change 'replication_user', 'slave2server.yourdomain.com' and 'a_strong_password' to whatever you want to use and suit your installation.

3- Add the following lines to the MySQL configuration file on the 'Master 1' and on the 'Master 2' servers. The MySQL configuration file path is usually '/etc/mysql/my.cnf', if you installed MySQL from the standard packages.

   master 1:

   [mysqld]
   server-id = 1
   log-bin

   master-host = master2server.yourdomain.com
   master-port = 3306
   master-user = replication_user
   master-password = a_strong_password

   replicate-do-db = newscoop
   replicate-do-table = newscoop.ArticleComments
   replicate-wild-do-table = newscoop.phorum_%

The value for master-host can be the valid fqdn (fully qualified domain name) or the IP address of your 'Master 2' server.

   master 2:

   [mysqld]
   server-id = 2
   log-bin

   master-host = master1server.yourdomain.com
   master-port = 3306
   master-user = replication_user
   master-password = a_strong_password

   replicate-do-db = newscoop
   replicate-ignore-table = newscoop.ArticleComments
   replicate-ignore-table = newscoop.SystemPreferences
   replicate-wild-ignore-table = newscoop.phorum_%

The value for master-host can be the valid fqdn (fully qualified domain name) or the IP address of your 'Master 1' server.

Note all of the above mysqld entries are replication specific. You usually have another couple of entries there, but it does not matter for replication purposes.

4- We need to make sure both 'Master 1' and 'Slave 1' will have the same Newscoop database at the same status when starting replication.

If you are doing a new installation on both 'Master 1' and 'Slave 1' and you are totally sure that you are installing the same Newscoop package and databases are exactly the same, you can skip this step and continue on 4-. If you do not, continue here.

We have got to make a dump of the 'newscoop' database from our 'Master 1' server, then we will copy it to the 'Slave 1' server. Let us use the mysqldump utility to do this.

   $ mysqldump newscoop > /tmp/newscoop_dump.sql --extended-insert \
   > --master-data -u mysql_user -p

You will be prompted to enter the corresponding password for your MySQL user (default user is the current logged in user). Remember, you need to run this command as a MySQL user with the granted privileges required to do this.

This will create a text file -/tmp/newscoop_dump.sql- containing SQL statements to create the 'newscoop' database tables with data.

Now, copy/transfer the dump file to the 'Slave 1' server, make sure MySQL is running there. If you already have a 'newscoop' database on the 'Slave 1' it is time to drop all of the tables on it -do not forget backing up, maybe you still need it-. Finally, enter something like the following on the 'Slave 1':

   $ mysql newscoop < /tmp/newscoop_dump.sql -u mysql_user -p

This will execute all of the SQL statements in the dump file, which will include the CREATE and INSERT statements. Once the dumped-up databases are loaded onto the 'Slave 1' server, go to the final step.

5- Restart both mysql 'Master 1' and 'Master 2', then execute the following SQL statement while logged in as root on both of them:

   mysql> START SLAVE;

6- Check replication works

Setting up and getting work this simple kind of replication is quite easy. If you followed the above indications then replication would have to be working well now. However, you can check this by running the SHOW MASTER STATUS command on the masters and SHOW SLAVE STATUS on the slaves. Those commands will give you some information about the current status of both the Master and Slave servers.

If you are experiencing troubles and replication does not work as it should, please take a look at the corresponding section in the MySQL manual and the other links referenced at the end of this document.

Syncing Articles-related Files

This Radio Package feature allows you to be able to synchronize Newscoop Article-related files between the Local server and the Online server over the Internet. Our goal is to ensure no user intervention is required when the server is restarted (for passwords or keys).

Please note these instructions are specific to Ubuntu Linux versions 4.01 and later. However, it should be run quite well on any *NIX type OS.

Requirements

Just simple like this:

Setting up

Keep this in mind:

  • remoteuser = the user to be use on the Online server
  • remotehost = the Online server (hostname or IP address)
  • localuser = the user to be use on the Local server
  • localhost = the Local server (hostname or IP address)

The remote user must have write access to the Newscoop instance HTML directory on the remote host. The local user must have read access to the Newscoop instance HTML directory on the local host. The default value of this directory is '/usr/local/newscoop/www/newscoop/html'. You can achieve this by adding the remote/local user to the apache group (default is www-data on Debian/Ubuntu and apache on RedHat). On the remote machine you have to run the following command also:

  chmod g+w /path/to/newscoop/instance/html/directory/ -R

1- Test whether rsync over ssh works at all.

   $ rsync -avz -e ssh remoteuser@remotehost:/remote/dir /local/dir/

Type the corresponding password when prompted.

2- Configure the Local server.

We need to generate a private/public pair of keys to allow a ssh connection without asking for a password.

   $ cd ~
   $ mkdir .newscoop-rsync
   $ ssh-keygen -t dsa -b 1024 -f ~/.newscoop-rsync/localhost-rsync-key
   Generating public/private dsa key pair.
   Enter passphrase (empty for no passphrase):
   Enter same passphrase again:
   Your identification has been saved in /home/localuser/.newscoop-rsync/localhost-rsync-key.
   Your public key has been saved in /home/localuser/.newscoop-rsync/localhost-rsync-key.pub.
   The key fingerprint is:
   2e:28:d9:ec:85:21:e7:ff:73:df:2e:07:78:f0:d0:a0 localuser@localhost

You have got to create an empty passphrase, so just press enter when asked for it to generate the keys. Make sure to do this to avoid no other unauthorized user can read the private key file.

   $ chmod 600 .newscoop-rsync/localhost-rsync-key

Now we will put the public key into the 'authorized_keys' file on remotehost (a.k.a. our Online server) specifically the one for remoteuser.

Let us use scp to transfer the file over from localhost to remotehost.

   $ scp .newscoop-rsync/localhost-rsync-key.pub remoteuser@remothost:~/

3- Configure the Online server.

SSH'ing over to remotehost. Once we are on /home/remoteuser/ we need to make sure that every directory and file that we need to authorize connections with the transfered key exist.

   $ if [ ! -d .ssh ]; then mkdir .ssh ; chmod 700 .ssh ; fi
   $ mv localhost-rsync-key.pub .ssh/
   $ cd .ssh/
   $ if [ ! -f authorized_keys ]; then touch authorized_keys ; chmod 600 authorized_keys ; fi
   $ cat localhost-rsync-key.pub >> authorized_keys

Now, we can make connections to the Online server from anywhere. We can edit 'authorized_keys' to increase security by adding somethink like this -use your preferred text editor.

Change the public key, that looks like this:

   ssh-dss AAAB3NzaC1kc3MAAAEBAOy99zr4UZxKVTcVIZOoUdUNa5LWUn4urIdY4bxP4SuLbcJZumIqyrkV
   YmoT0Qk8l1peRvLkpyE/G5TAyKeS0wpxm28mEF2oRcLx5/ItD2hwfm53irUKYAjiaLwHkAtDZyHUQyZiKku
   s4LqZo2eo196diq3UYxebKX3p6N+l10yBvZr8NGxtyf+QVybxaIKw7qcnUEz+jldV3zY/+wyO/LVtuiD0qN
   W6yG2jv4kYf4ENTz5auMdh5rSv9TXVigDV5DGOHhIKMlQ4Oee0tZ6K82S3yq9mhjOm8KXRmEgr+GQaG/4Yg
   dBmhPeKfneAfbHwD41vb0fvMjpxX9zVX+V4EpEAAAAVAOIO/+vd01P/UyC1KRQz9J10VqfRAAABAE/d1adp
   YnBaH2qxpvyom54eDig6wltril7BjxxEkP9HJS1+03MIJLDE0ZZPfxyiZl4n2SzPMp7uxhd0llywud6dIiF
   GMZ2u8YyJcuRs2dEwIz5CyNtGdTks8G8d3nTRhJEwkXexG83c+/RzGFZZW1ZnBwlvkfRedU2dELwV9ofQpV
   lQhnl0H5eM1M2U0O0pLHADp89ViuoKle4hFpBzPQQ4h+j6bVeemlvvZVNilGnEwatEpRrmtMcpet1yZEqFD
   darGlh/+V5MSjALw4f1E4BYWo23r2qBnoZl6+1MP26SDysXJzxAbA1yvmwbCVZFsTsPlyHhasn5cR25z11g
   Ob8AAAEAB0tCrUN5V1LgohyVmlIWBOa4nFle+tEBLIPC5Bbqgm4LVQ3MUBs4CADeaJHGirSC5JuJuyJXPGQ
   JOSpOdLk/q4ZaJp0SlxcVqXGX1hHIlHE7NuqFPc4+TgPETRaPztTvvg5zA0kTo8CP4qeYtcIt+Mm7F4YGir
   ySeMspPN3Xwh7/0rgDrY/t+FwxPmSRKEIWo1Tztx89+u4yb2FHLB8Hku157GHz502P47jhSYgk2WgoKrJhF
   BrhjvKClVBu8oD7OA10nI9ZNGa4j1OfdTUUukGHoGxJhXhkVXR+mPJMRr6AD6dr/oadObF2Fe6itfRq2KYr
   BGA0M4KNE6LH/2RfAQ== holman@mutant

to:

   from="local_IP",command="/home/remoteuser/.newscoop-rsync/validate-rsync" ssh-dss
   AAAAB3NzaC1kc3MAAAEBAOy99zr4UZxKVTcVIZOoUdUNa5LWUn4urIdY4bxP4SuLbcJZumIqyrkVYmoT0Qk
   8l1peRvLkpyE/G5TAyKeS0wpxm28mEF2oRcLx5/ItD2hwfm53irUKYAjiaLwHkAtDZyHUQyZiKkus4LqZo2
   eo196diq3UYxebKX3p6N+l10yBvZr8NGxtyf+QVybxaIKw7qcnUEz+jldV3zY/+wyO/LVtuiD0qNW6yG2jv
   4kYf4ENTz5auMdh5rSv9TXVigDV5DGOHhIKMlQ4Oee0tZ6K82S3yq9mhjOm8KXRmEgr+GQaG/4YgdBmhPeK
   fneAfbHwD41vb0fvMjpxX9zVX+V4EpEAAAAVAOIO/+vd01P/UyC1KRQz9J10VqfRAAABAE/d1adpYnBaH2q
   xpvyom54eDig6wltril7BjxxEkP9HJS1+03MIJLDE0ZZPfxyiZl4n2SzPMp7uxhd0llywud6dIiFGMZ2u8Y
   yJcuRs2dEwIz5CyNtGdTks8G8d3nTRhJEwkXexG83c+/RzGFZZW1ZnBwlvkfRedU2dELwV9ofQpVlQhnl0H
   5eM1M2U0O0pLHADp89ViuoKle4hFpBzPQQ4h+j6bVeemlvvZVNilGnEwatEpRrmtMcpet1yZEqFDdarGlh/
   +V5MSjALw4f1E4BYWo23r2qBnoZl6+1MP26SDysXJzxAbA1yvmwbCVZFsTsPlyHhasn5cR25z11gOb8AAAE
   AB0tCrUN5V1LgohyVmlIWBOa4nFle+tEBLIPC5Bbqgm4LVQ3MUBs4CADeaJHGirSC5JuJuyJXPGQJOSpOdL
   k/q4ZaJp0SlxcVqXGX1hHIlHE7NuqFPc4+TgPETRaPztTvvg5zA0kTo8CP4qeYtcIt+Mm7F4YGirySeMspP
   N3Xwh7/0rgDrY/t+FwxPmSRKEIWo1Tztx89+u4yb2FHLB8Hku157GHz502P47jhSYgk2WgoKrJhFBrhjvKC
   VBu8oD7OA10nI9ZNGa4j1OfdTUUukGHoGxJhXhkVXR+mPJMRr6AD6dr/oadObF2Fe6itfRq2KYrBGA0M4KN
   E6LH/2RfAQ== holman@mutant

Note at the beginning of the line. 'local_IP' is the IP address of the Local server, and '/home/remoteuser/.newscoop-rsync/validate-rsync' is a script that you can create -on the Online server- by:

   $ mkdir .newscoop-rsync
   $ cd .newscoop-rsync
   $ vim validate-rsync   // or your preferred editor

and this is its content:

   #!/bin/sh

   case "$SSH_ORIGINAL_COMMAND" in
   *\&*)
       echo "Rejected"
       ;;
   *\(*)
       echo "Rejected"
       ;;
   *\{*)
       echo "Rejected"
       ;;
   *\;*)
       echo "Rejected"
       ;;
   *\<*)
       echo "Rejected"
       ;;
   *\`*)
       echo "Rejected"
       ;;
   rsync\ --server*)
       $SSH_ORIGINAL_COMMAND
       ;;
   *)
       echo "Rejected"
       ;;
   esac

If 'localhost' has a variable IP address, or shares its address (via NAT or something similar) with hosts you do not trust, omit the 'from="local_IP",' part of the line (including the comma). This way, 'rsync' will be possible from connections using this key.

Make sure that the 'validate-rsync' script is executable by 'remoteuser' on 'remotehost'.

Check you do not allow SSH root access from anywhere, this is always a good security practice. You need to have an entry like this in the SSH daemon configuration file to do that:

   PermitRootLogin no

4- Let us test if all of the stuff above work well. Run the following command on the local server:

   $ rsync -avz -e "ssh -i /home/localuser/.newscoop-rsync/localhost-rsync-key" /home/localuser/somefile remoteuser@remotehost:/home/remoteuser/remote_dir

If it does not work, probably it is because of the file permissions, so check them for each file mentioned before.

5- What to transfer.

Files to be synchronized are those contained into 'images', 'files' and 'look' Newscoop directories. They will be transfered over from Local server to Online server. We need to create a file on the Local server with the list of directories and files to be sent.

   $ vim /home/localuser/.newscoop-rsync/rsync-files-to-sync.txt

This is the content you need to put in:

   images
   files
   look

That is it, make sure each entry is on a new line. Save and close the file.

6- Cron Job setup.

Now we create the cron script 'rsync-newscoop-afiles' like this:

  #!/bin/sh

  RSYNC=/usr/bin/rsync
  SSH=/usr/bin/ssh
  KEY=/home/localuser/.newscoop-rsync/localhost-rsync-key
  RUSER=remoteuser
  RHOST=remotehost

  LNEWSCOOP_WWW_PATH=/path/to/local/newscoop/html/instance/dir
  RNEWSCOOP_WWW_PATH=/path/to/remote/newscoop/html/instance/dir
  FILESFROM=/home/localuser/.newscoop-rsync/rsync-files-to-sync.txt

  $RSYNC -razO -e "$SSH -i $KEY" --files-from=$FILESFROM "$LNEWSCOOP_WWW_PATH" "$RUSER@$RHOST:$RNEWSCOOP_WWW_PATH"

The cron script is easy to understand, all what you need to know about it is:

  RSYNC: The full path to the 'rsync' binary file
  SSH: The full path to the 'ssh' binary file
  KEY: The full path to the SSH private key
  RUSER: The name for the user on the Online server
  RHOST: The hostname for the Online server
  LNEWSCOOP_WWW_PATH: The full path to the Newscoop instance HTML directory on
                      the local server (the usual value is:
                      /usr/local/newscoop/www/newscoop/html)
  RNEWSCOOP_WWW_PATH: The full path to the Newscoop instance HTML directory on
                      the remote server (the usual value is:
                      /usr/local/newscoop/www/newscoop/html)
  FILESFROM: The full path to the file containing the files/directories
             name to be synced

Set executable permission on it:

   $ chmod 755 /home/localuser/.newscoop-rsync/rsync-newscoop-afiles

Finally, we create an entry for this new cron job:

   $ crontab -e

line to insert:

   * * * * * /home/localuser/.newscoop-rsync/rsync-newscoop-afiles

for a by minute synchronization.

Handling Article Comments

If you are setting up a Local and Online servers environment, you need to take care of article comments. Why? Well, static and dynamic pages must be generated by working on the Local server, that is why we need to replicate data over from the Local to the Online server to keep it up to date.

On the other hand, we have got users from all around the world visiting our Website and, hopefully, most of them would like to post comments on the published articles, and they just can do that.

Said that, we will need to be able to replicate those article-comments-related tables back to the Local server.

All the stuff above means that comments management will be enabled in our Local server only whether we are online and able to reach our Online server.

This is what you need to do in order to set up comments replication:

  • Create a database user on the Online Server to connect from the Local server.
   mysql> GRANT ALL PRIVILEGES ON newscoop.* TO 'dbuser'@'localserver' IDENTIFIED BY 'somepassword';

Now, on the Local server:

  • Login to the Newscoop admin interface and go to System Preferences.
  • Check Yes for Setting up a Newscoop Replication Server?
  • Fill the form fields for the Newscoop Online Server Database as follows:
  • Database Host: The IP address or hostname of your Online server.
  • Database User: (dbuser) This is your user for the MySQL server at your Online server.
  • Database Password: (somepassword) The valid password for the database user.
  • Database Port: The port to connect to the remote database server (default is 3306).

That is it, Newscoop will show whether you are able or not to manage article comments on the Local server.

Finally

Please, run tests enough to check everything works well, after that you will can go in productive =)

Resources

  • No labels