Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to stop a old server running with MySQL 4.1 with lots of database on it (MyISAM and InnoDB), and to migrate to a new one (5.1.63 installed on a fresh Squeeze). It's a shared hosting servers so I have lot's of users and databases (around 2000).

I don't want to migrate all databases at same time but one by one.

What is the best way to do this ?

Should I expect some problems to migrate ?

share|improve this question
up vote 0 down vote accepted

You should mysqldump each database individually. Also, since the grants tables for MySQL 4.1 do not match the grant tables for MySQL 5.5, you have to dump MySQL Grants in SQL format.

# mysqldump each database except the 'mysql' schema
mysql ${MYSQL_CONN} -ANe"SHOW DATABASES" | grep -v mysql | > /tmp/ListDBs.txt
for DB in `cat /tmp/ListDBs.txt`
    mysqldump ${MYSQL_CONN} --hex-blob --databases ${DB} > ${DB}.sql 
# Dump MySQL Grants as SQL
mysql ${MYSQL_CONN} -ANe"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql ${MYSQL_CONN} -AN | sed 's/$/;/g' > MySQLUserGrants.sql

When you execute all the scripts against MySQL 5.5, they will

  • recreate each database
  • rebuild fresh indexes
  • load MySQL users from the MySQL 4.1 database into MySQL 5.5

Give it a Try !!!

share|improve this answer
Why using --hex-blob in mysqldump ? – profy Aug 16 '12 at 13:53
In the event, there was any CHAR BINARY, VARCHAR BINARY, BLOB data. If you know you do not have any, then don't include --hex-blob. – RolandoMySQLDBA Aug 16 '12 at 15:02

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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