MYSQL主从复制搭建

目录

一 复制概述

 二、复制原理

 三、复制优势

四、 使用docker完成1主1从

 1. master

1)新建服务器容器实例

 2)在/usr/local/mysql/mysql-docker/master/conf下新建my.cnf

3) 执行完毕之后,需要重启MySQL: 

4)进入mysql: 可以通过命令行show master status\G;查看当前binlog日志的信息(后面有用): 

 5) 创建同步数据的账户,并且进行授权操作:

  6)查看创建的是否有replication slave权限

 2. slave

1)新建服务器容器实例

 2)在/usr/local/mysql/mysql-docker/slave1/conf下新建my.cnf

3)在主数据库中查看同步状态

4)进入mysql-slave1实例

5)在从数据库中配置主从复制

 6)从数据库查看主从同步状态

 7)从数据库开启主从同步并再次查看主从同步状态

 8)测试主从同步

五、 使用docker完成2主2从

1 架构图

2 搭建步骤

1)docker创建容器实例

2)查看mysql实例是否创建成功

 3) 分别配置my.cnf文件

4)重启4个mysql实例

 5)进入master,创建用户并分配权限

5)进入master,查看master状态

 6)进入slave,在slave中进行主从复制

7)进入slave启动主从复制

8)查看主从复制状态

 9) 关闭从服务器的主从复制

10)master1,master2互相复制

 11)在master1,master2中开启主从,并查看状态

 12)开启slave1,slave2丛机主从

13)测试


一 复制概述

复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中,然后在从库上对这些日志重新执行,从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制, 从库同时也可以作为其他从服务器的主库,实现链状复制。

一、DML与DDL的含义:

1、DML(Data Manipulation Language)数据操作语言-数据库的基本操作,SQL中处理数据等操作统称为数据操纵语言,简而言之就是实现了基本的“增删改查”操作。包括的关键字有:select、update、delete、insert、merge

2、DDL(Data Definition Language)数据定义语言-用于定义和管理 SQL 数据库中的所有对象的语言,对数据库中的某些对象(例如,database,table)进行管理。包括的关键字有:

create、alter、drop、truncate、comment、grant、revoke

二、DML与DDL的区别:

1.DML操作是可以手动控制事务的开启、提交和回滚的。

2.DDL操作是隐性提交的,不能rollback!

 二、复制原理

MySQL的主从复制原理如下:

  • Master主库在事务提交时,会把数据变更记录在二进制日志文件 Binlog 中保存
  • 主库推送二进制日志文件中的日志事件到从库的中继日志 Relay Log
  • slave从库重新执行中继日志中的事件,将数据同步到自己的服务器

 三、复制优势

MySQL主从复制的优点主要包含以下三个方面:

  • 主库出现问题,可以快速切换到从库提供服务

  • 在从库上执行查询操作,在主库上执行更新操作,实现读写分离,降低主库的访问压力

  • 在从库上执行备份,以避免备份期间影响主库的服务

四、 使用docker完成1主1从

 1. master

1)新建服务器容器实例

docker run -p 3307:3306 --name mysql-master \
-v /usr/local/mysql/mysql-docker/master/log:/var/log/mysql \
-v /usr/local/mysql/mysql-docker/master/data:/var/lib/mysql \
-v /usr/local/mysql/mysql-docker/master/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=123456  \
-d mysql:5.7

 2)在/usr/local/mysql/mysql-docker/master/conf下新建my.cnf

[mysqld]

## 设置server_id,同一局域网中需要唯一

server_id=101 

## 指定不需要同步的数据库名称

binlog-ignore-db=mysql  

## 开启二进制日志功能

log-bin=mall-mysql-bin  

## 设置二进制日志使用内存大小(事务)

binlog_cache_size=1M  

## 设置使用的二进制日志格式(mixed,statement,row)

binlog_format=mixed  

