0

After an outage that had us down for 4 days I am attempting to establish replication between 2 of our servers in order to have a "hot" backup available should the need arise.

After dumping all of the databases on the master and creating them on the slave I enabled replication and have had nothing but trouble since. An extract of the log files from the slave and master are included below.

Both servers are sitting on the same VLAN on the same Ethernet segment so there should be, according to our network support folks, zero lag time. The Ethernet MTU on both servers is set to 1500. The max_allowed_packet on the master is set to 805306368.

Both servers are running 10.4.6-MariaDB.

The slave is already almost a day behind and it doesn't look like it is getting any better. I've searched high and low for what the errors in the log mean but none of the suggested solutions have helped or changed the errors in any way.

At this point any suggestions on what else I can try or look at would be appreciated. The replication_user referenced in the logs exists on both the master and the slave and has REPLICATION_CLIENT and REPLICATION_SLAVE authority. I have tried the user with a host of %, 10.% and the name of the slave/master.

The master in this setup is also a slave to another instance but only for a handful of tables in one specific database. That database is NOT replicated to the slave that is having an issue.

From the SLAVE log file:

2019-07-23 13:54:03 9 [Note] Slave: connected to master 'replication_user@10.53.17.154:3306',replication resumed in log 'DWR_Master.000002' at position 195396246

2019-07-23 13:55:33 9 [ERROR] Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013)

2019-07-23 13:55:33 9 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'DWR_Master.000002' at position 195522037

2019-07-23 13:55:54 9 [ERROR] Slave I/O: error reconnecting to master 'replication_user@10.53.17.154:3306' - retry-time: 60 maximum-retries: 86400 message: Can't connect to MySQL server on '10.53.17.154' (10060 "Unknown error"), Internal MariaDB error code: 2003

2019-07-23 13:57:03 9 [Note] Slave: connected to master 'replication_user@10.53.17.154:3306',replication resumed in log 'DWR_Master.000002' at position 195522037

2019-07-23 13:59:21 9 [ERROR] Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013)

2019-07-23 13:59:21 9 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'DWR_Master.000002' at position 195554351

2019-07-23 14:01:01 9 [ERROR] Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013)

2019-07-23 14:01:01 9 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'DWR_Master.000002' at position 195558070

2019-07-23 14:01:22 9 [ERROR] Slave I/O: error reconnecting to master 'replication_user@10.53.17.154:3306' - retry-time: 60 maximum-retries: 86400 message: Can't connect to MySQL server on '10.53.17.154' (10060 "Unknown error"), Internal MariaDB error code: 2003

2019-07-23 14:03:52 9 [Note] Slave: connected to master 'replication_user@10.53.17.154:3306',replication resumed in log 'DWR_Master.000002' at position 195558070

From the MASTER log file:

2019-07-23 13:54:03 30885 [Note] Start binlog_dump to slave_server(75), pos(DWR_Master.000002, 195396246)

2019-07-23 13:54:50 30885 [Warning] Aborted connection 30885 to db: 'unconnected' user: 'replication_user' host: 'slave_host_name.com' (Got an error writing communication packets)

2019-07-23 13:57:03 30966 [Note] Start binlog_dump to slave_server(75), pos(DWR_Master.000002, 195522037)

2019-07-23 13:58:50 30966 [Warning] Aborted connection 30966 to db: 'unconnected' user: 'replication_user' host: 'slave_host_name.com' (Got an error writing communication packets)

2019-07-23 13:59:30 31071 [Note] Start binlog_dump to slave_server(75), pos(DWR_Master.000002, 195554351)

2019-07-23 14:00:16 31071 [Warning] Aborted connection 31071 to db: 'unconnected' user: 'replication_user' host: 'slave_host_name.com' (Got an error writing communication packets)

2019-07-23 14:03:52 31217 [Note] Start binlog_dump to slave_server(75), pos(DWR_Master.000002, 195558070)

2019-07-23 14:04:41 31217 [Warning] Aborted connection 31217 to db: 'unconnected' user: 'replication_user' host: 'slave_host_name.com' (Got an error writing communication packets)

Master Status Values:

