===== Manual database restore procedure =====

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


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'


==== Select the archive to restore ====

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


==== Get the encryption-keys ====

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.


==== Restore the databases ====

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 -


==== Restore crypt-keys file ====

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


==== Repair the database where needed ====

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


==== Resuming operation ====

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


==== If login fails ====

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 


===== Manual restore of specific table(s) on replicating systems =====
 
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.
 
 
==== 1) Prepare primary server ====
 
-- 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"

<code>
gtar xvf YCEdb_lsrv1344_20150531_2300.arch

cd YCEdb_lsrv1344_20150531_2300/

cd YCE
</code>

-- 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
<code>
touch /opt/yce/etc/ignore_mysql
 
touch /opt/yce/etc/ignore_skulker
</code>

==== 2) Prepare replicating server ====
 
-- 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
<code>
touch /opt/yce/etc/ignore_mysql

touch /opt/yce/etc/ignore_skulker
</code> 
 
 
==== 3) Restore tables on primary server ====

  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


==== 4) Restore on second server ====

  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

 
==== 5) Cleanup ====
 
-- 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)
<code>
cd /var/opt/yce/backup

rm -rf YCEdb_lsrv1344_20150531_2300
</code>