搭建 MySQL 主从复制

搭建 MySQL 主从复制

概述：为了方便起见使用docker搭建两个mysql

1.docker的安装

# 首先安装 Docker
yum -y install docker

# 然后启动 Docker 服务
service docker start

# 测试安装是否成功
docker -v

2.配置MySQL容器

(1)docker run --name mysql1 -p 33061:3306 -e MYSQL_ROOT_PASSWORD=123 -d mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

(2)docker run --name mysql2 -p 33062:3306 -e MYSQL_ROOT_PASSWORD=123 -d mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

3.通过数据库连接工具分配用户

在mysql1中执行

GRANT REPLICATION SLAVE ON  *.* TO 'rep1'@'%' IDENTIFIED BY '123'

4.配置主机mysql1

(1)docker exec -it mysql1 /bin/bash
#修改配置文件
(2)cat /etc/mysql/mysql.conf.d/mysqld.cnf 

(3)vi mysqld.cnf
# 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]
##########添加的配置选项###########
log-bin=/var/lib/mysql/binlog
server-id=1
binlog-do-db=zhuantaidb #要备份的数据库
######################
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

(4)docker cp ./mysqld.cnf mysql1:/etc/mysql/mysql.conf.d/
(5)docker restart mysql1

5.在SQLyog中查看是否配置成功

SHOW MASTER STATUS

6.配置从机mysql2

(1)docker exec -it mysql2 /bin/bash

(2)cat /etc/mysql/mysql.conf.d/mysqld.cnf 

# 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]
##################
server-id=2
##################
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

(3)docker cp ./mysql.cnf mysql2:/etc/mysql/mysql.conf.d/
(4)docker restart mysql2
(5)docker exec -it mysql2 /bin/bash
(6)mysql -u root -p
(7)change master to master_host='182.92.169.xxx',master_port=33061,master_user='rep1',master_password='123',master_log_file='binlog.000001',master_log_pos=154;
(8)show slave status\G

查看是否配置成功：

7.测试数据库是否复制成功

在通过SQLyog在mysql1中创建一个名为zhuantaidb的数据库

在mysql2中刷新浏览器对象查看是否成功复制

复制成功，万事大吉！