容器Mysql部署并解决中文乱码问题

1 、运行mysql容器环境

## --privileged=true 权限开启
## -v /root/mysql/log:/var/log/mysql  挂mysql日志卷
## -v /root/mysql/data:/var/lib/mysql 挂mysql数据卷
## -v /root/mysql/conf:/etc/mysql/conf.d 配置文件映射
## -e MYSQL_ROOT_PASSWORD=123456 环境变量的设置，设置mysqlROOT的密码
[root@localhost ~]# docker run -d  -p 3306:3306 --privileged=true -v /root/mysql/log:/var/log/mysql  -v /root/mysql/data:/var/lib/mysql -v /root/mysql/conf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 --name mysql mysql:5.7
8d5a5e13bbd88f56a0783bb8762e7f03793e709a695c6735c465281d61a09f3d
[root@localhost ~]# docker ps -l
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
8d5a5e13bbd8        mysql:5.7           "docker-entrypoint..."   9 seconds ago       Up 8 seconds        0.0.0.0:3306->3306/tcp, 33060/tcp   mysql

2、进入mysql容器测试

[root@localhost conf]# docker exec -it mysql /bin/bash
root@8d5a5e13bbd8:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> use db1;
Database changed
mysql> create table test (id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(1,'z3');
Query OK, 1 row affected (0.02 sec)

3、远程工具连接测试

发现插入中文数据报错

Incorrect string value: '\xE6\xB5\x8B\xE8\xAF\x95' for column 'name' at row 1

查看mysql的字符编码都是linti1编码，不支持中文

mysql>
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/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

mysql>

解决问题：

修改配置文件，把字符编码设置为utf8就好了

[root@localhost ~]# cd /root/mysql/conf/
[root@localhost conf]# vim my.cnf
[root@localhost conf]#cat my.cnf
[client]
default_character_set=utf8
[mysqld]
collation_server = utf8_general_ci
character_set_server = utf8
[root@localhost ~]# docker restart mysql
mysql

查看字符编码是否修改成功

[root@localhost ~]# docker exec -it mysql /bin/bash
root@8d5a5e13bbd8:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql>

在新建库，新建表，是否可以成功？

CREATE DATABASE db2;
CREATE TABLE test2(id INT,`name` VARCHAR(20));
INSERT INTO test2 VALUE(1,"张三"),(2,"李四");

可以是可以插入中文字符了

参考文章：https://blog.csdn.net/weixin_51697758/article/details/124459499?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522165106229616780366542872%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=165106229616780366542872&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allfirst_rank_ecpm_v1~rank_v31_ecpm-1-124459499.142^v9control,157^v4control&utm_term=%E5%AE%B9%E5%99%A8Mysql%E9%83%A8%E7%BD%B2%E5%B9%B6%E8%A7%A3%E5%86%B3%E4%B8%AD%E6%96%87%E4%B9%B1%E7%A0%81%E9%97%AE%E9%A2%98&spm=1018.2226.3001.4187