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: Activate Secondary

MySQL Replication Walkthru: Activate Secondary

dataflow, howto Add comments

After Enabling Replication and Making a Remote Backup), we can activate the secondary server.

Already, our Primary has been returned to service, and we don’t really need to alter it from this point forward; all our work after enabling replication has been on the Secondary. We’ve saved our remote backup on Secondary’s disk, but not yet started the secondary server.

We will:

  1. start the Secondary (skip-start-slaves is still in the config file)
  2. Configure the replication
  3. Import the backup file
  4. Start the Replication process on the Secondary
  5. Configure the Secondary so that it will always start the Replication automatically

Our servers:

Primary: Sleepy (192.168.44.3)
Secondary: Doc (192.168.44.4)
MySQL: 5.1.41-enterprise-commercial-pro
OS: Windows 2008R2

Start the Secondary
We still have the option “skip-start-slaves” in our my.ini (my.cnf), and now we’re going to start the server; this is as simple as using the Windows services.msc to start the MySQL service.

On a Unix-like server, /sbin/init would start the service, or if you’re in the SysVinit-monstrosity, /etc/init.d/mysql* start or something similar would start the service. We can discuss why a script would reside in /etc/ some other time when the SystemV documents and the Linux FSH are both present (hint: violates both; config files only)

Configure the Replication
Replication can also be configured using the config file, but I did it using the CLI, as follows:

(On Doc/Secondary)

mysql.exe -u root mysql
mysql> CHANGE MASTER TO MASTER_HOST=’sleepy.example.com’, MASTER_USER=’repl’, MASTER_PASSWORD=’R3plPassw0rd’;
secondary:mysql> SHOW SLAVE STATUS;

(On Doc/Secondary)

mysql.exe -u root mysql
mysql> CHANGE MASTER TO MASTER_HOST=’sleepy.example.com’, MASTER_USER=’repl’, MASTER_PASSWORD=’R3plPassw0rd’;
secondary:mysql> SHOW SLAVE STATUS;

The output of “SHOW SLAVE STATUS” should have a proper Host, user, and pass, but the logfile and log position will be fairly incorrect.

Import the Backup File

We stored the backup file we made as repldbdump.db. We should already have a user on the local server that can insert and import (by default, “root” can do this), and we’ll import it using:

(On Secondary/Doc)

mysql.exe -u root < repldbdump.db

Another benefit of storing this backup/dump with the “master” options is that it will correct the laster log file and master log position for us. A repeat of “SHOW SLAVE STATUS” should show that the MASTER_LOG_FILE and MASTER_LOG_POS in the repldbdump.db has set things right.

Start the Replication process on the Secondary

We have the data loaded from the Primary, and we have our replication configured. The import has configured our replication binlog files and positions, so we’re ready to start.

(On Doc/Secondary)

mysql.exe -u root mysql
mysql> START SLAVE;

Configure the Secondary so that it will always start the Replication automatically

Finally, we can remove the config entry we put into our Secondary so that it would start with a crippled Replication config; comment out the skip-slave-start in your my.ini (my.cnf):

(Secondary/Doc)

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

There’s no need to restart the Secondary, but if it does restart, it will automatically get back into replication.

Leave a Reply

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