Home / server / Questions / 417431
Accepted
profy
Asked: 2012-08-15 08:17:54 +0800 CST

Migrate databases from MySQL 4.1 InnoDB to current version of MySQL 5.5

  • 772

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 ?

mysql

1 Answers

  1. Best Answer

    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_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql ${MYSQL_CONN} -ANe"SHOW DATABASES" | grep -v mysql | > /tmp/ListDBs.txt
for DB in `cat /tmp/ListDBs.txt`
do
    mysqldump ${MYSQL_CONN} --hex-blob --databases ${DB} > ${DB}.sql 
done
#
# 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 !!!

    • 0