mysql 同 实例 主从_同一台windows下配置安装多个mysql实例，实现主从同步

一、安装多个mysql

参见：

如博客说的，我解压安装文件两次，在同一台win下做两mysql实例的主从同步：

版本：mysql-5.6.17-winx64.zip

我没有采用：mysqld.exe --port=3307 --console 的方式启动多个实例，而是采用了注册服务的方式操作：

1，编辑my.ini文件，以其中一个my.ini文件为例子，另一个只需要修改一下port、basedir、datadir即可：

# 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 ifyou

#***upgrade to a newer version of MySQL.

[Client]

port= 3307[mysqld]

# Remove leading # and set to the amount of RAMforthe most important data

# cache in MySQL. Start at70% 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.

server_id= 1port= 3307basedir= C:\Users\eddy\Desktop\win-eddy\mysql-5.6.17-winx64

datadir= C:\Users\eddy\Desktop\win-eddy\mysql-5.6.17-winx64\data#back_log= 600#max_connections=2000#max_connect_errors= 6000#wait_timeout=605800#tmp_table_size=256M

#max_heap_table_size=256M

#default-storage-engine=MYISAM

# Remove leading # to set options mainly usefulforreporting servers.

# The server defaults are fasterfortransactions 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

#default_time_zone=+08:00[mysql]default-character-set=utf8

2，初始化mysql，搜索cmd找到命令提示符，右键以管理员身份运行，进入bin目录， 主要是生成一些data目录等。。

mysqld --initialize --user=mysql --console

3，安装服务(两个，在不同目录下)：

mysqld --install mysql3306

mysqld --install mysql3307

windows下生成了两个服务，可以运行regedit，进入注册表

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\mysql3306

C:\Users\eddy\Desktop\win-eddy\3306\bin\mysqld --defaults-file=C:\Users\eddy\Desktop\win-eddy\3306\my.ini mysql3306

同理，也修改一下mysql3307

到此，运行两个服务

net start mysql3306

net start mysql3307

4，初始化mysql的使用

二、主从同步问题

参见：

我以端口为3307 做了主,3306为从，

1，主mysql的my.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 ifyou

#***upgrade to a newer version of MySQL.

[Client]

port= 3307[mysqld]

# Remove leading # and set to the amount of RAMforthe most important data

# cache in MySQL. Start at70% 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.

server_id= 1port= 3307basedir= C:\Users\eddy\Desktop\win-eddy\mysql-5.6.17-winx64

datadir= C:\Users\eddy\Desktop\win-eddy\mysql-5.6.17-winx64\data

log-bin=mysql-bin

binlog-do-db=cms_main

#back_log= 600#max_connections=2000#max_connect_errors= 6000#wait_timeout=605800#tmp_table_size=256M

#max_heap_table_size=256M

#default-storage-engine=MYISAM

# Remove leading # to set options mainly usefulforreporting servers.

# The server defaults are fasterfortransactions 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

#default_time_zone=+08:00[mysql]default-character-set=utf8

2，从mysql，my.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 ifyou

#***upgrade to a newer version of MySQL.

[Client]

port= 3306[mysqld]

# Remove leading # and set to the amount of RAMforthe most important data

# cache in MySQL. Start at70% 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.

server_id=2basedir= C:\Users\eddy\Desktop\win-eddy\3306datadir= C:\Users\eddy\Desktop\win-eddy\3306\data

port= 3306log-slave-updates

log-bin=mysqld-bininnodb_force_recovery=0#default-storage-engine=MYISAM

#replicate-ignore-table=mydb.test1146

#replicate-do-db =cms_main

log-slave-updates

replicate-rewrite-db = cms_main ->cms_test

#back_log= 600#max_connections=2000#max_connect_errors= 6000#wait_timeout=605800#tmp_table_size=256M

#max_heap_table_size=256M[mysql]default-character-set=utf8

如果配置没有问题的话，重启是没有问题的。

3，备份主mysql的表结构，和insert语句，方式和方法有很多，这是为了从mysql中执行主mysql下的sql语句，提醒一点的是，如果没有做这一步，在主服务器中，修改从服务器中没有同步的数据，从服务器是没有记录的，就是说，从服务器只检测  主服务器insert，和已经同步过来的数据的update。

4，在主服务器：

4.1，连接主服务器 锁表   FLUSH TABLES WITH READ LOCK;这样防止在做的时候数据改变了，导致脏数据，也为了防止，影响后的流程，下面一部再说

4.2，SHOW MASTER STATUS;  执行该命令  这一步很关键，这也是为啥锁库的原因，执行这句命令一会有两个参数后面用，一个是File，一个是Position。一个是日志的名称，一个是日志的位置。下面在配置从服务器的时候会用到

5，在从服务器的操作：

5.1执行sql语句：

stop SLAVE;

CHANGE MASTER TO MASTER_HOST= '127.0.0.1',

MASTER_PORT= 3306,

MASTER_USER= 'user01',

MASTER_PASSWORD= '123456',

MASTER_LOG_FILE= 'mysql-bin.000008',

MASTER_LOG_POS= 2817;

start SLAVE;

show slave status;

当，Slave_IO_Running 和 Slave_SQL_Running 显示Yes，说明，从服务器配置完毕了

5.2 从服务器上执行主服务器上面备份的表结构和表执行语句。

修改主服务器上面的数据，从服务器上的数据也变更了。ok

三、一个主服务的多个数据库同步到从服务器的一个数据库

主服务只需要多配置一个  binlog-do-db=cms_main

从服务器只需要多配置一个 replicate-rewrite-db