mysql 单向同步 测试数据_windows下mysql单向主从同步配置

查看mysql配置文件地址

mysql --help|grep 'my.cnf'

ps aux|grep mysql|grep 'my.cnf'

配置分为两部分，主服务器部分，从服务器部分

本次测试的两台机器的主从同步，环境是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下的单向主从同步配置已经完成了。

如果想关掉主从同步，只需要在从服务端执行以下命令即可

stop slave:关闭从服务器的同步功能

start slave:开启从服务器的同步功能

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)

可以对比上边的开始状态，可知已经关闭了。