win10 mysql 主从复制_本地 win10系统 MySql主从库配置

一、主从分库的目的

MySql主从库，主库负责写数据，从库负责读数据，主从库读写分开可以为数据库的频繁访问减轻压力，主数据库只有一个，从数据库可以有一个或者多个。

二、环境说明

主库所在的操作系统：win10

主库的版本：mysql-5.7.25-winx64.zip

主库的ip地址：127.0.0.1

主库的端口：3306

从库所在的操作系统：win10

从库的版本：mysql-5.7.25-winx64.zip

从库的ip地址：127.0.0.1

主库的端口：3307

Mysql压缩包下载地址：https://dev.mysql.com/downloads/mysql/5.7.html#downloads

图-1

下载安装包建议到官网下载，有的安装包在有些系统里会不支持，官网下载的都有说明，是否支持某个版本。这里使用解压版的MySql安装包，有一个重要的原因就是本人电脑系统不支持安装版的，只能使用解压版的。

主从数据库备份，最好保证主从数据库版本一致，如果不一致，就要保证从数据库比主数据的版本要高。

三、数据库安装配置

下载MySql压缩包，然后进行解压，为了便于标识，并对加压后的文件重新命名。主数据的文件命名为mysql-master,从数据库的文件命名为mysql-slave。

图-2

解压后，首先对环境变量进行配置，增加MYSQL_HOME的环境变量，并把%MYSQL_HOME%/bin加到path中，新建环境变量，建的是系统变量，而不是用户变量。

图-3

图-4

1)主数据库安装

第一步，在解压目录下新建my.ini文件和data文件夹。

图-5

创建my.ini配置文件，内容如下：

#代码开始

[Client]

#设置3306端口

port = 3306

default-character-set=utf8

[mysqld]

#设置3306端口

port = 3306

#设置mysql服务器字符集

collation-server=utf8_general_ci

# 设置mysql的安装目录

basedir=D:\database\mysql-master

# 设置mysql数据库的数据的存放目录

datadir=D:\database\mysql-master\data

# 允许最大连接数

max_connections=200

# 服务端使用的字符集默认为8比特编码的latin1字符集

character-set-server=utf8

# 创建新表时将使用的默认存储引擎

default-storage-engine=INNODB

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#重新设置密码时，将此放开，设置成功后，再注释掉

#skip-grant-tables

#表名全部为小写，避免出现大小写敏感

lower_case_table_names=1

[mysql]

# 设置mysql客户端默认字符集

default-character-set=utf8

#让mysql数据库支持 事务设置 引擎为：INNODB

#default-storage-engine=INNODB

#代码结束

第二步，使用管理员身份进入到主库bin目录下，执行mysqld –install mysqlmaster –defaults-file=“d:\database\mysql-master\my.ini”命令，执行结果出现Servicesuccessfully installed.时，安装成功，其中mysqlmaster为主库服务名称。

图-6

第三步，初始化系统数据库和系统数据库中的表，执行mysqld–initialize命令；

图-7

第四步，查看data文件夹下，mysqld -–initialize执行的结果，出现mysql、sys等数据库时，代表此命令已经执行成功。

图-8

第五步，启动主库服务，使用net start mysqlmaster 命令，出现下图代表启动成功。

图-9

第六步，更改数据库密码，首先使用 net stop mysqlmaster 命令停止服务。

图-10

第七步，把my.ini中skip-grant-tables 前的注释去掉后保存，然后重新启动服务。

图-11

第八步，使用mysql -u root -p 命令进入mysql的命令行，然后输入密码123456。

图-12

第九步，使用 use mysql 使用mysql系统数据库，然后执行修改密码的指令 updateuser set authentication_string=PASSWORD(‘123456′)where user=’root’; 修改过后，使用 flush privileges; 执行刷新操作。

图-13

第十步，把my.ini中 skip-grant-tables前的注释加上，保存后，重启服务。

备注：如果服务名称安装错了，可以使用 sc delete mysqlmaster 命令来删除已经建立好的服务。

