Recently I tested several different MySQL upgrade scenarios for the blog posts I wrote about the temporal format upgrade. This required me to create a bunch of tables with datetime/timestamp/time columns in MySQL 5.5 and then physically copy those tables to a MySQL 5.6 or 5.7 database. I created 4 copies of each table to test out InnoDB versus MyISAM and partitioned tables versus non-partitioned tables. MySQL Sandbox made the process easy.

These are the MySQL versions I wanted to test:

  • 5.5.34
  • 5.6.15
  • 5.6.22
  • 5.6.25
  • 5.7.7

I was able to download the latest 5.6 and 5.7 releases here and the older releases here.

Read on for an overview of the MySQL Sandbox commands I used.

Create 5 different MySQL instances on my laptop

make_sandbox ~/Downloads/mysql-5.5.34-osx10.6-x86_64.tar.gz 
make_sandbox ~/Downloads/mysql-5.6.15-osx10.7-x86_64.tar.gz 
make_sandbox ~/Downloads/mysql-5.6.22-osx10.8-x86_64.tar.gz 
make_sandbox ~/Downloads/mysql-5.6.25-osx10.8-x86_64.tar.gz 
make_sandbox ~/Downloads/mysql-5.7.7-rc-osx10.8-x86_64.tar.gz

Create some tables in MySQL 5.5.34 and populate them with test data

~/sandboxes/msb_5_5_34/use -f < /tmp/temporal_testing.sql

Stop all 5 instances

~/sandboxes/msb_5_5_34/stop
~/sandboxes/msb_5_6_15/stop
~/sandboxes/msb_5_6_22/stop
~/sandboxes/msb_5_6_25/stop
~/sandboxes/msb_5_7_7/stop

Replace the 5.6 and 5.7 data directories with a copy the 5.5 data directory

sudo rm -rf ~/sandboxes/msb_5_6_15/data; sudo cp -fpr ~/sandboxes/msb_5_5_34/data ~/sandboxes/msb_5_6_15/.
sudo rm -rf ~/sandboxes/msb_5_6_22/data; sudo cp -fpr ~/sandboxes/msb_5_5_34/data ~/sandboxes/msb_5_6_22/.
sudo rm -rf ~/sandboxes/msb_5_6_25/data; sudo cp -fpr ~/sandboxes/msb_5_5_34/data ~/sandboxes/msb_5_6_25/.
sudo rm -rf ~/sandboxes/msb_5_7_7/data; sudo cp -fpr ~/sandboxes/msb_5_5_34/data ~/sandboxes/msb_5_7_7/.

Start the instances

~/sandboxes/msb_5_5_34/start
~/sandboxes/msb_5_6_15/start
~/sandboxes/msb_5_6_22/start
~/sandboxes/msb_5_6_25/start
~/sandboxes/msb_5_7_7/start

Run some tests

~/sandboxes/msb_5_6_22/use test

mysql > \W
Show warnings enabled.
mysql > alter table member 
    ->   add column is_active tinyint not null default 1;
Query OK, 10000000 rows affected, 1 warning (45.43 sec)
Records: 10000000  Duplicates: 0  Warnings: 1

Note (Code 1880): TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.

~/sandboxes/msb_5_6_25/use test

mysql > \W
Show warnings enabled.
mysql > set global avoid_temporal_upgrade = 'ON';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 1287): '@@avoid_temporal_upgrade' is deprecated and will be removed in a future release.
mysql > alter table members 
    ->   add column is_active tinyint not null default 1;
Query OK, 0 rows affected (15.48 sec)
Records: 0  Duplicates: 0  Warnings: 0