Docker

Using Mysql Images

Docker_Hub,Mysql official image Description Mysql official image Dockerfile Mysql container resolves non-UTF8 garbled characters

SET character_set_client = UTF8; SET character_set_client = UTF8; SET character_set_client = UTF8; Such changes do not work at all. The final solution is to modify the mysqld.conf or my.conf configuration files.

Import and start the Mysql container

Pull the Mysql image. If you need to login, run the docker login command and enter the account password as prompted.

Docker pull mysql: 5.7

Start Mysql container

Docker run it --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7

Enter the container to check the character encoding, the default character set used by latin1, so that Chinese garble problems are most likely to occur.

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 the set (0.00 SEC)Copy the code

Write/home/test/mysql. Conf/mysqld. CNF configuration file

[client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] port=13306 #character-set-client-handshake=FALSE character-set-server=utf8mb4 character-set-filesystem=utf8mb4 Collation -server= UTf8MB4_general_ci init-connect='SET NAMES UTF8MB4 'lower-case-table-names=1 skip-character-set-client-handshake mysql skip-name-resolveCopy the code

Start Mysql container

docker run -itd –name mysql -p 13306:13306 -v /home/test/project/mysql/:/var/lib/mysql -v /home/test/mysql.conf/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf -v /etc/localtime/:/etc/localtime/:ro -e MYSQL_ROOT_PASSWORD = 123456 mysql: 5.7 some bloggers give behind the command of the need to add the following two configurations, because the need to solve the problem of utf8, configure them directly into the configuration file – character – set – server = utf8mb4 –collation-server=utf8md4_unicode_ci

Create a user and start the Mysql container

docker run -itd --name mysql --restart=always -p 13306:13306 -e MYSQL_USER="testw" -e MYSQL_PASSWORD="123456" -e MYSQL_ROOT_PASSWORD="123456"

Docker updates some configurations

docker update --restart=always mysql

Docker startup parameters

-i: Runs the container in interactive mode, usually with -t. - it-t: reassigns a pseudo-input terminal to the container. It is usually used together with -i. -d: Runs the container in the background and returns the container ID. -p: specifies the port mapping. The format is: Host (host) port: container port --name="nginx-lb": specifies a name for the container. -e username=" Ritchie ": Set the environment variable. -v file mapping: Host folder: container folder; (It is mainly used to persist database data to the host to prevent data loss caused by container damage.) - net = "bridge" : specify the container's network connection type, support bridge/host/none/container: four types; --restart: restart policy: On -failure:3, restart container on abnormal exit (up to 3 times) 4. Always, 5. Unless -stopped, always restart the container when the container exits, but not for containers that stopped when the Docker daemon started # uncommon -a stdin: STDIN, STDOUT, and STDERR are optional. -p: random port mapping. Ports inside a container are randomly mapped to ports on the host. -- DNS 8.8.8.8: specifies the DNS server used by the container. --dns-search example.com: specify the container DNS search domain name, which is the same as the host by default. -h "Mars ": specifies the hostname of the container. --env-file=[]: reads environment variables from specified files; --cpuset="0-2" or --cpuset="0,1,2": bind the container to the specified CPU; -m: sets the maximum memory used by a container. --link=[]: Add link to another container; --expose=[]: Open a port or group of ports; --volume, -v: Binds a volumeCopy the code

Docker run Command parameter description

# the entire command docker run - itd - name mysql - p 13306:13306 - restart = always - v/home/test/project/mysql / : / var/lib/mysql - v /home/test/mysql.conf/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf -v /etc/localtime/:/etc/localtime/:ro -e MYSQL_USER="testw" -e MYSQL_PASSWORD="123456" -e MYSQL_ROOT_PASSWORD="123456 -i(run the container in interactive mode) -t(reassign a pseudo-input terminal to the container) -d(run the container in background, Mysql > select * from mysqld where port=13306 -p 13306:13306 # --restart=always # persist mysql data to the local server. Docker also strongly recommends persisting mysql data to the local server, because mysql containers are likely to be corrupted (if corrupted, data will be lost). -v / home/test/project/mysql /, / var/lib/mysql # separate configuration to replace mysqld default configuration, to solve the problem of the character set of the UTF8, and optimize the mysql. - v / home/test/mysql. Conf/mysqld. CNF: / etc/mysql/mysql. Conf., d/mysqld. CNF # container for solving time and the host of time is not the same problem - v The/etc/localtime /, / etc/localtime / : ro # to create a user name and password - MYSQL_USER = "testw" - e e MYSQL_PASSWORD = "123456" # set the root password - e MYSQL_ROOT_PASSWORD="123456 # specify start mirror mysql:5.7Copy the code

