mysql-主从同步配置-基于windows

读写分离

提高服务的稳健性，使用双机热备功能，主数据库提供事务性增删改功能，其它从数据库提供读（查询功能），避免因为数据过大而造成性能问题

主从的实现原理

• binglog：在主数据库服务器开启binlog日志功能，记录当前数据库的增删改操作
• 主从复制：从数据库服务器实时监控主数据库的binlog的日志变化，读取binlog日志，并同步到本身服务器
• 读写分离：将insert/upadte/delete 操作路由到主数据库，select操作路由到从数据库

mysql主从配置

主服务

my.ini配置

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8 
[mysqld]
#设置3306端口
port = 3307 
# 设置mysql的安装目录
basedir=D:\\pro\\mysql-5.7.32-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\\pro\\mysql-5.7.32-winx64\\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

## 主服务的配置，需要添加的配置
# 服务标识为1
server-id=1
# 启动MySQL二进制日志，即数据同步语句，从数据库会一条一条的执行这些语句
log_bin=master-bin
# 指定二进制索引文件的路径与名称
log_bin_index=master-bin.index
# MIXED 混合模式，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也是在statement和row之间选择一种
# ROW 日志中会记录成每一行数据修改的形式，然后在slave端再对相同的数据进行修改 产生大量的日志内容
# STATEMENT 记录执行语句,已经语句执行的一些上下文（来保证在两端的执行结果相同），日志量少，节约IO，从库应用日志块，但是一些新功能同步可能会有障碍,比如函数、触发器
binlog_format=ROW
# 指定会记录操作日志的数据库
binlog_do_db=demo_goods
# 指定不记录二进制日志的数据库
binlog_ignore_db=information_schema
binlog_ignore_db=sys
binlog_ignore_db=performance_schema
binlog_ignore_db=mysql
# binlog_do_db 与 binlog_ignore_db 两者只能在启动命令行中或配置文件中加入。指定多个数据库要分行写入

账户配置

#进入mysql数据库
mysql -uroot -p

#创建从数据库的masterbackup用户和权限
mysql> grant replication slave on *.* to masterbackup@'192.1.1.%' identified by 'password';
#备注
# 192.1.1.%通配符，表示0-255的IP都可访问，最好配置指定ip
# %，则任何ip均可访问
# 这里也可以设置为账户grant replication slave on *.* to masterbackup@'%' identified by 'password';

#退出mysql
mysql> exit;

重启服务

# net stop mysql， net start mysql
D:\pro\mysql-5.7.32-winx64\bin>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

D:\pro\mysql-5.7.32-winx64\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

查看状态

mysql> show master status;
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
| master-bin.000002 |      452 | demo_goods   | information_schema,sys,performance_schema,mysql |                   |
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

从服务

my.ini配置

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8 
[mysqld]
#设置3307端口, 因为这里两个mysql装在一台机子上，所以端口做区分,不与主服务3306冲突
port = 3307 
# 设置mysql的安装目录
basedir=D:\\pro\\mysql-5.7.32-winx64_slaver
# 设置mysql数据库的数据的存放目录
datadir=D:\\pro\\mysql-5.7.32-winx64_slaver\\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

## 从服务的配置，需要添加的配置
# 服务标识为2
server-id=2
# 启动MySQL二进制日志，可以用来做数据备份和崩溃恢复，或主服务器挂掉了，将此从服务器作为其他从服务器的主服务器
relay-log=slave-relay-bin
# 文件位置
relay-log-index=slave-relay-bin.index
# 指定同步的数据库，可以用来过滤出指定的数据库。
replicate-do-db=demo_goods
#replicate-ignore-db

安装配置

# 因为再同一个机子上安装了两个mysql，所以安装配置时候需要修改位置从服务名字
mysqld --install MySQLSlaver --defaults-file="D:\pro\mysql-5.7.32-winx64_slaver\my.ini"

重启服务

D:\pro\mysql-5.7.32-winx64\bin>net start mysqlslaver
MySQLSlaver 服务正在启动 .
MySQLSlaver 服务已经启动成功。

连接master主服务器

#进入mysql数据库,此处推荐使用数据库连接工具直接操作
mysql -h localhost -P 3307 -uroot -p

# 清除之前的主从配置
mysql> stop slave;
mysql> RESET SLAVE;

#连接master主服务器
mysql> change master to master_host='localhost',master_port=3306,master_user='masterbackup',master_password='pasword',master_log_file='master-bin.000002',master_log_pos=154;
# master_host对应主服务器的IP地址。
# master_port对应主服务器的端口。
# master_log_file对应show master status显示的File列：master-bin.000002。
# master_log_pos对应show master status显示的Position列：154

启动数据同步

#启动slave数据同步
mysql> start slave;

#停止slave数据同步（若有需要）
mysql> stop slave;

查看状态

# Slave_IO_Running和Slave_SQL_Running都为yes，则表示同步成功
show slave status;