I am not able to enable row based replication.

I am trying to set up a master slave replication between 2 nodes running

  • 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:

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.

Any help will be greatly appreciated.

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

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?

i have also reduced the max_connections to 2000. Also, the bin log and the restored database is still in place, nothing has been deleted. I just got to restore this db and get this replication up and running.

Please do let me know where am i missing anything

Thanks for the reply

The master prod machine has 40 gb ram and has 16 core cpu.

I followed the following process:

  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 errors of HA_ERR_KEY_NOT_FOUND.

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. please do let me know how I can fix this thanks

You should edit the original question to add information, instead of replying yourself.

However, if these are really the steps you followed:

brought up master instance.
ran the command show master status\G;. took note of bing log and the postion.

...note that you should do them in the opposite order. It makes no sense to get the coordinates from a running master, those coordinates have no use for you. Note down the coordinates before restarting the master: those coordinates describe the state of initial slave state (the files you scp to the slave).

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.