Slave can't connect to master (replication, 4.0.23a)

[Sander Smeenk]

Hello!

I'm trying to set up replication between two servers and even though I
did everything according to the documentation, the slave keeps failing
to connect to the master like this:

| 050119 0:23:43 Slave SQL thread initialized, starting replication in
| log 'zwart-bin.001' at position 79, relay log './blauw-relay-bin.001'
| position: 4
| 050119 0:23:43 Slave I/O thread: error connecting to master
| 'r***@xxx.xxx.x xx.yyy:3306': Error: 'Access denied for user:
| 'r***@xxx.xxx.x xx.xxx' (Using password: YES)' errno: 1045
| retry-time: 60 retries: 86400

But when I try to connect with mysql-client myself, using the same host,
same user and same password, I get in correctly.

On master everything is looking cool:
| mysql> select version()\G
| version(): 4.0.23a-log
| mysql> show grants for repl\G
| Grants for repl@%: GRANT SELECT, RELOAD, PROCESS, FILE, SUPER,
| REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED BY
| PASSWORD 'xxxxxxxxxxxxxx xx'
| mysql> show master status\G
| File: zwart-bin.001
| Position: 177
| Binlog_do_db: this,that,here, there
| Binlog_ignore_d b:
| shell> cat /etc/my.cnf
| [mysqld]
| log-bin
| binlog-do-db = mysql
| binlog-do-db = beheer
| binlog-do-db = v
| binlog-do-db = products
| server-id = 1

On slave everything looks cool too:
| mysql> select version()\G
| version(): 4.0.23a
| shell> cat /etc/my.cnf
| [mysqld]
| server-id = 2
| master-host = xxx.xxx.xxx.xxx
| master-port = 3306
| master-user = repl
| master-password = xxxxxxx
| replicate-do-db = this
| replicate-do-db = that
| replicate-do-db = here
| replicate-do-db = there
| log-warnings
| mysql> show slave status\G
| Master_Host: xxx.xxx.xxx.xxx
| Master_User: repl
| Master_Port: 3306
| Connect_retry: 60
| Master_Log_File : zwart-bin.001
| Read_Master_Log _Pos: 79
| Relay_Log_File: blauw-relay-bin.001
| Relay_Log_Pos: 4
| Relay_Master_Lo g_File: zwart-bin.001
| Slave_IO_Runnin g: Yes
| Slave_SQL_Runni ng: Yes
| Replicate_do_db : this,that,here, there
| Replicate_ignor e_db:
| Last_errno: 0
| Last_error:
| Skip_counter: 0
| Exec_master_log _pos: 79
| Relay_log_space : 4

It says 'Slave_(IO|SQL) _Running: Yes' even though it's not according to
the processlist of master which shows no remote users doing binlog
dumps...

I have done everything according to the books. The GRANT originally was
'GRANT REPLICATION SLAVE ON *.* TO ...' but during my tries to fix this
I tried other grants with the privileges shown above... I locked the
tables, copied everything, recorded the position, etc, etc...

The weirdest thing for me is that I can login doing:

| shell> mysql -u repl -h xxx.xxx.xxx.xxx -p
| Password:
| mysql>

But the mysql slave server can't on its self?

Both mysqld's are linked against the same version of libraries, both are
4.0.23a, running on FreeBSD 4.10 x86 platform.

Can anybody tell me what is missing?

Thanks,
Sander.
--
Hey.. I'm done talkin'. Now check out my pretty!

[Jonathan]

On Tue, 18 Jan 2005 23:23:23 +0000 (UTC), Sander Smeenk
<ss*****@freshd ot.not> wrote:

Hello!

I'm trying to set up replication between two servers and even though I
did everything according to the documentation, the slave keeps failing
to connect to the master like this:

| 050119 0:23:43 Slave SQL thread initialized, starting replication in
| log 'zwart-bin.001' at position 79, relay log './blauw-relay-bin.001'
| position: 4
| 050119 0:23:43 Slave I/O thread: error connecting to master
| 'r***@xxx.xxx.x xx.yyy:3306': Error: 'Access denied for user:
| 'r***@xxx.xxx.x xx.xxx' (Using password: YES)' errno: 1045
| retry-time: 60 retries: 86400
cut

The weirdest thing for me is that I can login doing:

| shell> mysql -u repl -h xxx.xxx.xxx.xxx -p
| Password:
| mysql>
Are you trying to log in from the slave box with this command? Just to
be sure. ;-) Have you tried logging in by this command with the
replication host and user specified like so:

mysql -u re**@xxx.xxx.xx x.yyy -h xxx.xxx.xxx.xxx -p

Because I think that when you log in manually the hostname is not
specified and may be repl@localhost instead of re**@xxx.xxx.xx x.yyy.

I've set up replication on two boxes rather succesfully. Haven't had
these weird problems. I only used the permissions stated in the
manual.

But the mysql slave server can't on its self?

Both mysqld's are linked against the same version of libraries, both are
4.0.23a, running on FreeBSD 4.10 x86 platform.
Didn't you forget to flush the privileges on the master? What is the
result you get when you log in as root user on the mysql master server
and issue the following command:

select * from mysql.user where user='replicati on'\G

It should show you the details of your replication user. Don't worry
about the password it can not be decripted.Can anybody tell me what is missing?

Thanks,
Sander.

Jonathan

[Sander Smeenk]

According to Sander Smeenk <ss*****@freshd ot.not>:

Following up on my own post :)

| master-host = xxx.xxx.xxx.xxx
| Master_Host: xxx.xxx.xxx.xxx

This is quite odd...

Turns out that for some odd reason it won't work when I specify
master_host as an IP-address, but it WILL work when I put the hostname
in.

Anyone got an explanation for this?

It's not resolvers, because then it *SHOULD* work with IP addresses and
*NOT* with hostnames, right?

Anyways, it's replicating as we speak!

Sander.
--
Hey.. I'm done talkin'. Now check out my pretty!

[Jonathan]

On Wed, 19 Jan 2005 08:50:22 +0000 (UTC), Sander Smeenk
<j0*****@freshd ot.not> wrote:

According to Sander Smeenk <ss*****@freshd ot.not>:

Following up on my own post :)

| master-host = xxx.xxx.xxx.xxx
| Master_Host: xxx.xxx.xxx.xxx
This is quite odd...

Turns out that for some odd reason it won't work when I specify
master_host as an IP-address, but it WILL work when I put the hostname
in.

Anyone got an explanation for this?

It's not resolvers, because then it *SHOULD* work with IP addresses and
*NOT* with hostnames, right?

Because hostnames are static for every pc unless you specifically
change them. IP numbers can be assigned dynamically. The following can
occur in case your network assigns the IP address dynamically.

If a new IP address is assigned to your box you cannot log in to the
MySQL server anymore and if this old IP number is assigned to another
PC that PC is allowed to log in. It could be that the computer that is
allowed to log in now is used by someone which you don't even want to
specify access to on the MySQL server.

Therefore use hostnames instead of IP numbers
Anyways, it's replicating as we speak!

Sander.

Jonathan