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.
