Location: Asia Pacific, Cyberspace, in the Dark Dystopia
Posts: 19,118
Thanks Given: 2,351
Thanked 3,359 Times in 1,878 Posts
MySQL Replication Issues: Duplicate Key Error
Lately my attention has been focused on distributed MySQL database replication to other web servers. All was going well, but then I noticed the replication would stop and there were there "Duplicate Primary Key" errors in SHOW SLAVE STATUS;
I started to trace these down and noticed that this is a very common issue in MySQL replication with many of these distributed web applications. It seems that the slave has dB tables of it's own like log files, authorization files, statistics. A mind of it's own! What kind of a slave is that!? Ha Ha...
Anyway, after doing the google-research-thing (GRT) for nearly an hour, I finally made the decision to add this line to my.cnf:
.... and I feel "bad" about it.
I always thought dB replication was dB replication, pure-and-simple, but now there is a more complex side.
It seems to actually be a "good replicator" we need to know which tables in the database to replicate, heaven forbid!
Most folks on the net, based on my GRT, use the slave-skip-errors = 1062 solution (where 1062 is the error number of the duplicate key insert error), but I feel like I've just struck a deal with the devil.
Now, I have no idea what is going on... because the longer I run in this slave-skip-errors = 1062 mode, the further and further the dBs will lose consistency.
GRT yielded no good solution.
I'm lazy to test, test, test... find 1062 errors .... and then see if the table in question should be excluded from the replicate list.
I'm an Oracle DBA and haven't used MySql. However, queries can be run against the tables based on the key columns to determine which rows are duplicates. I'm guessing that cleaning up the duplicates in the master, will in turn, fix the slaves.
Need to recover a replication broken error on the mysql slave server.
I want to force it to resend the binlog file from the begining.
What is the correct value for the: MASTER_LOG_POS ?
0, 1 or 107 ? (2 Replies)
Hello,
I want to create a table in mysql database by loading the local file. I am confused with the auto_increment column (say Run_ID) that I want to set it as primary key. My questions are:
1) Do I need add this Run_ID in my file ready? then this does not make any sense to the auto_increment;... (5 Replies)
Hi to all guyz ,
As i'm new to Shell scripting i was been working out to write a shell script for mysql Replication for multiple slave master architecture.i have done the process up with a script of 200 lines but still there are some things which can make my script more effective so i want your... (5 Replies)
if the key (A or B or ...others) has 4 in its 3rd column the 1st A row has to form 4 dupicates along with the all the values of A in 4th column (2.9, 3.8, 4.2) .
Hope I explain the question clearly.
Cheers
Ruby
input
"A" 1 4 2.9
"A" 2 5 ... (7 Replies)
For example suppose I have a file which contains data as:
$cat data
800,2
100,9
700,3
100,9
200,8
100,3
Now I want the output as
200,8
700,3
800,2
Key is first three characters, I don't want any reords which are having duplicate keys.
Like sort +0.0 -0.3 data can we use... (9 Replies)