MySQL主从实现

环境: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)[转载]