windows 安装mysql主从复制

1. MySQL主库设置

修改主库的my.ini文件

[mysqld]

#Path to installation directory. All paths are usually resolved relative to this.
basedir="D:/databases/mysql-8.0.27-winx64-master"

#Path to the database root
datadir="D:/databases/mysql-8.0.27-winx64-master/data"

# The TCP/IP Port the MySQL Server will listen on
port=3306

log-bin=mysql-bin

server-id=1

#同步的数据库
binlog-do-db=snow
#不参与同步的数据库 ，例如mysql
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

# ignore the auth
#skip-grant-tables

shared-memory

注：

• 主库server-id与从库server-id不能一样，如果在同一台机器端口也不能一样
• log-bin=mysql-bin  (开启binlog日志，日志的名字开头是mysql-bin)
• binlog-do-db (后面跟的是需要同步的数据库，多个数据库就多写几个)
• binlog-ignore-db (后面跟的是不需要同步的数据库)

2. MySQL从库设置

修改从库的my.ini文件

[mysqld]

#Path to installation directory. All paths are usually resolved relative to this.
basedir="D:/databases/mysql-8.0.27-winx64-slave"

#Path to the database root
datadir="D:/databases/mysql-8.0.27-winx64-slave/data"

# The TCP/IP Port the MySQL Server will listen on
port=3307

log-bin=mysql-bin

server-id=2

#主从复制的数据库
replicate_do_db=snow
#不参与主从复制的数据库，例如mysql
replicate_ignore_db=mysql
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=sys

# ignore the auth
# skip-grant-tables

注：

• 从库配置文件里的replicate-do-db是需要同步的数据库

3. 注册及设置数据库

管理员CMD模式到MySQL主库 bin目录下，运行如下命令进行安装及用户设置：

mysqld --install mysql-master --defaults-file="D:\databases\mysql-8.0.27-winx64-master\my.ini"
net start mysql-master
mysql -u root -p
# 输入密码，进入mysql
mysql> set password=password('123456');

管理员CMD模式到MySQL从库 bin目录下，运行：

mysqld --install mysql-slave --defaults-file="D:\databases\mysql-8.0.27-winx64-slave\my.ini"
net start mysql-slave
# following opertion is optional
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY'123456';
# 启动MySQL服务的时候跳过权限表认证
mysqld --console --skip-grant-tables --shared-memory
mysql -u root -P3307 -p

win+r  输入services.msc找mysql的服务就会看到两个服务：

4. 设置主库

create database 'snow' character set utf8 collate utf8_bin;

CREATE USER master_user IDENTIFIED BY '123456';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'master_user'@'127.0.0.1';
GRANT ALL PRIVILEGES ON *.* TO 'master_user'@'127.0.0.1';
FLUSH PRIVILEGES;

SELECT User FROM mysql.user;

# 需要记录查询出来的File和position在设置从库的时候会用到
show master status;

5. 设置从库

stop slave;

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