Mysql – Restoring old MySQL database settings

MySQLrestorewindows

I know this question may be similar to some others posted but think it may be slightly different, so hopefully it's ok.

Basically I just reinstalled Windows as I bought a new HD and I am in the process of copying all the MySQL databases back to the new installation dir; however I'm not sure if it's ok to copy all the default MySQL databases back with it? Those being:

  • cdcol
  • mysql
  • performance_schema
  • phpmyadmin
  • test
  • webauth

I assume one of these contain details I want to keep such as info about the users and also other stuff like mysql variables – unless that is found in a MySQL .ini file or something?

I'm not 100% sure but I'm pretty sure I'm still using the same version of MySQL. If it matters I am using XAMPP.

So basically my question is can I copy the old folders above over the new ones and what are these folders/databases used for exactly (other than the obvious phpmyadmin one)?

Best Answer

Let's say on the new server you want datadir to be D:\MySQLData

Simply copy everything and all subfolders from old server's C:\Program Files\MySQL\MySQL Server 5.5\data to the new server's D:\MySQLData. You said you are using XAMPP. RUn this query SHOW VARIABLES LIKE 'datadir';. Copy that folder to D:\MySQLData.

Then add this

[mysqld]
datadir="D:/MySQLData"

in the new server's my.ini

This will work seamlessly if the new server has the same version of MySQL as the old.

If the new server has the next higher major release (5.1 -> 5.5,5.5 - > 5.6) you will then have to run mysql_upgrade --upgrade-system-tables. Make sure you keep a backup.

If you are using InnoDB (in whole or in part), get all the InnoDB settings from the old my.ini over the new server's my.ini.

Give it a Try !!!

Related Question