MIgration of MySQL databases

M

murias

I hope that I am able to make this as clear as possible.

I have inherited a serious jar of pickles.

I have a hard drive that has the directory structure of 4 different freebsd machines on it. This drive is nonbootable. From the root of the hard drive looks like this:

/machine1
/machine2
/machine3
/machine4

Where the directory structure below each machine directory is an rsync backup of the entire file structure of that machine. such that the machine1 directory does have /bin /usr /boot /root /etc .... sub-directories... and so do the other machine directories.

What I need to do is get all databases out of the machine2 directory which was running MySQL ver 4.1.3 and migrated to another running machine that has 5.1 on it.

What are my options? I am willing to try anything that gives me the end result.

The existing databases on the machine that is running 5.1 are of no consequence to anyone, and can be obliterated if needed.

I greatly appreciate any and all insights that anyone may have for me.
Cheers
Murias
 
What I would try is:
  • install mysql41-server
  • copy the datafiles from machine2 and start mysqld
  • if everything seems ok, dump all the databases using mysqldump
  • pkg_delete -x mysql41
  • install mysql51-server
  • import the dumped files except for information_schema and mysql
  • eventually fix errors on dumped files and repeat the previous step
  • manually analyze information_schema and mysql dumps to rebuild users, grants, etc.
 
No need to copy files .. you need to dump mysql database export using mysqldump. It has option to export to various version. Just read man page and you get the idea. Once dump is ready move all sql dumps to new server. Install mysqlserver and use mysql command to import data. For example on old mysql backup database foo:
Code: 
mysqldump -u user -p password foo > foo.sql
scp foo.sql user@newsyste:/path/to/

On new system install mysql server and create foo database and required users:
Code: 
mysql -u root -p password -e 'create database foo;'
Now import it
Code: 
mysql -u root -p password foo < /path/to/foo.sql

Read man pages for more info.

HTH
 
vivek said:
No need to copy files
Click to expand...
He has just the filesystem of the machine with the old version.
And it's not bootable.
So I think he has to fix this before either making it bootable again (think about qemu...) or recovering the datafiles.
Do you have any idea about this?

vivek said:
.. you need to dump mysql database export using mysqldump. It has option to export to various version. Just read man page and you get the idea. Once dump is ready move all sql dumps to new server. Install mysqlserver and use mysql command to import data.
Click to expand...
Ok, more or less the same as I've said.

vivek said:
On new system install mysql server and create foo database
Click to expand...
mysqldump has an option to include the create database in the script, and with the correct character set.

I can't remember about all the options as, being lazy, at work I wrote a script to dump/restore (using mysqldump/mysql) any schema using the options I need.

@murias
Ask if you want the (really stupid) script.
 
An option to try (since you can mount the disk) is to make a tarball of the /var/db/mysql directory, (re)move /var/db/mysql on the 5.1 server and then run mysql-upgrade.
I'm not sure about the extent of upgrading that script can do, but it's worth investigating.
 
You must log in or register to reply here.
Back
Top