2)从数据库安装

同主数据库的安装一样，步骤如上，有几点需要注意，

第一点， 在安装目录下新建my.ini和data文件

第二点， my.ini中的端口号修改成3307

第三点， 下面的两个路径也要修改掉

#设置从库端口号

port = 3307

# 设置mysql的安装目录

basedir=D:\database\mysql-slave

# 设置mysql数据库的数据的存放目录

datadir=D:\database\mysql-slave\data

3)主数据库配置

第一步，打开主数据的my.ini文件，在 [mysqld] 下面添加

#主库配置,服务器标志号，注意在配置文件中不能出现多个这样的标识，如果出现多个的话mysql以第一个为准，一组主从中此标识号不能重复。

server_id = 1

#开启bin-log

log-bin=mysql-bin

#不需要备份，需要忽略的数据库

binlog-ignore-db=mysql

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

图-14

第二步，使用net start mysqlmaster 命令重启主数据库。

4)从数据库配置

第一步，打开从数据库的my.ini文件，在 [mysqld] 下面添加

#从库配置

server_id=2

#开启bin-log

log-bin=mysql-bin

第二步，使用 net start mysqlmaster 命令重启主数据库。

图-15

四、主从库备份授权

第一步，主库授权：

grant replication slave on *.* to ‘test’@’127.0.0.1’ identifiedby ‘123456’;

flush privileges; #授权后需要刷新后，才能生效；

第二步，使用 show master status; 命令查看主库授权状态。

图-16

第三步，从库授权

change master to master_host=’127.0.0.1′,master_user=’test’,master_password=’123456′,master_log_file=’mysql-bin.000003′,master_log_pos=154;

图-17

特别注意：master_log_file文件的名字、master_log_pos文件的位置要和 图-16中的File和Position参数对应，否则会影响执行的结果。

第四步，使用start slave; 命令启动授权；

图-18

图-19

备注：当授权出现错误时，可以先停止slave，然后清除授权，使用 reset slave; #清除授权，但是授权信息保留；

reset slave all; #清除授权，授权信息也被清除掉。

第五步，使用show slave slave status \G 命令，查看授权状态,参数Slave_IO_Running:Yes和Slave_SQL_Running:yes,这两项都为Yes才是配置成功。

图-20

第六步，主从库同步验证

在主库建立一个test数据库，然后刷新从数据库，可以看到新建的数据库已经被同步过去。

图-21

第七步，扩展，为读库创建只读权限的账户

创建只读用户：create user ‘root_readonly’@’%’ identified by ‘123456’;

修改密码：update user setauthentication_string=PASSWORD(‘123456′) where user=’root_readonly’;

授权只读

GRANT SElECT ON *.* TO ‘root_readonly’@’%’ IDENTIFIED BY “123456”; #其中%代表所有的IP地址都可以访问，如果设置为127.0.0.1，那么就只有localhost才能访问。

flush privileges; #授权后需要刷新后，才能生效；

如果要删除用户时，使用 drop user test; #删除用户

flush privileges;#删除用户后，需要刷新，否则再次创建同一个账号会报错；

备注：在读库，对开发者只开放只读用户，这样就可以减少人为的数据库修改，所引起的数据不能同步的错误。

五、总结

Mysql主从库分库，折腾了很久，原来用过主从库，但是没有自己建过主从库。现在自己创建了一次，看资料看了很久，在自己的电脑上实践，总是出现这样那样的错误，反反复复安装了一个星期，最后在测试服务器上搭建，又折腾了一下，最终一个上午搞定了。

原来使用的那个主从库，有一个问题经常出现，就是在开发环境下，读库经常被其他同时修改掉，导致的结果是主从库不能同步，还要花时间修复这个错误。为了避免这个情况，给读库建了一个只有只读权限的用户，对开发人员开放只有读权限的用户，这样就可以避免这个问题了，开发人员想修改读库数据也会因为权限不足而不能修改。