move a database

[rickthehick]

Hi all,
I have a database with about 8M records and growing. This was created on a very small drive and now I need to move it to another drive. Can someone suggest how to do this?

thanks

 

[TonyGroves]

Simple. Stop the server, move the data directory (the default path is mysql/data) to the new location, edit my.cnf (could be my.ini) so that the "datadir" entry in the [mysqld] section specifies the new path, and restart the server.

 

[rickthehick]

Thanks Tony. I moved the 'data' directory and edited my.ini, but now the server won't start.

#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"

#Path to the database root
datadir="H:/MySQL/Data/"


I looked up error 10061 and an troubleshooting why it wont start but not getting anywhere.

Any suggestions?

 

[TonyGroves]

Did you move the entire data directory? h:/mysql/data should now contain a subdirectory for each database (including "mysql" for the system tables). You might also check that the MySQL service user account (probably Administrator) has full permissions on that directory tree.

 

[rickthehick]

I did copy the entire directory last night. This morning I renamed the data directory on C to avoid confusion. Now everything that was on C: is now on(in) H: and there is no 'data' directory at C:/Program Files/MySQL/MySQL Server 5.0/.

this was in my error log when I just tried to restart the service in windows.

******************************
060818 10:00:37 InnoDB: Operating system error number 3 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: File name H:\MySql\data\ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.
********************************

I can see and access H but looks like MySql does not recognize it? This is a network drive and not a volume on my PC. Does that make a difference?

THanks for your time and help!

 

[TonyGroves]

Do you use InnoDB tables? If not, then you can disable InnoDB with the option "skip-innodb" in the [mysqld] section of your config file. And while you're at it, you could add "skip-bdb" as well (if you don't use BDB).

 

[rickthehick]

it was some sort of permission issue with the network drive. I installed another hard drive to my system and was able to use that as the new drive for my 'data'.

Aloha and thanks for the help