Docker上搭建Mysql8.0服务器

最新推荐文章于 2024-08-07 23:49:07 发布

星河_赵梓宇

最新推荐文章于 2024-08-07 23:49:07 发布

阅读量3.3k

点赞数

文章标签： MYSQL

MySQL is a widely used, open-source relational database management system (RDBMS).

Mysql是广泛使用的开源关系型数据库

Full Description

详细介绍

Supported tags and respective `Dockerfile` links

目前dockerhub提供的Mysql镜像的dockerfile链接，和支持的Mysql的docker镜像版本

How to use this image

如何使用镜像

Start a `mysql` server instance

Starting a MySQL instance is simple:

启动MySQL实例的命令

$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag

... where some-mysql is the name you want to assign to your container, my-secret-pw is the password to be set for the MySQL root user and tag is the tag specifying the MySQL version you want. See the list above for relevant tags.

此处的my-secret-pw，改为需要设置的root登录密码，some-mysql 改为mysql的你自己定义的容器名称，tag改为版本号，比如5.7。

演示：

# 本地使用33060端口来登录mysql服务 127.0.0.1:33060 --restart=always表示容器重启后，服务自动启动
$ sudo docker run --name fedora-mysql -p 33060:3306  -e MYSQL_ROOT_PASSWORD=password -e MYSQL_DATABASE=mydb -e MYSQL_USER=admin -e MYSQL_PASSWORD=password -d mysql --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --restart=always

#启动完毕后，可以使用 docker ps 来查看容器进程 ，不过此时本地客户端可能登录不上mysql,需要在容器中配置外网可以访问
$ sudo docker ps

[root@localhost ****]# docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                                NAMES
dc52595710e3        mysql               "docker-entrypoint..."   5 seconds ago       Up 3 seconds        33060/tcp, 0.0.0.0:33060->3306/tcp   fedora-mysql

#使用 docker logs ContainerID 来查看启动日志  docker logs dc52595710e3
[root@localhost ****]# docker logs dc52595710e3
Initializing database
2018-11-27T13:17:05.502469Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2018-11-27T13:17:05.502716Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.13) initializing of server in progress as process 28
2018-11-27T13:17:10.167118Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2018-11-27T13:17:13.305238Z 5 [Warning] [MY-010315] [Server] 'user' entry 'mysql.infoschema@localhost' ignored in --skip-name-resolve mode.
2018-11-27T13:17:13.306127Z 5 [Warning] [MY-010315] [Server] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-11-27T13:17:13.306381Z 5 [Warning] [MY-010315] [Server] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-11-27T13:17:13.306596Z 5 [Warning] [MY-010315] [Server] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2018-11-27T13:17:13.306820Z 5 [Warning] [MY-010323] [Server] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-11-27T13:17:13.307339Z 5 [Warning] [MY-010323] [Server] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-11-27T13:17:13.307557Z 5 [Warning] [MY-010311] [Server] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2018-11-27T13:17:13.307916Z 5 [Warning] [MY-010330] [Server] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-11-27T13:17:13.308120Z 5 [Warning] [MY-010330] [Server] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-11-27T13:17:14.725487Z 0 [System] [MY-013170] [Server] /usr/sbin/mysqld (mysqld 8.0.13) initializing of server has completed
Database initialized
MySQL init process in progress...
2018-11-27T13:17:17.453331Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2018-11-27T13:17:17.453641Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.13) starting as process 79
MySQL init process in progress...
mbind: Operation not permitted
2018-11-27T13:17:18.884853Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2018-11-27T13:17:18.894411Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2018-11-27T13:17:18.908823Z 0 [Warning] [MY-010315] [Server] 'user' entry 'mysql.infoschema@localhost' ignored in --skip-name-resolve mode.
2018-11-27T13:17:18.909341Z 0 [Warning] [MY-010315] [Server] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-11-27T13:17:18.909536Z 0 [Warning] [MY-010315] [Server] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-11-27T13:17:18.909958Z 0 [Warning] [MY-010315] [Server] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2018-11-27T13:17:18.910226Z 0 [Warning] [MY-010323] [Server] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-11-27T13:17:18.910404Z 0 [Warning] [MY-010323] [Server] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-11-27T13:17:18.910575Z 0 [Warning] [MY-010311] [Server] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2018-11-27T13:17:18.915169Z 0 [Warning] [MY-010330] [Server] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-11-27T13:17:18.915961Z 0 [Warning] [MY-010330] [Server] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-11-27T13:17:18.925362Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.13'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  MySQL Community Server - GPL.
2018-11-27T13:17:18.933326Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock'
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.


