mysql5.7-31 主从复制安装(windows)

发表于 | 分类于

准备两个MySQL服务实例

windows上可以用压缩版本，例如mysql-5.7.31-winx64.zip，解压文件夹再复制一份，添加my.ini配置文件。假设一个叫mysql-5.7.31-winx64，一个叫mysql-5.7.31-winx64-2，以为分别配置其为主和从

修改主mysql-5.7.31-winx64的my.ini

解压出来默认没有 my.ini 需要自己新建:

1
2
3
4
5
6
7
8
9
[mysqld]
basedir = ./
datadir = ./data
port = 3316
server_id = 16

sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log_bin=mysql-bin
binlog-format=Row

修改从mysql-5.7.31-winx64-2的my.ini

1
2
3
4
5
6
7
8
9
[mysqld]
basedir = ./
datadir = ./data
port = 3326
server_id = 26

sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log_bin=mysql-bin
binlog-format=Row

分别到相应目录执行初始化

如果本机已经安装过 3306的 mysql ,可以直接使用 mysqld命令 (需要环境变量)。--initialize-insecure 表示不给 root 设置密码,否则会生成一个随机的密码

1
./bin/mysqld.exe --defaults-file=my.ini --initialize-insecure

启动实例并连接

1
start ./bin/mysqld.exe

使用 powershell 分别连接到主库和从库:

1
2
mysql -u root  -h 127.0.0.1 -P3316
mysql -u root  -h 127.0.0.1 -P3326

在主库执行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.11 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.12 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.10 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      747 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

记录下 show master status 中 file 和 position 的值。

在从库执行

根据在主库设置的用户名、密码、file 、position 修改如下的指令：

1
2
3
4
5
6
7
CHANGE MASTER TO
    MASTER_HOST='localhost',  
    MASTER_PORT = 3316,
    MASTER_USER='repl',      
    MASTER_PASSWORD='123456',   
    MASTER_LOG_FILE='mysql-bin.000002',
    MASTER_LOG_POS=747;

执行 start slave来启动主从复制，通过 show slave status\G; 查看从库状态:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: localhost
                  Master_User: repl
                  Master_Port: 3316
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 747
               Relay_Log_File: hoppo-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
              
.................

重点关注如下两行的值，需要为Yes才表示配置成功了：

1
2
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

测试

在主库执行

1
2
3
4
5
6
7
8
9
10
11
mysql> create database db;
Query OK, 1 row affected (0.04 sec)

mysql> use db
Database changed

mysql> create table t1(id int);
Query OK, 0 rows affected (0.17 sec)

mysql> insert into t1(id) values(1),(2);
Query OK, 2 rows affected (0.04 sec)

在从库执行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use db;
Database changed
mysql> show tables;
+--------------+
| Tables_in_db |
+--------------+
| t1           |
+--------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

常用指令

可以通过 show master status\G, show slave status\G 查看状态
可以能改过 stop slave, start slave来停止复制。

reset slave all ,清除 slave 配置。

0%