如何在CodeIgniter中使用Utility Class备份含存储过程的MySQL数据库

Hey there! I totally get the frustration—CodeIgniter's built-in Utility Class works great for basic database backups, but it doesn't include stored procedures by default. Let me break down a couple of solid solutions to fix this:

Why the Default Backup Misses Stored Procedures #

Out of the box, CodeIgniter's Utility Class uses mysqldump (the standard MySQL backup tool) without the --routines flag. This flag is what tells mysqldump to include stored procedures and functions in the backup. So we just need to add that flag to the mix.

Solution 1: Extend the Utility Class (No Core File Edits) #

Modifying CodeIgniter's core files is never a good idea (updates will overwrite your changes). Instead, create a custom backup method in your controller or model that manually runs mysqldump with the right parameters:

public function backup_with_procedures()
{
    // Load your database config
    $db_config = $this->load->database('', true);
    $host = escapeshellarg($db_config->hostname);
    $user = escapeshellarg($db_config->username);
    $pass = escapeshellarg($db_config->password);
    $db_name = escapeshellarg($db_config->database);
    
    // Set up your backup file path and name
    $backup_dir = './database_backups/';
    if (!is_dir($backup_dir)) {
        mkdir($backup_dir, 0755, true);
    }
    $backup_file = $backup_dir . $db_config->database . '_' . date('Y-m-d_H-i-s') . '.sql';
    
    // Build the mysqldump command with --routines flag
    $command = "mysqldump -h {$host} -u {$user} -p{$pass} --routines {$db_name} > " . escapeshellarg($backup_file);
    
    // Execute the command
    exec($command, $output, $return_status);
    
    // Check if the backup succeeded
    if ($return_status === 0) {
        echo "Backup done! Your file is at: {$backup_file}";
        // You could also add code here to force-download the file
    } else {
        echo "Oops, backup failed. Check if mysqldump is available and your credentials are correct.";
    }
}

Key Notes for This Method: #

• Escaping: I used escapeshellarg() to sanitize credentials—this prevents issues if your password has special characters like spaces or symbols.
• Permissions: Make sure the database_backups directory has write permissions for your web server user.
• Windows Servers: If you're on Windows, use the full path to mysqldump.exe, e.g., C:\xampp\mysql\bin\mysqldump.exe.
• Exec Access: Ensure PHP's exec() function isn't disabled in your php.ini (check the disable_functions list).

Solution 2: Temporarily Modify the Core Utility Class (Not Recommended) #

If you absolutely need to use the built-in Utility Class directly, you can tweak the core driver file—but remember, this will get overwritten when you update CodeIgniter:

1. Navigate to system/database/drivers/mysqli/mysqli_utility.php (or mysql_utility.php if using the older MySQL driver).
2. Find the _backup() method, locate the line where the mysqldump command is constructed.
3. Add the --routines flag to the command string. For example:

   $command = "mysqldump --opt --routines -h '".$this->db->hostname."' ...";
   

Bonus: Backup Without Exec() #

If your server blocks exec(), you can manually fetch stored procedure definitions and append them to a standard Utility Class backup:

1. First, run a standard backup using $this->dbutil->backup() to get table data.
2. Query the INFORMATION_SCHEMA to retrieve stored procedures:

   $procedures = $this->db->query("
       SELECT CONCAT('DELIMITER //\n', ROUTINE_DEFINITION, '\n//\nDELIMITER ;') AS proc_def
       FROM INFORMATION_SCHEMA.ROUTINES
       WHERE ROUTINE_SCHEMA = '".$this->db->database."'
       AND ROUTINE_TYPE = 'PROCEDURE'
   ")->result();
   

3. Append each procedure definition to your backup file.

This method is more work, but it's a reliable fallback if you can't use mysqldump directly.

Hope one of these solutions gets your stored procedures backed up smoothly!

内容的提问来源于stack exchange，提问作者Koorma Ashok