Synology usage series 28 – Mirroring wordpress from webhost to synology box – the automatic way


Part 3 – Synchronization – database

For now we have a plugin to generate database backup daily, and have a script to download the database backup to our NAS everyday.

What we need is another script to extract the database script and import it to our local mysql database.

Create the script

# vi /opt/usr/local/bin/sync-db.pl

#!/usr/bin/perl
#
# Version 0.12
#       2011-05-18      Fix $ez_prefix bug
#
# Version 0.11
#       2011-05-18      Add $ez_prefix
#
# Version 0.1
#       2011-05-18

use File::Copy;

#
# Edit the variables below
#
$db_dump_path='/volume1/web/public_html/wp-content';
$tmp_dir='/opt/tmp';
$ez_dir='EZPZ_RESTORATION_FILES';
$ez_sql='EZPZ_DB.SQL';
$ez_prefix='yourhostname_';
$mysql_bin='/usr/syno/mysql/bin/mysql';
$sql_user="webhost_db_user";
$sql_pwd="password";
$sql_db="webhost_wordpress_db";
$patch_sql="/opt/usr/local/bin/sync_patch.sql";

# END EDITING, DO NOT EDIT BELOW!!
########
# variables checking rountine

print "Checking variables correctness... ";
sleep(2);
$ver_result=checkEnv($tmp_dir,$db_dump_path,$mysql_bin,$patch_sql);
if( $ver_result eq false){
        print "\nPlease double verifiy values of all variables.\n";
        exit;
}

print "Passed.\n";


#######
#Preparation
#######
$timestamp=generateTimestampString();
$db_dump=generateDBDumpFilename($ez_prefix);

#temp hardcode for dev
#$db_dump="_2011-05-17.zip";

#print "$timestamp\n";
#print "$db_dump\n";

$dump_found=false;
print "Looking for db dump $db_dump... ";
sleep(2);
if( -f "$db_dump_path/$db_dump"){
        print "Found.\n";
        $dump_found=true;
}else{
        print "Not Found.\n";
}

if($dump_found eq true){
        #create temporary directory
        print "Creating temp directory holding working file... ";
        sleep(2);

        mkdir "$tmp_dir/$timestamp", 0700 unless -d "$tmp_dir/$timestamp";
        print "Done.\n";
        print "Copying db dump to temp directory... ";
        sleep(2);
        copy("$db_dump_path/$db_dump","$tmp_dir/$timestamp/$db_dump");
        print "Done.\n";
        print "Unzipping db dump file...\n";
        sleep(2);
        system("unzip -d $tmp_dir/$timestamp $tmp_dir/$timestamp/$db_dump");
        sleep(2);

        $zip_flag=false;

        if(-f "$tmp_dir/$timestamp/$ez_dir/$ez_sql"){
                $db_script_size=-s "$tmp_dir/$timestamp/$ez_dir/$ez_sql";
                if($db_script_size > 0){
                        $zip_flag=true;
                        print "Done.\n";
                }
        }else{
                print "Failed.\n";
        }
        ## ready to imporot sql script
        if($zip_flag eq true){
                print "Importing data to $sql_db...\n";
                sleep(2);
                system("$mysql_bin --host=localhost --user=$sql_user --password=$sql_pwd $sql_db < $tmp_dir/$timestamp/$ez_dir/$ez_sql");
                sleep(2);

                print "Patching data... \n";
                sleep(2);
                system("$mysql_bin --host=localhost --user=$sql_user --password=$sql_pwd $sql_db < $patch_sql");
                sleep(2);

                print "Database imported and patched, ready for human verification.\n";

                print "Cleaning up temporary directories... ";
                sleep(2);
                #cleanup("$tmp_dir/$timestamp");

                if(-d "$tmp_dir/$timestamp"){
                        print "Failed.\n";
                }else{
                        print "Done.\n";
                }
        }
}

