在没有CREATE权限的情况下将SQL转储导入Typo3安装

Question

I have a customer with a Typo3 website that is hosted on a webserver with very restricting policies when it comes to database users.

I want to create a development site for that site and in order for that, I need to duplicate the database of the original site. The problem is, the database user I can use through phpMyAdmin has no CREATE ability.

So plainly exporting an sqldump and importing it into the DB of the development site, doesn't work, it gives me a "#1044 access denied" error. Also when I simply try to use the phpMyAdmin built in "database copying" tool, I get that same error.

External access via ODBC is off course denied. The webhosting support hasn't been very helpful with my problem yet.

My question: Can you think of any way I can import an sqldump either into an empty database, or an exiting Typo3 database without using phpMyAdmin or having external access through ODBC? Maybe it's possible through some PHP script? The DB user Typo3 uses must be able to do DB alterations, but it only has local access, so I cannot use it and it's also different from the one I can use on phpMyAdmin.

Answer 1

I found a solution to my problem:

The DB user used by Typo3 itself does have permission to alter the DB, of course, since otherwise Typo3 wouldn't work properly. However, that user is restricted to local usage only, so it can only be used from the server.

The solution to my problem was to use that Typo3 DB user in a PHP script, that I uploaded to the server. The script imports the sql dump, that I put in the same folder as the script via FTP.

I didn't write the script myself but found it here. I only changed it slightly. Also I had the problem that my sqldump was very large, so that this script would break the PHP memory cap. I solved that simply by splitting the dump into several pieces with a tool called SQLDumpSplitter and importing them one by one.

A bit hacky, but it worked!

// Name of the file
$filename = 'sqldump.sql';
// MySQL host
$mysql_host = 'thehost';
// MySQL username
$mysql_username = 'dbusername';
// MySQL password
$mysql_password = 'dbpassword';
// Database name
$mysql_database = 'dbdatabase';

// Connect to MySQL server
$con = @new mysqli($mysql_host, $mysql_username, $mysql_password, $mysql_database);

// Check connection
if ($con->connect_errno) {
    echo "Failed to connect to MySQL: " . $con->connect_errno;
    echo "<br/>Error: " . $con->connect_error;
}

// Temporary variable, used to store current query
$templine = '';
// Read in entire file
$lines = file($filename);
// Loop through each line
foreach ($lines as $line) {
// Skip it if it's a comment
    if (substr($line, 0, 2) == '--' || $line == '')
        continue;

// Add this line to the current segment
    $templine .= $line;
// If it has a semicolon at the end, it's the end of the query
    if (substr(trim($line), -1, 1) == ';') {
        // Perform the query
        $con->query($templine) or print('Error performing query \'<strong>' . $templine . '\': ' . ' ' . '<br /><br />');
        // Reset temp variable to empty
        $templine = '';
    }
}
echo "Tables imported successfully";
$con->close($con);