windows下一台机子配置多个mysql实例搭建主从教程

生产环境只有一台服务器，所以才这么做的

1.停止mysql

进入d:\BtSoft\mysql

复制二个MySQL5.7

改为为MySQL5.7S1 MySQL5.7S2

2.S1和S2中配置 my.ini 文件

S1配置

[client]
default-character-set=utf8

[mysqld]
character_set_server=utf8
port=3307
basedir  ="D:/data_center/BtSoft/mysql/MySQL5.7S1/"
datadir  ="D:/data_center/BtSoft/mysql/MySQL5.7S1/data/"
tmpdir   ="D:/data_center/BtSoft/mysql/MySQL5.7S1/data/"
socket   ="D:/data_center/BtSoft/mysql/MySQL5.7S1/data/mysql.sock"
log-error="D:/data_center/BtSoft/mysql/MySQL5.7S1/data/mysql_error.log"
log-bin=mysql-bin
binlog_format=mixed
server_id = 2

#skip-locking
max_connections = 1000
table_open_cache = 2048
query_cache_size = 384M
tmp_table_size = 2048M
thread_cache_size = 256
binlog_cache_size = 256K
thread_stack = 512K
read_buffer_size = 4096K
query_cache_type = 1
max_heap_table_size = 2048M
key_buffer_size = 1024M
expire_logs_days = 10

innodb_data_home_dir="D:/data_center/BtSoft/mysql/MySQL5.7S1/data/"
innodb_flush_log_at_trx_commit =1
innodb_log_buffer_size = 128M
innodb_buffer_pool_size = 4096M
innodb_log_file_size=10M
innodb_thread_concurrency=16
innodb-autoextend-increment=1000
join_buffer_size = 8192K
sort_buffer_size = 4096K
read_rnd_buffer_size = 2048K
max_allowed_packet = 1024M
explicit_defaults_for_timestamp=true
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
#STRICT_TRANS_TABLES,
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_lock_wait_timeout = 500
wait_timeout=2880000

interactive_timeout = 2880000

S2配置

[client]
default-character-set=utf8

[mysqld]
character_set_server=utf8
port=3308
basedir  ="D:/data_center/BtSoft/mysql/MySQL5.7S2/"
datadir  ="D:/data_center/BtSoft/mysql/MySQL5.7S2/data/"
tmpdir   ="D:/data_center/BtSoft/mysql/MySQL5.7S2/data/"
socket   ="D:/data_center/BtSoft/mysql/MySQL5.7S2/data/mysql.sock"
log-error="D:/data_center/BtSoft/mysql/MySQL5.7S2/data/mysql_error.log"
log-bin=mysql-bin
binlog_format=mixed
server_id = 3

#skip-locking
max_connections = 1000
table_open_cache = 2048
query_cache_size = 384M
tmp_table_size = 2048M
thread_cache_size = 256
binlog_cache_size = 256K
thread_stack = 512K
read_buffer_size = 4096K
query_cache_type = 1
max_heap_table_size = 2048M
key_buffer_size = 1024M
expire_logs_days = 10

innodb_data_home_dir="D:/data_center/BtSoft/mysql/MySQL5.7S2/data/"
innodb_flush_log_at_trx_commit =1
innodb_log_buffer_size = 128M
innodb_buffer_pool_size = 4096M
innodb_log_file_size=10M
innodb_thread_concurrency=16
innodb-autoextend-increment=1000
join_buffer_size = 8192K
sort_buffer_size = 4096K
read_rnd_buffer_size = 2048K
max_allowed_packet = 1024M
explicit_defaults_for_timestamp=true
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
#STRICT_TRANS_TABLES,
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_lock_wait_timeout = 500
wait_timeout=2880000

interactive_timeout = 2880000

3.删除MySQL5.7S1 MySQL5.7S2中DATA目录下auto.cnf文件，否则启动不成功

4.启动主数据库并设置只读

设置只读模式(此中模式连super都不能进行写)

mysql> show global variables like "%read_only%";

mysql> flush tables with read lock;

mysql> set global read_only=1;

mysql> show global variables like "%read_only%";

5.配置主从

创建同步账号

mysql> CREATE USER 'repl'@'123.57.44.85' IDENTIFIED BY 'slavepass';#创建用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'123.57.44.85';#分配权限
mysql>flush privileges;   #刷新权限
这步省略了，直接使用root账号

   查看master状态，记录位置 mysql-bin.000740      154

show master status;

 在2个从库配置同步命令

stop slave;

CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_PORT=3306,MASTER_USER='root',MASTER_PASSWORD='root',MASTER_LOG_FILE='mysql-bin.000740',MASTER_LOG_POS=154;

#启动slave进程
start slave;

 查看slave的状态，如果下面两项值为YES，则表示配置正确

show slave status

6.解除主库只读模式

mysql> unlock tables;

mysql> set global read_only=0;

7.设置环境变量，根据 MySQL 所在目录添加至系统环境变量

  先把path中mysql的环境变量去掉，一会再加回来  BtSoft\mysql\MySQL5.7\bin；先去掉

8.以管理员方式运行，分别进入 MySQL5.7S1和MySQL5.7S2 的 bin 目录

mysqld install MySQLS1-3307  --defaults-file=D:\data_center\BtSoft\mysql\MySQL5.7S1\my.ini

mysqld install MySQLS2-3308  --defaults-file=D:\data_center\BtSoft\mysql\MySQL5.7S2\my.ini

9.有错误时再修改注册表

HKEY_LOCAL_MACHINE-->SYSTEM-->CurrentControlSet-->Services

将MySQLS1下的ImagePath改成"D:\MySql\MySQL Server 5.7S1\bin\mysqld.exe" --defaults-file=D:\data_center\BtSoft\mysql\MySQL5.7\my.ini mysqls1-3307

参考： "D:\data_center\BtSoft\mysql\MySQL5.7\bin\mysqld.exe" --defaults-file=D:\data_center\BtSoft\mysql\MySQL5.7\my.ini MySQL

修改
"D:\data_center\BtSoft\mysql\MySQL5.7S1\bin\mysqld.exe" --defaults-file=D:\data_center\BtSoft\mysql\MySQL5.7S1\my.ini mysqls1-3307
"D:\data_center\BtSoft\mysql\MySQL5.7S2\bin\mysqld.exe" --defaults-file=D:\data_center\BtSoft\mysql\MySQL5.7S2\my.ini mysqls2-3308