#使用 docker exec -it ContainerID bash 进入容器的shell环境

[root@localhost miazzy]# docker exec -it dc52595710e3 bash
root@dc52595710e3:/# 
root@dc52595710e3:/# 
root@dc52595710e3:/# 

#使用 uname -a 查看linux内核信息

root@dc52595710e3:/# uname -a
Linux dc52595710e3 4.18.16-300.fc29.x86_64 #1 SMP Sat Oct 20 23:24:08 UTC 2018 x86_64 GNU/Linux
root@dc52595710e3:/# yum
bash: yum: command not found
root@dc52595710e3:/# systemctl
bash: systemctl: command not found

#使用 mysql -u root -p 登录root账户
root@dc52595710e3:/# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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> 
mysql> 
mysql> 
mysql> 

# 给账户设置所有网络的访问权限
mysql> grant all privileges on *.* to miazzy@'%'  with grant option;
Query OK, 0 rows affected (0.04 sec)

# 显示0 rows ，可能没有生效
mysql> select * from mysql.user；

# 更新表host字段为%
mysql> update user set host = '%' where user = 'root';  
ERROR 1046 (3D000): No database selected

mysql> use mysql
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> update user set host = '%' where user = 'root'; 
ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'

mysql> update user set host = '%' where user = 'miazzy';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

# 查询user表的信息
mysql> select host , user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | miazzy           |
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
6 rows in set (0.00 sec)

mysql>

Connect to MySQL from an application in another Docker container

其他容器使用此Mysql应用的方法

This image exposes the standard MySQL port (3306), so container linking makes the MySQL instance available to other application containers. Start your application container like this in order to link it to the MySQL container:

此镜像默认暴露了3306端口，所以其他容器运行时，可以通过 --link some-mysql:mysql 来使用Mysql实例，注意some-mysql要改成你自己定义的Mysql的容器名称。

$ docker run --name some-app --link some-mysql:mysql -d application-that-uses-mysql

Connect to MySQL from the MySQL command line client

The following command starts another mysql container instance and runs the mysql command line client against your original mysql container, allowing you to execute SQL statements against your database instance:

$ docker run -it --link some-mysql:mysql --rm mysql sh -c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD"'

... where some-mysql is the name of your original mysql container.

This image can also be used as a client for non-Docker or remote MySQL instances:

$ docker run -it --rm mysql mysql -hsome.mysql.host -usome-mysql-user -p

More information about the MySQL command line client can be found in the MySQL documentation

通过Docker-compse方式启动Mysql的实例

... via `docker stack deploy` or `docker-compose`

Example stack.yml for mysql:

# Use root/example as user/password credentials
version: '3.1'

services:

  db:
    image: mysql
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: example

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

保持上述代为为stack.yml,执行命令

docker stack deploy -c stack.yml mysql #或者执行命令

docker-compose -f stack.yml up

等待一段时间系统初始化，访问http://localhost:8080即可，adminer是一个Php的数据库管理器,这个地方启动了两个容器，一个Mysql,一个adminer.Mysql运行后，通过容器的Link机制，把3306端口暴露给了adminer的容器，adminer容器内部可以访问3306端口来管理数据。

Run docker stack deploy -c stack.yml mysql (or docker-compose -f stack.yml up), wait for it to initialize completely, and visit http://swarm-ip:8080, http://localhost:8080, or http://host-ip:8080 (as appropriate).

Container shell access and viewing MySQL logs

The docker exec command allows you to run commands inside a Docker container. The following command line will give you a bash shell inside your mysql container:

执行下面命令，可以进入此容器的shell命令终端，效果和 ssh远程登录相似。

$ docker exec -it some-mysql bash

The MySQL Server log is available through Docker's container log:

如需要打印容器日志，使用下面命令

$ docker logs some-mysql

Using a custom MySQL configuration file

The default configuration for MySQL can be found in /etc/mysql/my.cnf, which may !includedir additional directories such as /etc/mysql/conf.d or /etc/mysql/mysql.conf.d. Please inspect the relevant files and directories within the mysql image itself for more details.

