0

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.

6
  • 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, 2014 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. Jul 2, 2014 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) Jul 2, 2014 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, 2014 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. Jul 2, 2014 at 18:29

1 Answer 1

1

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 db FROM
(
    SELECT table_schema db,SUM(data_length) DataSize
    FROM information_schema.tables WHERE table_schema NOT IN
    ('information_schema','performance_schema','mysql')
    GROUP BY table_schema
) A ORDER BY DataSize;

Take this list of databases and numerically generate conversion scripts

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DBLIST=`/tmp/ListOfDatabasesOrderedBySize`
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}
DBNUM=1000000
SQL="SELECT CONCAT('ALTER TABLE ',table_name,' ENGINE=InnoDB;')"
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}`
do
    (( DBNUM++ ))
    CONVSCRIPT=DB${DBNUM}_${DB}.sql
    mysql ${MYSQL_CONN} -ANe"${SQL}" > ${CONVSCRIPT}
done

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

3
  • 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. Jul 2, 2014 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? Jul 3, 2014 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? Jul 3, 2014 at 10:04

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.