## 二进制日志过期清理时间。默认值为0,表示不自动清理。

expire_logs_days=7  

## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。

## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致

slave_skip_errors=1062

3) 执行完毕之后,需要重启MySQL: 

docker restart mysql-master

4)进入mysql: 可以通过命令行show master status\G;查看当前binlog日志的信息(后面有用): 

docker exec -it mysq--master /bin/bash

mysql -uroot -p123456

show master status\G;

 

字段含义

File : 从哪个日志文件开始推送日志文件
Position : 从该日志文件的哪个位置开始推送日志
Binlog_Ignore_DB : 指定不需要同步的数据库

 5) 创建同步数据的账户,并且进行授权操作:

#授予ip地址为任意的root用户在所有数据库的所有表上主从复制的权限,%可以换为ip地址
grant replication slave on *.* to 'ydj1'@'%' identified by '123456';

#刷新权限列表
flush privileges;

#注意mysql8.0以上版本先创建用户在进行授权

        #创建用户

         CREATE USER 'ydj'@'%' IDENTIFIED BY '123456';

        #修改用户

        alter user 'ydj'@'%' identified with mysql_native_password by '123456';

        #授权

        grant REPLICATION SLAVE on *.* to 'ydj'@'%';

        #刷新权限列表
        flush privileges;

  6)查看创建的是否有replication slave权限

select user, repl_slave_priv from mysql.user;

 

也可以通过远程连接mysql工具navicat进行修改权限

 2. slave

使用docker完成从机搭建

1)新建服务器容器实例

docker run -p 3308:3306 --name mysql-slave1 \
-v /usr/local/mysql/mysql-docker/slave1/log:/var/log/mysql \
-v /usr/local/mysql/mysql-docker/slave1/data:/var/lib/mysql \
-v /usr/local/mysql/mysql-docker/slave1/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=123456  \
-d mysql:5.7

 2)在/usr/local/mysql/mysql-docker/slave1/conf下新建my.cnf

[mysqld]

## 设置server_id,同一局域网中需要唯一

server_id=102

## 指定不需要同步的数据库名称

binlog-ignore-db=mysql  

## 开启二进制日志功能,以备Slave作为其它数据库实例的Master时使用

log-bin=mall-mysqlbin  

## 设置二进制日志使用内存大小(事务)

binlog_cache_size=1M  

## 设置使用的二进制日志格式(mixed,statement,row)

binlog_format=mixed  

## 二进制日志过期清理时间。默认值为0,表示不自动清理。

expire_logs_days=7  

## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。

## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致

slave_skip_errors=1062  

## relay_log配置中继日志

relay_log=mall-mysql-relay-bin  

## log_slave_updates表示slave将复制事件写进自己的二进制日志

log_slave_updates=1  

## slave设置为只读(具有super权限的用户除外)

read_only=1

3)在主数据库中查看同步状态

4)进入mysql-slave1实例

docker exec -it mysql-slave1 /bin/bash

mysql -uroot -p123456

5)在从数据库中配置主从复制

change master to
master_host='192.168.80.130',master_user='ydj',master_password='123456',

master_port=3306,master_log_file='mysqlbin.000003',master_log_pos=1295,

master_connect_retry=30;

master_host:主数据库的IP地址;

master_port:主数据库的运行端口;

master_user:在主数据库创建的用于同步数据的用户账号;

master_password:在主数据库创建的用于同步数据的用户密码;

master_log_file:指定从数据库要复制数据的日志文件,通过查看主数据的状态,获取File参数;

master_log_pos:指定从数据库从哪个位置开始复制数据,通过查看主数据的状态,获取Position参数;

master_connect_retry:连接失败重试的时间间隔,单位为秒

 6)从数据库查看主从同步状态

show slave status \G;

 

 7)从数据库开启主从同步并再次查看主从同步状态

start slave;

show slave status \G;

 8)测试主从同步

