Mysql
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/mysq
# 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/
[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.lo
pid-file=/var/run/mysqld/m
**************************
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
**************************
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_Tabl
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','121852
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-re
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:
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-re
081024 15:16:12 [Note] Slave I/O thread: connected to master 'replicate@192.168.215.83:
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','121852
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
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/mysq
# 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/
[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.lo
pid-file=/var/run/mysqld/m
**************************
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
**************************
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_Tabl
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','121852
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-re
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:
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-re
081024 15:16:12 [Note] Slave I/O thread: connected to master 'replicate@192.168.215.83:
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','121852
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,1
--slave-skip-errors=all