Variable_name               Value
Aborted_clients             455
Aborted_connects            2087
Aborted_connects_preauth        14
Access_denied_errors            4632
Acl_column_grants           0
Acl_database_grants         64
Acl_function_grants         0
Acl_procedure_grants            0
Acl_package_spec_grants         0
Acl_package_body_grants         0
Acl_proxy_users             2
Acl_role_grants             0
Acl_roles               0
Acl_table_grants            2
Acl_users               40
Binlog_commits              49286
Binlog_group_commits            49286
Binlog_group_commit_trigger_count   0
Binlog_group_commit_trigger_lock_wait   0
Binlog_group_commit_trigger_timeout 0
Binlog_snapshot_file            DWR_Master.000002
Binlog_snapshot_position        196890378
Binlog_bytes_written            196969751
Binlog_cache_disk_use           0
Binlog_cache_use            142
Binlog_stmt_cache_disk_use      571
Binlog_stmt_cache_use           49144
Connections             31879
Created_tmp_disk_tables         21949
Created_tmp_files           2324
Created_tmp_tables          170910
Empty_queries               319668
Open_files              3851
Open_table_definitions          1950
Open_tables             2000
Opened_files                177752
Opened_table_definitions        11630
Opened_tables               13882
Opened_views                8587
Queries                 132054966
Questions               1478834
Rows_read               2685456711
Rows_sent               690082934
Rows_tmp_read               490475978
Slave_connections           322
Slave_heartbeat_period          30.000
Slaves_connected            1
Slaves_running              1
Slow_queries                403
Sort_merge_passes           1068
Sort_priority_queue_sorts       5587
Table_locks_immediate           8709091
Table_locks_waited          88
Table_open_cache_active_instances   1
Table_open_cache_hits           71892809
Table_open_cache_misses         50381
Table_open_cache_overflows      10201
Threadpool_threads          4
Threads_cached  0
Threads_connected           12
Threads_created             504
Threads_running             9
Uptime                  116292
Uptime_since_flush_status       116292

Edit to add information requested in the comments

The master and slave have 16 gig of physical memory.

Slave Status:

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Reconnecting after a failed master event read
                   Master_Host: 10.53.17.154
                   Master_User: replication_user
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: DWR_Master.000002
           Read_Master_Log_Pos: 213653980
                Relay_Log_File: DWR_Server-relay-bin.000004
                 Relay_Log_Pos: 114299355
         Relay_Master_Log_File: DWR_Master.000001
              Slave_IO_Running: Connecting
             Slave_SQL_Running: No
               Replicate_Do_DB:
           Replicate_Ignore_DB: cronos,mysql,phpmyadmin,test
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 1133
                    Last_Error: Error 'Can't find any matching row in the user table' on query. Default database: ''. Query: 'GRANT SELECT, SHOW VIEW, CREATE TEMPORARY TABLES  ON `gistables`.* TO 'cuaeditlite'@'10.%''
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 302102356
               Relay_Log_Space: 608187864
               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: 2003
                 Last_IO_Error: error reconnecting to master 'replication_user@10.53.17.154:3306' - retry-time: 60  maximum-retries: 86400  message: Can't connect to MySQL server on '10.53.17.154' (10060 "Unknown error")
                Last_SQL_Errno: 1133
                Last_SQL_Error: Error 'Can't find any matching row in the user table' on query. Default database: ''. Query: 'GRANT SELECT, SHOW VIEW, CREATE TEMPORARY TABLES  ON `gistables`.* TO 'cuaeditlite'@'10.%''
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 50
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State:
              Slave_DDL_Groups: 29
Slave_Non_Transactional_Groups: 1030778
    Slave_Transactional_Groups: 324

Change master statement:

CHANGE MASTER TO
  MASTER_HOST='10.53.17.154',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='replication_password',
  MASTER_PORT=3306,
  MASTER_USE_GTID=slave_pos,
  MASTER_CONNECT_RETRY=60;

Settings from the post Rick linked to:

show variables like '%packet%';

| max_allowed_packet       | 16777216   |
| slave_max_allowed_packet | 1073741824 |

show variables like 'net_read_timeout';

| net_read_timeout | 30    |

show variables like 'slave_net_timeout';

| slave_net_timeout | 60    |

show variables like 'read_buffer_size';

| read_buffer_size | 131072 |

I should note that the size of the max allowed packet size on the slave does not match the master. The master max allowed packet size is currently set to 805306368.

  • What was the nature of the "outage that had us down for 4 days"? Did you have to reinstall MariaDB? – mustaccio Jul 23 at 19:10
  • The data directory was accidentally deleted. MariaDB was, essentially, reinstalled. – Dave Jul 23 at 19:12
  • The contents of the data directory were restored and although we have a few corrupt tables almost everything was recovered. Checks and repairs were made against all databases before dumping them to copy over to the slave. – Dave Jul 23 at 19:18
  • Since the binlog position seems to be moving up, based on the logs you've posted, and the master complains about being unable to write to a socket every minute or so, I'm inclined to think that the problem is in the network, despite what your network support folks might think. May be they want to run tcpdump and see what's going on in the replication session between "Slave: connected to master" and master's "Aborted connection". – mustaccio Jul 23 at 19:35
  • How much RAM in each? – Rick James Jul 23 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.