Restore DB from AWS snapshot


Tue Jun 14 2022 03:49:01 GMT+0000 (UTC)

Saved by @phuongtmr #shell

1. Restore DB from a snapshot on AWS console.
2. Test connection and data need to restore
    mysql -h -u apply -pakGbvgzeEy applydb -A
3. Dump the ai_company_areas, ai_areas_users, ai_conversation_locs, ai_leads by company_id
    mkdir restore_1351 && cd restore_1351
    mysqldump --no-tablespaces --single-transaction --set-gtid-purged=OFF -h -u apply -pakGbvgzeEy applydb ai_company_areas --where="company_id=1351" > ai_company_areas.sql
    mysqldump --no-tablespaces --single-transaction --set-gtid-purged=OFF -h -u apply -pakGbvgzeEy applydb ai_areas_users --where="company_id=1351" > ai_areas_users.sql
    # select GROUP_CONCAT(id SEPARATOR ",") from ai_conversations where company_id=1351;
    mysqldump --no-tablespaces --single-transaction --set-gtid-purged=OFF -h -u apply -pakGbvgzeEy applydb ai_conversation_locs --where="conversation_id IN (2388,3024,3025,3090,3126,3139,3206,3207,3232,3283,3316,3576,3626,3949,4126,4653,5084,5166,10084,11524,11530,11536,11551,11554,19566,19624,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,43780,43785,52626,52627,52628,52629,52630,52631,58514,58515,58516,58520,70029,71744,76284,80728,80729,88485,88489,99004,99005,99846,99859,99861,99864,99870,126219,126221,139283,139284,196286,196288,196289,210399,210400,372433,372434)" > ai_conversation_locs.sql
    mysqldump --no-tablespaces --single-transaction --set-gtid-purged=OFF -h -u apply -pakGbvgzeEy applydb ai_leads --where="company_id=1351 AND job_loc_id>0" -f > ai_leads.sql
4. Rename to temp tables with the suffix is `_r` in the dumped files
    sed -i 's/ai_company_areas_restore/ai_company_areas_r/g' ai_company_areas.sql
    sed -i 's/ai_areas_users/ai_areas_users_r/g' ai_areas_users.sql
    sed -i 's/ai_conversation_locs/ai_conversation_locs_r/g' ai_conversation_locs.sql
    sed -i 's/ai_leads/ai_leads_r/g' ai_leads.sql
5. Load restore data to the temp tables
    mysql -h -u apply -pakGbvgzeEy applydb < ai_company_areas.sql
    mysql -h -u apply -pakGbvgzeEy applydb < ai_areas_users.sql
    mysql -h -u apply -pakGbvgzeEy applydb < ai_conversation_locs.sql
    mysql -h -u apply -pakGbvgzeEy applydb < ai_leads.sql
6. Insert restore data from temp tables to the main tables
    SET foreign_key_checks = 0;
    # verify the number of restore rows
    SELECT count(*) FROM ai_company_areas_r;
    SELECT count(*) FROM ai_areas_users_r;
    SELECT count(*) FROM ai_conversation_locs_r;
    SELECT count(*) FROM ai_leads_r;

    # Restore
    INSERT IGNORE INTO ai_company_areas SELECT * FROM ai_company_areas_r;
    INSERT IGNORE INTO ai_areas_users SELECT * FROM ai_areas_users_r;
    INSERT IGNORE INTO ai_conversation_locs SELECT * FROM ai_conversation_locs_r;
    UPDATE ai_leads t1 INNER JOIN ai_leads_r t2 ON = SET t1.job_loc_id = t2.job_loc_id WHERE t1.company_id = 1351;
    SET foreign_key_checks = 1;
7. Drop restore tables (!!!!!!!!!!!!!!!!Be careful!!!!!!!!!!!!!!!!)
    drop table ai_company_areas_r;
    drop table ai_areas_users_r;
    drop table ai_conversation_locs_r;
    drop table ai_leads_r;
8. Drop the DB instance from snapshot via AWS console.