向主数据插入数据,观察从数据库同步是否成功.

五、 使用docker完成2主2从

1 架构图

 

2 搭建步骤

1)docker创建容器实例

docker run -p 3307:3306 --name mysql-master1 \
-v /usr/local/mysql/mysql-docker/master1/log:/var/log/mysql \
-v /usr/local/mysql/mysql-docker/master1/data:/var/lib/mysql \
-v /usr/local/mysql/mysql-docker/master1/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=123456  \
-d mysql:5.7

docker run -p 3308:3306 --name mysql-slave1 \
-v /usr/local/mysql/mysql-docker/slave1/log:/var/log/mysql \
-v /usr/local/mysql/mysql-docker/slave1/data:/var/lib/mysql \
-v /usr/local/mysql/mysql-docker/slave1/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=123456  \
-d mysql:5.7

docker run -p 3309:3306 --name mysql-master2 \
-v /usr/local/mysql/mysql-docker/master2/log:/var/log/mysql \
-v /usr/local/mysql/mysql-docker/master2/data:/var/lib/mysql \
-v /usr/local/mysql/mysql-docker/master2/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=123456  \
-d mysql:5.7

docker run -p 3310:3306 --name mysql-slave2 \
-v /usr/local/mysql/mysql-docker/slave2/log:/var/log/mysql \
-v /usr/local/mysql/mysql-docker/slave2/data:/var/lib/mysql \
-v /usr/local/mysql/mysql-docker/slave2/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=123456  \
-d mysql:5.7

2)查看mysql实例是否创建成功

docker ps

 3) 分别配置my.cnf文件

masrer  另外一个master配置文件将server_id=103即可

[mysqld]

## 设置server_id,同一局域网中需要唯一

server_id=101 

## 指定不需要同步的数据库名称

binlog-ignore-db=mysql  

## 开启二进制日志功能

log-bin=mall-mysql-bin  

## 设置二进制日志使用内存大小(事务)

binlog_cache_size=1M  

## 设置使用的二进制日志格式(mixed,statement,row)

binlog_format=mixed  

## 二进制日志过期清理时间。默认值为0,表示不自动清理。

expire_logs_days=7  

## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。

## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致

slave_skip_errors=1062

## 在作为从数据库的时候, 有写入操作也要更新二进制日志文件

log-slave-updates

slave另外一个slave配置文件将server_id=104即可

[mysqld]

## 设置server_id,同一局域网中需要唯一

server_id=102

## 指定不需要同步的数据库名称

binlog-ignore-db=mysql  

## 开启二进制日志功能,以备Slave作为其它数据库实例的Master时使用

log-bin=mall-mysqlbin  

## 设置二进制日志使用内存大小(事务)

binlog_cache_size=1M  

## 设置使用的二进制日志格式(mixed,statement,row)

binlog_format=mixed  

## 二进制日志过期清理时间。默认值为0,表示不自动清理。

expire_logs_days=7  

## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。

## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致

slave_skip_errors=1062  

## relay_log配置中继日志

relay_log=mall-mysql-relay-bin  

## log_slave_updates表示slave将复制事件写进自己的二进制日志

log_slave_updates=1  

## slave设置为只读(具有super权限的用户除外)

read_only=1

4)重启4个mysql实例

docker restart mysql-master1  mysql-master2 mysql-slave1 mysql-slave2

 5)进入master,创建用户并分配权限

docker exec -it mysql-master1 /bin/bash

grant replication slave on *.* to 'ydj'@'%' identified by '123456';

docker exec -it mysql-master2 /bin/bash

grant replication slave on *.* to 'ydj'@'%' identified by '123456';

5)进入master,查看master状态

docker exec -it mysql-master1 /bin/bash

mysql -uroot -p123456

show master status \G;

docker exec -it mysql-master2 /bin/bash

mysql -uroot -p123456

show master status \G;

 

 

 6)进入slave,在slave中进行主从复制

