mysql 主从复制提示server_ids一样 mysql主从复制的实现需要

MySQL实现主从复制

文章目录

• MySQL实现主从复制
• 前言
• 一、什么是主从复制？
• 二、主从复制的作用
• 三、复制过程
• 四、主机操作
• 五、从机的具体操作
• 六、关闭主从复制
• 七、MySql Host is blocked because of many connection errors 解决方法
• 八、MySQL8.0中binlog的深入讲解

---

前言

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实例：

1. 将下载后的mysql压缩包解压如下图：
2. 在根目录下创建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

3. 在根目录下创建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

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

cd /d %~dp0\bin
net stop mysql_3306
mysqld -remove mysql_3306
pause

5. 执行安装操作：
   配置mysql环境变量，右键以管理员身份运行install_3306.bat，如需卸载请执行remove_3306.bat
   如上图显示安装成功并启动成功，默认密码为空
   执行以下操作

--连接数据库
mysql -h 地址 -P 端口 -u 账号 -p 密码（为空）
-- 切换到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;

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

五、从机的具体操作

1. 安装第二个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

2. 执行安装.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

3. 测试在主库创建表，插入数据；从库查看表，查看数据,数据相同则大功告成
4. 与redis主从复制不同
   mysql从接入点开始复制
   redis从头开始复制

六、关闭主从复制

详情链接: https://www.cnblogs.com/musen/p/11162783.html

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

详情链接: https://www.cnblogs.com/wclwcw/p/7645298.html

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

详情链接:https://www.jb51.net/article/197042.htm