Error connecting to Master MySQL server during replication

Thanks for your resposne ushastry.

on my slave box, it looks like i can connect fine to my master:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql.exe -h10.0.0.233 -udrayer -pmypass
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.67-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql>

I believe my slave is now connected to my master. I ran this command:

mysql> show slave status\G;
ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privil
ge for this operation
ERROR:
No query specified

Looks like a permission issue? But I also re-ran the grant replication... command on my slave.

From the slave as root:

mysql> show slave status\G;
*************************** 1. row ***************************
             Slave_IO_State:
                Master_Host: test2k3-1
                Master_User: drayer
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File:
        Read_Master_Log_Pos: 4
             Relay_Log_File: test2k3-1-relay-bin.000010
              Relay_Log_Pos: 98
      Relay_Master_Log_File:
           Slave_IO_Running: No
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 0
            Relay_Log_Space: 98
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL
1 row in set (0.00 sec)

ERROR:
No query specified

Here is part of my test2k3-1.err(SLAVE) log:

090806  8:40:15  InnoDB: Started; log sequence number 0 43655
090806  8:40:15 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=test2k3-1-relay-bin' to avoid this problem.
090806  8:40:15 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log '.\test2k3-1-relay-bin.000009' position: 98
090806  8:40:15 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.67-community-nt'  socket: ''  port: 3306  MySQL Community Edition (GPL)
090806  8:40:15 [ERROR] Slave I/O thread: error connecting to master 'drayer@test2k3-1:3306': Error: 'Access denied for user 'drayer'@'test2k3-1.drayerpt.lcl' (using password: YES)'  errno: 1045  retry-time: 60  retries: 86400
090806  8:42:15 [Note] Slave I/O thread killed while connecting to master
090806  8:42:15 [Note] Slave I/O thread exiting, read up to log 'FIRST', position 4
090806  8:42:15 [Note] Error reading relay log event: slave SQL thread was killed
090806  9:00:30 [ERROR] Slave I/O thread: error connecting to master 'drayer@test2k3-1:3306': Error: 'Access denied for user 'drayer'@'test2k3-1.drayerpt.lcl' (using password: YES)'  errno: 1045  retry-time: 60  retries: 86400
090806  9:00:30 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log '.\test2k3-1-relay-bin.000010' position: 98
090806  9:01:30 [Note] Slave I/O thread killed while connecting to master
090806  9:01:30 [Note] Slave I/O thread exiting, read up to log 'FIRST', position 4
090806  9:01:30 [Note] Error reading relay log event: slave SQL thread was killed


Last few from my MASTER log:

090806  8:42:43 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Shutdown complete

090806  8:43:39 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=test2k3-2-bin' to avoid this problem.
090806  8:43:42  InnoDB: Started; log sequence number 0 67341
090806  8:43:42 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=test2k3-2-relay-bin' to avoid this problem.
090806  8:43:42 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.67-community-nt-log'  socket: ''  port: 3306  MySQL Community Edition (GPL)

Here is also the grants for my user:

mysql> show grants for drayer;
+-------------------------------------------------------------------------------
------------------------------------+
| Grants for drayer@%
                                    |
+-------------------------------------------------------------------------------
------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'drayer'@'%' IDENTIFIED BY PASSWORD '*5E79C1
E27562AD963ABAC4C4EAB95CF15C1CD3B4' |
+-------------------------------------------------------------------------------
------------------------------------+
1 row in set (0.00 sec)

I don't see Master_Log_File - binary log details from master in the show slave status command..  

Pls Post both master & slaves my.ini files .. I suspect whether or not the server id is set for slave and also the binary log files

My Slave my.ini file (i took out all the comments for readability):


[client]

port=3306

[mysql]

default-character-set=latin1



[mysqld]

port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"
default-character-set=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=100

query_cache_size=0

table_cache=256

tmp_table_size=103M


thread_cache_size=8

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=205M

key_buffer_size=175M

read_buffer_size=64K
read_rnd_buffer_size=256K

sort_buffer_size=256K


innodb_additional_mem_pool_size=7M

innodb_log_buffer_size=3498K

innodb_buffer_pool_size=339M

innodb_log_file_size=170M

innodb_thread_concurrency=8

