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)