I am going to answer based on option #3.
You can dump all databases except the following:
- information_schema
- performance_schema
- mysql
You should dump it like this:
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="select group_concat(schema_name) from information_schema.schemata"
SQL="${SQL} where schema_name not in ('information_schema','mysql','performance_schema')"
DBLIST=`mysql -ANe"${SQL}" | sed 's/,/ /g'`
mysqldump --databases ${DBLIST} | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' > AllMyData.sql
You can just load that and you are all set.
If you want to move the MySQL Users and Passwords, do this:
MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ',"
SQLSTMT="${SQLSTMT} QUOTE(user),'@',QUOTE(host),';') "
SQLSTMT="${SQLSTMT} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > GetGrants.sql
echo "SET sql_log_bin = 0;" > MySQLUserGrants.sql
mysql ${MYSQL_CONN} -AN < GetGrants.sql | sed 's/$/;/g' >> MySQLUserGrants.sql
rm -f GetGrants.sql
Then, just import MySQLUserGrants.sql into the MySQL 5.6 instance.
Give it a Try !!!
Your issues have nothing to due with the version of MySQL. It has to do with the Storage Engine.
Answer to PROBLEM #1 : Wow is that slow!
Running mysqldump only touches data from the .MYD file of the MyISAM table. Thus, I do not find anything surprising about dumping 163 million rows in 15 minutes.
Loading data into Amazon RDS taking 50 hours is not shocking to me either. Why ?
Regardless of which server model you choose for MySQL RDS, InnoDB transaction logs (ib_logfile0, ib_logfile1) are always 128M and are not allowed to be changed, not even which the RDS CLI. I wrote about this before : Local database vs Amazon RDS
All writes to InnoDB are written to the Double Write Buffer> You should disable it before loading : See my post Possibilities to speed up InnoDB INSERTs and UPDATEs
Each chunk of rows from each INSERT is handled as a transaction with stuff being written through ibdata1's double write buffer and the transaction logs. Thus, the slowness.
Answer to PROBLEM #2 : Where are my rows?
Look at the nodeindex. I can see it is a prefix index.
According to the MySQL Documentation on CREATE INDEX:
Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 1000 bytes long for MyISAM tables, and 767 bytes for InnoDB tables.
I can almost guarantee that any row where the length of graph,subject,predicate,object exceeds 767 did not make it into the InnoDB table .
Answer to PROBLEM #3 : Wow is that slow!
This is due to the Storage Engine.
When you run select count(id) from node; against a MyISAM, MyISAM cheats and reaches into the .MYD header to get the row count. Thus, the running time for getting a row count is not a function of the actual numbers of rows. That's way the MySQL Query Optimizer optimizes away all standard mechanisms and gives you a row count.
When it comes to InnoDB, because it does not store the row count, a table must be fully scanned each time : See my post Why doesn't InnoDB store the row count?
SUGGESTION
I would not import it as InnoDB. I would import is MyISAM first. Then, convert all your MyISAM tables to InnoDB. Before converting it, you may have to change the nodeindex or get rid of it altogether. Otherwise, you will lose the rows upon conversion.
See my post Which first: upgrade mysql version or convert storage engine? for more information.
Best Answer
I have resolved this issue using the instructions posted here: http://dev.mysql.com/doc/refman/5.6/en/innodb-backup.html
I was unaware that by default all data in InnoDB tables across all databases on the server are stored in one single file at the root of the data directory: ibdata1. That file, and I believe the log files, must also be copied along with the folders containing the FRM files (which apparently just store the table structure). The side effect of this, of course, is that any InnoDB tables already defined on your new server will be rendered unusable.