Tech Notes

After Enabling Network Access, we can Enable Replication before Making a Remote Backup of the Database. If you’re reading this to simply make recurring backups of your MySQL remotely, then you can ignore this step.

In this step, we’ll

1. assign IDs to the Primary and Secondary
2. restart the primary
3. proceed to backup the database before starting the Secondary

Primary: Sleepy (ID = 1)
Secondary: Doc (ID = 2)
MySQL: 5.1.41-enterprise-commercial-pro
OS: Windows 2008R2

Assign ID to the Primary
While assigning a replication ID, we can also define the binary log for replication; I did this using two parameters into the my.ini (my.cnf) file:

(Primary/Sleepy)

[mysqld]
...
#bind-address=127.0.0.1 # commented to bind to all interfaces
log-bin=”E:\Data\repl-bin”
binlog=format=ROW
server-id=1

(Secondary/Doc)

[mysqld]
...
server-id=2
skip-slave-start

Start the Primary server, but don’t start the secondary yet. Note that “skip-slave-start” is there as opposed to running the Secondary with the option “–skip-slave-start”, which is difficult to do using Windows’ service stop/start. This option is only there for the first run of the Secondary.

(On Sleepy/Primary)

mysql.exe -u root mysql
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@'%’;
mysql> FLUSH PRIVILEGES;

You should notice that when the Primary server starts up again, it begins creating E:Datarepl-bin.index and E:Datarepl-bin.000001 files