mysql主从复制2主一从_详解MySQL实现主从复制(一主一从)

MySQL实现主从复制

前言

windows 下的mysql主从复制

一、什么是主从复制？

将主数据库中的DDL和DML操作通过二进制日志(BINLOG)传输到从数据库上，然后将这些日志重新执行(重做)；从而使得从数据库的数据与主数据库保持一致。

二、主从复制的作用

1、主数据库出现问题，可以切换到从数据库。

2、可以进行数据库层面的读写分离，

3、可以在从数据库上进行日常备份

三、复制过程

第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。

第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。

第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

四、主机操作

首先按照常规方式按照一个mysql实例：

将下载后的mysql压缩包解压如下图：

在根目录下创建my_3306.ini配置文件，配置内容如下：

#设置3306端口

port=3306

#设置mysql的安装目录

basedir=D:\\dev\\mysql-8.0.20-winx64

#设置mysql数据库的数据的存放目录

datadir=D:\\dev\\mysql-8.0.20-winx64\\Data_3306

#允许最大连接数

max_connections=200

#允许连接失败的次数。

max_connect_errors=10

#服务端使用的字符集默认为utf8mb4

character-set-server=utf8mb4

#创建新表时将使用的默认存储引擎

default-storage-engine=INNODB

#默认使用“mysql_native_password”插件认证

#mysql_native_password

default_authentication_plugin=mysql_native_password

#服务器 id

server-id=3306

#二进制文件存放路径log_3306文件夹需要手动创建

log-bin=D:\\dev\\mysql-8.0.20-winx64\\log_3306\\mysql-bin

#待同步的数据库(根据需要修改)

binlog-do-db=testdb

#不需要备份的数据库 (根据需要修改)

#binlog-ignore-db=mysql

binlog-ignore-db = information_schema

#二进制文件记录格式

binlog_format = STATEMENT

[mysql]

#设置mysql客户端默认字符集

default-character-set=utf8mb4

[client]

#设置mysql客户端连接服务端时默认使用的端口

port=3306

default-character-set=utf8mb4

在根目录下创建install_3306.bat自动安装执行脚本，配置内容如下

cd /d %~dp0\bin

mysqld --defaults-file="D:\dev\mysql-8.0.20-winx64\my_3306.ini" --initialize-insecure --user=mysql --console

mysqld install mysql_3306 --defaults-file="D:\dev\mysql-8.0.20-winx64\my_3306.ini"

net start mysql_3306

pause

在根目录下创建remove_3306.bat自动卸载执行脚本，配置内容如下：

cd /d %~dp0\bin

net stop mysql_3306

mysqld -remove mysql_3306

pause

执行安装操作：

配置mysql环境变量，右键以管理员身份运行install_3306.bat，如需卸载请执行remove_3306.bat

如上图显示安装成功并启动成功，默认密码为空

执行以下操作

-- 切换到mysql数据库

use mysql;

-- 修改root用户的可连接的host %代表所有

UPDATE USER SET HOST='%' WHERE USER='root'; -h IP地址

-- 查看是否修改成功

SELECT HOST,USER FROM USER;

-- 刷新MySQL的系统权限相关表

FLUSH PRIVILEGES;

-- 修改root用户密码

ALTER USER 'root'@'%' IDENTIFIED BY 'root';

-- 在主机创建一个用户

create user 'slave'@'%' identified by 'slave';

-- 给创建的而用户赋予复制的权限

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

-- 展示主机状态

show master status;

执行完查看主机状态之后不要再操作以免数据变动

五、从机的具体操作

安装第二个mysql实例：

复制install_3306.bat,my_3306.ini,remove_3306.bat三个文件在同一目录，并分别改名为install_3307.bat,my_3307.ini,remove_3307.bat，并将三个3307文件中的所有3306改为3307,修改完成后按照上面的执行安装操作

my_3307.ini配置

[mysqld]

#设置3307端口

port=3307

#设置mysql的安装目录

basedir=D:\\dev\\mysql-8.0.20-winx64

#设置mysql数据库的数据的存放目录

datadir=D:\\dev\\mysql-8.0.20-winx64\\Data_3307

#允许最大连接数

max_connections=200

#允许连接失败的次数。

max_connect_errors=10

#服务端使用的字符集默认为utf8mb4

character-set-server=utf8mb4

#创建新表时将使用的默认存储引擎

default-storage-engine=INNODB

#默认使用“mysql_native_password”插件认证

#mysql_native_password

default_authentication_plugin=mysql_native_password

#服务器 id

server-id=3307

#开启二进制日志(从服务器不是必须要开启二进制日志log_3307文件夹需手动创建)

log-bin=D:\\dev\\mysql-8.0.20-winx64\\log_3307\\mysql-bin

#执行同步操作的数据库(根据需要修改)需要和master数据库要同步的数据库名称保持一致

replicate-do-db=testdb

#复制的数据库

binlog-ignore-db=information_schema

[mysql]

#设置mysql客户端默认字符集

default-character-set=utf8mb4

[client]

#设置mysql客户端连接服务端时默认使用的端口

port=3307

default-character-set=utf8mb4

执行安装.bat

安装成功并启动成功，默认密码为空

执行以下操作

-- 切换到mysql数据库

use mysql;

-- 修改root用户的可连接的host %代表所有

UPDATE USER SET HOST='%' WHERE USER='root'; -h IP地址

-- 查看是否修改成功

SELECT HOST,USER FROM USER;

-- 刷新MySQL的系统权限相关表

FLUSH PRIVILEGES;

-- 修改root用户密码

ALTER USER 'root'@'%' IDENTIFIED BY 'root';

-- 与主机连接

change master to master_host='主机ip地址',

master_port=主机数据库端口号,

master_user='主机分配的复制用户',

master_password='主机分配的复制用户的密码',

master_log_file='主机执行show master status 之后得到的值',

master_log_pos='主机执行show master status 之后得到的值';

-- 开启复制

start slave;

-- 查看状态

show slave status\G;

-- 当结果中的这两个值为yes则一主一从搭建完成

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

测试在主库创建表，插入数据；从库查看表，查看数据,数据相同则大功告成

与redis主从复制不同

mysql从接入点开始复制

redis从头开始复制

六、关闭主从复制

七、MySql Host is blocked because of many connection errors 解决方法

八、MySQL8.0中binlog的深入讲解