MySQL主从实现

2022-10-10 21:26:57

环境：MySQL8.0.17、Navicat12、win10

主从架构:
异步:效率有延迟高，安全性低，

主:可读可写，(dump thread)
从:可读不可写 (sql thread, i/o thread)
主从复制的原理:
主可读可写，从可读不可写(read only)
当有数据增删改的时候主库中会写入二进制日志，从的io线程会读取主的二进制日志，主的dump发送数据给从然后存储到中继日志中，从的sql线程会读取中中继日志进行数据同步
主从的同步机制是异步的，数据的同步有一定延迟，也就是说有可能会造成数据的丢失
其他主从原理相关信息查看

https://blog.csdn.net/qq_39570637/article/details/81664410

这里搭建的主从是最简单的单一主从复制（即一个Master和一个slave，没有任何的热机备份和多slave），该主从结构的基本拓扑图如下所示：

MySQL主从实现

建立基本的主从复制可以总结为以下三个基本步骤：

  1. 配置master服务器
  2. 配置slave服务器
  3. 将slave连接到master

在配置完成之后，我们会做一个简单的测试，测试复制是否正常。

实现过程

第一步---主库（Master）配置

先讲一主一从的搭建

首先解压出两个mysql来，按端口重命名，端口分别为3310和3311，3310端口的为主库，端口3311为从库。我们先来配置主库3310。

MySQL主从实现

我的mysql是免安装版的，如果你也是，需要手动配置my.ini和创建data文件夹

我们先来创建data文件夹，cmd（管理员模式）进入当前mysql的bin目录下，输入

mysqld --initialize-insecure --user=mysql

等待一会，可以看见data文件夹生成，接下来设置数据库密码，使用mysql -u root进入mysql，接着输入

alter user root@localhost identified by ‘你的密码‘;

密码即设置成功。

MySQL主从实现

再在根目录下新建一个my.ini文件

配置如下

[mysqld]
# 服务器id（不可重复）
server-id=1

# 输入自己对应的文件夹即可，如tmp文件夹不存在就自己新建一个
basedir = D:/mysql-8.0.17-winx64-3310
datadir = D:/mysql-8.0.17-winx64-3310/data
tmpdir = D:/mysql-8.0.17-winx64-3310/tmp

# 端口设置为3310
port = 3310
log-bin=master-bin
log-bin-index=master-bin.index

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# 因为MySQL8默认的认证插件是caching_sha2_password，很多客户端都不支持，可将默认的认证插件修改为
# mysql_native_password，在配置文件中配置
# `default_authentication_plugin=mysql_native_password`。

default_authentication_plugin = mysql_native_password

[client]
port = 3310
#default-character-set = utf8

完成后保存，接着进入bin目录下，输入mysqld --console启动数据库

mysqld --console

MySQL主从实现

启动成功后如图，主库启动后打开navicate，新建一个连接，选择mysql，配置如下

MySQL主从实现

在主数据库下创建一个用户

create user slaver1

MySQL主从实现

密码设置为slaver1pwd，插件按上图选择。服务器权限选择如下，然后保存。

MySQL主从实现

到此主库创建完毕，主库控制台下使用FLUSH PRIVILEGES;命令（关键）。
FLUSH PRIVILEGES;刷新MySQL的系统权限相关表,否则会出现拒绝访问

FLUSH PRIVILEGES;

第二步---从库（Slave）配置

回到3311文件夹下，生成data文件夹和配置密码我不再重复，如有需要往上翻

MySQL主从实现

配置my.ini文件

[mysqld]
server-id=2

# 如果tmp文件夹没用就自己创建
basedir = D:/mysql-8.0.17-winx64-3311
datadir = D:/mysql-8.0.17-winx64-3311/data
tmpdir = D:/mysql-8.0.17-winx64-3311/tmp
port = 3311
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[client]
port = 3311
#default-character-set = utf8

完成后在bin目录下使用mysqld --console启动数据库

再在navicat上创建从库

MySQL主从实现

现在虽然配置好了Master和Slave。但是Master和Slave之间没有任何的联系，这时如果在Slave的控制台执行：show slave status;会出现提示：it is not a slave

第三步---Slave连接到Master

将Slave连接到Master,需要知道Master的四个基本的信息：

（1） 主机名或者IP地址，由于这里都是单机，主机的IP为127.0.0.1

（2） Master使用的端口号，3310

（3） Master上具有REPLICATION SLAVE权限的用户 (slaver1)

（4） 该账号的密码。(slaver1pwd)

我们在配置Master的时候已经创建了一个具有相关权限的用户，在Slave的控制台运行以下命令：

CHANGE MASTER TO Master_host = ‘127.0.0.1‘,
Master_port = 3310,
Master_user = ‘slaver1‘,
Master_Password = ‘slaver1pwd‘;

执行完之后，可以通过start Slave命令启动Slave。

