0
MySQL Slave I.O Error 1045
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 schon an diesen Lösungen befolgt:
https://www.howtoforge.com/how-to-repair-mysql-replication
Danach habe wie befolgt in der Log-Datei:
Älter:
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'
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:
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>//
Ich hab sonst noch Fehlermeldungen 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
windektiv
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 schon an diesen Lösungen befolgt:
https://www.howtoforge.com/how-to-repair-mysql-replication
Danach habe wie befolgt in der Log-Datei:
Älter:
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'
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:
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>//
Ich hab sonst noch Fehlermeldungen 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
windektiv
Auf Facebook teilen
Auf Twitter teilen
Auf Reddit teilen
Auf Linkedin teilenBitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 344257
Url: https://administrator.de/contentid/344257
Ausgedruckt am: 25.11.2024 um 10:11 Uhr
