Solved

Mysql

Posted on 2008-10-24
2
685 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

943 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

5 Experts available now in Live!

Get 1:1 Help Now