mysql主从复制(在虚拟机centos的docker下)

1.安装docker

Docker安装(CentOS)+简单使用-CSDN博客

2.部署2个mysql

docker run --name some-mysql1 -p 33061:3306  -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

docker run --name some-mysql2 -p 33062:3306  -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

3.试试连接的情况

window下连接到虚拟机中的数据库

4.修改主从数据库的配置

主：

# Copyright (c) 2015, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
log-bin = /var/lib/mysql/binlog
# 同一局域网中需要唯一
server-id = 1
# 需要同步的数据库
binlog-do-db = xl_game3

pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
#log-error      = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

以下几个需要修改的（保存文件为 mysqld.cnf）

bin = /var/lib/mysql/binlog
server-id = 1
binlog-do-db = xl_game3

写好后执行复制到docker的命令：

docker cp ./mysqld.cnf some-mysql1:etc/mysql/mysql.conf.d/

从：

# Copyright (c) 2014, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server-id = 2

以下几个需要修改的（保存文件为 mysqld.cnf）
server-id = 2

写好后执行复制到docker的命令：

docker cp ./mysqld.cnf some-mysql1:etc/mysql/mysql.conf.d/

5.重启2个主从数据库

docker restart some-mysql1

docker restart some-mysql2

6.配置从数据库

1.查看主数据库的信息 show master status

2.进入到从数据库后执行

change master to master_host='192.168.2.77',master_port=33061,master_user='root',master_password='123456',master_log_file='binlog.000006',master_log_pos=1338;

START SLAVE;

show slave STATUS;

记得替换 master_log_file 和 master_log_pos

3.在从数据库执行下查看

其中：Slave_IO_Running 与 Slave_SQL_Running都是Yes则说明主从配置成功！

4.大功告成来点语句测下

create database if not exists xl_game3;

use xl_game3;

-- 玩家用户数据
DROP TABLE IF EXISTS `player_data`;
CREATE TABLE IF NOT EXISTS `player_data` (
  `uid` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'uid',
  `account` varchar(500) NOT NULL COMMENT '账号',
  `password` varchar(500) COMMENT '密码',
  `player_status` varchar(10) DEFAULT "0" COMMENT '状态 0:正常 1黑名单',
  `data` mediumtext CHARACTER SET utf8mb4 COMMENT '数据',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `account` (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='玩家用户数据';

在主数据库执行