mysql主从创建数据库

主从创建数据库"/>

mysql主从创建数据库

！！以下内容在mysql8.0+测试未通过

一、docker创建mysql主从数据库

1.进入docker compose目录，创建docker compose文件

mysql_master.yml

version: '3.7'

services:

mysql_master:

image: mysql:5.7.26

hostname: mysql_master

environment:

- TZ=Asia/Shanghai

- MYSQL_ROOT_PASSWORD=Mysql123

configs:

- source: mysql_master

target: /etc/mysql/conf.d/mysqlf

volumes:

- /docker/data/mysql_master:/var/lib/mysql

networks:

- cloud

ports:

- 3306:3306

configs:

mysql_master:

file: ./profiles/mysql_master/mysqlf

networks:

cloud:

external:

name: cloud

mysql_slave.yml

version: '3.7'

services:

mysql_slave:

image: mysql:5.7.26

hostname: mysql_slave

environment:

- TZ=Asia/Shanghai

- MYSQL_ROOT_PASSWORD=Mysql123

configs:

- source: mysql_slave

target: /etc/mysql/conf.d/mysqlf

volumes:

- /docker/data/mysql_slave:/var/lib/mysql

networks:

- cloud

ports:

- 13306:3306

configs:

mysql_slave:

file: ./profiles/mysql_slave/mysqlf

networks:

cloud:

external:

name: cloud

2.创建本地挂载数据目录

3.创建mysqlf文件

mysql master配置文件

[mysqld]

server-id=100 # server-id必须唯一

log-bin=mysql-bin

log-slave-updates=1 # 重要参数！

binlog-do-db={dbname} # 需要备份的数据库名，如果有多个，重复设置这个选项即可

主数据库可选配置项

binlog-ignore-db={dbname} # 不需要备份的数据库名，如果有多个，重复设置这个选项即可

slave-skip-errors=1 # 跳过错误，继续执行复制操作

expire_logs_days=30 # 二进制日志过期时间

max_binlog_size=500M # 二进制日志文件大小上限

mysql slave配置文件

[mysqld]

server-id=101 # server-id必须唯一

log-bin=mysql-slave-bin

relay_log=edu-mysql-relay-bin

4.发布

docker stack deploy -c mysql_master.yml --with-registry-auth cloud

docker stack deploy -c mysql_slave.yml --with-registry-auth cloud

二、建立主从复制

1.连接mysql主数据库，新建一个测试DB

2.mysql主数据库操作

1)进入mysql主数据库容器

docker exec -it {容器ID：mysql-master} /bin/bash # 进入master数据库容器

2)登录mysql主数据库，创建一个复制用户，具有replication slave 权限

CREATE USER 'slave'@'%' IDENTIFIED WITH ‘mysql_native_password’ BY 'Slave123';

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

FLUSH PRIVILEGES;

3)设置读锁

FLUSH TABLES WITH READ LOCK;

4)查看binlog日志文件名和偏移量

SHOW MASTER STATUS;

QQ截图20200806142850.png

6)备份需要同步的数据库

mysqldump -uroot -pMysql123 {dbname}>{dbname}.sql # 备份需要同步的数据库

7)解锁

UNLOCK TABLES;

3.mysql从数据库操作

1)将备份的数据库数据恢复到从数据库

cd /docker/data/mysql-slave/

cp ../mysql-master/{dbname}.sql .

docker exec -it {容器ID：mysql-slave} /bin/bash #进入slave数据库容器

CREATE DATABASE `{dbname}` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

USE {dbname}

SOURCE /var/lib/mysql/{dbname}.sql

2)设置从数据库

STOP SLAVE;

change master to

master_host='mysql-master',

master_user='slave',

master_password='Slave123',

master_port=3306,

master_log_file='mysql-bin.000003',

master_log_pos=76324,

master_connect_retry=30;

START SLAVE;

/** 查看状态，如果Slave_IO_Running和Slave_SQL_Running均为Yes则成功 **/

SHOW SLAVE STATUS;

QQ截图20200806144000.png