I am working on a backup script that should grab all records belonging to user X on the system, for us to restore later on if there is a need for it. For this I am using mysqldump utility and "--where". Idea is to allow user to back up and restore their data when they want to.
I have quite a few pivot tables that I am having to backup with code similar to below:
# get list of projects id's
$projectsArr = $user->projects()->get()->lists('id')->toArray();
$whereClause = '--where="project_id IN (' . implode(",", $projectsArr) . ')"';
$table_name = 'project_task';
$this->createTableBackup($projectsArr, $table_name, $whereClause);
Above code works fine when the list of ID's is around 500, which might not be the case in my situation...
The only solution that I can see at the present moment is adding user_id to every pivot table:
$whereClause = '--where="user_id = ' . $user->id . '"';
$table_name = 'project_task';
$this->createTableBackup($projectsArr, $table_name, $whereClause);
Is there a better solution to my problem besides what I have above?
Below is a snippet of code that I use for the backup:
public function dump($destinationFile, $table_name, $whereClause, $options) {
$command = sprintf('mysqldump %s --user=%s --password=%s --host=%s --port=%s %s %s %s > %s', $options, escapeshellarg($this->user), escapeshellarg($this->password), escapeshellarg($this->host), escapeshellarg($this->port), escapeshellarg($this->database), escapeshellarg($table_name), escapeshellarg($whereClause), escapeshellarg($destinationFile)
);
return $this->run($command);
}