I have a large amount (4000) of individual databases, 90% of which are small. They all include about 20 tables each, and in total, they use about 75GB of space. All tables are MyISAM.

I want to transfer them to a newer, more powerful server with an SSD for increased performance.

We do not need to continue using MyISAM - i.e. we're not using fulltext or anything - so whilst we could convert to InnoDB and then use Xtrabackup, the time it will actually take to convert to InnoDB concerns me.

Also, during the conversion, wouldn't the database be locked? I don't want any (or at least, less than 5-10 minutes) downtime on the master server.

I cannot use Xtrabackup because it will bring everything down while it copies as it's all MyISAM. I can't use rsync because mysqld has to be stopped during the copy. I can't convert to InnoDB and then use Xtrabackup because the time it will take to convert about 80000 tables (even if they are small) concerns me, and those tables will lock during the conversion process.

And I can't find any other solutions to get this data across! Help is hugely appreciated.

  • 1) convert everything to InnoDB using pt-online-schema-change. 2) create a slave on the new server with xtrabackup 3) switch from old server to the new server. Downtime will be less than a minute – akuzminsky Jul 2 '14 at 17:10
  • That.. sounds pretty good to me (pt-online-schema-change). I didn't know that was a thing. It all sounds like I should be totally fine to go ahead with this. – Marc Fowler Jul 2 '14 at 18:09
  • But I did just read that I must have a unique key for each table - is that accurate? (fromdual.com/online-ddl_vs_pt-online-schema-change) – Marc Fowler Jul 2 '14 at 18:09
  • 1
    unique key or primary key. It's good to have one anyway. For those tables without any you can go ahead to a plain ALTER TABLE. You said the tables are small, so blocking time should be short – akuzminsky Jul 2 '14 at 18:27
  • Another option for quick backups of MyISAM databases is LVM snapshots. A good tool to help is mylvmbackup. But your data directory needs to be on a volume capable of snapshots already. – Bill Karwin Jul 2 '14 at 18:29

You are going to have to bite the bullet somewhere in this process.

You should determine which databases need to be dumped first (smallest to largest):

    SELECT table_schema db,SUM(data_length) DataSize
    FROM information_schema.tables WHERE table_schema NOT IN
    GROUP BY table_schema
) A ORDER BY DataSize;

Take this list of databases and numerically generate conversion scripts

SQL="SELECT db FROM (SELECT table_schema db,SUM(data_length) DataSize"
SQL="${SQL} FROM information_schema.tables WHERE table_schema NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql')"
SQL="${SQL} AND engine='MyISAM'"
SQL="${SQL} GROUP BY table_schema) A ORDER BY DataSize"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLIST}
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema=DATABASE()"
SQL="${SQL} AND engine='MyISAM'"
SQL="${SQL} ORDER BY data_length+index_length"
for DB in `cat ${DBLIST}`
    (( DBNUM++ ))
    mysql ${MYSQL_CONN} -ANe"${SQL}" > ${CONVSCRIPT}

You can now run each conversion script by name in alphanumeric order.

As you get to the bigger databases, you should run them during off hours.

Once everything is converted to InnoDB, enable binary logging on the current server.

Next, mysqldump all of it as a point-in-time dump and gzip it

DUMP_OPTIONS="--routines --triggers --single-transaction --master-data=1"
mysqldump ${MYSQL_CONN} ${DUMP_OPTIONS} | gzip > MySQLData.sql.gz

Transport MySQLData.sql.gz to the new machine and setup replication.

I'll leave the rest to your imagination

  • Please keep in mind that I make no promises about not having downtime or performance issues during the conversion. All I did was basically automate the conversion by size. – RolandoMySQLDBA Jul 2 '14 at 18:27
  • Thanks for the help Rolando. Couple of questions with this.. why can't you set up replication WITHOUT having something to start from? I don't understand why that's required if replication copies over ALL changes from one server to another. It might take a lot longer, but why can't you just turn on replication and wait? – Marc Fowler Jul 3 '14 at 9:48
  • Also, could I set up my server and restore an (image-based) backup of my live server (this is obviously 24 hours or whatever behind the live one). And then run this process on the copy to do the conversion to InnoDB without it affecting the live one. Then set up replication from the live, MyISAM server to this new InnoDB one? – Marc Fowler Jul 3 '14 at 10:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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