Editing Zeb Live DB Restore Task

Zeb Live db restore task to zeb uat instance followed by below steps
------------------------------------------------------------------- 


First we need connect AWS---> URL is

https://eu-west-1.console.aws.amazon.com/

Goto--> RDS--->Databases

1. RDS instance restore from last night automated backup file.

Goto Automated Backups---> Select zeb-db---> Under System Snapshots--> Select yesterday Snapshot(its created everyday 19.10(zeb time))---> Actions--->  Restore Snapshot---> New Instace creating( DB Instance class---> db.m4.large, DB Instance Identifier---> db-live-restore,  Public accessability ---> Yes, VPC security Groups---> Choose Existing---> MYSQL , MYSQL External, Default, Subnet-a,  DB parameter group---> zeb-mysql-new)


2. Connect new RDS instance from zeb uat instance using host name & user name & password details as below


hostname: zeb-db.-newc0fntifenc9y.eu-west-1.rds.amazonaws.com

user: zebdbmaster

pwd: busawove95


Goto mobXterm---> Connect to uat.zeb.apoyar---> run below command

mysql -hzeb-live-restore.c0fntifenc9y.eu-west-1.rds.amazonaws.com -u zebdbmaster -p

show databses;



3. check the live db name ( zeb) as i verified on live magento zeb sever

--->Connect to magento.zeb.apoyar----> run below command & One file opened

vim /var/www/html/migration/app/etc/env.php

in that file copy crendials 

'host' => 'zeb-db.c0fntifenc9y.eu-west-1.rds.amazonaws.com',

                'dbname' => 'zeb',

                'username' => 'zeb',

                'password' => 'PPdD%t5-$qT4aD',



4. export the database file into .sql file like zeb_uat_jan21.sql

---> Again go to uat.zeb.apoyar

export the database by using below command

mysqldump -hzeb-live-restore.c0fntifenc9y.eu-west-1.rds.amazonaws.com -u zebdbmaster -p > zeb_uat_jan21


if we need check export is running or not ---> Open new terminal of uat.zeb.apoyar---> ls-ltrah



5. Once export finished stop Rds instance ( ze-live-restore or delete it completely)

Right Click on Instance---> Stop the Service and delete it.


6. Compress sql file into zp file

using this command ---->   sudo tar cvzf zeb_uat_jan21.tar.gz zeb_uat_jan21.sql


7. remove tags and stored procedures from sql dump file using this command

sudo perl -pe 's/\sDEFINER=[^]+@[^]+//' < zeb_uat_jan21.sql > zeb_uat_jan21.fixed.sql
                              and
sudo perl -pi -e 's#\/\*\!5001[7|3].*?`[^\*]*\*\/##g' zeb_uat_jan21.fixed.sql


8.  Connect Zeb uat RDS instance by using command below 

---> mysql -hzeb-db-new.c0fntifenc9y.eu-west-1.rds.amazonaws.com -u zebdbmaster -p
  
Mysql> create database zeb_uat_jan21;

Mysql> show databases;


09. Database import command:

mysql -hzeb-db-new.c0fntifenc9y.eu-west-1.rds.amazonaws.com -u zebdbmaster -p  zeb_uat_jan21 < zeb_uat_jan21.fixed.sql


10.  You can open new terminal and check show tables command how many tables importing


Note: Better to run import command on any of remote desktop server i used devops.apoyar and connected uat.zeb.apoyar server through putty and started importing to avoid disconnection internet it will stop command once import is finished without any error.




Errors:

7th step runnig import command it will occur below error to fix this run ( sudo perl -pi -e 's#\/\*\!5001[7|3].*?`[^\*]*\*\/##g' zeb_uat_jan21.fixed.sql)
ERROR 1064 (42000) at line 1658: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@`172.31.0.80`*/ /*!50003 TRIGGER update_visit_stat AFTER INSERT ON amasty_faq_v' at line 1