MySQL在同一台机器上实现主从复制，多个mysql实例

何为MySQL主从分离

读写分离，基本的原理是让主数据库处理事务性增、改、删操作(INSERT、UPDATE、DELETE)，而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库

为什么需要主从分离

因为数据库的"写"(写10000条数据到oracle可能要3分钟)操作是比较耗时的，但是数据库的"读"(从oracle读10000条数据可能只要5秒钟)，所以读写分离，解决的是，数据库的写入，影响了查询的效率

什么时候使用主从分离

数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用，利用数据库主从同步 。可以减少数据库压力，提高性能。当然，数据库也有其它优化方案。memcache 或是 表折分，或是搜索引擎。都是解决方法

主从复制和读写分离

在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于前面我们学习过的rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份

环境准备

需要提前安装两个MySQL实例

主库配置

my.ini数据库配置文件

[Client]
port = 3306
 
[mysqld]
#设置3306端口
port = 3306
server-id = 1
# 设置mysql的安装目录
basedir=D:\DevTools\mysql-8.0.27-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\DevTools\mysql-8.0.27-winx64\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 二进制日志文件格式
log-bin=mysql-bin
# binlog日志格式
binlog_format=mixed

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4

启动第一个mysql实例

需要cmd管理员运行进行操作

# 开始 MySQL服务
net start mysql
# 关闭MySQL服务
net stop mysql

创建一个用户用于与从库同步的用户名和密码（这里我创建一个masterUser用户，密码为mysql），并给masterUser用户授权，以用于主库操作从库

# 创建从库账户
create user 'my3306'@'%' identified with mysql_native_password by 'my3306' ;
# 授权
grant replication slave on *.* to 'my3306'@'%' ;
# 刷新权限
flush privileges ;

启动实例

mysql -uroot -proot

从库配置

my.ini数据库配置文件

[Client]
port = 3307
 
[mysqld]
#设置3307端口
port = 3307
server-id = 2
# 设置mysql的安装目录
basedir=D:\DevTools\mysql-8.0.27-winx64-1
# 设置mysql数据库的数据的存放目录
datadir=D:\DevTools\mysql-8.0.27-winx64-1\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
log-bin=mysql-bin
relay-log=mysql-relay
binlog_format=mixed 
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4

启动第二个mysql实例

需要cmd管理员运行进行操作

# 开始 MySQL服务
net start mysql
# 关闭MySQL服务
net stop mysql
# 登录第二个mysql实例3307
mysql -uroot -proot -P3307;

配置连接主库

查看主库状态

show status ;

需要记住position,file；使用主库分配的授权用户连接主库

change master to master_host='127.0.0.1',master_user='my3306',master_password='my3306',
master_log_file='mysql-bin.000004',master_log_pos=156;

开始主从复制

start slave ;
show slave status  \G ;

需要保证 Slave_IO_Running: Yes；Slave_SQL_Running: Yes

来源

1. mysql 创建用户并授权访问部分数据库 / 主从同步设置
2. 解决MySQL的主从数据库没有同步的两种方法
3. mysql在同一台机器上实现主从复制,多个mysql(windows)
4. win10按照MySQL主从复制
5. mysql主从分离优缺点
6. 个人网站