如何使用PHP脚本通过.sql文件更新远程服务器的数据库？

I want to create a utility in PHP like phpMyAdmin's import option, which should allow database updates to the remote server via a .sql file without creating a new database.

Since it's a client side utility, access to cpanel is not allowed.

The app has two kinds of working environments, offline & online.

If the client works offline, they need to take the backup of database and should update the database with remote server similar for online.

Then they have to update the database of remote server.

Solution 1

If you are running your PHP on a Linux system, you can try using the 'mysql' command itself. However please note that your PHP installation has the permission to run "system" commands, like system(), exec() etc.

So here is what I mean to say:

system("mysql -U{db_user_name} -h{db_host} -P{db_password} < {full_path_to_your_sql_file}");

Please replace,

{db_user_name} with the DB username,

{db_host} with the DB host,

{db_password} with the DB password,

{full_path_to_your_sql_file} with the path to your SQL file.

And this of course requires the SQL file to be uploaded.

Solution 2:

Read the SQL file line by line and while reading execute each statement using PHP's standard MySQL library. Something like:

$arrFile = file("full_path_to_sql_file.sql", FILE_IGNORE_NEW_LINES);
foreach ($arrFile as $q) {
    mysql_query($q);
}

However, this might not be as simple as it seems. If your SQL file has comments and other .sql specific statements, you might need to put checks to ignore them. Or better if the SQL file contains nothing but SQL statements.

You can use a regular upload script to obtain the .sql file, make sure you sanitize appropriately the input string to obtain only the .sql file and text type,

move_uploaded_file($_FILES["file"]["tmp_name"],"tmpdb/" . $_FILES["file"]["name"]);

Once you have that, you can either preset their db settings defining the db using

mysql_select_db('dbname');

Then just open the sql file with fopen(); slap that sucker in a variable

$file = fopen("userdb.sql","r");
$usersql = fread($file, 5);
fclose($file);

then just throw it in a mysql_query();

$uploaddb = mysql_query($usersql) or die(mysql_error());

Those are the concepts I would suggest, alternatively you can use shell exec but then that just opens up other security concerns.

You may want to consider using BigDump?

Eventually,I've got answer for my question myself.I've just pasted the php coding without config and other stuff.

    $filename ="test.sql";        
    mysql_select_db("test"); 

    //truncate the database.

    $result_t = mysql_query("SHOW TABLES");
    while($row = mysql_fetch_assoc($result_t))
    {
       mysql_query("TRUNCATE " . $row['Tables_in_' . $mysql_database]);
    }
    // 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
            mysql_query($templine) or print('Error performing query \'<strong>' . $templine . '\': ' . mysql_error() . '<br /><br />');
            // Reset temp variable to empty
            $templine = '';
        }
    }