文档课题：Windows 环境下MySQL主从复制.

系  统：Microsoft Windows Server 2012 Standard 64位

数据库：mysql 5.7.21

安装包：mysql-installer-community-5.7.21.0.msi

主从环境如下：

1、环境搭建

1.1、配置IP

安装两台MySQL数据库主机后，配置好两台主机IP地址，确保两台服务器可以互相ping通，注意防火墙状态.

1.2、配置Master

1.2.1、开启binlog

--检查mysql数据库binlog情况.

mysql> show variables like '%log_bin%';

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| log_bin                         | OFF   |

| log_bin_basename                |       |

| log_bin_index                   |       |

| log_bin_trust_function_creators | OFF   |

| log_bin_use_v1_row_events       | OFF   |

| sql_log_bin                     | ON    |

+---------------------------------+-------+

6 rows in set, 1 warning (0.00 sec)

 

mysql> show variables like '%datadir%';

+---------------+---------------------------------------------+

| Variable_name | Value                                       |

+---------------+---------------------------------------------+

| datadir       | C:\ProgramData\MySQL\MySQL Server 5.7\Data\ |

+---------------+---------------------------------------------+

1 row in set, 1 warning (0.02 sec)

 

--在Master主机上开启binlog日志，打开C:\ProgramData\MySQL\MySQL Server 5.7\目录下的配置文件my.ini，添加如下代码，开启binlog功能.

[mysqld]

log_bin="D:/MySQL/binlog/binglog"

expire_logs_days = 10

max_binlog_size = 100M

 

提示：

a、系统层面新建目录D:/MySQL/binlog/

b、expire_logs_days表示二进制日志文件删除的天数，max_binlog_size表示二进制日志文件最大大小.

 

--重启mysql服务后确认log_bin是否开启.

mysql> show variables like '%log_bin%';

+---------------------------------+------------------------------+

| Variable_name                   | Value                        |

+---------------------------------+------------------------------+

| log_bin                         | ON                           |

| log_bin_basename                | D:\MySQL\binlog\binlog       |

| log_bin_index                   | D:\MySQL\binlog\binlog.index |

| log_bin_trust_function_creators | OFF                          |

| log_bin_use_v1_row_events       | OFF                          |

| sql_log_bin                     | ON                           |

+---------------------------------+------------------------------+

6 rows in set, 1 warning (0.02 sec)

 

说明：log_bin参数为on表示二进制日志文件已经成功开启.

1.2.2、配置账户

在master上配置复制所需要的账户，创建repl用户，%表示任何远程地址的repl用户都可以连接master主机，语句如下：

mysql> grant replication slave on *.* to repl@'%' identified by 'repl';

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

1.2.3、建测试库

mysql> create database test;

Query OK, 1 row affected (0.00 sec)

mysql> use test;

Database changed

mysql> create table books

    -> (

    -> bk_id int not null primary key,

    -> bk_title varchar(50) not null,

    -> copyright year not null

    -> );

Query OK, 0 rows affected (0.01 sec)

 

mysql> insert into books values

    -> (11078,'Learning MYSQL',2010),

    -> (11033,'Study Html',2011),

    -> (11035,'How to use php',2003),

    -> (11072,'Teach yourself javascript',2005),

    -> (11028,'Learning C++',2005),

    -> (11069,'MYSQL professional',2009),

    -> (11026,'Guide to MySQL 5.7',2008),

    -> (11041,'Inside VC++',2011);

Query OK, 8 rows affected (0.00 sec)

Records: 8  Duplicates: 0  Warnings: 0

 

mysql> select * from books;

+-------+---------------------------+-----------+

| bk_id | bk_title                  | copyright |

+-------+---------------------------+-----------+

| 11026 | Guide to MySQL 5.7        |      2008 |

| 11028 | Learning C++              |      2005 |

| 11033 | Study Html                |      2011 |

| 11035 | How to use php            |      2003 |

| 11041 | Inside VC++               |      2011 |

| 11069 | MYSQL professional        |      2009 |

| 11072 | Teach yourself javascript |      2005 |

| 11078 | Learning MYSQL            |      2010 |

+-------+---------------------------+-----------+

8 rows in set (0.00 sec)

