配置分为两部分,主服务器部分,从服务器部分
本次测试的两台机器的主从同步,环境是windows(linux同理)
主服务器ip:192.168.1.199
从服务器ip:192.168.1.198
1)主服务器端的配置
找到MySQL的配置文件my.ini文件
在[mysqld]内开启日志功能
log-bin=mylog-bin名字可以任意取
server-id=199本次去的ip最后三个数字
此时重启主服务器数据库服务
net stop mysql停止mysql服务
net start mysql开启mysql服务
然后进入数据库
执行如下命令
mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'admin';//此处必须大写,
mysql>show master status;//查看主服务器日志文件状态
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 439 | | | |
+------------------+----------+--------------+------------------+-------------------+
从服务器需要使用Position的值
到这里,主服务器的配置就算完成了。
2)从服务器的配置
同样需要修改my.ini中的值
在[mysqld]下修改
server-id=198
从服务器的日志文件不是必须开启,如果配置的是双向同步的话,此出的log-bin需要配置
重启从服务器的myql服务
登录从服务器,执行以下命令
mysql>change master to master_host='192.168.145.199',master_user='mysync',master_password='admin',master_log_file='mysql-bin.000001',master_log_pos=439;
mysql>show slave status\G;//检查从服务器复制功能的状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.199 //主服务器地址
Master_User: mysync //主服务器创建的同步帐号
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001 //同步的日志文件
Read_Master_Log_Pos: 935
Relay_Log_File: PC05-relay.000003
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //此处必须是yes
Slave_SQL_Running: Yes //此处必须是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: 935
Relay_Log_Space: 1184
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: 199
Master_UUID: 516cb325-a148-11e9-b3df-02004c4f4f50
Master_Info_File: C:\ProgramData\MySQL\MySQL Server 5.7\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:
1 row in set (0.00 sec)
到这里从服务器的配置也完成了,剩下就是创建数据库和表进行测试了。
3)测试
主服务器创建数据库
mysql>create database scrb;
mysql> create database scrb;
Query OK, 1 row affected (0.01 sec)
创建表
mysql> create table user(id int(3),name varchar(15));
Query OK, 0 rows affected (0.02 sec)
从服务器查看同步过来没有
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysync |
| performance_schema |
| scrb |
| sys |
+--------------------+
6 rows in set (0.00 sec)
查看表
mysql> show tables;
+----------------+
| Tables_in_scrb |
+----------------+
| user |
+----------------+
1 row in set (0.00 sec)
可以看见,已经成了。
到此windows下的单向主从同步配置已经完成了。
如果想关掉主从同步,只需要在从服务端执行一下命令即可
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.199
Master_User: mysync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1278
Relay_Log_File: PC05-relay.000003
Relay_Log_Pos: 663
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No //已经关闭同步复制功能
Slave_SQL_Running: No
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: 1278
Relay_Log_Space: 1527
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: NULL
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: 199
Master_UUID: 516cb325-a148-11e9-b3df-02004c4f4f50
Master_Info_File: C:\ProgramData\MySQL\MySQL Server 5.7\Data\master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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:
1 row in set (0.00 sec)
可以对比上边的开始状态,可知已经关闭了。