MySQL replication for non-standard port (3306)

MySQLreplication

I want to do MySQL replication using MySQL 5.6.14 under MS Windows 2008 R2, the port for Master and Slave should be 1000 (master database on PC1, slave database installed on PC2).

I added the following parameters for master My.ini

[mysqld]
server-id=1
log-bin=black-bin.log
datadir=d:\mysql\master\
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Then I edited slave My.ini adding the following parameters:

[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
datadir=d:\mysql\slave

After that I logged in into Master database using root user and executed the following commands

CREATE USER repl_user@slave_ip;
GRANT REPLICATION SLAVE ON *.* TO repl_user@slave_ip IDENTIFIED BY 'password';

To Initialize Replication I logged in into slave database and executed the following commands:

CHANGE MASTER TO MASTER_HOST='maset_ip',
 -> MASTER_USER='repl_user',
 -> MASTER_PASSWORD='password';
start slave;

The replication works fine if the port for the master is 3306 but if the port is changed then it does not work anymore. My question is: how to make slave connect to port 1000? I tried to add the following parameter to slave my.ini master-port=1000 then restarted the slave SID but it did not work (service did not start, error message: unknown parameter master-port). Please advise, thanks.

Best Answer

You need to add the port to my.ini for the Master

[mysqld]
server-id=1
port=1000
log-bin=black-bin.log
datadir=d:\mysql\master\
innodb_flush_log_at_trx_commit=1
sync_binlog=1

and restart mysql

First run this

STOP SLAVE;
SHOW SLAVE STATUS\G

From the output

  • use Relay_Master_Log_File as CurrentLogFile
  • use Exec_Master_Pos as CurrentLogPosition

As was mentioned in the comment, run CHANGE MASTER TO on the Slave like this

CHANGE MASTER To
MASTER_HOST='ipaddrofmaster',
MASTER_PORT=1000,
MASTER_USER='repl_user',
MASTER_PASSWORD='password'
MASTER_LOG_FILE='CurrentLogFile',
MASTER_LOG_POS=CurrentLogPosition;
START SLAVE;

Give it a Try !!!

Related Question