-1

I need to import all the old data for backup but i have too many data that it cant execute all query like 2k of query in my .sql file. Some are executed but not 100% of all the query are executed. There are no errors showed after. I tried to loop it four times maybe it will execute it all because when i tried with no loop it only creates 4 tables but in the backup I have 9 tables so i clicked the button again and it turned out to be 8 tables on the database and on my 3rd try clicking it again it has now 9 tables but the data is incomplete.

Here is a test i made for backup and recovery

here is my code:

<?php 
ini_set('max_execution_time', 300);
for($c = 0; $c < 4;$c++){
    $connection = mysqli_connect('localhost','root','','test');

    $filename = 'backup.sql';
    $handle = fopen($filename, "r+");
    $contents = fread($handle, filesize($filename));

    $sql = explode(';', $contents);
    foreach ($sql as $query) {
        $result = mysqli_query($connection,$query);
    }
    fclose($handle);
}
echo "Successfully Imported";
?>
Karlo
  • 9
  • 1
  • What is actually happening? Do you get an error message on the screen? Have you checked your logs? How big is the actual backup file? Please read about [ask] questions here. Also note: trying to execute all of this code four times in a loop is not going to help at all; that's just a shot in the dark. It's a bad idea to add a loop simply because you don't know what's wrong with your code in hopes that it magically starts working. – elixenide Jan 12 '19 at 20:37
  • Possible duplicate of [how to import .sql file in mysql database using php](https://stackoverflow.com/questions/19751354/how-to-import-sql-file-in-mysql-database-using-php) – Dharman Jan 12 '19 at 20:39
  • 1
    This is generally a bad idea. Use phpMyAdmin or a CLI mysql client. – Dharman Jan 12 '19 at 20:40
  • Since this is a one-time task (you hope!) I'd manually break the file up into manageable parts, write a basic import script, and upload multiple files at one time – ivanivan Jan 12 '19 at 21:58

1 Answers1

0

The most efficient way to do a database/table backup and restore from backup is to use mysql command line tools, e.g.:

Create a database backup:

mysqldump -u db_user -p dbname > dbname.sql

Restore database from backup:

mysql -u db_user -p dbname < dbname.sql

The same can be done with a specific table:

mysqldump -u db_user -p dbname tablename > tablename.sql
mysql -u db_user -p dbname < tablename.sql

If you really need to do import in PHP then try to add the following line at the beginning of the php script. Probably your php script is interrupted because of lack of output for a long time:

ignore_user_abort(true);

You may also disable execution time limit to run script as long as it needs:

ini_set('max_execution_time', 0);
Yurii Tvardyi
  • 24
  • 4