这时如果执行show slave status;会输出以下类似内容（如果Slave_IO_Running和Slave_SQL_Running不是YES请直接看最下面的错误解决）：

MySQL主从实现

现在，一个基本的mysql主从已经搭建起来了。我们接着做一些简单的测试，看看复制是否正常。测试步骤如下：

第四步---测试同步

1.　　在Master上创建表并插入数据：**

MySQL主从实现

create database test1 charset=utf8;

use test1

CREATE TABLE `t_user` (
	`id` BIGINT ( 11 ) NOT NULL AUTO_INCREMENT,
	`user_name` VARCHAR ( 255 ) DEFAULT NULL COMMENT ‘用户名‘,
	`user_phone` VARCHAR ( 255 ) DEFAULT NULL COMMENT ‘手机号‘,
	`user_email` VARCHAR ( 255 ) DEFAULT NULL COMMENT ‘邮箱地址‘,
	`user_pwd` VARCHAR ( 32 ) DEFAULT NULL COMMENT ‘加盐后用户密码‘,
	`user_sex` TINYINT ( 4 ) DEFAULT NULL COMMENT ‘性别‘,
	`create_time` TIMESTAMP NULL DEFAULT NULL COMMENT ‘创建时间‘,
	`modify_time` TIMESTAMP NULL DEFAULT NULL COMMENT ‘最后修改时间‘,
	`is_delete` TINYINT ( 4 ) DEFAULT NULL COMMENT ‘是否删除，0-未删除；1-已删
	除‘,
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 11 DEFAULT CHARSET = utf8 ROW_FORMAT = DYNAMIC COMMENT = ‘用户表‘;

INSERT INTO `t_user`
VALUES
	( ‘1‘, ‘dcliu0‘, ‘1825659520‘, ‘ldc1230@163.co
	m‘, NULL, NULL, NULL, NULL, NULL );
INSERT INTO `t_user`
VALUES
	( ‘2‘, ‘dcliu1‘, ‘1821251232‘, ‘ldc1230@163.co
	m‘, NULL, NULL, NULL, NULL, NULL );
INSERT INTO `t_user`
VALUES
	( ‘3‘, ‘dcliu2‘, ‘1124659580‘, ‘ldc1230@163.co
	m‘, NULL, NULL, NULL, NULL, NULL );
INSERT INTO `t_user`
VALUES
	( ‘4‘, ‘dcliu3‘, ‘1634476243‘, ‘ldc1230@163.co
	m‘, NULL, NULL, NULL, NULL, NULL );

2.　　进入Slave查看是否同步正常

MySQL主从实现

发现同步上了，此时基本的主从已经配置成功了。

搭建多个从库步骤是一样的，就不在此说明了，祝大家学业有成。

出现错误总结

1、Slave_IO_Running：NO

如果出现Slave_IO_Running连接不上的情况（NO或者Connecting），如下图：

MySQL主从实现

原因：用户名或密码不正确、slaver1的权限不够！
如果正确的执行：GRANT FILE,SELECT,REPLICATION SLAVE ON . TO ‘slaver1‘@‘127.0.0.1‘ IDENTIFIED BY ‘slaver1pwd‘; 仍然显示SQL I/O Running:NO，可能是Master的用户管理中Slaver用户未选中REPLICATION Client （用Navicat－Manager User ->Slaver1 的右边可选列表 ）

如果显示connecting的话是因为mysql8默认使用插件caching_sha2_password,有些client连接报这个错误，需要拿到server的public key来加密password。也可以直接修改加密方式为native。这里我们采用第二种方法。

此时修改主机加密方式即可。

MySQL主从实现

重启slave,在slave中进行如下操作

STOP SLAVE

START SLAVE

SHOW SLAVE STATUS

发现IO解决了，SQL变成了no。

MySQL主从实现

2、Slave_SQL_Running：NO

原因：

1.程序可能在slave上进行了写操作

2.也可能是slave机器重起后，事务回滚造成的.

一般是事务回滚造成的：

解决办法：

mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;

解决办法二、手工同步（推荐）

首先停掉Slave服务：stop slave
到主服务器上查看主机状态：
记录File和Position对应的值

进入master，执行show master status;

show master status;

输出如下，我们只要两个关键内容

MySQL主从实现

记录下来，我们回到slave控制台，执行

CHANGE MASTER TO master_host = ‘127.0.0.1‘,
master_user = ‘slaver1‘,
master_password = ‘slaver1pwd‘,
master_port = 3310,
master_log_file = ‘master-bin.000005‘,
master_log_pos = 440;

问题解决了，再次start slave启动并show slave status 查看状态。

MySQL主从实现

回到上面的测试环节。

附加一条

如果还有问题可能是数据库的uuid重复了,可以改一下,尽量不要相同。

位置在data文件夹下面，如图
MySQL主从实现

MySQL主从实现

MySQL主从实现

上一篇：Oracle常用脚本


下一篇：C++操作mysql方法总结（1)[转载]