mysql多个实例 主从库_Windows下安装多个Mysql实例，实现数据库主从同步备份

首先把mysql复制两份出来，可以分别命名MySQL Server 5.5_3307，MySQL Server 5.5_3308。

分别修改MySQL Server 5.5_3307和MySQL Server 5.5_3308的两个配置文件如下：

3307下面的my.ini：

[client]

port=3307

[mysql]

default-character-set=utf8

[mysqld]

server-id = 1

binlog-ignore-db=test #不记录binlog

replicate-ignore-db=test #不复制test库的binlog

log-bin=mysql-bin

binlog_cache_size = 1M

binlog_format=mixed

expire_logs_days=3

# The TCP/IP Port the MySQL Server will listen on

port=3307

#Path to installation directory. All paths are usually resolved relative to this.

basedir="C:/Program Files (x86)/MySQL/MySQL Server 5.5_3307/"

#Path to the database root

#datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

datadir="D:/data/mysql2/data/"

3308下面的my.ini：

[client]

port=3308

[mysql]

default-character-set=utf8

[mysqld]

server_id=2

binlog-do-db = mydb

binlog-ignore-db=test #不记录binlog

replicate-ignore-db=test #不复制test库的binlog

log-bin=mysql-bin

binlog_cache_size = 1M

binlog_format=mixed

expire_logs_days=3

# The TCP/IP Port the MySQL Server will listen on

port=3308

#Path to installation directory. All paths are usually resolved relative to this.

basedir="C:/Program Files (x86)/MySQL/MySQL Server 5.5_3308/"

#Path to the database root

#datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

datadir="D:/data/mysql3/data/"

主从配置修改完毕，接着分别启动两个mysql

在mysql3307中输入

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'127.0.0.1' IDENTIFIED BY '123456';

FLUSH PRIVILEGES;

SHOW MASTER STATUS;

如果显示如下状态则成功

"File"                         "Position"    "Binlog_Do_DB" "Binlog_Ignore_DB"

"mysqld-bin.000001"   "591"        ""                            ""

接着在mysql3308中输入

CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_PORT=3307,MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysqld-bin.000001',MASTER_LOG_POS=591;

START SLAVE;

SHOW SLAVE status

同样显示出slave的状态，并且无error记录，则代表成功。

至此主从配置完毕，在3307的mysql增删改查都会在3308中同步。