Solved

Mysql

Posted on 2008-10-24
2
709 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:
Umesh 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

705 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