MySQL replicationn error on inital install

Discussion in 'ISPConfig 3 Priority Support' started by dgeho1, Oct 31, 2013.

  1. dgeho1

    dgeho1 Member HowtoForge Supporter

    I am working thru the http://www.howtoforge.com/installin...tabase-cluster-on-debian-6.0-with-ispconfig-3 manual installing two server mirror. step two

    Debian 6.08

    MySQL Your MySQL connection id is 41
    Server version: 5.1.72-2-log (Debian)

    mysql> SHOW SLAVE STATUS \G;
    *************************** 1. row ***************************
    Slave_IO_State: Connecting to master
    Master_Host: 10.3.192.21
    Master_User: slaveuser
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000046
    Read_Master_Log_Pos: 106
    Relay_Log_File: mysqld-relay-bin.000001
    Relay_Log_Pos: 4
    Relay_Master_Log_File: mysql-bin.000046
    Slave_IO_Running: No
    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: 106
    Relay_Log_Space: 262
    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
    Master_SSL_Verify_Server_Cert: No
    Last_IO_Errno: 2013
    Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 86400
    Last_SQL_Errno: 0
    Last_SQL_Error:
    1 row in set (0.00 sec)
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    Is this the first or second server? Is mysql started on the oher server?
     
  3. dgeho1

    dgeho1 Member HowtoForge Supporter

    this is server #1 MySQL is running on server 2
     
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    Th master is not able to connect to the slave. Did you create the user "slaveuser" on the slave server already? is here a firewall between the servers that might block mysql traffic?
     
  5. dgeho1

    dgeho1 Member HowtoForge Supporter

    Slave user created on both servers, no firewall, same vlan on same switch

    Can ping eachother
     
  6. dgeho1

    dgeho1 Member HowtoForge Supporter

    I stopped at the step that indicates that It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output.

    the synchronization step just before this appeared to run successfully (does that mean we have communication?)
     
  7. till

    till Super Moderator Staff Member ISPConfig Developer

    restart mysql on the slave. Does it work then when you execute this command on the first server (enter the password of the slaveuser when the mysql command asks for it):

    mysql -u slaveuser -h 10.3.192.21 -p
     
  8. dgeho1

    dgeho1 Member HowtoForge Supporter

    I get the MySQL> prompt, It works
     
  9. dgeho1

    dgeho1 Member HowtoForge Supporter

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

    mysql> SHOW SLAVE STATUS;
    ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation
    MySQL>
     
  10. till

    till Super Moderator Staff Member ISPConfig Developer

    Thats ok, the important part is that the login works. You tested this command on server 1 to connect to server 2 and not on server2? The slave user is limited to replication, so you cant use it to show the status.
     
  11. dgeho1

    dgeho1 Member HowtoForge Supporter

    Ok so we have confirmed that we can connect and login, and yes I ran the command on server 1 to connect to server 2 (not directly on server 2)so your comment about not being able to show status is correct.

    what is our next step?
     
  12. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    What says
    SHOW GRANTS FOR 'slaveuser'@'slaveserver.com';
    executed on the master?
     
  13. dgeho1

    dgeho1 Member HowtoForge Supporter

    I tried it both by ip and by name to eliminate potential name resolution issue

    SHOW GRANTS FOR 'slaveuser'@'10.3.192.21';
    ERROR 1141 (42000): There is no such grant defined for user 'slaveuser' on host '10.3.192.21'
    mysql> SHOW GRANTS FOR 'slaveuser'@'WebServer2.dmgenterprises.info';
    ERROR 1141 (42000): There is no such grant defined for user 'slaveuser' on host 'webserver2.dmgenterprises.info'
     
  14. till

    till Super Moderator Staff Member ISPConfig Developer

    Please redo this on both servers:

    and then try again the command that Croydon posted.
     
  15. dgeho1

    dgeho1 Member HowtoForge Supporter

    after executing the grant command on both servers, and restarting MySQL I am still getting the same error msg

    I tried it both by ip and by name to eliminate potential name resolution issue

    SHOW GRANTS FOR 'slaveuser'@'10.3.192.21';
    ERROR 1141 (42000): There is no such grant defined for user 'slaveuser' on host '10.3.192.21'
    mysql> SHOW GRANTS FOR 'slaveuser'@'WebServer2.dmgenterprises.info';
    ERROR 1141 (42000): There is no such grant defined for user 'slaveuser' on host 'webserver2.dmgenterprises.info'
     
  16. till

    till Super Moderator Staff Member ISPConfig Developer

    You are logged in as mysql root user when you execute "SHOW GRANTS FOR 'slaveuser'@'10.3.192.21';"?
     
  17. dgeho1

    dgeho1 Member HowtoForge Supporter

    if the following command logs me in as root to the MySQL console then yes..

    mysql -u root -p
     
  18. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    You have tp make a SHOW GRANTS FOR 'slaveuser'@'%' if you want to see the grants set as you didn't set them for the ip address, but for "all hosts".
     
  19. dgeho1

    dgeho1 Member HowtoForge Supporter

    results from server 1 -WebServer0 - 10.3.192.20

    mysql> SHOW GRANTS FOR 'slaveuser'@'%'
    -> ;
    +----------------------------------------------------------------------------------------------------------------------+
    | Grants for slaveuser@% |
    +----------------------------------------------------------------------------------------------------------------------+
    | GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY PASSWORD '*7EF5FCE2D07AEDBDF2399F02CB7A6C8437F9DA82' |
    +----------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    Results from server 2 - WebServer2 - 10.3.192.21

    mysql> SHOW GRANTS FOR 'slaveuser'@'%';
    +----------------------------------------------------------------------------------------------------------------------+
    | Grants for slaveuser@% |
    +----------------------------------------------------------------------------------------------------------------------+
    | GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY PASSWORD '*7EF5FCE2D07AEDBDF2399F02CB7A6C8437F9DA82' |
    +----------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    What's our next step?
     
  20. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    Just to be sure:
    You said you used the mysql -h command to connect to server 2 from server 1 - but to test the replication user access you have to connect from slave to master.
    Did you do it the wrong way or was the information in the quoted post just a mistake?
    Or is your master server 2 and slave server 1 (quite irritating ;) )?
     

Share This Page