LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Software (https://www.linuxquestions.org/questions/linux-software-2/)
-   -   mysql master slave replication error (https://www.linuxquestions.org/questions/linux-software-2/mysql-master-slave-replication-error-4175484891/)

divyashree 11-17-2013 06:27 AM
mysql master slave replication error
 
Hi,

I have a mysql server(version 5.6) on MAC.
And another CentOS 6 box with mysql 5.1.

I did the replication with MAC system DB as master and CentOS as slave.

Everything went well. and these are output of status on both system:


PHP Code:
mysql> show master status;
+---------------------------------+----------+--------------+------------------+-------------------+
| File                            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------------------+----------+--------------+------------------+-------------------+
| xxxxs-MacBook-Pro-bin.000004 |      120 |              |                  |                   |
+---------------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.03 sec) 
and on slave
Code:
mysql> 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 | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |
+----------------------------------+----------------------+-------------+-------------+---------------+---------------------------------+---------------------+-------------------------+---------------+---------------------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Waiting for master to send event | xxxxs-MacBook-Pro | userreplica  |        3306 |            60 | xxxxs-MacBook-Pro-bin.000004 |                120 | mysqld-relay-bin.000016 |          280 | xxxxs-MacBook-Pro-bin.000004 | Yes              | Yes              |                | mysql              |                    |                        | mydb.%                  |                            |          0 |            |            0 |                120 |            436 | None            |                |            0 | No                |                    |                    |                |                  |                |                    0 | No                            |            0 |              |              0 |                |
+----------------------------------+----------------------+-------------+-------------+---------------+---------------------------------+---------------------+-------------------------+---------------+---------------------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
1 row in set (0.00 sec)
PHP Code:
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                                 | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
|  1 | system user |           | NULL | Connect |   12 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  2 | system user |           | NULL | Connect |   12 | Waiting for master to send event                                      | NULL             |
|  4 | root        | localhost | NULL | Query   |    0 | NULL                                                                  | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec) 
in log its showing:
Quote:
131117 17:22:41 InnoDB: Initializing buffer pool, size = 8.0M
131117 17:22:41 InnoDB: Completed initialization of buffer pool
131117 17:22:41 InnoDB: Started; log sequence number 0 44556
131117 17:22: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.
131117 17:22:41 [Note] Event Scheduler: Loaded 0 events
131117 17:22:41 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.69' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
131117 17:22:41 [Note] Slave SQL thread initialized, starting replication in log 'xxxxs-MacBook-Pro-bin.000004' at position 120, relay log './mysqld-relay-bin.000014' position: 280
131117 17:22:41 [Note] Slave I/O thread: connected to master 'usereplica@xxxxs-MacBook-Pro:3306',replication started in log 'xxxxs-MacBook-Pro-bin.000004' at position 120
So I think replication is happening, but while querying the tables its giving 2 different errors for different tables:
PHP Code:
mysql> desc testdb.user_credentials;
ERROR 1146 (42S02): Table 'testdb.user_credentials' doesn't exist
mysql> desc testdb.user_basic_info;
ERROR 1033 (HY000): Incorrect information in file: './testdb/user_basic_info.frm' 
The tables are there. But why its saying doesnot exist.
PHP Code:
mysql> show tables;
+----------------------+
| Tables_in_testdb   |
+----------------------+
| c3p0_test_table      |
| testdb_email_id    |
| user_basic_info      |
| user_contact_details |
| user_credentials     |
+----------------------+
5 rows in set (0.00 sec) 
And the log shows:
Quote:
131117 17:41:33 [ERROR] Cannot find or open table testdb/user_credentials from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/...eshooting.html
how you can resolve the problem.

131117 17:41:46 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './testdb/user_basic_info.frm'
I checked the innodb_log_file_size is 48 MB in both master and slave.
So I checked the table is corrupted or not on slave,its saying corrupted:

PHP Code:
mysql> check table user_basic_info;
+--------------------------+-------+----------+-----------------------------------------------------------------+
| Table                    | Op    | Msg_type | Msg_text                                                        |
+--------------------------+-------+----------+-----------------------------------------------------------------+
| testdb.user_basic_info | check | Error    | Incorrect information in file: './testdb/user_basic_info.frm' |
| testdb.user_basic_info | check | error    | Corrupt                                                         |
+--------------------------+-------+----------+-----------------------------------------------------------------+
2 rows in set (0.00 sec) 
But how its corrupted ? Please advice.

pingu 11-20-2013 01:56 PM
I don't really know exactly what is wrong, maybe you could try to repair it. Mysql has a few alternatives for this, here's one link: http://dev.mysql.com/doc/refman/5.1/...ng-tables.html
But I must say I doubt it.
You are replicating from a higher version master to a lower version slave, and not only one major version between but two!
The other way might work - it's not supported, only with one major version difference, but still it could work.
You see, a new version often means changes in database structure, so replication from 5.6 to 5.1 means slave gets data it can't handle.

But I can't be sure of course, sometimes it works the way you do it.
So, you do have a backup of course, take another one before you start repairing.
You could try with mysql's built-in repair tools, I've used them a few times with good result.
Another possibility is to take out a fresh dump of testdb.user_basic_info from master and insertit into slave. Youll have to be careful here, stop mysql check position etc.
And please report back, I'm very interrested in how it goes!

divyashree 12-01-2013 09:09 PM
The issue got resolved. I installed mysql-server the same version as master in the slave and it worked fine without any error.


All times are GMT -5. The time now is 06:55 PM.