Mysql – Slave failing to replication in MariaDB 10

mariadbMySQLreplication

I am not able to enable row based replication. I am trying to set up a master slave replication between two nodes running.

Considerations

  • Server version 10.0.23-MariaDB-log
  • Protocol version 10
  • UNIX socket /var/lib/mysql/mysql.sock.

The scenario I have followed is this:

  1. Shut the prod (mc1), took a cold backup and restored on slave (mc2).

  2. On master ran show master status; prodarchivedlogs-bin.000001 and the pos as 582240

  3. Set these info in slave.

  4. Started the slave, it started throwing up error message:

160812 3:31:50 [ERROR] Slave SQL: Could not execute Update_rows_v1 event on table radius1.radacct; Can't find record in 'radacct', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log prodarchivedlogs-bin.000001, end_log_pos 587695, Gtid 0-1-79, Internal MariaDB error code: 1032

160812 3:17:18 [Warning] Slave: Can't find record in 'radacct' Error_code: 1032

160812 3:17:18 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'prodarchivedlogs-bin.000001' position 587072

I have tried all methods based on my knowledge, but it is just not functioning. I tried using mysqlbinlog tool to extract to a file and then tried inserting, even then it is failing.

The prod my.cnf is as follows

symbolic-links=0
innodb_buffer_pool_size=10G
innodb_data_home_dir= /var/lib/mysql/data2
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/redologs
innodb_log_file_size    = 256M
innodb_buffer_pool_size = 10000M
innodb_flush_method     = O_DIRECT
thread_stack    = 256K
max_connections=100000

expire_logs_days        = 5
max_binlog_size         = 100M

log-bin=/backup/archivedlogs/prodarchivedlogs-bin
binlog_format=row
server-id=1

The slave my.cnf is as follows

skip-name-resolve
skip-slave-start
datadir=/var/lib/mysql/data1
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0

innodb_buffer_pool_size=10G
innodb_data_home_dir= /var/lib/mysql/data2
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/redologs
innodb_log_file_size    = 256M
innodb_buffer_pool_size = 10000M
innodb_flush_method     = O_DIRECT
thread_stack    = 256K
max_connections=100000
expire_logs_days        = 5
max_binlog_size         = 100M
log-bin=/backup/archivedlogs/prodarchivedlogs-bin
binlog_format=row
server-id=2

Further details

The master prod machine has 40 GB RAM and has a 16 core CPU.

I followed the process below:

  1. created the rep user and gave the access.
  2. shutdown prod.
  3. scp'ied the files from prod to slave machine.
  4. brought up master instance.
  5. ran the command show master status\G;. took note of bing log and the postion.
  6. brought up the slave instance
  7. configured the slave profile with the bin log file and the position
  8. ran the start slave command.
    then ran the command show slave status\G; and I started seeing the error messages of

    HA_ERR_KEY_NOT_FOUND.

Also:

  • Tried to run the query mysqlbinlog on the bin log. The report said the file is being used.

  • Ran the command flush logs; still no results.

Trying all methods to fix this.

Any help will be greatly appreciated.

Best Answer

max_connections=100000 -- Unreasonable, and dangerous. Even 1000 may be unnecessarily high for the number of simultaneous connections.

10G for the buffer pool -- I assume you have at least 15GB of RAM?

It sounds like the data on the two servers, at least for redacct, is not consistent. How did you initially populate the Slave?

Related Question