MySQL replicationn error on inital install

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

  1. dgeho1

    dgeho1 Member HowtoForge Supporter

    the command was executed on server 1, (master) to connect to server 2 (slave) it was my understanding that this was ultimately a Master-Master type scenario...

    The instructions Till gave me earlier in this thread gave me the impression that I needed to execute on server 1,(maybe I misunderstood?)

    Just to make sure we are all on the same page

    Server #1 (Webserver0 - 10.3.192.20) Server 2 (WebServer2 10.3.192.21)

    As far as I am aware Server 1 is the true master

    Thanx

    Next step please
     
  2. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    Ok, the connection test with "mysql -u slaveuser -h xxxxx" has to be executed on the SLAVE to test it's connection to the MASTER, not the other way.
    The slave needs to be able to connect to the master to get it's updates.
     
  3. dgeho1

    dgeho1 Member HowtoForge Supporter

    so I executed the command on server #2 specifying the IP of server #1 and I was able to connect... I get the following..

    [email protected]:/home/dgeho1# mysql -u slaveuser -h 10.3.192.20 -p
    Enter password:
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 41
    Server version: 5.1.72-2-log (Debian)

    Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> show master status;
    ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation
    mysql>
     
  4. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    What I don't understand is:
    In your first post, you posted the SHOW SLAVE STATUS command.
    This is normally issued on the slave server. Yours states:
    Code:
     Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 86400
    So this would mean, the server you executed this, tries to connect to master 10.3.192.21 but you assumed the ip .20 is the master.
    I really don't get what your configuration really is :(
     
  5. dgeho1

    dgeho1 Member HowtoForge Supporter

    the install guide informs to check slave status from the master (server #1) 10.3.192.20

    http://www.howtoforge.com/installing...th-ispconfig-3

    On server 1:

    Log into MySQL on the shell with...

    mysql -u root -p

    ... and enter the MySQL root passord that you had choosen during mysql install. Then execute this commnd on the MySQL shell:

    GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY 'slave_user_password';
    FLUSH PRIVILEGES;
    quit;

    Replace 'slave_user_password' with a secure password that you want to use for the slave to connect to the master server. Replace this placeholder in the next steps with the password that you had choosen wherever the placeholder occurs.

    Now let's configure our 2 MySQL nodes:

    On server 1:

    vi /etc/mysql/my.cnf

    Search for the section that starts with [mysqld], and put the following options into it (commenting out all existing conflicting options):

    [...]
    [mysqld]
    server-id = 1
    replicate-same-server-id = 0
    auto-increment-increment = 2
    auto-increment-offset = 1

    master-host = 192.168.0.106
    master-user = slaveuser
    master-password = slave_user_password
    master-connect-retry = 60

    expire_logs_days = 10
    max_binlog_size = 500M
    log_bin = /var/log/mysql/mysql-bin.log
    [...]Then stop MySQL:

    /etc/init.d/mysql stop

    Now do nearly the same on server2...

    On server 2:

    vi /etc/mysql/my.cnf

    Search for the section that starts with [mysqld], and put the following options into it (commenting out all existing conflicting options):

    [...]
    [mysqld]
    server-id = 2
    replicate-same-server-id = 0
    auto-increment-increment = 2
    auto-increment-offset = 2

    master-host = 192.168.0.105
    master-user = slaveuser
    master-password = slave_user_password
    master-connect-retry = 60

    expire_logs_days = 10
    max_binlog_size = 500M
    log_bin = /var/log/mysql/mysql-bin.log
    [...]Then stop MySQL:

    /etc/init.d/mysql stop

    Now we have to sync the two mysql servers. We do this by copying over the mysql data directory from the master to the slave and also the debian configuration file that contains the debian-sys-maint user. This can be done as we stopped mysql before on both servers.

    On server 1:

    scp -pr /var/lib/mysql/* [email protected]:/var/lib/mysql/
    scp -pr /etc/mysql/debian.cnf [email protected]:/etc/mysql/debian.cnf

    Now we start MySQL on the master server again:

    /etc/init.d/mysql start

    Log into the MySQL shell as root user...

    mysql -u root -p

    ... and execute this command in the MySQL shell...

    SHOW MASTER STATUS;

    ... to get the MySQL master status:

    mysql> SHOW MASTER STATUS;
    +------------------+----------+--------------+------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000002 | 106 | | |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)

    The information that we need for the next step is the binlog file mysql-bin.000002 and the binlog position 106. We need the same details for server2 later below.

    Now execute this command in the MySQL shell on the master to connect it to the slave:

    STOP SLAVE;
    CHANGE MASTER TO MASTER_HOST='192.168.0.106', MASTER_USER='slaveuser', MASTER_PASSWORD='slave_user_password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106;
    START SLAVE;

    Then check the slave status:

    SHOW SLAVE STATUS \G

    It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output.

    On server 2:

    Log into the MySQL shell as root user...

    mysql -u root -p

    ... and execute this command in the MySQL shell:

    STOP SLAVE;
    CHANGE MASTER TO MASTER_HOST='192.168.0.105', MASTER_USER='slaveuser', MASTER_PASSWORD='slave_user_password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106;
    START SLAVE;

    Then check the slave status:

    SHOW SLAVE STATUS \G;
     
  6. dgeho1

    dgeho1 Member HowtoForge Supporter

    This tutorial describes the installation of a clustered web, email, database and DNS server to be used for redundancy, high availability and load balancing on Debian 6 with the ISPConfig 3 control panel. MySQL Master/Master replication will be used to replicate the MySQL client databases between the servers and Unison will be used to Sync the /var/www (websites) and /var/vmail (email account data) folders
     
  7. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    Ok, sorry. overlooked the information about master-master replication.

    Have you compared both slave status commands on both servers? does it work the other direction?
     
  8. dgeho1

    dgeho1 Member HowtoForge Supporter

    here is the result from server number 1 10.3.192.20

    mysql> show slave status \G;
    *************************** 1. row ***************************
    Slave_IO_State:
    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.000019
    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: 1236
    Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
    Last_SQL_Errno: 0
    Last_SQL_Error:
    1 row in set (0.00 sec)

    here is the result from server 2 10.3.192.21

    ysql> show slave status \G;
    *************************** 1. row ***************************
    Slave_IO_State:
    Master_Host: 10.3.192.21
    Master_User: slave
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000042
    Read_Master_Log_Pos: 106
    Relay_Log_File: mysqld-relay-bin.000001
    Relay_Log_Pos: 4
    Relay_Master_Log_File: mysql-bin.000042
    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: 106
    Relay_Log_Space: 0
    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: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:
    1 row in set (0.00 sec)
     
  9. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    Ok, so:
    1.) is it correct, that the user is "slave" instead of "slaveuser" on the second server? Maybe that's one reason it can't connect to replicate.

    2.) may it be that binary logging is not enabled on the second server?
    "Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'"
     
  10. dgeho1

    dgeho1 Member HowtoForge Supporter

    I hadn't noticed the differences... what's best way to correct?
     
  11. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    Repeat the steps with "CHANGE MASTER TO....." for the second server.
    Check the mysql config if binary logging is enabled on both.
    After that - restart both mysql servers and check the slave status again.
     
  12. dgeho1

    dgeho1 Member HowtoForge Supporter

    I Looked at the .conf files and it looks like bin logging should be turned on (not exactly sure what I'm looking for)

    When trying to change master on server 2 (10.3.192.21) I get the following result
    (I got the .binlog filename by showing master status on server #1 per the instructions)


    mysql> CHANGE MASTER TO MASTER_HOST='10.3.192.20', MASTER_USER='slaveuser', MASTER_PASSWORD='nc3mx-bagxlrblk', MASTER_LOG_FILE='mysql-bin.000068', MASTER_LOG_POS=106;
    ERROR 29 (HY000): File './mysqld-relay-bin.000019' not found (Errcode: 13)
     
  13. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    Have you done a STOP SLAVE before?
     
  14. dgeho1

    dgeho1 Member HowtoForge Supporter

    yes stop slave
     
  15. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    It seems your installation is quite messed up somehow. :(
    I have no idea how to fix it other than completely reconfigure the slave mysql (second master server).
     
  16. dgeho1

    dgeho1 Member HowtoForge Supporter

    So what is my best option?

    Reformat drive and start over on both servers?
     
  17. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    Id first try just uninstalling mysql on the second server that has the problems, delete the /var/lib/mysql and reinstall and reconfigure mysql after that.
    Maybe you can avoid reinstalling everything in this way.
     
  18. dgeho1

    dgeho1 Member HowtoForge Supporter

    what is the best way to uninstall? Plz provide commands so I am sure to get it right.

    Thanks
     
  19. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    BACKUP first if you have any data to save. They might and probably will be lost ;)

    If you want to make a complete uninstall of mysql you might use
    apt-get purge mysql-server*
    But be cautious! This deletes all files for mysql, all databases and config.
    Check if /var/lib/mysql doesn't contain any more data and move away (backup) /etc/mysql if it still exists.

    Then reinstall with apt-get install mysql-server-5.0 (or 5.1, am not sure what's on debian 6). And configure it following the howto. I hope it works out this time.
     
  20. dgeho1

    dgeho1 Member HowtoForge Supporter

    I tried the method you mentioned last and it didn't work.. so I formatted disks and started it over.

    still not working.. when I get to the section of the guide that tells me to make sure that I/o is running

    1 row in set (0.00 sec)

    The information that we need for the next step is the binlog file mysql-bin.000002 and the binlog position 106. We need the same details for server2 later below.

    Now execute this command in the MySQL shell on the master to connect it to the slave:

    STOP SLAVE;
    CHANGE MASTER TO MASTER_HOST='192.168.0.106', MASTER_USER='slaveuser', MASTER_PASSWORD='slave_user_password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106;
    START SLAVE;

    Then check the slave status:

    SHOW SLAVE STATUS \G

    It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output

    I get the following error


    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.000001
    Read_Master_Log_Pos: 106
    Relay_Log_File: mysqld-relay-bin.000001
    Relay_Log_Pos: 4
    Relay_Master_Log_File: mysql-bin.000001
    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: 106
    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)

    I've done everything exactly as the guide says..
     

Share This Page