Bug #41929 | Thinks master and slave server IDs are same when they are not | ||
---|---|---|---|
Submitted: | 7 Jan 2009 17:18 | Modified: | 22 Mar 2012 18:24 |
Reporter: | Marc Perkel | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
Version: | 5.0.67 | OS: | Linux |
Assigned to: | CPU Architecture: | Any |
[7 Jan 2009 17:18]
Marc Perkel
[12 Jan 2009 10:36]
Susanne Ebrecht
Many thanks for writing a bug report. Please let us know exactly, how you setup and start the replication. How did you install MySQL? Which Linux are you using? How did you setup replication? Paste your my.cnf for master and your my.cnf for slave.
[12 Jan 2009 15:26]
Marc Perkel
I'm running Fedora 10. I installed from standard RPMs. ==== MASTER MY.CNF ====== # The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 socket = /mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /mysql/mysql.sock delay_key_write = all key_buffer = 6G max_allowed_packet = 32M table_cache = 4096 tmp_table_size = 64M sort_buffer_size = 8M read_buffer_size = 8M read_rnd_buffer_size = 16M myisam_sort_buffer_size = 256M thread_cache = 512 query_cache_size = 128M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 64 max_connections = 4000 max_connect_errors = 10000 external_locking enable_locking log-bin=mysql-bin # required unique id between 1 and 232 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server_id = 1 innodb_buffer_pool_size = 512M innodb_additional_mem_pool_size = 100M [mysqldump] quick max_allowed_packet = 64M [mysql] no-auto-rehash protocol=tcp # Remove the next comment character if you are not familiar with SQL # safe-updates [isamchk] key_buffer = 4G sort_buffer_size = 2G read_buffer = 64M write_buffer = 64M [myisamchk] key_buffer = 4G sort_buffer_size = 2G read_buffer = 64M write_buffer = 64M [mysqlhotcopy] interactive-timeout ====== SLAVE MY.CNF ========= [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqld] datadir=/mysql socket=/mysql/mysql.sock #bind-address=127.0.0.1 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 server_id = 2 master_host = mysql.ctyme.com master_user = repslave master_password = ****** key_buffer = 1G max_allowed_packet = 32M table_cache = 4096 tmp_table_size = 64M sort_buffer_size = 8M read_buffer_size = 8M read_rnd_buffer_size = 16M myisam_sort_buffer_size = 64M thread_cache = 512 query_cache_size = 128M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 16 # Uncomment the following if you are using InnoDB tables innodb_buffer_pool_size = 512M innodb_additional_mem_pool_size = 100M [mysql.server] user=mysql basedir=/var/lib [isamchk] key_buffer = 4G sort_buffer_size = 2G read_buffer = 64M write_buffer = 64M [myisamchk] key_buffer = 4G sort_buffer_size = 2G read_buffer = 64M write_buffer = 64M
[12 Jan 2009 18:08]
Sveta Smirnova
Thank you for the feedback. Please also provide output of SHOW VARIABLES LIKE 'hostname', SHOW VARIABLES LIKE 'port' and SHOW SLAVE STATUS from slave.
[12 Jan 2009 18:38]
Marc Perkel
=== MASTER === SHOW VARIABLES LIKE 'hostname'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | hostname | mysql.ctyme.com | +---------------+-----------------+ SHOW VARIABLES LIKE 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ ==== SLAVE ==== SHOW SLAVE STATUS; +----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | +----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+ | Waiting for master to send event | mysql.ctyme.com | repslave | 3306 | 60 | mysql-bin.000001 | 150146 | mysqld-relay-bin.000004 | 150283 | mysql-bin.000001 | Yes | Yes | | | | | | | 0 | | 0 | 150146 | 150283 | None | | 0 | No | | | | | | 0 | +----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+ 1 row in set (0.00 sec)
[12 Jan 2009 19:03]
Sveta Smirnova
Please also provide output of SHOW VARIABLES LIKE 'hostname' and SHOW VARIABLES LIKE 'port' from the slave.
[12 Jan 2009 19:08]
Marc Perkel
==== SLAVE === SHOW VARIABLES LIKE 'hostname'; +---------------+--------------------+ | Variable_name | Value | +---------------+--------------------+ | hostname | mysqlrep.ctyme.com | +---------------+--------------------+ SHOW VARIABLES LIKE 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+
[12 Jan 2009 19:12]
Marc Perkel
show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: mysql.ctyme.com Master_User: repslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 5739021 Relay_Log_File: mysqld-relay-bin.000006 Relay_Log_Pos: 3505795 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 5739021 Relay_Log_Space: 3505795 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: 0 1 row in set (0.00 sec)
[14 Jan 2009 8:34]
Sveta Smirnova
Thank you for the feedback. In the error message I see: 090107 9:08:12 [ERROR] The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). 090107 9:08:12 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000001', position 2762409 But SHOW SLAVE STATUS shows: Read_Master_Log_Pos: 5739021 Relay_Log_File: mysqld-relay-bin.000006 Relay_Log_Pos: 3505795 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes .... Exec_Master_Log_Pos: 5739021 So slave is running and executed queries after position 2762409 in the binary log. Do you really experience described problem now? (Usually such happens when one forget to set up server_id for slave, then fix it.(
[14 Jan 2009 8:50]
Marc Perkel
Yes - I still get the same message in the log even though the server IDs are clearly not the same. Hence the bug report. It says the server IDs are equal when they are not equal. 090114 00:18:39 mysqld started 090114 0:18:40 InnoDB: Started; log sequence number 3 3408834604 090114 0:18:41 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem. 090114 0:18:41 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './mysqld-relay-bin.000007' position: 98 090114 0:18:41 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.67-log' socket: '/mysql/mysql.sock' port: 3306 Source distribution 090114 0:18:41 [Note] Slave I/O thread: connected to master 'repslave@mysql.ctyme.com:3306', replication started in log 'mysql-bin.000001' at position 2762409 090114 0:18:41 [ERROR] The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). 090114 0:18:41 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000001', position 2762409
[14 Jan 2009 9:39]
Sveta Smirnova
Thank you for the feedback. Error log shows position 2762409 of master binary log which is smaller than position 5739021 from the SHOW SLAVE STATUS. Also name of relay log is different. Are you sure you provide output of SHOW SLAVE STATUS and error log from the same server? Please check this with help of statement SHOW VARIABLES LIKE 'log_error'
[14 Jan 2009 16:15]
Marc Perkel
You want me to run SHOW SLAVE STATUS on the MASTER?
[14 Jan 2009 20:19]
Sveta Smirnova
> You want me to run SHOW SLAVE STATUS on the MASTER? Not, but error log and output looks like they are from 2 different mysqld instances. For example, 2 slaves.
[14 Jan 2009 23:21]
Marc Perkel
I only have one slave on a separate computer.
[16 Jan 2009 16:30]
Susanne Ebrecht
I think this is not a bug at all. Anyway, could it be possible that both computers have the same IP by accident or something like this?
[16 Jan 2009 20:59]
Marc Perkel
If both computers have the same IP one couldn't be working backing up the other. It is a bug. The servers have separate IP addresses.
[16 Jan 2009 21:03]
Marc Perkel
If you google "[ERROR] The slave I/O thread stops because master and slave have equal MySQL server ids" you'll see I'm not the only one seeing this.
[26 Jan 2009 15:01]
Susanne Ebrecht
Please try to take the IP numbers instead of the hostnames. It could be that the DNS get curious here.
[26 Jan 2009 15:03]
MySQL Verification Team
Hi Marc! server_id's are indeed different. Please try using IP address to configure the slave in CHANGE MASTER MASTER_HOST=<ip> ... At least verify that: ping mysql.ctyme.com ping mysqlrep.ctyme.com are pinging different IP addresses internally when run on the slave. (Externally they do..)
[27 Feb 2009 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[29 Dec 2010 4:31]
Rajashekhar Karki
No, I am getting same issue From Master, excerpts as below 101228 19:56:38 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './mysqld-relay-bin.000001' position: 98 101228 19:56:38 [Note] Slave I/O thread: connected to master 'root@192.168.40.37:3306', replication started in log 'FIRST' at position 4 101228 19:56:38 [ERROR] The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). 101228 19:56:38 [Note] Slave I/O thread exiting, read up to log 'FIRST', position 4 mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 30 | +---------------+-------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'hostname'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | hostname | share-srvr | +---------------+------------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'port' -> ; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ 1 row in set (0.00 sec) mysql> SHOW SLAVE STATUS; +----------------+---------------+-------------+-------------+---------------+-- ---------------+---------------------+-------------------------+---------------+ -----------------------+------------------+-------------------+----------------- +---------------------+--------------------+------------------------+----------- --------------+-----------------------------+------------+------------+--------- -----+---------------------+-----------------+-----------------+---------------- +---------------+--------------------+--------------------+--------------------+ -----------------+-------------------+----------------+-----------------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | M aster_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_ Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Cou nter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | +----------------+---------------+-------------+-------------+---------------+-- ---------------+---------------------+-------------------------+---------------+ -----------------------+------------------+-------------------+----------------- +---------------------+--------------------+------------------------+----------- --------------+-----------------------------+------------+------------+--------- -----+---------------------+-----------------+-----------------+---------------- +---------------+--------------------+--------------------+--------------------+ -----------------+-------------------+----------------+-----------------------+ | | 192.168.40.37 | root | 3306 | 60 | | 4 | mysqld-relay-bin.000005 | 98 | | No | Yes | | | | | | | 0 | | 0 | 0 | 98 | None | | 0 | No | | | | | | NULL | +----------------+---------------+-------------+-------------+---------------+-- ---------------+---------------------+-------------------------+---------------+ -----------------------+------------------+-------------------+----------------- +---------------------+--------------------+------------------------+----------- --------------+-----------------------------+------------+------------+--------- -----+---------------------+-----------------+-----------------+---------------- +---------------+--------------------+--------------------+--------------------+ -----------------+-------------------+----------------+-----------------------+ 1 row in set (0.00 sec)
[29 Dec 2010 20:32]
Sveta Smirnova
Raj K, thank you for the feedback. What is value of server_id and host on master?
[30 Dec 2010 17:05]
Rajashekhar Karki
Sorry, I have miss-configured the my.cnf on master. I did add "binlog-do-db", I believe this is not advised to add this parameter. After removing this, everything worked fine. Thanks Raj
[30 Dec 2010 18:33]
Sveta Smirnova
Thank you for the feedback. This is not a bug in your case than. But we still need feedback from Marc on Shane's question.
[31 Jan 2011 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[14 Feb 2011 7:19]
Rajashekhar Karki
After few days, this error thrown again.. 110214 12:12:08 [ERROR] The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). 110214 12:12:08 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000003', position 350407719
[17 Mar 2011 20:21]
Sveta Smirnova
Thank you for the feedback. We still need your slave server_id value and full configuration files from both master and slave.
[17 Apr 2011 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[17 Mar 2012 11:19]
Mauro Bonaglia
Same issue. Deleting file master.info resolved the problem.
[22 Mar 2012 16:02]
Vaughn Hawk
I'm having the exact same issue; driving me nuts. I'll post everything I have: MASTER: mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 67.221.32.36 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: db01-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: testing Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 0 Relay_Log_Space: 106 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 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1593 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec) mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 2 | +---------------+-------+ mysql> show variables like 'hostname'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | hostname | db01.mlp1 | +---------------+-----------+ mysql> show variables like 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ And finally for master, my my.cnf config: # # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [client] port = 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # # * Basic Settings # # # * IMPORTANT # If you make changes to these settings and your system uses apparmor, you may # also need to also adjust /etc/apparmor.d/usr.sbin.mysqld. # user = mysql socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp skip-external-locking # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = omitted, routable IP # # * Fine Tuning # key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover = BACKUP #max_connections = 100 #table_cache = 64 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size = 16M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 log_error = /var/log/mysql/error.log # Here you can see queries with especially long duration #log_slow_queries = /var/log/mysql/mysql-slow.log #long_query_time = 2 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. server-id = 2 replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 1 master-host = omitted, routable IP master-user = backup master-password = passwd123 master-connect-retry = 60 replicate-do-db = testing log-bin = /var/log/mysql/mysql-bin.log #binlog-do-db = testing expire_logs_days = 10 max_binlog_size = 100M #relay-log = mysqld-relay-bin report-host = db01.mlp1.peakwebhosting.com log-slave-updates #binlog_ignore_db = include_database_name # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # For generating SSL certificates I recommend the OpenSSL GUI "tinyca". # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem #innodb_flush_log_at_trx_commit=1 #sync_binlog=1 [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 16M # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/ And the error from the logs on Master: 120322 8:38:28 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Error_code: 1593 120322 8:38:28 [Note] Slave I/O thread exiting, read up to log 'FIRST', position 4
[22 Mar 2012 16:04]
Vaughn Hawk
And the Slave configuration info: SLAVE: mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 67.221.32.36 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 106 Relay_Log_File: db02-relay-bin.000002 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: testing Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 106 Relay_Log_Space: 405 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec) mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 4 | +---------------+-------+ mysql> show variables like 'hostname'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | hostname | db02.mlp1 | +---------------+-----------+ mysql> show variables like 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ And the my.cnf config for SLAVE: # # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [client] port = 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # # * Basic Settings # # # * IMPORTANT # If you make changes to these settings and your system uses apparmor, you may # also need to also adjust /etc/apparmor.d/usr.sbin.mysqld. # user = mysql socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp skip-external-locking #log-slave-updates # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = omitted, routable IP - does NOT match master, however # # * Fine Tuning # key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover = BACKUP #max_connections = 100 #table_cache = 64 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size = 16M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 log_error = /var/log/mysql/error.log # Here you can see queries with especially long duration #log_slow_queries = /var/log/mysql/mysql-slow.log #long_query_time = 2 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. server-id = 4 replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 2 master-host = omitted, routable IP master-user = backup master-password = passwd123 master-connect-retry = 60 replicate-do-db = testing log-bin = /var/log/mysql/mysql-bin.log #binlog-do-db = testing expire_logs_days = 10 max_binlog_size = 100M master-user = backup master-password = passwd123 master-connect-retry = 60 replicate-do-db = testing log-bin = /var/log/mysql/mysql-bin.log #binlog-do-db = testing expire_logs_days = 10 max_binlog_size = 100M report-host = db02.mlp1.peakwebhosting.com #relay-log = mysqld-relay-bin log-slave-updates #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # For generating SSL certificates I recommend the OpenSSL GUI "tinyca". # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 16M # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/ And no errors in Slave regarding replication. I've tried what a few others have mentioned already - deleting the master.info file, and disabling binlog-do-db in the my.cnf file (restarting mysql each time), and still for some reason the master thinks itself and the slave have the same server ID's. This is driving me nuts, and I have no idea how to fix - any ideas? Signed, Desperate :)
[22 Mar 2012 18:24]
Sveta Smirnova
Thank you for the feedback. Both servers have Master_Host: 67.221.32.36 MySQL server can not replicate from itself. Most likely this is the reason of the failure.
[19 Feb 2015 18:03]
Vaibhav Fouzdar
I was struggling with this issue for sometime where master shows error -- Master & Slave were set up correctly with different IPs & different "server-id" in my.ini ---------------------------------------------------------------------------- Relay_Master_Log_File: log-bin.000001 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 107 Relay_Log_Space: 107 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 aster_SSL_Verify_Server_Cert: No Last_IO_Errno: 1593 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Last_SQL_Errno: 0 ---------------------------------------------------------------------------- ISSUE: Slave SQL_Thread was running on master server as well...(Not sure how :( ) FIX: run "stop slave;" on MASTER POST ACTIONS - Make sure replication is still working by verify slave status (on SLAVE) In my case, above fixed the issue and master-slave replication also worked fine after that.