Believe it or not, I once wrote a post about why you should not do that (How can I disable utf8mb4 entirely on MySQL 5.5?). However, in the spirit of my old post and the commentary in it from @ChristopherSchultz, I will go out on a limb and tell you how you can do it, then tell you why you should not.
I once wrote a post about the home position of any empty binary log:
Over the years in this forum, I learned from someone (I think it was either Aaron Brown or Morgan Tocker) that there is a universal position for all binary logs regardless of the MySQL Version: position 4.
I once put that in an answer (Mar 05, 2013 : MySQL Replication without stopping master). In Step 06 from my answer I wrote this:
CHANGE MASTER TO
MASTER_HOST='10.1.20.30',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;
I also used position 4 in these other posts
Rarely do I repeat this info in any other posts for a reason. Personally, I fear that binlog events might be represented differently from version to version in terms of the size (in bytes) of each event. Believe it or not, over the past two weeks I have been upgrading DB Servers from MySQL 5.5. to MySQL 5.6. Due to mixed mode binary logging, there have been rare events when replication breaks and you cannot reset it from binlog files and positions by standard replication techniques. I have had to hose binary logs on Master, copy data, and setup replication from scratch a few times (5 out 400 VMs, but it still happened 5 times). I am very sure that replicating from a new Master to an old Slave would cause many more problems along these lines.
Therefore, I can only say that you can do it theoretically and MySQL may not object, that is, until MySQL Replication encounters a binlog event that is in a format it does not recognize and cannot interpret.
UPDATE 2014-11-18 22:32 EST
Just for official reference, this example CHANGE MASTER TO command
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
appears in the MySQL 5.6 Documentation. It's also in the MySQL 4.1 Documentation.
Thus, position 4 has always been known (I have only known a couple of years). Notwithstanding, I trust MySQL Replication from old Master to new Slave (but not on a permanent basis). I do not trust MySQL Replication from new Master to old Slave.
UPDATE 2014-11-19 17:47 EST
Please don't go down the Circular Replication path as it just adds to the risk of lost binlog events due to different versions. You should always replicate one direction to a newer version. Then, just failover to the newer version.
Since it is a temp table, you are OK to just delete the files.
rm -f *#sql-ib32694.*
Is this harmful to InnoDB ? No. There was a data dictionary entry still inside ibdata1. However, that entry is either missing or invalid (due to the tablespace_id). There is actually a one in 1,099,511,627,766 chance of it causing a problem.
See my older posts on why it is not that harmful and how to deal with
Please keep in mind that temp tables (regardless of storage engine) are transient in nature. A temp table will disappear under these circumstances:
- When a query is finished using it
- When a DB connection terminates normally
- When a DB connection terminates abnormally
If a temp table still exists due to a crash, rest assured that the data dictionary no longer acknowledges its existence. Please delete them and keep a good conscience.
If you are worried about, your only recourse when this happens is to perform a fully InnoDB Cleanup. See my post Howto: Clean a mysql InnoDB storage engine? in StackOverflow. The lesson here
- Use
CREATE TEMPORARY TABLE (...) ENGINE=MyISAM; if you have to use temp tables in your app.
- Any system-generated InnoDB temp tables can be deleted after a crash.
Best Answer
While @ohlin's answer has a decent workaround, it still requires setting up an entire MySQL instance with the undo logs configured the way you want. Then, you have to failover the application to the new MySQL Instance. That still introduces a few minutes of logical downtime from the perspective of the application because of changing the IP addresses in the code or changing DNS.
Your Original Question
The answer is definitely NO. Why ? Given the following diagram
Look at the following options for the location of the undo logs
OPTION #1 : Undo Logs Inside System Tablespace
By default, the undo logs reside in the system tablespace file known as ibdata1. Undo logs has been the cause of the greatest amount of uncontrolled growth in the face of many transactions (See my post How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?)
To relocate ibdata1, you must configure innodb_data_home_dir and innodb_data_file_path. They are global variables, but they are not dynamic. Since a mysql restart would be required to change these settings, the undo logs buried inside ibdata1 cannot be moved for a live MySQL instance.
OPTION #2 : Undo Logs Outside System Tablespace
There are options to configure the undo logs on different disks:
Both innodb_undo_tablespaces and innodb_undo_directory are global variables, but they are not dynamic. Since a mysql restart would be required to change these settings, the undo logs outside ibdata1 cannot be moved for a live MySQL instance.
EPILOGUE
When I read this question, it reminded me of a blog from a FaceBook DB Engineer that configured InnoDB on different disks.
I referred to his blog in my post How do I determine how much data is being written per day through insert, update and delete operations?
Even with this idea, it requires configuring the undo logs in a different disk location from the very beginning. Doing this after mysql is running is simply not possible.