I already know how to use transactions, what I want to know is how MySQL transaction handles data being processed...
For example, consider the following:
1. I have 2 databases (db1 and db2) and both databases are geographically far from each other, more like cities away.
2. db1 has a table namely, tbl_orig
3. db2 has a table namely, tbl_backup
4. I want to use MySQL transaction when copying the table from db1 (tbl_orig) into db2 (tbl_backup) so that in case of a power outage or anything that would interrupt the process, it would prevent my database to be in an unstable state.
Say I use the following commands below:
mysql> start transaction;
mysql> insert into db2.tbl_backup select * from db1.tbl_orig;
In the above sql statements, in between the "start transaction" and the "commit" lines... what exaclty is happening to the rows indicated in the insert statement?
Does all of the rows affected by the insert statement from db1 gets transfered into db2 immediately and are waiting for the commit line to execute in order to make the insert official?
Or, are the rows affected by the insert statement are still inside db1 and are just waiting to be transfered into db2 once the "commit" command executes?
I wanted to know this facts because I will be copying data from a database through the internet and I'm worried that I might lose some data in the process... Any help would be greaty appreciated.