【服务器】Docker部署MySQL和Clickhouse

安装mysql

安装前配置

mkdir /docker/mysql  #创建mysql目录
cd /docker/mysql
mkdir config data log conf.d	#创建配置目录
cd config
vim my.conf

my.conf文件内容

[mysqld]
user=mysql
character-set-server=utf8
default_authentication_plugin=mysql_native_password
secure_file_priv=/var/lib/mysql
expire_logs_days=7
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
max_connections=1000

log-bin=mysql-bin.log #binlog日志名称前缀
binlog_format=ROW
server-id=1

gtid-mode=on
enforce-gtid-consistency=1 #设置为主从强一致性 
log-slave-updates=1 #记录日志

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

安装

docker pull mysql:latest

docker run \
--restart=always \
--privileged=true \
-p 3306:3306 --name mysql \
-v /docker/mysql/log:/var/log/mysql \
-v /docker/mysql/data:/var/lib/mysql \
-v /docker/mysql/config/my.cnf:/etc/mysql/my.cnf \
-v /docker/mysql/conf.d:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD="mysql@root" \
-d mysql

安装clickhouse

mkdir /docker/clickhouse

docker pull yandex/clickhouse-server

docker run -d --name clickhouse-server --ulimit nofile=262144:262144 -v /docker/clickhouse:/var/lib/clickhouse yandex/clickhouse-server

测试

mysql操作

mysql> CREATE DATABASE db;
mysql> CREATE TABLE db.test (a INT PRIMARY KEY, b INT);
mysql> INSERT INTO db.test VALUES (1, 11), (2, 22);
mysql> DELETE FROM db.test WHERE a=1;
mysql> ALTER TABLE db.test ADD COLUMN c VARCHAR(16);
mysql> UPDATE db.test SET c='Wow!', b=222;
mysql> SELECT * FROM test;

clickhouse操作

CREATE DATABASE mysql ENGINE = MaterializeMySQL('ip:3306', 'db', 'root', 'mysql@root'); #docker需要宿主ip

常见问题

问题：
Code: 1002. DB::Exception: Received from localhost:9000. DB::Exception: The replication sender thread cannot start in AUTO_POSITION mode: this server has GTID_MODE = OFF instead of ON…
**A：**修改MySQL GTID模式

问题：
**Code: 1000. DB::Exception: Received from localhost:9000. DB::Exception: mysqlxx::ConnectionFailed: Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2) ((nullptr):0). **
Code: 1000. DB::Exception: Received from localhost:9000. DB::Exception: mysqlxx::ConnectionFailed: Can’t connect to MySQL server on ‘127.0.0.1’ (115) ((nullptr):0).
A：需要使用宿主IP

问题：
**Code: 336. DB::Exception: Received from localhost:9000. DB::Exception: MaterializeMySQL is an experimental database engine. Enable allow_experimental_database_materialize_mysql to use it… **
A：clickhouse需要开启mysql物化引擎
clickhouse-client运行

SET allow_experimental_database_materialize_mysql = 1;