单台windows搭建mysql主从机备份

2017-04-08

技术

1.在一台window上搭建两个MySQL实例

1.1.下载 MySQL 5.6

下载地址：http://download.csdn.net/download/zzu_wang/8708049

注意，一定要下载 5.6 版本，5.7版本会出现各种各样奇怪的问题。

1.2.建立对应目录

在你喜欢的位置（我的是D:\WorkSpace\mysql\）建立两个文件夹，分别叫 mysql5601 和 mysql5602。将刚才下载到的 zip 文件分别复制到这两个文件夹中解压。

解压完成之后应该是这个样子的：

1.3.修改响应配置文件

分别打开 mysql5601 和 mysql5602 ，修改 my-default.ini 文件

打开 my-default.ini 文件，修改下图中标记的位置

basedir 为 mysql 的解压目录，datadir 为 basedir\data，两个实例的 port 分别设置为 3306 和 3307，server_id 分别设置为 1 和 2。

mysql5601：

basedir = D:\WorkSpace\mysql\msql5601
datadir = D:\WorkSpace\mysql\msql5601\data
port = 3306
server_id = 1

mysql5602：

basedir = D:\WorkSpace\mysql\msql5602
datadir = D:\WorkSpace\mysql\msql5602\data
port = 3307
server_id = 2

注意，只修改刚才提到的地方就好，其他地方暂时先不用管，后面会有提到。

1.4.启动 MySQL 服务

在 windows 搜索框中搜索 cmd，得到结果如下图所示：

按下 Ctrl + Shift + Enter，以管理员身份进入 cmd，进入之后，如下图所示：

进入到 D:\WorkSpace\mysql\msql5601\bin 目录下，输入：

1	mysqld install MySQL1 --defaults-file="D:\WorkSpace\mysql\msql5601\my-default.ini" ：

因为我已经安装过了，所以会显示 The service already exists! ，第一次安装应该显示 Service successfully installed.

【MySQL1】是服务的名称，安装成功后，在 windows 搜索里输入【服务】，可以在服务中找到它。

安装成功后，输入 net start MySQL1，启动这个服务：

然后用同样的方法启动另一个服务，这回叫MySQL2：

进入到 D:\WorkSpace\mysql\msql5602\bin 目录下，输入：

1	mysqld install MySQL2 --defaults-fil e= "D:\WorkSpace\mysql\msql5602\my-default.ini"

安装成功后，输入 net start MySQL2，启动这个服务，得到结果如下图所示：

两个服务都跑起来之后，我们就可以进入下一步：实现主从备份了。

2.搭建主从备份

2.1.在两个 MySQL 实例创建数据库

我们以 mysql5601 中的实例作为主库，以 mysql5602 中的实例作为从库。

在这两个数据库中各自创建一个叫做 test 的库作为用来同步的库。具体操作如下：

进入 D:\WorkSpace\mysql\msql5602\bin 目录下，输入 mysql -uroot -proot -P3306

在这个命令中，前一个小写的 p 是密码（password）的 p，后一个大写的 P 是端口（Port）的 P。

然后输入：create database test;

我的因为已经建立过了，所以会报出 database exists。

输入：show databases;

显示现有的 database，我们可以看到，test已经在里面了。

接下来我们建立从机的数据库，打开一个cmd，进入 D:\WorkSpace\mysql\msql5602\bin 目录下，输入 mysql -uroot -proot -P3307

然后输入：create database test;

这样两个数据库就建立好了。

2.2.修改主库配置文件

打开 mysql5601，修改 my-default.ini 文件，在其中添加如下内容：

1
2
3

server-id=1    #服务器 id 
log-bin=C:\Program Files\MySQL\MySQL Server 5.5\mysql-bin    #二进制文件存放路径
binlog-do-db=test1    #待同步的数据库

