首先把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中同步。