Linux Adminstration Tips

Fixing MySQL Replication with duplicate key error

Today our MySQL Master tracking db went down (due to host server crashing) which consequently screwed up the slave. Here are the steps I took to fix it.
Firstly, check the slave status with:

 show slave status\G;

If the slave is reporting something like ”Last_Error: Error Duplicate entry replication”, this can be fixed by the following:

 stop slave;
 set global sql_slave_skip_counter=1;(IT AVOID ONE ERROR)
 start slave;
 show slave status\G;

It will take some time after issuing those commands for the slave to rebuild itself from the master, but you can check the progress by querying the db to see what record we are up to, for example:

 connect trackdb;
 SELECT * FROM `track` WHERE 1 ORDER BY id DESC LIMIT 10;

How To configure MySQL Replication

a) You must have 2 servers(primery/secondary) running MySQL
b) There will be 2 configurations one for primery and another one for secondary.

$$$$$$$$$$$$$     Primery Server(  Master Side)  $$$$$$$$$$$$$

1) Edit /etc/hosts.allow and add this line ( you MUST change this IP’s for yours !! )

Mysql : 10.0.0.1

2) Edit   /etc/mysql/my.cnf or /etc/my.cnf

$$$$$$$$$$$$$$$ Comment this below lines $$$$$$$$$$$$$$$$
bind-address = xxx.xxx.xxx.xxx
skip-networking
####### Make sure this lines exists in the [mysqld] Section:
log-bin=mysql-bin

binlog-do-db=database1

binlog-do-db=database2

etc.,
server-id = 1

3) Now lets move to mysql using a power user like root xD

mysql> GRANT ALL ON databasename.* TO repl@'%' IDENTIFIED BY 'put_some_pass_here';
mysql> FLUSH PRIVILEGES;
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'%' IDENTIFIED BY 'put_some_pass_here';

4) restart MySQL service

$$$$$$$$$$$$$$$$   Client Side $$$$$$$$$$$$$$

5) Edit my.cnf …. In my case located at: /etc/my.cnf
* I’m going to show just a what you need the my.cnf file contains much more info.

#### Add this lines or modify them
log-bin=SOME_NAME-relay-bin
server-id = 2
#### This number increases when you add more than one SLAVE os CLIENT to the MASTER server
master-host = 192.168.50.8
#### Put the Hostname or IP from your MASTER server
master-user = repl
master-password = put_some_pass_here
replicate-do-db=databasename1

replicate-do-db=databasename2

save and close the file.

you have to run this query to set the SLAVE. Before execute the query. you have to check  the MASTER_LOG_FILE and MASTER_LOG_POS in master log(mysql> show master status\G)of primary server

mysql -u root -p

mysql> CHANGE MASTER TO MASTER_HOST=’Ip_of_your_master’, MASTER_USER=’repl’, MASTER_PASSWORD=’some_password’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=345;

6) restart the service

7) done !! all data will be Replicated.

Troubleshooting:
Master:


mysql> SHOW GRANTS FOR repl;
mysql> SHOW MASTER LOGS \G
mysql> SHOW BINARY LOGS;
mysql> SHOW MASTER STATUS;
mysql> RESET MASTER   ==> CAUTON !!!

mysql> show master status\G

*************************** 1. row ***************************

File: mysql-bin.000002

Position: 106

Binlog_Do_DB: opt

Binlog_Ignore_DB:

1 row in set (0.00 sec)

mysql> show processlist;

+-----+------------+------------------+------+-------------+------+----------------------------------------------------------------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+-----+------------+------------------+------+-------------+------+----------------------------------------------------------------+------------------+

| 242 | root | localhost | NULL | Query | 0 | NULL | show processlist |

| 244 | slave_user | 10.0.9.242:37546 | NULL | Binlog Dump | 250 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |

+-----+------------+------------------+------+-------------+------+----------------------------------------------------------------+------------------+

2 rows in set (0.00 sec)

SLAVE:


mysql> SHOW SLAVE STATUS;
mysql> STOP SLAVE;
mysql> START SLAVE;
mysql> RESET SLAVE; ==> CAUTON !!!

mysql> show processlist;

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+

| 46 | root | localhost | NULL | Query | 0 | NULL | show processlist |

| 47 | system user | | NULL | Connect | 276 | Waiting for master to send event | NULL |

| 48 | system user | | NULL | Connect | 270 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+

3 rows in set (0.02 sec)

---------------------------------------------


mysql> show slave status\G

or SHOW SLAVE STATUS;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.9.251

Master_User: slave_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 106

Relay_Log_File: tnqt111-relay-bin.000004

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: opt

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

Relay_Log_Space: 553

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

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.01 sec)

#######You can load the data(tables,data,etc., from databases),while u use the below command

Error1 :

mysql> LOAD DATA FROM MASTER;
ERROR 1219 (HY000): Error running query on master: Access denied; you need the RELOAD privilege for this operation
////////// IF YOU SEE THIS in SLAVE side .... Type This NEXT in the MASTER SERVER:


mysql> GRANT RELOAD on *.* to repl@'%' identified by 'your_password';
OR
mysql> GRANT SUPER on *.* to repl@'%' identified by 'your_password';

Error2:

mysql>load data from master;

While u will use the above command,u may got below error:

Error 1189 (08S01): Net error reading from master

Discussion Discussion

This error can happen while executing the LOAD DATA FROM MASTER statement because of a couple of reasons. It could happen if a non-MyISAM table was loaded. LOAD DATA FROM MASTER works only with MyISAM engine. It can also happen if a recoverable network error occurs. This error can happen while executing the LOAD DATA FROM MASTER statement because of a couple of reasons. It could happen if a non-MyISAM table was loaded. LOAD DATA FROM MASTER works only with MyISAM engine. It can also happen if a recoverable network error occurs.

Solution

If this error is thrown, make sure all tables on the master are MyISAM. If so, retry running the SQL statement. In case of databases containing InnoDB tables or other engines, use mysqldump for slave initialization. If this error is thrown, make sure all tables on the master are MyISAM. If so, retry running the SQL statement. In case of databases containing InnoDB tables or other engines, use mysqldump for slave initialization.