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 Network Access

MySQL Replication Walkthru: Enable Network Access

dataflow, howto Add comments

In my replication setup, I needed to make a backup, and I needed to enable TCP/IP access eventually, so I did them as a single step.

Primary: Sleepy
Secondary: Doc
MySQL: 5.1.41-enterprise-commercial-pro
OS: Windows 2008R2

In order to allow Doc (Secondary) access into Sleepy (Primary), Sleepy had to accept remote TCP clients. The process for that was:

  1. create a username/password pair for a new remote user (wildcard host, or a specific host)
  2. configure MySQL to accept remote client access

Create a User/Pass Pair

I wanted to ensure I could access the server’s authentication, so I restarted with “–skip-grant-tables”. In my case, I added this to the WINDOWSmy.ini, but Linux and Unix-like users (including BSD) might find /etc/my.cnf or /etc/inet/my.cnf. My config looked like:

(On Sleepy/Primary)

[mysqld]
...
...
skip-grant-tables
...

Restart the server.

Next, I connected and ran a GRANT command:

(On Sleepy/Primary)

mysql.exe -u root mysql
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'R3plPassw0rd';
mysql> GRANT SELECT ON *.* TO 'repl'@'%';
mysql> FLUSH PRIVILEGES;

Note: “mysql.exe” is obviously “mysql” on Unix-like systems. “%” is a wildcard in MySQL’s world.

The FLUSH PRIVILEGES is not quite necessary because in our next step, we’ll be restarting the database anyhow.

If you cannot connect, check that the unix-socket is present, and check for socket configs in the my.ini (my.cnf).

Configure MySQL to Accept Remote Client Access
In order to open up the external port (which might already be open, depending on your configuration), I commented out the bind-address in my my.ini (my.cnf) config file:

(On Sleepy/Primary)

[mysqld]
...
...
#bind-address=127.0.0.1
skip-grant-tables
...

After I restarted, I noticed that I could connect using “-h 127.0.0.1” (as I could before) but also using the external address (192.168.44.3):

(On Sleepy/Primary)

mysql.exe -u root -h 127.0.0.1 mysql
mysql> exit
mysql.exe -u repl --password=R3plPassw0rd -h 127.0.0.1 mysql
mysql> exit
mysql.exe -u root -h 127.0.0.1 mysql
(fails, as expected)
mysql.exe -u repl --password=R3plPassw0rd -h 192.168.44.3 mysql
mysql> exit

(On Doc/Secondary)

mysql.exe -u repl --password=R3plPassw0rd -h 192.168.44.3 mysql
mysql> exit

If you cannot connect with “-h 127.0.0.1”, check that the “bind-address” is defined properly or absent completely from the my.ini (my.cnf) file, and that you have restarted the server since you made that change. “netstat -n” would confirm whether mysql is listening on port 3306. “telnet 127.0.0.1 3306” or “nc 127.0.0.1 3306” would confirm whether MySQL is available on that port (or something else is).

If you cannot connect with your external IP address, check that you have the right address, and confirm (using telnet or nc) that you have a service responding there.

If that works fine, comment out your “skip-grant” and restart, then recheck with the same OS-level mysql(.exe) statements as above. Connectivity should work and fail as above.

(On Sleepy/Primary)

[mysqld]
...
#bind-address=127.0.0.1
#skip-grant-tables
...

One Response to “MySQL Replication Walkthru: Enable Network Access”

  1. MySQL Replication Walkthru: Enable Replication Says:

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

Leave a Reply

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