CentOS MySQL create database - encoding settings

Keywords: Web Server MySQL encoding Database socket

Create database

mysqladmin -h '192.168.2.233' -u root -p create namc;

View newly created database encoding

mysql> SHOW VARIABLES LIKE 'character%'
-> ;
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+

Encoding settings

set character_set_client=utf8;
set character_set_connection=utf8;
set character_set_database=utf8;
set character_set_filesystem=binary;
set character_set_results=utf8;
set character_set_server=utf8;

The above is the temporary setting encoding. If the database is restarted, the encoding will be restored.

Permanently set the encoding in mysql's configuration file.

vi /etc/my.cnf

In the [client] field, add default character set = utf8, as follows:

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set=utf8

In the [mysqld] field, add character set server = utf8, as follows:

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
character-set-server=utf8
//perhaps
default-character-set=utf8

In the [mysql] field, add default character set = utf8, as follows:

[mysql]
no-auto-rehash
default-character-set=utf8

After the modification is completed, service mysql restart restarts the mysql service to take effect. Note: there is a difference between the [mysqld] field and the [mysql] field.

Use SHOW VARIABLES LIKE 'character%'; check and find that the database code has been changed to utf8.

+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| 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/ |

If all of the above are modified and still garbled, the rest of the problem must be on the connection layer. The solution is to execute the following sentence before sending the query (directly written in the front of the SQL file):

SET NAMES 'utf8′;

It is equivalent to the following three instructions:

SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;

Modify ssh display Chinese format
vim /etc/sysconfig/i18n

LANG="en_US.UTF-8"
SYSFONT="latarcyrheb-sun16"
Change the above to the following
LANG="zh_CN.UTF-8"
SYSFONT="zh_CN.UTF-8:zh_CN:zh"

Posted by disconne on Fri, 06 Dec 2019 10:59:26 -0800