Docker install MySQL 5.7

Pull MySQL5.7 mirror

docker pull mysql:5.7   Mysql 5.7
docker pull mysql       # pull the latest mysql image

# Check whether the mirror is pulled successfully
docker images
Copy the code

Build the MySQL database container

docker run -d -e \
MYSQL_ROOT_PASSWORD=123456 - the name mysql 5.7 \- v/BJFC/docker/mysql/data: / var/lib/mysql \ v/etc/localtime: / etc/localtime \ -p 3306:3306 \ mysql: 5.7Copy the code
  • – name: indicates the container name, which is mysql-5.7
  • -e: configures the password of the root user of mysql
  • -p: indicates port mapping. Port 3306 of the host is mapped to port 3306 of the container
  • -d: Runs the container in the background to ensure that the container continues to run after you exit the terminal
  • -v: indicates the directory mapping between a host and a container. “:” indicates a host directory before the “:” indicates a container directory after the “:”

Note: the time in the docker container is “standard time”, which is 8 hours different from “Beijing time”. There are many solutions here, but this is only one solution to ensure the correct storage time

Check the MySQL container and log in to MySQL

Mysql -uroot -pfc888888 docker mysql -uroot -pfc888888Copy the code

There is one caveat when using remote connection software

Mysql -u root -p ALTER USER 'root'@'localhost' IDENTIFIED BY '123456 '; CREATE USER 'HCTM '@'%' IDENTIFIED WITH mysql_native_password BY '123456 '; GRANT ALL PRIVILEGES ON *.* TO 'hctm'@'%'; Grant all PRIVILEGES on *.* to root@'%' IDENTIFIED by "123456"; Mysql > select * from user where user = 'root'; Database changed mysql> select host,user,password from user; +--------------+------+-------------------------------------------+ | host | user | password | +--------------+------+-------------------------------------------+ | localhost | root | * A731AEBFB621E354CD41BAF207D884A609E81F5E | | 192.168.1.1 | root | * A731AEBFB621E354CD41BAF207D884A609E81F5E | + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC) mysql > grant all privileges  on *.* to root@'%' identified by "password"; Query OK, 0 rows affected (0.00 SEC) mysql> flush PRIVILEGES; Query OK, 0 rows affected (0.00 SEC) mysql> select host,user,password from user; +--------------+------+-------------------------------------------+ | host | user | password | +--------------+------+-------------------------------------------+ | localhost | root | * A731AEBFB621E354CD41BAF207D884A609E81F5E | | 192.168.1.1 | root | * A731AEBFB621E354CD41BAF207D884A609E81F5E | | | % root | *A731AEBFB621E354CD41BAF207D884A609E81F5E | +--------------+------+-------------------------------------------+ 3 Rows in set (0.00 SEC)Copy the code

MySQL table name case problem in container

This is very useful if you are a coder with ocD, it can solve your inner pain and there are only two things you need to know about this problem

  • Change the mysqld.conf configuration parameter: lower_case_table_names The default value of this parameter is different for different systems.
    • Lower_case_table_names = 1 Table names are stored in lower case on disk, but are case-insensitive for comparison
    • Lower_case_table_names =0 Table names are stored for a given size and are case sensitive for comparison
    • Lower_case_table_names =2. Table names are stored in the given case but are compared in lower case

Note: Default values are 0 for Windows and 2 for MacOS

  • The mysqld.conf configuration file is stored in /etc/mysql.conf.d
Docker exec it mysql-5.7 /bin/bash mysql -u root -p # mysql> show global variables like '%lower_case%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_file_system | OFF | | lower_case_table_names | 0 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 2 rows in the set (0.01 SEC) #lower_case_file_system Indicates whether the current system file is case sensitive and read-only and cannot be changed. #ON indicates case insensitive. #OFF indicates case sensitivity. # Since vi or vim is not installed in the container, copy the configuration file from the container, modify it and then copy it into the container. Copy files from the container to the host /data directory: MySQL > exit MySQL > exit MySQL > exit MySQL CD/data into the data folder docker cp mysql 5.7: / etc/mysql/mysql. Conf., d/mysqld. CNF/data # will mysqld. CNF file is copied to the data folder vim mysqld. CNF # modify the file/mysqld lower_case_table_names pid = 1 - the file = / var/run/mysqld/mysqld. Pid socket = / var/run/mysqld/mysqld. The sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log # By default we only accept connections from localhost "mysqld.cnf" 39L, After completion of 1635 c # modified then mysqld. Cp CNF file to docker container docker cp/data/mysqld CNF mysql 5.7: / etc/mysql/mysql. Conf., d docker restart Mysql > show global variables like '%lower_case%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_file_system | OFF | | lower_case_table_names | 1 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 2 rows in the set (0.01 SEC)Copy the code

This action resolves the case of MySQL table names, especially for activiti tables that are automatically created, and will show unified lower case that’s all. Note: When performing this operation on a database with existing tables, back up and delete the tables that have been capitalized. After performing this operation, restore the saved table data. If there is no backup, the above operation will hide all your upper case tables, but hide them and restore them automatically if you change lower_case_table_names =0. However, if you do this without dropping the table, an uppercase table and a lowercase table with the same name appear in the database. Author obsessive-compulsive disorder is very serious, if there is no such demand, can be ignored.