docker搭建基于percona-xtradb-cluster方案的mysql集群

一、部署环境

序号 hostname ip 备注
1 manager107 10.0.3.107 centos7;3.10.0-957.1.3.el7.x86_64
2 worker68 10.0.3.68 centos7;3.10.0-957.1.3.el7.x86_64
3 worker80 10.0.3.80 centos7;3.10.0-957.1.3.el7.x86_64

 

 

 

 

 

 docker 版本

docker version
Client:
 Version:           18.09.0
 API version:       1.39
 Go version:        go1.10.4
 Git commit:        4d60db4
 Built:             Wed Nov  7 00:48:22 2018
 OS/Arch:           linux/amd64
 Experimental:      false

Server: Docker Engine - Community
 Engine:
  Version:          18.09.0
  API version:      1.39 (minimum version 1.12)
  Go version:       go1.10.4
  Git commit:       4d60db4
  Built:            Wed Nov  7 00:19:08 2018
  OS/Arch:          linux/amd64
  Experimental:     false

二、安装docker(略)

三、创建swarm集群

选择一台服务器作为manager,比如:manager107,在manager107上执行:

docker swarm init

将加入口令,比如:

docker swarm join --token SWMTKN-1-06on43srtyq7pzogivo8hvja2lvew7zey9wpdf9cy1h8ju18nc-4l3ic9ur29h8yb30ntl0f2v6x 10.0.3.107:2377

分别拷到worker68和worker80上执行,即可创建swarm集群。

查看集群节点,在manager107上执行:

docker node ls

结果类似:

ID                            HOSTNAME            STATUS              AVAILABILITY        MANAGER STATUS      ENGINE VERSION
2uuuareav7n9phpfhmpnfscml *   manager107          Ready               Active              Leader              18.09.0
w0val5pdhhnhgwzuvbtb2qys9     worker68            Ready               Active                                  18.09.0
h4bcpsgw2q9r46qbgbd8erbx7     worker80            Ready               Active                                  18.09.0

四、创建一个overlay网络用于mysql集群之间通信

docker network create -d overlay --attachable pxc-net

查看network:

docker network ls
NETWORK ID          NAME                DRIVER              SCOPE
846a843726ca        bridge              bridge              local
35ca60480ba7        docker_gwbridge     bridge              local
fd148d0596c6        host                host                local
8f8ohxutlvam        ingress             overlay             swarm
bd8bdb46dd54        none                null                local
qlvzoa8gmi3k        pxc-net             overlay             swarm

五、拉取percona-xtradb-cluster镜像,并改名

docker pull docker.io/percona/percona-xtradb-cluster

修改tag

docker tag percona/percona-xtradb-cluster pxc

六、分别在三台服务器上创建数据卷和备份卷

在manager107上执行:

[root@manager107 ~]# docker volume create v1
[root@manager107 ~]# docker volume create backup

在worker68上执行:

[root@worker68 ~]# docker volume create v2
[root@worker68 ~]# docker volume create backup

在worker80上执行:

[root@worker80 ~]# docker volume create v3
[root@worker80 ~]# docker volume create backup

七、创建基于percona-xtradb-cluster集群的mysql容器

在manager107上执行:

[root@manager107 ~]# docker run -d -p 3310:3306 -e MYSQL_ROOT_PASSWORD=123456 -e CLUSTER_NAME=PXC -e XTRABACKUP_PASSWORD=123456 -v v1:/var/lib/mysql -v backup:/data --privileged --name=node1 --net=pxc-net pxc

在worker68上执行:

[root@worker68 ~]# docker run -d -p 3310:3306 -e MYSQL_ROOT_PASSWORD=123456 -e CLUSTER_NAME=PXC -e XTRABACKUP_PASSWORD=123456 -e CLUSTER_JOIN=node1 -v v2:/var/lib/mysql -v backup:/data --privileged --name=node2 --net=pxc-net pxc

在worker80上执行:

[root@worker80 ~]# docker run -d -p 3310:3306 -e MYSQL_ROOT_PASSWORD=123456 -e CLUSTER_NAME=PXC -e XTRABACKUP_PASSWORD=123456 -e CLUSTER_JOIN=node1 -v v3:/var/lib/mysql -v backup:/data --privileged --name=node3 --net=pxc-net pxc

说明:

  • CLUSTER_NAME:集群名称
  • XTRABACKUP_PASSWORD:同步密码
  • CLUSTER_JOIN:加入的集群节点

好了,现在可以随便连接一台服务器的3310接口,访问mysql,对该数据库的任何操作都可以实时同步到另外两台。

八、可能出现的问题

容器node1在manager107上正常运行,容器node2或者node3无法启动

进入manager107上的node1容器中的mysql

docker exec -it node1 bash

查看mysql日志

more /var/lib/mysql/innobackup.backup.log

可能出现错误(用户没有RELOAD和PROCESS权限)

Error: failed to execute query LOCK TABLES FOR BACKUP: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
Error: failed to execute query SHOW ENGINE INNODB STATUS: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

然后进入mysql

mysql -uroot -p

查看是否有“xtrabackup”和“sstuser”两个用户

use mysql;
select * from user;

没有的话,先创建这两个用户,有的话直接授权

创建用户:

create user 'xtrabackup'@'localhost' identified by '123456';
create user 'sstuser'@'localhost' identified by '123456';

或者查看权限(以“xtrabackup”为例):

select user,host,Reload_priv,Process_priv from user where user='xtrabackup';

授权:

grant reload, process,create tablespace,lock tables ,replication client,super on *.* to 'xtrabackup'@localhost;

刷新权限:

flush privileges;

 

posted @ 2019-01-04 15:33  dowi  阅读(3166)  评论(1编辑  收藏  举报