mysql kingshard 扩容_kingshard实践01-构建mysql主从复制

kingshard简介

kingshard是一个由Go开发高性能MySQL Proxy项目，kingshard在满足基本的读写分离的功能上，致力于简化MySQL分库分表操作；能够让DBA通过kingshard轻松平滑地实现MySQL数据库扩容。 详情查看https://github.com/flike/kingshard

基于docker构建主从数据库

1. 在自己喜欢的目录创建下mysql文件夹

cd mysql

mkdir master

mkdir slave

2. master下创建my.cnf和Dockerfile构建主数据库

Dockerfile

FROM mysql:5.7

COPY my.cnf /etc/mysql/mysql.conf.d/mysqld.cnf

EXPOSE 3306

CMD ["mysqld"]

my.cnf

[mysqld]

## 设置server_id，一般设置为IP，注意要唯一

server_id=1

bind-address=0.0.0.0

## 复制过滤：也就是指定哪个数据库不用同步(mysql库一般不同步)

## binlog-ignore-db=mysql

## 主从复制的格式(mixed,statement,row，默认格式是statement)

binlog_format=row

## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。

expire_logs_days=30

max_binlog_size=100M

gtid_mode=ON

enforce-gtid-consistency=ON

binlog-checksum=CRC32

master-verify-checksum=1

## 开启二进制日志功能，可以随便取，最好有含义(关键就是这里了)

log-bin=/var/lib/mysql/mysql-bin

log-bin_index=/var/lib/mysql/mysql-bin.index

log-slave-updates=ON

生成主数据库镜像

docker build -t master5.7 .

3. slave下创建my.cnf和Dockerfile构建从数据库

Dockerfile

FROM mysql:5.7

COPY my.cnf /etc/mysql/mysql.conf.d/mysqld.cnf

EXPOSE 3306

CMD ["mysqld"]

my.cnf

[mysqld]

## 设置server_id，一般设置为IP，注意要唯一

server_id=2

gtid_mode=ON

enforce-gtid-consistency=true

relay-log=/var/lib/mysql/mysql-relay

relay-log-index=/var/lib/mysql/mysql-relay.index

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=16

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

生成从数据库镜像

docker build -t slave5.7 .

4. mysql目录下创建docker-compose.yml，使用docker-compose定义和运行容器

docker-compose.yml

version: '3'

services:

mysql-master:

image: master5.7

container_name: master_compose5.7

ports:

- "3307:3306"

volumes:

- /Users/xiaohu/tools/docker/mysql5.7/master/data:/var/lib/mysql

environment:

MYSQL_ROOT_PASSWORD: "kingshard"

mysql-slave:

image: slave5.7

container_name: slave_compose5.7

ports:

- "3308:3306"

volumes:

- /Users/xiaohu/tools/docker/mysql5.7/slave/data:/var/lib/mysql

environment:

MYSQL_ROOT_PASSWORD: "kingshard"

执行docker-compose up运行容器

mysql5.7 docker-compose up

Creating network "mysql57_default" with the default driver

Creating slave_compose5.7 ... done

Creating master_compose5.7 ... done

Attaching to slave_compose5.7, master_compose5.7

slave_compose5.7 | 2019-03-04 07:12:03+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.30-1debian10 started.

master_compose5.7 | 2019-03-04 07:12:03+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.30-1debian10 started.

master_compose5.7 | 2019-03-04 07:12:03+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'

slave_compose5.7 | 2019-03-04 07:12:03+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'

master_compose5.7 | 2019-03-04 07:12:03+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.30-1debian10 started.

···

测试数据库

主数据库：mysql -h127.0.0.1 -uroot -pkingshard -P3307

从数据库：mysql -h127.0.0.1 -uroot -pkingshard -P3308

关联主从数据库

主数据库创建用户

CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';

grant replication slave on *.* TO 'repl'@'%';

flush privileges;

从数据库进行关联

CHANGE MASTER TO MASTER_HOST='master_compose5.7', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_AUTO_POSITION=1;

start slave

查看是否关联成功

mysql> show slave status \G

...

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Slave_IO_Running: Yes和Slave_SQL_Running: Yes都为YES即关联成功

测试主从数据

主数据库创建库跟表：

mysql> create database ms;

Query OK, 1 row affected (0.02 sec)

mysql> use ms;

Database changed

mysql> CREATE TABLE `user`(

-> `id` int(11) NOT NULL,

-> `age` int(11) NOT NULL,

-> `name` VARCHAR(100) DEFAULT NULL,

-> PRIMARY KEY ( `id` )

-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.04 sec)

mysql> show tables;

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

| Tables_in_ms |

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

| user |

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

1 row in set (0.01 sec)

mysql> insert into user(id, age, name) values(1,20,'tom');

Query OK, 1 row affected (0.01 sec)

从数据查看数据

mysql> use ms;

Database changed

mysql> show tables;

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

| Tables_in_ms |

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

| user |

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

1 row in set (0.00 sec)

mysql> select * from user;

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

| id | age | name |

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

| 1 | 20 | tom |

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

1 row in set (0.00 sec)