mysql replication - stuck on errors.

Discussion in 'Questions and Answers' started by mtwiscool, Nov 24, 2013.

  1. mtwiscool

    mtwiscool New Member

    753
    45
    Jul 18, 2013
    i'm trying to set up mysql replication on my new website(under counstion)

    hers the error i get when i start the slave(and yes bind has been # out):


    mysql> start slave;
    Query OK, 0 rows affected, 1 warning (0.00 sec)

    mysql> show slave status\G;
    *************************** 1. row ***************************
    Slave_IO_State: Connecting to master
    Master_Host: 199.175.48.246
    Master_User: root
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File:
    Read_Master_Log_Pos: 4
    Relay_Log_File: mysqld-relay-bin.000006
    Relay_Log_Pos: 4
    Relay_Master_Log_File:
    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: 0
    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: 1045
    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)

    ERROR:
    No query specified



    if you want more info please reply if you want some config info.
     
  2. KS_Samuel

    KS_Samuel New Member Verified Provider

    25
    18
    Nov 20, 2013
    Silly usual questions.

    Can you ping the master?

    Is replication setup on the master?

    Can I see the output of netstat -tapn on the master?

    Is there a firewall in the way?

    I'd also highly discourage using root as the replication user. You can create a user with the replication privileges that have no access to the tables directly.
     
    Last edited by a moderator: Nov 24, 2013
  3. mtwiscool

    mtwiscool New Member

    753
    45
    Jul 18, 2013
    Can you ping the master?

    yes i can

    Is replication setup on the master?

    yes it is

    Can I see the output of netstat -tapn on the master?

    [email protected]:~# netstat -tapn
    Active Internet connections (servers and established)
    Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
    tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN      6354/apache2
    tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      331/sshd
    tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      17336/mysqld
    tcp        0      0 199.175.48.246:3306     192.227.164.226:54045   TIME_WAIT   -
    tcp        0      0 199.175.48.246:22       92.24.12.178:49258      ESTABLISHED 16216/0
    tcp6       0      0 :::22                   :::*                    LISTEN      331/sshd
    [email protected]:~#

    Is there a firewall in the way?

    none yet.

    and i tried to login to mysql from slave to master server and it worked but not for replication.
     
  4. KS_Samuel

    KS_Samuel New Member Verified Provider

    25
    18
    Nov 20, 2013
    Try setting up a replication user instead. Run this on the master


    GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'replication_password';

    and then re-setup replication on the slave.


    CHANGE MASTER TO master_host='MASTERIP', master_port=3306, master_user='replication', master_password='replication_password', master_log_file='LOG FILE', master_log_pos=POSITION;

    Be sure to replace MASTERIP, LOG FILE and POSITION with the appropriate values from.

    SHOW MASTER STATUS
     
    Last edited by a moderator: Nov 24, 2013
  5. mtwiscool

    mtwiscool New Member

    753
    45
    Jul 18, 2013
    where would , LOG FILE and POSITION be?
     
  6. KS_Samuel

    KS_Samuel New Member Verified Provider

    25
    18
    Nov 20, 2013
    Code:
    mysql> SHOW MASTER STATUS;
    +------------------+-----------+--------------+------------------+
    | File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+-----------+--------------+------------------+
    | mysql-bin.000025 | 382431892 |              |                  |
    +------------------+-----------+--------------+------------------+
    1 row in set (0.02 sec)
    
    
     
  7. KS_Samuel

    KS_Samuel New Member Verified Provider

    25
    18
    Nov 20, 2013
    mtwiscool and GIANT_CRAB like this.
  8. mtwiscool

    mtwiscool New Member

    753
    45
    Jul 18, 2013