I checked mysql config, server-ids are okay as well as auto-increment-offset per "how to". I've managed to install everything on server #2. Now, I see both servers in main ISPConfig control panel. It looks like mysql-replication is not working again. This is what I got now: Server #1 Code: mysql> SHOW SLAVE STATUS \G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.1.106 Master_User: slaveuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 213991 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000003 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: 213991 Relay_Log_Space: 578 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: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin.000003' at 213991, the last event read from '/var/log/mysql/mysql-bin.000003' at 4, the last byte read from '/var/log/mysql/mysql-bin.000003' at 4.' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 1 row in set (0.00 sec) server #2 Code: mysql> SHOW SLAVE STATUS \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.105 Master_User: slaveuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 23920699 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 7351 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: 1146 Last_Error: Error 'Table 'dbispconfig.monitor_data' doesn't exist' on query. Default database: 'dbispconfig'. Query: 'REPLACE INTO monitor_data (server_id, type, created, data, state) VALUES (1, 'harddisk_quota', UNIX_TIMESTAMP(), 'a:2:{s:4:\"user\";a:15:{s:9:\"webmaster\";a:4:{s:4:\"used\";s:2:\"24\";s:4:\"soft\";s:1:\"0\";s:4:\"hard\";s:1:\"0\";s:5:\"files\";s:1:\"7\";}s:4:\"web1\";a:4:{s:4:\"used\";s:6:\"549128\";s:4:\"soft\";s:1:\"0\";s:4:\"hard\";s:1:\"0\";s:5:\"files\";s:5:\"54354\";}s:4:\"web2\";a:4:{s:4:\"used\";s:6:\"826744\";s:4:\"soft\";s:1:\"0\";s:4:\"hard\";s:1:\"0\";s:5:\"files\";s:5:\"11146\";}s:4:\"web3\";a:4:{s:4:\"used\";s:5:\"40600\";s:4:\"soft\";s:1:\"0\";s:4:\"hard\";s:1:\"0\";s:5:\"files\";s:4:\"1946\";}s:4:\"web4\";a:4:{s:4:\"used\";s:5:\"98208\";s:4:\"soft\";s:1:\"0\";s:4:\"hard\";s:1:\"0\";s:5:\"files\";s:4:\"2111\";}s:4:\"web5\";a:4:{s:4:\"used\";s:7:\"1543688\";s:4:\"soft\";s:1:\"0\";s:4:\"hard\";s:1:\"0\";s:5:\"files\";s:5:\"13767\";}s:4:\"web6\";a:4:{s:4:\"used\";s:5:\"28980\";s:4:\"soft\";s Skip_Counter: 0 Exec_Master_Log_Pos: 7205 Relay_Log_Space: 63148441 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: 0 Last_IO_Error: Last_SQL_Errno: 1146 Last_SQL_Error: Error 'Table 'dbispconfig.monitor_data' doesn't exist' on query. Default database: 'dbispconfig'. Query: 'REPLACE INTO monitor_data (server_id, type, created, data, state) VALUES (1, 'harddisk_quota', UNIX_TIMESTAMP(), 'a:2:{s:4:\"user\";a:15:{s:9:\"webmaster\";a:4:{s:4:\"used\";s:2:\"24\";s:4:\"soft\";s:1:\"0\";s:4:\"hard\";s:1:\"0\";s:5:\"files\";s:1:\"7\";}s:4:\"web1\";a:4:{s:4:\"used\";s:6:\"549128\";s:4:\"soft\";s:1:\"0\";s:4:\"hard\";s:1:\"0\";s:5:\"files\";s:5:\"54354\";}s:4:\"web2\";a:4:{s:4:\"used\";s:6:\"826744\";s:4:\"soft\";s:1:\"0\";s:4:\"hard\";s:1:\"0\";s:5:\"files\";s:5:\"11146\";}s:4:\"web3\";a:4:{s:4:\"used\";s:5:\"40600\";s:4:\"soft\";s:1:\"0\";s:4:\"hard\";s:1:\"0\";s:5:\"files\";s:4:\"1946\";}s:4:\"web4\";a:4:{s:4:\"used\";s:5:\"98208\";s:4:\"soft\";s:1:\"0\";s:4:\"hard\";s:1:\"0\";s:5:\"files\";s:4:\"2111\";}s:4:\"web5\";a:4:{s:4:\"used\";s:7:\"1543688\";s:4:\"soft\";s:1:\"0\";s:4:\"hard\";s:1:\"0\";s:5:\"files\";s:5:\"13767\";}s:4:\"web6\";a:4:{s:4:\"used\";s:5:\"28980\";s:4:\"soft\";s Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec) mysql log server #1 Code: Jul 9 22:48:35 sun dovecot: auth-worker(21472): mysql(localhost): Connected to database dbispconfig Jul 9 22:50:35 sun dovecot: auth-worker(21555): mysql(localhost): Connected to database dbispconfig Jul 9 22:52:35 sun dovecot: auth-worker(21580): mysql(localhost): Connected to database dbispconfig Jul 9 22:55:35 sun dovecot: auth-worker(21658): mysql(localhost): Connected to database dbispconfig Jul 9 22:57:35 sun dovecot: auth-worker(21693): mysql(localhost): Connected to database dbispconfig Jul 9 23:00:35 sun dovecot: auth-worker(18465): mysql(localhost): Connected to database dbispconfig Jul 9 23:01:35 sun dovecot: auth-worker(20648): mysql(localhost): Connected to database dbispconfig Jul 9 23:03:35 sun dovecot: auth-worker(29907): mysql(localhost): Connected to database dbispconfig Jul 9 23:06:25 sun mysqld: 130709 23:06:25 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('_transient_doing_cron', '1373429185.1063289642333984375000', 'yes') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`) Jul 9 23:07:35 sun dovecot: auth-worker(30158): mysql(localhost): Connected to database dbispconfig Jul 9 23:09:07 sun mysqld: 130709 23:09:07 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: DELETE FROM dns_soa WHERE id = 10 LIMIT 1 Jul 9 23:09:15 sun mysqld: 130709 23:09:15 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: DELETE FROM web_domain WHERE domain_id = 11 LIMIT 1 Jul 9 23:09:36 sun mysqld: 130709 23:09:36 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: DELETE FROM web_database_user WHERE database_user_id = 15 LIMIT 1 Jul 9 23:09:44 sun mysqld: 130709 23:09:44 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: DELETE FROM web_database WHERE database_id = 15 LIMIT 1 Jul 9 23:14:35 sun dovecot: auth-worker(30379): mysql(localhost): Connected to database dbispconfig Jul 9 23:15:35 sun dovecot: auth-worker(30438): mysql(localhost): Connected to database dbispconfig Jul 9 23:18:35 sun dovecot: auth-worker(30488): mysql(localhost): Connected to database dbispconfig Jul 9 23:20:35 sun dovecot: auth-worker(30556): mysql(localhost): Connected to database dbispconfig Jul 9 23:22:35 sun dovecot: auth-worker(30579): mysql(localhost): Connected to database dbispconfig Jul 9 23:25:35 sun dovecot: auth-worker(30657): mysql(localhost): Connected to database dbispconfig Jul 9 23:26:08 sun mysqld: 130709 23:26:08 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('_transient_doing_cron', '1373430368.4692249298095703125000', 'yes') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`) Jul 9 23:27:35 sun dovecot: auth-worker(30679): mysql(localhost): Connected to database dbispconfig Jul 9 23:31:35 sun dovecot: auth-worker(30786): mysql(localhost): Connected to database dbispconfig mysql log server #2 Code: Jul 9 22:50:21 moon mysqld: Jul 9 22:50:21 moon mysqld: 130709 22:50:21 [Note] Event Scheduler: Purging the queue. 0 events Jul 9 22:50:21 moon mysqld: 130709 22:50:21 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) Jul 9 22:50:21 moon mysqld: 130709 22:50:21 [Note] Slave I/O thread killed while reading event Jul 9 22:50:21 moon mysqld: 130709 22:50:21 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000007', position 21758278 Jul 9 22:50:23 moon mysqld: 130709 22:50:23 [Warning] /usr/sbin/mysqld: Forcing close of thread 4502 user: 'ispconfig2' Jul 9 22:50:23 moon mysqld: Jul 9 22:50:23 moon mysqld: 130709 22:50:23 InnoDB: Starting shutdown... Jul 9 22:50:24 moon mysqld: 130709 22:50:24 InnoDB: Shutdown completed; log sequence number 31515470 Jul 9 22:50:24 moon mysqld: 130709 22:50:24 [Note] /usr/sbin/mysqld: Shutdown complete Jul 9 22:50:24 moon mysqld: Jul 9 22:50:24 moon mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended Jul 9 22:50:25 moon mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql Jul 9 22:50:25 moon mysqld: 130709 22:50:25 [ERROR] An old style --language value with language specific part detected: /usr/share/mysql/english/ Jul 9 22:50:25 moon mysqld: 130709 22:50:25 [ERROR] Use --lc-messages-dir without language specific part instead. Jul 9 22:50:25 moon mysqld: 130709 22:50:25 [Note] Plugin 'FEDERATED' is disabled. Jul 9 22:50:25 moon mysqld: 130709 22:50:25 InnoDB: The InnoDB memory heap is disabled Jul 9 22:50:25 moon mysqld: 130709 22:50:25 InnoDB: Mutexes and rw_locks use GCC atomic builtins Jul 9 22:50:25 moon mysqld: 130709 22:50:25 InnoDB: Compressed tables use zlib 1.2.7 Jul 9 22:50:25 moon mysqld: 130709 22:50:25 InnoDB: Using Linux native AIO Jul 9 22:50:25 moon mysqld: 130709 22:50:25 InnoDB: Initializing buffer pool, size = 128.0M Jul 9 22:50:25 moon mysqld: 130709 22:50:25 InnoDB: Completed initialization of buffer pool Jul 9 22:50:25 moon mysqld: 130709 22:50:25 InnoDB: highest supported file format is Barracuda. Jul 9 22:50:25 moon mysqld: 130709 22:50:25 InnoDB: Waiting for the background threads to start Jul 9 22:50:26 moon mysqld: 130709 22:50:26 InnoDB: 5.5.31 started; log sequence number 31515470 Jul 9 22:50:27 moon mysqld: 130709 22:50:27 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 Jul 9 22:50:27 moon mysqld: 130709 22:50:27 [Note] - '0.0.0.0' resolves to '0.0.0.0'; Jul 9 22:50:27 moon mysqld: 130709 22:50:27 [Note] Server socket created on IP: '0.0.0.0'. Jul 9 22:50:27 moon mysqld: 130709 22:50:27 [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. Jul 9 22:50:27 moon mysqld: 130709 22:50:27 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000003' at position 7205, relay log './mysqld-relay-bin.000002' position: 7351 Jul 9 22:50:27 moon mysqld: 130709 22:50:27 [ERROR] Slave SQL: Error 'Table 'dbispconfig.monitor_data' doesn't exist' on query. Default database: 'dbispconfig'. Query: 'REPLACE INTO monitor_data (server_id, type, created, data, state) VALUES (1, 'harddisk_quota', UNIX_TIMESTAMP(), 'a:2:{s:4:\"user\";a:15:{s:9:\"webmaster\";a:4:{s:4:\"used\";s:2:\"24\";s:4:\"soft\";s:1:\"0\";s:4:\"hard\";s:1:\"0\";s:5:\"files\";s:1:\"7\";}s:4:\"web1\";a:4:{s:4:\"used\";s:6:\"549128\";s:4:\"soft\";s:1:\"0\";s:4:\"hard\";s:1:\"0\";s:5:\"files\";s:5:\"54354\";}s:4:\"web2\";a:4:{s:4:\"used\";s:6:\"826744\";s:4:\"soft\";s:1:\"0\";s:4:\"hard\";s:1:\"0\";s:5:\"files\";s:5:\"11146\";}s:4:\"web3\";a:4:{s:4:\"used\";s:5:\"40600\";s:4:\"soft\";s:1:\"0\";s:4:\"hard\";s:1:\"0\";s:5:\"files\";s:4:\"1946\";}s:4:\"web4\";a:4:{s:4:\"used\";s:5:\"98208\";s:4:\"soft\";s:1:\"0\";s:4:\"hard\";s:1:\"0\";s:5:\"files\";s:4:\"2111\";}s:4:\"web5\";a:4:{s:4:\"used\";s:7:\"1543688\";s:4:\"soft\";s:1:\"0\";s:4:\"hard\";s:1:\"0\";s:5:\"files\";s:5:\"13767\";}s:4:\"web6\";a:4:{s:4:\" Jul 9 22:50:27 moon mysqld: used\";s:5:\"28980\";s:4 Jul 9 22:50:27 moon mysqld: 130709 22:50:27 [Warning] Slave: Table 'dbispconfig.monitor_data' doesn't exist Error_code: 1146 Jul 9 22:50:27 moon mysqld: 130709 22:50:27 [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 7205 Jul 9 22:50:27 moon mysqld: 130709 22:50:27 [Note] Event Scheduler: Loaded 0 events Jul 9 22:50:27 moon mysqld: 130709 22:50:27 [Note] /usr/sbin/mysqld: ready for connections. Jul 9 22:50:27 moon mysqld: Version: '5.5.31-0+wheezy1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian) Jul 9 22:50:27 moon /etc/mysql/debian-start[3928]: Upgrading MySQL tables if necessary. Jul 9 22:50:27 moon /etc/mysql/debian-start[3932]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored Jul 9 22:50:27 moon /etc/mysql/debian-start[3932]: Looking for 'mysql' as: /usr/bin/mysql Jul 9 22:50:27 moon /etc/mysql/debian-start[3932]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck Jul 9 22:50:27 moon /etc/mysql/debian-start[3932]: This installation of MySQL is already upgraded to 5.5.31, use --force if you still need to run mysql_upgrade Jul 9 22:50:27 moon /etc/mysql/debian-start[3943]: Checking for insecure root accounts. Jul 9 22:50:27 moon /etc/mysql/debian-start[3948]: Triggering myisam-recover for all MyISAM tables Jul 9 22:50:27 moon mysqld: 130709 22:50:27 [Note] Slave I/O thread: connected to master '[email protected]:3306',replication started in log 'mysql-bin.000007' at position 21758278 Jul 9 22:58:40 moon amavis[5407]: Module DBD::mysql 4.021 Is there a correct way to fix replication? Every time I try to fix it, another issue appears? I would appreciate any input - thank you
Your sql-servers are not in sync due to missing tables. Make sure, the db-structure is identical on all servers. Beside this: it has nothing to do with mysql 5.5.x
I ran these steps to re-sync mysql At the master: mysql -u root -p 1) RESET MASTER; 2) FLUSH TABLES WITH READ LOCK; 3) SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 107 | | | +------------------+----------+--------------+------------------+ 4) mysqldump -uroot -p --events --ignore-table=mysql.events --all-databases > /var/backups/mysqldump.sql 5) UNLOCK TABLES; 6) Copy mysql dump file to server 2 scp -p /var/backups/mysqldump.sql [email protected]:/tmp At the slave: 7) STOP SLAVE; 8) mysql -uroot -p < /tmp/mysqldump.sql 9) RESET SLAVE; 10) CHANGE MASTER TO MASTER_HOST='192.168.1.105', MASTER_USER='slaveuser', MASTER_PASSWORD='mypassword'; 11) CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107; 12) START SLAVE; server 1 looks okay Code: *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.105 Master_User: slaveuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 3082866 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 3083012 Relay_Master_Log_File: mysql-bin.000001 [COLOR="Blue"] Slave_IO_Running: Yes Slave_SQL_Running: Yes[/COLOR] 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: 3082866 Relay_Log_Space: 3083169 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: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 server 2 (error 1236) Code: *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.1.106 Master_User: slaveuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 213991 Relay_Log_File: mysqld-relay-bin.000007 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000003 [COLOR="Red"] Slave_IO_Running: No Slave_SQL_Running: Yes[/COLOR] 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: 213991 Relay_Log_Space: 264 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 [COLOR="red"]Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin.000003' at 213991, the last event read from '/var/log/mysql/mysql-bin.000003' at 4, the last byte read from '/var/log/mysql/mysql-bin.000003' at 4.'[/COLOR] Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 1 row in set (0.00 sec) Server 1 looks fine while server 2 has error 1236. Any ideas how to fix this without breaking replication on server 1? Thanks!
Anyone knows how to fix mysql master-slave replication error 1050? Last_SQL_Errno: 1050 Last_SQL_Error: Error 'Table 'attempts_login' already exists' on query. Default database: 'dbispconfig2'. Query: 'CREATE TABLE `attempts_login` ( `ip` varchar(39) NOT NULL, `times` int(11) DEFAULT NULL, `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8' Thanks!
This fixed my problem, so just wanted to say thanks for posting the steps. Have yourself a beer. Or a cigar if you're a pink floyd fan.