The character_set_system character set is utF8, and other characters are UTF8MB4.

mysql> show variables like "%character%"; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | utf8mb4 | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | The/usr/share/mysql/charsets / | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 8 rows in the set (0.01 SEC)Copy the code

Use the mysql container to connect to the database

Docker run mysql container to simulate the mysql environment for remote connection mysql container, mysql can be implemented without entering the container can be mysql operation.

Docker run it --rm mysql:5.7 mysql -uroot -h 10.122.30.199 -p 13306-p

mysql skip-name-resolve

Mysql skip-name-resolve problem Skip-name -resolve problem

8 character_set variables in Mysql

Use show variables like “%char%”;

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     | latin1                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
Copy the code

How these parameters work:

1. If a character set is not specified, the character_set_server character set is used. When creating a table: If the character set is not explicitly specified, use the character set used by the current library. If the character set is not specified when modifying a table field, the character set used by the current table is used.
2. Character set conversion process: CHARACTER_set_client –> CHARACTER_set_connection –> table character set. Character set conversion process: table character set -> CHARACTER_set_result
3. Character_set_database Character set of the current default database. For example, after the use XXX command is executed, the current database becomes XXX.

After starting Mysql, just pay attention to whether the following variables meet the requirements

• character_set_client
• character_set_connection
• character_set_database
• character_set_results
• character_set_server

The following three system variables we do not need to care about, will not affect the garble and other problems

• character_set_filesystem
• character_set_system
• character_sets_dir

Create a database and import and export data

Creating a database

CREATE DATABASE IF NOT EXISTS test_databasedefault CHARSET utf8 COLLATE utf8_general_ci.

Import data

Source G:\aa\test.sql

Export data

mysqldump -h localhost -u root -p test> G:\aa\test.sql

Principle of MySQL character encoding conversion

When receiving a request, the MySQL Server converts the request data from character_set_client to CHARACTER_set_CONNECTION before performing an internal operation The internal operation character set (CHARACTER_set_server), the steps are as follows:

1. Use the CHARACTER SET value for each data field;
2. If the above values do not exist, use the character set of the corresponding data table
3. If the above values do not exist, the character set value of the corresponding database is used
4. If the value does not exist, set the character_set_server value.
5. Finally, convert the operation result from the internal character set to CHARACTER_set_results;

Docker – compose start Mysql

New docker – compose. Yml

Services: mysql: image: mysql:5.7 # command: ['--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci'] volumes: - /home/test/project/mysql/:/var/lib/mysql - ./mysql/:/docker-entrypoint-initdb.d/ - /home/test/mysql.conf/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf - ./sqlfile:/app/sqlfile environment: - "MYSQL_DATABASE=apigateway-ai" - "MYSQL_USER=appuser" - "MYSQL_PASSWORD=123456" - "MYSQL_ROOT_PASSWORD=root" ports: - "13306:13306" restart: logging: driver: "json-file" options: max-size: "1g"Copy the code

Creating a Mysql Image

Mysql > create a master/slave copy of the Mysql image based on Docker

Mysql, Nginx, Redis, Tomcat, etc., all have official images. Normally we just use it.

Docker images

• KERNEL is available for every Linux operating system,image is not included.
• Debian-image represents common Linux systems (excluding KERNEL), such as CentOS, Ubuntu, and DEBIAN, which are also known as Linux distributions. In addition to Kernel, these include a large number of operating system extensions, various management tools, development tools, graphical interface support, etc. They are usually huge, and the command line version of the file system can be hundreds of megabytes. These images are about 100 to 200M.
• BUSY Box-Image represents a compact Linux system, such as Alpine, BusyBox, CoreOS, etc., that supports only the most basic Linux commands and a simple application package management, as well as support to meet the needs of cloud applications. These mirrors are 5-10m.
• ADD emacs-image represents a basic application like EMACS, which simply adds its own code and dependency packages to the base operating system files;
• ADD apache-image indicates the APACHE Web application, which is based on the existing Emacs image. That is, APACHE and its dependent packages are added to the file system. This creates a layered file system, and even though apache-Image contains only its own content, once started, we can access the underlying operating system, programs and packages provided by Emacs.
• WRITEBLE COTAINER is an intermediate file generated when an application runs in isolation from a Host in an image.

Therefore:

Image is a collection of file (delta) and Context configuration files.

Docker – compose the installation

apt-get install python-pip
pip install --upgrade pip
pip install docker-compose
docker-compose -version
Copy the code

Mysql container running

• Run the container and synchronize time with the host:

-v /etc/localtime:/etc/localtime

The resources

• Note about 8 character_set variables in MySQL
• MySQL character set – check rules
• MySQL character set introduction
• mysql Utf8
• docker