MySQL Replikation Slave I/O Error 1045

L

linfox

New Member
Hallo alle zusammen,

mein MySQL Replikation läuft nicht mehr einwandfrei. Auf dem Server soll eine Webseite (Anwendung) für Ausfälle mit einer IP-Cluster laufend. Dabei soll die Datenbank synchron gehalten werden.
Mein Kollege davor hatte das alles eingerichtet und es hat auch funktioniert, jedoch ist mir aufgefallen, nachdem er nicht bei uns ist, das die Datenbank vom Slave kleiner ist als die vom Master-Server.


Ich habe zwei externe Root-Server, die mit Windows 2012 R2 installiert sind. Auf den beiden Servern ist MySQL 5.7 und PHP-Version 4.7 installiert. In PHP habe ich dann noch ein weiteren User als Slave stehen.

Daher habe mich dann mal gewagt mir das anzuschauen.

Hierbei habe ich mich an diese Lösungen befolgt:

https://www.howtoforge.com/how-to-repair-mysql-replication

Danach habe wie befolgt in der Log-Date geschaut:
-----------------
Code: 
[I]
2017-07-21T12:30:18.045601Z 1 [Note] Slave I/O thread for channel '' killed while connecting to master
2017-07-21T12:30:18.047602Z 1 [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.000005', position 8127820
2017-07-21T12:32:59.345096Z 48 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2017-07-21T12:32:59.364174Z 49 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2017-07-21T12:32:59.366144Z 49 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000005' at position 466159, relay log 'D:\mysql\relay.000018' position: 29679
2017-07-21T12:32:59.369146Z 49 [Note] 'SQL_SLAVE_SKIP_COUNTER=1' executed at relay_log_file='D:\mysql\relay.000018', relay_log_pos='29679', master_log_name='mysql-bin.000005', master_log_pos='466159' and new position at relay_log_file='D:\mysql\relay.000018', relay_log_pos='30026', master_log_name='mysql-bin.000005', master_log_pos='466506' 
2017-07-21T12:32:59.492296Z 48 [ERROR] Slave I/O for channel '': error connecting to master 'slave@masterhost.net:3306' - retry-time: 60  retries: 1, Error_code: 1045
2017-07-21T12:33:02.554922Z 49 [ERROR] Slave SQL for channel '': Could not execute Write_rows event on table test_db01.auth_codes; Duplicate entry '5' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000005, end_log_pos 523496, Error_code: 1062
2017-07-21T12:33:02.556923Z 49 [Warning] Slave: Duplicate entry '5' for key 'PRIMARY' Error_code: 1062
2017-07-21T12:33:02.557923Z 49 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000005' position 523180
2017-07-21T12:33:59.636938Z 48 [ERROR] Slave I/O for channel '': error connecting to master 'slave@masterhost.net:3306' - retry-time: 60  retries: 2, Error_code: 1045
2017-07-21T12:34:59.781879Z 48 [ERROR] Slave I/O for channel '': error connecting to master 'slave@masterhost.net:3306' - retry-time: 60  retries: 3, Error_code: 1045
2017-07-21T12:35:08.736104Z 48 [Note] Slave I/O thread for channel '' killed while connecting to master
2017-07-21T12:35:08.737105Z 48 [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.000005', position 8127820
2017-07-21T12:35:20.232639Z 50 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2017-07-21T12:35:20.254659Z 51 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2017-07-21T12:35:20.256662Z 51 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000005' at position 523180, relay log 'D:\mysql\relay.000018' position: 86700
2017-07-21T12:35:20.258668Z 51 [Note] 'SQL_SLAVE_SKIP_COUNTER=2' executed at relay_log_file='D:\mysql\relay.000018', relay_log_pos='86700', master_log_name='mysql-bin.000005', master_log_pos='523180' and new position at relay_log_file='D:\mysql\relay.000018', relay_log_pos='87047', master_log_name='mysql-bin.000005', master_log_pos='523527' 
2017-07-21T12:35:20.378806Z 50 [ERROR] Slave I/O for channel '': error connecting to master 'slave@masterhost.net:3306' - retry-time: 60  retries: 1, Error_code: 1045
2017-07-21T12:35:20.738127Z 51 [ERROR] Slave SQL for channel '': Could not execute Write_rows event on table test_db01.auth_codes; Duplicate entry '6' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000005, end_log_pos 529115, Error_code: 1062
2017-07-21T12:35:20.741131Z 51 [Warning] Slave: Duplicate entry '6' for key 'PRIMARY' Error_code: 1062
2017-07-21T12:35:20.742132Z 51 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000005' position 528799
2017-07-21T12:35:42.520054Z 50 [Note] Slave I/O thread for channel '' killed while connecting to master
2017-07-21T12:35:42.521083Z 50 [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.000005', position 8127820
2017-07-21T12:36:09.843707Z 52 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2017-07-21T12:36:09.926486Z 53 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2017-07-21T12:36:09.928488Z 53 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000005' at position 528799, relay log 'D:\mysql\relay.000018' position: 92319
2017-07-21T12:36:09.931496Z 53 [Note] 'SQL_SLAVE_SKIP_COUNTER=3' executed at relay_log_file='D:\mysql\relay.000018', relay_log_pos='92319', master_log_name='mysql-bin.000005', master_log_pos='528799' and new position at relay_log_file='D:\mysql\relay.000018', relay_log_pos='92666', master_log_name='mysql-bin.000005', master_log_pos='529146' [/I]
Nun kriege ich diese Fehlermeldung nun noch ganze Zeit:
2017-07-21T12:36:09.994579Z 52 [ERROR] Slave I/O for channel '': error connecting to master 'masterhost.net:3306' - retry-time: 60 retries: 1, Error_code: 1045


Das hier ist der aktuelle Serverstatus vom Slave:
Code: 
[I]mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: Master.host.net #fiktiver name 
                  Master_User: slave_sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 8127820
               Relay_Log_File: relay.000024
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: db_01
          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: 8127820
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: D:/mysql/cert/ca-cert.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert: D:/mysql/cert/client-cert.pem
            Master_SSL_Cipher:
               Master_SSL_Key: D:/mysql/cert/client-key.pem
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1045
                Last_IO_Error: error connecting to master 'slave_sync@master.host.net:3306' - retry-time: 60  retries: 3988
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID: *******
             Master_Info_File: C:\ProgramData\MySQL\MySQL Server 5.7\Data\master
.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more up
dates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 170724 09:12:49
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>[/I]


Ich hab sonst noch diese Fehlermeldung in MySQL Workbench im Anhang gepostet.

Vielen Danke für eure Zeit.

Wenn ihr noch mehr Information braucht, sagt Bescheid..
Vielleicht reicht da ja schon aus..

VG

linus
 

Attachments

  • fehlermeldung workbench.png
    fehlermeldung workbench.png
    34.2 KB · Views: 431
Last edited by a moderator:
Kurz geraten: Wenn masterhost.net Port 3306 nicht erreichbar ist, dann
Kann es sein, dass:
Master nicht im gleichen Netzwerk
Server horcht nicht nicht an masterhost.net
Tunnel nach masterhost.net funktioniert nicht
Firewall blockiert
 
GwenDragon said:
Kurz geraten: Wenn masterhost.net Port 3306 nicht erreichbar ist, dann
Kann es sein, dass:
Master nicht im gleichen Netzwerk
Server horcht nicht nicht an masterhost.net
Tunnel nach masterhost.net funktioniert nicht
Firewall blockiert
Click to expand...

Ports sind aufjedenfall da:
TCP [::1]:3306 server:51330 HERGESTELLT
TCP [::1]:51330 server:3306 HERGESTELLT

Jedoch wenn ich ein ping mache auf dem Master, 100% Verlust.

Na haben wir es.

Er finden, wie du schon sagtest den Server nicht...

Hmm...
 
Last edited by a moderator:
Den Slave-User hab ich kein Passwort gegeben und das hat auch nichts gebracht.


Nun ich vermute es hat was mit den ursprünglichen fehlern zu tun,die ich dann mit der ersten Lösung übersprungen habe....

Ich denke darüber nach den Slave-user neu zu erstellen...

Aber ich vermute es liegt an etwas anderem..
Die Ports sind offen, die beiden Server sind in einem Netzwerk...
 
Hallo zusammen,

ich hab das Problem mit dem user gelöst.

Es lag an der Master.info datei. Hab wieder das ursprüngliche Passwort von der Datei wieder benutzt und er hat sich wieder verbunden.
Das Master.info (Die Configuration da kann mit nur dem Befehl CHANGE MASTER TO im laufenden Betrieb ändern, ansonsten geht das nicht.)

Jedoch habe ich das Problem, dass nun die Binär-Logs-Dateien unterscheidlich sind.

Auf dem Slave ist er noch bei der mysql0005.bin datei und der master benutzt die mysql00012.bin datei.

Wie krieg ich das hier jetzt gelöst, dass der Slave die aktuellen Änderungen anpasst?
 
Aktueller Stand:

HTML: 
2017-07-25T13:55:36.128920Z 26 [ERROR] Slave I/O for channel '': error connecting to master 'slave@master.net:3306' - retry-time: 60  retries: 10, Error_code: 1045
2017-07-25T13:56:36.274079Z 26 [ERROR] Slave I/O for channel '': error connecting to master 'slave@master.net:3306' - retry-time: 60  retries: 11, Error_code: 1045
2017-07-25T13:57:36.419311Z 26 [ERROR] Slave I/O for channel '': error connecting to master 'slave@master.net:3306' - retry-time: 60  retries: 12, Error_code: 1045
2017-07-25T13:58:36.564484Z 26 [ERROR] Slave I/O for channel '': error connecting to master 'slave@master.net:3306' - retry-time: 60  retries: 13, Error_code: 1045
2017-07-25T13:59:36.710286Z 26 [ERROR] Slave I/O for channel '': error connecting to master 'slave@master.net:3306' - retry-time: 60  retries: 14, Error_code: 1045
2017-07-25T14:00:36.855891Z 26 [ERROR] Slave I/O for channel '': error connecting to master 'slave@master.net:3306' - retry-time: 60  retries: 15, Error_code: 1045
2017-07-25T14:01:37.003710Z 26 [ERROR] Slave I/O for channel '': error connecting to master 'slave@master.net:3306' - retry-time: 60  retries: 16, Error_code: 1045
2017-07-25T14:02:37.151074Z 26 [ERROR] Slave I/O for channel '': error connecting to master 'slave@master.net:3306' - retry-time: 60  retries: 17, Error_code: 1045
2017-07-25T14:03:37.298221Z 26 [ERROR] Slave I/O for channel '': error connecting to master 'slave@master.net:3306' - retry-time: 60  retries: 18, Error_code: 1045
2017-07-25T14:04:37.448022Z 26 [Note] Slave I/O thread for channel '': connected to master 'slave@master.net:3306',replication started in log 'mysql-bin.000005' at position 8127820
2017-07-25T14:06:03.267128Z 27 [Note] Slave SQL thread for channel '' exiting, replication stopped in log 'mysql-bin.000005' at position 9080452
2017-07-25T14:06:03.329635Z 26 [Note] Slave I/O thread killed while reading event for channel ''
2017-07-25T14:06:03.329635Z 26 [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.000005', position 15250145
2017-07-25T14:06:08.111245Z 35 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2017-07-25T14:06:08.126868Z 36 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2017-07-25T14:06:08.126868Z 36 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000005' at position 9080452, relay log 'D:\mysql\relay.000003' position: 952952
2017-07-25T14:06:08.251878Z 35 [Note] Slave I/O thread for channel '': connected to master 'slave@master.net:3306',replication started in log 'mysql-bin.000005' at position 15250145
2017-07-25T14:32:24.146006Z 36 [Note] Error reading relay log event for channel '': slave SQL thread was killed
2017-07-25T14:32:24.192884Z 35 [Note] Slave I/O thread killed while reading event for channel ''
2017-07-25T14:32:24.192884Z 35 [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.000005', position 15250719
2017-07-25T14:32:43.071421Z 57 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2017-07-25T14:32:43.087016Z 58 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2017-07-25T14:32:43.087016Z 58 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000005' at position 15250719, relay log 'D:\mysql\relay.000004' position: 894
2017-07-25T14:32:43.212056Z 57 [Note] Slave I/O thread for channel '': connected to master 'slave@master.net:3306',replication started in log 'mysql-bin.000005' at position 15250719
2017-07-25T14:37:26.890592Z 58 [Note] Error reading relay log event for channel '': slave SQL thread was killed
2017-07-25T14:37:26.921844Z 57 [Note] Slave I/O thread killed while reading event for channel ''
2017-07-25T14:37:26.921844Z 57 [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.000005', position 15250719
2017-07-25T15:06:40.108943Z 91 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2017-07-25T15:06:40.140195Z 92 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2017-07-25T15:06:40.140195Z 92 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000005' at position 15250719, relay log 'D:\mysql\relay.000005' position: 320
2017-07-25T15:06:40.249547Z 91 [Note] Slave I/O thread for channel '': connected to master 'slave@master.net:3306',replication started in log 'mysql-bin.000005' at position 15250719
 
Slave Status:

HTML: 
mysql> SHOW SLAVE STATUS \G;
* 1. row *
Slave_IO_State: Waiting for master to send event
Master_Host: 32498234master.net
Master_User: slave_sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 15252340
Relay_Log_File: relay.000006
Relay_Log_Pos: 1941
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db01
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: 15252340
Relay_Log_Space: 2304
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: D:/mysql/cert/ca-cert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: D:/mysql/cert/client-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: D:/mysql/cert/client-key.pem
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f6f77101-a9d8-11e6-93b2-0cc47ac69bea
Master_Info_File: C:\ProgramData\MySQL\MySQL Server 5.7\Data\master
.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more up
dates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified
 
Master-Status

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+---------------
----+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_
Set |
+------------------+----------+--------------+------------------+---------------
----+
| mysql-bin.000012 | 154 | | |
|
+------------------+----------+--------------+------------------+---------------
----+
1 row in set (0.00 sec)

mysql>
 
Prozesse:

HTML: 
mysql> SHOW PROCESSLIST \G;
* 1. row *
Id: 8
User: root
Host: localhost:52165
db: NULL
Command: Query
Time: 0
State: starting
Info: SHOW PROCESSLIST
* 2. row *
Id: 91
User: system user
Host:
db: NULL
Command: Connect
Time: 5094
State: Waiting for master to send event
Info: NULL
* 3. row *
Id: 92
User: system user
Host:
db: NULL
Command: Connect
Time: 481
State: Slave has read all relay log; waiting for more updates
Info: NULL
3 rows in set (0.00 sec)

ERROR:
No query specified
 
You must log in or register to reply here.
Back
Top