1.2.4、修改配置文件

在my.ini配置文件中配置master主机的相关信息.

添加如下内容：

[mysqld]

log_bin="D:/MySQL/binlog/binlog"

expire_logs_days=10

max_binlog_size =100M

 

server-id=1

binlog-do-db=test

binlog-ignore-db=mysql

 

说明：

server-id 表示服务器标识id号，master和slave主机的server-id不能一样

binlog-do-db 表示需要复制的数据库，此处以test数据库为例

binlog-ignore-db 表示不需要复制的数据库

 

--修改完my.ini后，重启master主机的mysql服务，然后查询master主机信息.

mysql> show master status \G

*************************** 1. row ***************************

File: binlog.000005

Position: 154

Binlog_Do_DB: test

Binlog_Ignore_DB: mysql

Executed_Gtid_Set:

1 row in set (0.00 sec)

 

注意：此处File与Position在后面从库操作时需要.

1.2.5、备份test库

C:\Users\Administrator>mysqldump -u root -p -h localhost test > D:\test.txt

Enter password: ********

1.3、Slave配置

1.3.1、导入数据

将master主库备份出来test库数据传输到Slave主机后进行导入.

mysql> create database test;

Query OK, 1 row affected (0.00 sec)

 

mysql> use test;

Database changed

mysql> source D:/test.txt

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.01 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 8 rows affected (0.00 sec)

Records: 8  Duplicates: 0  Warnings: 0

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected, 2 warnings (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| books          |

+----------------+

1 row in set (0.00 sec)

说明：如上所示，数据成功导入Slave从库端.

1.3.2、修改配置文件

修改Slave主机配置文件my.ini.

添加如下：

[mysql]

default-character-set=utf8

log_bin="D:/MySQL/binlog/binlog"

expire_logs_days=10

max_binlog_size=100M

 

[mysqld]

server-id=2

 

说明：

a、需将server-id=2写到[mysqld]后面，另外如果配置文件中还有log_bin等相同参数的配置，需将其注释掉.

b、新建目录D:/MySQL/binlog/，然后重启Slave主机.

1.3.3、主从同步

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> change master to

    -> master_host='192.168.133.136',

    -> master_user='repl',

    -> master_password='repl',

    -> master_log_file='binlog.000005',

    -> master_log_pos=154;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

 

注意：此处master_log_file与master_log_pos为此前Master主库所查得信息.

说明：

master_host 表示实现复制的主机ip地址

master_user 表示实现复制的登录远程主机的用户

master_password 表示实现复制的登录远程主机的密码

master_log_file 表示实现复制的binlog日志文件

master_log_pos 表示实现复制的binlog日志文件的偏移量

 

开启slave.

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show slave status \G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.133.136

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: binlog.000005

          Read_Master_Log_Pos: 154

               Relay_Log_File: WIN-12HQ2SAA8ON-relay-bin.000003

                Relay_Log_Pos: 317

        Relay_Master_Log_File: binlog.000005

             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: 154

              Relay_Log_Space: 534

              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: 1

                  Master_UUID: a1db5f5d-f722-11ed-84af-000c29a2bafe

             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)

2、主从复制测试

--Master端执行如下命令：

mysql> use test;

Database changed

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| books          |

+----------------+

1 row in set (0.00 sec)

 

mysql> create table rep_test(

    -> data integer

    -> );

Query OK, 0 rows affected (0.00 sec)

 

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| books          |

| rep_test       |

+----------------+

2 rows in set (0.00 sec)

 

--Slave端检查.

mysql> use test;

Database changed

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| books          |

| rep_test       |

+----------------+

2 rows in set (0.00 sec)

 

mysql> select * from rep_test;

Empty set (0.00 sec)

说明：如上所示，Slave端能查询出Master端的新增表rep_test.

 

--Master端新增数据测试.

mysql> insert into rep_test (data) values (1);

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from rep_test;

+------+

| data |

+------+

|    1 |

+------+

1 row in set (0.00 sec)

 

--Slave端查询

mysql> select * from rep_test;

+------+

| data |

+------+

|    1 |

+------+

1 row in set (0.00 sec)

 

说明：如上所示，数据成功同步到Slave.