-1

There's master mysql running 5.6.30. Engine InnoDB. innodb_file_per_table is used. Slave server is running 5.5.55 Doing next steps:

 - (on master) innobackupex --no-timestamp /path/to/backup
 - (on master) innobackupex --apply-log
 - (on slave) /etc/init.d/mysql stop; clearing /var/lib/mysql
 - (on master) rsync -avpP /path/to/backup slave:/var/lib/mysql
 - (on slave) chown -R mysql:mysql /var/lib/mysql
 - (on slave) /etc/init.d/mysql start
 - doing other steps to enable Master-Slave replication...

Doing this steps on other servers, where no innodb_file_per_table is used, works ok: database successfully copied and replication sets up and works.

But with "multiple .ibd" starting mysql on slave fails with error in corrupted tables. So something I'm doing wrong. But what? Wrong export-import? Slave has to be not older than master? Something else?

error log:

170430 21:51:05 InnoDB: 5.5.55 started; log sequence number 3110007332942 170430 21:51:05 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 170430 21:51:05 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 170430 21:51:05 [Note] Server socket created on IP: '0.0.0.0'. 170430 21:51:05 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

Improve this question
3
  • Please provide the error log content or error message from the slave server for better understanding of the question.
    – Rathish Kumar B
    Commented Apr 30, 2017 at 18:29
  • Added error.log to answer. This error says nothing, 'cause 3 times backup was done from running server, every step said everything is OK, transfer was ok too.
    – Someone
    Commented Apr 30, 2017 at 18:55
  • It is advisable to have a newer version in the Slave. Perhaps an update happened to master.host, but perhaps such does not yet exist in 5.5.55.
    – Rick James
    Commented May 1, 2017 at 1:25

1 Answer 1

Reset to default
0

It's a fairly safe bet that some of the tables have (including system privilege tables) have structure that MySQL 5.5 can't recognize. MySQL 5.5 and 5.6 is a major version difference (regardless it looks minor) and they are not backwards compatible.

Try adding skip-grant-tables to my.cnf to see if the problem is only with system tables (when you add skip-grant-tables, privilege tables won't be used at all and anyone will be allowed to connect from anywhere with any password). If MySQL would start, that means you have a broken privileges tables and mysql_install_db should help you load the blank ones.

If it doesn't help, you may have InnoDB structure changes that MySQL 5.5 can't recognize. In that case, you'll have to use logical backup (mysqldump or mydumper) for such downgrade. BTW, you can use any of the working slaves for it.

BTW, if you're using innodb_file_per_table on the master, there's no need NOT to use it on slave. In fact, it doesn't make much sense to.

Improve this answer
2
  • Great thanks for so full answer! skip-grant-tables allowed to start mysql. I can login to shell, execute SQL statements. So the problem in MySQL versions, not in innobackupex usage by me. Now the answer is: will replication from 5.6 master to 5.5 slave work correctly?
    – Someone
    Commented May 1, 2017 at 16:50
  • It's possible to get it to work, but you will need to disable a number of MySQL 5.6 features on the master (these changes require a restart). Here's required my.cnf variables: binlog_checksum=NONE, binlog_row_image=FULL, binlog_rows_query_log_events=OFF, log_bin_use_v1_row_events=1, gtid_mode=OFF. You can find an explanation here
    – Aurimas Mikalauskas
    Commented May 2, 2017 at 3:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.