Конвертация базы в UTF-8

1. Dump your database:

mysqldump --opt -p whmcs > whmcs.sql

2. Convert it to UTF8 with iconv. Make sure you’ll enter correct SOURCE charset. In my case it was Windows-1251

iconv -f CP1251 -t UTF-8  whmcs.sql > whmcs.utf8.sql

3. Import converted SQL back into the database:

mysql -p whmcs < whmcs.utf8.sql

4. Drop 1 index or you’ll not be able to convert table’s to UTF8 charset

alter table tblknowledgebase drop index `title`;

5. Create this PHP script and execute it on your server. Make sure you’ll use your database/user names and password.

// original script (v1.0) by/from: http://www.phpwact.org/php/i18n/utf-8/mysql
// improved/modified (v1.03) by Bogdan http://bogdan.org.ua/
// this script will output all queries needed to change all fields/tables to a different
// it is HIGHLY suggested you take a MySQL dump/backup prior to running any of the
//generated queries
// this code is provided AS IS and without any warranty
//die("Make a backup of your MySQL database, then remove this line from the code!");


// collation you want to change to:
$convert_to   = 'utf8_general_ci';

// character set of new collation:
$character_set= 'utf8';

// DB login information - *modify before use*
$username = 'USERNAME';
$password = 'PASSWORD';
$database = 'whmcs';
$host     = 'localhost';

//-- usually, there is nothing to modify below this line --//

// show TABLE alteration queries?
$show_alter_table = true;
// show FIELD alteration queries?
$show_alter_field = true;

mysql_connect($host, $username, $password);

$rs_tables = mysql_query(" SHOW TABLES ") or die(mysql_error());

print '<pre>';
while ($row_tables = mysql_fetch_row($rs_tables)) {
    $table = mysql_real_escape_string($row_tables[0]);

    // Alter table collation
    if ($show_alter_table)
        echo("ALTER TABLE `$table` DEFAULT CHARACTER SET $character_set;\r\n");

    $rs = mysql_query(" SHOW FULL FIELDS FROM `$table` ") or die(mysql_error());

    while ( $row = mysql_fetch_assoc($rs) ) {

        if ( $row['Collation'] == '' || $row['Collation'] == $convert_to )

        // Is the field allowed to be null?
        if ( $row['Null'] == 'YES' )
            $nullable = ' NULL ';
            $nullable = ' NOT NULL';

        // Does the field default to null, a string, or nothing?
        if ( $row['Default'] === NULL )
            $default = " DEFAULT NULL";
        elseif ( $row['Default'] != '' )
            $default = " DEFAULT '".mysql_real_escape_string($row['Default'])."'";
            $default = '';

        // Alter field collation:
        // ALTER TABLE `tab` CHANGE `fiel` `fiel` CHAR( 5 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
        if ($show_alter_field) {
            $field = mysql_real_escape_string($row['Field']);
            echo "ALTER TABLE `$table` CHANGE `$field` `$field` $row[Type] CHARACTER SET $character_set COLLATE $convert_to $nullable $default; \r\n";

6. Copy it’s output and save into .sql file.

7. Open it with your favorite text editor and change “NOT NULL DEFAULT NULL” to “NOT NULL” (script has a bug but I’m not that good to fix it)

8. Save the file and upload it to your server. Then run:

mysql -p whmcs < SCRIPTOUTPUT.sql

9. Add index back:

alter table tblknowledgebase add FULLTEXT KEY `title` (`title`,`article`);

That’s it. You’re now using UTF8 data and UTF8 charset for database/tables; Works for me. Took few hours to figure it out.

Взято с форума whmcs.com.