MySql Replication issue with live data

I currently have a master-master (lets say A and B where A is the main master) replication setup with mysql 5.1.45. The data is over 15 GB. Whenever I see duplicate key error, 开发者_运维问答I used to set sql_slave_skip_counter to 1 and restarted the slave. Over many months, this has been done, and now there is a lot of differences in A and B and due to vast amount of data, I cannot fix the inconsistency as well.

This left me with an option to recreate server B which I did. I took a dump from A and copied it over to B. A is production and I cannot afford a downtime. The binary log file of A is around bin.001115 where as after the dump, the binary log is bin.000012.

I am confused after this step on how to change the master log position on both these servers. I have set the master log file and position on A (from B's show master status) and master log file and position of B (from A's show master status).

Can someone tell me if I followed the correct approach?

Even after this, I still see the duplicate key errors? Is there any way that I can follow so as to avoid this duplicate key errors. Or is there any mechanism so that I can do a raw data replication?

Thanks a lot for your response.

For the the log positions and synchronizing server B with A: unfortunately making an SQL dump while the server is live is not going to give you a clean snapshot in the end, because some tables might have been written to after they were added to the dump, before the dump was completed. You already stated that you can't do a full lock or stop the server to copy the data files, so here's something you can try:

1) Stop replication/logging on B

2) Get the log position on A

3) Make the SQL dump

4) Restore B from the dump

5) Start B with configuration option slave_skip_errors=all (see: http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_slave-skip-errors)

6) Start B's replication at the position you got in 2)

7) Let B catch up to A

8) Read note re: preventing duplicate key errors in the future, change settings if it works for you

9) Restart B without slave_skip_errors

10) Start B's logging to A

To help avoid having duplicate key errors in the future:

Take the number of servers you have (in this case 2) Set each server's auto_increment_increment=[that number] Set each server's auto_increment_offset, starting at 1 and going up 1 for each server

Example:

A's config

auto_increment_increment=2
auto_increment_offset=1

B's config

auto_increment_increment=2
auto_increment_offset=2

A will generate autoincrement ID's like 1,3,5,7,... B will generate 2,4,6,8,...

http://dev.mysql.com/doc/refman/5.1/en/replication-options-master.html#sysvar_auto_increment_increment#auto_increment_increment

Keep in mind that neither slave_skip_errors=all nor custom autoincrements will help you keep your data consistent if both A and B are inserting keys that might be duplicated and are not autoincrements (i.e. primary keys that are generated by your code).

Appending here my procedure

On A (main server):

1. stop slave;
2. reset master; (I need not reset, but this will help in starting all over as if new so eliminates confusion)
3. reset slave;
4. take a dump with --master-data

On B:

1. Reinstall and restart mysqld with --skip-slave-start option.
2. Create replication username and password
3. Restore from the dump
4. reset master; (Do not reset slave. If we reset slave, data from B will not replicate to A)
5. start slave (This will sync the differences from A)
6. restart mysql without --skip-slave-start option.

On A:

1. start slave;

And I agree with auto_increment_increment option. My both the servers are setup that way.