MariaDB How to change MariaDB MySQL default character set & collation

After Centmin Mod install and before you create databases, read http://centminmod.com/mysql.html#charset.

If you use phpmyadmin, also read on my blog http://vbtechsupport.com/1375/ to be clear of the differences between phpmyadmin default session character set and collation versus your database defaults. They are 2 different things. It could phpmyadmin default session collation is set different from your MariaDB MySQL database defaults.

These changes can't be done automatically by default for Centmin Mod installs, as some folks import existing databases into the server and they may rely on a character set different or assume latin1 which would screw up their data's character set and collation.

Changing MariaDB 5.5 default character set and collation from latin1 to utf8 ?

Page 3 of my blog article outlines different ways to change MySQL 5.1 or 5.5 server's default character set and collation from latin 1 to utf8. The same applies to MariaDB 5.5 MySQL server which defaults to latin1 character set and latin1_swedish_ci collation.

You can see the defaults for MariaDB 5.5 MySQL server by typing the following command in ssh telnet (you'll be prompted to enter mysql root password if set, otherwise if mysql root doesn't have a password set, just hit enter):

Code:

mysqladmin -u root -p var | grep -E 'character|collation' | tr -s ' '

following output:

Code:

| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |

Note, character_set_system is UTF8 by default on latin1 default system and that is normal as MySQL system database and tables themselves at /var/lib/mysql/mysql/*are UTF8 by default. But every other database created defaults to latin1 if database and table creation queries don’t specify charset and collation values.

To change defaults respectively for character set and collation to utf8 and utf8_general_ci, you need to add under [mysqld] group in /etc/my.cnf the following line character-set-server=utf8:

---

Code:

[mysqld]
character-set-server=utf8

Then restart MariaDB 5.5 MySQL server via command:

Code:

service mysql restart

or via Centmin Mod command shortcut:

Code:

mysqlrestart

Then double check the defaults with the above command again:

Code:

mysqladmin -u root -p var | grep -E 'character|collation' | tr -s ' '

following output:

Code:

| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |

Note, only MySQL databases and tables created after this change will by default be created as utf8 character set and collations. Any databases before this change, will still be latin1.

 

Note, only MySQL databases and tables created after this change will by default be created as utf8 character set and collations. Any databases before this change, will still be latin1.

Changing existing database's character set data can be tricky as it can screw up existing database's existing non-latin1 character set based data if there is any if you're existing database came from a latin1 based MySQL server originally. If it did come from a latin1 based MySQL server original, leave your default at latin1 instead of UTF8.

If you must change your database from latin1 to UTF8, do it on a separate test VPS server first to make sure it works and always backup and download a copy of your database before changes are made.

You're on your own for changing existing database's character sets which involve 4 levels of changes from server level, database level, table level and column level as outlined in multi-page documentation at http://dev.mysql.com/doc/refman/5.5/en/charset-syntax.html. The above guide at https://community.centminmod.com/th...db-mysql-default-character-set-collation.398/ outlines just the first level for server level defaults for newly created MySQL databases.