Piwigo.org

plg · 2013-04-09 10:10:37

Hi,

Yesterday I moved piwigo.org/piwigo.us to a new server.

* CPU before: Intel Core2 Quad Q8300@2.50GHz (benchmark 3066), after: Intel Xeon E3-1225 V2@3.20GHz (benchmark 6801)

* hard disks before: 750GB, after: 2x2TB RAID1

* memory before: 4GB, after: 16GB

+shorter delay on technical failures

First I moved all files with rsync. Then I moved all databases with a mysqldump + transfert + ssh call to import dump on remote server:

Code:

#!/bin/sh

root_dest_temp=/home/sys/tmp/databases
date_backup=$(date +"%Y%m%d_%H%M%S")
local_mysql_user=root
local_mysql_password=************************
remote_mysql_user=root
remote_mysql_password=***************************

for database in $(echo 'show databases;' | mysql --user=$local_mysql_user --password=$local_mysql_password | grep -v Database)
do
  dest_temp=$root_dest_temp"/"$database"/";
  filename=$dest_temp"/"$database"-"$date_backup".sql"

  if [ ! -e $dest_temp ]
  then
    mkdir -p $dest_temp
  fi

  echo "dumping "$database
  mysqldump --user=$local_mysql_user --password=$local_mysql_password --databases $database > $filename
  gzip $filename

  # send the dump
  rsync -av $root_dest_temp ns331371.ovh.net:/home/sys/tmp/

  # load the dump on the backup server
  ssh ns331371.ovh.net 'zcat /home/sys/tmp/databases/'$database'/'$database'-'$date_backup'.sql.gz | mysql --user='$remote_mysql_user' --password='$remote_mysql_password

done

Then I opened an SSH tunnel from the old server to the new server, mapping 127.0.0.1:3406 to port 3306 on the new server. I then modified forums connection settings (and many other services on the server) to 127.0.0.1:3406 so that everybody uses database on the new server.

Code:

autossh -T -L 3406:localhost:3306 root@ns331371.ovh.net

On the old server, I stopped SVN commits with a hooks/pre-commit (exit 1)

Eventually I changed the DNS zone to the new IP address and waited for everybody to go on the new server.

As far as I can see, piwigo.org didn't go down for a single second and no data was lost thanks to SSH tunnel. SSH tunnel rocks!

flop25 · 2013-04-09 14:31:08

Interesting! thx for sharing

romaint · 2013-04-21 02:26:09

Why did you use a SSH tunnel to connect to MySQL server ? Is it less secure to connect directly on port 3306 on new server ?

plg · 2013-04-21 09:09:55

romaint wrote:
Why did you use a SSH tunnel to connect to MySQL server ? Is it less secure to connect directly on port 3306 on new server ?

SSH tunnel is of course more secure than opening remote connections on MySQL. Moreover, opening remote connections requires a restart of MySQL, not the SSH tunnel.

Greybrow · 2013-05-01 22:40:49

Good job, and nice read :)

Piwigo.org

Announcement

#1 2013-04-09 10:10:37

New server, details

Code:

Code:

#2 2013-04-09 14:31:08

Re: New server, details

#3 2013-04-21 02:26:09

Re: New server, details

#4 2013-04-21 09:09:55

Re: New server, details

romaint wrote:

#5 2013-05-01 22:40:49

Re: New server, details

Board footer