If /my/custom/config-file.cnf is the path and name of your custom configuration file, you can start your mysql container like this (note that only the directory path of the custom config file is used in this command):

使用自定义的mysql配置文件，配置文件请放在/my/custom目录中，另外需要设置/my/custom为docker容器的共享目录

$ docker run --name some-mysql -v /my/custom:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag

This will start a new container some-mysql where the MySQL instance uses the combined startup settings from /etc/mysql/my.cnf and /etc/mysql/conf.d/config-file.cnf, with settings from the latter taking precedence.

Configuration without a `cnf` file

Many configuration options can be passed as flags to mysqld. This will give you the flexibility to customize the container without needing a cnf file. For example, if you want to change the default encoding and collation for all tables to use UTF-8 (utf8mb4) just run the following:

通过配置参数，来设置UTF-8(utf8mb4)字符集

$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

If you would like to see a complete list of available options, just run:

如果你需要查看各个可选配置信息，请在命令后面加 --verbose --help

$ docker run -it --rm mysql:tag --verbose --help

Environment Variables

环境变量介绍

When you start the mysql image, you can adjust the configuration of the MySQL instance by passing one or more environment variables on the docker run command line. Do note that none of the variables below will have any effect if you start the container with a data directory that already contains a database: any pre-existing database will always be left untouched on container startup.

See also https://dev.mysql.com/doc/refman/5.7/en/environment-variables.html for documentation of environment variables which MySQL itself respects (especially variables like MYSQL_HOST, which is known to cause issues when used with this image).

`MYSQL_ROOT_PASSWORD`

This variable is mandatory and specifies the password that will be set for the MySQL root superuser account. In the above example, it was set to my-secret-pw.

此参数用于设置 root的密码

`MYSQL_DATABASE`

This variable is optional and allows you to specify the name of a database to be created on image startup. If a user/password was supplied (see below) then that user will be granted superuser access (corresponding to GRANT ALL) to this database.

此参数用于在启动时，初始化一个database

`MYSQL_USER`, `MYSQL_PASSWORD`

These variables are optional, used in conjunction to create a new user and to set that user's password. This user will be granted superuser permissions (see above) for the database specified by the MYSQL_DATABASE variable. Both variables are required for a user to be created.

Do note that there is no need to use this mechanism to create the root superuser, that user gets created by default with the password specified by the MYSQL_ROOT_PASSWORD variable.

此参数用于定义Mysql账号和密码，对上述MYSQL_DATABASE数据库拥有超级权限，记住MYSQL_DATABASE/MYSQL_USER/MYSQL_PASSWORD应当一起设置

`MYSQL_ALLOW_EMPTY_PASSWORD`

This is an optional variable. Set to yes to allow the container to be started with a blank password for the root user. NOTE: Setting this variable to yes is not recommended unless you really know what you are doing, since this will leave your MySQL instance completely unprotected, allowing anyone to gain complete superuser access.

`MYSQL_RANDOM_ROOT_PASSWORD`

This is an optional variable. Set to yes to generate a random initial password for the root user (using pwgen). The generated root password will be printed to stdout (GENERATED ROOT PASSWORD: .....).

开启随机设置ROOT密码，系统启动时将打印ROOT的随机密码

如果没有看见日志信息，可以使用命令 $ docker logs some-mysql查看

`MYSQL_ONETIME_PASSWORD`

Sets root (not the user specified in MYSQL_USER!) user as expired once init is complete, forcing a password change on first login. NOTE: This feature is supported on MySQL 5.6+ only. Using this option on MySQL 5.5 will throw an appropriate error during initialization.

Docker Secrets

As an alternative to passing sensitive information via environment variables, _FILE may be appended to the previously listed environment variables, causing the initialization script to load the values for those variables from files present in the container. In particular, this can be used to load passwords from Docker secrets stored in /run/secrets/<secret_name> files. For example:

$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD_FILE=/run/secrets/mysql-root -d mysql:tag

Currently, this is only supported for MYSQL_ROOT_PASSWORD, MYSQL_ROOT_HOST, MYSQL_DATABASE, MYSQL_USER, and MYSQL_PASSWORD.

Initializing a fresh instance

