We help IT Professionals succeed at work.

Trying to set up MySQL Replication

itnifl
itnifl asked
on
729 Views
Last Modified: 2016-07-21
I do the following on both servers:
mysql>CREATE USER 'replicator'@'%' IDENTIFIED BY 'some_pass';
mysql>Grant all privileges on *.* to replicator@'%' with grant option;

Open in new window


Then I do the following on the master:
mysqluc> mysqlreplicate --master='replicator'@192.168.92.130:3306 --slave='replicator'@192.168.92.131:3306 --rpl-user replicator:some_pass
WARNING: Using a password on the command line interface can be insecure.
# master on 192.168.92.130: ...
ERROR: Access denied for user 'replicator'@'mysql-1-PC.localdomain' (using password: NO)

Open in new window


I thought 'replicator'@'%' would have access from all hosts?
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2004
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
itniflProgrammer

Author

Commented:
OK, thanks. Now I did this:
mysqluc> mysqlreplicate --master='replicator:some_pass'@192.168.92.130:3306 --slave='replicator:some_pass'@192.168.92.131:3306 --rpl-user replicator:some_pass
WARNING: Using a password on the command line interface can be insecure.
# master on 192.168.92.130: ... connected.
# slave on 192.168.92.131: ... connected.
# Checking for binary logging on master...
# Setting up replication...
ERROR: failed to sync slave with master.
ERROR: Cannot setup replication.

Open in new window


In the logs, I see this:

Timestamp, Thread, Type, Details
2016-07-21T07:22:16, 3, Note, Aborted connection 3 to db: 'unconnected' user: 'replicator' host: 'mysql-2-PC' (Got an error reading communication packets)
2016-07-21T07:22:16, 4, Note, Aborted connection 4 to db: 'unconnected' user: 'replicator' host: 'mysql-2-PC' (Got an error reading communication packets)
2016-07-21T07:22:26, 2, Note, Aborted connection 2 to db: 'unconnected' user: 'replicator' host: 'mysql-1-PC.localdomain' (Got an error reading communication packets)
2016-07-21T07:23:16, 5, Note, Aborted connection 5 to db: 'unconnected' user: 'replicator' host: 'mysql-2-PC' (Got an error reading communication packets)
2016-07-21T07:24:16, 8, Note, Aborted connection 8 to db: 'unconnected' user: 'replicator' host: 'mysql-2-PC' (Got an error reading communication packets)
itniflProgrammer

Author

Commented:
Seems like replicator@192.168.1.131 gets auto created at the master when mysqlreplicate is executed. It ignores replicator@'%'. This user replicator@192.168.1.131 did not have sufficient access rights. Giving these enabled mysqlreplicate to finish the setup.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.