Migration MySQL 4 to 5

Fist a dump of all current databases

mysqldump \
  --all-databases -u httpd -p \
  --default-character-set=latin1 > alldb.tmp_sql

Stop mysql

/bin/sh /usr/local/mysql/support-files/mysql.server stop

and move the old datadir

sudo mv /usr/local/mysql/data /usr/local/mysql/data_4
sudo mkdir  /usr/local/mysql/data
chown -R _mysql /usr/local/mysql/data

Then split the datadump into seperate files

split -p "-- Current Database" alldb.tmp_sql

To prepend special params mysql dump provided, prepend the first file (probably xaa) and edit SET NAMES latin1 to SET NAMES utf8

for file in `ls x*`
do
 new=`head -1 $file | \
   sed -e 's/-- Current Database: //' -e 's/\`//g' -e 's/ /_/g'`
 cat xaa > $new.sql
 cat "$file" >> $new.sql
done

Then restart without checking the user privileges (so we can alter the usertable)

/usr/local/mysql/bin/safe_mysqld --skip-grant-tables &

Then finally do the import and watch for errors to fix

for file in `ls *.sql`
do
 echo "Importing $file"
 cat "$file" | sed -e \
   's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/' | mysql
done

Restart the processes:

/bin/sh /usr/local/mysql/support-files/mysql.server restart

Comments are closed.