WINDOWS mysql 主从同步
前提:
1.mysql 5.7.32
2.Navicat Premium 12
准备
MySQL的一些日志同步，数据的恢复有时是需要开启binlog（二进制日志）功能的，但是MySQL默认是不开启的，这个时候就需要我们手工开启
查看mysql 是否开启了二进制日志:
log_bin 为ON表示开启，为OFF表示未开启
log_bin_basename 表示二进制的文件保存路径
mysql> show variables like 'log_bin%';
+---------------------------------+------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------------------------------+
| log_bin | ON |
| log_bin_basename | C:Program FilesMYSQLmysql-5.7.32-winx64datamaster-bin |
| log_bin_index | C:Program FilesMYSQLmysql-5.7.32-winx64datamaster-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+------------------------------------------------------------------+
查看当前二进制日志文件状态:
mysql> show master status;
+-------------------+----------+--------------+------------------------------------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------------------------------------------------------------------+-------------------+
| master-bin.000002 | 2432 | master | mysql,information_schema,performance_schema,ds0,ds1,slave,sszt_base,sys,test | |
+-------------------+----------+--------------+------------------------------------------------------------------------------+-------------------+```
## 主库配置
#### 修改主库配置
在my.ini 文件中找到[mysqld]（#注释自行删除）
添加如下配置（需要同步的数据库有多少都可以写进去，主从同步会根据库名称找到对应的丛库去同步数据）
```text
server-id=1#主库和从库需要不一致
log-bin=master-bin
log_bin_index=master-bin.index
binlog-do-db=master#同步的数据库(需要同步的数据库名字,可以多个)
binlog-do-db=master2#同步的数据库(需要同步的数据库名字,可以多个)
binlog-ignore-db=mysql#不需要同步的数据库
binlog-ignore-db=mysql2#不需要同步的数据库
修改完成之后需要重启mysql服务
然后确认二进制日志文件开启 ON
为从库创建用户
将用户名与密码记住
create user slave;
grant replication slave on *.* to slave@'从库ip' identified by '密码';
flush privileges;
查看主库状态
mysql> show master status;
+-------------------+----------+--------------+------------------------------------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------------------------------------------------------------------+-------------------+
| master-bin.000002 | 2432 | master | mysql,information_schema,performance_schema,ds0,ds1,slave,sszt_base,sys,test | |
+-------------------+----------+--------------+------------------------------------------------------------------------------+-------------------+
**File名称 与 Position要记住从库需要用到 **
到此主库配置完毕
从库配置
修改从库配置
一样先修改my.ini文件
server-id=2#主库和从库需要不一致
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
replicate-do-db=master#需要同步的库
replicate-do-db=master2#需要同步的库
replicate-ignore-db=mysql#不需要同步的库
replicate-ignore-db=mysql2#不需要同步的库
然后重新启动
设置运行代码从库配置
change master to master_host='主库ip',master_port=端口,master_user='slave',master_password='密码',master_log_file='master-bin.000008',master_log_pos=120;
start slave;
**File名称 与 Position从主库查询 **
测试是否打通
mysql> show slave status;
+----------------------------------+---------------+-------------+-------------+---------------+-------------------+---------------------+------------------------+---------------+-----------------------+------------------+-------------------+-----------------+--------------------------------------------------------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-----------------------------------------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |
+----------------------------------+---------------+-------------+-------------+---------------+-------------------+---------------------+------------------------+---------------+-----------------------+------------------+-------------------+-----------------+--------------------------------------------------------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-----------------------------------------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Waiting for master to send event | 192.168.10.77 | slave | 3306 | 60 | master-bin.000002 | 2432 | slave-relay-bin.000002 | 321 | master-bin.000002 | Yes | Yes | master | mysql,information_schema,performance_schema,local,ll,sszt_base,sys | | | | | 0 | | 0 | 2432 | 528 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 | 541f334e-2fa7-11eb-9536-244bfe03354a | C:Program Filesdevmysqlmysql-5.7.32-winx64datamaster.info | 0 | NULL | Slave has read all relay log; waiting for more updates | 86400 | | | | | | | | 0 | | | |
+----------------------------------+---------------+-------------+-------------+---------------+-------------------+---------------------+------------------------+---------------+-----------------------+------------------+-------------------+-----------------+--------------------------------------------------------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-----------------------------------------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
找到
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
这两个必须是yes,由于太长可能不好看,所以可以复制在文本里查看;
至此配置完毕.
测验的话 在主库添加从库会同步添加.
解除主从同步
mysql> stop slave;
mysql> reset slave all;