Change the collation for all tables in a MySQL database

How to change the database collation is easy, but how to change all the table collation? You can use script as below

This example is change all table collation to utf8_general_ci

$db = mysql_connect('localhost','dbuser','password');
if(!$db) echo "Cannot connect to the database - incorrect details";
mysql_select_db('dbname'); $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!";

dbuser= database user
password= database user password
dbname= database name

How to add mysql user manually

It may too easy for the mysql expert out there, it just a simple sharing to the Mysql Amateur user.

If you have SSH, just type “mysql” then you will be in mysql command screen

(1) First create user first

CREATE USER ‘dbuser‘@’localhost’ IDENTIFIED BY ‘password‘;

(2) Assign it to the database

GRANT ALL ON db.* TO ‘dbuser‘@’localhost’;

db: your database name
dbuser: your database username
password: the password