[TriLUG] MySQL Replication

[Brian McCullough]

Thank you, All.  I am slowly progressing.



On Thu, Jan 29, 2015 at 03:58:37PM -0500, Igor Partola wrote:
> Here's what I'd do:
> 
> 1. Disable DNS lookups inside MySQL (see skip-name-resolve option). You
> want this off because it both confuses permissions AND makes things
> incredibly slow. No idea why MySQL insists on having it on by default.
> 
> 2. Set up permissions based on IP addresses.

That's what I had to start with, and then started running into those
"can't resolve" issues.


> 3. Use telnet to connect from slave to master like so: `telnet $MASTER_IP
> 3306`. If this doesn't work, make sure the master is really listening on
> that IP and that port. Of course, make sure the master is running at all.

Yes, that was one of the things I did before I asked for help. That, and ping.


> 4. If the master is listening on that port (you can connect from the master
> host to `telnet $MASTER_IP 3306` where $MASTER_IP is the 10.x.x.x address,
> and you get a "remote host closed connection" then the permissions are
> incorrect. Don't forget to FLUSH PRIVILEGES. Remember, MySQL will drop a
> connection even before you authenticate if there is no user that's set up
> from the client's IP.

Nope, not that.


> 5. If you are able to connect via telnet, then try from the slave server
> `mysql -u $USERNAME -p --host $MASTER_IP`. Fill in $USERNAME and password.
> (BTW, make sure you don't have a ~/my.cnf with the wrong password
> somewhere). If this doesn't work, check or reset the password.

That works ( now that I have the right password. ) ( oops ).


So, let me fix the dns item, your number 1, and see where I get.


Hmmm.  New messages in error.log, telling me that my "name" entries in
my "user" table will be ignored now that I have skip-name-resolve.



> BTW, get ready for data inconsistencies. MySQL replication is a fickle
> beast. Reading the O'Reilly animal book on MySQL, or at least the relevant
> chapters, will be very helpful.

This is to make sure that the Joomla user table is common to both
machines, to keep CiviCRM happy.



> Igor


Brian