Setting Default CHARSET and DB Engine in MySQL

character-setinnodbmariadbmyisamMySQL

I recently updated my server's database system from MySQL to MariaDB.
After update, everything works well.

But, when I went to phpMyAdmin, the default charset and types are changed:

MySQL chareset and type info

At the end of this image, the default type was set to 'InnoDB' and charset was set to 'latin1_swedish_ci'.
They were 'MyISAM' and 'utf8_general_ci' before.
How can I revert my character set and type settings back, and how can I customize settings when I make new database?

When I looked at /etc/my.cnf and /etc/my.cnf.rpmnew, I couldn't find any settings related to DB type/charset.

Best Answer

"How can I revert my character set" :

ALTER TABLE myTable CONVERT TO CHARACTER SET "utf8";

"How can I revert my type": (MySQL Engine):

ALTER TABLE myTbale ENGINE=MyISAM;

"how can I customize settings when I make new database?"

In your my.cnf

default-storage-engine=MyISAM

Best Regards.

Max.

Related Solutions

Mysql – How to configure global charset on MySQL

You need to incorporate the following into your my.cnf file

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8


[mysqld]
default-character-set = utf8    
collation-server = utf8_general_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

You can find more information on the following manual pages

In response to Mark's answer, if you need to convert existing tables/databases to the correct character set you will need a suitable ALTER query. Something similar to the below:

ALTER TABLE `table` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Alternatively there's a great PHP script here which does it for you.

MySQL – Replication with Raw Data and InnoDB Tables

When you get 'Unknown table engine INNODB', it means something when wrong with startup. If you look in your error logs, you should be able to get more information.

My first instinct is that your ib_logfile is a different size on your master than what is configured in the slave. If you stop the slave server, delete the ib_logfile from the snapshot when setting up the slave, then restart mysql, it should correct itself.

NOTE: Do not delete the ibdata file! Only the ib_logfiles

Best Answer

Related Solutions

Mysql – How to configure global charset on MySQL

MySQL – Replication with Raw Data and InnoDB Tables

Related Question