准备

我是在CentOS上使用docker部署,因此需要提前准备与docker相关的操作.

docker

CentOS安装

配置

映射

一个主机对多个虚拟机,所以需要使用不同的具名配置文件夹,比如/etc/mysql/my.cnf.d/master01来做不同mysql-docker实例的启动项的配置区分.除此之外还有数据文档配置的区分:/var/lib/mysql/master01

mysql-docker一般默认的数据及启动项配置分别对应/var/lib/mysql和/etc/my.cnf.d,对应起来就是如下关系:

/etc/mysql/my.cnf.d/master01:/etc/my.cnf.d
/var/lib/mysql/master01:/var/lib/mysql

可能涉及创建文件夹

mkdir -p /etc/mysql/my.cnf.d/master01 /var/lib/mysql/master01
# 权限设置要严格,否则有些配置文件,比如my.cnf会失效
chmod 645 -R /etc/mysql/my.cnf.d/master01
chmod 777 -R /var/lib/mysql/master01

端口

默认是3306,我映射为6306

镜像

根据情况,这里搭建PXC(Percona XtraDB Cluster)集群,那么选择percona:5.7.23

权限

这里指的是root用户的初始密码如何定义和设置的问题,可以有三种选择,三选一:

1. 无密码: MYSQL_ALLOW_EMPTY_PASSWORD=true

2. 指定密码: MYSQL_ROOT_PASSWORD=XXX

3. 随机密码: MYSQL_RANDOM_ROOT_PASSWORD=true.

   不推荐! 因为这意味着需要自己重置密码,并且需要预先在my.cnf的[mysqld]下配置skip-grant-tables,自己定义密码后还需要重启,比较麻烦.

启动

主节点

创建容器

以上准备工作大致勾勒出了容器创建命令:

docker create --name percona-master01 -v /var/lib/mysql/master01:/var/lib/mysql -v /etc/mysql/my.cnf.d/master01:/etc/my.cnf.d -p 6306:3306 -e MYSQL_ROOT_PASSWORD=master01 percona:5.7.23

my.cnf

要做集群,首先至少需要配置如下几项:

1. 开启log-bin
2. 设置server-id

所以在对应目录/etc/mysql/my.cnf.d/master01下创建my.cnf并写入:

[mysqld]
log-bin=mysql-bin
server-id=1
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

这里的sql_mode是为了解决可能出现的1055错误的,与是否配置集群无关.

提供一下初始化的便捷脚本:

cat << EOF > /etc/mysql/my.cnf.d/master01/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
EOF

启动镜像

运行master01并查看日志:

docker start percona-master01  && docker logs -f percona-master01

验证

等待启动完成后,可以先在宿主机上查看相应的配置是否生效,比如log-bin:

ll /var/lib/mysql/master01 | grep mysql-bin

或者直接登录验证,可以连接宿主机端口,也可以连接docker虚拟机,不过需要先查询它的IP.

1. 宿主机

# 不能省略-h127.0.0.1,否则会认为是localhost而拒绝登录
mysql -h127.0.0.1 -P6306 -uroot -pmaster01 -Dmysql

1. 虚拟机

docker exec -it percona-master01 mysql -uroot -pmaster01 -Dmysql 

授权

此时服务端就搭建好了,也连接上了,还需要做些初始化操作,比如创建用户pxc并赋予权限.

可以在服务端,也可以在客户端连接并操作.无论是服务端还是客户端,实际的sql操作自然不变.

以创建集群用户,赋予权限和查看集群状态的核心sql为例:

use mysql;
create user 'pxc'@'%' identified by 'pxc';
grant replication slave on *.* to 'pxc'@'%';
flush privileges;

重启后执行:

# 查看集群状态
show master status;
# 查看二进制日志相关的配置项
show global variables like 'binlog%';
# 查看server相关的配置项
show global variables like 'server%';

小结

不论怎么连,只要是命令就可以使用shell脚本一条一条实现比如创建用户

# -D数据库名 -e需要执行的SQL脚本
mysql -h127.0.0.1 -P6306 -uroot -pmaster01 -Dmysql -e"create user 'pxc'@'%' identified by 'pxc';"

从节点

与主节点的配置大致相同,这里只列出关键配置的区别.

