Technical documentation

We are currently working on a new method of importing and exporting the database.
Starting from version 8.2.2, this procedure will be deprecated.

NetYCE database archive sets are normally restored using the front-end tool “DB archives”. However, when a database gets corrupted to the degree that a front-end login is no longer possible, a back-end manual procedure can help out.

This article describes that procedure. Some UNIX skills are required to complete it. Only an experienced system administrator should execute it. The procedure applies to both MySQL and MariaDB.

Only exec as the ‘yce' functional user, never as 'root'

The database archives are stored here:

cd /var/opt/yce/backup

Select archive to restore, eg YCEdb_yceone_20150503_2300.arch

The archive YCEdb_yceone_20150503_2300 will be used from this point onward. Adjust the sample commands to the archive name used.

gtar tvf YCEdb_yceone_20150503_2300.arch

Extract the databases from the archive

gtar xvf YCEdb_yceone_20150503_2300.arch

Find the latest crypt-key-id from the dbmanifest.xml

tail -10 YCEdb_yceone_20150503_2300/dbmanifest.xml

Look for the highest “crypt_keys” “id” value in the lines similar to:
<crypt_keys id=“00” key=“U2FsdGVkX1/a7SOlfNV0dFapnzQDFzxyPzayImoG0PzlusanYVEWwsF/BqPs9eed” name=“00” /> Normally the id is “00”

Locate the encryption key used for the databases On the system that archived use ck_license to find it

/opt/yce/system/ck_license -y

The demo/development NetYCE key is: 39BC-2124-E8AB-40D2-0B08

The customer license key 39BC-2124-E8AB-40D2-0B08 will be used from this point onward. Adjust the sample commands to the key used.

Chdir to mysql data

cd /var/opt/mysql

Stop mysql, prevent psmon to restart

touch /opt/yce/etc/ignore_mysql
sudo service mysql stop

Stop the skulker too

touch /opt/yce/etc/ignore_mysql
/opt/yce/system/init/yce_skulker stop

Remove the existing YCE and NMS databases (or rename)

rm -rf YCE
rm -rf NMS

It is not recommended, but possible, to restore the mysql database and the performance_schema database as well. Only when access to or, startup of, the mysql database fails after the YCE and NMS databases were restored should that be attempted.

Extract to new YCE directory, use the located key and archive name

openssl des3 -d -pass pass:39BC-2124-E8AB-40D2-0B08 < /var/opt/yce/backup/YCEdb_yceone_20150503_2300/YCE.des3 | tar xvzf -

And the NMS db too

openssl des3 -d -pass pass:39BC-2124-E8AB-40D2-0B08 < /var/opt/yce/backup/YCEdb_yceone_20150503_2300/NMS.des3 | tar xvzf -

Update the crypt.keys for the database column encryption. Edit this file

vi /opt/yce/etc/crypt.keys

And ensure the entry with the crypt-key-id and the same database encryption key is the highest available.

NOTE: Incorrectly configuring the crypt.keys prevents passwords and other sensitive data in the database to be read

00 39BC-2124-E8AB-40D2-0B08

Use the mysql_repair script to cleanup binlogs, error logs and check / repair database

NOTE: This will also clear any replication setup!

(ignore the “MySQL server PID file could not be found” error. Mysql was already stopped)

/opt/yce/system/mysql_repair.sh

If the replication setup is to be preserved, skip mysql_repair.sh and remove appropriate files manually, then start database and check the error log.

Check for unfixed errors and fix, check mysql is running

ps -ef | grep mysql

Allow control of mysql by psmon (repair script should have removed it, but check)

rm /opt/yce/etc/ignore_mysql

And allow the skulker to resume its database sync and crypt tasks

rm /opt/yce/etc/ignore_skulker

Cleanup extracted archive

rm -rf /var/opt/yce/backup/YCEdb_yceone_20150503_2300

Login should be possible using the existing accounts in the database. However, a super-account can be added. The ‘system’-level account ‘NetYCE’ using password ‘NetYCE’ will be added using:

/opt/yce/system/patches/vendor_support.pl add 

The following procedure show the steps required to restore one table, Kpn_vlans, from an existing daily backup archive on two replicating servers The procecure is entirely manual a requires the operator to be familiar with Unix AND the YCE database structure. Restoring a single or selected set of tables is NOT recommended since many tables in the database depend on linked columns. The operator MUST be aware of these dependencies an be able to verify and correct them.

The steps and commands show how to proceed using a pair of replicating Mysql / MariaDB servers in Master - Master configuration. On single server systems, the steps involving the replicating system can simply be skipped.

– Login as 'yce'. NEVER use root privileges – See what archives are present

cd /var/opt/yce/backup/

– Select the archive set to restore from. This example uses “YCEdb_lsrv1344_20150531_2300.arch”

gtar xvf YCEdb_lsrv1344_20150531_2300.arch

cd YCEdb_lsrv1344_20150531_2300/

cd YCE

– Retrieve the decoding key. The response must be substituted as the decryption key

ck_license -y

– Extract the database locally. Substitute your decryption key in the command

openssl des3 -d -pass pass:E14E-4CD7-168E-6EAE-0C16 < YCE.des3 | gtar xvzf -

– Select the files needed to restore and create a new tarball with them – Each table requires three files, include all three in the tarball – The example below selects the “Kpn_vlans” table. The tarball name is for your own reference.

gtar cvzf /var/tmp/kpnvlans.tgz Kpn_vlans.*

– Set the ignore flags to allow stop/ start of mysql and skulker without interferes from yce_psmon

touch /opt/yce/etc/ignore_mysql
 
touch /opt/yce/etc/ignore_skulker

– Copy tarball to second server. Pull to prevent owner issues – Choose user and servername as appropriate for your environment

scp yspeere@lsrv1344:/var/tmp/kpnvlans.tgz .

– Set the ignore flags to allow stop/ start of mysql and skulker without interferes from yce_psmon

touch /opt/yce/etc/ignore_mysql

touch /opt/yce/etc/ignore_skulker

cd /var/opt/mysql/YCE

– check tarball is in place

gtar tvzf /var/tmp/kpnvlans.tgz

– stop the skulker

pkill yce_skulker

– On replicating systems, perform this step within about 20 seconds of the secondary – Stop mysql, restore and restart all in one go

sudo /etc/init.d/mysql stop; gtar xvzf /var/tmp/kpnvlans.tgz; sudo /etc/init.d/mysql start

cd /var/opt/mysql/YCE

– Check tarball is in place

gtar tvzf /var/tmp/kpnvlans.tgz

– Stop the skulker - prevent db switchover on availability or replication errors.

pkill yce_skulker

– On replicating systems, perform this step within about 20 seconds of the secondary – Stop mysql, restore and restart all in one go

sudo /etc/init.d/mysql stop; gtar xvzf /var/tmp/kpnvlans.tgz; sudo /etc/init.d/mysql start

– Remove ignore flag on both servers

rm /opt/yce/etc/ignore_*

– Review replication status, skip any errors – Use the web-front-end tool under Admin - system. Check both systems

– Remove the unpacked database from the backup environment (primary system only)

cd /var/opt/yce/backup

rm -rf YCEdb_lsrv1344_20150531_2300