Notice: register_sidebar was called incorrectly. No id was set in the arguments array for the "Sidebar 1" sidebar. Defaulting to "sidebar-1". Manually set the id to "sidebar-1" to silence this notice and keep existing sidebar content. Please see Debugging in WordPress for more information. (This message was added in version 4.2.0.) in /usr/share/wordpress/wp-includes/functions.php on line 4139 Tech Notes » Blog Archive » MySQL Replication Walkthru: Enable Replication

MySQL Replication Walkthru: Enable Replication

dataflow, howto Add comments

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

2 Responses to “MySQL Replication Walkthru: Enable Replication”

  1. MySQL Replication Walkthru: Making a Remote Backup Says:

    […] Well With Others MySQL Replication Walkthru: Enable Replication Nov […]

  2. MySQL Replication Walkthru: Activate Secondary Says:

    […] Enabling Replication and Making a Remote Backup), we can activate the secondary […]

Leave a Reply

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in