# MIgration of MySQL databases



## murias (Feb 10, 2009)

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


----------



## ale (Feb 11, 2009)

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.


----------



## vivek (Feb 11, 2009)

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:

```
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:

```
mysql -u root -p password -e 'create database foo;'
```
Now import it

```
mysql -u root -p password foo < /path/to/foo.sql
```

Read man pages for more info. 

HTH


----------



## ale (Feb 11, 2009)

vivek said:
			
		

> No need to copy files


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.


Ok, more or less the same as I've said.



			
				vivek said:
			
		

> On new system install mysql server and create foo database


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.


----------



## Mel_Flynn (Feb 12, 2009)

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.


----------



## superHal44 (Mar 7, 2009)

*mysql*

if you need help with syntax try
http://www.examplenow.com/mysql

however i usually just do a mysqldump or copy over the db files depending on if you can take down the server or now

good luck


----------

