How to restore the database double encoded by mysqldump

I use the mysqldump to make a backup of my database. My database was destroyed by an accident and now I want to restore it. But the SQL file is double encoded by bug#28969. http://bugs.mysql.com/bug.php?id=28969 Is there any solution for my data to go back? I only have the SQL file made by mysqldump. Thanks.

I got my data back. Thanks everyone.

By this way,

1.import the messy data

2.use sqldump as 'mysqldump -h "$DB_HOST -u "$DB_USER" -p"$DB_PASSWORD" --opt --quote-names --skip-set-charset --default-character-set=latin1 "$DB_NAME" > /tmp/temp.sq开发者_StackOverflowl'

Reference

http://pastebin.com/iSwVPk1w

I got my data back. Thanks everyone.

By this way,

1.import the messy data

2.use sqldump as mysqldump -h "$DB_HOST -u "$DB_USER" -p"$DB_PASSWORD" --opt --quote-names --skip-set-charset --default-character-set=latin1 "$DB_NAME" > /tmp/temp.sql

Reference

#!/bin/bash -e

DB_HOST="$1"
DB_USER="$2"
DB_PASSWORD="$3"
DB_NAME="$4"


mysqldump -h "$DB_HOST -u "$DB_USER" -p"$DB_PASSWORD" --opt --quote-names \
    --skip-set-charset --default-character-set=latin1 "$DB_NAME" > /tmp/temp.sql

mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" \
    --default-character-set=utf8 "$DB_NAME" < /tmp/temp.sql

If it's just doubling the UTF-8 bytes or prepending something, I'd suggest putting together a quick sed/awk command to match and correct them.

http://www.osnews.com/story/21004/Awk_and_Sed_One-Liners_Explained

If you're not comfortable with this, any scripting language with regex support could be used to easily do the same thing, though it might take a few more minutes.

If Your DB contain correct Collation but full data in DB are Doubly Encoded then this will help you remember you only execute its once and take backup of your DB.

<?php
/**
 * DoublyEncodeCorrection.php
 *
 * NOTE: Look for 'TODO's for things you may need to configure.
 * PHP Version 5
 *
 */
ini_set('display_errors','1');
//error_reporting(E_ALL ^ E_NOTICE ^ E_DEPRECATED);
// TODO: Pretend-mode -- if set to true, no SQL queries will be executed.  Instead, they will only be echo'd
// to the console.
$pretend = true;

// TODO: Should SET and ENUM columns be processed?
$processEnums = false;

// TODO: The collation you want to convert the overall database to
$defaultCollation = 'utf8_general_ci';

// TODO Convert column collations and table defaults using this mapping
// latin1_swedish_ci is included since that's the MySQL default
$collationMap = array(
    'latin1_bin'        => 'utf8_bin',
    'latin1_general_ci' => 'utf8_general_ci',
    'latin1_swedish_ci' => 'utf8_general_ci'
);

$mapstring = '';
foreach ($collationMap as $s => $t) {
    $mapstring .= "'$s',";
}

$mapstring = substr($mapstring, 0, -1); // Strip trailing comma
//echo $mapstring;

// TODO: Database information
$dbHost = 'localhost';
$dbName = 'tina';
$dbUser = 'root';
$dbPass = 'root';

// Open a connection to the information_schema database
$infoDB = mysql_connect($dbHost, $dbUser, $dbPass);

mysql_select_db('information_schema', $infoDB);

// Open a second connection to the target (to be converted) database
$targetDB = mysql_connect($dbHost, $dbUser, $dbPass, true);
mysql_select_db($dbName, $targetDB);

if (!is_resource($targetDB)) {
    echo "Could not connect to db!: " . mysql_error();exit;
}

if (mysql_select_db($dbName, $targetDB) === FALSE) {
    echo "Could not select database!: " . mysql_error();exit;
}

//
// TODO: FULLTEXT Indexes
//
// You may need to drop FULLTEXT indexes before the conversion -- execute the drop here.
// eg.
//    sqlExec($targetDB, "ALTER TABLE MyTable DROP INDEX `my_index_name`", $pretend);
//
// If so, you should restore the FULLTEXT index after the conversion -- search for 'TODO'
// later in this script.
//

// Get all tables in the specified database
$tables = sqlObjs($infoDB,
    "SELECT TABLE_NAME, TABLE_COLLATION
     FROM   TABLES
     WHERE  TABLE_SCHEMA = '$dbName'");

foreach ($tables as $table) {
    $tableName      = $table->TABLE_NAME;
    $tableCollation = $table->TABLE_COLLATION;

    // Find all columns that aren't of the destination collation
    $cols = sqlObjs($infoDB,
        "SELECT *
         FROM   COLUMNS
         WHERE  TABLE_SCHEMA    = '$dbName'
            AND TABLE_Name      = '$tableName'
            ");

    $intermediateChanges = array();
    $finalChanges = array();

    foreach ($cols as $col) {

        // If this column doesn't use one of the collations we want to handle, skip it
        if (in_array($col->COLLATION_NAME, $collationMap)) {
            //echo "<pre>";print_r($col->COLUMN_NAME);exit;
           sqlExec($targetDB,"UPDATE $dbName.$tableName SET $col->COLUMN_NAME = CONVERT(CAST(CONVERT($col->COLUMN_NAME USING latin1) AS BINARY) USING utf8)") ;
        }
    }
}


/**
 * Executes the specified SQL
 *
 * @param object  $db      Target SQL connection
 * @param string  $sql     SQL to execute
 * @param boolean $pretend Pretend mode -- if set to true, don't execute query
 *
 * @return SQL result
 */
function sqlExec($db, $sql, $pretend = false)
{
    echo "$sql;\n";

    if ($pretend === false) {
        $res = mysql_query($sql, $db);
        //echo "<pre>";print_r($res);exit;
        $error = mysql_error($db);
        if ($error !== '') {
            print "!!! ERROR: $error\n";
        }

        return $res;
    }

    return false;
}

/**
 * Gets the SQL back as objects
 *
 * @param object $db  Target SQL connection
 * @param string $sql SQL to execute
 *
 * @return SQL objects
 */
function sqlObjs($db, $sql)
{
    $res = sqlExec($db, $sql);

    $a = array();

    if ($res !== false) {
        while ($obj = mysql_fetch_object($res)) {
            $a[] = $obj;
        }
    }

    return $a;
}

?>