I would like to back up one of my database tables, abc using PHP.
One of the column is
timestamp. I would like to back up the table that is 3 months old intofilename.sql.gzand delete the data from the table. Keep latest 3 months data on the table.
If possible the output file only has the INSERT query.
分享 You first need to go through the PHP+database integration tutorials. You can achieve above in mysql query itself. You can use PHP as well...you have to write your own code for this simple task. following are the steps that I will follow in completing this tasks, you can modify the steps according to your requirements...
you have not mentioned which database you are using, am assuming its Mysql/mariadb
Connect database (proper access required), some PHP file management knowledge also necessary
Based on timestamp, you should write a query which can pull the old data and write it to a .sql file (plenty of questions have already been asked on this topic in stackoverflow)
On success of STEP 2, you can perform DELETE operation
While doing this activity, make sure that no other process/queries are running on this database/table.
Script must include TRANSACTION START/BEGIN, COMMIT and ROLLBACK
If you want output file in INSERT query then BATCH INSERT is required. Refer this to get an idea how batch query looks like (How to do a batch insert in MySQL) You can write a custom PHP function to generate BATCH implementation.
No need to touch the data that you want to keep, will remain as it is
分享
