0

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 into filename.sql.gz and delete the data from the table. Keep latest 3 months data on the table.

If possible the output file only has the INSERT query.

Ryan Nghiem
  • 2,417
  • 2
  • 18
  • 28
LonelyToh
  • 15
  • 1
  • 8
  • 1
    what have you tried? a simple select, with a where clause for the date and any formatted output you like would seem to be the best approach –  Apr 22 '19 at 04:15
  • @tim I tried to query using `SELECT * FROM abc where date<'2019-01-01'` which selected the data that I wanted. I could not export the data out. I have tried using `SELECT * INTO OUTFILE {dir} FROM abc where {condition}` – LonelyToh Apr 22 '19 at 04:43

1 Answers1

0

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

  1. Connect database (proper access required), some PHP file management knowledge also necessary

  2. 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)

  3. On success of STEP 2, you can perform DELETE operation

  4. While doing this activity, make sure that no other process/queries are running on this database/table.

  5. Script must include TRANSACTION START/BEGIN, COMMIT and ROLLBACK

  6. 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.

  7. No need to touch the data that you want to keep, will remain as it is

Mangesh Sathe
  • 1,987
  • 4
  • 21
  • 40
  • Hi, currently I'm stuck at Step 2, and after seeking for answer on site. I tried to use `INTO OUTFILE`, the `secure_file_priv` is unable to solve. I even put = `` and `""` and still have the same problem. And what does Step 5 mean? – LonelyToh Apr 22 '19 at 06:27
  • First of all just write a simple SELECT query to pull the data you need from database – Mangesh Sathe Apr 22 '19 at 08:09
  • START TRANSACTION or BEGIN start a new transaction.,COMMIT commits the current transaction, making its changes permanent.,ROLLBACK rolls back the current transaction, canceling its changes.,SET autocommit disables or enables the default autocommit mode for the current session. https://dev.mysql.com/doc/refman/8.0/en/commit.html – Mangesh Sathe Apr 22 '19 at 08:11
  • if SELECT * FROM abc where date<'2019-01-01' this query is pulling data then you can just start with BATCH script generation with the help of PHP file manipulations – Mangesh Sathe Apr 22 '19 at 08:14