Database automation has always made life of a database administrator easier. Earlier this week, I got a requirement from a client asking me to transfer mysql database table entries (fields) from one database to another. He also asked that the transfer has to be done automatically when ever there is a new entry or an update in the first database.
Well, I am not a database expert to give a very quick solution. So, obviously, I took my time and created a work around. So, this is the work around I did to get the job done.
First things first. I used combination of php, shell scripting and mysql to get this automation working.
Initially, I created a mysql trigger to create a new entry in another table (temporary table) when ever there was a new entry in the original table. Trigger helps you create a sample reference entries that can help you to make automation.
Once the triggers are setup, I created a shell script with a set of php scripts to fetch the required field data from required tables. These scripts fetch the data from first mysql database in an associative array and transfer the data to second mysql database. That’s how the flow should go on.
Please note the complexity of the scripting can increase based on the requirement of your client. However, the flow should remain the same.
STEP1: Create a trigger.
STEP2: write scripts to fetch data using the data generated by the trigger. The trigger can also be used to fetch reference values to help the scripts gather required data into the associative array.
STEP3: Update other database.
Once the flow is setup, then use crontab to automate all this process. Automation is only possible with a proper combination of triggers and scripting. If you need my help to do something, contact me through this contact page.