Mysql – How to transfer 70GB MySQL with no downtime

myisamMySQL

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.

Best Answer

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

Related Question