我搭建的环境是Windows 10 + MySQL Community Server 8.0.23,
首先从MySQL的官方网站(https://dev.mysql.com/downloads/mysql/)下载zip包并解压,
我这里是解压到E:\MySQL,然后创建主从库的数据目录E:\MySQL\data_master和E:\MySQL\data_slave。
主库设置:
创建主库配置文件:E:\MySQL\my_master.cnf,内容如下:
[mysqld]
port=3306
user=mysql
datadir=E:\\MySQL\\data_master
server-id=111
log-bin=mysql-bin
初始化数据库:
E:\MySQL\bin\mysqld.exe -I --console --datadir=E:\MySQL\data_master
最后一行显示的密码 xxx_master 需要记下:
[Server] A temporary password is generated for root@localhost: xxx_master
Windows 下可以创建一个服务,来启动主库服务器进程:
E:\MySQL\bin\mysqld.exe install MySQL_Master --defaults-file=E:\MySQL\my_master.cnf
进入主库并修改密码为123456:
E:\MySQL\bin\mysql.exe -uroot -pxxx_master
> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
> QUIT
创建用于主从同步的用户:
E:\MySQL\bin\mysql.exe -uroot -p123456
> CREATE USER 'repl'@'localhost' IDENTIFIED WITH mysql_native_password BY '654321';
> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost' WITH GRANT OPTION;
> FLUSH PRIVILEGES;
显示主库状态,记下 File 和 Position 字段的值:
> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 3476 | | | |
+------------------+----------+--------------+------------------+-------------------+
从库设置:
创建从库配置文件:E:\MySQL\my_slave.cnf,内容如下:
[mysqld]
port=3307
user=mysql
datadir=E:\\MySQL\\data_slave
server-id=222
log-bin=mysql-bin
初始化数据库:
E:\MySQL\bin\mysqld.exe -I --console --datadir=E:\MySQL\data_slave
最后显示的密码 xxx_slave 需要记下:
[Server] A temporary password is generated for root@localhost: xxx_slave
********* 需要确保以下两个配置文件中主从库的 UUID 不一样 *********
E:\MySQL\data_master\auto.cnf
E:\MySQL\data_slave\auto.cnf
Windows 下可以创建一个服务,来启动从库服务器进程:
E:\MySQL\bin\mysqld.exe install MySQL_Slave --defaults-file=E:\MySQL\my_slave.cnf
进入从库并修改密码为123456:
E:\MySQL\bin\mysql.exe -P3007 -uroot -pxxx_slave
> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
> QUIT
修改主库配置,进行同步,其中 SOURCE_LOG_FILE 和 SOURCE_LOG_POS 对应了上面获取主库状态时的值:
E:\MySQL\bin\mysql.exe -P3307 -uroot -p123456
> CHANGE REPLICATION SOURCE TO SOURCE_HOST='localhost',SOURCE_PORT=3306,SOURCE_USER='repl',SOURCE_PASSWORD='654321',SOURCE_LOG_FILE='mysql-bin.000001',SOURCE_LOG_POS=3476;
> SHOW WARNINGS;
开始同步:
> START REPLICA;
> SHOW WARNINGS;
显示从库状态,如果 Slave_IO_Running 和 Slave_SQL_Running 都是 Yes,则表示同步成功,否则需要查看 Last_Error、Last_IO_Error 和 Last_SQL_Error 显示的错误:
> SHOW SLAVE STATUS\G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
显示正在同步的从库列表:
返回主库,查看 processlist,应该可以看到有一个正在 Binlog Dump 的项:
E:\MySQL\bin\mysql.exe -uroot -p123456
> SHOW PROCESSLIST;
+----+-----------------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 2262 | Waiting on empty queue | NULL |
| 9 | root | localhost:57772 | NULL | Query | 0 | init | show processlist |
| 32 | root | localhost:59779 | NULL | Sleep | 802 | | NULL |
| 42 | repl | localhost:60929 | NULL | Binlog Dump | 21 | Master has sent all binlog to slave; waiting for more updates | NULL |
+----+-----------------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
查看从库列表:
> SHOW REPLICAS;
+-----------+------+------+-----------+--------------------------------------+
| Server_Id | Host | Port | Source_Id | Replica_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 222 | | 3307 | 111 | 471390de-6ea8-11eb-aecf-002381507d33 |
+-----------+------+------+-----------+--------------------------------------+
其中这里的 Host 是空的,如果要显示出来,需要在从库 E:\MySQL\my_slave.cnf 配置上增加一行,并重启从库服务器进程:
report-host=123.123.123.123
这里的report-host可以随便填,可以用来区别不同的从库。
重启从库服务器进程后,主库的从库列表中的 Host 字段就会显示对应的名称:
> SHOW REPLICAS;
+-----------+-----------------+------+-----------+--------------------------------------+
| Server_Id | Host | Port | Source_Id | Replica_UUID |
+-----------+-----------------+------+-----------+--------------------------------------+
| 222 | 123.123.123.123 | 3307 | 111 | 471390de-6ea8-11eb-aecf-002381507d33 |
+-----------+-----------------+------+-----------+--------------------------------------+