权限设置要严格,否则有些配置文件,比如my.cnf会失效

bash-4.2$ mysql -uroot -p
mysql: [Warning] World-writable config file '/etc/my.cnf.d/my.cnf' is ignored.
mysql: [Warning] World-writable config file '/etc/mysql/my.cnf' is ignored.

mkdir -p /etc/mysql/my.cnf.d/slave01 /var/lib/mysql/slave01
chmod 644 -R /etc/mysql/my.cnf.d 
chmod g+w,o+w -R /var/lib/mysql

my.cnf

仍然采用相同的思路在/etc/mysql/my.cnf.d/slave01下创建my.cnf并写入:

[mysqld]
# 服务ID,不与master重复即可
server-id=2
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

docker create --privileged --name percona-slave01 -v /var/lib/mysql/slave01:/var/lib/mysql -v /etc/mysql/my.cnf.d/slave01:/etc/my.cnf.d -p 6307:3306 -e MYSQL_ROOT_PASSWORD=slave01 percona:5.7.23

docker start percona-slave01  && docker logs -f percona-slave01

mysql -h0.0.0.0 -P6307 -uroot -pslave01 -Dmysql

查看主节点(端口是6306)的二进制日志(bin-log)信息:

mysql -h0.0.0.0 -P6306 -uroot -pmaster01 -Dmysql -e'show master status;'

输出如下:

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

关联主节点

CHANGE MASTER TO
master_host='192.168.0.122',
master_user='pxc',
master_password='pxc',
master_port=6306,
master_log_file='mysql-bin.000001',
master_log_pos=154;
start slave;
show slave status;

启动从节点并查看从节点状态

start slave;
show slave status;

master02和slave02搭建的命令瀑布

这里是记录下自己完整的操作过程,不要直接执行.

#!/bin/bash###### master02 ###### mkdir -p /etc/mysql/my.cnf.d/master02 /var/lib/mysql/master02
chmod 645 -R /etc/mysql/my.cnf.d/master02
chmod 777 -R /var/lib/mysql/master02docker create --name percona-master02 -v /var/lib/mysql/master02:/var/lib/mysql -v /etc/mysql/my.cnf.d/master02:/etc/my.cnf.d -p 7306:3306 -e MYSQL_ROOT_PASSWORD=master02 percona:5.7.23cat << EOF > /etc/mysql/my.cnf.d/master02/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=10
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
EOFdocker start percona-master02 && docker logs -f percona-master02docker exec percona-master02 mysql -uroot -pmaster02 -e"show variables like 'log_bin'"
docker exec percona-master02 mysql -uroot -pmaster02 -e"show master status;"
docker exec percona-master02 mysql -uroot -pmaster02 -e"show global variables like 'server%';"docker exec percona-master02 mysql -uroot -pmaster02 -Dmysql -e"create user 'pxc'@'%' identified by 'pxc';"
docker exec percona-master02 mysql -uroot -pmaster02 -Dmysql -e"grant replication slave on *.* to 'pxc'@'%';"
docker exec percona-master02 mysql -uroot -pmaster02 -Dmysql -e"flush privileges;"###### slave02 ######
mkdir -p /etc/mysql/my.cnf.d/slave02 /var/lib/mysql/slave02
chmod 645 -R /etc/mysql/my.cnf.d/slave02
chmod 777 -R /var/lib/mysql/slave02docker create --name percona-slave02 -v /var/lib/mysql/slave02:/var/lib/mysql -v /etc/mysql/my.cnf.d/slave02:/etc/my.cnf.d -p 7307:3306 -e MYSQL_ROOT_PASSWORD=slave02 percona:5.7.23cat << EOF > /etc/mysql/my.cnf.d/slave02/my.cnf
[mysqld]
server-id=12
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
EOFdocker start percona-slave02docker exec percona-slave02 mysql -uroot -pslave02 -e"CHANGE MASTER TO master_host='192.168.0.122', master_user='pxc', master_password='pxc', master_port=7306, master_log_file='mysql-bin.000003', master_log_pos=154;start slave;"
docker exec percona-slave02 mysql -uroot -pslave02 -e"start slave;"
docker exec percona-slave02 mysql -uroot -pslave02 -e"show slave status;"

参考

1. mysql shell脚本
2. Percona 数据库