How to manage MySQL 8.0 server based on docker

Execute docker-based installation script

• Step 1

Run the following script to initialize a mysql docker instance

docker run --detach \
--restart always \
--publish 3306:3306 \
--name mysql \
--volume /usr/share/zoneinfo/Asia/Shanghai:/etc/localtime \
--env MYSQL_ROOT_PASSWORD=Gah6kuP7ohfio4 \
--env MYSQL_USER=lanzhou \
--env MYSQL_DATABASE=lanzhou \
--env MYSQL_PASSWORD=Gah6kuP7ohfio4 \
mysql:8.0.21 \
--character-set-server=utf8mb4 \
--collation-server=utf8mb4_unicode_ci

After executing the above docker script, a server instance named mysql will be started in the docker container.
Considering that domestic developers will store data information based on the East 8 district where Beijing time is in the actual application process, so here we switch the time zone of the mysql server instance to the East 8 district where the Beijing time zone is located in the script script.In addition to these, we have also created an independent database for practical applications and users who operate the database. Of course, the super administrator in the entire mysql server system is still root. After the system is delivered, the super administrator can log in based on the root user to manage the entire mysql database in a unified and centralized manner.

• Setp 2

Now let us create the following directories for mounting related directories of the mysql container instance.

sudo mkdir -p /data/docker/mysql

Next, we use the docker command to copy the data in the directories /var/lib/mysql and /etc/mysql in the container mysql to the /data/docker/mysql directory of the docker host.

sudo docker cp mysql:/var/lib/mysql /data/docker/mysql/data
sudo docker cp mysql:/etc/mysql /data/docker/mysql/etc

• Step 3

Next, we will use the following commands to change the ownership of the two directories above /data/docker/myql/data and /data/docker/mysql/etc to non-root users.

sudo chown lwk:lwk -R /data/docker/mysql/etc/
sudo chown lwk:lwk -R /data/docker/mysql/data/

• Step4

Next, we use the following docker command to remove the docker container named mysql.

docker stop mysql
docker rm mysql

• Step 5

Next, we use the following docker command to regenerate a docker container named mysql.

docker run  --detach \
--restart always \
--publish 3306:3306 \
--name mysql \
--volume /data/docker/mysql/data:/var/lib/mysql \
--volume /data/docker/mysql/etc:/etc/mysql \
--volume /usr/share/zoneinfo/Asia/Shanghai:/etc/localtime \
--env MYSQL_ROOT_PASSWORD=Gah6kuP7ohfio4 \
--env MYSQL_USER=lanzhou \
--env MYSQL_DATABASE=lanzhou \
--env MYSQL_PASSWORD=Gah6kuP7ohfio4 \
mysql:8.0.21 \
--character-set-server=utf8mb4

After the above script is executed, the docker system regenerates a new container named mysql for us, and the data directory and configuration directory have been mounted to the corresponding directory on the host

Connect to mysql server

Use the mysql command line tool to connect to our docker container-based mysql service.

lwk@qwfys:~$ mysql -h 127.0.0.1 -u lanzhou -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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 |
| lanzhou            |
+--------------------+
2 rows in set (0.01 sec)

mysql> SHOW SESSION VARIABLES LIKE 'character\_set\_%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database   | utf8mb4 |
| character_set_filesystem | binary  |
| character_set_results    | utf8mb4 |
| character_set_server     | utf8mb4 |
| character_set_system     | utf8    |
+--------------------------+---------+
7 rows in set (0.01 sec)

mysql> SHOW SESSION VARIABLES LIKE 'collation_connection';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
1 row in set (0.01 sec)

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

mysql> 

本文地址：https://blog.csdn.net/qwfys200/article/details/82385923