A, download

Download mirror

$ docker pull mysql:8.0.16
Copy the code

Configuration file

Place all configuration files and associated folders in /opt/docker-mysql

$ mkdir -p /opt/docker-mysql/conf.d
Copy the code

Example Add and modify the configuration file config-file. CNF

Set the table name to be case insensitive. This is done by default on Linux and not on Windows

[mysqld] # table name is case-insensitive lower_case_table_names datadir = = 1 # server - id = 1 / var/lib/mysql # socket = / var/lib/mysql/mysqlx sock #symbolic-links=0 # sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pidCopy the code

Three, start,

Adding a data folder

$ mkdir -p /opt/docker-mysql/var/lib/mysql
Copy the code

Start, set the default password 123456- ABC, TZ set the container’s default time zone

$ docker run --name mysql \ --restart=always \ -p 3306:3306 \ -v /opt/docker-mysql/conf.d:/etc/mysql/conf.d \ -v /opt/docker-mysql/var/lib/mysql:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=123456-abc \ -e TZ=Asia/Shanghai \ -d Mysql: 8.0.16Copy the code

4. Common commands

Into the container

$ docker exec -it mysql bash
Copy the code

See the log

$ docker logs -f mysql
Copy the code

The backup data

$ docker exec mysql sh -c 'exec mysqldump --all-databases -uroot -p"123456-abc"' > /some/path/on/your/host/all-databases.sql
Copy the code

Restore data

$ docker exec -i mysql sh -c 'exec mysql -uroot -p"123456-abc"' < /some/path/on/your/host/all-databases.sql
Copy the code

5. Other issues

Only_full_group_by problem

If the installation version is 5.7, the following error occurs when querying data

this is incompatible with sql_mode=only_full_group_by

You can use the following methods to solve the problem

1. Querysql_mode
select @@sql_mode
Copy the code

The results are as follows

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_EN GINE_SUBSTITUTION

2. Reset

Delete the ONLY_FULL_GROUP_BY configuration and set it to config-file. CNF again

[mysqld] # table name is case-insensitive lower_case_table_names datadir = = 1 # server - id = 1 / var/lib/mysql # socket = / var/lib/mysql/mysqlx sock #symbolic-links=0 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBST ITUTION [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pidCopy the code
3. Restart the container
$ docker restart mysql
Copy the code