docker 安装 mysql和mysql集群和分库分表

下载镜像文件

docker pull mysql:5.7

创建实例并启动

docker run -p 3306:3306 --name mysql \
-v /mydata/mysql/log:/var/log/mysql \
-v /mydata/mysql/data:/var/lib/mysql \
-v /mydata/mysql/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7

MySQL 配置

vi /mydata/mysql/conf/my.cnf
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8' 
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve

通过容器的 mysql 命令行工具连接

docker exec -it mysql mysql -uroot -proot

设置 root 远程访问

grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
flush privileges;

进入容器文件系统

docker exec -it mysql /bin/bash

集群

master 节点

docker 安装

docker run -p 3307:3306 --name mysql-master \
-v /mydata/mysql/master/log:/var/log/mysql \
-v /mydata/mysql/master/data:/var/lib/mysql \
-v /mydata/mysql/master/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
--restart=always \
-d mysql:5.7

配置

vim /mydata/mysql/master/conf/my.cnf
#以下配置写入到my.cnf中
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8' 
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve

添加master主从复制部分配置

#添加master主从复制部分配置
server_id=1
log-bin=mysql-bin
read-only=0
#哪些库产生二进制 gulimall_ums,gulimall_pms......
binlog-do-db=gulimall_ums
binlog-do-db=gulimall_pms
binlog-do-db=gulimall_oms
binlog-do-db=gulimall_sms
binlog-do-db=gulimall_wms
#哪些库忽略,mysql系统库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

然后重启master节点

slavert 节点

docker 安装

docker run -p 3317:3306 --name mysql-slaver-01 \
-v /mydata/mysql/slaver/log:/var/log/mysql \
-v /mydata/mysql/slaver/data:/var/lib/mysql \
-v /mydata/mysql/slaver/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
--restart=always \
-d mysql:5.7

配置

vim
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8' 
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve

添加slaver子节点从主关系部分配置

#添加slaver主从复制部分配置
server_id=2
log-bin=mysql-bin
read-only=1
#哪些库产生二进制
binlog-do-db=gulimall_ums
binlog-do-db=gulimall_pms
binlog-do-db=gulimall_oms
binlog-do-db=gulimall_sms
binlog-do-db=gulimall_wms
#哪些库忽略,mysql系统库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

然后重启slaver节点

master节点授权一个用户可以访问主节点进行日志复制

执行sql

在master的mysql中，写入以下sql语句：授权用户：bank，密码：123456 的账号具备复制功能

GRANT REPLICATION SLAVE ON *.* TO 'bankup'@'%' IDENTIFIED BY '123456'

查看状态

SHOW MASTER STATUS

会出现如下：

FILE	POSITION	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
mysql-bin.000001	154	gulimall_ums,gulimall_pms,gulimall_oms,gulimall_sms,gulimall_wms

slaver节点设置主库连接

执行sql，同步哪个master节点

其中以下的参数从sql：SHOW MASTER STATUS中获取

CHANGE MASTER TO MASTER_HOST='192.168.182.130',
MASTER_USER='backup',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0,
MASTER_PORT=3307

开始启动主从集群

START SLAVE

查看状态

SHOW SLAVE STATUS

报错解决

当执行sql ：SHOW SLAVE STATUS有以下报错

ERROR CONNECTING TO MASTER ‘USER@MYSQL1:3306‘ - RETRY-TIME: 60 RETRIES: 86400

可能是防火墙的问题，关闭防火墙，并且重启mysql的主从或者docker容器

分库分表

方式1：自增主键

在my.cnf中加入以下配置：

#从几开始增长
auto_increment_offset: 1
#每次步长 
auto_increment_increment: 2

方式2：ShardingSphere

https://shardingsphere.apache.org/document/current/cn/overview/

ShardingSphere-JDBC

ShardingSphere-Proxy （一般使用这个方法）