How to get current snapshot of MySQL Database Table and FTP it to remote server using PHP.

Question

Here is the scenario:

1. I get an CSV file, which has data1, data2, data3, data4(Add, Update, Delete), data5 and there are like 90000 entries in the CSV file, first line gives description of column like

   DATE AGE NAME STATUS(ADD, UPDATE, DELETE) TIME

   1 01/02 23 Phil A 23:00:00
   2 02/03 32 Erick D 12:00:00 3 09/07 36 Timmy U 11:00:00

2. I need to parse through this CSV file and store data into the database and depending upon STATUS, I need to add, update or delete information from the database. I am doing so using PDO and prepared statements and bind parameters.

Few Questions, Q2 I have asked which would add meat to this summary and would be useful.

1. After doing desired operations on database like ADD, DELETE and UPDATE depending upon the STATUS table, I need to get current snapshot of the MySQL Table(or database but am more concerned about Table right now) and send it to an FTP Server from PHP.

My Issues:

1. I am trying to update content of the database depending on STATUS but somehow it is not been updated, this issue has been discussed in Q2, listed above.

2. Also I want to get current snapshot or state of MySQL Table which I need to export to an FTP Server and I am not sure of what approach do I need to take to achieve my goal.

Hope question is clear here and would get some useful information.

Thanks.

Update: I have tried to look for other alternatives regarding the approach to get snapshot of Current State of Database, in my case MySQL, but have not found any which can be used. Google is giving me wierd answers and getting me into wrong direction, Is it really that hard to get snapshot of current database state and send it to FTP Server using PHP ?

Answer 1

You can create a snapshot of a MySQL DB or table/group of tables using the mysqldump command.

That will give you a text file. You will want to compress the file if your DB is reasonably large. On Linux I use the gzip command.

You can then FTP that file to the remote server. Depending on your OS and PHP configuration, you might be able to execute scripted OS command to perform the FTP. What OS is this running on? Does your PHP config allow you to execute OS commands?

EDIT:

Since you can't run OS level commands, consider doing a SELECT * from the table in PHP, writing the results to a file, then using the built-in PHP FTP commands to transfer that file to your remote server.