docker exec -it mysql-slave1 /bin/bash

mysql -uroot -p123456

change master to
master_host='192.168.80.130',master_user='ydj',master_password='123456',

master_port=3307,master_log_file='mall-mysql-bin.000001',master_log_pos=154,

master_connect_retry=30;

 

docker exec -it mysql-slave2 /bin/bash

mysql -uroot -p123456

change master to
master_host='192.168.80.130',master_user='ydj',master_password='123456',

master_port=3309,master_log_file='mall-mysql-bin.000001',master_log_pos=436,

master_connect_retry=30;

 

7)进入slave启动主从复制

start slave;

8)查看主从复制状态

show slave status \G;

 

注意:如果主从复制失败,或一直连接不上的情况下先在丛机执行

stop slave;

reset slave;

在重复第6步

若还是不行就需要去排查问题;

 9) 关闭从服务器的主从复制

stop slave;

reset slave;

10)master1,master2互相复制

master1中

change master to
master_host='192.168.80.130',master_user='ydj',master_password='123456',

master_port=3309,master_log_file='mall-mysql-bin.000001',master_log_pos=436,

master_connect_retry=30;

master2中

change master to
master_host='192.168.80.130',master_user='ydj',master_password='123456',

master_port=3307,master_log_file='mall-mysql-bin.000001',master_log_pos=154,

master_connect_retry=30;

 11)在master1,master2中开启主从,并查看状态

master1

start slave;

show slave status;

master2

start slave;

show master status;

 

 12)开启slave1,slave2丛机主从

步骤如6步

13)测试

 

  • 0
    点赞
  • 19
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
MySQL主从复制是指将一个MySQL数据库的变化同步到另一个MySQL数据库上,从而实现数据的备份、负载均衡、读写分离等功能。下面我们来介绍一下MySQL主从复制搭建过程。 1. 配置主服务器 首先需要在主服务器上配置一些参数。打开MySQL配置文件my.cnf,在[mysqld]下添加以下内容: ``` server-id=1 #服务器唯一ID log-bin=mysql-bin #启用二进制日志 binlog-do-db=testdb #指定需要复制的数据库名 ``` 然后重启MySQL服务。 2. 配置从服务器 接着需要在从服务器上配置一些参数。打开MySQL配置文件my.cnf,在[mysqld]下添加以下内容: ``` server-id=2 #服务器唯一ID,要保证与主服务器不同 ``` 然后重启MySQL服务。 3. 创建复制用户并授权 在主服务器上创建一个用于复制的用户,并授权给从服务器使用。在MySQL命令行中输入以下命令: ``` CREATE USER 'replication'@'%' IDENTIFIED BY 'password'; #创建用户 GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%'; #授权 ``` 其中,'password'是复制用户的密码。 4. 获取主服务器状态信息 在主服务器上执行以下命令,获取主服务器的状态信息: ``` SHOW MASTER STATUS; ``` 会显示出如下信息: ``` +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 120 | testdb | | | +------------------+----------+--------------+------------------+-------------------+ ``` 其中,File列和Position列的值会在后面的步骤中用到。 5. 配置从服务器复制信息 在从服务器上执行以下命令,配置从服务器的复制信息: ``` CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', #主服务器的File值 MASTER_LOG_POS=120; #主服务器的Position值 ``` 其中,master_ip是主服务器的IP地址,'password'是复制用户的密码,mysql-bin.000001和120分别是主服务器的File值和Position值。 6. 开启从服务器复制 在从服务器上执行以下命令,开启从服务器的复制: ``` START SLAVE; ``` 执行成功后,可以使用以下命令查看从服务器的状态: ``` SHOW SLAVE STATUS\G; ``` 如果看到“Slave_IO_Running”和“Slave_SQL_Running”两项都为“YES”,则说明从服务器复制已经正常启动。 至此,MySQL主从复制搭建完成。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值