Replication slave receiving errors
I'm attempting to setup replication between our main MySQL server and three
slave servers (which are part of a load-balancing web cluster). On our master
server, I have added the following lines to the my.cnf under the heading
[mysqld]:
log-bin
server-id = 1
After doing so and restarting the MySQL server process, I issued several grant
statements:
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'node1' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'node2' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'node3' IDENTIFIED BY 'password';
(The hostnames are all recorded in /etc/hosts.) I then issued the FLUSH
PRIVILEGES command to activate the permissions.
I then issued FLUSH TABLES WITH READ LOCK and tar'd up the relevant directories
for the databases I want to replicate out of /var/lib/mysql. I ran SHOW MASTER
STATUS and recorded the log file and position, and issued UNLOCK TABLES.
On each node, I then edited the my.cnf to add the following lines under
[mysqld]:
log-bin
server-id = 10X (wher X is the node number)
set-variable = replicate-ignore-db=test
I untarred the databases from the master into /var/lib/mysql, and started the
mysql daemon. Within the mysql interface, I issued the following directive:
CHANGE MASTER TO
MASTER_HOST='master_hostname',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='master-log',
MASTER_LOG_POS=logposition;
where 'master-log' was the file I recorded earlier, and logposition the position
recorded. I then issued START SLAVE.
SHOW SLAVE STATUS shows that both the slave IO and SQL are running. However,
SHOW PROCESSLIST shows:
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 34611
State: Connecting to master
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 34611
State: Has read all relay log; waiting for the I/O slave thread to update it
Info: NULL
Additionally, the mysqld.err log on that machine shows the following:
050301 23:24:41 Slave I/O thread: error connecting to master
'test@master_hostname:3306': Error: 'Access denied for user:
'test@ip_of_slave' (Using password: YES)' errno: 1045 retry-time: 60
retries: 86400
Why is replication using the user 'test' when the user 'repl' has been
indicated? Have I forgotten some necessary permissions?
When I go over to the master host, SHOW SLAVE HOSTS returns an empty set.
I've tested to see if updates are making it from the master to the slave, but so
far, I haven't seen any updates happen on the slaves.
What am I missing? From what I can tell, I followed the replication instructions
from the manual exactly. I've seen some similar questions posted to this forum
-- but either no solutions have been presented, or they have not been
appropriate for my situation.
Any suggestions?
--
Matthew Weier O'Phinney
Webmaster and IT Specialist
National Gardening Association