Differences

This shows you the differences between two versions of the page.

--- guides:reference:database:manual_database_restore [2019/12/23 16:16] – ↷ Page moved from guides:user:manual_database_restore to guides:reference:database:manual_database_restore yspeerte
+++ guides:reference:database:manual_database_restore [2024/07/03 12:31] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
+===== 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
+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>