添加完后，我们的 my-default.ini 文件就会像下面这样：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir = D:\WorkSpace\mysql\msql5601
datadir = D:\WorkSpace\mysql\msql5601\data
port = 3306
server_id = 1
log-bin=D:\WorkSpace\mysql\msql5601\mysql-bin    #二进制文件存放路径
binlog-do-db=test    #待同步的数据库
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Windows 搜索【服务】，重启 MySQL1 服务。

重启之后，你会看到 D:\WorkSpace\mysql\msql5601 目录下多出了两个文件（因为我重启过三遍所以多出来四个）：

2.3.给要连接的从服务器设置权限

打开一个cmd，进入 D:\WorkSpace\mysql\msql5602\bin 目录下，输入 mysql -uroot -proot -P3306

进入 mysql 命令行模式，输入

1	grant replication slave,reload,super on . to slave@10.0.76.192 identified by 'root';

给主机10.0.76.192添加权限，用户名：slave，密码：root；

注意，10.0.76.192是我的 ip 地址，这个地址需要改成你自己的 ip 地址（如果不是一台机器则要改成从机的 ip 地址）。查询 ip 地址的方法很简单，打开 cmd，输入 ipconfig 即可。

2.4.输入命令 show master status;

找到 File 和 Position 的值记录下来，这些值后面会用到。

2.5.修改从库配置文件

打开mysql5602，修改my-default.ini文件：

1 2	server-id=2 #服务器 id ，不能和主服务器一致 replicate-do-db=test1 #待同步的数据库

修改后的 my-default.ini 文件如下所示：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir = D:\WorkSpace\mysql\msql5602
datadir = D:\WorkSpace\mysql\msql5602\data
port = 3307
server_id = 2
replicate-do-db=test    #待同步的数据库
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

重启 MySQL2 服务。

2.6.修改从库对主库的连接参数

打开一个cmd，进入 D:\WorkSpace\mysql\msql5602\bin 目录下，输入 mysql -uroot -proot -P3307

进入 mysql 命令行模式，输入：

1	change master to master_host='10.0.76.192',master_user='slave',master_password='root', master_log_file='mysql-bin.000003',master_log_pos=120;

注意：这里输入的 mysql-bin.000003 和 120 请替换为步骤2.4中查询出来的值，master_host 替换为你自己的 ip。

2.7.启动 salve 线程

输入：start slave;

然后输入：show slave status\G （没有分号）

看是否有以下两行：

1 2	Slave_IO_Running: Yes Slave_SQL_Running: Yes

这两个指标非常重要，任意一行为No都不能正常运行。我就曾经遇到了下面的这种问题。

1	[ERROR] Slave I/O: error connecting to master 'slave@10.0.76.192:3306:3306' - retry-time: 60 retries: 6, Error_code: 2005

解决方法很简单，重启以下 slave 的服务就可以了。

2.8.测试同步

打开两个 cmd ，进入 D:\WorkSpace\mysql\msql5602\bin 目录下，分别输入：

1 2	mysql -uroot -proot -P3306（主机） mysql -uroot -proot -P3307（从机）

在主机上输入：use test;

转到 test 数据库，然后输入：create table table1;

新建一个名为 table1 的表。然后输入：show tables;

得到结果如下图所示：

上面的操作只是在主机上的 test 数据库中建立了一张名为 table1 的表。

接下来我们进入从机的 cmd，输入：use test;

然后输入：show tables;

得到结果如下图所示：

可以看到，两个数据库的数据已经开始自动同步了。

3.参考链接

windows下mysql主从同步

http://www.cnblogs.com/xiaochangwei/p/4824355.html

mysql （master/slave）复制原理及配置

http://blog.csdn.net/mer1234567/article/details/7405775

window下一台PC机器上安装多个mysql的方法

http://blog.csdn.net/zfqzpp/article/details/13630905

mysql 数据同步出现Slave_IO_Running:No问题的解决方法小结

http://www.jb51.net/article/27220.htm

slave have equal MySQL Server UUIDs原因及解决

http://www.linuxidc.com/Linux/2015-02/113564.htm