Running MySQL *.sql files in PHP
Automating database initialization from within a web application often presents a specific set of constraints, particularly when console access is unavailable. In many production environments, you need to trigger schema creation and seed data directly from the application code rather than relying on manual command-line execution via SSH or FTP. This scenario frequently arises when building automated site generation pipelines where a fresh database instance must be provisioned before the web server starts serving traffic.
The core challenge involves bridging the gap between PHP's execution context and the MySQL client utility. While it is tempting to rely solely on the PDO or MySQLi extensions found within your application framework, these libraries are designed for interactive queries, not for piping entire .sql files into a session. Attempting to load a file containing CREATE TABLE statements or bulk inserts directly through a prepared statement often fails due to how SQL parsers handle multi-statement execution or unexpected whitespace in dump files.
To address this without console access, the most direct approach is to invoke the MySQL command-line client (mysql) from PHP using the shell_exec() function. This method treats the client as an external binary, allowing it to parse .sql files exactly as a user would via the terminal. The critical distinction here is that we are not asking the database server to read a file; we are asking the operating system's shell to execute a command that reads a file and pipes its contents into the MySQL server process.
A robust implementation requires constructing the command string carefully. You must use the --execute flag to issue the SOURCE command, which tells the client utility to read the specified script path. Furthermore, using the --option=value format (e.g., --host=127.0.0.1) is generally safer and more reliable than positional arguments or older syntax like -h. This ensures that environment variables do not inadvertently override connection parameters and reduces the likelihood of parsing errors in complex command strings.
Here is a pattern for executing two distinct scripts: one for table structure and another for default data population:
$command = 'mysql'
. ' --host=' . $vals['db_host']
. ' --user=' . $vals['db_user']
. ' --password=' . $vals['db_pass']
. ' --database=' . $vals['db_name']
. ' --execute="SOURCE ' . $script_path . '/site_structure.sql"';
$output1 = shell_exec($command);
$command2 = 'mysql'
. ' --host=' . $vals['db_host']
. ' --user=' . $vals['db_user']
. ' --password=' . $vals['db_pass']
. ' --database=' . $vals['db_name']
. ' --execute="SOURCE ' . $script_path . '/site_db.sql"';
$output2 = shell_exec($command2);
One common pitfall when using shell_exec() is handling the output. By default, shell_exec() returns the complete output of the command as a single string. If your .sql files produce verbose logging or warnings, this string can become massive, potentially causing memory exhaustion on larger datasets. In such cases, switching to exec() is often preferable, as it allows you to capture only specific lines or handle the return code more granularly without loading the entire stdout buffer into memory at once.
Another significant consideration is security. While shell_exec() offers a convenient way to bypass framework limitations, it opens the door to command injection if the connection variables ($vals) are not strictly sanitized. Never concatenate user input directly into the command string; always whitelist allowed hosts and users before constructing the execution path. If you are working within the Zend Framework or similar architectures, ensure that these execution blocks are isolated within a secure service layer that validates all database credentials before passing them to the shell command.
Ultimately, this technique provides a reliable bridge between your application logic and infrastructure provisioning tasks. It allows you to maintain a consistent deployment process where the same .sql files used for manual setup can be seamlessly integrated into an automated workflow without requiring external server access.