docker部署mysql服务-解决中文乱码-数据持久化

docker部署mysql服务-解决中文乱码-数据持久化

下载mysql5.7镜像

[root@localhost ~]# docker pull mysql:5.7
[root@localhost ~]# docker images
REPOSITORY    TAG       IMAGE ID       CREATED         SIZE
hello-world   latest    d2c94e258dcb   12 months ago   13.3kB
mysql         5.7       c20987f18b13   2 years ago     448MB

启动、运行

格式：宿主机端口3306，容器内端口3306，root密码123456，镜像版本mysql:5.7
-e是环境变量，指定mysql数据库密码

[root@localhost ~]# docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
84bbbc8705042184297e507d17186ae6a3b9fd1ba5b790150340e3ebc5712f56
[root@localhost ~]# docker ps
CONTAINER ID   IMAGE       COMMAND                  CREATED         STATUS        PORTS                                                  NAMES
84bbbc870504   mysql:5.7   "docker-entrypoint.s…"   2 seconds ago   Up 1 second   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   adoring_lamarr

#进入容器实例，登录数据库
[root@localhost ~]# docker exec -it adoring_lamarr /bin/bash
root@84bbbc870504:/# 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

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

mysql> use db01;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table t1(id int,name varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> insert into t1  values (1,'wq');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | wq   |
+------+------+
1 row in set (0.00 sec)

解决中文乱码、数据持久化

删除之前创建的容器

docker rm -f 84bbbc870504

重新创建一个容器

# 格式：
docker run -d -p 3306:3306 --privileged=True \
-v /docker-volume/mysql/log:/var/log/mysql \
-v /docker-volume/mysql/data:/var/lib/mysql \
-v /docker-volume/mysql/conf:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=123456 \
--name=mysql mysql:5.7

[root@localhost ~]# docker ps
CONTAINER ID   IMAGE       COMMAND                  CREATED          STATUS          PORTS                                                  NAMES
4cd78afe58e9   mysql:5.7   "docker-entrypoint.s…"   14 seconds ago   Up 13 seconds   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql

-v /docker-volume/mysql/log:/var/log/mysql   存放日志
-v /docker-volume/mysql/data:/var/lib/mysql    存放数据
-v /docker-volume/mysql/conf:/etc/mysql/conf.d    存放配置文件

解决中文乱码

进入同步的容器卷目录中，新建my.cnf文件，通过容器卷同步给mysql容器实例，解决中文乱码

[root@localhost ~]# cd /docker-volume/mysql/conf/
[root@localhost conf]# ll
total 0
[root@localhost conf]# vi 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 conf]# docker restart mysql
mysql

进入容器实例、登录数据库

[root@localhost conf]# docker exec -it mysql /bin/bash
root@4cd78afe58e9:/# 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 test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed

mysql> create table t1(id int,name varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1,'Tom');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | Tom  |
+------+------+
1 row in set (0.00 sec)

mysql> insert into t1 values (2,'测试');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | Tom    |
|    2 | 测试   |
+------+--------+
2 rows in set (0.00 sec)

检验数据持久化

mysql容器实例被删除之后，是否还能恢复

删除容器实例

[root@localhost conf]# docker ps
CONTAINER ID   IMAGE       COMMAND                  CREATED          STATUS          PORTS                                                  NAMES
4cd78afe58e9   mysql:5.7   "docker-entrypoint.s…"   17 minutes ago   Up 10 minutes   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql
[root@localhost conf]# docker rm -f mysql
mysql
[root@localhost conf]# docker ps
CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES

重新创建容器
指定卷

[root@localhost conf]# docker run -d -p 3306:3306 --privileged=True \
> -v /docker-volume/mysql/log:/var/log/mysql \
> -v /docker-volume/mysql/data:/var/lib/mysql \
> -v /docker-volume/mysql/conf:/etc/mysql/conf.d \
> -e MYSQL_ROOT_PASSWORD=123456 \
> --name=mysql mysql:5.7
0a16e25df22113c6ef3790fd2fb651edca9fb532de070704f3bcbf370cf10ac2
[root@localhost conf]# docker ps
CONTAINER ID   IMAGE       COMMAND                  CREATED          STATUS          PORTS                                                  NAMES
0a16e25df221   mysql:5.7   "docker-entrypoint.s…"   22 seconds ago   Up 21 seconds   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql

查看数据是否存在

[root@localhost conf]# docker exec -it mysql /bin/bash
root@0a16e25df221:/# 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                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | Tom    |
|    2 | 测试   |
+------+--------+
2 rows in set (0.00 sec)