Hi all,
I am trying to design a data replication process from my staging DB to my production DB, that idealy I can automate via cron. I have come up with an idea but would like to have it reviewed by the creative Yii pros here and to get some critics on how to improve it.
Staging DB:
-
SELECT … INTO OUTFILE (exporting desired tables; performance is not imperative since this is on staging)
-
FTP the file to a folder on the production instance
Production DB:
-
Cron job runs every … mins and checks if DB export file exists. If it does go to step 2.
-
Enter into Maintenance mode(suggestions how to automate this are very welcome
) -
Truncate table1,table2…etc (until all exported tables are empty and ready for import; performance is imperative)
-
LOAD DATA INFILE (performance is imperative since on production)
-
Switch maintenance mode off.
Concerns:
The operations on Production are mostly in replace mode (old data deleted and new imported due to the nature of the data), which implies that the production storefront may go down therefore I am thinking how to optimize the import combined with automatically switching into maintenance mode… importing the data as fast as possible and than switching maint mode off.
All suggestions and critics are welcome.
Cheers,
bettor
p.s. I heard a rumor that the staff is designing a replication process as part of Yii but not sure what it will include and when it will be released and I need to start off with something so I can replicate data until then.