一、安装多个mysql
参见：
https://blog.csdn.net/wrh_csdn/article/details/80198795
https://www.cnblogs.com/qjoanven/p/7898006.html
https://www.cnblogs.com/qq931399960/p/10186627.html
如博客说的，我解压安装文件两次，在同一台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 if you # *** upgrade to a newer version of MySQL. [Client] port = 3307 [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. server_id = 1 port = 3307 basedir = C:UserseddyDesktopwin-eddymysql-5.6.17-winx64 datadir = C:UserseddyDesktopwin-eddymysql-5.6.17-winx64data #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 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 #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_MACHINESYSTEMCurrentControlSetServicesmysql3306
C:UserseddyDesktopwin-eddy3306inmysqld --defaults-file=C:UserseddyDesktopwin-eddy3306my.ini mysql3306
同理，也修改一下mysql3307
到此，运行两个服务
net start mysql3306
net start mysql3307
4，初始化mysql的使用
https://blog.csdn.net/wzcyamadie/article/details/82699398
二、主从同步问题
参见：
https://blog.csdn.net/L_Mr_l/article/details/81485391
我以端口为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 if you # *** upgrade to a newer version of MySQL. [Client] port = 3307 [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. server_id = 1 port = 3307 basedir = C:UserseddyDesktopwin-eddymysql-5.6.17-winx64 datadir = C:UserseddyDesktopwin-eddymysql-5.6.17-winx64data 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 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 #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 if you # *** upgrade to a newer version of MySQL. [Client] port = 3306 [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. server_id =2 basedir = C:UserseddyDesktopwin-eddy3306 datadir = C:UserseddyDesktopwin-eddy3306data port = 3306 log-slave-updates log-bin=mysqld-bin innodb_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