When a container is started for the first time, a new database with the specified name will be created and initialized with the provided configuration variables. Furthermore, it will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d. Files will be executed in alphabetical order. You can easily populate your mysql services by mounting a SQL dump into that directory and provide custom images with contributed data. SQL files will be imported by default to the database specified by the MYSQL_DATABASE variable.

Caveats

Where to Store Data

Important note: There are several ways to store data used by applications that run in Docker containers. We encourage users of the mysql images to familiarize themselves with the options available, including:

Let Docker manage the storage of your database data by writing the database files to disk on the host system using its own internal volume management. This is the default and is easy and fairly transparent to the user. The downside is that the files may be hard to locate for tools and applications that run directly on the host system, i.e. outside containers.
Create a data directory on the host system (outside the container) and mount this to a directory visible from inside the container. This places the database files in a known location on the host system, and makes it easy for tools and applications on the host system to access the files. The downside is that the user needs to make sure that the directory exists, and that e.g. directory permissions and other security mechanisms on the host system are set up correctly.

The Docker documentation is a good starting point for understanding the different storage options and variations, and there are multiple blogs and forum postings that discuss and give advice in this area. We will simply show the basic procedure here for the latter option above:

Create a data directory on a suitable volume on your host system, e.g. /my/own/datadir.

Start your mysql container like this:

$ docker run --name some-mysql -v /my/own/datadir:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag

The -v /my/own/datadir:/var/lib/mysql part of the command mounts the /my/own/datadir directory from the underlying host system as /var/lib/mysql inside the container, where MySQL by default will write its data files.

No connections until MySQL init completes

If there is no database initialized when the container starts, then a default database will be created. While this is the expected behavior, this means that it will not accept incoming connections until such initialization completes. This may cause issues when using automation tools, such as docker-compose, which start several containers simultaneously.

If the application you're trying to connect to MySQL does not handle MySQL downtime or waiting for MySQL to start gracefully, then a putting a connect-retry loop before the service starts might be necessary. For an example of such an implementation in the official images, see WordPress or Bonita.

Usage against an existing database

If you start your mysql container instance with a data directory that already contains a database (specifically, a mysql subdirectory), the $MYSQL_ROOT_PASSWORD variable should be omitted from the run command line; it will in any case be ignored, and the pre-existing database will not be changed in any way.

Running as an arbitrary user

If you know the permissions of your directory are already set appropriately (such as running against an existing database, as described above) or you have need of running mysqld with a specific UID/GID, it is possible to invoke this image with --user set to any value (other than root/0) in order to achieve the desired access/configuration:

如果宿主机的$PWD/data目录，没有读写权限，请加上 --user 1000:1000 确保这个UID/GID具此目录的读写权限

$ mkdir data
$ ls -lnd data
drwxr-xr-x 2 1000 1000 4096 Aug 27 15:54 data
$ docker run -v "$PWD/data":/var/lib/mysql --user 1000:1000 --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag

Creating database dumps

Most of the normal tools will work, although their usage might be a little convoluted in some cases to ensure they have access to the mysqld server. A simple way to ensure this is to use docker exec and run the tool from the same container, similar to the following:

导出数据库数据的命令：

$ docker exec some-mysql sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > /some/path/on/your/host/all-databases.sql

Quick reference

其他参考链接

Where to get help:
the Docker Community Forums, the Docker Community Slack, or Stack Overflow
Where to file issues:
https://github.com/docker-library/mysql/issues
Maintained by:
the Docker Community and the MySQL Team
Supported architectures: (more info)
amd64
Published image artifact details:
repo-info repo's repos/mysql/ directory (history)
(image metadata, transfer size, etc)
Image updates:
official-images PRs with label library/mysql
official-images repo's library/mysql file (history)
Source of this description:
docs repo's mysql/ directory (history)
Supported Docker versions:
the latest release (down to 1.6 on a best-effort basis)

License

版权信息

View license information for the software contained in this image.

As with all Docker images, these likely also contain other software which may be under other licenses (such as Bash, etc from the base distribution, along with any direct or indirect dependencies of the primary software being contained).

Some additional license information which was able to be auto-detected might be found in the repo-info repository's mysql/ directory.

As for any pre-built image usage, it is the image user's responsibility to ensure that any use of this image complies with any relevant licenses for all software contained within.

Docker Pull Command