print "Terminated.\n";
sub cleanup{
        my ($tmpdir) = @_;
        system("rm -r -f $tmpdir");
}
sub checkEnv{
        my($tmpdir,$dbdir,$mysqlbin,$patch) = @_;
        my $result=true;
        if(! -d $tmpdir){
                print "\nTemporary directory not existed.";
                $result=false;
        }
        if(! -d $dbdir){
                print "\nDB Dump folder not existed.";
                $result=false;
        }
        if(! -f $mysqlbin){
                print "\nMySQL executable not found.";
                $result=false;
        }
        if(! -f $patch){
                print "\nData patch sql script not found.";
                $result=false;
        }
        return $result;

}

sub generateDBDumpFilename{
        my($prefix)=@_;
        my($sec,$min,$hr,$day,$mon,$year)=localtime;
        $year=$year+1900;
        $mon++;
        $mon=appendLeadZero($mon);
        $day=appendLeadZero($day);
        return "$prefix$year-$mon-$day.zip";
}

sub generateTimestampString{
        my($sec,$min,$hr,$day,$mon,$year)=localtime;

        $year=$year+1900;
        $mon++;

        $mon=appendLeadZero($mon);
        $day=appendLeadZero($day);
        $hr=appendLeadZero($hr);
        $min=appendLeadZero($min);
        $sec=appendLeadZero($sec);

        return "$year$mon$day$hr$min$sec";
}

sub appendLeadZero{
        my ($param) = @_;
        if($param < 10){
                $param = '0'.$param;
        }
        return $param;
}

$db_dump_path defines where the script to looks for the daily backup file

$tmp_dir defines a temporary directory, must be writable by root, a temporary working directory will be generated here

$ez_dir defines the directory storing the backup file after unzip, leave it as default value unless the plugin changed the setting

$ez_sql defines the actual database backup sql generated by the plugin. Leave it as default value unless the plugin changed the setting

$ez_prefix defines the prefix of the backup filename generated by the plugin.

$mysql_bin defines the mysql command

$sql_user defines the local database user

$sql_pwd defines the password of the database user

$sql_db defines the local database

$patch_sql defines the data patching script describe below

What the script does?

- First lookup the backup file
- Create a temporary directory
- Copy the backup file to temporary directory
- Unzip the backup file if existed
- Import the database script to local database
- Patching data
- Remove the temporary directory

Setup cron job

Again, setup another cron job to execute the database import script. Must run this script AFTER the rsync script, otherwise it never able to lookup the backup file because the backup file is not downloaded to the NAS yet!

# vi /etc/crontab

Example below execute the script every 1pm.


0 13 * * * root /usr/bin/perl /opt/usr/local/bin/sync-db.pl | /opt/bin/nail -s "Sync database done." email@address.com

Data Patching

The data imported contains setting for webhost account. We need to patch the local database because the domain name is changed from www.mydomain.com to backup.mynasdomain.com.

The script above will execute a data patching script after import the database. The patching script is defined by the $patch_sql variables.

Create the data patching script

# vi /opt/usr/local/bin/sync_patch.sql

Define data patching sql

You can define any patching sql to the file, below is my setup to patch the wp_options and wp_posts table.

lock tables wp_options write;
update wp_options set option_value = replace(option_value, 'http://www.mydomain.com','http://backup.mynasdomain.com') where option_value like '%http://www.mydomain.com%' and option_name!='custom_login_settings' and option_name not like '%wp_table_reloaded_data%';
update wp_options set option_value = replace(option_value, '/home/id/public_html','/volume1/web/public_html') where option_value like '%/home/id/public_html%';
unlock tables;


#fixing wp_posts
lock tables wp_posts write;
update wp_posts set post_content = replace(post_content, 'http://www.mydomain.com','http://backup.mynasdomain.com') where post_content like '%http://www.mydomain.com%';
unlock tables;

My script above will patch all wordpress option except the 'Custom Login' and 'WP Table Reloaded' Plugins.

Feel free to modify it for your setup.

Now my webhost wordpress site is mirrored to my NAS and update itself everyday 🙂





Leave a Reply

Your email address will not be published. Required fields are marked *