0

I am going to move all data from the live stage database into another new database. The project is based on Codeignitor. For now I am using dump file but it's really slow to handle them.

$temp_path = './db/dump/old_backup.sql';

// Create temporary table
echo "Preparing temporary database ..... \n";
$this->createTemporarDB();

// Make Dump from live db
echo "Preparing backup from live database ..... \n";
$backup = $this->exportDumpFromDB('default', 'old_backup.sql');
write_file($temp_path, $backup, 'w+');

$this->new_db = $this->load->database('new_db', true);
$temp_line = '';
$lines = file('./db/dump/old_backup.sql');

foreach ($lines as $line) {
    if (substr($line, 0, 2) == '--' || $line == '' || substr($line, 0, 1) == '#') {
        continue;
    }
    $temp_line .= $line;
    // Display percentage of temp data
    if (substr(trim($line), -1, 1) == ';') {
        $this->temp_db->query($temp_line);
        $temp_line = '';
    }
    $index++;
}

Is there any best method to solve my issues( latency issue, I need to speed up ). Thank you.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
  • Have you tried creating a dump file with `mysqldump` directly on the database server or using your database management software to create the dump from there? – Javier Larroulet Sep 15 '20 at 11:21
  • I am building one cli to dump file, so need to implement it via code using php. is it possible? –  Sep 15 '20 at 11:48
  • 1
    Yes, it's very possible. You can use php to call `mysqldump` rather than build the dump yourself (which would take longer). Check [this answer](https://stackoverflow.com/questions/6750531/using-a-php-file-to-generate-a-mysql-dump) for details (it's an old answer, from old PHP5 days, but the basics are still the same) – Javier Larroulet Sep 15 '20 at 14:13
  • Does this answer your question? [Using a .php file to generate a MySQL dump](https://stackoverflow.com/questions/6750531/using-a-php-file-to-generate-a-mysql-dump) – Javier Larroulet Sep 15 '20 at 14:13
  • Thanks for your help, I can fix this issue but your approach was nice for me to help to jump another step. –  Sep 15 '20 at 15:08

1 Answers1

0

I fixed this issue. Attached my approach here.

$tables = $this->db->query("SHOW TABLES FROM [old db name]")->result_array();
$this->temp_db = $this->load->database[new db name]', true);
foreach ($tables as $key => $val) {
   $this->temp_db->query('CREATE TABLE ' . $val['Tables_in_squirrel'] . ' LIKE [old db name].' . $val['Tables_in_squirrel']);
   $this->temp_db->query('INSERT ' . $val['Tables_in_[old db name]'] . ' SELECT * FROM squirrel.' . $val['Tables_in_squirrel']);
  }
}

This is the code based on Codeigniter.