MySQL Error 1236

Hi,

When mysqld crashes before flushes the log , you may get this error and replication can not resume.

This can be shown like when “show slave status” performed:

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

For solution :

Suppose that you have a Master-Master Replication on HostA and HostB.

On HostA:

1- stop slave;

2-reset master;

3-reset slave;

On HostB:

1- stop slave;

2-reset master;

3-reset slave;

After all you can set up replication between them using this method https://hsnyrd.wordpress.com/2013/07/15/setting-up-master-slave-mysql-replication/

Good Luck.

Advertisements

Setting up Master – Slave and Master – Master MySQL Replication

If you are using MySQL as a database, one of the best solution is using MySQL Replication for High Availability.

Here is a short procedure for setting up replication :

  • Set one of server’s “server-id” parameter to any integer value different from the slave one.

For master mysql database :

# vi /etc/my.cnf

….

server-id     = 5   # This server’s id is 5

….

For slave mysql database

# vi /etc/my.cnf

….

server-id     = 6   # This server’s id is 6

….

  • Restart both MySQL servers for reload my.cnf files.

If you have mysql script under /etc/init.d/ , you can start and stop db servers running “service mysql stop” and “service mysql start” commands.

  • Login slave MySQL database server and run “fake” change master command. Please fill the MASTER_HOST information with the master host’s hostname.

mysql> CHANGE MASTER TO MASTER_HOST=’hostname’, MASTER_USER=’rep_user’,
-> MASTER_PASSWORD=’rep_pass’, MASTER_PORT=3306;

  • Now we will migrate the data from the master host to the slave one. First export full master database to alldb.sql using below command.

# mysqldump -uroot -p –all-databases –single-transaction  –master-data=1  > alldb.sql

  • scp the dump file (alldb.sql) to the slave node from the master node.

# scp alldb.sql slave_node:

  • Import this file to the slave mysql database.

# mysql -uroot -p < alldb.sql

  • When import operation finishes, you can logon the slave database and enable slave mode running :

mysql> start slave;

  • You can check running “show slave status \G” on the slave server. Both Slave_IO_Running and Slave_SQL_Running must be run. (“Yes” indicates that it is running)

mysql> show slave status \G

…..

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

….

  • Now test time !!

create a table to the master database under test schema and see that it comes to slave database..

If you want to set up Master-Master Replication , you perform a CHANGE MASTER command on the first host taking MASTER_LOG_FILE and MASTER_LOG_POS values from the slave.

On Slave:

mysql> show master status \G
*************************** 1. row ***************************
File: db2-bin.000004
Position: 588484588
Binlog_Do_DB:
Binlog_Ignore_DB:

On Master:

CHANGE MASTER TO
MASTER_HOST=’db2′,
MASTER_USER=rep_user’,
MASTER_PASSWORD=’rep_pass’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’db2-bin.000004′,
MASTER_LOG_POS=588484588;

Good Luck 🙂