一、环境
window环境下,两台电脑,ip: 192.168.163.128(主库), 192.168.163.100(从库)。mysql服务器版本:mysql-5.7.15-winx64
二、操作步骤
1.128和100电脑安装mysql服务器时,修改my-default.ini 配置环境
主库:
log-bin=mysql-bin
binlog_format=mixed
server-id=128
从库:
log-bin=mysql-bin
binlog_format=mixed
server-id=100
2.正常安装两个mysql服务器
3.在主库中给从库分配账户
在192.168.163.128电脑中执行操作:
cmd 登录 mysql 执行:
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.95.12' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
4.查看192.168.163.128MySQL服务器二进制文件名与位置
mysql>SHOW MASTER STATUS;
5.告知二进制文件名与位置
在192.168.163.100中执行:
mysql>CHANGE MASTER TO
>MASTER_HOST=’192.168.163.128’,
>MASTER_USER=‘slave’,
>MASTER_PASSWORD=’123456’,
>MASTER_LOG_FILE=’mysql-bin.000001’,
>MASTER_LOG_POS=1415;
6.在192.168.163.100中开启复制:
mysql> START SLAVE; #开启复制
mysql>SHOW SLAVE STATUS\G #查看主从复制是否配置成功
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.163.128
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1415
Relay_Log_File: DESKTOP-RP5649O-relay-bin.000002
Relay_Log_Pos: 1283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1415
Relay_Log_Space: 1500
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 128
Master_UUID: 4923ecdd-0a26-11e8-b560-708bcd9fe08d
Master_Info_File: D:\mysql-5.7.15-winx64\data\master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
检查Exec_Master_Log_Pos 和Relay_Master_Log_File 值是否一致。
然后配置成功,接下来可以检验是否主从复制成功。
主主复制的机制和主从复制的机制相同,只是配置稍微有点差异
server-id=11 #任意自然数n,只要保证两台MySQL主机不重复就可以了。
log-bin=mysql-bin #开启二进制日志
auto_increment_increment=2 #步进值auto_imcrement。一般有n台主MySQL就填n(新增)
auto_increment_offset=1 #起始值。一般填第n台主MySQL。此时为第一台主MySQL(新增)
binlog-ignore=mysql #忽略mysql库【我一般都不写】
binlog-ignore=information_schema #忽略information_schema库【我一般都不写】
replicate-do-db=aa #要同步的数据库,默认所有库
交叉配置,第一个库作为第二个库的主库,第二个库作为第一个库的主库