Change MySQL Database Collation

[10NANCY]

How can I change my MySQL database collation?

[vps-RecS]

Hello,

Changing MySQL database collation is easy in cpanel servers, you need to access phpMyAdmin.

Then need to select the database you wish to alter.
In phpmyadmin you will find a Operations tab in top of phpMyAdmin.
At the bottom of the page you will see the collation option.
You can now select a collation from the drop down menu and click on the Go button.

After changing the collation of a database only the new tables will be created with the new collation. The old tables in database remain on old collation.

Note : Please make a backup of database before alter the database collation.

[PATRICIAF5]

How to optimize a MySQL database using phpMyAdmin?

[vps-RecS]

Hello,

phpMyAdmin itself provides inbuilt function to optimize the database.
To optimize a database through phpMyAdmin.
Login to you phpMyAdmin and select the database.
Simply click [Check All] to select all tables and From the [With selected:] drop-down menu choose Optimize table. This will Optimize database tables.

[samson]

If you have ssh access to the server you can use the following script to change the collation of the database.

<?php
$db = mysql_connect('localhost','database_user','passwor d');
if(!$db) echo "Unable to connect to the database - incorrect details";
mysql_select_db('database_name'); $result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
foreach ($tables as $key => $value) {
mysql_query("ALTER TABLE $value COLLATE utf8_general_ci");
}}
echo "The collation of your database has been successfully changed!";
?>

The above script will change the collation to 'utf8_general_ci'.

Also if you have ssh access to the server , you can optimize and repair the database using the following commands respectively.

mysqlcheck -o database_name
mysqlcheck -r database_name