master-host=Test2k3-2
master-user=drayer
master-password=l84ad8
server-id=2


My master my.ini file:

[client]

port=3306

[mysql]

default-character-set=latin1


[mysqld]

port=3306



basedir="C:/Program Files/MySQL/MySQL Server 5.0/"


datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"

default-character-set=latin1

default-storage-engine=INNODB


sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"


max_connections=100


query_cache_size=0


table_cache=256

tmp_table_size=18M


thread_cache_size=8


myisam_max_sort_file_size=100G


myisam_sort_buffer_size=35M


key_buffer_size=25M


read_buffer_size=64K
read_rnd_buffer_size=256K


sort_buffer_size=256K


innodb_additional_mem_pool_size=2M


innodb_flush_log_at_trx_commit=1


innodb_log_buffer_size=1M


innodb_buffer_pool_size=47M


innodb_log_file_size=24M


innodb_thread_concurrency=8
log-bin
server-id=1

Everything seems to be fine as for as the config files are concerned... Hmmm so you were trying LOAD DATA from MASTER - Since the current implementation of LOAD DATA FROM MASTER and LOAD TABLE FROM MASTER  is very limited, these statements are deprecated in versions 4.1 of MySQL and above .

I suggest you to do this

from slave server -

1. Take master server dump

mysqldump -uuser -ppassword --master-data=1 --all-databases > /path/to/complete.dmp.sql

2. Import it

mysql -uuser -ppassword < /path/to/complete.dmp.sql

Once the above import is completed you can check the status from slave as

show slave status\G

Yes, that is the command I was using.

I just tried to do a mysqldump from the slave to the master and used root credentials, and it works fine. It seems to be an issue with my user.

What i don't get is it seems like a permission issue on my 'drayer' user. But he has good access:

mysql> show grants for drayer\G;
*************************** 1. row ***************************
Grants for drayer@%: GRANT SELECT, RELOAD, PROCESS, FILE, SUPER, REPLICATION SLA
VE, REPLICATION CLIENT ON *.* TO 'drayer'@'%' IDENTIFIED BY PASSWORD '*5E79C1E27
562AD963ABAC4C4EAB95CF15C1CD3B4'
1 row in set (0.00 sec)

ERROR:
No query specified

For reference sake, here is my master.info file on my SLAVE:

14

4
test2k3-1
drayer
l84ad8
3306
60
0

I just changed the line in the SLAVE master.info from 'test2k3-1' to 'test2k3-2' and i got a new error, a more promising error(!) when I try to load data:

net error reading from master

My master.info file on my slave now reads:

14
test2k3-2-bin.000003
430
test2k3-2
drayer
l84ad8
3306
60
0

After stopping and starting slave, the status now reads:

mysql> show slave status\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: test2k3-2
                Master_User: drayer
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: test2k3-2-bin.000003
        Read_Master_Log_Pos: 505
             Relay_Log_File: test2k3-1-relay-bin.000016
              Relay_Log_Pos: 314
      Relay_Master_Log_File: test2k3-2-bin.000003
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 505
            Relay_Log_Space: 314
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.01 sec)

ERROR:
No query specified

Pls confirm whether you have synced the slave as I shown in mt prev command ..taking dump and importing?   also instead of host anme better use ip address

This seems to be fine...

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: test2k3-2
                Master_User: drayer
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: test2k3-2-bin.000003
        Read_Master_Log_Pos: 505
             Relay_Log_File: test2k3-1-relay-bin.000016
              Relay_Log_Pos: 314
      Relay_Master_Log_File: test2k3-2-bin.000003
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 505
            Relay_Log_Space: 314
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.01 sec)

ushastry -

I see that the status has now changed, which looks like my drayer user has connected to my master. It seems to be waiting for something on the master. Is there something I need to do on the master?

Nothing to worry.. This tells you what the thread is doing: trying to connect to the master, waiting for events from the master, reconnecting to the master etc etc...

The thing which you have to keep an eye on are -

           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes

and

  Seconds_Behind_Master: 0

Excellent, so my databases are replicating?

Yes... :-)  

Compare one of the Masters table count with that of the slaves table count..

excellent. it looks like it is working. thank you for your help :)