mysql部署_MySQL容器部署及数据持久化

运维经常要和数据库打交道，尤其是MySQL，以前的方式是在本机装个MySQL用于本地调试，但有了docker以后就不需要再去繁琐的安装MySQL啦(虽然安装MySQL也不是很麻烦)，直接在docker中启一个MySQL容器就可以了。

1. 安装docker服务

# 卸载老版本docker

[root@docker ~]# yum remove docker docker-client docker-client-latest docker-common docker-latest docker-latest-logrotate docker-logrotate docker-engine

[root@docker ~]# yum -y install epel-release wget

[root@docker ~]# wget -O /etc/yum.repos.d/docker-ce.repo https://mirrors.ustc.edu.cn/docker-ce/linux/centos/docker-ce.repo

[root@docker ~]# sed -i 's#download.docker.com#mirrors.tuna.tsinghua.edu.cn/docker-ce#g' /etc/yum.repos.d/docker-ce.repo

[root@docker ~]# yum -y install docker-ce

# 启动并配置镜像加速

[root@docker ~]# systemctl start docker.service && systemctl enable docker.service

[root@docker ~]# cat /etc/docker/daemon.json

{

"registry-mirrors": ["https://registry.docker-cn.com"]

}

[root@docker ~]# systemctl restart docker.service

2. 先决条件与配置文件

[root@docker ~]# docker pull mysql:5.7.30

[root@docker ~]# docker images

REPOSITORY TAG IMAGE ID CREATED SIZE

mysql 5.7.30 a4fdfd462add 10 hours ago 448MB

[root@docker ~]# mkdir -p /data/mysql/{conf,data}

[root@docker ~]# cat /data/mysql/conf/my.cnf

[mysqld]

server_id = 33060000

port = 3306

log_timestamps=SYSTEM

max_allowed_packet = 16M

read_only = 0

character_set_server = utf8mb4

secure_file_priv = ""

max_connect_errors = 100000

interactive_timeout = 1800

wait_timeout = 1800

# BINLOG

log_bin = mysql-bin

binlog_format = row

log_slave_updates = 1

max_binlog_size = 200M

relay_log = relay-bin

sync_binlog = 1

# GTID

gtid_mode = ON

enforce_gtid_consistency = 1

binlog_gtid_simple_recovery = 1

# ENGINE

default_storage_engine = InnoDB

innodb_flush_log_at_trx_commit=1

3. 启动MySQL容器

[root@docker ~]# docker run --name mysql_1 --hostname mysql_1 -v /data/mysql/conf/my.cnf:/etc/my.cnf -v /data/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 mysql:5.7.30

2020-05-21 14:53:02+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.30-1debian10 started.

2020-05-21 14:53:02+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'

2020-05-21 14:53:02+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.30-1debian10 started.

2020-05-21 14:53:03+00:00 [Note] [Entrypoint]: Initializing database files

...(略)

2020-05-21T14:53:11.392145-00:00 0 [Note] Event Scheduler: Loaded 0 events

2020-05-21T14:53:11.392418-00:00 0 [Note] mysqld: ready for connections.

Version: '5.7.30-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL)

新打开一个Linux终端窗口

[root@docker ~]# docker ps -a

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

441b3c464d09 mysql:5.7.30 "docker-entrypoint.s…" About a minute ago Up About a minute 0.0.0.0:3306->3306/tcp, 33060/tcp mysql_1

[root@docker ~]# docker exec -it mysql_1 bash

root@mysql_1:/# mysql -uroot -p

Enter password: 123456

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.30-log 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 |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

4. 测试数据持久化

mysql> create database db;

Query OK, 1 row affected (0.00 sec)

mysql> use db

Database changed

mysql> create table t1(id int,name varchar(32));

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1,'aa'),(2,'bb'),(3,'cc');

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from db.t1;

+------+------+

| id | name |

+------+------+

| 1 | aa |

| 2 | bb |

| 3 | cc |

+------+------+

3 rows in set (0.00 sec)

销毁当前MySQL容器

[root@docker ~]# docker stop $(docker ps -qa)

441b3c464d09

[root@docker ~]# docker rm $(docker ps -qa)

441b3c464d09

[root@docker ~]# ls /data/mysql/data/

auto.cnf ca.pem client-key.pem ib_buffer_pool ib_logfile0 mysql mysql-bin.000001 mysql-bin.000003 performance_schema public_key.pem server-key.pem

ca-key.pem client-cert.pem db ibdata1 ib_logfile1 mysql_1-slow.log mysql-bin.000002 mysql-bin.index private_key.pem server-cert.pem sys

再次创建MySQL容器

[root@docker ~]# docker run --name mysql_1 --hostname mysql_1 -v /data/mysql/conf/my.cnf:/etc/my.cnf -v /data/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 mysql:5.7.30

2020-05-21 14:59:49+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.30-1debian10 started.

2020-05-21 14:59:49+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'

2020-05-21 14:59:49+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.30-1debian10 started

...(略)

2020-05-21T14:59:50.114693-00:00 0 [Note] mysqld: ready for connections.

Version: '5.7.30-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL)

# 打开一个新Linux终端窗口执行

[root@docker ~]# docker exec -it mysql_1 bash

root@mysql_1:/# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.30-log 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> select * from db.t1;

+------+------+

| id | name |

+------+------+

| 1 | aa |

| 2 | bb |

| 3 | cc |

+------+------+

3 rows in set (0.01 sec)