Solved

Mysql

Posted on 2008-10-24
2
673 Views
Last Modified: 2012-06-27
Issue: Unable to replicate Mysql slave to Mysql master

Master my.cnf file:

[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
user = mysql
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
log_slow_queries

log-bin=mysql-bin
server-id       = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

*******************************************************************************

Slave my.cnf file

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
skip-locking
key_buffer = 512M
max_allowed_packet = 1M
table_cache = 256
master-host=192.168.215.83
master-user=replicate
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 256M
thread_cache_size = 8
query_cache_size= 32M
thread_concurrency = 4
log_slow_queries
#log-bin=mysql-bin
server-id=2
#relay-log=/var/lib/mysql/steuross2-relay-bin
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqldump]
quick
max_allowed_packet = 16M

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


********************************************************************
Slave Status:

bash# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.51a-community-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.215.83
                Master_User: replicate
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000008
        Read_Master_Log_Pos: 708081909
             Relay_Log_File: mysqld-relay-bin.000005
              Relay_Log_Pos: 747
      Relay_Master_Log_File: mysql-bin.000003
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1062
                 Last_Error: Error 'Duplicate entry '1230388' for key 1' on query. Default database: 'esupport'. Query: 'insert into asTickets(ticketid, dateline, fullname, email, subject, level, lrdateline, lrby, type, depid, department, trackingno, replies, ipaddress, flagged, isflagged, isnoted, hasattachments, movedfrom, movedfromdep, totalrating, countrating, date_j, month_j, year_j, creator, recemail, isemailed) values('VQC-19691','1218526922', 'Íèêîëàé Áàêååâ', 'postav_celi@o2.pl', 'ÊÀÊ ÇÀÑÒÀÂÈÒÜ ÌÅÍÅÄÆÅÐÎÂ ÏÎ ÏÐÎÄÀÆÀÌ ÂÛÏÎËÍßÒÜ ÏÎÑÒÀÂËÅÍÛÅ ÏËÀÍÛ (Ìîòèâàöèÿ ñýéëç-ìåíåäæåðîâ äëÿ ðóêîâîäèòåëåé)', '1', '1218526922', 'none', '1','74','CasinoClubPoker','048b7739','0', '', 0, 0, 0, 0, 0, '', 0, 0, '12', '8', '2008', 'customer', '', 1)'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 610
            Relay_Log_Space: 5629730280
            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: NULL
1 row in set (0.00 sec)


**********************************************************************
Slave Server Log:


bash# tail -f /var/log/mysqld.log
081024 15:14:07 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000003' at position 1956, relay log '/var/run/mysqld/mysqld-relay-bin.000005' position: 2093
081024 15:14:07 [ERROR] Slave: Error 'Duplicate entry '2839755' for key 1' on query. Default database: 'esupport'. Query: 'insert into asAuditLog(day_j, month_j, year_j, hour_j, min_j, contents, dateline, ticketid, resid) values('12', '8', '2008', '9', '42', 'Ticket created by Íèêîëàé Áàêååâ', '1218526922', 'VQC-19691', '')', Error_code: 1062
081024 15:14:07 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000003' position 1956
081024 15:14:07 [Note] Slave I/O thread: connected to master 'replicate@192.168.215.83:3306',  replication started in log 'mysql-bin.000008' at position 705951521
081024 15:15:58 [Note] Slave I/O thread killed while reading event
081024 15:15:58 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000008', position 706190091
081024 15:16:12 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log '/var/run/mysqld/mysqld-relay-bin.000001' position: 4
081024 15:16:12 [Note] Slave I/O thread: connected to master 'replicate@192.168.215.83:3306',  replication started in log 'FIRST' at position 4
081024 15:16:13 [ERROR] Slave: Error 'Duplicate entry '1230388' for key 1' on query. Default database: 'esupport'. Query: 'insert into asTickets(ticketid, dateline, fullname, email, subject, level, lrdateline, lrby, type, depid, department, trackingno, replies, ipaddress, flagged, isflagged, isnoted, hasattachments, movedfrom, movedfromdep, totalrating, countrating, date_j, month_j, year_j, creator, recemail, isemailed) values('VQC-19691','1218526922', 'Íèêîëàé Áàêååâ', 'postav_celi@o2.pl', 'ÊÀÊ ÇÀÑÒÀÂÈÒÜ ÌÅÍÅÄÆÅÐÎÂ ÏÎ ÏÐÎÄÀÆÀÌ ÂÛÏÎËÍßÒÜ ÏÎÑÒÀÂËÅÍÛÅ ÏËÀÍÛ (Ìîòèâàöèÿ ñýéëç-ìåíåäæåðîâ äëÿ ðóêîâîäèòåëåé)', '1', '1218526922', 'none', '1','74','CasinoClubPoker','048b7739','0', '', 0, 0, 0, 0, 0, '', 0, 0, '12', '8', '2008', 'customer', '', 1)', Error_code: 1062
081024 15:16:13 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000003' position 610

0
Comment
Question by:linuxraja
2 Comments
 
LVL 1

Expert Comment

by:cristides
ID: 22796104
--slave-skip-errors=[err_code1,err_code2,...|all]

Normally, replication stops when an error occurs on the slave. This gives you the opportunity to resolve the inconsistency in the data manually. This option tells the slave SQL thread to continue replication when a statement returns any of the errors listed in the option value.

Do not use this option unless you fully understand why you are getting errors. If there are no bugs in your replication setup and client programs, and no bugs in MySQL itself, an error that stops replication should never occur. Indiscriminate use of this option results in slaves becoming hopelessly out of synchrony with the master, with you having no idea why this has occurred.

For error codes, you should use the numbers provided by the error message in your slave error log and in the output of SHOW SLAVE STATUS. Appendix B, Errors, Error Codes, and Common Problems, lists server error codes.

You can also (but should not) use the very non-recommended value of all to cause the slave to ignore all error messages and keeps going regardless of what happens. Needless to say, if you use all, there are no guarantees regarding the integrity of your data. Please do not complain (or file bug reports) in this case if the slave's data is not anywhere close to what it is on the master. You have been warned.

Examples:

--slave-skip-errors=1062,1053
--slave-skip-errors=all
0
 
LVL 26

Accepted Solution

by:
ushastry earned 500 total points
ID: 22797822
Try this...

 stop slave;
 set global sql_slave_skip_counter=1;
 start slave;

 show slave status\G

If the same error comes out then repeat above step...
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now